How to use spring-jdbc from the springframework?
In my previous post we have seen how to create a webapp. Today we will see how to use spring-jdbc which is the Spring Framework's JDBC abstraction framework. I invite you to check the documentation for spring jdbc. Thanks to this you will be able to connect your application or webapp to a database (I'll use the webapp from my previous post here). In my example I will use mysql but it is really easy to use any other database.
Add dependencies
As in the previous post, I use maven. You'll need to have dependencies for commons-collections, commons-dbcp, mysql and spring-jdbc so in your pom file you have:
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>commons-collections</groupId>
<artifactId>commons-collections</artifactId>
<version>3.2.1</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>3.0.0.RC3</version>
</dependency>
If we have commons-dbcp and commons-collections, it's because we want to have a connection pool. Indeed, creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds. In my experience commons-dbcp is a very good choice to get a database connection pool.
Spring beans
Now that we have all the dependencies we need we'll create the spring beans which we'll inject using the AutoWired annotation (by the way, it's a good idea to read the documentation this link lead to).
The bean to create the datasource:
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mydb?autoReconnect=true" />
<property name="username" value="myuser" />
<property name="password" value="mypassword" />
<property name="initialSize" value="5"/>
<property name="validationQuery" value="select 1" />
<property name="defaultAutoCommit" value="false" />
<property name="maxActive" value="10" />
<property name="maxIdle" value="10" />
<property name="maxWait" value="15000" />
<property name="removeAbandoned" value="true" />
<property name="removeAbandonedTimeout" value="300" />
<property name="testOnBorrow" value="true" />
</bean>
Check the javadoc of BasicDataSource if you want to know more about this.
The bean to create the JdbcTemplate:
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg><ref bean="dataSource" /></constructor-arg>
</bean>
Use JdbcTemplate
In my previous post we add Application.java, let's use it to see what we can do with JdbcTemplate.
@Controller
public class Application {
@Autowired
private JdbcTemplate template;
@RequestMapping(value="/",method=RequestMethod.GET)
public ModelAndView home(HttpServletRequest request, HttpServletResponse response) {
final List<Post> posts = new ArrayList<Post>();
template.query("SELECT * FROM Post LIMIT 5", new RowCallbackHandler() {
public void processRow(ResultSet rs) throws SQLException {
Post p = new Post();
p.setId(rs.getLong("id"));
p.setTitle(rs.getString("title"));
p.setContent(rs.getString("content"));
}
});
Post post = template.queryForObject("SELECT * FROM Post WHERE id=?", new RowMapper<Post>() {
public Post mapRow(ResultSet rs, int arg1) throws SQLException {
Post p = new Post();
p.setId(rs.getLong("id"));
p.setTitle(rs.getString("title"));
p.setContent(rs.getString("content"));
return p;
}
});
Map<String,Object> model = new HashMap<String,Object>();
model.put("posts",posts);
model.put("post",post);
return new ModelAndView("home",model);
}
}
Post is a class I have created. You can see here how I make use of the RowCallbackHandler to create a List
A little bit of JSTL
To display the List
<c:forEach var="p" items="${posts}">
${p.id} - ${p.title}
${p.content}
</c:forEach>
Conclusion
That's it. In this post you learned how to create and use a database connection pool and how to use JdbcTemplate. We've seen as well AutoWired and a little bit of jstl.
Click here to download the code related to this post
4 comments