SQLITEE.JAVA
java
2 months ago
15 kB
1
Indexable
Never
package Controller; import Model.History; import Model.Logs; import Model.Product; import Model.User; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.sql.PreparedStatement; import java.sql.SQLException; public class SQLite { public int DEBUG_MODE = 0; String driverURL = "jdbc:sqlite:" + "database.db"; private Connection connection; public void createNewDatabase() { try (Connection conn = DriverManager.getConnection(driverURL)) { if (conn != null) { DatabaseMetaData meta = conn.getMetaData(); System.out.println("Database database.db created."); } } catch (Exception ex) { System.out.print(ex); } } public void createHistoryTable() { String sql = "CREATE TABLE IF NOT EXISTS history (\n" + " id INTEGER PRIMARY KEY AUTOINCREMENT,\n" + " username TEXT NOT NULL,\n" + " name TEXT NOT NULL,\n" + " stock INTEGER DEFAULT 0,\n" + " timestamp TEXT NOT NULL\n" + ");"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("Table history in database.db created."); } catch (Exception ex) { System.out.print(ex); } } public void createLogsTable() { String sql = "CREATE TABLE IF NOT EXISTS logs (\n" + " id INTEGER PRIMARY KEY AUTOINCREMENT,\n" + " event TEXT NOT NULL,\n" + " username TEXT NOT NULL,\n" + " desc TEXT NOT NULL,\n" + " timestamp TEXT NOT NULL\n" + ");"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("Table logs in database.db created."); } catch (Exception ex) { System.out.print(ex); } } public void createProductTable() { String sql = "CREATE TABLE IF NOT EXISTS product (\n" + " id INTEGER PRIMARY KEY AUTOINCREMENT,\n" + " name TEXT NOT NULL UNIQUE,\n" + " stock INTEGER DEFAULT 0,\n" + " price REAL DEFAULT 0.00\n" + ");"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("Table product in database.db created."); } catch (Exception ex) { System.out.print(ex); } } public void createUserTable() { String sql = "CREATE TABLE IF NOT EXISTS users (\n" + " id INTEGER PRIMARY KEY AUTOINCREMENT,\n" + " username TEXT NOT NULL UNIQUE,\n" + " password TEXT NOT NULL,\n" + " role INTEGER DEFAULT 2,\n" + " locked INTEGER DEFAULT 0\n" + ");"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("Table users in database.db created."); } catch (Exception ex) { System.out.print(ex); } } public void dropHistoryTable() { String sql = "DROP TABLE IF EXISTS history;"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("Table history in database.db dropped."); } catch (Exception ex) { System.out.print(ex); } } public void dropLogsTable() { String sql = "DROP TABLE IF EXISTS logs;"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("Table logs in database.db dropped."); } catch (Exception ex) { System.out.print(ex); } } public void dropProductTable() { String sql = "DROP TABLE IF EXISTS product;"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("Table product in database.db dropped."); } catch (Exception ex) { System.out.print(ex); } } public void dropUserTable() { String sql = "DROP TABLE IF EXISTS users;"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("Table users in database.db dropped."); } catch (Exception ex) { System.out.print(ex); } } public void addHistory(String username, String name, int stock, String timestamp) { String sql = "INSERT INTO history(username,name,stock,timestamp) VALUES('" + username + "','" + name + "','" + stock + "','" + timestamp + "')"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()){ stmt.execute(sql); } catch (Exception ex) { System.out.print(ex); } } public void addLogs(String event, String username, String desc, String timestamp) { String sql = "INSERT INTO logs(event,username,desc,timestamp) VALUES('" + event + "','" + username + "','" + desc + "','" + timestamp + "')"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()){ stmt.execute(sql); } catch (Exception ex) { System.out.print(ex); } } public void addProduct(String name, int stock, double price) { String sql = "INSERT INTO product(name,stock,price) VALUES('" + name + "','" + stock + "','" + price + "')"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()){ stmt.execute(sql); } catch (Exception ex) { System.out.print(ex); } } public void addUser(String username, String password) { String sql = "INSERT INTO users(username,password) VALUES(?, ?)"; String hash = Encrypt.generateSecurePassword(password); try (Connection conn = DriverManager.getConnection(driverURL); PreparedStatement ps = conn.prepareStatement(sql)) { ps.setString(1, username); ps.setString(2, hash); ps.executeUpdate(); } catch (SQLException ex) { System.out.print(ex); } } public ArrayList<History> getHistory(){ String sql = "SELECT id, username, name, stock, timestamp FROM history"; ArrayList<History> histories = new ArrayList<History>(); try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)){ while (rs.next()) { histories.add(new History(rs.getInt("id"), rs.getString("username"), rs.getString("name"), rs.getInt("stock"), rs.getString("timestamp"))); } } catch (Exception ex) { System.out.print(ex); } return histories; } public ArrayList<Logs> getLogs(){ String sql = "SELECT id, event, username, desc, timestamp FROM logs"; ArrayList<Logs> logs = new ArrayList<Logs>(); try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)){ while (rs.next()) { logs.add(new Logs(rs.getInt("id"), rs.getString("event"), rs.getString("username"), rs.getString("desc"), rs.getString("timestamp"))); } } catch (Exception ex) { ex.printStackTrace(); } return logs; } public ArrayList<Product> getProduct(){ String sql = "SELECT id, name, stock, price FROM product"; ArrayList<Product> products = new ArrayList<Product>(); try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)){ while (rs.next()) { products.add(new Product(rs.getInt("id"), rs.getString("name"), rs.getInt("stock"), rs.getFloat("price"))); } } catch (Exception ex) { System.out.print(ex); } return products; } public ArrayList<User> getUsers(){ String sql = "SELECT id, username, password, role, locked FROM users"; ArrayList<User> users = new ArrayList<User>(); try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)){ while (rs.next()) { users.add(new User(rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getInt("role"), rs.getInt("locked"))); } } catch (Exception ex) {} return users; } public User getUser(String username, String password){ String sql = "SELECT id, username, password, role, locked FROM users WHERE username=? AND password=?"; User user = null; try{ Connection conn = DriverManager.getConnection(driverURL); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, username); pstmt.setString(2, password); ResultSet rs = pstmt.executeQuery(); while(rs.next()){ user = new User(rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getInt("role"), rs.getInt("locked")); } }catch(Exception ex){ ex.printStackTrace(); } return user; } public User getUser(String username) { String sql = "SELECT id, username, password, role, locked FROM users WHERE username=?"; User user = null; try { Connection conn = DriverManager.getConnection(driverURL); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, username); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { user = new User(rs.getInt("id"), rs.getString("username"), rs.getString("password"), rs.getInt("role"), rs.getInt("locked")); } } catch (Exception ex) { ex.printStackTrace(); } return user; } public void addUser(String username, String password, int role) { String sql = "INSERT INTO users(username,password,role) VALUES('" + username + "','" + password + "','" + role + "')"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()){ stmt.execute(sql); } catch (Exception ex) { System.out.print(ex); } } public void updateUser(User user){ String sql = "UPDATE users SET username = ?, password = ?, role = ?, locked = ? WHERE username = ?"; try{ Connection conn = DriverManager.getConnection(driverURL); PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setString(1, user.getUsername()); pstmt.setString(2, user.getPassword()); pstmt.setInt(3, user.getRole()); pstmt.setInt(4, user.getLocked()); pstmt.setString(5, user.getUsername()); pstmt.execute(); System.out.println("User Updated"); }catch(Exception ex){ ex.printStackTrace(); } } public void removeUser(String username) { String sql = "DELETE FROM users WHERE username='" + username + "';"; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement()) { stmt.execute(sql); System.out.println("User " + username + " has been deleted."); } catch (Exception ex) { System.out.print(ex); } } public Product getProduct(String name){ String sql = "SELECT name, stock, price FROM product WHERE name='" + name + "';"; Product product = null; try (Connection conn = DriverManager.getConnection(driverURL); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)){ product = new Product(rs.getString("name"), rs.getInt("stock"), rs.getFloat("price")); } catch (Exception ex) { System.out.print(ex); } return product; } public void updateProductInDatabase(Product updatedProduct) { String sql = "UPDATE product SET stock = ?, price = ? WHERE name = ?"; try (Connection conn = DriverManager.getConnection(driverURL); PreparedStatement pstmt = conn.prepareStatement(sql)) { pstmt.setInt(1, updatedProduct.getStock()); pstmt.setDouble(2, updatedProduct.getPrice()); pstmt.setString(3, updatedProduct.getName()); pstmt.executeUpdate(); System.out.println("Product Updated in Database"); } catch (SQLException ex) { ex.printStackTrace(); } } }