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());

     }

    

 

  }

 

}