Spring JdbcTemplate框架（二）——动态建表

  本篇博客使用Spring JdbcTemplate实现动态建表。前面介绍了，它封装了数据库的基本操作，让我们使用起来更加灵活，下面来实战。

 

1、准备工作

引入jar包

2、applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:aop="http://www.springframework.org/schema/aop"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
           http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd
           http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd">

	<!-- JDBC 操作模板 --> 
	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<constructor-arg>
			<ref bean="dataSource"/>
		</constructor-arg>
	</bean>		
 <!-- 配置数据库连接 -->
     <bean id="dataSource"  
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />  
         <property name="url" value="jdbc:mysql://localhost:3306/dynamic" />  
        <property name="username" value="root" />  
         <property name="password" value="123456" />  
     </bean>   
</beans>

3、代码

private static ApplicationContext context = null;
	//通过测试类测试
    public static void main(String[] args) {  
        context = new ClassPathXmlApplicationContext("applicationContext.xml");       
        Users user = new Users();  
        user.setUserName("liutengteng");  
        user.setUserPass("liutengteng");         
        int re = insertObject("users",user);  
        System.out.println("================" + re + "====================");  
    } 
    /**
     * 创建表，添加记录
     * @param tableName
     * @param obj
     * @return
     */
    public static int insertObject(String tableName,Object obj){  
        int re = 0;       
        try {  
            JdbcTemplate jt = (JdbcTemplate)context.getBean("jdbcTemplate");  
            SimpleDateFormat format = new SimpleDateFormat("yyyy_MM");            
            String tname = tableName + "_" + format.format(new Date());  
            // 判断数据库是否已经存在这个名称的表，如果有某表  则保存数据；否则动态创建表之后再保存数据
            if(getAllTableName(jt,tname)){  
                re = saveObj(jt,tname,obj);  
            }else{  
                re = createTable(jt,tname,obj);  
                re = saveObj(jt,tname,obj);  
            }             
        } catch (Exception e) {  
            e.printStackTrace();  
        }         
        return re;  
    } 
    /** 
     * 根据表名称创建一张表 
     * @param tableName 
     */  
    public static int createTable(JdbcTemplate jt,String tableName,Object obj){  
        StringBuffer sb = new StringBuffer("");  
        sb.append("CREATE TABLE `" + tableName + "` (");  
        sb.append(" `id` int(11) NOT NULL AUTO_INCREMENT,");          
        Map<String,String> map = ObjectUtil.getProperty(obj);  
        Set<String> set = map.keySet();  
        for(String key : set){  
            sb.append("`" + key + "` varchar(255) DEFAULT '',");  
        }         
        sb.append(" `tableName` varchar(255) DEFAULT '',");  
        sb.append(" PRIMARY KEY (`id`)");  
        sb.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8;");  
        try {  
            jt.update(sb.toString());  
            return 1;  
        } catch (Exception e) {  
            e.printStackTrace();  
        }  
        return 0;  
    }     
    
    /** 
     * 拼接语句，往表里面插入数据
     */  
    public static int saveObj(JdbcTemplate jt,String tableName,Object obj){  
        int re = 0;  
        try{              
            String sql = " insert into " + tableName + " (";  
            Map<String,String> map = ObjectUtil.getProperty(obj);  
            Set<String> set = map.keySet();  
            for(String key : set){  
                sql += (key + ",");  
            }  
            sql += " tableName ) ";               
            sql += " values ( ";  
            for(String key : set){  
                sql += ("'" + map.get(key) + "',");  
            }  
            sql += ("'" + tableName + "' ) ");  
            re = jt.update(sql);          
        } catch (Exception e) {  
            e.printStackTrace();  
        }         
        return re;  
    }     
    
   
    /** 
     * 查询数据库是否有某表 
     * @param cnn 
     * @param tableName 
     * @return 
     * @throws Exception 
     */  
    @SuppressWarnings("unchecked")  
    public static boolean getAllTableName(JdbcTemplate jt,String tableName) throws Exception {  
        Connection conn = jt.getDataSource().getConnection();  
        ResultSet tabs = null;  
        try {  
            DatabaseMetaData dbMetaData = conn.getMetaData();  
            String[]   types   =   { "TABLE" };  
            tabs = dbMetaData.getTables(null, null, tableName, types);  
            if (tabs.next()) {  
                return true;  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            tabs.close();  
            conn.close();  
        }  
        return false;  
    }

4、总结

          通过这种方式，让我们更加灵活的运用，但是也有弊端，如果系统的代码量很大，用最基本的这套框架就会有很多重复性的代码，这时就需要一层层的抽象，封装。抽象之后让代码的复用性更高。其实每一套框架也是抽象封装来的，不断的抽象封装，让我们的代码更灵活，质量更高。