多库多表动态查询dao

68 阅读 0 评论 45 点赞

我是靠谱客的博主魁梧紫菜，最近开发中收集的这篇文章主要介绍多库多表动态查询dao，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

多库多表动态查询dao

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* @author
* @ClassName: DynamicTableDao
* @Description:多库多表动态查询dao
* @date
* @Copyright
*/
public class DynamicTableDao {
private static final Logger LOGGER = LoggerFactory.getLogger(DynamicTableDao.class);
/**
* 根据sql查询动态表中的数据总数
*
* @param entitySql 要执行sql
* @param dbName
数据库名称
* @return 记录数
*/
public static Integer queryDataCount(String entitySql, String dbName) {
//获取连接池
DruidDataSource dataSource = DruidDataSourceUtils.getDataSource(BouleraiConstans.BUSINESS_DBNAME);
QueryRunner qr = new QueryRunner(dataSource);
//获取所有条数
Integer totalCount = 0;
try {
totalCount = ((Long) qr.query(entitySql, new ScalarHandler())).intValue();
} catch (SQLException e) {
LOGGER.error("sql查询失败! " + entitySql, e);
}
return totalCount;
}
/**
* 根据sql查询动态表中的分页数据列表
*
* @param entitySql 要执行sql
* @param dbName
数据库名称
* @return 分页数据列表
*/
public static List<Map<String, Object>> queryPageDataList(String entitySql, String dbName) {
//获取连接池
DruidDataSource dataSource = DruidDataSourceUtils.getDataSource(BouleraiConstans.BUSINESS_DBNAME);
QueryRunner qr = new QueryRunner(dataSource);
List<Map<String, Object>> dbList = null;
Connection connection = null;
try {
connection = dataSource.getConnection();
dbList = qr.query(connection, entitySql, new MapListHandler());
} catch (Exception ex) {
LOGGER.error("sql查询失败! " + entitySql, ex);
} finally {
try {
DbUtils.close(connection);
} catch (Exception e1) {
LOGGER.error("close
error! ", e1);
}
}
return dbList;
}
}


public class BouleraiConstans {
public
static volatile
Boolean
finishFinished=false;
/**
*
返回的一次性多少条记录
*/
public static final
int
PAGE_SIZE=100;
/**
*数据库
*/
public static final String BUSINESS_DBNAME = "eimos_business";
/**
*执行数据库删除
*/
public final static
String DB_ACTION_DELETE ="delete";
/**
*执行数据库更新
*/
public final static
String DB_ACTION_UPDATE ="update";
/**
*执行数据库新增
*/
public final static
String DB_ACTION_CREATE ="create";
/**
*客户端请求类型：option
*/
public final static
String REQUEST_TYPE_OPTION ="option";
/**
*客户端请求类型: tree
*/
public final static
String REQUEST_TYPE_TREE="tree";
/**
*客户端请求类型: others
*/
public final static
String REQUEST_TYPE_OTHERS="others";
/**
*顶层树id
*/
public final static
Long
BUSINESS_TREE_ROOT_ID =0L;
/**
* 返回所有data_type类型列表
*/
public final static
String ALL_DATA_TYPE_LIST_SQL="select bd_basic_data_type_id
as id,type_code,type_name
from bd_basic_data_type
where is_delete is false
";
public final static String DATE_LONG_FORMAT = "yyyy-MM-dd HH:mm:ss";
public final static String DATE_SHORT_FORMAT = "yyyy-MM-dd";
public final static String PG_TABLE_SCHEMAL_SQL = "yyyy-MM-dd";
}

数据源工具

import com.alibaba.druid.pool.DruidDataSource;
import com.boulderaitech.data.config.rules.entity.C3p0ConfigEntity;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.Connection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author
* @ClassName: DruidDataSourceUtils
* @Description: 数据源工具
* @date
* @Copyright
*/
public class DruidDataSourceUtils {
private final static Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceUtils.class);
/**
* 数据库连接池保存map： name:DruidDataSource
*/
private static Map<String, DruidDataSource> databaseMap = new HashMap<String, DruidDataSource>();
/**
* 根据配置初始化连接池
*
* @param dbconfigs 连接池配置
*/
public static Boolean initDbList(List<C3p0ConfigEntity> dbconfigs) {
if (dbconfigs == null || dbconfigs.isEmpty()) {
LOGGER.error("数据库连接池配置无效！请检查");
return false;
}
Boolean hasError = false;
//遍历获取连接池
for (C3p0ConfigEntity c3p0Config : dbconfigs) {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setName(c3p0Config.getDbName());
//dataSource.setDriverClassName(driverClassName);//如果不配置druid会根据url自动识别dbType，然后选择相应的driverClassName
dataSource.setUrl(c3p0Config.getJdbcUrl());
dataSource.setUsername(c3p0Config.getUserName());
dataSource.setPassword(c3p0Config.getPassword());
dataSource.setDriverClassName("org.postgresql.Driver");
dataSource.setMaxActive(30);
dataSource.setInitialSize(2);
// 配置获取连接等待超时的时间
dataSource.setMaxWait(10000);
dataSource.setMinIdle(2);
// 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
dataSource.setTimeBetweenEvictionRunsMillis(60000);
// 配置一个连接在池中最小生存的时间，单位是毫秒 超过这个时间每次会回收默认3个连接
dataSource.setMinEvictableIdleTimeMillis(30000);
// 线上配置的mysql断开闲置连接时间为1小时,数据源配置回收时间为3分钟,以最后一次活跃时间开始算
dataSource.setMaxEvictableIdleTimeMillis(180000);
// 连接最大存活时间，默认是-1(不限制物理连接时间)，从创建连接开始计算，如果超过该时间，则会被清理
dataSource.setPhyTimeoutMillis(15000);
dataSource.setValidationQuery("select 1");
dataSource.setTestWhileIdle(true);
dataSource.setTestOnBorrow(false);
dataSource.setTestOnReturn(false);
dataSource.setPoolPreparedStatements(true);
dataSource.setMaxOpenPreparedStatements(20);
dataSource.setUseGlobalDataSourceStat(true);
dataSource.setKeepAlive(true);
dataSource.setRemoveAbandoned(true);
dataSource.setRemoveAbandonedTimeout(180);
/**
* //用来检测连接是否有效
*/
dataSource.setValidationQuery(" select 1
");
try {
dataSource.init();
} catch (Exception e1) {
LOGGER.error("动态数据初始化失败: " + c3p0Config, e1);
hasError = true;
}
databaseMap.put(c3p0Config.getDbName(), dataSource);
}
return hasError;
}
/**
* 根据数据库名称获取连接池
*
* @param dbName 数据库名称
* @return 连接池
*/
public static DruidDataSource getDataSource(String dbName) {
DruidDataSource db = databaseMap.get(dbName);
return db;
}
/**
* 根据数据库名称获取连接
*
* @param dbName 数据库名称
* @return 数据库连接
*/
public static Connection getConnection(String dbName) {
DruidDataSource db = databaseMap.get(dbName);
Connection conn = null;
try {
conn = db.getConnection();
} catch (Exception ex) {
LOGGER.error("getConnection error!", ex);
}
return conn;
}
}

数据库连接池配置


import java.util.Date;
import java.util.Objects;
/**
* @ClassName: C3p0ConfigEntity
* @Description:数据库连接池配置
* @author
* @date
* @Copyright
*/
public class C3p0ConfigEntity extends BaseBean {
private static final long serialVersionUID = -2007678110412798920L;
/**
*
c3p0配置相关参数，不注释，自己区看c3p0东西
*/
private String dbName;
private String
driverClass;
private String jdbcUrl;
private String userName;
private String password;
private int initialPoolSize;
private int maxIdleTime;
private int maxPoolSize;
private int minPoolSize;
private int maxStatements;
private Boolean testConnectionOnCheckout=false;
private Boolean testConnectionOnCheckin=false;
private int acquireIncrement=3;
private Boolean autoCommitOnClose=false;
private int priority;
private int status;
private Date modifyTime;
private Boolean canDropOdpsTable=false;
@Override
public boolean equals(Object o) {
if (this == o) {
return true;
}
if (o == null || getClass() != o.getClass()) {
return false;
}
C3p0ConfigEntity that = (C3p0ConfigEntity) o;
return Objects.equals(dbName, that.dbName);
}
public Boolean getCanDropOdpsTable() {
return canDropOdpsTable;
}
public void setCanDropOdpsTable(Boolean canDropOdpsTable) {
this.canDropOdpsTable = canDropOdpsTable;
}
@Override
public int hashCode() {
return Objects.hash(dbName);
}
public String getDbName() {
return dbName;
}
public void setDbName(String dbName) {
this.dbName = dbName;
}
public String getDriverClass() {
return driverClass;
}
public void setDriverClass(String driverClass) {
this.driverClass = driverClass;
}
public String getJdbcUrl() {
return jdbcUrl;
}
public void setJdbcUrl(String jdbcUrl) {
this.jdbcUrl = jdbcUrl;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getInitialPoolSize() {
return initialPoolSize;
}
public void setInitialPoolSize(int initialPoolSize) {
this.initialPoolSize = initialPoolSize;
}
public int getMaxIdleTime() {
return maxIdleTime;
}
public void setMaxIdleTime(int maxIdleTime) {
this.maxIdleTime = maxIdleTime;
}
public int getMaxPoolSize() {
return maxPoolSize;
}
public void setMaxPoolSize(int maxPoolSize) {
this.maxPoolSize = maxPoolSize;
}
public int getMinPoolSize() {
return minPoolSize;
}
public void setMinPoolSize(int minPoolSize) {
this.minPoolSize = minPoolSize;
}
public int getMaxStatements() {
return maxStatements;
}
public void setMaxStatements(int maxStatements) {
this.maxStatements = maxStatements;
}
public Boolean getTestConnectionOnCheckout() {
return testConnectionOnCheckout;
}
public void setTestConnectionOnCheckout(Boolean testConnectionOnCheckout) {
this.testConnectionOnCheckout = testConnectionOnCheckout;
}
public Boolean getTestConnectionOnCheckin() {
return testConnectionOnCheckin;
}
public void setTestConnectionOnCheckin(Boolean testConnectionOnCheckin) {
this.testConnectionOnCheckin = testConnectionOnCheckin;
}
public int getAcquireIncrement() {
return acquireIncrement;
}
public void setAcquireIncrement(int acquireIncrement) {
this.acquireIncrement = acquireIncrement;
}
public Boolean getAutoCommitOnClose() {
return autoCommitOnClose;
}
public void setAutoCommitOnClose(Boolean autoCommitOnClose) {
this.autoCommitOnClose = autoCommitOnClose;
}
public int getPriority() {
return priority;
}
public void setPriority(int priority) {
this.priority = priority;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public Date getModifyTime() {
return modifyTime;
}
public void setModifyTime(Date modifyTime) {
this.modifyTime = modifyTime;
}
@Override
public String toString() {
return "C3p0ConfigEntity{" +
"dbName='" + dbName + ''' +
", driverClass='" + driverClass + ''' +
", jdbcUrl='" + jdbcUrl + ''' +
", userName='" + userName + ''' +
", password='" + password + ''' +
", initialPoolSize=" + initialPoolSize +
", maxIdleTime=" + maxIdleTime +
", maxPoolSize=" + maxPoolSize +
", minPoolSize=" + minPoolSize +
", maxStatements=" + maxStatements +
", testConnectionOnCheckout=" + testConnectionOnCheckout +
", testConnectionOnCheckin=" + testConnectionOnCheckin +
", acquireIncrement=" + acquireIncrement +
", autoCommitOnClose=" + autoCommitOnClose +
", priority=" + priority +
", status=" + status +
", modifyTime=" + modifyTime +
'}';
}
}