Spring JDBC Framework detailed--Batch JDBC operations, ORM Mapping

First, Spring JDBC overview

Spring provides a powerful template class JdbcTemplate simplifies JDBC operations, and datasource,jdbctemplate can be defined in a bean-like XML configuration file. JdbcTemplate create simply inject a DataSource, the application DAO layer only needs to inherit jdbcdaosupport, or inject jdbctemplate, you can get JdbcTemplate, JdbcTemplate is a thread-safe class in which multiple DAO can be injected into a jdbctemplate;

<!--Oracle data source--> <bean id= "DataSource class=" Org.apache.commons.dbcp.BasicDataSource "destroy-method=" Close "> <property name=" driverclassname "value=" Oracle.jdbc.driver.OracleDriver "/> <property" url " Value= "Jdbc:oracle:thin: @oracle. Devcake.co.uk:1521:intl"/> <property name= "username" value= "sa"/> < Property name= "Password" value= "/> </bean> <bean id=" JdbcTemplate "class=" Org.springframework.jdbc.core.JdbcTemplate "> <property name=" dataSource "ref=" DataSource "/> </bean> <!--set injection method to get JdbcTemplate--> <bean id= "Customerdao" class= "Jdbccustomerdao" > <property name= " JdbcTemplate "ref=" JdbcTemplate "/> </bean> <!--injected Datasource,customerdao through inheritance jdbcdaosupport, Use This.getjdbctemplate () to get JdbcTemplate--> <bean id= "Customerdao" class= "Jdbccustomerdao" > <property Name= "DataSource" ref= "DataSource"/> </bean>

The JdbcTemplate object is then injected into the custom DAO, or inherited Jdbcdaosupport, for example: Java code public class Jdbccustomerdao extends Jdbcdaosupport Implements Customerdao {} public class Jdbccustomerdao implements Customerdao {private Jdbctemplat e jdbctemplate public void setjdbctemplate () jdbctemplate jdbctemplate{THIS.JDBCTEMPLATE=JD Bctemplate}}

Second, JdbcTemplate provides the following main ways to simplify JDBC operations:

2.1. List query (String sql,ojbect[] args,rowmapper rowmapper)

     Description: Commonly used queries, SQL to execute SQL statements, args is the parameters of SQL statements, RowMapper is responsible for each row of records into Java objects stored in the list, and eventually returned, for example: Java code   Public list<book> querybyauthor (string author)  {            String sql =  "select * from book where  Author=? ";            collection c = getjdotemplate (). Find (sql,                    new object[] { author },new bookrowmapper ());            List<Book> books = new ArrayList<Book> ();            books.addall (c);            return books;  }      Class bookrowmapper  implements rowmapper{        public object maprow (ResultSet  res, int index)  throws SQLException {              book book = new book ();              book.setid (Rs.getint ("id"));              //omitted set          return book;        }  }  

Updates, deletes, and other query operations are similar, for example, for details refer to the Spring API:

The Java code//return value is a long shaping public long getaverageage () {returns Getjdbctemplate (). queryForLong ("Select AVG." From Empl     Oyee "); //Returns an integer public int gettotalnumberofemployees () {return getjdbctemplate (). queryForInt ("Select COUNT (0) from E"     Mployees ");            }//Update operation This.jdbcTemplate.update (INSERT into T_actor (first_name, surname) values (?,?) ", New object[] {"Leonor", "Watling"});

2.2, Spring 2.5 new features, alternative JDBC Orm:beanpropertyrowmapper

We must implement RowMapper to convert the result set to Java object when we retrieve it. Spring2.5 simplifies this operation, so that we do not have to implement RowMapper, the two magic things to achieve this function is: Parameterizedrowmapper,parameterizedbeanpropertyrowmapper, Seemingly through the Java reflection mechanism to map the ResultSet field to Java objects, but the columns of the datasheet must correspond to the properties of the Java object, there is no source of research, a bit similar to the Apache Beanutil, I wonder why this part in the Spring Development Reference manual does not, Isn't it a classic?

Java code//using Parameterizedbeanpropertyrowmapper @SuppressWarnings ({"Unchecked"}) public list<customer> getAll () {return getjdbctemplate (). Query ("SELECT * T_customer", parameterizedbeanpropertyrowmapper.newinstance (Custo     Mer.class)); }//Using Beanpropertyrowmapper @SuppressWarnings ({"Unchecked"}) public list<customer> GetAll () {RET     Urn getjdbctemplate (). Query ("SELECT * from T_customer", New Beanpropertyrowmapper (Customer.class)); }

Note: Parameterizedbeanpropertyrowmapper is a beanpropertyrowmapper subclass. The field name of the table must be the same as the member variable name of the entity class;

2.3. The JDBC batch operation of Spring

Jdbctemplate.batchupdate (final string[] SQL), API explanation: Issue Multiple SQL updates on a single JDBC Statement using batching, flip The solution is roughly: to solve multiple SQL inserts, updates, deletes in a statement. Performance in general.

Jdbctemplate.batchupdate (String sql, Final BatchPreparedStatementSetter PSS), similar to JDBC's PreparedStatement, has improved performance.

    we illustrate how to use, examples are as follows: Java code   final int count = 2000;        final List<String> firstNames = new ArrayList<String> ( Count);       final List<String> lastNames = new  Arraylist<string> (count);       for  (int i = 0; i  < count; i++)  {         firstnames.add ("the  Name  " + i);         lastnames.add (" Last Name   " + i);       }        Jdbctemplate.batchupdate (               ) insert into customer  (id, first_name, last_name, last_login, comments)  values  (?, ?,  ?, ?, ?) ",                new batchpreparedstatementsetter ()  {               //sets the parameters for the prepared statement. The number of times this method will be invoked throughout the process            public void setvalues ( Preparedstatement ps, int i)  throws SQLException {                    ps.setlong (1, i + &NBSP;10);                    ps.setstring (2, firstnames.get (i));                    ps.setstring (3, lastnames.get (i));                 &Nbsp; ps.setnull (4, types.timestamp);                    ps.setnull (5, types.clob);                  }                  //returns the updated result set bar               public int getbatchsize ()  {                       return count;                  }   &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP});     }   

Batchsqlupdate class is a sqlupdate subclass, suitable for inserting, deleting, updating bulk operation, internal use PreparedStatement, so the efficiency is very high, the batch statement achieves the set batchsize, or manually call flush to perform a bulk operation. Note: This class is not thread-safe, you must create an instance for each consumer, or call reset before you use it in the same thread.

   below we illustrate how to use batchsqlupdate to perform bulk operations. Examples are as follows: Java code   class batchinsert extends batchsqlupdate {     private  static final String SQL =  "insert into t_customer  (id,  first_name, last_name, last_login,  "         + " Comments)  values  (?,  ?, ?, ?, null) ";         Batchinsert (Datasource datasource)  {       super (datasource, sql);        declareparameter (New sqlparameter (Types.integer));        declareparameter (New sqlparameter (Types.varchar));        Declareparameter (New sqlparameter (Types.varchar));       declareparameter ( New sqlparameter (Types.timestamp));        &Nbsp; setbatchsize (a);     }     }  

Java code int count = 5000;       for (int i = 0; i < count; i++) {batchinsert.update (new object[] {i + 100L, "a" + I, "B" + I, null}); }

So far, the main application of spring JDBC is basically a simple list, all the code for the article for example, not very rigorous, only for the demonstration of each usage, the hope has a unique view of the brick, there are problems please indicate the problem, thank you