las.DBConnector.java Source code

Java tutorial

Introduction

Here is the source code for las.DBConnector.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package las;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;

import org.apache.commons.lang.StringUtils;

import au.com.bytecode.opencsv.CSVReader;
import java.io.IOException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import javax.swing.ListSelectionModel;

/**
 *
 * @author alvinho0304
 */
public class DBConnector {

    public static DBConnector dbConnector;

    public static DBConnector getInstance() throws ClassNotFoundException, SQLException {
        if (dbConnector == null) {
            dbConnector = new DBConnector();
        }
        return dbConnector;
    }

    //Database name
    private static final String db_name = "LAS";

    //Database username and password
    private static final String USER = "las";
    private static final String PW = "las";

    //DO NOT MODIFY THIS TWO LINES!!!
    private static final String DRIVER = "org.apache.derby.jdbc.ClientDriver";
    private static final String JDBC_URL = "jdbc:derby://localhost:1527/" + db_name + ";create=true";

    //attributes for loading CSV Files into Table
    private static final String SQL_INSERT = "INSERT INTO ${table}(${keys}) VALUES(${values})";
    private static final String TABLE_REGEX = "\\$\\{table\\}";
    private static final String KEYS_REGEX = "\\$\\{keys\\}";
    private static final String VALUES_REGEX = "\\$\\{values\\}";

    static Connection conn = null;
    private static char separator;

    //DO NOT MODIFY THIS OPERATION!!!
    private DBConnector() throws SQLException, ClassNotFoundException {
    }

    /*  Command operations /common functionalities for database */
    //Getting connection from SQL Database
    public static void connect() throws ClassNotFoundException, SQLException {
        Class.forName(DRIVER);
        DBConnector.conn = DriverManager.getConnection(JDBC_URL, USER, PW);
        if (DBConnector.conn != null) {
            System.out.println("Connected to LAS Database");
        }
    }

    /**
     * Create Table with(example): tableName: ITEMS  <All in capital letters>
     * details: ITEM_ID,NAME,NUMBER_AVAILABLE
     * <All in capital letters and put a dash instead of space>
     */
    public static void createTable(String tableName, String details) throws SQLException {
        DatabaseMetaData dbmd = conn.getMetaData();
        ResultSet rs = dbmd.getTables(null, "LAS", tableName, null);

        if (!rs.next()) {
            String data = "CREATE TABLE " + tableName + "(" + details + ")";
            PreparedStatement pt = conn.prepareStatement(data);
            pt.executeUpdate();
            System.out.println(tableName + " has been created");
        } else {
            System.out.println(tableName + " already exists in LAS Database");
        }
    }

    //Online source with loading CSV into SQLTable : http://viralpatel.net/blogs/java-load-csv-file-to-database/
    /**
     * Load CSV test data into SQL Table
     *
     * example for clearFirst: boolean check =
     * DBConnector.checkDataExistedInTable("MEMBERS");
     *
     * if (check) { DBConnector.loadCSVIntoTable("src/resources/members.csv",
     * "MEMBERS", true); System.out.println("Test data inserted into MEMBERS
     * table"); }
     *
     * ignore createNewReader, since it uses for loadCSVIntoTable, don't modify
     * it
     *
     * Getter and Setter provided for Separator to set your own separator inside
     * your CSV File
     *
     * @param csvFile : src/resources/xxx.csv (put your csv file under this
     * path)
     * @param tableName: TABLENAME (All in capital letters)
     * @param clearFirst true = if data not existed in SQL Table, write test
     * data inside false = if data exisited in SQL Table, don't write again.
     * @throws java.lang.Exception
     */
    public static void loadCSVIntoTable(String csvFile, String tableName, boolean clearFirst) throws Exception {

        CSVReader csvReader = null;
        if (null == DBConnector.conn) {
            throw new Exception("Not a valid connection.");
        }
        try {

            csvReader = DBConnector.getInstance().createNewReader(csvFile);

        } catch (ClassNotFoundException | SQLException | FileNotFoundException e) {
            e.printStackTrace();
            throw new Exception("Error occured while executing file. " + e.getMessage());
        }

        String[] headerRow = csvReader.readNext();

        if (null == headerRow) {
            throw new FileNotFoundException(
                    "No columns defined in given CSV file." + "Please check the CSV file format.");
        }

        String questionmarks = StringUtils.repeat("?,", headerRow.length);
        questionmarks = (String) questionmarks.subSequence(0, questionmarks.length() - 1);

        String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
        query = query.replaceFirst(KEYS_REGEX, StringUtils.join(headerRow, ","));
        query = query.replaceFirst(VALUES_REGEX, questionmarks);

        String[] nextLine;
        PreparedStatement ps = null;
        try {
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(query);

            if (clearFirst) {
                //delete data from table before loading csv
                conn.createStatement().execute("DELETE FROM " + tableName);
            }

            final int batchSize = 1000;
            int count = 0;
            Date date = null;
            while ((nextLine = csvReader.readNext()) != null) {

                if (null != nextLine) {
                    int index = 1;
                    for (String string : nextLine) {
                        date = DateUtil.convertToDate(string);
                        if (null != date) {
                            ps.setDate(index++, new java.sql.Date(date.getTime()));
                        } else {
                            ps.setString(index++, string);
                        }
                    }
                    ps.addBatch();
                }
                if (++count % batchSize == 0) {
                    ps.executeBatch();
                }
            }
            ps.executeBatch(); // insert remaining records
            conn.commit();
        } catch (SQLException e) {
            conn.rollback();
            e.printStackTrace();
            throw new Exception("Error occured while loading data from file to database." + e.getMessage());
        } finally {
            if (null != ps) {
                ps.close();
            }

            csvReader.close();
        }
    }

