Library System

# get basic information

# login username
user=librarySystemManager

# login password
password=123456

# the url of the database
url=jdbc:mysql://localhost:3306/LibrarySystem?rewriteBatchedStatements=true

# database driver
driverClass=com.mysql.cj.jdbc.Driver

# Provide four basic information to get the connection
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/LibrarySystem?rewriteBatchedStatements=true
username=librarySystemManager
password=123456

# The basic information to manage the database connection pool
initialSize=10
maxActive=10

/**
 * @Description  Generic add, delete and update operation (version2.0) (Consider database transaction)
 * @author gregperlinli
 * @param conn connection of database
 * @param sql sql format
 * @param args fill the placeholder(variable)
 *
 * How to output:
 * BaseDAO.update(conn, sql, ...args);
 */
public int update(Connection conn, String sql, Object ...args) throws Exception {  // the length of variable parameters should be equal to the number of placeholders
    PreparedStatement ps = null;
    try {
        // precompiled Statement and return ps instance
        ps = conn.prepareStatement(sql);
        // fill placeholder
        for ( int i = 0; i < args.length; i++ ) {
            // Be careful to the parameter declaration exception
            ps.setObject(i+1, args[i]);
        }
        // perform operation
        return ps.executeUpdate();
    } catch ( Exception e ) {
        e.printStackTrace();
    } finally {
        // close the resource
        JDBCUtills.closeResource(null, ps);
    }
    return 0;
}

/**
 * @Description Generic query with one object
 *              using PreparedStatement to realize the query operation by different form (version 2.0, consider to transaction)
 * @author gregperlinli
 * @param conn connection of database
 * @param sql sql format
 * @param args fill the placeholder(variable)
 * @return Class or null
 *
 * How to output:
 * ClassName className = getQuery(conn, ClassName.class, sql, ...args);
 */
 public T getQuery(Connection conn, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // get the metadata of the result set：ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            // get the column of the result set through metadata
            int columnCount = rsmd.getColumnCount();
            if (rs.next()) {
                T t = clazz.newInstance();
                // handle each column in one row of the result set
                for (int i = 0; i < columnCount; i++) {
                    // get the value of the column
                    Object columnValue = rs.getObject(i + 1);
                    // get name of each row
                    String columnName = rsmd.getColumnLabel(i + 1);
                    // give one corresponding "columnName" of the Object "t" assignment to "columnValue" through reflex
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                return t;
            }
        } catch ( Exception e ) {
            e.printStackTrace();
        } finally {
            JDBCUtills.closeResource(null, ps, rs);
        }
        return null;
 }

/**
 * @Description Generic query with multiple object
 *              using PreparedStatement to realize multi query operation by different form (version 2.0, consider to transaction)
 * @author gregperlinli
 * @param conn connection of database
 * @param sql sql format
 * @param args fill the placeholder(variable)
 * @return List or null
 *
 * How to output:
 * List<ClassName> listName = getMultiQuery(Classname.class, sql, ...args);
 *
 * How to print out:
 * listName.forEach(System.out::println);
 */
 public List<T> getMultiQuery(Connection conn, String sql, Object...args) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i + 1, args[i]);
            }
            rs = ps.executeQuery();
            // get the metadata of the result set：ResultSetMetaData
            ResultSetMetaData rsmd = rs.getMetaData();
            // get the column of the result set through metadata
            int columnCount = rsmd.getColumnCount();
            // create an aggregate object
            ArrayList<T> list = new ArrayList<T>();
            while (rs.next()) {
                T t = clazz.newInstance();
                // handle each column in one row of the result set and assignment the value to object "t"
                for (int i = 0; i < columnCount; i++) {
                    // get the value of the column
                    Object columnValue = rs.getObject(i + 1);
                    // get name of each row
                    String columnName = rsmd.getColumnLabel(i + 1);
                    // give one corresponding "columnName" of the Object "t" assignment to "columnValue" through reflex
                    Field field = clazz.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(t, columnValue);
                }
                list.add(t);
            }
            return list;
        } catch ( Exception e ) {
            e.printStackTrace();
        } finally {
            JDBCUtills.closeResource(null, ps, rs);
        }
        return null;
  }