您现在的位置:首页 > >

SpringBoot_扩展数据库操作_DBUtils

发布时间:



SpringBoot_扩展数据库操作_DBUtils
依赖pom.xml配置类配置源码


依赖pom.xml


commons-dbutils
commons-dbutils


c3p0
c3p0
0.9.1.2


配置类

//pingruan.base.c3p0.data.use-ext-data=true
//pingruan.base.c3p0.data.driver-class=com.mysql.jdbc.Driver
//pingruan.base.c3p0.data.jdbc-url=jdbc:mysql://192.168.164.100:3306/manage-a?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
//pingruan.base.c3p0.data.user=root
//pingruan.base.c3p0.data.password=qwe123

配置源码

@Component
@ConfigurationProperties(prefix="pingruan.base.c3p0.data")
@Data
public class C3p0Properties {
private String useExtData = "false";
private String driverClass;
private String jdbcUrl;
private String user;
private String password;
private int initialPoolSize = 10;
private int minPoolSize = 5;
private int maxPoolSize = 20;
}
/**
* 获取数据库连接
*
* @author vander
* @date 2018年11月26日
*/
public class Db {

private static final QueryRunner runner = new QueryRunner();
private static ComboPooledDataSource ds = null;
private static ThreadLocal threadLocal = new ThreadLocal();

Db(C3p0Properties c3p0){
try {
ds = new ComboPooledDataSource();
ds.setDriverClass(c3p0.getDriverClass());
ds.setJdbcUrl(c3p0.getJdbcUrl());
ds.setUser(c3p0.getUser());
ds.setPassword(c3p0.getPassword());
ds.setInitialPoolSize(c3p0.getInitialPoolSize());
ds.setMinPoolSize(c3p0.getMinPoolSize());
ds.setMaxPoolSize(c3p0.getMaxPoolSize());
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}

/**
* 获取连接
*
* @return
* @throws SQLException
*/
private Connection getConnection() throws SQLException {
Connection conn = threadLocal.get();
if (null == conn) {
conn = getDataSource().getConnection();
threadLocal.set(conn);
}
return conn;
}

/**
* 开启事务
*/
private void startTransaction() {
try {
Connection conn = threadLocal.get();
if (null == conn) {
conn = getConnection();
threadLocal.set(conn);
}
conn.setAutoCommit(false);
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

/**
* 事务提交
*/
private void commit() {
try {
Connection conn = threadLocal.get();
if (conn != null) {
conn.commit();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

/**
* 事务回滚
*/
private void rollback() {
try {
Connection conn = threadLocal.get();
if (conn != null) {
conn.rollback();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

/**
* 释放连接
*/
private void close() {
try {
Connection conn = threadLocal.get();
if (conn != null) {
conn.close();
threadLocal.remove();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}

/**
* 获取数据源
*
* @return
*/
private DataSource getDataSource() {
return ds;
}


/**
* 查询(返回Array结果)
* @param sql
* @param params
* @return
*/
public Object[] queryArray(String sql, Object... params) {
Object[] result = null;
try {
result = runner.query(getConnection(), sql, new ArrayHandler(), params);
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return result;
}

/**
* 查询(返回ArrayList结果)
* @param sql
* @param params
* @return
*/
public List queryArrayList(String sql, Object... params) {
List result = null;
try {
result = runner.query(getConnection(), sql, new ArrayListHandler(), params);
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return result;
}

/**
* 查询(返回Map结果)
* @param sql
* @param params
* @return
*/
public Map queryMap(String sql, Object... params) {
Map result = null;
try {
result = runner.query(getConnection(), sql, new MapHandler(), params);
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return result;
}

/**
* 查询(返回MapList结果)
* @param sql
* @param params
* @return
*/
public List> queryMapList(String sql, Object... params) {
List> result = null;
try {
result = runner.query(getConnection(), sql, new MapListHandler(), params);
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return result;
}

/* 查询(返回Bean结果) */
public T queryBean(Class cls, Map map, String sql,
Object... params) {
T result = null;
try {
if (map != null) {
result = runner.query(getConnection(), sql, new BeanHandler(cls, new BasicRowProcessor(new BeanProcessor(map))),
params);
} else {
result = runner.query(getConnection(), sql, new BeanHandler(cls), params);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return result;
}

/**
* 查询(返回BeanList结果)
* @param cls
* @param map
* @param sql
* @param params
* @return
*/
public List queryBeanList(Class cls, Map map, String sql,
Object... params) {
List result = null;
try {
if (map != null) {
result = runner.query(getConnection(), sql,
new BeanListHandler(cls, new BasicRowProcessor(new BeanProcessor(map))), params);
} else {
result = runner.query(getConnection(), sql, new BeanListHandler(cls), params);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return result;
}

/**
* 查询指定列名的值(单条数据)
* @param column
* @param sql
* @param params
* @return
*/
public T queryColumn(String column, String sql, Object... params) {
T result = null;
try {
result = runner.query(getConnection(), sql, new ScalarHandler(column), params);
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return result;
}

/**
* 查询指定列名的值(多条数据)
* @param column
* @param sql
* @param params
* @return
*/
public List queryColumnList(String column, String sql, Object... params) {
List result = null;
try {
result = runner.query(getConnection(), sql, new ColumnListHandler(column), params);
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return result;
}

/**
* 查询指定列名对应的记录映射
* @param column
* @param sql
* @param params
* @return
*/
public Map> queryKeyMap(String column, String sql,
Object... params) {
Map> result = null;
try {
result = runner.query(getConnection(), sql, new KeyedHandler(column), params);
} catch (SQLException e) {
e.printStackTrace();
}finally {
close();
}
return result;
}

/**
* 更新(包括UPDATE、INSERT、DELETE,返回受影响的行数)
* @param sql
* @param params
* @return
*/
public int update( String sql, Object... params) {
int result = 0;
try {
startTransaction();
result = runner.update(getConnection(), sql, params);
commit();
} catch (SQLException e) {
rollback();
e.printStackTrace();
}finally {
close();
}
return result;
}
}

//@Autowired
//Db db;
/**
* 扩展数据源
*
*
* @author vander
* @date 2018年11月26日
*/
@Configuration
@ConditionalOnProperty(value="pingruan.base.c3p0.data.use-ext-data",havingValue="true")
public class DbConfig {

@Autowired
C3p0Properties c3p0Properties;

@Bean
public Db dbs() {
return new Db(c3p0Properties);
}

}


热文推荐
猜你喜欢
友情链接: 团党工作范文 工作范文 表格模版 社科文档网 营销文档资料 工程文档大全