import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import connection.ConnectionUtil;
/**
* Dao:通用的增删改查操作
* @author CharlieLiang
*
*/
public abstract class BaseDAO {
//通用增删改操作考虑事务
public void update(Connection con,String sql,Object ...args) {
PreparedStatement ps=null;
try {
ps=con.prepareStatement(sql);
for(int i=0;i<args.length;i++) {
ps.setObject(i+1, args[i]);
}
ps.execute();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ConnectionUtil.closeResource(null, ps);
}
}
//通用的查询记录返回表中的一条数据,考虑事务
public <T> T getInstance(Connection con,Class<T> clazz,String sql,Object ...args) {
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps=con.prepareStatement(sql);
for(int i=0;i<args.length;i++) {
ps.setObject(i+1, args[i]);
}
rs=ps.executeQuery();
ResultSetMetaData rsmd=rs.getMetaData();
//获取列数
int column=rsmd.getColumnCount();
while(rs.next()) {
T t=clazz.newInstance();
for(int i=0;i<column;i++) {
//获取列值
Object columnValue=rs.getObject(i+1);
//获取每个列的列名
String columnLabel=rsmd.getColumnLabel(i+1);
//根据列名获取类的属性
Field field=clazz.getDeclaredField(columnLabel);
//列的属性赋值
field.setAccessible(true);
//用列名给
field.set(t, columnValue);
}
return t;
}
}catch(Exception e) {
e.printStackTrace();
}finally {
ConnectionUtil.closeResource(null, ps,rs);
}
return null;
}
//通用的查询记录返回表中的多条数据构成的集合,考虑事务
public <T> List<T> getForList(Connection con,Class<T> clazz,String sql,Object...args) {
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps=con.prepareStatement(sql);
//2.填充sql语句的通配符,
for(int i=0;i<args.length;i++) {
ps.setObject(i+1, args[i]);
}
//3.执行sql语句返回结果集
rs=ps.executeQuery();
ArrayList<T> list=new ArrayList<>();
//4.获取结果集的元数据
ResultSetMetaData rsmd=rs.getMetaData();
//元数据中获取列
int col=rsmd.getColumnCount();
while(rs.next()) {
//泛型的class
T t=clazz.newInstance();
for(int i=0;i<col;i++) {
Object columnValue=rs.getObject(i+1);
String columnName=rsmd.getColumnLabel(i+1);
//反射填充
Field field=clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} 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();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ConnectionUtil.closeResource(null, ps,rs);
}
return null;
}
//用于查询特殊值的通用方法
public int getValue(Connection con,String sql,Object...args) {
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps=con.prepareStatement(sql);
for(int i=0;i<args.length;i++) {
ps.setObject(i+1, args[i]);
}
rs=ps.executeQuery();
if(rs.next()) {
return (int) rs.getObject(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
ConnectionUtil.closeResource(null, ps,rs);
}
return 0;
}
}
以上是一个项目中,对SQL server的通用的增删改查操作的代码