现在的数据层的开发，大多会使用如MyBatis或JPA之类的开发工具。这些开发工具给我们的开发过程中带来了极大的便利。
但是在一些极端的场景下往往原生的jdbc方式操作数据库更灵活，性能更高。由于部分场景下MyBatis或JPA之类无法满足我的需求，所以我打算自己封装一套查数据库的工具类。
我们会用到fastjson,druid,mysql所以pom.xml增加依赖如下：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.62</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.9</version>
</dependency>
<!-- Mysql驱动包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
以下是核心代码部分
package cn.ycmit.jdbcutil.util;
import cn.ycmit.jdbcutil.domain.DataSource;
import cn.ycmit.jdbcutil.dto.ResponseDto;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
@Slf4j
public class JdbcUtil {
/**
* 执行sql并返回结果
*
* @param datasource 数据源连接
* @param sql 语句
*/
public static ResponseDto executeSql(DataSource datasource, String sql) {
return executeSql(datasource,sql,new ArrayList<Object>());
}
/**
* 执行sql并返回结果
*
* @param datasource 数据源连接
* @param sql 语句
* @param jdbcParamValues
*/
public static ResponseDto executeSql(DataSource datasource, String sql, List<Object> jdbcParamValues) {
log.debug(sql);
log.debug(JSON.toJSONString(jdbcParamValues));
DruidPooledConnection connection = null;
try {
connection = PoolManager.getPooledConnection(datasource);
PreparedStatement statement = connection.prepareStatement(sql);
for (int i = 1; i <= jdbcParamValues.size(); i++) {
statement.setObject(i, jdbcParamValues.get(i - 1));
}
boolean hasResultSet = statement.execute();
if (hasResultSet) {
ResultSet rs = statement.getResultSet();
int columnCount = rs.getMetaData().getColumnCount();
List<String> columns = new ArrayList<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = rs.getMetaData().getColumnLabel(i);
columns.add(columnName);
}
List<JSONObject> list = new ArrayList<>();
while (rs.next()) {
JSONObject jo = new JSONObject();
columns.stream().forEach(t -> {
try {
Object value = rs.getObject(t);
jo.put(t, value);
} catch (SQLException e) {
e.printStackTrace();
}
});
list.add(jo);
}
return ResponseDto.apiSuccess(list);
} else {
int updateCount = statement.getUpdateCount();
return ResponseDto.apiSuccess("sql修改数据行数：" + updateCount);
}
} catch (Exception e) {
e.printStackTrace();
return ResponseDto.fail(e.getMessage());
} finally {
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
jdbcutil/src/main/java/cn/ycmit/jdbcutil/util/PoolManager.java
代码如下：
package cn.ycmit.jdbcutil.util;
import cn.ycmit.jdbcutil.domain.DataSource;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import lombok.extern.slf4j.Slf4j;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
/**
* 数据库连接池工具类
*/
@Slf4j
public class PoolManager {
private static Lock lock = new ReentrantLock();
private static Lock deleteLock = new ReentrantLock();
//所有数据源的连接池存在map里
static Map<String, DruidDataSource> map = new HashMap<>();
public static DruidDataSource getJdbcConnectionPool(DataSource ds) {
if (map.containsKey(ds.getId())) {
return map.get(ds.getId());
} else {
lock.lock();
try {
log.info(Thread.currentThread().getName() + "获取锁");
if (!map.containsKey(ds.getId())) {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setName(ds.getName());
druidDataSource.setUrl(ds.getUrl());
druidDataSource.setUsername(ds.getUsername());
druidDataSource.setPassword(ds.getPassword());
druidDataSource.setDriverClassName(ds.getDriver());
druidDataSource.setConnectionErrorRetryAttempts(3); //失败后重连次数
druidDataSource.setBreakAfterAcquireFailure(true);
map.put(ds.getId(), druidDataSource);
log.info("创建Druid连接池成功：{}", ds.getName());
}
return map.get(ds.getId());
} catch (Exception e) {
return null;
} finally {
lock.unlock();
}
}
}
//删除数据库连接池
public static void removeJdbcConnectionPool(String id) {
deleteLock.lock();
try {
DruidDataSource druidDataSource = map.get(id);
if (druidDataSource != null) {
druidDataSource.close();
map.remove(id);
}
} catch (Exception e) {
log.error(e.toString());
} finally {
deleteLock.unlock();
}
}
public static DruidPooledConnection getPooledConnection(DataSource ds) throws SQLException {
DruidDataSource pool = PoolManager.getJdbcConnectionPool(ds);
DruidPooledConnection connection = pool.getConnection();
// log.info("获取连接成功");
return connection;
}
}
jdbcutil/src/main/java/cn/ycmit/jdbcutil/dto/ResponseDto.java
代码如下：
package cn.ycmit.jdbcutil.dto;
import lombok.Data;
/**
* 返回值包装类
*/
@Data
public class ResponseDto {
String msg;
Object data;
boolean success;
public static ResponseDto apiSuccess(Object data) {
ResponseDto dto = new ResponseDto();
dto.setData(data);
dto.setSuccess(true);
dto.setMsg("接口访问成功");
return dto;
}
public static ResponseDto successWithMsg(String msg) {
ResponseDto dto = new ResponseDto();
dto.setData(null);
dto.setSuccess(true);
dto.setMsg(msg);
return dto;
}
public static ResponseDto successWithData(Object data) {
ResponseDto dto = new ResponseDto();
dto.setData(data);
dto.setSuccess(true);
return dto;
}
public static ResponseDto fail(String msg) {
ResponseDto dto = new ResponseDto();
dto.setSuccess(false);
dto.setMsg(msg);
return dto;
}
}
jdbcutil/src/main/java/cn/ycmit/jdbcutil/domain/DataSource.java
代码如下：
package cn.ycmit.jdbcutil.domain;
import lombok.Data;
/**
* 数据源实体类
*
*/
@Data
public class DataSource {
String id;
String name;
String url;
String username;
String password;
String driver;
}
jdbcutil/src/main/java/cn/ycmit/jdbcutil/controller/JdbcDemoController.java
package cn.ycmit.jdbcutil.controller;
import cn.ycmit.jdbcutil.domain.DataSource;
import cn.ycmit.jdbcutil.dto.ResponseDto;
import cn.ycmit.jdbcutil.util.JdbcUtil;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.ArrayList;
import java.util.List;
@RestController
public class JdbcDemoController {
public static DataSource ds = new DataSource();
static {
//配置数据源
ds.setId("1");
ds.setName("mysql");
ds.setUrl("jdbc:mysql://172.16.184.23:3307/dbapi?useSSL=false&characterEncoding=UTF-8&serverTimezone=GMT%2B8");
ds.setUsername("root");
ds.setPassword("itkey123456");
ds.setDriver("com.mysql.cj.jdbc.Driver");
}
/**
* 查询测试
* @return
*/
@RequestMapping("/api/list")
public ResponseDto queryList()
{
String sql = "show tables";
ResponseDto responseDto = JdbcUtil.executeSql(ds,sql);
return responseDto;
}
}
https://download.csdn.net/download/lxyoucan/85094574
https://github.com/freakchick/DBApi