Spring JdbcTemplate framework (2) -- Dynamic table creation, springjdbctemplate

Spring JdbcTemplate framework (2) -- Dynamic table creation, springjdbctemplate

This blog uses Spring JdbcTemplate for dynamic table creation. As described above, it encapsulates the basic operations of the database and makes it more flexible to use. The following is a practical example.

 

1. Preparations

Introduce 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.spri Ngframework.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 operation template --> <bean id = "jdbcTemplate" class = "org. springframework. jdbc. core. jdbcTemplate "> <constructor-arg> <ref bean =" dataSource "/> </constructor-arg> </bean> <! -- Configure 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 "/> </bean> </ beans>

3. Code

Private static ApplicationContext context = null; // test 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 + "====================== ==== ");} /*** create a table and add the record * @ 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 (); // determines whether a table with this name already exists in the Database. If a table exists, the data is saved; otherwise, after the table is dynamically created, 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 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;}/*** concatenate statement to insert data into the 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 ;} /*** check whether a table exists in the Database * @ 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. Summary

This method makes us more flexible to use, but it also has drawbacks. If the system has a large amount of code, there will be a lot of repetitive code with the most basic framework, in this case, layers of abstraction and encapsulation are required. After abstraction, the code is more reusable. In fact, each set of frameworks is also abstracted and encapsulated, so that our code is more flexible and the quality is higher.