SimpleJdbcCall
A SimpleJdbcCall is a multi-threaded, reusable object representing a call to a stored procedure or a stored function. It provides meta-data processing to simplify the code needed to access basic stored procedures/functions. All you need to provide is the name of the procedure/function and a Map containing the parameters when you execute the call. The names of the supplied parameters will be matched up with in and out parameters declared when the stored procedure was created.
The meta-data processing is based on the DatabaseMetaData provided by the JDBC driver. Since we rely on the JDBC driver this “auto-detection” can only be used for databases that are known to provide accurate meta-data.
The actual functionality is being handled using Spring’s JdbcTemplate by the way of delegation by SimpleJdbcCall class.
Listing 8-22. Usage of SimpleJdbcCall to execute a function to calculate loyalty points
|
1 2 3 4 5 6 7 8 9 |
public class TestJDBCFunctionCall{ public static void main(String args[]) throws Exception { ApplicationContext ac = new ClassPathXmlApplicationContext("applicationContext.xml", TestJDBCCall.class); DataSource dataSource = (DataSource) ac.getBean("dataSource"); BigDecimal loyaltyPoints = new SimpleJdbcCall(dataSource).withFunctionName("calculate_loyalty"). withReturnValue().executeObject(BigDecimal.class, Collections.emptyMap()); System.out.println(loyaltyPoints ); } } |
Listing 8-23. Usage of SimpleJdbcCall to execute a procedure to generate customer number
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
public class TestJDBCProcedureCall{ public static void main(String args[]) throws Exception { ApplicationContext ac = new ClassPathXmlApplicationContext( "applicationContext.xml", TestJDBCCall.class); DataSource dataSource = (DataSource) ac.getBean("dataSource"); SimpleJdbcCall generateCustomerNumberCall = new SimpleJdbcCall(dataSource).withProcedureName("generate_customer_no"); String customerNumber = generateCustomerNumberCall.executeObject(String.class, Collections.singletonMap("in_prefix", "XYZ")); } } |
Main features of SimpleJdbcCall can be summarized as below:
- Simplify call to functions and procedures
- Any database can be used by explicitly defining the parameters
- Supported databases like Oracle, MySQL etc. provides automatic parameter configuration, which is really handy
SqlQuery
This is a class in the package org.springframework.jdbc.object, which lets you encapsulate any SQL query in your application. Subclasses of this class must implement the newRowMapper(..) method to provide an object that can extract the results of iterating over the ResultSet created during the execution of the query. SqlQuery instances are thread-safe after their initialization is complete. The SqlQuery class is rarely used directly because the MappingSqlQuery subclass provides a much more convenient implementation for mapping rows to Java classes.
Listing 8-24. Usage of SqlQuery which encapsulates the actual SQL query
|
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 |
import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper; import org.springframework.jdbc.object.SqlQuery; public class CustomerSelectQuery extends SqlQuery { private static final String SQL = "select * from customer"; CustomerSelectQuery(DataSource ds) { super(ds, SQL); } protected RowMapper newRowMapper(Object[] parameters, Map context) { return ParameterizedBeanPropertyRowMapper.newInstance(Customer.class); } } class TestClass { public static void main(String args[]) throws Exception { ApplicationContext ac = new ClassPathXmlApplicationContext( "applicationContext.xml", TestClass.class); DataSource dataSource = (DataSource) ac.getBean("dataSource"); CustomerSelectQuery selectCustomer = new CustomerSelectQuery (dataSource); List result = selectCustomer.execute(); } } |
SqlUpdate
Similar to SqlQuery SqlUpdate encapsulates an SQL update. Like a query, update object is also reusable. The class provides number of update(..) methods which helps encapsulate a lot from the developer.
Listing 8-25. Usage of SqlUpdate which encapsulates the actual SQL query
|
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 |
public class UpdateFirstNameQuery extends SqlUpdate { static final String SQL = "UPDATE customer SET first_name = ? WHERE id = ?"; public UpdateFirstNameQuery(DataSource dataSource) { super(dataSource, SQL); declareParameter(new SqlParameter("first_name", Types.VARCHAR)); declareParameter(new SqlParameter("id", Types.INTEGER)); compile(); } void updateCustomerFirstName(long id, String firstName) { update(new Object[] { id, firstName }); } } class TestClass { public static void main(String args[]) throws Exception { ApplicationContext ac = new ClassPathXmlApplicationContext( "applicationContext.xml", TestClass .class); DataSource dataSource = (DataSource) ac.getBean("dataSource"); UpdateFirstNameQuery testQueryClass = new UpdateFirstNameQuery (dataSource); testQueryClass.update(new Object[] { 1L, "john" }); } } |
Page Visitors: 10454
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..