com.css.common.jdbcTemplate中的类

package com.css.common.jdbcTemplate;

import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.ListOrderedMap;

/**
 * jdbcTemplate的数据层接口类 <br/>
 *
 * @author 何青
 */
public interface GenericJDBCDao<T> {

 /**
  * 通过ID查找具体的信息
  *
  * @param tableName  表名
  * @param id   ID
  */
 public T selectById(final String tableName,final Integer id) throws Exception;
 
 /**
  * 通过SQL语句查找具体的信息
  *
  * @param sql   带问号的SQL
  * @param params  问号对应的数据参数
  */
 public T select(final String sql,final Object[] params) throws Exception;
 
 /**
  * 拼接SQL以BEAN里的有值的属性为查询条件
  *
  * @param tableName  表名
  * @param t    带数据的BEAN
  */
 public T select(final String tableName,final T t) throws Exception;
 
 /**
  * 修改数据,拼接SQL以BEAN里的有值的属性为SET参数
  *
  * @param tableName  表名
  * @param t    带数据的BEAN
  * @param where   SQL里的WHERE条件
  */
 public void update(final String tableName,final T t,final String where) throws Exception;
 
 /**
  * 修改数据,以MAP里的键值对为SET拼接条件
  *
  * @param tableName  表名
  * @param map   数据键值对
  * @param where   SQL里的WHERE条件
  */
 public void update(final String tableName,final Map<String,String> map,final String where) throws Exception;
 
 /**
  * 修改单独一个字段的值
  *
  * @param tableName   表名
  * @param updateFieldName 被修改的字段名
  * @param updateFieldValue 要修改成的值
  * @param updateKey   条件的字段
  * @param updateKeyValue 条件字段的值
  */
 public void update(final String tableName, String updateFieldName, String updateFieldValue,String updateKey, String updateKeyValue) throws Exception;
 
 /**
  * 修改数据
  * 
  * @param sql  带问号的SQL
  * @param param  问号对应的数据参数
  */
 public void update(String sql,Object[] param) throws Exception;
 
 /**
  * 修改数据
  *
  * @param sql  SQL语句
  */
 public void update(String sql) throws Exception;
 
 /**
  * 删除数据
  *
  * @param tableName  表名
  * @param fieldName  条件字段
  * @param fieldValue 条件字段的值
  */
 public void delete(final String tableName,final String fieldName,final String fieldValue) throws Exception;
 
 /**
  * 删除数据,根据MAP键值对生成SQL
  *
  * @param tableName  表名
  * @param map   条件键值对
  */
 public void delete(String tableName, Map map) throws Exception;
 
 /**
  * 保存数据,以BEAN里的有值属性拼接成INSERT条件
  *
  * @param tableName  表名
  * @param t    带数据的BEAN
  */
 public void save(final String tableName,final T t) throws Exception;
 
 /**
  * 查询出数据集合
  *
  * @param tableName  表名
  * @param where   查询条件
  */
 public List<T> list(final String tableName,final String where) throws Exception;
 
 /**
  * 查询出数据集合
  *
  * @param sql   SQL语句
  */
 public List<ListOrderedMap> listSQL(final String sql) throws Exception;
 
 /**
  * 查询出数据集合
  *
  * @param sql   带问号的SQL
  * @param obj   问号对应的数据参数
  */
 public List<ListOrderedMap> listSQL(final String sql,Object[] obj) throws Exception;
}

package com.css.common.jdbcTemplate;

import java.lang.reflect.Field;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.map.ListOrderedMap;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultSetExtractor;
import org.springframework.util.Assert;

import com.css.common.util.DateUtil;

/**
 * jdbcTemplate的数据层实现类 <br/>
 *
 * @author 何青
 */
public class GenericJDBCDaoImpl<T> implements GenericJDBCDao<T>{

 private static Map<String,Map<String,String>> properMap = new HashMap<String,Map<String,String>>();
 
 private Class<T> creteClass;
 
 private JdbcTemplate jdbcTemplate;

