jdbc封装
package com.softeem.lesson34.batch.utils;
import com.alibaba.druid.pool.DruidDataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
public class DBUtils3 {
private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost:3306/test7";
private static final String USER = "root";
private static final String PASSWORD = "000728";
private static DruidDataSource dataSource;
private static final int INIT_SIZE = 1;
private static final int MAX_ACTIVE = 5;
private static final long MAX_WAIT = 5000;
private static final int MIN_IDLE = 1;
static {
dataSource = createDateSource();
}
public static DruidDataSource createDateSource() {
dataSource = new DruidDataSource();
dataSource.setUrl(URL);
dataSource.setUsername(USER);
dataSource.setPassword(PASSWORD);
dataSource.setInitialSize(INIT_SIZE);
dataSource.setMaxActive(MAX_ACTIVE);
dataSource.setMaxWait(MAX_WAIT);
dataSource.setMinIdle(MIN_IDLE);
return dataSource;
}
public static Connection getConn() {
try {
if (dataSource == null || dataSource.isClosed()) {
dataSource = createDateSource();
}
return dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
static {
try {
Class.forName(DRIVER_CLASS);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs, Statement stat, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (stat != null) {
stat.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static boolean exeUpdate(String sql, Object... params) {
Connection conn = getConnection();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
if (Objects.nonNull(params)) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
return ps.executeUpdate() > 0;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtils3.close(null, ps, conn);
}
return false;
}
public static boolean exeUpdate(Connection conn, String sql, Object... params) throws SQLException {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
if (Objects.nonNull(params)) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
return ps.executeUpdate() > 0;
} finally {
DBUtils3.close(null, ps, null);
}
}
public static List<Map<String, Object>> queryToMap(String sql, Object... params) {
List<Map<String, Object>> list = new ArrayList<>();
Connection conn = DBUtils2.getConn();
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
if (Objects.nonNull(params)) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
while (rs.next()) {
Map<String, Object> map = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
String cname = rsmd.getColumnName(i);
Object value = rs.getObject(i);
map.put(cname, value);
}
list.add(map);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(rs, ps, conn);
}
return list;
}
public static <T> T queryOne(Class<T> t, String sql, Object... params) {
List<Map<String, Object>> list = queryToMap(sql, params);
if (list.size() > 0) {
Map<String, Object> map = list.get(0);
return mapToBean(map, t);
}
return null;
}
public static <T> List<T> queryList(Class<T> t, String sql, Object... params) {
List<T> list = new ArrayList<T>();
List<Map<String, Object>> maps = queryToMap(sql, params);
maps.forEach(m -> {
T obj = mapToBean(m, t);
list.add(obj);
});
return list;
}
private static <T> T mapToBean(Map<String, Object> map, Class<T> t) {
try {
T obj = t.newInstance();
map.forEach((k, v) -> {
try {
Field field = t.getDeclaredField(k);
field.setAccessible(true);
field.set(obj, v);
} catch (NoSuchFieldException e) {
e.printStackTrace();
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
});
return obj;
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
return null;
}
}