    public CSVReader createNewReader(String csvFile) throws FileNotFoundException {
        CSVReader csvReader = new CSVReader(new FileReader(csvFile), DBConnector.separator);
        return csvReader;
    }

    public static char getSeparator() {
        return separator;
    }

    public static void setSeparator(char separator) {
        DBConnector.separator = separator;
    }
    //Loading CSV into Table END

    //Check whether data existed or not in specific TABLENAME
    public static boolean checkDataExistedInTable(String tableName) throws SQLException {
        boolean exists = true;
        String check = "SELECT * FROM " + tableName;
        PreparedStatement pt = conn.prepareStatement(check);
        ResultSet rs = pt.executeQuery();
        if (rs.next()) {
            exists = false;
        }
        return exists;
    }

    public static void CreateAndLoadDataIntoTable() {
        try {
            DBConnector.setSeparator(',');

            DBConnector.createTable("MEMBERS",
                    "MEMBER_ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY(START WITH 1,INCREMENT" + " BY 1), "
                            + "NAME VARCHAR(255) NOT NULL, " + "EMAIL VARCHAR(255), "
                            + "PRIVILEGE VARCHAR(255) NOT NULL, " + "ISSTAFF BOOLEAN NOT NULL, "
                            + "PRIMARY KEY (MEMBER_ID)");

            DBConnector.createTable("ITEMS",
                    "ITEM_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                            + "TITLE VARCHAR(255) NOT NULL,AUTHOR VARCHAR(255),TYPE VARCHAR(255),"
                            + "AMOUNTLEFT INT NOT NULL,PRIMARY KEY (ITEM_ID)");

            DBConnector.createTable("TRANSACTIONS", "MEMBER_ID INTEGER NOT NULL,ITEM_ID INTEGER NOT NULL,"
                    + "TRANSACTION_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,"
                    + "PRIMARY KEY (MEMBER_ID, ITEM_ID)," + "FOREIGN KEY (MEMBER_ID) REFERENCES MEMBERS(MEMBER_ID),"
                    + "FOREIGN KEY (ITEM_ID) REFERENCES ITEMS(ITEM_ID)");

            boolean checkMembers = DBConnector.checkDataExistedInTable("MEMBERS");
            boolean checkItems = DBConnector.checkDataExistedInTable("ITEMS");
            boolean checkTransactions = DBConnector.checkDataExistedInTable("Transactions");
            if (checkMembers) {
                DBConnector.loadCSVIntoTable("src/resources/members.csv", "MEMBERS", true);
                System.out.println("Data inserted into MEMBERS table");
            }
            if (checkItems) {
                DBConnector.loadCSVIntoTable("src/resources/items.csv", "ITEMS", true);
                System.out.println("Data inserted into ITEMS table");
            }
            if (checkTransactions) {
                DBConnector.loadCSVIntoTable("src/resources/transactions.csv", "TRANSACTIONS", true);
                System.out.println("Data inserted into TRANSACTIONS table");
            }

        } catch (SQLException ex) {
            Logger.getLogger(UI.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(UI.class.getName()).log(Level.SEVERE, null, ex);
        } catch (Exception ex) {
            Logger.getLogger(UI.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /*  Transaction Part Functions */
    public static void insertTransactionIntoTable(Member member, Item item) throws SQLException {
        String data = "INSERT INTO TRANSACTIONS(MEMBER_ID,ITEM_ID)" + "Values (?,?)";
        PreparedStatement pt = conn.prepareStatement(data);
        pt.setInt(1, member.getID());
        pt.setInt(2, item.getItemID());
        pt.executeUpdate();
    }

    public static ArrayList<Transaction> getTransactionTable() throws SQLException {
        ArrayList<Transaction> table = new ArrayList<>();
        String data = "SELECT * FROM Transactions";
        PreparedStatement pt = conn.prepareStatement(data);
        ResultSet rs = pt.executeQuery();
        while (rs.next()) {
            table.add(new Transaction(rs.getInt("MEMBER_ID"), rs.getInt("ITEM_ID"),
                    rs.getTimestamp("TRANSACTION_TIME")));
        }

        return table;
    }

    public static void insertTransactionIntoTable(Transaction transaction) throws SQLException {
        String data = "INSERT INTO TRANSACTIONS(MEMBER_ID, ITEM_ID)" + "Values (?,?)";
        PreparedStatement pt = conn.prepareStatement(data);
        pt.setInt(1, transaction.getMemberID());
        pt.setInt(2, transaction.getItemID());
        try {
            pt.executeUpdate();
        } catch (Exception DerbySQLIntegrityConstraintViolationException) {
            JOptionPane.showMessageDialog(null, "Cannot Issue an item a member already owns!");
        }

    }

    public static void removeTransactionFromTable(Transaction transaction) throws SQLException {
        String data = "DELETE FROM TRANSACTIONS WHERE MEMBER_ID = ?" + "AND ITEM_ID = ?";
        PreparedStatement pt = conn.prepareStatement(data);
        pt.setInt(1, transaction.getMemberID());
        pt.setInt(2, transaction.getItemID());
        pt.executeUpdate();
    }

    /*  Item Part Functions */
    public static void insertItemIntoTable(Item item) throws SQLException {
        String data = "INSERT INTO Items(title, author, type, amountleft)" + "Values (?,?,?,?)";
        PreparedStatement pt = conn.prepareStatement(data);
        pt.setString(1, item.getTitle());
        pt.setString(2, item.getAuthor());
        pt.setString(3, item.getType());
        pt.setInt(4, item.getAmountLeft());
        pt.executeUpdate();
    }

    public static ArrayList<Item> getItemTable() throws SQLException {
        ArrayList<Item> table = new ArrayList<>();
        String data = "SELECT * FROM Items";
        PreparedStatement pt = conn.prepareStatement(data);
        ResultSet rs = pt.executeQuery();
        while (rs.next()) {
            table.add(new Item(rs.getString("title"), rs.getString("author"), rs.getString("type"),
                    rs.getInt("Item_ID"), rs.getInt("amountleft")));
        }

        return table;
    }

    public static void incrementAmountLeft(Item item) throws SQLException {
        String data = "UPDATE ITEMS SET AMOUNTLEFT = ? WHERE ITEM_ID = ?";
        PreparedStatement pt = conn.prepareStatement(data);
        pt.setInt(1, (item.getAmountLeft() + 1));
        pt.setInt(2, item.getItemID());
        pt.executeUpdate();
    }

    public static void decrementAmountLeft(Item item) throws SQLException {
        String data = "UPDATE ITEMS SET AMOUNTLEFT = ? WHERE ITEM_ID = ?";
        PreparedStatement pt = conn.prepareStatement(data);
        pt.setInt(1, (item.getAmountLeft() - 1));
        pt.setInt(2, item.getItemID());
        pt.executeUpdate();
    }

    /*  Member Part Functions   */
    public static void insertMemberIntoTable(Member member) throws SQLException {
        String data = "INSERT INTO MEMBERS(MEMBER_ID,NAME,EMAIL,PRIVILEGE,ISSTAFF)" + "Values (?,?,?,?,?)";
        PreparedStatement pt = conn.prepareStatement(data);
        pt.setInt(1, member.getID());
        pt.setString(2, member.getName());
        pt.setString(3, member.getEmail());
        pt.setString(4, member.getPrivilege());
        pt.setBoolean(5, member.isIsStaff());
        pt.executeUpdate();
    }

    public static ArrayList<Member> getMemberTableIntoList() throws SQLException, ClassNotFoundException {
        ArrayList<Member> mtable = new ArrayList<>();
        String data = "SELECT * FROM LAS.MEMBERS";
        PreparedStatement pt = conn.prepareStatement(data);
        ResultSet rs = pt.executeQuery();
        while (rs.next()) {
            mtable.add(new Member(rs.getInt("MEMBER_ID"), rs.getString("NAME"), rs.getString("EMAIL"),
                    rs.getString("PRIVILEGE"), rs.getBoolean("ISSTAFF")));
        }

        return mtable;
    }

}