元数据可以用来优化JDBC对数据库的CURD操作。其中增、删、改可以优化为同一个接口,查询比较复杂些,可单独优化为一个接口。
增删改优化为如下,此方法接受两个参数,sql语句和sql语句中对应的参数:
public static int update(String sql, Object[] params){
if(sql.equals("") || sql==null){
return -1;
}
try {
conn = JdbcC3p0Pools.getConnection2();
ps = conn.prepareStatement(sql);
if(params!=null && params.length>0){
pm = ps.getParameterMetaData();
int pmCount = pm.getParameterCount();
while(pmCount>0){
ps.setObject(pmCount, params[pmCount-1]);
pmCount--;
}
}
return ps.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return -1;
}
2、查询方法优化如下:
public static ArrayList<Object> query(String sql, Object[] params, Class clazz ) throws Exception{
if(sql.equals("") || sql==null){
return null;
}
try {
conn = JdbcC3p0Pools.getConnection2();
ps = conn.prepareStatement(sql);
if(params!=null && params.length>0){
pm = ps.getParameterMetaData();
int pmCount = pm.getParameterCount();
while(pmCount>0){
ps.setObject(pmCount, params[pmCount-1]);
pmCount--;
}
}
rs = ps.executeQuery();
BeanListHander hander = new BeanListHander(clazz); //利用了策略模式,将结果封装到clazz bean中
return hander.hander(rs);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
这里需要定义另外一个类,BeanListHander,此类利用反射技术,将ResultSet对象查询结果封装到Clazz的对象中。
类BeanListHander
package cn.itcast.utils;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
public class BeanListHander {
private Class clazz;
private ArrayList<Object> list = new ArrayList<Object>();
public BeanListHander(Class clazz){
this.clazz = clazz;
}
public ArrayList<Object> hander(ResultSet rs) throws Exception{
ResultSetMetaData rm = null;
int count = 0;
while(rs.next()){
Object bean = clazz.newInstance();
rm = rs.getMetaData();
count = rm.getColumnCount();
//要求数据库字段和域方法一致
while(count>0){
String name = rm.getColumnName(count);
Object value = rs.getObject(count);
Field f = clazz.getDeclaredField(name);//利用反射技术
f.setAccessible(true);
f.set(bean, value);
count--;
}
list.add(bean);
}
return list;
}
}
//demo
package cn.itcast.demo;
import java.util.ArrayList;
import org.junit.Test;
import cn.itcast.domain.Account;
import cn.itcast.utils.DatabaseOperationUtils;
public class Demo6 {
@Test
public void query(){
String sql = "select * from account";
ArrayList<Object> list;
try {
list = DatabaseOperationUtils.query(sql, null, Account.class);
for(Object a : list){
Account account = (Account)a;
System.out.println(account.getId()+":"+account.getName()+":"+account.getMoney());
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
@Test
public void update(){
String sql = "update account set money=money+1000 where name=?";
Object[] params = new Object[1];
params[0]="aaa";
DatabaseOperationUtils.update(sql, params);
}
@Test
public void add(){
String sql = "insert into account(name,money) values(?,?)";
Object[] params = new Object[2];
params[0]="eee";
params[1]=1500;
DatabaseOperationUtils.update(sql, params);
}
@Test
public void del(){
String sql = "delete from account where name=?";
Object[] params = new Object[1];
params[0]="eee";
DatabaseOperationUtils.update(sql, params);
}
}