Java实现关系型数据库工具类JdbcUtils系列九：通用DAO

Java实现关系型数据库工具类JdbcUtils系列九：通用DAO

• 一、创建对应数据库表的实体类
• 二、数据库连接池Druid工具类
• 三、DAO类
• 四、BaseDAO
• 五、DatabaseInfoDao
• 六、通用DAO测试类

一、创建对应数据库表的实体类

数据库表结构

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);
        }
    }
}