java 封装查询sql_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

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

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

}

}

}