Open Tao Spring3 (7.4)-Support for JDBC 7.4 Additional help provided by spring

7.4 Spring offers additional help

7.4.1 Simplejdbc Way

The Spring JDBC Abstraction Framework provides the Simplejdbcinsert and Simplejdbccall classes, which simplify JDBC operations by leveraging the database metadata provided by the JDBC driver.

1. Simplejdbcinsert: For inserting data, inserting data according to database metadata, this class is used to simplify the insert operation, and provides three types of methods: The Execute method is used for normal insertion, The Executeandreturnkey and Executeandreturnkeyholder methods are used to get the primary key value when inserting, and the ExecuteBatch method is used for batch processing.

@Test Public voidTestsimplejdbcinsert () {Simplejdbcinsert Insert=NewSimplejdbcinsert (JdbcTemplate); Insert.withtablename ("Test"); Map<string, object> args =NewHashmap<string, object>(); Args.put ("Name", "Name5");          Insert.compile (); //1. Normal insertionInsert.execute (args); Assert.assertequals (1, Jdbctemplate.queryforint ("SELECT count (*) from Test")); //2. Get primary key value when insertingInsert =NewSimplejdbcinsert (JdbcTemplate); Insert.withtablename ("Test"); Insert.setgeneratedkeyname ("id"); Number ID=Insert.executeandreturnkey (args); Assert.assertequals (1, id); //3. Batch processingInsert =NewSimplejdbcinsert (JdbcTemplate); Insert.withtablename ("Test"); Insert.setgeneratedkeyname ("id"); int[] Updatecount = Insert.executebatch (Newmap[] {args, args, args}); Assert.assertequals (1, Updatecount[0]); Assert.assertequals (5, Jdbctemplate.queryforint ("SELECT count (*) from Test")); }  

• new Simplejdbcinsert (JdbcTemplate) : initializes Simplejdbcinsert by DataSource object or JdbcTemplate object for the first time;
• insert.withtablename ("test") : used to set the database table name;
• args : used to specify the column name and value at insert time, as in this case, only the Name column name, that is, the compiled SQL is similar to "INSERT INTO Test (?)" ；
• Insert.compile () : Optional Compile step, which is compiled automatically when executing method is called, and can no longer be modified on insert object after compiling;
• Execute: The Execute method is used to perform a normal insert; Executeandreturnkey is used to execute and get the auto-generated primary key (note is the number type), you must first pass the Setgeneratedkeyname set the primary key before you can get it, if you want to get a composite primary key, use Setgeneratedkeynames to describe the primary key and then Executereturningkeyholder gets the compound primary key Keyholder object; ExecuteBatch is used for batch processing;

2, Simplejdbccall: for calling stored procedures and custom functions, this class is used to simplify stored procedures and custom function calls.

@Test Public voidtestSimpleJdbcCall1 () {//MySQL is used here because HSQLDB calls custom functions as well as stored proceduresSimplejdbccall call =NewSimplejdbccall (Getmysqldatasource ()); Call.withfunctionname ("Function_test"); Call.declareparameters (NewSqloutparameter ("Result", Types.integer)); Call.declareparameters (NewSqlParameter ("str", Types.varchar)); Map<string, object> outvlaues = Call.execute ("Test"); Assert.assertequals (4, Outvlaues.get ("result")); }  

• New Simplejdbccall (Getmysqldatasource ()) : Initializes Simplejdbccall by DataSource object or JdbcTemplate object;
• withfunctionname ("Function_test") : define Custom function names; The custom functions SQL statement will be compiled to resemble {? = call ...} Form
• declareparameters : Describes the parameter type, using the same method as the StoredProcedure object;
• Execute: Call the Execute method to execute the custom function;

@Test Public voidtestSimpleJdbcCall2 () {//Call the HSQLDB custom function to use the following methodSimplejdbccall call =NewSimplejdbccall (JdbcTemplate); Call.withprocedurename ("Function_test"); Call.declareparameters (NewSqlreturnresultset ("Result",      NewResultsetextractor<integer>() {@Override PublicInteger Extractdata (ResultSet rs)throwsSQLException, DataAccessException { while(Rs.next ()) {returnRs.getint (1); }                return0;          }})); Call.declareparameters (NewSqlParameter ("str", Types.varchar)); Map<string, object> outvlaues = Call.execute ("Test"); Assert.assertequals (4, Outvlaues.get ("result")); }  

Calling the HSQLDB database custom function is completely different from calling the MySQL custom function, as explained in StoredProcedure.

@Test Public voidTestSimpleJdbcCall3 () {Simplejdbccall call=NewSimplejdbccall (JdbcTemplate); Call.withprocedurename ("Procedure_test"); Call.declareparameters (NewSqlinoutparameter ("Inoutname", Types.varchar)); Call.declareparameters (NewSqloutparameter ("Outid", Types.integer)); Sqlparametersource params=NewMapsqlparametersource (). AddValue ("Inoutname", "Test"); Map<string, object> outvlaues =Call.execute (params); Assert.assertequals ("Hello,test", Outvlaues.get ("Inoutname")); Assert.assertequals (0, Outvlaues.get ("Outid")); }  

Unlike custom function calls, you use Withprocedurename to specify stored procedure names, and other parameter descriptions are exactly the same.

7.4.2 Controlling database connections

Spring JDBC Controls the database connection via DataSource, that is, the database connection is obtained through the DataSource implementation.

Spring JDBC provides a DataSource implementation:

• Drivermanagerdatasource : Simply encapsulates the DriverManager to get the database connection, and obtains the database connection through the DriverManager getconnection method;
• Singleconnectiondatasource : The internal encapsulation of a connection, the connection is not closed after use, and can not be used in a multithreaded environment, generally used for testing;
• lazyconnectiondatasourceproxy : Wraps a DataSource, which is used to delay getting a database connection, and only gets the connection if the statement is actually created. So again, the actual project finally uses the agent to wrap the original datasource so that it is only available when the connection is really needed.

The DataSource implementations provided by third parties are mainly c3p0, Proxool, DBCP, etc., which have the ability of database connection pool.

datasourceutils: Within the Spring JDBC Abstraction Framework, the database connection is obtained through its getconnection (DataSource DataSource) method, Releaseconnection (Connection con, DataSource DataSource) is used to free up database connections, Datasourceutils is used to support spring management transactions, and only connections obtained using datasourceutils have Spring management transactions.

7.4.3 get auto-generated primary key

There are many databases that provide the ability to generate primary keys automatically, so we may need to get these auto-generated primary keys, the JDBC 3.0 standard supports getting the auto-generated primary key, and the database must support auto-generated key acquisition.

1 ) jdbctemplate get auto-generated primary key mode:

@Test Public voidTestFetchKey1 ()throwsSQLException {FinalString insertsql = "INSERT into test (name) VALUES (' Name5 ')"; Keyholder generatedKeyHolder=NewgeneratedKeyHolder (); Jdbctemplate.update (NewPreparedStatementCreator () {@Override PublicPreparedStatement createpreparedstatement (Connection conn)throwsSQLException {returnConn.preparestatement (Insertsql,Newstring[]{"ID"});          }}, generatedKeyHolder); Assert.assertequals (0, Generatedkeyholder.getkey ()); }  

Use the JdbcTemplate update (final PreparedStatementCreator psc, final Keyholder generatedKeyHolder) method to perform an INSERT statement that needs to return the auto-generated primary key. Where the PSC is used to create PreparedStatement and specify auto-generated keys, such as "Preparestatement (Insertsql, New string[]{" ID "})" ; generatedKeyHolder is a keyholder type that gets the auto-generated primary key or composite primary key, such as using the Getkey method to get the auto-generated primary key.

2 ) sqlupdate get auto-generated primary key mode:

@Test Public voidTestFetchKey2 () {FinalString insertsql = "INSERT into test (name) VALUES (' Name5 ')"; Keyholder generatedKeyHolder=NewgeneratedKeyHolder (); Sqlupdate Update=Newsqlupdate ();      Update.setjdbctemplate (JdbcTemplate); Update.setreturngeneratedkeys (true); //update.setgeneratedkeyscolumnnames (New string[]{"ID"}); Update.setsql (Insertsql); Update.update (NULL, generatedKeyHolder); Assert.assertequals (0, Generatedkeyholder.getkey ()); }  

Sqlupdate gets the auto-generated primary key mode and jdbctemplate exactly the same, you can use Setreturngeneratedkeys (true) to indicate that you want to get the auto-generated key ; You can also use Setgeneratedkeyscolumnnames to specify the auto-generated key column name.

3 ) Simplejdbcinsert : The preceding example has been introduced and is not demonstrated here.

7.4.4 JDBC Bulk Operations

JDBC batch is used to reduce the number of interactions with the database to improve performance, and the Spring JDBC Abstraction Framework simplifies batch operations by encapsulating batch Operations

1 ) jdbctemplate Batch processing: support common batch processing and placeholder batch processing;

    @Test      publicvoid  testBatchUpdate1 () {          = ' INSERT into Test (name) VALUES (' Name5 ');           New string[] {insertsql, insertsql};          Jdbctemplate.batchupdate (batchsql);          Assert.assertequals (2, Jdbctemplate.queryforint ("SELECT count (*) from Test"));  

Call the BatchUpdate method directly to execute the statement that requires batching.

@Test Public voidTestBatchUpdate2 () {String insertsql= "INSERT into test (name) VALUES (?)"; Finalstring[] Batchvalues =NewString[] {"Name5", "Name6"}; Jdbctemplate.batchupdate (Insertsql,NewBatchPreparedStatementSetter () {@Override Public voidSetvalues (PreparedStatement PS,intIthrowsSQLException {ps.setstring (1, Batchvalues[i]); } @Override Public intgetbatchsize () {returnbatchvalues.length;          }          }); Assert.assertequals (2, Jdbctemplate.queryforint ("SELECT count (*) from Test")); }  

JdbcTemplate can also be batched through the batchupdate (String SQL, Final BatchPreparedStatementSetter pss) method, which uses precompiled statements, Then set the value (Setvalues) with the BatchPreparedStatementSetter implementation and specify the batch size (getbatchsize).

2 ) namedparameterjdbctemplate Batch processing: support named parameter batch processing;

@Test Public voidTestBatchUpdate3 () {namedparameterjdbctemplate namedparameterjdbctemplate=Newnamedparameterjdbctemplate (JdbcTemplate); String Insertsql= "INSERT into test (name) VALUES (: MyName)"; Usermodel Model=NewUsermodel (); Model.setmyname ("Name5"); Sqlparametersource[] Params= Sqlparametersourceutils.createbatch (Newobject[] {model, model});          Namedparameterjdbctemplate.batchupdate (Insertsql, params); Assert.assertequals (2, Jdbctemplate.queryforint ("SELECT count (*) from Test")); }  

Use the batchupdate (String sql, sqlparametersource[] Batchargs) method to batch the named parameters, Batchargs specify the batch data set. Sqlparametersourceutils.createbatch is used to create a corresponding JavaBean object or map. Beanpropertysqlparametersource or Mapsqlparametersource.

3) Simplejdbctemplate Batch processing: a simpler way to batch processing;

