Java Spring JDBC Batch save data example

Data List:list.add (bchistory);

BULK INSERT:

The code is as follows

Copy Code

try { Jt.batchupdate ( "INSERT into B_chat_history (id,from_phone,from_user,to_phone,to_user,type,msg,url,thumb,length,timestamp) value (?) ,?,?,?,?,?,?,?,?,?,?)", New BatchPreparedStatementSetter () { public void Setvalues (preparedstatement ps, int i) throws SQLException { Ps.setstring (1, Uuid.randomuuid (). toString ());//ID's value Ps.setstring (2, List.get (i). Getfrom_phone ()); Ps.setstring (3, List.get (i). Getfrom_user ()); Ps.setstring (4, List.get (i). Getto_phone ()); Ps.setstring (5, List.get (i). Getto_user ()); Ps.setstring (6, List.get (i). GetType ()); Ps.setstring (7, List.get (i). getmsg ()); Ps.setstring (8, List.get (i). GETURL ()); Ps.setstring (9, List.get (i). Getthumb ()); Ps.setint (10,list.get (i). GetLength ()); Ps.settimestamp (one, List.get (i). Gettimestamp ()); } public int getbatchsize () { return List.size (); } }); catch (Exception E2) { SYSTEM.OUT.PRINTLN ("There may be data anomalies, synchronizing partial data exceptions"); E2.printstacktrace (); }

Add:

Use JdbcTemplate for basic bulk operations

This method is most commonly used on the Internet, but in practical applications I do not feel very convenient, this method can be made into a common interface? Never studied it carefully.

The code is as follows

Copy Code

public class Jdbcactordao implements Actordao { Private JdbcTemplate JdbcTemplate; public void Setdatasource (DataSource DataSource) { This.jdbctemplate = new JdbcTemplate (DataSource); }   Public int[] BatchUpdate (final list<actor> actors) {     int[] updatecounts = jdbctemp Late.batchupdate ("update t_actor set first_name =?," +         "last_name =? WHERE id =? ",       new BatchPreparedStatementSetter () {          public void Setvalues (preparedstatement ps, int i) throws SQLException {              ps.setstring (1, Actors.get (i). Getfirstname ());             ps.setstring (2, Actors.get (i) getlastname ());             Ps.setlong (3, Actors.get (i). GetId (). Longvalue ());          } public int getbatchsize () { return Actors.size (); } }); return updatecounts; } ... additional methods }

Batch operations using the parameters in the List collection form

This approach is more appropriate if you use the list collection for bulk operations, which provides a Sqlparamtersource object in the spring JDBC core package and then uses the

The code is as follows

Copy Code

Sqlparametersourceutils.createbatch In this way, the JavaBean list is converted to array,spring and the values are recycled; public class Jdbcactordao implements Actordao { Private Namedparametertemplate namedparameterjdbctemplate; public void Setdatasource (DataSource DataSource) { This.namedparameterjdbctemplate = new Namedparameterjdbctemplate (DataSource); } Public int[] BatchUpdate (final list<actor> actors) { sqlparametersource[] Batch = Sqlparametersourceutils.createbatch (Actors.toarray ()); int[] updatecounts = Namedparameterjdbctemplate.batchupdate ( "Update t_actor Set first_name =: firstName, last_name =: lastName WHERE id =: id", Batch); return updatecounts; } ... additional methods }

Of course, you can also use a similar method for bulk operations, the following code: (code from the official website sample)

The code is as follows

Copy Code

public class Jdbcactordao implements Actordao { Private JdbcTemplate JdbcTemplate; public void Setdatasource (DataSource DataSource) { This.jdbctemplate = new JdbcTemplate (DataSource); } Public int[] BatchUpdate (final list<actor> actors) { list<object[]> batch = new arraylist<object[]> (); for (Actor actor:actors) { Object[] values = new object[] { Actor.getfirstname (), Actor.getlastname (), Actor.getid ()}; Batch.add (values); } int[] updatecounts = Jdbctemplate.batchupdate ( "Update t_actor Set first_name =?, last_name =?" WHERE id =? ", Batch); return updatecounts; } ... additional methods }

Third, the use of multidimensional arrays for bulk operations

The code is as follows

Copy Code

public class Jdbcactordao implements Actordao { Private JdbcTemplate JdbcTemplate; public void Setdatasource (DataSource DataSource) { This.jdbctemplate = new JdbcTemplate (DataSource); } Public int[][] BatchUpdate (final collection<actor> actors) { int[][] updatecounts = Jdbctemplate.batchupdate ( "Update t_actor Set first_name =?, last_name =?" WHERE id =? ", Actors, 100, New Parameterizedpreparedstatementsetter<actor> () { public void Setvalues (PreparedStatement PS, Actor argument) throws SQLException { Ps.setstring (1, Argument.getfirstname ()); Ps.setstring (2, Argument.getlastname ()); Ps.setlong (3, Argument.getid (). Longvalue ()); } }); return updatecounts; } ... additional methods }

In the code above, 100 indicates the capacity of a bulk operation;

Iv. use Simplejdbcinsert for simple insert operations

In general, we use update to insert, but spring provides a much simpler way to insert object-oriented inserts:

The code is as follows

Copy Code

Public Class Jdbcactordao implements Actordao {   private jdbctemplate jdbctemplate;   Private simplejdbcin SERT Insertactor;   public void Setdatasource (DataSource DataSource) {     this.jdbctemplate = new JdbcTemplate (DataSource);     this.insertactor = new Simplejdbcinsert (dataSource). Withtablename ("T_actor");  }   public void Add (Actor Actor) {     map<string, object> parameters = new Hashmap<stri Ng, object> (3);     parameters.put ("id", Actor.getid ());     parameters.put ("First_Name", Actor.getfirstname ());     parameters.put ("Last_Name", Actor.getlastname ());     insertactor.execute (parameters);  }  //... additional methods }

What if we need to get the return value of the insert?

The code is as follows

Copy Code

public class Jdbcactordao implements Actordao { Private JdbcTemplate JdbcTemplate; Private Simplejdbcinsert Insertactor; public void Setdatasource (DataSource DataSource) { This.jdbctemplate = new JdbcTemplate (DataSource); This.insertactor = new Simplejdbcinsert (dataSource) . Withtablename ("T_actor") . Usinggeneratedkeycolumns ("id"); } public void Add (Actor Actor) { map<string, object> parameters = new hashmap<string, object> (2); Parameters.put ("First_Name", Actor.getfirstname ()); Parameters.put ("Last_Name", Actor.getlastname ()); Number newId = insertactor.executeandreturnkey (parameters); Actor.setid (Newid.longvalue ()); } ... additional methods }

Note that a map is used here as a parameter, and the field name is the key, so we can automatically get a map of parameter names and values based on the incoming object, prompting: You can use reflection to handle the original:

Sample code:

The code is as follows

Copy Code

/** * Get a map of the name value * @param Class1 * @return */ public static <M> Sqlparametersource Getparamsmap (M Bean) { if (bean==null) return null; Mapsqlparametersource parameters = new Mapsqlparametersource (); class<?> _class = Bean.getclass (); Table _table = _class.getannotation (Table.class); if (_table==null) { Logger.error (_class.getname () + "not find @Table value!please check it!"); Throw (New Notfoundtableannotaionexception (_class.getname () + "not find @Table value!please check it!")); } String Primarykey=_table.primarykey (); Field[] Allfields=getallfields (_class); Object _primarykeyvalue= ""; for (Field field:allfields) { if (Modifier.isstatic (Field.getmodifiers ())) continue; Field.setaccessible (TRUE); try { if (Stringutil.isequal (PrimaryKey, Field.getname ())) { _primarykeyvalue=field.get (Bean); Continue } Parameters.addvalue (Field.getname (), Field.get (Bean)); catch (IllegalArgumentException e) { TODO auto-generated Catch block E.printstacktrace (); catch (Illegalaccessexception e) { TODO auto-generated Catch block E.printstacktrace (); } } return parameters; }

Ask a question: What if my field name and the database field name are not the same?

Solution:

The code is as follows

Copy Code

public class Jdbcactordao implements Actordao { Private JdbcTemplate JdbcTemplate; Private Simplejdbcinsert Insertactor; public void Setdatasource (DataSource DataSource) { This.jdbctemplate = new JdbcTemplate (DataSource); This.insertactor = new Simplejdbcinsert (dataSource) . Withtablename ("T_actor") . Usingcolumns ("First_Name", "last_name") . Usinggeneratedkeycolumns ("id"); } public void Add (Actor Actor) { map<string, object> parameters = new hashmap<string, object> (2); Parameters.put ("First_Name", Actor.getfirstname ()); Parameters.put ("Last_Name", Actor.getlastname ()); Number newId = insertactor.executeandreturnkey (parameters); Actor.setid (Newid.longvalue ()); } ... additional methods } Usingcolumns ("First_Name", "last_name")//mainly this sentence ~

Of course, you can also use the sqlparametersource parameters = new Mapsqlparametersource () that we mentioned earlier, and we can do chain operations.

The code is as follows

Copy Code

public class Jdbcactordao implements Actordao { Private JdbcTemplate JdbcTemplate; Private Simplejdbcinsert Insertactor; public void Setdatasource (DataSource DataSource) { This.jdbctemplate = new JdbcTemplate (DataSource); This.insertactor = new Simplejdbcinsert (dataSource) . Withtablename ("T_actor") . Usinggeneratedkeycolumns ("id"); } public void Add (Actor Actor) { Sqlparametersource parameters = new Mapsqlparametersource () . AddValue ("First_Name", Actor.getfirstname ()) . AddValue ("Last_Name", Actor.getlastname ()); Number newId = insertactor.executeandreturnkey (parameters); Actor.setid (Newid.longvalue ()); } ... additional methods }