获取连接工具类 v1
package com.binxin.api.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class JdbcUtils {
private static DataSource dataSource = null;
static {
Properties properties = new Properties();
InputStream ips = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
try {
properties.load(ips);
} catch (IOException e) {
throw new RuntimeException(e);
}
try {
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection connection() throws SQLException {
return dataSource.getConnection();
}
public static void freeConnection(Connection connection) throws SQLException {
connection.close();
}
}
package com.binxin.api.utils;
import java.sql.Connection;
import java.sql.SQLException;
public class JdbcCurdPart {
public void testInsert() throws SQLException {
Connection connection=JdbcUtils.connection();
JdbcUtils.freeConnection(connection);
}
}
获取连接工具类 v2
- 同一个线程不同方法获取同一个链接
package com.binxin.api.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
public class JdbcUtilsV2 {
private static DataSource dataSource = null;
private static ThreadLocal<Connection> tl = new ThreadLocal<>();
static {
Properties properties = new Properties();
InputStream ips = JdbcUtilsV2.class.getClassLoader().getResourceAsStream("druid.properties");
try {
properties.load(ips);
} catch (IOException e) {
throw new RuntimeException(e);
}
try {
dataSource = DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection connection() throws SQLException {
Connection connection = tl.get();
if (connection == null) {
connection = dataSource.getConnection();
tl.set(connection);
}
return connection;
}
public static void freeConnection() throws SQLException {
Connection connection = tl.get();
if (connection != null) {
tl.remove();
connection.setAutoCommit(true);
connection.close();
}
}
}
高级应用封装 BaseDao
package com.binxin.api.utils;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class BaseDao {
public int excuteUpdate(String sql, Object... params) throws SQLException {
Connection connection = JdbcUtilsV2.connection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 1; i < params.length; i++) {
preparedStatement.setObject(i, params[i - 1]);
}
int rows = preparedStatement.executeUpdate();
preparedStatement.close();
if (connection.getAutoCommit()) {
JdbcUtilsV2.freeConnection();
}
return rows;
}
public <T> List<T> excuteQuery(Class<T> clazz, String sql, Object... params) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException {
Connection connection = JdbcUtilsV2.connection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
if (params != null && params.length > 0) {
for (int i = 1; i < params.length; i++) {
preparedStatement.setObject(i, params[i - 1]);
}
}
ResultSet resultSet = preparedStatement.executeQuery();
List<T> list = new ArrayList<>();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while (resultSet.next()) {
T t= clazz.newInstance();
for (int i = 1; i <= columnCount; i++) {
Object value = resultSet.getObject(i);
String columnLabel = metaData.getColumnLabel(i);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, value);
}
list.add(t);
}
resultSet.close();
preparedStatement.close();
if (connection.getAutoCommit()){
JdbcUtilsV2.freeConnection();
}
return list;
}
}