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

?

總結

以上是生活随笔為你收集整理的com.css.common.jdbcTemplate中的类的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。