import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Properties;
import java.util.Set;
import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
/*通用数据库工具类,基于Druid连接池实现
* 1,获取资源
* 2,关闭资源
* 3,执行通用的更新操作
* 4,执行通用的查询列表操作
* 5,执行通用的查询单条记录
*/
public class DBUtils {
private static DruidDataSource pool;
private static String url;
private static String username;
private static String password;
private static int initialSize;
private static int maxActive;
private static int minIdle;
private static long maxWait;
static {
init();
}
private static void loadProp() {
try {
// 属性文件位于src根目录时,加“/”则不要使用ClassLoader;如果使用ClassLoader则无需“/”
InputStream is = DBUtils.class.getResourceAsStream("/jdbc.properties");
Properties p = new Properties();
p.load(is);
url = p.getProperty("jdbc.url");
username = p.getProperty("jdbc.username");
password = p.getProperty("jdbc.password");
initialSize = Integer.parseInt(p.getProperty("initialSize"));
maxActive = Integer.parseInt(p.getProperty("maxActive"));
minIdle = Integer.parseInt(p.getProperty("minIdle"));
maxWait = Long.parseLong(p.getProperty("maxWait"));
} catch (IOException e) {
e.printStackTrace();
}
}
private static void init() {
pool = new DruidDataSource();
// 加载属性文件初始化配置
loadProp();
pool.setUrl(url);
pool.setUsername(username);
pool.setPassword(password);
// 设置连接池中初始连接数
pool.setInitialSize(initialSize);
// 设置最大连接数
pool.setMaxActive(maxActive);
// 设置最小闲置链接数
pool.setMinIdle(minIdle);
// 设置最大的等待时间(等待获取链接的时间)
pool.setMaxWait(maxWait);
}
public static Connection getConn() {
try {
// 设置最大的等待时间(等待获取链接的时间)
if (pool == null || pool.isClosed()) {
init();
}
return pool.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
/**
* 资源关闭
*
* @param stmt
* @param conn
*/
public static void close(Statement stmt, Connection conn) {
try {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/*
* 封装通用的更新操作,对所有更新(INSERT,UPDATE,DELETE)有关的操作都能通过该方法实现
*
* @param sql
*
* @return
*
* insert into daily(id,user,dept,content,bak) values(?,?,?,?,?) delete from
* daily where id=? update daily set content=? where id=?
*/
public static boolean exeUpdate(Connection conn, String sql, Object... obj) {
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < obj.length; i++) {
ps.setObject(i + 1, obj[i]);
}
return ps.executeUpdate() > 0;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
close(ps,null );
}
return false;
}
// ji术参数: 泛型,集合框架,反射,JDBC
// * 封装通用查询多条及操作
// * @param t
// * @param sql
// * @param params
// * @return
public static <T> T queryOne(Class<T> t, String sql, Object... params) {
T obj = null;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConn();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsm = rs.getMetaData();
if (rs.next()) {
// 创建一个指定的类型对象 必须有默认构造器
obj = t.newInstance();
for (int i = 0; i < rsm.getColumnCount(); i++) {
String cname = rsm.getColumnLabel(i + 1);
Object value = rs.getObject(cname);
Field f = t.getDeclaredField(cname);
f.setAccessible(true);
f.set(obj, value);
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return obj;
}
public static <T> List<T> queryList(Class<T> t, String sql, Object... params) {
List<T> list = new ArrayList<>();
T obj = null;
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConn();
ps = conn.prepareStatement(sql);
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
ResultSet rs = ps.executeQuery();
// 获取插叙结果集中的元数据(类型,数量,长度)
ResultSetMetaData rsmd = rs.getMetaData();
// 临时一个map集合 存储查询到的数据 (key:列名 value:列值)
Map<String, Object> map = new HashMap<>();
// 遍历他的所有结果
while (rs.next()) {
// clear 清空上次缓存 防止缓存上次的数据
map.clear();
// 遍历所有的列
for (int i = 0; i < rsmd.getColumnCount(); i++) {
// 获取列名
String cname = rsmd.getColumnLabel(i + 1);
// 获取列值
Object value = rs.getObject(cname);
// 添加存储到map
map.put(cname, value);
}
// 如果map不等于空
// 利用反射将map的数据注入到java对象,将对象存入集合
if (!map.isEmpty()) {
// 获取map集合键集(列名集合)
Set<String> columnNames = map.keySet();
// 创建对象
obj = t.newInstance();// new Student() //java.lang.Object
System.out.println("-------" + obj);
for (String column : columnNames) {
// 根据键获取值
Object value = map.get(column);
// 当数据对象不为空时,才注入数据到属性中
if (Objects.nonNull(value)) {
// 获取属性对象
Field f = t.getDeclaredField(column);
// 设置属性为可访问状态
f.setAccessible(true);
// 为属性设置
f.set(obj, value);
}
// 添加
list.add(obj);
}
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (NoSuchFieldException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
public static void main(String[] args) {
System.out.println(getConn());
}
}
####JDBC-MySQL
###jdbc.driver = com.mysql.Driver
jdbc.url =jdbc:mysql://localhost:3306/work
jdbc.username=root
jdbc.password=root
### Connection Pool
initialSize =1
maxActive=20
minIdle=1
maxWait=10000