Definition of metadata

Two: Get the DataBaseMetaData object

DatabaseMetaData metaData = Connection.getMetaData();

Three: DataBaseMetaData related api

1. getURL(): Returns a String class object representing the URL of the database;
2. getUserName(): Returns the user name that links the current database management system;
3. getDatabaseProductName(): Returns the version name of the database;
4. getDatabaseProductVersion(): Returns the version number of the database;
5. getDriverName(): Returns the name of the driver driver;
6. getDriveVersion(): Returns the version number of the database program;
7. isReadOnly(): Returns a boolean value indicating whether the database only supports read operations.

// 3. Result Set Metadata
    @Test
    public void testRs() throws Exception {
        String sql = "select * from dept ";

        // Get Connections
        Connection conn = JdbcUtil.getConnection();
        PreparedStatement pstmt = conn.prepareStatement(sql);
        ResultSet rs = pstmt.executeQuery();
        // Get Result Set Metadata (Target: Get column names from Result Set Metadata)
        ResultSetMetaData rs_metaData = rs.getMetaData();

        // Iterate each row of results
        while (rs.next()) {
            // 1. Get the number of columns
            int count = rs_metaData.getColumnCount();
            // 2. Traverse to get the name of each column
            for (int i=0; i<count; i++) {
                // Get the name of the column
                String columnName = rs_metaData.getColumnName(i + 1);
                // Gets the value of each column in each row
                Object columnValue = rs.getObject(columnName);
                // test
                System.out.print(columnName + "=" + columnValue + ",");
            }
        }

    }

The above code can iterate through the data of database tables, so we can use it to encapsulate a BaseDao base class into a common method:

/**
 * A generic dao, inherited by all the Daos you write yourself;
 * This class defines two common methods:
 *  1. Update (Delete Update Insert)
 *  2. query
 *
 */
public class BaseDao {

    // Initialization parameters
    private Connection con;
    private PreparedStatement pstmt;
    private ResultSet rs;

    /**
     * Updated Common Methods
     * @param sql   Updated sql statement (update/insert/delete)
     * @param paramsValue  sql The value corresponding to the placeholder in the statement (pass in null if no placeholder)
     */
    public void update(String sql,Object[] paramsValue){

        try {
            // Get Connections
            con = JdbcUtil.getConnection();
            // Create stmt object to execute commands
            pstmt = con.prepareStatement(sql);
            // Parameter metadata: Get the number of placeholder parameters
            int count = pstmt.getParameterMetaData().getParameterCount();

            // Set the value of the placeholder parameter
            if (paramsValue != null && paramsValue.length > 0) {
                // Loop assignment of parameters
                for(int i=0;i<count;i++) {
                    pstmt.setObject(i+1, paramsValue[i]);
                }
            }
            // Perform Updates
            pstmt.executeUpdate();

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.closeAll(con, pstmt, null);
        }
    }

    /**
     * General method of query
     * @param sql
     * @param paramsValue
     */
    public <T> List<T> query(String sql, Object[] paramsValue,Class<T> clazz){

        try {
            // Returned Set
            List<T> list = new ArrayList<T>();
            // object
            T t = null;

            // 1. Get connected
            con = JdbcUtil.getConnection();
            // 2. Create stmt objects
            pstmt = con.prepareStatement(sql);
            // 3. Get the number of placeholder parameters and set the value of each parameter
            //int count = pstmt.getParameterMetaData().getParameterCount();
            if (paramsValue != null && paramsValue.length > 0) {
                for (int i=0; i<paramsValue.length; i++) {
                    pstmt.setObject(i+1, paramsValue[i]);
                }
            }
            // 4. Execute the query
            rs = pstmt.executeQuery();
            // 5. Get Result Set Metadata
            ResultSetMetaData rsmd = rs.getMetaData();
            // --->Get the number of columns
            int columnCount = rsmd.getColumnCount();

            // 6. Traverse rs
            while (rs.next()) {
                // Objects to be encapsulated
                t = clazz.newInstance();

                // 7. Traverse each column of each row, encapsulating data
                for (int i=0; i<columnCount; i++) {
                    // Get the column name for each column
                    String columnName = rsmd.getColumnName(i + 1);
                    // Gets the column name for each column, corresponding value
                    Object value = rs.getObject(columnName);
                    // Encapsulation: Set to the properties of the t object [BeanUtils component]
                    BeanUtils.copyProperty(t, columnName, value);               
                }

                // Add encapsulated objects to the list collection
                list.add(t);
            }

            return list;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.closeAll(con, pstmt, rs);
        }
    }
}

I inherit the base class BaseDao at a specific Dao level:

public class AdminDao extends BaseDao {

    // delete
    public void delete(int id) {
        String sql = "delete from admin where id=?";
        Object[] paramsValue = {id};
        super.update(sql, paramsValue);
    }

    // insert
    public void save(Admin admin) {
        String sql = "insert into admin (userName,pwd) values (?,?)";
        Object[] paramsValue = {admin.getUserName(),admin.getPwd()};
        super.update(sql, paramsValue);
    }

    // Query All
    public List<Admin> getAll(){
        String sql = "select * from admin";
        List<Admin> list = super.query(sql, null, Admin.class);
        return list;
    }
}

Four: DbUtils components

Int update( String sql, Object param);
Int update( String sql, Object... param);
Int[] batch( String sql, Object[][] params)
Note: If the operation database method of the DbUtils component is called and no connection object is passed in, then the data source object needs to be passed in when instantiating the QueryRunner object: QueryRunner qr = new QueryRunner(ds);

Some objects that DbUtils provides to encapsulate results:

/**
 * 1. Data Access Layer, Interface Design
 */
public interface IAdminDao {

    /**
     * Preservation
     * @param admin
     */
    void save(Admin admin);

    /**
     * Query by username password
     */
    Admin findByNameAndPwd(Admin admin);

    /**
     * Check if user name exists
     * @param name   User name to check
     * @return  true Indicates that the user name already exists; otherwise, the user name does not exist
     */
    boolean userExists(String name);
}


--------------------------------------------------------------------

/**
 * 2. Implementation Class of Data Access Layer Interface
 * (Importing DbUtils components to simplify jdbc operations)
 */
public class AdminDao implements IAdminDao {

    private Connection con;
    private QueryRunner qr = new QueryRunner();

    @Override
    public Admin findByNameAndPwd(Admin admin) {
        String sql = "select * from admin where userName=? and pwd=?";
        try{
            con = JdbcUtil.getConnection();
            Admin ad = qr.query(con, sql, 
                    new BeanHandler<Admin>(Admin.class), 
                    admin.getUserName(),
                    admin.getPwd());
            return ad;
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.closeAll(con, null, null);
        }
    }

    @Override
    public void save(Admin admin) {
        String sql = "INSERT INTO admin(userName,pwd) VALUES(?,?);";
        try {
            con = JdbcUtil.getConnection();
            // Method updates using DbUtils components
            qr.update(con, sql, admin.getUserName(),admin.getPwd());
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.closeAll(con, null, null);
        }

    }

    @Override
    public boolean userExists(String name) {
        String sql = "select id from admin where userName=?";
        try {
            con = JdbcUtil.getConnection();
            Integer in = qr.query(con, sql, new ScalarHandler<Integer>(), name);
            if (in != null){
                return true;
            }
            return false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JdbcUtil.closeAll(con, null, null);
        }
    }

}

The above share is the reward of tonight!I find that I am still very happy to watch the video to find information and learn recently. I have gained a lot. I hope that I will cherish the time and understand the unclear knowledge to make up for my deficiency.Come on, tomorrow is another great day!