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、总结

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