用JDBCTemplate實現的單表通用DAO,實現增刪改查和統計 – JAVA編程語言程序開發技術文章

[java]
package com.nbport.xk.dao; 
 
import java.beans.PropertyDescriptor; 
import java.lang.reflect.InvocationTargetException; 
import java.lang.reflect.Method; 
import java.text.SimpleDateFormat; 
import java.util.ArrayList; 
import java.util.List; 
 
import org.springframework.beans.BeanWrapper; 
import org.springframework.beans.BeanWrapperImpl; 
import org.springframework.jdbc.core.RowMapper; 
 
public class Table<T,PK> { 
     
    //bean類型 
    private Class<?> beanClass = null; 
     
    //表名 
    private String tableName = null; 
     
    //主鍵名 
    private String tableKey = null; 
     
    //用數據庫自動創建的增長ID 
    private boolean autoGeneratePK = false; 
     
    //時間格式化 
    private SimpleDateFormat dateFormat = null; 
     
    //rowMapper 
    private RowMapper<T> rowMapper = null; 
     
    class TableCache{ 
         
        private String insertSqlPrefix = null; 
         
        private String deleteSqlPrefix = null; 
         
        private String updateSqlPrefix = null; 
         
        private String searchSqlPrefix = null; 
         
        private String searchByIDSqlPrefix = null; 
         
        private String countSqlPrefix = null; 
         
        private String countByConditionSqlPrefix = null; 
         
        private List<PropertyDescriptor> properties = null; 
         
        private PropertyDescriptor keyDescriptor = null; 
         
        private Method keySetMethod = null; 
         
        private Method keyGetMethod = null; 
         
        public TableCache(){ 
             
        } 
 
        public String getInsertSqlPrefix() { 
            return insertSqlPrefix; 
        } 
 
        public void setInsertSqlPrefix(String insertSqlPrefix) { 
            this.insertSqlPrefix = insertSqlPrefix; 
        } 
 
        public String getDeleteSqlPrefix() { 
            return deleteSqlPrefix; 
        } 
 
        public void setDeleteSqlPrefix(String deleteSqlPrefix) { 
            this.deleteSqlPrefix = deleteSqlPrefix; 
        } 
 
        public String getUpdateSqlPrefix() { 
            return updateSqlPrefix; 
        } 
 
        public void setUpdateSqlPrefix(String updateSqlPrefix) { 
            this.updateSqlPrefix = updateSqlPrefix; 
        } 
 
        public List<PropertyDescriptor> getProperties() { 
            return properties; 
        } 
 
        public void setProperties(List<PropertyDescriptor> properties) { 
            this.properties = properties; 
        } 
 
        public Method getKeySetMethod() { 
            return keySetMethod; 
        } 
 
        public void setKeySetMethod(Method keySetMethod) { 
            this.keySetMethod = keySetMethod; 
        } 
 
        public Method getKeyGetMethod() { 
            return keyGetMethod; 
        } 
 
        public void setKeyGetMethod(Method keyGetMethod) { 
            this.keyGetMethod = keyGetMethod; 
        } 
 
        public PropertyDescriptor getKeyDescriptor() { 
            return keyDescriptor; 
        } 
 
        public void setKeyDescriptor(PropertyDescriptor keyDescriptor) { 
            this.keyDescriptor = keyDescriptor; 
        } 
 
        public String getSearchSqlPrefix() { 
            return searchSqlPrefix; 
        } 
 
        public void setSearchSqlPrefix(String searchSqlPrefix) { 
            this.searchSqlPrefix = searchSqlPrefix; 
        } 
 
        public String getSearchByIDSqlPrefix() { 
            return searchByIDSqlPrefix; 
        } 
 
        public void setSearchByIDSqlPrefix(String searchByIDSqlPrefix) { 
            this.searchByIDSqlPrefix = searchByIDSqlPrefix; 
        } 
 
        public String getCountSqlPrefix() { 
            return countSqlPrefix; 
        } 
 
        public void setCountSqlPrefix(String countSqlPrefix) { 
            this.countSqlPrefix = countSqlPrefix; 
        } 
 
        public String getCountByConditionSqlPrefix() { 
            return countByConditionSqlPrefix; 
        } 
 
