封装jdbc增、删、改、查询全部、按条件查
package com.softeem.utils;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
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.Properties;
import java.util.Set;
public class DBUtils {
public static String driver;
public static String url;
public static String username;
public static String password;
static{
try {
//读取属性文件为输入流
InputStream is = DBUtils.class.getResourceAsStream("jdbc.properties");
//创建Properties对象
Properties p = new Properties();
//加载输入流到属性中
p.load(is);
//根据属性名获取属性值
driver = p.getProperty("jdbc.driver");
url = p.getProperty("jdbc.url");
username = p.getProperty("jdbc.username");
password = p.getProperty("jdbc.password");
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return
*/
public static Connection getConn(){
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
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(String sql,Object... obj){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = getConn();
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) {
e.printStackTrace();
} finally{
close(ps, conn);
}
return false;
}
/**
* 技术参数: 泛型,集合框架,反射,JDBC
* 封装通用查询多条及操作
* @param t
* @param sql
* @param params
* @return
*/
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()){
//防止缓存上一条数据
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中的数据注入到Java对象中,并将对象存入集合
if(!map.isEmpty()){
//获取map集合键集(列名集合)
Set<String> columnNames = map.keySet();
//创建对象
obj = t.newInstance();
for (String column : columnNames) {
//根据键获取值
Object value = map.get(column);
//获取属性对象
Field f = t.getDeclaredField(column);
//设置属性为可访问状态
f.setAccessible(true);
//为属性设置
f.set(obj, value);
}
list.add(obj);
}
}
} catch (SQLException e) {
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 list;
}
public static void main(String[] args) {
System.out.println(getConn());
}
}
创建一个File文件
jdbc.properties
##jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test
jdbc.username=root
jdbc.password=root
####Connection Pool
initialSize=1
maxActive=20
minIdle=1
maxWait=60000