Q. When will you use NamedParameterStatement over PreparedStatement?
A.
Reason 1: PreparedStatement uses anonymous parameters that are accessed by index, which is ok when you have 2 or 3 parameters, but for larger queries with many parameters it os more difficult to keep track of the indices. The developer has to count the number of question marks.
|
1 2 3 4 5 6 |
String sql = "select * from people where (first_name = ? or last_name = ?) and address = ?"; PreparedStatement p = con.prepareStatement(sql); p.setString(1, name); p.setString(2, name); p.setString(3, address); |
The above query will require you to renumber the indices, and it can be improved as shown below.
|
1 2 3 4 5 6 7 |
String sql = "select * from people where (first_name = ? or last_name = ?) and address = ?"; PreparedStatement p = con.prepareStatement(sql); int i = 1; p.setString(i++, name); p.setString(i++, name); p.setString(i++, address); |
The NamedParameter is even more cleaner.
|
1 2 3 4 5 |
String sql = "select * from people where (first_name = :name or last_name = :name) and address = :address"; NamedParameterStatement p = new NamedParameterStatement(con, sql); p.setString("name", name); p.setString("address", address); |
Reason 2: In some cases parameters make your query more readable when you have combination of parameters and database functions like getdate( ), etc. The following example is Spring jdbc based to use parameter names.
|
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 int insertOfflineSuperEquityRequest(TradeDetail td) { String sql = "insert into trade_request ( account_code, source_system, reference, ext_reference," + " security_code,create_date,create_id,update_date,update_id) " + " values (:acc_code,'SYSTEM_A', :ref, :ext_ref, :security, getdate(), suser_name(),getdate(), suser_name())"; //Spring JDBC Named Parameter class NamedParameterJdbcTemplate npJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate.getDataSource()); Map<String, Object> parameters = new HashMap<String, Object> (); parameters.put("acc_code", td.accountCode); parameters.put("ref", td.getReference()); parameters.put("ext_ref", td.getExternalReference()); parameters.put("security", td.getSecurityCode()); int updateCount = npJdbcTemplate.update(sql, getOfflineRequestArgs(td)); return updateCount; } |
As you can see, it has a combination of named parameters (:acc_code, :ref, :ext_ref, and security), a constant (i.e. SYSTEM_A), and Sybase database functions like getDate( ) to get current date time and suser_name( ) to get the user id.
