NamedParameterJdbcTemplate
The NamedParameterJdbcTemplate class adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder (‘?’) arguments. This way of doing things is more flexible as you dont have to worry about the order of the paraemetrs. The NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work
Listing 8-18. NamedParameterJdbcTemplate usage for invoking SQL using named parameter
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
private NamedParameterJdbcTemplate namedParameterJdbcTemplate; public void setDataSource(DataSource dataSource) { this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource); } public int countOfCustomersByFirstName(String firstName) { String sql = "select count(*) from CUSTOMER where first_name = :first_name"; SqlParameterSource namedParameters = new MapSqlParameterSource("first_name", firstName); return namedParameterJdbcTemplate.queryForInt(sql, namedParameters); } |
You can also pass along named parameters and their corresponding values to a NamedParameterJdbcTemplate instance by using the Map-based style as shown in Listing 8-19.
Listing 8-19. NamedParameterJdbcTemplate usage for invoking SQL using named parameterin map style
|
1 2 3 4 5 6 7 8 9 |
public int countOfCustomersByFirstName(String firstName) { String sql = "select count(*) from CUSTOMER where first_name = :first_name"; Map namedParameters = Collections.singletonMap("first_name", firstName); return namedParameterJdbcTemplate.queryForInt(sql, namedParameters); } |
SimpleJdbcInsert
A SimpleJdbcInsert is a multi-threaded, reusable object providing easy insert capabilities for a table. It provides meta-data processing to simplify the code needed to construct a basic insert statement. All you need to provide is the name of the table and a Map containing the column names and the column values.
The meta-data processing is based on the java.sql.DatabaseMetaData provided by the JDBC driver. As long as the JBDC driver can provide the names of the columns for a specified table than we can rely on this auto-detection feature. If that is not the case then the column names must be specified explicitly. This was of explicitly defining columns is used in the Listing 8-20.
The actual insert is being handled using Spring’s JdbcTemplate by the way of delegation by SimpleJdbcInsert class.
Listing 8-20. Usage of SimpleJdbcInsert class to create a Customerby auto-generating customer id
|
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 |
public class JdbcCustomerRepository implements CustomerRepository { private final SimpleJdbcInsert insertCustomer; public JdbcCustomerRepository(DataSource dataSource){ insertCustomer = new SimpleJdbcInsert(dataSource).withTableName("CUSTOMER").usingGeneratedKeyColumns("ID"); } public Customer createCustomer(Customer customer) { Map<string, object=""> parameters = new HashMap<string, object="">(); parameters.put("first_name", customer.getFirstName()); parameters.put("last_name", customer.getLastName()); Number id = this.insertCustomer.executeAndReturnKey(parameters); customer.setId(id.intValue()); return customer; } } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
public class TestJDBCInsert { public static void main(String[] args) { XmlBeanFactory beanFactory = new XmlBeanFactory(new ClassPathResource("aplicationContext.xml")); CustomerRepository customerRepository = (CustomerRepository) beanFactory.getBean("customerRepository"); Customer customer = new Customer(); customer.setFirstName(“john”); customer.setLastName(“smith”); customerRepository.createCustomer(customer); } } |
In Listing 8-20, The customer id is auto-generated and then set to the customer and then the customer object is returned back. If you would like to supply the customer id yourself, there is a slight change in the way the SimpleJdbcInsert object is created, which is shown in Listing 8-21 below.
Listing 8-21. Usage of SimpleJdbcInsert class to create a Customer without auto-generating customer id
|
1 2 3 |
SimpleJdbcInsert insertCustomer = new SimpleJdbcInsert(dataSource).withTableName("CUSTOMER"); |
Main features of SimpleJdbcInsert can be summarized as below:
- Simplifies database insert operation
- Uses table meta-data to automatically provide column information to this class
- It also has explicit way by which to provide column details
- Supports auto generated keys in a database independent manner
- Supports batch operation as well with ease
java.sql.DatabaseMetaData is the interface implemented by database driver vendors to let users know the capabilities of a Database Management System (DBMS). Different relational DBMS often support different features, implement features in different ways, and use different data types. The driver vendor will implement various methods which provide adequate information for the SimpleJdbcInsert class to auto-detect the columns.
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..