SQL Zone is brought to you in partnership with:

Meyyappan has posted 101 posts at DZone. View Full User Profile

Spring NamedParameterJdbcTemplate Tutorial

06.15.2012
| 41476 views |
  • submit to reddit

The NamedParameterJdbcTemplate class helps you specify the named parameters insted of classic placeholder('?') argument. Named parameters improves readiablity and are easier to maintain.

package com.vaannila.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

import com.vaannila.domain.Forum;

public class ForumDAOImpl implements ForumDAO {

	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

	public void setDataSource(DataSource dataSource) {
	    this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
	}

	public void insertForum(Forum forum) {
		String query = "INSERT INTO FORUMS (FORUM_ID, FORUM_NAME, FORUM_DESC) VALUES (:forumId,:forumName,:forumDesc)";
		Map namedParameters = new HashMap();
		namedParameters.put("forumId", Integer.valueOf(forum.getForumId()));
		namedParameters.put("forumName", forum.getForumName());
		namedParameters.put("forumDesc", forum.getForumDesc());
		namedParameterJdbcTemplate.update(query, namedParameters);
	}
	
	public Forum selectForum(int forumId) {
		String query = "SELECT * FROM FORUMS WHERE FORUM_ID=:forumId";
		SqlParameterSource namedParameters = new MapSqlParameterSource("forumId", Integer.valueOf(forumId));

		return (Forum) namedParameterJdbcTemplate.queryForObject(query,
				namedParameters, new RowMapper() {
			public Object mapRow(ResultSet resultSet, int rowNum)
					throws SQLException {
				return new Forum(resultSet.getInt("FORUM_ID"), resultSet.getString("FORUM_NAME"), resultSet.getString("FORUM_DESC"));
			}
		});
	}

}

The named parameter values can be specified using a map, where the parameter name serves as the key. You can also map the sql parameters using the MapSqlParameterSource class.

You can download and try the example here.

Source :Download
Source + Lib :Download

 

Published at DZone with permission of its author, Meyyappan Muthuraman.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags:

Comments

Stuart Stephen replied on Fri, 2012/10/05 - 4:01am

Could you also include and example of this with Blob/Clobs. Thanks.

Brian Keller replied on Thu, 2014/01/30 - 10:54pm in response to: Stuart Stephen

//using spring jdbc 2.5.6, apache commons io 2.0.1 oracle 10g in tomcat 6.2.9
//org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate referenced as //jdbcTemplate below
//injected datasource using tomcat connection pool (context.xml resource ojdbc14_g.jar in //endorsed folder)
<Resource auth="Container" driverClassName="oracle.jdbc.driver.OracleDriver" maxActive="20" maxIdle="-1" maxWait="-1" name="jdbc/MyDataSource" password="mypw" testWhileIdle="true" type="javax.sql.DataSource" url="jdbc:oracle:thin:@myserver.com:1650:SID" username="myuser"/>
<jee:jndi-lookup id="datasourceID" jndi-name="java:/comp/env/jdbc/MyDataSource" />
//org.springframework.jdbc.core.namedparam.MapSqlParameterSource referenced as params //below

//code snippets:
//... file to byte[] ...
File f = new File("C:/temp/filename.pdf");
if (f.exists()) {
     BufferedReader reader = new BufferedReader(new FileReader(f));
     byte[] fileBytes = org.apache.commons.io.FileUtils.readFileToByteArray(f);
}
//...
     LobHandler lobHandler = new DefaultLobHandler();
     params.addValue("blob", new SqlLobValue(fileBytes, lobHandler), Types.BLOB);
     String sqlInsert = "insert into my_blob_table values (:blob)";
     jdbcTemplate.update(sqlInsert, params);
//...
reading blob back into byte[]...
     LobHandler lobHandler = new DefaultLobHandler();
     byte[] savedFile = lobHandler.getBlobAsBytes(rs, "blob_column_name");
     
     org.apache.commons.io.FileUtils.writeByteArrayToFile(new File(
"C:/temp/new/filename.pdf"), savedFile);

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.