MappingSqlQuery
The class is present in org.springframework.jdbc.object package and aids in accessing database in a more object-oriented manner. This class is used to execute queries and get the results back as a list containing business objects with the relational column data mapped to the properties of the business object. Listing 8-26 shows a custom query that maps the data from the ‘customer’ table to an instance of the ‘Customer’ class.
Listing 8-26. Usage of MappingSqlQuery to map the table ‘customer’ to the ‘Customer’ object
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
public class CustomerFirstNameAndLastNameQuery extends MappingSqlQuery { private static final String SQL = "SELECT id, first_name, last_name FROM customer WHERE first_name = ? AND last_name = ?"; public CustomerFirstNameAndLastNameQuery(DataSource ds) { super(ds, SQL); declareParameter(new SqlParameter("first_name", Types.VARCHAR)); declareParameter(new SqlParameter("last_name", Types.VARCHAR)); compile(); } protected Object mapRow(ResultSet resultSet, int row) throws SQLException { Customer customer = new Customer(); customer.setId(resultSet.getInt("id")); customer.setFirstName(resultSet.getString("first_name")); customer.setLastName(resultSet.getString("last_name")); return customer; } } |
StoredProcedure
Spring’s abstraction of RDBMS stored procedure is achieved using this class which is present in org.springframework.jdbc.object package. This class is abstract and it is intended that subclasses will provide a typed method for invocation that delegates to the supplied execute (…) method.
Listing 8-27. Usage of StoredProcedure which encapsulates the actual procedure
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
public class AnyStoredProcedure extends StoredProcedure { private static final String SQL = "any_procedure"; AnyStoredProcedure(DataSource dataSource) { super(dataSource, SQL); declareParameter(new SqlParameter("test", Types.INTEGER)); } void execute(int value) { Map<string, object> parameters = new HashMap<string, object>(1); parameters.put("test", value); execute(parameters); } } public class TestClass { public static void main(String args[]) throws Exception { ApplicationContext ac = new ClassPathXmlApplicationContext( "applicationContext.xml", Main.class); DataSource dataSource = (DataSource) ac.getBean("dataSource"); AnyStoredProcedure storeProc = new AnyStoredProcedure(dataSource); storeProc.execute(10); } } |
JDBC Testing Support
The org.springframework.test.jdbc package contains SimpleJdbcTestUtils, which is a collection of JDBC related utility functions intended to simplify standard database testing scenarios. Note that org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests is the JUnit support class and org.springframework.test.context.testng.AbstractTransactionalTestNGSpringContextTests is the TestNG support class which provides convenience methods which delegate to SimpleJdbcTestUtils internally. These classes were covered in some detail in the Chapter 7 and now you can easily relate its relevance.
JDBC Batch Operations
In any enterprise application, there will be cases in which you may have to insert a batch of records into database in one shot. If you call a single insert method for every record that you want to insert, the SQL statement will be compiled repeatedly, causing your system to have performance issues.
Listing 8-28 shows using JdbcTemplate’s batchUpdate(..) method to perform this batch customer inserts.
Listing 8-28. Usage of JdbcTemplate to perform batch insert of customers
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
public void insertCustomersAsABatch(final List customers){ String sql = "INSERT INTO CUSTOMER " + "( ID, FIRST_NAME, LAST_NAME) VALUES (?, ?, ?)"; getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Customer customer = customers.get(i); ps.setLong(1, customer.getId()); ps.setString(2, customer.getFirstName()); ps.setString(3, customer.getLastName() ); } @Override public int getBatchSize() { return customers.size(); } }); } |
Listing 8-29 shows the usage of NamedParameterJdbcTemplate to perform batch update of customers using named parameters in the query.
Listing 8-29. Usage of NamedParameterJdbcTemplate to perform batch update of customers using named parameters
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
public int[] upadteCustomersAsABatch(final List customers){ String sql = "update customer set first_name = :firstName, last_name = :lastName where id = :id"; SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(customers.toArray()); int[] updateCounts = getNamedParameterJdbcTemplate.batchUpdate(sql, batch); return updateCounts; } |
If you would like to use the JdbcTemplate to do batch update in more than one step, Spring also provides to split your big batches into multiples and do the operation. Listing 8-30 shows doing this in multiples of 50 updates to a batch containing 5000 records of customers.
Listing 8-30. Usage of JdbcTemplate to perform batch update of customers in multiples of 50
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
public int[][] upadteCustomersAsABatchInMultiplesOfFifty(final List customers){ String sql = "update customer set first_name = ?, last_name = ? where id = ?"; Collection<object[]> batch = new ArrayList<object[]>(); for (Customer customer : customers) { Object[] values = new Object[] { customer.getFirstName(), customer.getLastName(), customer.getId()}; batch.add(values); } int[][] updateCounts = jdbcTemplate.batchUpdate(sql, customers, 50, new ParameterizedPreparedStatementSetter() { public void setValues(PreparedStatement ps, Customer customer) throws SQLException { ps.setString(1, customer.getFirstName()); ps.setString(2, customer.getLastName()); ps.setLong(3, customer.getId().longValue()); } }); return updateCounts; } |
In Listing 8-30, the batch update methods for this call returns an array of int arrays containing an array entry for each batch with an array of the number of affected rows for each update. The top level array’s length indicates the number of batches executed and the second level array’s length indicates the number of updates in that batch.
Page Visitors: 10629
Tomcy John
Latest posts by Tomcy John (see all)
- A Guide to Continuous Improvement for Architects - February 2, 2023
- Cloud-first Architecture Strategy - January 26, 2023
- Architecture Strategy and how to create One - January 24, 2023
Hi my friend! I wish to say that this article is awesome, nice written and come with approximately all important infos. Iˇ¦d like to peer more posts like this .
simple, well written. keep writing..