Spring
provides a simplification in handling database access with the Spring JDBC
Template.
The Spring JDBC Template has the following advantages compared with standard JDBC.
More convenient is the usage of ResultSetExtractor or RowMapper which allows to translates the SQL result direct into an object (ResultSetExtractor) or a list of objects (RowMapper). Both these methods will be demonstrated in the coding.
The Spring JDBC Template has the following advantages compared with standard JDBC.
·
The Spring JDBC template allows to clean-up the resources
automatically, e.g. release the database connections.
·
The Spring JDBC template converts the standard JDBC SQLExceptions
into RuntimeExceptions. This allows the programmer to react more flexible to the
errors. The Spring JDBC template converts also the vendor specific error
messages into better understandable error messages.
The Spring
JDBC template offers several ways to query the database. queryForList() returns
a list of HashMaps. The name of the column is the key in the hashmap for the
values in the table. More convenient is the usage of ResultSetExtractor or RowMapper which allows to translates the SQL result direct into an object (ResultSetExtractor) or a list of objects (RowMapper). Both these methods will be demonstrated in the coding.
JdbcDemo.java
package org.yash.watertechsol;
import org.springframework.context.ApplicationContext;
import
org.springframework.context.support.ClassPathXmlApplicationContext;
import org.yash.watertechsol.dao.JdbcDaoImpl;
import org.yash.watertechsol.model.Circle;
public class JdbcDemo {
public static void main(String[]
args){
//Circle
circle = new JdbcDaoImpl().getCircle(1);
//System.out.println(circle.getName());
ApplicationContext
context = new ClassPathXmlApplicationContext("spring.xml");
JdbcDaoImpl dao =
context.getBean("jdbcDaoImpl", JdbcDaoImpl.class);
//Circle
circle = dao.getCircle(1);
//System.out.println(circle.getName());
System.out.println(dao.getCircleCount());
}
}
JdbcDaoImpl.java
package org.yash.watertechsol.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.yash.watertechsol.model.Circle;
@Component
public class JdbcDaoImpl {
@Autowired
private DataSource dataSource;
private JdbcTemplate jdbcTemplate;// = new
JdbcTemplate();
public Circle
getCircle(int circleId){
Connection conn =null;
try{
/*
String
driver = "com.mysql.jdbc.Driver";
Class.forName(driver).newInstance();
conn
= DriverManager.getConnection("jdbc:mysql://localhost:3306/testdb","root","");
*/
conn = dataSource.getConnection();
PreparedStatement
ps = conn.prepareStatement("select * from circle where id=?");
ps.setInt(1,
circleId);
Circle
circle =null;
ResultSet
rs = ps.executeQuery();
if(rs.next()){
circle
= new
Circle(circleId, rs.getString("name"));
}
rs.close();
ps.close();
return circle;
}catch(Exception e){
throw new
RuntimeException(e);
}
finally{
try {
conn.close();
} catch (Exception e2)
{
// TODO: handle
exception
}
}
}
public int getCircleCount(){
String sql = "SELECT
COUNT(*) FROM circle";
//jdbcTemplate.setDataSource(dataSource);
return jdbcTemplate.queryForInt(sql);
}
@Autowired
public void
setDataSource(DataSource dataSource) {
this.jdbcTemplate = new
JdbcTemplate(dataSource);
//this.dataSource
= dataSource;
}
public DataSource
getDataSource() {
return dataSource;
}
public JdbcTemplate
getJdbcTemplate() {
return jdbcTemplate;
}
public void
setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
}
Circle.java
package org.yash.watertechsol.model;
public class Circle {
private int id;
private String name;
public Circle(int circleId,
String name){
setId(circleId);
setName(name);
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String
getName() {
return name;
}
public void setName(String
name) {
this.name = name;
}
}
spring.xml
<?xml
version="1.0" encoding="UTF-8"?>
<beans
xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<context:annotation-config />
<context:component-scan base-package="org.yash.watertechsol"></context:component-scan>
<!--
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
-->
<bean id="dataSource"
class="org.apache.commons.dbcp.BasicDataSource"> <!-- this we will use for connection pooling -->
<property name="driverClassName"
value ="com.mysql.jdbc.Driver"></property>
<property name="url"
value="jdbc:mysql://localhost:3306/testdb"></property>
<property name="username"
value="root"></property>
<property name="password"
value=""></property>
<property name="initialSize"
value="2"></property>
<property name="maxActive"
value="5"></property>
</bean>
</beans>
Article:
No comments:
Post a Comment