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

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.