package cn.nordrassil.test2.util;
import org.apache.commons.dbcp2.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;
/**
* @Auther: liyue
* @Date: 2020/6/19 15:48
* @Description:
*/
public class JdbcUtil {
/**
* 传递给JDBC驱动的用于建立连接的URL
*/
private final static String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&useSSL=false";
/**
* 使用的JDBC驱动的完整有效的Java 类名
*/
private final static String driverClassName = "com.mysql.jdbc.Driver";
/**
* 传递给JDBC驱动的用于建立连接的用户名
*/
private final static String username = "root";
/**
* 传递给JDBC驱动的用于建立连接的密码
*/
private final static String password = "123";
/**
* 初始化连接:连接池启动时创建的初始化连接数量,1.2版本后支持
*/
private final static Integer initialSize = 4;
/**
* 最大活动连接数
* 可以在这个池中同一时刻被分配的有效连接数的最大值,如设置为负数,则不限制
*/
private final static Integer maxTotal = 30;
/**
* 最大空闲连接:连接池中容许保持空闲状态的最大连接数量,超过的空闲连接将被释放,
* 如果设置为负数表示不限制
*/
private final static Integer maxIdle = 10;
/**
* 最小空闲连接:连接池中容许保持空闲状态的最小连接数量,低于这个数量将创建新的连接,
* 如果设置为0则不创建
*/
private final static Integer minIdle = 5;
/**
* 从连接池获取一个连接时,最大的等待时间
* 设置为-1时,如果没有可用连接,连接池会一直无限期等待,直到获取到连接为止。
* 如果设置为N(毫秒),则连接池会等待N毫秒,等待不到,则抛出异常。
*/
private final static Integer maxWaitMillis = 1000;
/**
* 是否在从池中取出连接前进行检验,如果检验失败,
* 则从池中去除连接并尝试取出另一个.
*/
private final static String testOnBorrow = "true";
/**
* SQL查询,用来验证从连接池取出的连接,在将连接返回给调用者之前.如果指定,
* 则查询必须是一个SQL SELECT并且必须返回至少一行记录
*/
private final static String validationQuery = "select 1";
/**
* 获取连接
*
* @return
*/
public static Connection getConn() throws Exception {
Connection conn = null;
try {
Class.forName(driverClassName);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 获取连接池
*
* @return
*/
public static DataSource getDataSourceInstance() throws Exception {
Properties properties = new Properties();
properties.put("driverClassName", driverClassName);
properties.put("url", url);
properties.put("username", username);
properties.put("password", password);
properties.put("initialSize", initialSize);
properties.put("maxTotal", maxTotal);
properties.put("maxIdle", maxIdle);
properties.put("minIdle", minIdle);
properties.put("maxWaitMillis", maxWaitMillis);
properties.put("testOnBorrow", testOnBorrow);
properties.put("validationQuery", validationQuery);
//创建dataSource对象
DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
return dataSource;
}
/**
* 获取建表语句
*
* @param conn
* @param table
* @return
*/
public static String showCreateTableSql(Connection conn, String table) throws Exception {
ResultSet rs = null;
PreparedStatement ps = null;
try {
long t1 = System.currentTimeMillis();
String sql = "SHOW CREATE TABLE " + table;
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
String str = null;
while (rs.next()) {
String string = rs.getString(2);
if (string != null)
str = string;
}
return str;
} 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 {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 通用查询
*
* @param sql
* @param conn
* @return
* @throws Exception
*/
public static List<List<String>> query(String sql, Connection conn, Object... params) throws Exception {
List<List<String>> lists = new LinkedList<>();
ResultSet rs = null;
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
List<String> titles = new LinkedList<>();
boolean b = Boolean.TRUE;
while (rs.next()) {
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
List<String> list = new LinkedList<>();
for (int i = 1; i <= columnCount; i++) {
if (b) {
titles.add(rsmd.getColumnName(i));
}
list.add(rs.getString(i));
}
b = Boolean.FALSE;
lists.add(list);
}
List<List<String>> result = new ArrayList<>();
result.add(titles);
result.addAll(lists);
return result;
} 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 {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 查询返回第一行第一列数值
*
* @param conn
* @param sql
* @param params
* @return
*/
public static String selectOneField(Connection conn, String sql, Object... params) throws Exception {
ResultSet rs = null;
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
String result = null;
while (rs.next()) {
result = rs.getString(1);
break;
}
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 {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 执行SQL,返回影响行数
*
* @param conn
* @param sql
* @param params
* @return
* @throws SQLException
*/
public static int execute(Connection conn, String sql, Object... params) throws SQLException {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
return ps.executeUpdate();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 统计数量
*
* @param conn
* @param sql
* @param params
* @return
*/
public static int count(Connection conn, String sql, Object... params) throws Exception {
ResultSet rs = null;
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i, params[i]);
}
rs = ps.executeQuery();
int result = 0;
while (rs.next()) {
result = rs.getInt(1);
}
return result;
} 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 {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 返回查询结果第一列
*
* @param conn
* @param sql
* @param params
* @return
*/
public static List<String> selectListOneField(Connection conn, String sql, Object... params) throws Exception {
ResultSet rs = null;
PreparedStatement ps = null;
try {
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));
}
return result;
} 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 {
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
}
依赖
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.6.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.3.1</version>
</dependency>
END。