 public GenericJDBCDaoImpl(Class<T> creteClass) {
    this.creteClass = creteClass;
 }
 
 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
  this.jdbcTemplate = jdbcTemplate;
 }
 
 class objectRowMapper implements RowMapper {
  public Object mapRow(final ResultSet rs,final int index) throws SQLException {
   T t = (T) resultSetConvertBean(rs,creteClass);
   return t;
  }
 }

 public T selectById(final String tableName,final Integer id) throws Exception
 {
  StringBuffer sql = new StringBuffer("select * from "+tableName);
  sql.append(" where id=?");
  Object[] params=new Object[]{ id };
  T t=(T) jdbcTemplate.query(sql.toString(), params, new ResultSetExtractor() {
   public T extractData(final ResultSet rs) throws SQLException,DataAccessException {
    T t= null;
    if(rs.next())
    {
     t=(T) resultSetConvertBean(rs,creteClass);
    }
    rs.close();
    return t;
   }
  });
  return t;
 }

 public T select(final String tableName,final T t) throws Exception
 {
  Assert.notNull(t);
  StringBuffer sql = new StringBuffer("select * from "+tableName+" where ");
  Map<String,String> map = sqlKey(t);
  Class clazz = t.getClass();
  Field[] fields = clazz.getDeclaredFields();
  Object[] params=null;
  List<Object> list=new ArrayList<Object>();
  for(Field f:fields)
  {
   f.setAccessible(true);
   try {
    if(f.get(t) != null && !f.get(t).equals(""))
    {
     list.add(f.get(t));
     sql.append(map.get(f.getName()));
     sql.append("=? and ");
    }
   } catch (Exception e) {
    e.printStackTrace();
   }
   f.setAccessible(false);
  }
  if(sql.toString().endsWith(" and "))
  {
   sql.delete(sql.length()-5, sql.length());
   params=new Object[list.size()];
   for(int i1=0;i1 <= list.size()-1;i1++)
   {
    params[i1] = list.get(i1);
   }
  }
  T t1=(T) jdbcTemplate.query(sql.toString(), params, new ResultSetExtractor() {
   public T extractData(final ResultSet rs) throws SQLException,DataAccessException {
    T t1= null;
    if(rs.next())
    {
     t1=(T) resultSetConvertBean(rs,creteClass);
    }
    rs.close();
    return t1;
   }
  });
  return t1;
 }
 
 public T select(final String sql,final Object[] params) throws Exception
 {
  T t=(T) jdbcTemplate.query(sql, params, new ResultSetExtractor() {
   public T extractData(final ResultSet rs) throws SQLException,DataAccessException {
    T t= null;
    if(rs.next())
    {
     t=(T) resultSetConvertBean(rs,creteClass);
    }
    rs.close();
    return t;
   }
  });
  return t;
 }
 
 public void update(final String tableName,final T t,final String where) throws Exception
 {
  Assert.notNull(t);
  StringBuffer sql=new StringBuffer("update "+tableName+" set ");
  StringBuffer values=new StringBuffer();
  Map<String,String> map=sqlKey(t);
  Class clazz=t.getClass();
  Field[] fields=clazz.getDeclaredFields();
  for(Field f:fields)
  {
   f.setAccessible(true);
   try {
    if(f.get(t) != null){
     values.append(map.get(f.getName())+"='");
     if(f.getType().toString().equals("class java.util.Date")){
      values.append(DateUtil.convertDateToString((Date)f.get(t)));
     }else{
      values.append(f.get(t));
     }
     values.append("',");
    }
   } catch (Exception e) {
    e.printStackTrace();
   }
   f.setAccessible(false);
  }
  if(values.toString().endsWith(","))
  {
   sql.append(values.substring(0, values.length()-1));
  }
  if(where !=null && !where.equals(""))
  {
   sql.append(" where "+where);
  }
  jdbcTemplate.update(sql.toString());
 }
 
 public void update(final String tableName,final Map<String,String> map,final String where) throws Exception
 {
  StringBuffer sql=new StringBuffer("update "+tableName+" set ");
  StringBuffer values=new StringBuffer();
  for(Map.Entry<String, String> entry:map.entrySet())
  {
   values.append(entry.getKey()+"='");
   values.append(entry.getValue());
   values.append("',");
  }
  if(values.toString().endsWith(","))
  {
   sql.append(values.substring(0, values.length()-1));
  }
  if(where !=null && !where.equals(""))
  {
   sql.append(" where "+where);
  }
  jdbcTemplate.update(sql.toString());
 }
 
    public void update(final String tableName,final String updateFieldName,final String updateFieldValue,
            final String updateKey,final String updateKeyValue) throws Exception{
            StringBuffer sql = new StringBuffer("update ");
            sql.append(tableName);
            sql.append(" set ").append(updateFieldName).append(" = ").append(updateFieldValue);
            sql.append(" where ").append(updateKey).append(" in (").append(updateKeyValue).append(")");
            jdbcTemplate.update(sql.toString());
        }
 
    public void update(final String sql) throws Exception
    {
     jdbcTemplate.update(sql);
    }
   
    public void update(final String sql,final Object[] param) throws Exception
    {
     jdbcTemplate.update(sql, param);
    }
   
   
 public void delete(final String tableName,final String fieldName,final String fieldValue) throws Exception
 {
   StringBuffer sql = new StringBuffer("delete from ");
   sql.append(tableName);
   sql.append(" where ").append(fieldName).append(" in (").append(fieldValue).append(")");
  jdbcTemplate.update(sql.toString());
 }
 
 
 public void delete(final String tableName,final Map map) throws Exception{
        StringBuffer sql = new StringBuffer();;
        if (map != null && map.size() > 0) {
         sql.append("delete from ");
         sql.append(tableName);
         sql.append(" where ");
            Iterator iterator = map.entrySet().iterator();
            while (iterator.hasNext()) {
                Map.Entry entity = (Map.Entry)iterator.next();
                sql.append(entity.getKey() + " in (" + entity.getValue() + ")");
                sql.append(" and ");
            }
            if (sql.toString().endsWith(" and ")) {
             sql.delete(sql.length()-5, sql.length());
            }
            jdbcTemplate.update(sql.toString());
        }
    }
     
 public void save(final String tableName,final T t) throws Exception{
  Assert.notNull(t);
  StringBuffer sql = new StringBuffer("insert into "+tableName+" (");
  StringBuffer values = new StringBuffer(") values (");
  Map<String,String> map=sqlKey(t);
  List list = new ArrayList();
  Class clazz=t.getClass();
  int i=0;
  for(Map.Entry<String, String> entry:map.entrySet())
  {
   try {
    Field f=clazz.getDeclaredField(entry.getKey());
    f.setAccessible(true);
    if(f.get(t) != null){
     list.add(f.get(t));
     i++;
     sql.append(entry.getValue()+",");
     values.append("?,");
    }
    f.setAccessible(false);
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
  if(values.toString().endsWith(","))
  {
   values.delete(values.toString().length()-1, values.toString().length());
  }
  sql.delete(sql.length()-1, sql.length());
  sql.append(values.toString()+")");
  jdbcTemplate.update(sql.toString(), list.toArray());
 }

 public List<T> list(final String tableName,final String where) throws Exception{
  List<T> list = new ArrayList<T>();
  StringBuffer sql =new StringBuffer("select * from "+tableName);
  if(where != null && !where.equals(""))
  {
   sql.append(" where ");
   sql.append(where);
  }
  list = (List<T>) jdbcTemplate.query(sql.toString(), new RowMapperResultSetExtractor(
    new objectRowMapper()));
  return list;
 }
 
 
 public List<ListOrderedMap> listSQL(final String sql) throws Exception{
  return this.jdbcTemplate.queryForList(sql);
 }
 
 public List<ListOrderedMap> listSQL(final String sql,Object[] obj) throws Exception{
  return this.jdbcTemplate.queryForList(sql, obj);
 }
 
 /**
  * 从数据库里取得�?并装配成完整的BEAN
  *
  * @param rs
  * @param obj
  * @return
  * @throws Exception
  */
 private static Object resultSetConvertBean(final ResultSet rs,final Class clazz)
 {
  Object obj = null;;
  Map<String,String> map=new HashMap<String,String>();
  try {
   obj = clazz.newInstance();
   map=sqlKey(obj);
  } catch (Exception e1) {
   e1.printStackTrace();
  }
  Field[] fields=clazz.getDeclaredFields();
  try
  {
   for(Field f:fields)
   {
    f.setAccessible(true);
    String fieldName=map.get(f.getName());
    String fieldType=f.getType().toString();
    if(fieldType.equals("class java.lang.Integer"))
    {
     f.set(obj, rs.getInt(fieldName));
    }else if(fieldType.equals("class java.lang.Boolean"))
    {
     f.set(obj, rs.getBoolean(fieldName));
    }else if(fieldType.equals("class java.lang.Byte"))
    {
     f.set(obj, rs.getByte(fieldName));
    }else if(fieldType.equals("class java.lang.Double"))
    {
     f.set(obj, rs.getDouble(fieldName));
    }else if(fieldType.equals("class java.lang.Float"))
    {
     f.set(obj, rs.getFloat(fieldName));
    }else if(fieldType.equals("class java.lang.Long"))
    {
     f.set(obj, rs.getLong(fieldName));
    }else if(fieldType.equals("class java.lang.Short"))
    {
     f.set(obj, rs.getShort(fieldName));
    }else if(fieldType.equals("class java.lang.String"))
    {
     f.set(obj, rs.getString(fieldName));
    }else if(fieldType.equals("class java.util.Date"))
    {
     f.set(obj, rs.getTimestamp(fieldName));
    }
    f.setAccessible(false);
   }
  }catch(Exception e)
  {
   e.printStackTrace();
  }
  return obj;
 }
 
 /**
  * 取得�?��BEAN里的�?��属�?的名称在数据表里的字段对映名�?
  *
  * @param obj
  * @return
  */
 private static Map<String,String> sqlKey(final Object obj)
 {
  Class clazz = obj.getClass();
  if(properMap.get(clazz.getName()) == null)
  {
   Map<String,String> map=new HashMap<String,String>();
   Field[] fields=clazz.getDeclaredFields();
   for(Field f:fields)
   {
    f.setAccessible(true);
    map.put(f.getName(),beanNameConvertSqlKey(f.getName()));
    f.setAccessible(false);
   }
   properMap.put(clazz.getName(), map);
   return map;
  }else
  {
   return properMap.get(clazz.getName());
  }
 }
 
 /**
  * 将BEAN里的属�?名称转换成数据库里的字段名称，例如memberEmail => member_email
  *
  * @param beanName
  * @return
  */
 private static String beanNameConvertSqlKey(final String beanName)
 {
  char chars[]=beanName.toCharArray();
  StringBuffer sb=new StringBuffer();
  for(char c:chars)
  {
   sb.append(Character.isUpperCase(c)? "_"+(char)(c + 32):c);
  }
  return sb.toString();
 }
 
}