One disadvantage of “JdbcTemplate” is you cannot add naming
convention in your sql query.
String sql = "SELECT
name FROM circle where id =?";
If you want to add naming convention in your sql query then
you have use “NamedParameterJdbcTemplate”
String sql = "INSERT into circle (id, name) values(:id,
:name)";
public void
insertCircle(Circle circle){
String sql = "INSERT
into circle (id, name) values(:id, :name)";
SqlParameterSource
namedParameters =
new MapSqlParameterSource("id",
circle.getId()) .addValue("name",circle.getName());
namedParameterJdbcTemplate.update(sql,
namedParameters);
}
If you want to add both conventions you need to use
“SimpleJdbcTemplate”.
JdbcDaoImpl.java
package org.yash.watertechsol.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
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
org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;
import org.yash.watertechsol.model.Circle;
@Component
public class JdbcDaoImpl {
@Autowired
private
DataSource dataSource;
private
JdbcTemplate jdbcTemplate;
private
NamedParameterJdbcTemplate namedParameterJdbcTemplate;
private
SimpleJdbcTemplate simpleJdbcTemplate;
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
}
}
}
@Autowired
public
void setDataSource(DataSource dataSource) {
this.jdbcTemplate
= new JdbcTemplate(dataSource);
this.namedParameterJdbcTemplate
= new NamedParameterJdbcTemplate(dataSource);
}
public
DataSource getDataSource() {
return
dataSource;
}
public
JdbcTemplate getJdbcTemplate() {
return
jdbcTemplate;
}
public
void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate
= jdbcTemplate;
}
public
int getCircleCount(){
String
sql = "SELECT COUNT(*) FROM circle";
//jdbcTemplate.setDataSource(dataSource);
return
jdbcTemplate.queryForInt(sql);
}
public
String getCircleName(int circleId){
String
sql = "SELECT name FROM circle where id =?";
return
jdbcTemplate.queryForObject(sql, new Object[]{circleId}, String.class);
}
public
Circle getCircleForId(int circleId){
String
sql = "SELECT * FROM circle where id =?";
return
jdbcTemplate.queryForObject(sql, new Object[]{circleId}, new CircleMapper());
}
private
static final class CircleMapper implements RowMapper<Circle>{
@Override
public
Circle mapRow(ResultSet resultSet, int rowNum) throws SQLException {
Circle
circle = new Circle();
circle.setId(resultSet.getInt("id"));
circle.setName(resultSet.getString("name"));
return
circle;
}
}
public
List<Circle> getAllCircle(){
String
sql="SELECT * from circle";
return
jdbcTemplate.query(sql, new CircleMapper());
}
/*
public
void insertCircle(Circle circle){
String
sql = "INSERT into circle (name) values(?)";
jdbcTemplate.update(sql,new
Object[]{circle.getName()});
}
*/
public
void insertCircle(Circle circle){
String
sql = "INSERT into circle (id, name) values(:id, :name)";
SqlParameterSource
namedParameters = new MapSqlParameterSource("id", circle.getId())
.addValue("name",
circle.getName());
namedParameterJdbcTemplate.update(sql,
namedParameters);
}
public
void createTriangleTable(){
String
sql = "CREATE TABLE Triangle(id integer, name varchar(50))";
jdbcTemplate.execute(sql);
}
public
void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate
namedParameterJdbcTemplate) {
this.namedParameterJdbcTemplate
= namedParameterJdbcTemplate;
}
public
NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
return
namedParameterJdbcTemplate;
}
}
JdbcDemo.java
package org.yash.watertechsol;
import java.util.List;
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){
ApplicationContext
context = new ClassPathXmlApplicationContext("spring.xml");
JdbcDaoImpl dao =
context.getBean("jdbcDaoImpl", JdbcDaoImpl.class);
dao.insertCircle(new Circle(5, "Fifth
Circle"));
System.out.println(dao.getAllCircle().size());
}
}
Circle.java
package org.yash.watertechsol.model;
public class Circle {
private int id;
private String name;
public Circle(){}
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>
No comments:
Post a Comment