一、創建對應資料庫表的物體類

資料庫表結構

CREATE TABLE `databaseInfo` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `database_name` varchar(100) NOT NULL COMMENT '資料庫名稱',
  `table_name` varchar(100) NOT NULL COMMENT '表名',
  `table_comment` varchar(300) DEFAULT NULL COMMENT '表的描述',
  `table_rows` bigint(20) DEFAULT 0 COMMENT '表的行數',
  `column_key` varchar(100) NOT NULL COMMENT '表的主鍵',
  `create_time` datetime DEFAULT NULL COMMENT '創建時間',
  `update_time` datetime DEFAULT NULL COMMENT '更新時間',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_databasename_tablename` (`database_name`,`table_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='源資料庫表資訊';

根據表結構創建對應的物體類

import lombok.*;

import java.io.Serializable;

/**
 * <p>
 * 源資料庫表資訊
 * </p>
 *
 * @author fei.yang4
 * @since 2021-11-03
 */
@Data
@AllArgsConstructor
@EqualsAndHashCode(callSuper = false)
@ToString
@NoArgsConstructor
public class DatabaseInfo implements Serializable {

    /**
     * 資料庫名稱
     */
    private String databaseName;

    /**
     * 資料表全稱,格式：dbName.tableName
     */
    private String tableName;

    /**
     * 表的描述
     */
    private String tableComment;

    /**
     * 表的行數
     */
    private Long tableRows;

    /**
     * 表的主鍵
     */
    private String tableKey;
}

二、資料庫連接池Druid工具類

import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.beanutils.PropertyUtils;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JdbcUtils {
    private static DataSource ds = null;

    static {
        try {
            Properties props = new Properties();
            props.load(JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(props);
        } catch (Exception e) {
                throw new RuntimeException("讀取組態檔例外", e);
        }
    }

    //Druid連接池
    public static Connection getDruidConnection() throws Exception {
        Connection conn = null;
        conn = ds.getConnection();
        return conn;
    }


    public static void release(Connection conn) throws SQLException {
        if(conn != null) conn.close();
    }


    public static void release(Connection conn, PreparedStatement ps) throws SQLException {
        if(ps!=null) ps.close();
        if(conn!=null) conn.close();
    }

    public static void release(Connection conn, PreparedStatement ps, java.sql.ResultSet rs) throws SQLException {
        if(rs!=null) rs.close();
        if(ps!=null) ps.close();
        if(conn!=null) conn.close();
    }
}

三、DAO類

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public interface DAO<T> {

    /**
     * 獲取特殊值，如：最大值，總數等，
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public Object getValue(Connection conn,String sql,Object ... params) throws SQLException;

    /**
     * 查詢多個物件并存入List<T>中
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public List<T> getList(Connection conn,String sql,Object ... params) throws SQLException;

    /**
     * 查詢單個物件
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public T get(Connection conn,String sql,Object ... params) throws SQLException;

    /**
     * 考慮事務，通用的增刪改
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public int update(Connection conn,String sql,Object ... params) throws SQLException;
}

四、BaseDAO

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public abstract class BaseDAO<T> implements DAO<T> {

    Class<T> clazz = null;

    public BaseDAO(){
        //1.獲取帶范型父類的型別
        Type type = this.getClass().getGenericSuperclass();

        //2.引數化型別
        ParameterizedType pt = (ParameterizedType) type;

        //3.獲取真實引數
        Type[] types = pt.getActualTypeArguments();

        clazz = (Class<T>) types[0];
    }

    private QueryRunner qr = new QueryRunner();

    @Override
    public Object getValue(Connection conn, String sql, Object... params) throws SQLException {
        return qr.query(conn,sql,new ScalarHandler(),params);
    }

    @Override
    public List<T> getList(Connection conn, String sql,Object... params) throws SQLException {
        return qr.query(conn,sql,new BeanListHandler<>(clazz),params);
    }

    @Override
    public T get(Connection conn, String sql, Object... params) throws SQLException {
        return qr.query(conn,sql,new BeanHandler<>(clazz),params);
    }

    @Override
    public int update(Connection conn, String sql, Object... params) throws SQLException {
        return qr.update(conn,sql,params);
    }
}

五、DatabaseInfoDao

public class DatabaseInfoDao extends BaseDAO<DatabaseInfo> {
}

六、通用DAO測驗類

import com.bigdata.plus.Entity.DatabaseInfo;
import com.bigdata.plus.Entity.DatabaseInfoDao;
import org.junit.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class DAOTest {

    @Test
    public void test1() throws SQLException, IOException, ClassNotFoundException {
        Connection conn = null;
        try{
            conn = JdbcUtils.getConnection();
            DatabaseInfoDao databaseInfoDao = new DatabaseInfoDao();
            String sql = "select t.TABLE_SCHEMA as databaseName,CONCAT_WS('.',t.TABLE_SCHEMA,t" +
                    ".TABLE_NAME)" +
                    " as " +
                    "tableName,t.TABLE_COMMENT as tableComment,t.TABLE_ROWS as tableRows,k.COLUMN_NAME as " +
                    "tableKey\n" +
                    "from INFORMATION_SCHEMA.TABLES as t\n" +
                    "inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k\n" +
                    "on k.TABLE_NAME=t.TABLE_NAME and CONSTRAINT_NAME = 'PRIMARY' and t.TABLE_SCHEMA=?";
            Object[] params = {"dw"};
            DatabaseInfo databaseInfo = databaseInfoDao.get(conn, sql, params);
            System.out.println(databaseInfo);
        } catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,null,null);
        }
    }


    @Test
    public void test2() throws SQLException, IOException, ClassNotFoundException {
        Connection conn = null;
        try{
            conn = JdbcUtils.getConnection();
            DatabaseInfoDao databaseInfoDao = new DatabaseInfoDao();
            String sql = "select t.TABLE_SCHEMA as databaseName,CONCAT_WS('.',t.TABLE_SCHEMA,t" +
                    ".TABLE_NAME)" +
                    " as " +
                    "tableName,t.TABLE_COMMENT as tableComment,t.TABLE_ROWS as tableRows,k.COLUMN_NAME as " +
                    "tableKey\n" +
                    "from INFORMATION_SCHEMA.TABLES as t\n" +
                    "inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as k\n" +
                    "on k.TABLE_NAME=t.TABLE_NAME and CONSTRAINT_NAME = 'PRIMARY' and t.TABLE_SCHEMA=?";
            Object[] params = {"dw"};
            List<DatabaseInfo> list = databaseInfoDao.getList(conn, sql, params);
            for(DatabaseInfo databaseInfo : list){
                System.out.println(list);
            }
        } catch (SQLException e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,null,null);
        }
    }
}