Spring Named Parameters examples in SimpleJdbcTemplate

In JdbcTemplate, SQL parameters are represented by a special placeholder “?” symbol and bind it by position. The problem is whenever the order of parameter is changed, you have to change the parameters bindings as well, it’s error prone and cumbersome to maintain it.

To fix it, you can use “Named Parameter“, whereas SQL parameters are defined by a starting colon follow by a name, rather than by position. In additional, the named parameters are only support in SimpleJdbcTemplate and NamedParameterJdbcTemplate.

See following three examples to use named parameters in Spring.

Example 1

Example to show you how to use named parameters in a single insert statement.


	//insert with named parameter
	public void insertNamedParameter(Customer customer){
			
		String sql = "INSERT INTO CUSTOMER " +
			"(CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)";
			
		Map<String, Object> parameters = new HashMap<String, Object>();
		parameters.put("custId", customer.getCustId());
		parameters.put("name", customer.getName());
		parameters.put("age", customer.getAge());
			
		getSimpleJdbcTemplate().update(sql, parameters);
				
	}

Example 2

Examples to show how to use named parameters in a batch operation statement.


	public void insertBatchNamedParameter(final List<Customer> customers){
			
		String sql = "INSERT INTO CUSTOMER " +
		"(CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)";
				
		List<SqlParameterSource> parameters = new ArrayList<SqlParameterSource>();
		for (Customer cust : customers) {
			parameters.add(new BeanPropertySqlParameterSource(cust)); 
		}
		
		getSimpleJdbcTemplate().batchUpdate(sql,
			parameters.toArray(new SqlParameterSource[0]));
	}

Example 3

Another examples to use named parameters in a batch operation statement.


	public void insertBatchNamedParameter2(final List<Customer> customers){
			
	   SqlParameterSource[] params = 
		SqlParameterSourceUtils.createBatch(customers.toArray());
		
	   getSimpleJdbcTemplate().batchUpdate(
		"INSERT INTO CUSTOMER (CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)",
		params);
		
	}

Download Source Code

Download it – Spring-JDBC-Named-Parameter-Example.zip (15 KB)

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
9 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
9 Comment authors
JavierBharatYogeeta AroraPradeepPmt Recent comment authors
newest oldest most voted
Javier
Guest
Javier

i, what would it be like to add in an Oracle table that has a sequence?

Bharat
Guest
Bharat

Hi mkyong, i have a question regarding batch update:

.batchUpdate(
“INSERT INTO CUSTOMER (CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)”,

Is the above method transactional? Will all inserts rollback if one insert fails due to some data constraiant?

Yogeeta Arora
Guest
Yogeeta Arora

Hi mkyong, I am facing a problem with NamedParameterJdbcTemplate.Whenever I deploy the build with NamedParameterJdbcTemplate and query starts hitting the Database, database CPU usage reaches 100% and when I am using simpleJdbcTemplate it remains between 30-40%. Is this a know issue? Please help me to solve this problem and let me know if you want more information from me.

Pradeep
Guest
Pradeep

I am trying to use the following sql with the batchUpdate and doesn’t work. Any ideas?

UPDATE Role set Name=replace(Name, :name, :targetGroupName) where Name like %:name

Pmt
Guest
Pmt

I’m getting this exception when I try to do this:

“java.sql.SQLException: Invalid column type”

Any idea??

Thanks!

Priyanka Patil
Guest
Priyanka Patil

Hi MK,
Have you ever tried a NamedParameterJdbcTemplate with more than 20000 records where you need to set the fetch Size?

Anand
Guest
Anand

Mr. Yong

Your tutorials are awesome for any novice on JAVA related topics. Keep up the good work and BIG THANKS

micahli123
Guest
micahli123

Thanks!

Dilshat
Guest
Dilshat

Awesome site! Superb tutorials! Greate work MK!