java封装sql查询

package com.sunjing.palm;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.beanutils.BeanUtils;

public class DbUtil {

private static Map paramsMap = new HashMap();

private static final String classPackage = "com.ord.model."; //1.将对应数据库的JavaBean放到此包下面(如:UserInfo)

private static ConnectionPool connPool = new ConnectionPool();

/**
* 查询操作
* @param sql
* @param param
* @param className
* @return
*/
public static List query(String sql, Map param, String className) {

List result = new ArrayList();

try {

Connection conn = connPool.getConnection();

PreparedStatement p = conn.prepareStatement(parseSql(sql));

boolean flag = fillParameters(p, param);

if (flag) {

ResultSet rs = p.executeQuery();

result = buildResult(rs, className);

}

} catch (SQLException e) {

e.printStackTrace();
}

close();

return result;
}

/**
* 修改和删除操作
* @param sql
* @param param
* @return
*/
public static boolean update(String sql, Map param) {

try {

Connection conn = connPool.getConnection(); // 从连接库中获取一个可用的连接

PreparedStatement p = conn.prepareStatement(parseSql(sql));

boolean flag = fillParameters(p, param);

if (flag) {

if (p.executeUpdate() == 1) {

return true;

}else {

return false;
}

}

} catch (SQLException e) {

e.printStackTrace();
}

close();

return false;
}

/**
* 关闭数据库
*/
private static void close(){

}

private static List buildResult(ResultSet rs, String className) {

List result = new ArrayList();

try {

Class c = Class.forName(classPackage + className);

Field[] fields = c.getDeclaredFields();

while (rs.next()) {

Object bean = c.newInstance();

for (int i = 0; i < fields.length; i++) {

Field field = fields[i];

BeanUtils.setProperty(bean, field.getName(), rs.getObject(field.getName()));

}

result.add(bean);

}

} catch (SQLException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();
} catch (IllegalAccessException e) {

e.printStackTrace();
} catch (InvocationTargetException e) {

e.printStackTrace();
} catch (InstantiationException e) {

e.printStackTrace();
}

return result;

}

/**
* 替换sql语句中的命名参数
* (select * from db where name=:name and age=:age
* 转换后为
* select * from db where name=? and age =?
* )
* @param sql
* @return
*/
public static String parseSql(String sql) {

String regex = "(:(//w+))";
Pattern p = Pattern.compile(regex);
Matcher m = p.matcher(sql);
paramsMap.clear();
int idx = 1;
while (m.find()) {
//参数名称可能有重复，使用序号来做Key
paramsMap.put(new Integer(idx++), m.group(2));

}
String result = sql.replaceAll(regex, "?");

return result;
}

/**
* 填充PreparedStatement参数
* @param ps
* @param param
* @return
*/
private static boolean fillParameters(PreparedStatement ps, Map param) {
boolean result = true;
String paramName = null;
Object paramValue = null;
int idx = 1;

for (Iterator itr = paramsMap.entrySet().iterator(); itr.hasNext();) {

Entry entry = (Entry) itr.next();
paramName = (String) entry.getValue();
idx = ((Integer) entry.getKey()).intValue();
//不包含会返回null
paramValue = param.get(paramName);

try {

ps.setObject(idx, paramValue);

} catch (Exception e) {

result = false;
}
}

return result;
}

}

/**

*JavaBean

name对应数据库中字段name

age对应数据库字段age

*

*/

public class UserInof{

private String name;

private Integer age;

public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}

}

package com.sunjing.palm;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.Date;

public class BeanUtils {

/**
*
* @param bean 赋值的JavaBean
* @param name JavaBean的属性名
* @param value 属性的值Integer :20 Double :20d Float :20f Long:20L Boolean:true|false
*/
public static void setProperty(Object bean,String name,Object value){

Class beanClass = bean.getClass();

String methodName ="set"+ name.substring(0, 1).toUpperCase()+name.substring(1);

Class argClass = Object.class;

if (value instanceof Boolean) {

argClass = Boolean.class;

} else if (value instanceof String) {

argClass = String.class;

} else if (value instanceof Integer) {

argClass = Integer.class;

} else if (value instanceof Double) {

argClass = Double.class;

} else if (value instanceof Long) {

argClass = Long.class;

} else if (value instanceof Float) {

argClass = Float.class;

} else {

throw new RuntimeException(
"Not set for other types yet");
}

try {

Method method =beanClass.getMethod(methodName, argClass);

method.invoke(bean, value);

} catch (SecurityException e) {

e.printStackTrace();
} catch (NoSuchMethodException e) {

e.printStackTrace();
} catch (IllegalArgumentException e) {

e.printStackTrace();
} catch (IllegalAccessException e) {

e.printStackTrace();
} catch (InvocationTargetException e) {

e.printStackTrace();
}
}
}

public class DbUtilsTest(){

pulbic static void main(String args[]){

String sql = "select * from user where name=:name and age=:age";

Map param = new HashMap();

param.put("name","sj"); //2.Map中的关键字一定要和sql语句中命令参数一样 即:name中的name

param.put("age",23);

List result =DbUtils.query(sql,param,"UserInfo"); //3.一定要传对应表user的类，此处是UserInfo类。

for(int i=0;i<result.size();i++){

UserInfo userInfo = (UserInfo)result.get(i);

System.out.println(userInfo.getName()+"---"+useInfo.getAge());

}

}

}