JdbcTemplate的使用

前段時(shí)間由于工作原因，需要編寫一個(gè)制造測(cè)試數(shù)據(jù)（mysql）的工具類，網(wǎng)上搜了下，說JdbcTemplate不錯(cuò)，于是乎就準(zhǔn)備使用下。為方便調(diào)用者無需了解JdbcTemplate，于是在此之上封裝了一層，分別實(shí)現(xiàn)了增、刪、查，且可批量操作，在封裝批量新增方法時(shí)，費(fèi)了點(diǎn)勁，最后用反射實(shí)現(xiàn)的。代碼如下：
接口DataBaseDAO:

package com.wb.hz.test.util;
import java.util.List;
public interface DataBaseDAO {
    public void delete(String tableName,String fieldName,Object id);
    public void delete(String tableName,String fieldName,List<?> ids);
    public int  statisticsById(String tableName,String fieldName,Object id);
    public <T> List<?> queryById(String tableName, String fieldName, Object id,boolean desc);
    public <T> List<?> queryByIds(String tableName,String fieldName,Object startid,Object endid,boolean desc);
    public <T> void insert(String sql,List<T> list);
}

實(shí)現(xiàn)類DataBaseDAOImpl

package com.wb.hz.test.util.impl;
import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import com.wb.hz.test.util.DataBaseDAO;
public class DataBaseDAOImpl implements DataBaseDAO {
                            
    @Resource
    private JdbcTemplate jdbcTemplate;
                            
    public void delete(String tableName, String fieldName, Object id) {
        String sql="delete from "+tableName+" where "+fieldName+"=?";
        jdbcTemplate.update(sql, id);
    }
    public void delete(String tableName, String fieldName, List<?> ids) {
        String sql="delete from "+tableName+" where "+fieldName+"=?";
        for(int i=0;i<ids.size();i++){
            jdbcTemplate.update(sql, ids.get(i));
        }
    }
    public <T> List<Map<String,Object>> queryById(String tableName, String fieldName, Object ids, boolean desc) {
        String sql="select * from "+tableName+" where "+fieldName+"="+String.valueOf(ids);
        if(desc==true){
            sql=sql+" order by id desc";
        }
        List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
                                
        return list;
    }
                            
                            
    public int statisticsById(String tableName,String fieldName ,Object id) {
        String sql="select count(*) from "+tableName+" where "+fieldName+"="+String.valueOf(id);
        int num=jdbcTemplate.queryForInt(sql);
        return num;
    }
    public <T> List<?> queryByIds(String tableName,String fieldName, Object startid, Object endid,boolean desc) {
        String sql="select * from "+tableName+" where "+fieldName+" >= "+String.valueOf(startid)+"and "+fieldName+" <= "+String.valueOf(endid);
        if(desc==true){
            sql=sql+" order by id desc";
        }
        List<Map<String, Object>> list=jdbcTemplate.queryForList(sql);
        return list;
    }
    public <T> void insert(String sql, List<T> objlist) {
        final List<T> list=objlist;
        BatchPreparedStatementSetter setter=new BatchPreparedStatementSetter(){  
                                        
                public int getBatchSize(){  
                    return list.size();  
                }  
                public void setValues(PreparedStatement ps,int index) throws SQLException{  
                    T t=list.get(index);  
                    Field fields[]=t.getClass().getDeclaredFields();
                    try {
                        for(int i=0;i<fields.length;i++){
                            PropertyDescriptor  prop=new PropertyDescriptor(fields[i].getName(),t.getClass());
                            Method getmethod=prop.getReadMethod();
                            if(fields[i].getType().getCanonicalName().equalsIgnoreCase("java.lang.String")){
                                ps.setString(i+1, String.valueOf(getmethod.invoke(t)));
                                //System.out.println(ps.getResultSet().getString(i+1));
                            }
                            else if(fields[i].getType().getCanonicalName().equalsIgnoreCase("int")){
                                ps.setInt(i+1, (Integer)getmethod.invoke(t));
                                //System.out.println(ps.getResultSet().getInt(i+1));
                            }
                            else if(fields[i].getType().getCanonicalName().equalsIgnoreCase("long")){
                                ps.setLong(i+1, (Long) getmethod.invoke(t));
                                //System.out.println(ps.getResultSet().getLong(i+1));
                            }
                            else if(fields[i].getType().getCanonicalName().equalsIgnoreCase("double")){
                                ps.setDouble(i+1, (Double) getmethod.invoke(t));
                                //System.out.println(ps.getResultSet().getDouble(i+1));
                            }
                            else if(fields[i].getType().getCanonicalName().equalsIgnoreCase("java.lang.Integer")){
                                ps.setInt(i+1, (Integer)getmethod.invoke(t));
                                //System.out.println(ps.getResultSet().getInt(i+1));
                            }
                            else if(fields[i].getType().getCanonicalName().equalsIgnoreCase("java.lang.Long")){
                                ps.setLong(i+1, (Long) getmethod.invoke(t));
                                //System.out.println(ps.getResultSet().getLong(i+1));
                            }
                            else if(fields[i].getType().getCanonicalName().equalsIgnoreCase("java.lang.Double")){
                                ps.setDouble(i+1, (Double) getmethod.invoke(t));
                                //System.out.println(ps.getResultSet().getDouble(i+1));
                            }
                            else if(fields[i].getType().getCanonicalName().equalsIgnoreCase("java.util.Date")){
                                ps.setDate(i+1,   new java.sql.Date(((Date)getmethod.invoke(t)).getTime()));
                                //System.out.println(ps.getResultSet().getDate(i+1));
                            }
                        }
                    } catch (IllegalAccessException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } catch (IllegalArgumentException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } catch (InvocationTargetException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } catch (IntrospectionException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                } 
            };  
            jdbcTemplate.batchUpdate(sql,setter);  
                                
    }
}

寫完后，試驗(yàn)了一下，這些方法都沒問題！于是被當(dāng)做工具類使用，誰知?jiǎng)傆脹]多久，問題曝露了，對(duì)某表調(diào)用批量新增方法插入數(shù)據(jù)報(bào)錯(cuò)，提示某個(gè)值對(duì)應(yīng)的字段不對(duì)，可是這個(gè)字段值實(shí)際對(duì)應(yīng)的字段并非異常中顯示的，初步判斷肯定是字段映射出了問題，重新檢查了java反射的那段代碼，發(fā)現(xiàn)沒問題呀！于是冷靜分析，決定從頭排查，發(fā)現(xiàn)傳入的sql語句為：insert into tablename values(?,?,?,?,?,?,?); tablename后沒有寫明字段，于是根據(jù)傳入的vo對(duì)象中定義的字段屬性的順序?qū)⑦@些字段對(duì)應(yīng)的表中字段名寫入sql,再次執(zhí)行，不報(bào)錯(cuò)了！看來實(shí)現(xiàn)類還需要去解析表字段并將傳入的對(duì)象屬性進(jìn)行映射才能避免這種情況發(fā)生，否則這個(gè)方法非常不智能??！