JdbcTemplate的使用

前段时间由于工作原因，需要编写一个制造测试数据（mysql）的工具类，网上搜了下，说JdbcTemplate不错，于是乎就准备使用下。为方便调用者无需了解JdbcTemplate，于是在此之上封装了一层，分别实现了增、删、查，且可批量操作，在封装批量新增方法时，费了点劲，最后用反射实现的。代码如下：
接口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);
}

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

亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>