Spring JdbcTemplate FRAME (2)--Dynamic build table

This blog uses Spring jdbctemplate to implement dynamic table-building. Described earlier, it encapsulates the basic operation of the database, let us use more flexible, the following to combat.

1. Preparatory work

introduced Jar Package

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.springfram Ework.org/schema/tx/spring-tx-2.0.xsd "><!--JDBC Action template--<bean id=" JdbcTemplate "class=" Org.springframework.jdbc.core.JdbcTemplate "><constructor-arg><ref bean=" DataSource "/></ constructor-arg></bean> <!--configuration database connection-<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"/> </be An> </beans>

3. Code

private static ApplicationContext context = null;//test public static void main (string[] args) by test class {context =               New Classpathxmlapplicationcontext ("Applicationcontext.xml");          Users user = new users ();          User.setusername ("Liutengteng");                 User.setuserpass ("Liutengteng");          int re = InsertObject ("Users", user);      System.out.println ("================" + Re + "===================="); }/** * CREATE TABLE, add record * @param tableName * @param obj * @return */public static int InsertObject (St               Ring 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 ()); Determine whether the database already exists in the name of the table, if there is a table to save data, otherwise dynamically create the table and then save the data 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; /** * Create a table based on the table name * @param tableName */public static int createtable (JdbcTemplate jt,string tabl          Ename,object obj) {stringbuffer sb = new StringBuffer ("");          Sb.append ("CREATE TABLE" + TableName + "' (");                  Sb.append ("' ID ' int (one) 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;           }/** * Splicing statement, insert data into table */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;     }           /** * Query database for a table * @param CNN * @param tableName * @return * @throws Exception */ @SuppressWarnings ("unchecked") public static Boolean getalltablename (JdbcTemplate jt,string tableName) throws E          xception {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. Summary

in this way, let us more flexible use, but there are drawbacks, if the system code is very large, with the most basic framework will have a lot of repetitive code, then need a layer of abstraction, encapsulation. After abstraction, the reusability of the code is higher. In fact, each set of frameworks is also abstract encapsulation, the continuous abstraction package, so that our code more flexible, higher quality.

Spring JdbcTemplate FRAME (2)--Dynamic build table