Spring Framework : A Framework from SpringSource :: Spring with JDBC Example
- Spring with JdbcTemplate Example
- Spring with JdbcTemplate Using DMDS(DriverManagerDataSource) Example
- Spring with NamedParameterJdbcTemplate Example
- Download Examples
| Spring with JdbcTemplate Example |
Spring JdbcTemplate Example
Item.Java
package com.javaskool;
public class Item {
private int itemId;
private String itemName;
private int qty;
public int getItemId() {
return itemId;
}
public void setItemId(int itemId) {
this.itemId = itemId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public int getQty() {
return qty;
}
public void setQty(int qty) {
this.qty = qty;
}
}
applicationContext.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">
<bean id="obj" class="com.javaskool.Item">
<property name="itemId" value="1001"/>
<property name="itemName" value="Mouse"/>
<property name="qty" value="100"/>
</bean>
<bean id="dmds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/javaskoolDB"/>
<property name="username" value="root"/>
<property name="password" value="admin"/>
</bean>
<bean id="template" class="org.springframework.jdbc.core.JdbcTemplate" autowire="byType"/>
</beans>
TestDrive.java
package com.javaskool;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.dao.DataAccessException;
public class TestDrive {
public static void main(String[] args) throws DataAccessException, Exception
{
Resource res = new ClassPathResource("applicationContext.xml");
BeanFactory beans=new XmlBeanFactory(res);
Item p=(Item)beans.getBean("obj");
System.out.println(p.getItemId());
System.out.println(p.getItemName());
System.out.println(p.getQty());
JdbcTemplate template=(JdbcTemplate)beans.getBean("template");
template.execute("create table ItemTab(ItemId int, ItemName varchar(20), Qty int)");
System.out.println("Table Created....");
String query="insert into ItemTab values('"
+p.getItemId()+"','"+p.getItemName()+"','"+p.getQty()+"')";
//template.execute(query);
int row=template.update(query);
System.out.println(row+ " Rows inserted....");
}
}
Output
Mar 30, 2013 2:40:16 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
1001
Mouse
100
Mar 30, 2013 2:40:16 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Table Created....
1 Rows inserted....
mysql> show create Table ItemTab;
CREATE TABLE `itemtab` (
`ItemId` int(11) DEFAULT NULL,
`ItemName` varchar(20) DEFAULT NULL,
`Qty` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> select * from itemTab;
+--------+----------+------+
| ItemId | ItemName | Qty |
+--------+----------+------+
| 1001 | Mouse | 100 |
+--------+----------+------+
1 row in set (0.02 sec)
mysql>
| Spring with JdbcTemplate Using DMDS(DriverManagerDataSource) Example |
Item.Java
package com.javaskool;
public class Item {
private int itemId;
private String itemName;
private int qty;
public int getItemId() {
return itemId;
}
public void setItemId(int itemId) {
this.itemId = itemId;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public int getQty() {
return qty;
}
public void setQty(int qty) {
this.qty = qty;
}
}
applicationContext.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">
<bean id="obj" class="com.javaskool.Item">
<property name="itemId" value="1001"/>
<property name="itemName" value="Mouse"/>
<property name="qty" value="100"/>
</bean>
</beans>
TestDrive.java
package com.javaskool;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.dao.DataAccessException;
public class TestDrive {
public static void main(String[] args) throws DataAccessException, Exception
{
Resource res = new ClassPathResource("applicationContext.xml");
BeanFactory beans=new XmlBeanFactory(res);
Item p=(Item)beans.getBean("obj");
System.out.println(p.getItemId());
System.out.println(p.getItemName());
System.out.println(p.getQty());
<em>DriverManagerDataSource</em> dmds=new DriverManagerDataSource();
//For Oracle
//dmds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
//dmds.setUrl("jdbc:oracle:thin:@192.168.1.8:1521:orcl");
//dmds.setUsername("system");
//dmds.setPassword("admin");
//for MySQL
dmds.setDriverClassName("com.mysql.jdbc.Driver");
dmds.setUrl("jdbc:mysql://localhost:3306/javaskoolDB");
dmds.setUsername("root");
dmds.setPassword("admin");
<em>JdbcTemplate</em> template=new JdbcTemplate();
template.setDataSource(dmds);
template.execute("create table ItemTab(ItemId int, ItemName varchar(20), Qty int)");
System.out.println("Table Created....");
String query="insert into ItemTab values('"
+p.getItemId()+"','"+p.getItemName()+"','"+p.getQty()+"')";
//template.execute(query);
int row=template.update(query);
System.out.println(row+ " Rows inserted....");
}
}
Output
Mar 30, 2013 2:40:16 AM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
1001
Mouse
100
Mar 30, 2013 2:40:16 AM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver Table Created....
1 Rows inserted....
SQL Code
mysql> show create Table ItemTab;
CREATE TABLE `itemtab` (
`ItemId` int(11) DEFAULT NULL,
`ItemName` varchar(20) DEFAULT NULL,
`Qty` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> select * from itemTab;
+--------+----------+------+
| ItemId | ItemName | Qty |
+--------+----------+------+
| 1001 | Mouse | 100 |
+--------+----------+------+
1 row in set (0.02 sec)
mysql>
| Spring with NamedParameterJdbcTemplate Example |
Project Structure used in this example
Travel.Java
package com.javaskool;
public class Travel {
private int travelId;
private String Src;
private String Dest;
private int amt;
public int getTravelId() {
return travelId;
}
public void setTravelId(int travelId) {
this.travelId = travelId;
}
public String getSrc() {
return Src;
}
public void setSrc(String src) {
Src = src;
}
public String getDest() {
return Dest;
}
public void setDest(String dest) {
Dest = dest;
}
public int getAmt() {
return amt;
}
public void setAmt(int amt) {
this.amt = amt;
}
}
TravelMapper.Java
package com.javaskool;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class TravelMapper implements RowMapper<Travel> {
@Override
public Travel mapRow(ResultSet rs, int rowNum) throws SQLException {
// TODO Auto-generated method stub
Travel t = new Travel();
t.setTravelId(rs.getInt("tId"));
t.setDest(rs.getString("Dest"));
t.setSrc(rs.getString("Src"));
t.setAmt(rs.getInt("amt"));
return t;
}
}
/*
<em>RowMapper :</em>
An interface used by JdbcTemplate for mapping rows of a ResultSet on a per-row basis.
Implementations of this interface perform the actual work of mapping each row to a result object,
but don't need to worry about exception handling. SQLExceptions will be caught and handled by the calling JdbcTemplate.
*/
applicationContext.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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">
<bean id="tobj" class="com.javaskool.Travel">
<property name="travelId" value="9001"/>
<property name="Src" value="Bangalore"/>
<property name="Dest" value="USA"/>
<property name="amt" value="9600"/>
</bean>
<!-- For MySQL with database sabre-->
<bean id="dmds"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/javaskooldb"/>
<property name="username" value="root"/>
<property name="password" value="admin"/>
</bean>
<!-- For Oracle Express Edition with database XE-->
<!-- <bean id="dmds"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
<property name="username" value="SYSTEM"/>
<property name="password" value="admin"/>
</bean> -->
<bean id="namedtemplate"
class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg index="0" ref="dmds"></constructor-arg>
</bean>
</beans>
TestDrive.Java
package com.javaskool;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
public class TestDrive {
public static void main(String[] args)
{
Resource res = new ClassPathResource("applicationContext.xml");
BeanFactory beans=new XmlBeanFactory(res);
Travel t=(Travel)beans.getBean("tobj"); //getting Supplier Object from BeanFactory
System.out.println("Travel Id : "+t.getTravelId());
System.out.println("SRC : "+t.getSrc());
System.out.println("Dest : "+t.getDest());
System.out.println("Amt : "+t.getAmt());
<em>NamedParameterJdbcTemplate</em> namedtemplate=(NamedParameterJdbcTemplate)beans.getBean("namedtemplate");
String sql = "select count(0) from travelTab where Dest= :xyz";
SqlParameterSource namedParameters = new MapSqlParameterSource("xyz", t.getDest());
int i=namedtemplate.queryForInt(sql, namedParameters);
System.out.println("Total Travel for "+t.getDest()+" is "+i);
String query="insert into travelTab values(:id, :src,:dest,:amt)";
Map<String,Object> m=new HashMap<String,Object>();
m.put("id", t.getTravelId());
m.put("src", t.getSrc());
m.put("dest", t.getDest());
m.put("amt", t.getAmt());
Integer row=namedtemplate.execute(query, m, new PreparedStatementCallback<Integer>()
{
public Integer doInPreparedStatement(PreparedStatement ps)
throws SQLException ,DataAccessException
{
return ps.executeUpdate();
}
});
System.out.println(row+ " Rows inserted....");
}
}
SQL Code
mysql> use javaskooldb;
Database changed
mysql> create table traveltab
-> (
-> travelId int,
-> Src varchar(20),
-> Dest varchar(20),
-> Fare int
-> );
Query OK, 0 rows affected (1.39 sec)
mysql>
Output
Mar 02, 2014 6:44:28 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [applicationContext.xml]
Travel Id : 9001
SRC : Bangalore
Dest : USA
Amt : 9600
Mar 02, 2014 6:44:29 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
Total Travel for USA is 1
1 Rows inserted....
Click Below to download the Examples
Recent Comments