        public void setCountByConditionSqlPrefix(String countByConditionSqlPrefix) { 
            this.countByConditionSqlPrefix = countByConditionSqlPrefix; 
        } 
                 
    } 
     
    private TableCache cache = null; 
     
    public Table(Class<T> beanClass,RowMapper<T> rowMapper,String tableName,String tableKey,boolean autoGeneratePK,SimpleDateFormat dateFormat){ 
        this.beanClass = beanClass; 
        this.rowMapper = rowMapper; 
        this.tableName = tableName; 
        this.tableKey = tableKey; 
        this.autoGeneratePK = autoGeneratePK; 
        this.dateFormat = dateFormat; 
        init(); 
    } 
     
    public Table(Class<T> beanClass,RowMapper<T> rowMapper,String tableName,String tableKey){ 
        this(beanClass,rowMapper, tableName, tableKey, true, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")); 
    } 
     
    public void init() { 
        cache = new TableCache(); 
        BeanWrapper beanWrapper = null; 
        try { 
            beanWrapper = new BeanWrapperImpl(beanClass.newInstance()); 
        } catch (InstantiationException e) { 
            e.printStackTrace(); 
        } catch (IllegalAccessException e) { 
            e.printStackTrace(); 
        } 
        cache.setKeyDescriptor( beanWrapper.getPropertyDescriptor(tableKey) ); 
        cache.setProperties(new ArrayList<PropertyDescriptor>()); 
        {            
            for (PropertyDescriptor descriptor : beanWrapper.getPropertyDescriptors()) { 
                String name = descriptor.getName(); 
                if (!beanWrapper.isWritableProperty(name) 
                    || (getAutoGeneratePK() && name.compareToIgnoreCase(getTableKey())==0)){ 
                        continue; 
                    } 
                cache.getProperties().add(descriptor); 
            } 
        } 
        cache.setKeyGetMethod(cache.getKeyDescriptor().getReadMethod()); 
        cache.setKeySetMethod(cache.getKeyDescriptor().getWriteMethod()); 
        cache.setDeleteSqlPrefix("delete from "+tableName+" where "+tableKey+"="+getHoldFlag(cache.getKeyDescriptor())); 
        cache.setSearchByIDSqlPrefix("select * from "+tableName+" where "+tableKey+"="+getHoldFlag(cache.getKeyDescriptor())); 
        cache.setSearchSqlPrefix("select * from "+tableName+" where "); 
        cache.setCountSqlPrefix("select count(1) from "+tableName); 
        cache.setCountByConditionSqlPrefix("select count(1) from "+tableName+" where "); 
        { 
            StringBuffer tempColumns = new StringBuffer(); 
            StringBuffer tempValueHolds = new StringBuffer(); 
            for (PropertyDescriptor descriptor : cache.getProperties()) { 
                String name = descriptor.getName(); 
                if (!beanWrapper.isWritableProperty(name) 
                    || (getAutoGeneratePK() && name.compareToIgnoreCase(getTableKey())==0)){ 
                    continue; 
                } 
                tempColumns.append(name).append(","); 
                tempValueHolds.append(getHoldFlag(descriptor)).append(","); 
            } 
            cache.setInsertSqlPrefix("insert into "+tableName+"(" 
                    +tempColumns.substring(0,tempColumns.length()-1)+") values (" 
                    +tempValueHolds.substring(0,tempValueHolds.length()-1)+")");     
        } 
        { 
            StringBuffer tempColumns = new StringBuffer(); 
            for (PropertyDescriptor descriptor : cache.getProperties()) { 
                String name = descriptor.getName(); 
                if (!beanWrapper.isWritableProperty(name) 
                    || (getAutoGeneratePK() && name.compareToIgnoreCase(getTableKey())==0)){ 
                    continue; 
                } 
                tempColumns.append(name).append("=").append(getHoldFlag(descriptor)).append(","); 
            } 
            cache.setUpdateSqlPrefix("update "+tableName+" set "+tempColumns.substring(0,tempColumns.length()-1) 
                    +" where "+tableKey+"="+getHoldFlag(cache.getKeyDescriptor())); 
        } 
    } 
 
    public String getTableName() { 
        return tableName; 
    } 
 
    public void setTableName(String tableName) { 
        this.tableName = tableName; 
    } 
 
    public String getTableKey() { 
        return tableKey; 
    } 
 
    public void setTableKey(String tableKey) { 
        this.tableKey = tableKey; 
    } 
 
    public boolean getAutoGeneratePK() { 
        return autoGeneratePK; 
    } 
 
    public void setAutoGeneratePK(boolean autoGeneratePK) { 
        this.autoGeneratePK = autoGeneratePK; 
    } 
 
    public SimpleDateFormat getDateFormat() { 
        return dateFormat; 
    } 
 
    public void setDateFormat(SimpleDateFormat dateFormat) { 
        this.dateFormat = dateFormat; 
    } 
 
    public TableCache getCache() { 
        return cache; 
    } 
 
    public void setCache(TableCache cache) { 
        this.cache = cache; 
    } 
     
    public Class<?> getBeanClass() { 
        return beanClass; 
    } 
 
    public void setBeanClass(Class<?> beanClass) { 
        this.beanClass = beanClass; 
    } 
     
    public RowMapper<T> getRowMapper() { 
        return rowMapper; 
    } 
 
    public void setRowMapper(RowMapper<T> rowMapper) { 
        this.rowMapper = rowMapper; 
    } 
     
    public T setID(T instance,Object value) { 
        try { 
            cache.getKeySetMethod().invoke(instance, value); 
        } catch (IllegalArgumentException e) { 
            e.printStackTrace(); 
        } catch (IllegalAccessException e) { 
            e.printStackTrace(); 
        } catch (InvocationTargetException e) { 
            e.printStackTrace(); 
        } 
        return instance; 
    } 
     
     
    public PK getPK(T instance){ 
        PK pkValuePk = null; 
        try { 
            pkValuePk = (PK) cache.getKeyGetMethod().invoke(instance, null); 
        } catch (IllegalArgumentException e) { 
            e.printStackTrace(); 
        } catch (IllegalAccessException e) { 
            e.printStackTrace(); 
        } catch (InvocationTargetException e) { 
            e.printStackTrace(); 
        } 
        return pkValuePk; 
    } 
     
    public String createInsertSql(T instance){ 
        String sql = cache.getInsertSqlPrefix(); 
        for (PropertyDescriptor descriptor : cache.getProperties()) { 
            try { 
                sql=sql.replaceFirst("[?]", descriptor.getReadMethod().invoke(instance, null).toString()); 
            } catch (IllegalArgumentException e) { 
                e.printStackTrace(); 
            } catch (IllegalAccessException e) { 
                e.printStackTrace(); 
            } catch (InvocationTargetException e) { 
                e.printStackTrace(); 
            } 
        } 
        return sql; 
    } 
     
    public String createUpdateSql(T instance){ 
        String sql = cache.getUpdateSqlPrefix(); 
        for (PropertyDescriptor descriptor : cache.getProperties()) { 
            try { 
                sql=sql.replaceFirst("[?]", descriptor.getReadMethod().invoke(instance, null).toString()); 
            } catch (IllegalArgumentException e) { 
                e.printStackTrace(); 
            } catch (IllegalAccessException e) { 
                e.printStackTrace(); 
            } catch (InvocationTargetException e) { 
                e.printStackTrace(); 
            } 
        } 
        return sql.replaceFirst("[?]",getPK(instance).toString()); 
    } 
     
    public String createDeleteSql(T instance){ 
        String sql = cache.getDeleteSqlPrefix(); 
        return sql.replaceFirst("[?]",getPK(instance).toString()); 
    } 
     
    public String createDeleteSqlByPKValue(PK pkValue){ 
        String sql = cache.getDeleteSqlPrefix(); 
        return sql.replaceFirst("[?]",pkValue.toString()); 
    } 
     
    public String createSearchSqlByPKValue(PK pkValue){ 
        String sql = cache.getSearchByIDSqlPrefix(); 
        return sql.replaceFirst("[?]",pkValue.toString()); 
    } 
     
    public String createSearchSqlByCondition(String condition){ 
        String sql = cache.getSearchSqlPrefix(); 
        return sql+condition; 
    } 
     
    public String createCountSql() { 
        return cache.getCountSqlPrefix(); 
    } 
     
    public String createCountSqlByCondition(String condition){ 
        return cache.getCountByConditionSqlPrefix()+condition; 
    } 
     
    private String getHoldFlag(PropertyDescriptor descriptor) { 
        return (isNeedWarpQuotes(descriptor)==true?"'?'":"?"); 
    } 
     
    private boolean isNeedWarpQuotes(PropertyDescriptor descriptor){ 
        String type = descriptor.getPropertyType().getSimpleName(); 
        if (type.compareToIgnoreCase("String")==0) { 
            return true; 
        }else if (type.compareToIgnoreCase("Date")==0) { 
            return true; 
        }else{ 
            return false; 
        } 
    } 
     

[java]
<pre name="code" class="java">package com.nbport.xk.dao; 
[java]
import java.sql.Connection; 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.List; 
 
import org.springframework.beans.factory.annotation.Autowired; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.jdbc.core.PreparedStatementCreator; 
import org.springframework.jdbc.core.RowMapperResultSetExtractor; 
import org.springframework.jdbc.support.GeneratedKeyHolder; 
import org.springframework.jdbc.support.KeyHolder; 
import org.springframework.stereotype.Component; 
import org.springframework.transaction.annotation.Propagation; 
import org.springframework.transaction.annotation.Transactional; 
 
/**
 * @author xiakan
 * @version 1.0
 */ 
@Component 
@Transactional(propagation=Propagation.SUPPORTS,readOnly=true) 
public abstract class BaseDao<T,PK> { 
     
    @Autowired 
    protected JdbcTemplate template = null; 
     
    protected Table<T,PK> table = null; 
     
    public BaseDao(){ 
         
    } 
     
    public Table<T,PK> getTable() { 
        return table; 
    } 
 
    public void setTable(Table<T,PK> table) { 
        this.table = table; 
    } 
 
    public JdbcTemplate getTemplate() { 
        return template; 
    } 
 
    public void setTemplate(JdbcTemplate template) { 
        this.template = template; 
    } 
     
    @Transactional(propagation=Propagation.REQUIRED,readOnly=false) 
    public T insert(T item){ 
        final KeyHolder keyHolder = new GeneratedKeyHolder(); 
        final String sql = table.createInsertSql(item); 
        template.update(new PreparedStatementCreator(){ 
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException { 
                PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); 
                return ps; 
                } 
            }, keyHolder); 
        table.setID(item,keyHolder.getKey().longValue()); 
        return item; 
    } 
     
    @Transactional(propagation=Propagation.REQUIRED,readOnly=false) 
    public int update(T item){ 
        String sql = table.createUpdateSql(item); 
        return template.update(sql); 
    } 
     
    @Transactional(propagation=Propagation.REQUIRED,readOnly=false) 
    public int deleteItem(T item){ 
        String sql = table.createDeleteSql(item); 
        return template.update(sql); 
    } 
     
    @Transactional(propagation=Propagation.REQUIRED,readOnly=false) 
    public int delete(PK pkValue){ 
        String sql = table.createDeleteSqlByPKValue(pkValue); 
        return template.update(sql); 
    } 
     
    public T searchByID(PK pkValue){ 
        String sql = table.createSearchSqlByPKValue(pkValue); 
        List<T> list = template.query(sql,new RowMapperResultSetExtractor<T>(table.getRowMapper())); 
        return (null!=list&&list.size()==1?list.get(0):null); 
    } 
     
    public List<T> search(String condition){ 
        String sql = table.createSearchSqlByCondition(condition); 
        return template.query(sql,new RowMapperResultSetExtractor<T>(table.getRowMapper())); 
    } 
     
    public Long count(){ 
        String sql = table.createCountSql(); 
        return template.queryForLong(sql); 
    } 
     
    public Long count(String condition){ 
        String sql = table.createCountSqlByCondition(condition); 
        return template.queryForLong(sql); 
    } 
     

testcase:
[java]
package com.nbport.ctos2.dao; 
 
import java.util.List; 
 
import org.apache.commons.lang.RandomStringUtils; 
 
import com.nbport.ctos2.common.AppDefine; 
import com.nbport.ctos2.dao.ContainerDao; 
import com.nbport.ctos2.entity.Container; 
 
import junit.framework.Assert; 
import junit.framework.TestCase; 
 
public class ContainerDaoTest extends TestCase{ 
     
    public void testInsert() { 
        ContainerDao containerDao = AppDefine.getAppContext().getBean(ContainerDao.class); 
        for (int i = 0; i < 1; i++) { 
            Container c = new Container(); 
            c.setContainerID("APLX"+RandomStringUtils.randomNumeric(7)); 
            c.setService("APL"); 
            c.setType("45"); 
            Assert.assertNotNull(containerDao.insert(c)); 
        } 
    } 
     
    public void testInsert2() { 
        ContainerDao containerDao = AppDefine.getAppContext().getBean(ContainerDao.class); 
        for (int i = 0; i < 1; i++) { 
            Container c = new Container(); 
            c.setContainerID("APLX"+RandomStringUtils.randomNumeric(7)); 
            c.setService("APL"); 
            c.setType("45"); 
            Assert.assertNotNull(containerDao.insert(c)); 
        } 
    } 
     
    public void testUpdate() { 
        ContainerDao containerDao = AppDefine.getAppContext().getBean(ContainerDao.class); 
        for (int i = 0; i < 1; i++) { 
            Container c = new Container(); 
            c.setContainerID("APLX"+RandomStringUtils.randomNumeric(7)); 
            c.setService("APL"); 
            c.setType("45"); 
            Assert.assertNotNull(containerDao.insert(c)); 
            c.setType("22"); 
            Assert.assertEquals(1,containerDao.update(c)); 
        } 
    } 
     
    public void testDelete() { 
        ContainerDao containerDao = AppDefine.getAppContext().getBean(ContainerDao.class); 
        for (int i = 0; i < 1; i++) { 
            Container c = new Container(); 
            c.setContainerID("APLX"+RandomStringUtils.randomNumeric(7)); 
            c.setService("APL"); 
            c.setType("45"); 
            Assert.assertNotNull(containerDao.insert(c)); 
            c.setType("22"); 
            Assert.assertEquals(1,containerDao.deleteItem(c)); 
        } 
    } 
     
    public void testSearchByID() { 
        ContainerDao containerDao = AppDefine.getAppContext().getBean(ContainerDao.class); 
        for (int i = 0; i < 1; i++) { 
            Container c = new Container(); 
            c.setContainerID("APLX"+RandomStringUtils.randomNumeric(7)); 
            c.setService("APL"); 
            c.setType("45"); 
            Assert.assertNotNull(containerDao.insert(c)); 
            c.setType("22"); 
            Assert.assertEquals(c.getId(),containerDao.searchByID(c.getId()).getId()); 
        } 
    } 
     
    public void testSearchByCondition() { 
        ContainerDao containerDao = AppDefine.getAppContext().getBean(ContainerDao.class); 
        for (int i = 0; i < 10; i++) { 
            Container c = new Container(); 
            c.setContainerID("APLX"+RandomStringUtils.randomNumeric(7)); 
            c.setService("APL"); 
            c.setType("45"); 
            Assert.assertNotNull(containerDao.insert(c)); 
        } 
        List<Container> list = containerDao.search("containerid like 'APLX%'"); 
        Assert.assertTrue(list.size()>0); 
    } 
     
    public void testCount() { 
        ContainerDao containerDao = AppDefine.getAppContext().getBean(ContainerDao.class); 
        for (int i = 0; i < 10; i++) { 
            Container c = new Container(); 
            c.setContainerID("APLX"+RandomStringUtils.randomNumeric(7)); 
            c.setService("APL"); 
            c.setType("45"); 
            Assert.assertNotNull(containerDao.insert(c)); 
        } 
        Assert.assertTrue(containerDao.count()>9); 
    } 
     
    public void testCountByCondition() { 
        ContainerDao containerDao = AppDefine.getAppContext().getBean(ContainerDao.class); 
        for (int i = 0; i < 10; i++) { 
            Container c = new Container(); 
            c.setContainerID("APLX"+RandomStringUtils.randomNumeric(7)); 
            c.setService("APL"); 
            c.setType("45"); 
            Assert.assertNotNull(containerDao.insert(c)); 
        } 
        Assert.assertTrue(containerDao.count("type='45'")>9); 
    } 
     

摘自 xiakan008的專欄

發佈留言