package com.lx.DBUtil;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
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.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
public class DBUtil<T> {
private static String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static String URL = "jdbc:sqlserver://localhost:1433;DataBaseName=Book";
private static String USER = "sa";
private static String PASSWORD = "123123";
// 获取连接
public static Connection getConn() {
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//添加通用类
public static void setParams(Object[] objs,PreparedStatement ps){
try {
if(objs!=null){
for(int i=0;i<objs.length;i++){
ps.setObject(i+1, objs[i]);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static int executeSQL(String sql,Object[] objs){
Connection conn=null;
PreparedStatement ps=null;
int res=0;
try {
conn=getConn();
ps=conn.prepareStatement(sql);
setParams(objs, ps);
res=ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return res;
}
//查询通用类
public int queryCount(String sql, List args, Class clazz) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConn();
pstmt = conn.prepareStatement(sql);
// 如果sql语句有参数用args集合循环给其赋值
if (args != null && args.size() > 0) {
for (int i = 0; i < args.size(); i++) {
pstmt.setObject(i + 1, args.get(i));// 参数位置号从1开始
}
}
rs = pstmt.executeQuery();
if (rs.next())
return rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(null, pstmt, conn);
}
return 0;
}
// 查询方法
@SuppressWarnings("unchecked")
public List<T> query(String sql, List args, Class clazz) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// 存储返回的集合对象
List<T> list = new ArrayList<T>();
try {
// 使用反射获取类的相关信息(实体类的属性--数据库里相应表的字段)
Field[] fields = clazz.getDeclaredFields();
Method method = null;
conn = getConn();
pstmt = conn.prepareStatement(sql);
// 如果sql语句有参数用args集合循环给其赋值
if (args != null && args.size() > 0) {
for (int i = 0; i < args.size(); i++) {
pstmt.setObject(i + 1, args.get(i));// 参数位置号从1开始
}
}
rs = pstmt.executeQuery();
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();//从结果集中获取元数据
// 获取一共有多少列
int columnCount = rsmd.getColumnCount();
while (rs.next()) {
// 创建一个新对象
T obj = (T) clazz.newInstance();
// 取出的结果集中列号从1开始
for (int i = 1; i <= columnCount; i++) {
String cname = rsmd.getColumnName(i);// 获取每一列的名称
int ctype = rsmd.getColumnType(i);// 获取每一列的数据类型
for (Field f : fields) {
if (cname.equalsIgnoreCase(f.getName()))// 如果列名和字段名相同,equalsIgnoreCase代表忽略大小写
{
// 开始封装数据
String methodName = "set"
+ f.getName().substring(0, 1).toUpperCase()
+ f.getName().substring(1);
// 根据列的type值的类型进行处理
switch (ctype) {
case Types.INTEGER:
method = clazz.getMethod(methodName,
Integer.class);
method.invoke(obj, rs.getInt(i));
break;
case Types.VARCHAR:
method = clazz.getMethod(methodName,
String.class);
method.invoke(obj, rs.getString(i));
break;
case Types.CHAR:
method = clazz.getMethod(methodName,
String.class);
method.invoke(obj, rs.getString(i));
break;
// 前台的java数据类型为java.util.Date类型
case Types.TIMESTAMP:
method = clazz.getMethod(methodName,
Timestamp.class);
method.invoke(obj, rs.getString(i));
break;
case Types.DECIMAL:// 或者改为case
// 3,因为数据库中的money类型的ctype值为3,注:java.math.bigdecimal对应数据库里的money类型
method = clazz.getMethod(methodName,
BigDecimal.class);
method.invoke(obj, rs.getBigDecimal(i));
break;
}
}
}
}
list.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(null, pstmt, conn);
}
return list;
}
// 执行增删改方法
public int saveOrDeleteOrUpdate(String sql, List list) {
Connection conn = null;
PreparedStatement pstmt = null;
int count = 0;
try {
conn = getConn();
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
// 循环给sql参数赋值
if (list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
pstmt.setObject(i + 1, list.get(i));
}
}
count = pstmt.executeUpdate();
conn.commit();
} catch (Exception e) {
try {
conn.rollback();// 捕获事务异常,事务回滚
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
System.out.println("数据库操作异常!");
} finally {
closeAll(null, pstmt, conn);
}
return count;
}
// 关闭连接
public void closeAll(ResultSet rs, Statement stmt, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
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.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
public class DBUtil<T> {
private static String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static String URL = "jdbc:sqlserver://localhost:1433;DataBaseName=Book";
private static String USER = "sa";
private static String PASSWORD = "123123";
// 获取连接
public static Connection getConn() {
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
//添加通用类
public static void setParams(Object[] objs,PreparedStatement ps){
try {
if(objs!=null){
for(int i=0;i<objs.length;i++){
ps.setObject(i+1, objs[i]);
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static int executeSQL(String sql,Object[] objs){
Connection conn=null;
PreparedStatement ps=null;
int res=0;
try {
conn=getConn();
ps=conn.prepareStatement(sql);
setParams(objs, ps);
res=ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
return res;
}
//查询通用类
public int queryCount(String sql, List args, Class clazz) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConn();
pstmt = conn.prepareStatement(sql);
// 如果sql语句有参数用args集合循环给其赋值
if (args != null && args.size() > 0) {
for (int i = 0; i < args.size(); i++) {
pstmt.setObject(i + 1, args.get(i));// 参数位置号从1开始
}
}
rs = pstmt.executeQuery();
if (rs.next())
return rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(null, pstmt, conn);
}
return 0;
}
// 查询方法
@SuppressWarnings("unchecked")
public List<T> query(String sql, List args, Class clazz) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
// 存储返回的集合对象
List<T> list = new ArrayList<T>();
try {
// 使用反射获取类的相关信息(实体类的属性--数据库里相应表的字段)
Field[] fields = clazz.getDeclaredFields();
Method method = null;
conn = getConn();
pstmt = conn.prepareStatement(sql);
// 如果sql语句有参数用args集合循环给其赋值
if (args != null && args.size() > 0) {
for (int i = 0; i < args.size(); i++) {
pstmt.setObject(i + 1, args.get(i));// 参数位置号从1开始
}
}
rs = pstmt.executeQuery();
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();//从结果集中获取元数据
// 获取一共有多少列
int columnCount = rsmd.getColumnCount();
while (rs.next()) {
// 创建一个新对象
T obj = (T) clazz.newInstance();
// 取出的结果集中列号从1开始
for (int i = 1; i <= columnCount; i++) {
String cname = rsmd.getColumnName(i);// 获取每一列的名称
int ctype = rsmd.getColumnType(i);// 获取每一列的数据类型
for (Field f : fields) {
if (cname.equalsIgnoreCase(f.getName()))// 如果列名和字段名相同,equalsIgnoreCase代表忽略大小写
{
// 开始封装数据
String methodName = "set"
+ f.getName().substring(0, 1).toUpperCase()
+ f.getName().substring(1);
// 根据列的type值的类型进行处理
switch (ctype) {
case Types.INTEGER:
method = clazz.getMethod(methodName,
Integer.class);
method.invoke(obj, rs.getInt(i));
break;
case Types.VARCHAR:
method = clazz.getMethod(methodName,
String.class);
method.invoke(obj, rs.getString(i));
break;
case Types.CHAR:
method = clazz.getMethod(methodName,
String.class);
method.invoke(obj, rs.getString(i));
break;
// 前台的java数据类型为java.util.Date类型
case Types.TIMESTAMP:
method = clazz.getMethod(methodName,
Timestamp.class);
method.invoke(obj, rs.getString(i));
break;
case Types.DECIMAL:// 或者改为case
// 3,因为数据库中的money类型的ctype值为3,注:java.math.bigdecimal对应数据库里的money类型
method = clazz.getMethod(methodName,
BigDecimal.class);
method.invoke(obj, rs.getBigDecimal(i));
break;
}
}
}
}
list.add(obj);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
closeAll(null, pstmt, conn);
}
return list;
}
// 执行增删改方法
public int saveOrDeleteOrUpdate(String sql, List list) {
Connection conn = null;
PreparedStatement pstmt = null;
int count = 0;
try {
conn = getConn();
conn.setAutoCommit(false);
pstmt = conn.prepareStatement(sql);
// 循环给sql参数赋值
if (list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
pstmt.setObject(i + 1, list.get(i));
}
}
count = pstmt.executeUpdate();
conn.commit();
} catch (Exception e) {
try {
conn.rollback();// 捕获事务异常,事务回滚
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
System.out.println("数据库操作异常!");
} finally {
closeAll(null, pstmt, conn);
}
return count;
}
// 关闭连接
public void closeAll(ResultSet rs, Statement stmt, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}