需要注意的是
execute()既能执行修改表中数据,又可以执行创建表,删除表,创建数据库,删除数据库等操作 excuteUpdate()针对于修改更新表中的数据,返回int类型的值,存储修改的行数
使用代码
package com.test;
/**
* @Author:jeff
* @Date: 2022/11/16
* @Desc
*/
public class Test {
public static void main(String[] args) throws Exception {
DruidDemo druidDemo=new DruidDemo();
druidDemo.executeFuncAndSql("select * from alert_dataset limit 1");
}
}
普通工具类
package com.test;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.google.protobuf.ServiceException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
/**
* @Author:jeff
* @Date: 2022/11/16
* @Desc
*/
class DruidDemo {
private static final Logger logger = LoggerFactory.getLogger(DruidDemo.class);
private DataSource ds;
private static final int LIMIT = 10000;
private int taskTimeout=20000;
public DruidDemo() throws Exception {
Properties pro = new Properties();
InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("jdbc.properties");
pro.load(is);
//获取连接池对象
ds = DruidDataSourceFactory.createDataSource(pro);
if (ds == null) {
throw new Exception("数据源不存在!");
}
}
public void executeFuncAndSql(String mainSql){
Connection connection = null;
PreparedStatement stmt = null;
ResultSet resultSet = null;
try {
// create connection
connection = ds.getConnection();
// pre sql
stmt = prepareStatementAndBind(connection, mainSql);
//查询
resultSet = stmt.executeQuery();
resultProcess(resultSet);
// 删除
// int i= stmt.executeUpdate();
} catch (Exception e) {
logger.error("execute sql error",e);
throw new RuntimeException("execute sql error");
} finally {
close(resultSet,stmt,connection);
}
}
/**
* preparedStatement bind
* @param connection
* @return
* @throws Exception
*/
private PreparedStatement prepareStatementAndBind(Connection connection, String mainSql) throws Exception {
// is the timeout set
PreparedStatement stmt = connection.prepareStatement(mainSql);
if(0!=taskTimeout){
stmt.setQueryTimeout(taskTimeout);
}
logger.info("prepare statement replace sql : {} ", stmt);
return stmt;
}
/**
* result process
*
* @param resultSet resultSet
* @throws Exception
*/
private void resultProcess(ResultSet resultSet) throws Exception{
List<Map<String,Object>> resultMapList = new ArrayList<>();
ResultSetMetaData md = resultSet.getMetaData();
int num = md.getColumnCount();
int rowCount = 0;
while (rowCount < LIMIT && resultSet.next()) {
Map<String,Object> mapOfColValues = new HashMap<>();
for (int i = 1; i <= num; i++) {
mapOfColValues.put(md.getColumnName(i), resultSet.getObject(i));
}
resultMapList.add(mapOfColValues);
rowCount++;
}
System.out.println("execute sql : {}"+JSONObject.toJSONString(resultMapList, SerializerFeature.WriteMapNullValue));
}
/**
* close jdbc resource
*
* @param resultSet resultSet
* @param pstmt pstmt
* @param connection connection
*/
private void close(ResultSet resultSet,
PreparedStatement pstmt,
Connection connection){
if (resultSet != null){
try {
connection.close();
} catch (SQLException e) {
}
}
if (pstmt != null){
try {
connection.close();
} catch (SQLException e) {
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
}
}
}
}
引入阿里巴巴连接池
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<druid.version>1.0.9</druid.version>
<mysql.version>8.0.28</mysql.version>
<log4j2.version>2.16.0</log4j2.version>
<fastjson.version>1.2.79</fastjson.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<!-- MySql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<!-- 连接池 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>${druid.version}</version>
</dependency>
<!-- Log4j2 门面API-->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.14.1</version>
</dependency>
<!-- Log4j2 日志实现 -->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.14.1</version>
</dependency>
<!--使用slf4j作为日志的门面,使用log4j2来记录日志 -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.30</version>
</dependency>
<!--为slf4j绑定日志实现 log4j2的适配器 -->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-slf4j-impl</artifactId>
<version>2.12.1</version>
</dependency>
<!-- 阿里JSON解析器 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>${fastjson.version}</version>
</dependency>
</dependencies>
配置文件 jdbc.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/gwm_alert?characterEncoding=utf8&useOldAliasMetadataBehavior=true&tinyInt1isBit=false
username=root
password=123456
maxActive=50
initialSize=5
minIdle=1
maxWait=60000
timeBetweenEvictionRunsMillis=30000
minEvictableIdleTimeMillis=300000
testWhileIdle=true
testOnBorrow=false
testOnReturn=false
filters=stat,wall