@Test Public voidtestBatchUpdate4 () {simplejdbctemplate simplejdbctemplate=Newsimplejdbctemplate (JdbcTemplate); String Insertsql= "INSERT into test (name) VALUES (?)"; List<Object[]> params =NewArraylist<object[]>(); Params.add (Newobject[]{"Name5"}); Params.add (Newobject[]{"Name5"});          Simplejdbctemplate.batchupdate (Insertsql, params); Assert.assertequals (2, Jdbctemplate.queryforint ("SELECT count (*) from Test")); }  

This example uses the BatchUpdate (String sql, list<object[]> Batchargs) method to complete the placeholder batch and, of course, to support named parameter batching.

4 ) Simplejdbcinsert batch processing:

  @Test  public  void   TestBatchUpdate5 () {simplejdbcinsert insert  = new   Simplejdbcinsert (JdbcTemplate);          Insert.withtablename ( "test" ); Map  <string, object> valueMap = new  hashmap<string, object>< Span style= "color: #000000;"          >();          Valuemap.put ( "name", "Name5" ); Insert.executebatch ( new   map[] {         ValueMap, valueMap}); Assert.assertequals ( 2, Jdbctemplate.queryforint ("SELECT count (*) from test" ))      ; }  

As shown in the code, use the ExecuteBatch (map<string, object>[] batch) method to perform the batch process.

Open Tao Spring3 (7.4)-Support for JDBC 7.4 Additional help provided by spring