多库多表动态查询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;
public class DynamicTableDao {
private static final Logger LOGGER = LoggerFactory.getLogger(DynamicTableDao.class);
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;
}
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";
public final static String REQUEST_TYPE_OPTION ="option";
public final static String REQUEST_TYPE_TREE="tree";
public final static String REQUEST_TYPE_OTHERS="others";
public final static Long BUSINESS_TREE_ROOT_ID =0L;
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;
public class DruidDataSourceUtils {
private final static Logger LOGGER = LoggerFactory.getLogger(DruidDataSourceUtils.class);
private static Map<String, DruidDataSource> databaseMap = new HashMap<String, DruidDataSource>();
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.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);
dataSource.setMinEvictableIdleTimeMillis(30000);
dataSource.setMaxEvictableIdleTimeMillis(180000);
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;
}
public static DruidDataSource getDataSource(String dbName) {
DruidDataSource db = databaseMap.get(dbName);
return db;
}
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;
public class C3p0ConfigEntity extends BaseBean {
private static final long serialVersionUID = -2007678110412798920L;
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 +
'}';
}
}