参考 : https://blog.csdn.net/qq_21137441/article/details/78781239#commentBox
Service
import java.sql.Connection;
import java.util.List;
/**
* @Auther: liyue
* @Date: 2019/1/17 14:25
* @Description:
*/
public interface JdbcService {
Connection getConn();
/**
* 查询
*
* @param sql
* @param T
* @param params
* @param <T>
* @return
*/
<T> List<T> select(String sql, Class<T> T, Object... params);
/**
* 查询一个
*
* @param sql
* @param T
* @param params
* @param <T>
* @return
*/
<T> T selectOne(String sql, Class<T> T, Object... params);
/**
* 查询一行一个字段
*
* @param sql
* @param params
* @return
*/
String selectOneField(String sql, Object... params);
/**
* 查询一列
*
* @param sql
* @param params
* @return
*/
List<String> selectListOneField(String sql, Object... params);
/**
* insert 或者 updte
*
* @param sql
* @param params
* @return
*/
int execute(String sql, Object... params);
/**
* 计算总记录数
*
* @param sql
* @param params
* @return
*/
int count(String sql, Object... params);
}
Impl
import com.aliyun.et.industry.pangang.biz.service.JdbcService;
import com.aliyun.et.industry.pangang.common.util.DateUtil;
import com.aliyun.et.industry.pangang.common.util.ResulstToObjectUtil;
import com.google.common.util.concurrent.ThreadFactoryBuilder;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
/**
* @Auther: liyue
* @Date: 2019/4/17 10:59
* @Description:
*/
@Service
public class JdbcServiceImpl implements JdbcService {
private static final Logger LOGGER = LoggerFactory.getLogger(JdbcServiceImpl.class);
/**
* sql执行时间上限记录sql
*/
private Long slow_sql_log_time_consume = 2000L;
private ExecutorService executorService = new ThreadPoolExecutor(5, 10,
3000, TimeUnit.MILLISECONDS, new LinkedBlockingDeque<>(10), new ThreadFactoryBuilder()
.setNameFormat("CoonPoolTuoliuServiceImpl-%d").build());
private void close(Connection conn) {
try {
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
private static Properties properties = null;
private static DataSource dataSource = null;
private static Connection connection = null;
static {
try {
//获取配置文件,转换成流
String propertiesPath = "db.properties";
InputStream in = null;
try {
URL url = CoonPoolDefectServiceImpl.class.getClassLoader().getResource(propertiesPath);
File file = new File(url.getFile());
in = new FileInputStream(file);
} catch (Exception e) {
}
if (in == null) {
ClassPathResource classPathResource = new ClassPathResource(propertiesPath);
in = classPathResource.getInputStream();
}
//创建properties对象
properties = new Properties();
//加载流
properties.load(in);
//创建dataSource对象
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getCoon() {
try {
//得到connection对象
connection = dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
@Override
public Connection getConn() {
return getCoon();
}
@Override
public <T> List<T> select(String sql, Class<T> T, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
Connection conn = null;
try {
long t1 = System.currentTimeMillis();
conn = getCoon();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
List<T> list = new LinkedList<>();
rs = ps.executeQuery();
while (rs.next()) {
T t = ResulstToObjectUtil.rsToObject(rs, T);
list.add(t);
}
long t2 = System.currentTimeMillis();
recordSlowSql(sql, t1, t2);
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
close(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
@Override
public <T> T selectOne(String sql, Class<T> T, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
Connection conn = null;
try {
long t1 = System.currentTimeMillis();
conn = getCoon();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
T t = null;
while (rs.next()) {
t = ResulstToObjectUtil.rsToObject(rs, T);
break;
}
long t2 = System.currentTimeMillis();
recordSlowSql(sql, t1, t2);
return t;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
close(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
@Override
public String selectOneField(String sql, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
Connection conn = null;
try {
conn = getCoon();
long t1 = System.currentTimeMillis();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
String result = rs.getString(1);
long t2 = System.currentTimeMillis();
recordSlowSql(sql, t1, t2);
return result;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
close(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
@Override
public int count(String sql, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
Connection conn = null;
try {
conn = getCoon();
long t1 = System.currentTimeMillis();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
int result = rs.getInt(1);
long t2 = System.currentTimeMillis();
recordSlowSql(sql, t1, t2);
return result;
} catch (SQLException e) {
e.printStackTrace();
return 0;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
close(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
@Override
public List<String> selectListOneField(String sql, Object... params) {
ResultSet rs = null;
PreparedStatement ps = null;
Connection conn = null;
try {
conn = getCoon();
long t1 = System.currentTimeMillis();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
List<String> result = new LinkedList<>();
while (rs.next()) {
result.add(rs.getString(1));
}
long t2 = System.currentTimeMillis();
recordSlowSql(sql, t1, t2);
return result;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
close(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
@Override
public int execute(String sql, Object... params) {
PreparedStatement ps = null;
Connection conn = null;
try {
conn = getCoon();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
return 0;
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
close(conn);
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
private void recordSlowSql(String sql, long t1, long t2) {
String date = DateUtil.getNowYmdhms();
long timeConsume = t2 - t1;
if (timeConsume > slow_sql_log_time_consume) {
executorService.submit(new Runnable() {
@Override
public void run() {
String insert = "\n" +
"insert into `cold_slow_sql_log` (`gmt_create`,`gmt_modify`,`sql`,`time_consume`,`db`)\n" +
" values('" + date + "','" + date + "','" + sql.replaceAll("'", "\"") + "'," + timeConsume + ",'mysql')\n";
execute(insert);
}
});
}
}
}
dbcp配置 ,db.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/defect
username=root
password=root
initialSize=30
maxTotal=30
maxIdle=10
minIdle=5
maxWaitMillis=1000
testOnBorrow=true
validationQuery=select 1
慢sql日志表
CREATE TABLE `cold_slow_sql_log` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`gmt_create` datetime NOT NULL COMMENT '创建时间',
`gmt_modify` datetime DEFAULT NULL COMMENT '修改时间',
`sql` varchar(2000) DEFAULT NULL COMMENT 'SQL',
`time_consume` bigint(20) DEFAULT NULL COMMENT '耗时(单位:毫秒)',
`db` varchar(32) DEFAULT NULL COMMENT '数据库',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=466 DEFAULT CHARSET=utf8
dbcp依赖
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-dbcp2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.6.0</version>
</dependency>
线程池依赖
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>18.0</version>
</dependency>
工具类 ResulstToObjectUtil
https://blog.csdn.net/Lxinccode/article/details/84881943
dbcp连接池详细
https://blog.csdn.net/Lxinccode/article/details/89637178
END。