import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.*;
import java.text.MessageFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
public class DBUtil {
private static final Logger LOG = LoggerFactory.getLogger(DBUtil.class);
/**
* 数据库链接对象
*/
public static class Client{
private String driver;
private String jdbcUrl;
private String username;
private String password;
public String getDriver() {
return driver;
}
public void setDriver(String driver) {
this.driver = driver;
}
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 static class Result{
//是否成功执行
private boolean isSuccess = true;
//执行sql
private String sql = "";
//错误信息
private String errorMessage = "";
//列信息(列名称-列类型)
private Map<String,String> columnInfo;
//返回的数据集-List-Map
private List<Map<String, Object>> data;
public boolean isSuccess() {
return isSuccess;
}
public void setSuccess(boolean success) {
isSuccess = success;
}
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
public String getErrorMessage() {
return errorMessage;
}
public void setErrorMessage(String errorMessage) {
this.errorMessage = errorMessage;
}
public Map<String, String> getColumnInfo() {
return columnInfo;
}
public void setColumnInfo(Map<String, String> columnInfo) {
this.columnInfo = columnInfo;
}
public List<Map<String, Object>> getData() {
return data;
}
public void setData(List<Map<String, Object>> data) {
this.data = data;
}
}
/**
* 获取连接对象
* @param client
* @return
*/
public static Connection getConnect(Client client){
Connection conn = null;
try{
Class.forName(client.getDriver());
conn = DriverManager.getConnection(client.getJdbcUrl(),client.getUsername(),client.getPassword());
}catch (Exception ex){
LOG.error("数据库连接失败!",ex);
}
return conn;
}
/**
* 格式化结果集
* @param resultSet
* @return List<Map<String, Object>>
*/
private static Map<String,Object> resultFormat(ResultSet resultSet){
Map<String,Object> retData = new HashMap<>();
ResultSetMetaData md = null;
try {
//捕获异常判断当前执行语句是否存在返回值
try{
md = resultSet.getMetaData();
}catch (NullPointerException ex){
return retData;
}
int columnCount = md.getColumnCount(); //列数
Map<String,String> columnInfo = new HashMap<>();
for (int i = 1;i<=columnCount;i++){
columnInfo.put(md.getColumnName(i),md.getColumnTypeName(i));
}
retData.put("columnInfo",columnInfo);
List<Map<String, Object>> mapData = new ArrayList<>();
//历遍结果数据
while (resultSet.next()) {
Map<String, Object> rowData = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
rowData.put(md.getColumnName(i).toUpperCase(), resultSet.getObject(i));
}
mapData.add(rowData);
}
retData.put("mapData",mapData);
} catch (Exception e){
LOG.error("SQL结果集合格式化失败!",e);
}
return retData;
}
/**
* 执行sql语句
* @param connection
* @param sql
* @return
*/
public static Result exec(Connection connection, String sql) {
Result result = new Result();
result.setSql(sql);
PreparedStatement ps = null;
ResultSet ret = null;
try {
ps = connection.prepareStatement(sql);
ret = ps.executeQuery();
Map<String,Object> retFormatObject = resultFormat(ret);
result.setColumnInfo((Map<String, String>) retFormatObject.get("columnInfo")); //列信息
result.setData((List<Map<String, Object>>) retFormatObject.get("mapData")); //行数据
} catch (Exception e) {
result.setSuccess(false);
result.setErrorMessage(Method.getExceptionToString(e));
LOG.error("SQL执行失败:"+result.getSql(),e);
} finally {
if (ret != null){
try {
ret.close();
} catch (Exception e) {
//e.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (Exception e) {
//e.printStackTrace();
}
}
}
return result;
}
/**
* 插入数据
* @param connection
* @param tableName:插入表名称,全名称包含数据库名
* @param object:插入的数据对象
*/
public static Boolean insert(Connection connection ,String tableName,Object object){
PreparedStatement preparedStatement = null;
Boolean flag = true;
try {
connection.setAutoCommit(false); //取消自动提交
String sql = "INSERT INTO {0}(\"{1}\") VALUES({2})";
//匹配传入参数对象类型
if (object instanceof List){
//List类型
List<Object> addListData = (List<Object>) object;
if (addListData.get(0) instanceof Map){
//List-Map嵌套多行
List<Map<String,Object>> listMapData = (List<Map<String, Object>>) object;
List<String> columnName = new ArrayList<>(listMapData.get(0).keySet());
String col = String.join("\",\"", columnName); //列拼接
String placeholder = String.join(",", Collections.nCopies(columnName.size(), "?")); //占位符
String addSql = MessageFormat.format(sql, tableName, col, placeholder);
LOG.info("INSERT SQL:"+addSql);
preparedStatement = connection.prepareStatement(addSql);
for (int i = 0; i < listMapData.size(); i++) {
for (int k = 0; k< columnName.size();k++){
// 从1开始计数
preparedStatement.setObject(k+1,listMapData.get(i).get(columnName.get(k)));
}
preparedStatement.addBatch();
// 一万行执行一次批量操作
if ((i+1)%10000 == 0){
preparedStatement.executeBatch();
preparedStatement.clearBatch();
connection.commit();
}
}
}else {
LOG.error("不支持插入当前对象类型!");
return false;
}
}else if (object instanceof Map){
//Map类型(单行记录)
Map<String,Object> onceMapData = (Map<String, Object>) object;
List<String> columnName = new ArrayList<>(onceMapData.keySet());
String col = String.join("\",\"", columnName); //列拼接
String placeholder = String.join(",", Collections.nCopies(columnName.size(), "?")); //占位符
String addSql = MessageFormat.format(sql, tableName, col, placeholder);
LOG.info("INSERT SQL:"+addSql);
preparedStatement = connection.prepareStatement(addSql);
for (int k = 0; k< columnName.size();k++){
// 从1开始计数
preparedStatement.setObject(k+1,onceMapData.get(columnName.get(k)));
}
preparedStatement.addBatch();
}else{
LOG.error("不支持插入当前对象类型!");
return false;
}
preparedStatement.executeBatch();
connection.commit();
LOG.info("数据插入完成!");
}catch (Exception e){
LOG.error("数据插入失败!",e);
try {
connection.rollback(); //回滚
} catch (SQLException throwables) {
//throwables.printStackTrace();
}
flag = false;
}finally {
if (preparedStatement != null);{
try {
preparedStatement.close();
} catch (SQLException throwables) {
//throwables.printStackTrace();
}
}
}
return flag;
}
/**
* 释放资源连接
* @param connection Connection
*/
public static void releaseResources(Connection connection){
if (connection != null){
try {
connection.close();
} catch (Exception e) {
//e.printStackTrace();
}
}
}
public static void main(String[] args) {
Client client = new Client();
client.setDriver("ru.yandex.clickhouse.ClickHouseDriver");
//client.setDriver("com.github.housepower.jdbc.ClickHouseDriver");
client.setJdbcUrl("jdbc:clickhouse://127.0.0.1:8123");
client.setUsername("default");
client.setPassword("123456");
Connection conn = getConnect(client);
Result result = exec(conn,"SELECT * FROM DATASOURCE.SYNC_LOG_BAK LIMIT 5");
System.out.println(result.getColumnInfo());
List<String> columnName = new ArrayList<>(result.getColumnInfo().keySet());
//第一种插入方式(单行Map)
Map<String,Object> onceMapData = new HashMap<>();
onceMapData.put("BATCH_ID","111222333");
onceMapData.put("STATUS","onceMapData");
onceMapData.put("READ_RECORD","1234");
onceMapData.put("END_DATE",LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
insert(conn,"DATASOURCE.SYNC_LOG_BAK",onceMapData);
//第二种插入方式(List嵌套Map)
List<Map<String,Object>> listMapData = new ArrayList<>();
onceMapData.put("STATUS","listMapData");
listMapData.add(onceMapData);
insert(conn,"DATASOURCE.SYNC_LOG_BAK",listMapData);
releaseResources(conn);
}
}```
数据库JDBC操作类封装
于 2022-11-18 09:14:54 首次发布