一、表(这里用mysql,数据库名为yiibai)
CREATE TABLE `customer` ( `CUST_ID` int(10) UNSIGNED NOT NULL, `NAME` varchar(100) NOT NULL, `AGE` int(10) UNSIGNED NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `customer` ADD PRIMARY KEY (`CUST_ID`);
二、不用JdbcTemplate的情况
表的映射类
package com.yiibai.springjdbc.bean; public class Customer { int custId; String name; int age; public Customer(int custId, String name, int age) { super(); this.custId = custId; this.name = name; this.age = age; } public int getCustId() { return custId; } public void setCustId(int custId) { this.custId = custId; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } @Override public String toString() { return "Customer [custId=" + custId + ", name=" + name + ", age=" + age + "]"; } }
DAO接口
package com.yiibai.springjdbc.dao; import java.util.List; import com.yiibai.springjdbc.bean.Customer; public interface CustomerDAO { public void insert(Customer customer); public Customer findByCustomerId(int custId); public List<Customer> queryCustomer() throws Exception ; }
DAO实现(不用JdbcTemplate)
package com.yiibai.springjdbc.daoimpl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import javax.sql.DataSource; import com.yiibai.springjdbc.bean.Customer; import com.yiibai.springjdbc.dao.CustomerDAO; public class CustomerImplDAO implements CustomerDAO { private DataSource dataSource; @Override public void insert(Customer customer) { // TODO 自动生成的方法存根 String sql = "INSERT INTO customer " + "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)"; Connection conn = null; try { conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, customer.getCustId()); ps.setString(2, customer.getName()); ps.setInt(3, customer.getAge()); ps.executeUpdate(); ps.close(); } catch (SQLException e) { throw new RuntimeException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { } } } } @Override public Customer findByCustomerId(int custId) { // TODO 自动生成的方法存根 String sql = "SELECT * FROM customer WHERE CUST_ID = ?"; Connection conn = null; try { conn = dataSource.getConnection(); PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, custId); Customer customer = null; ResultSet rs = ps.executeQuery(); if (rs.next()) { customer = new Customer(rs.getInt("CUST_ID"), rs.getString("NAME"), rs.getInt("Age")); } rs.close(); ps.close(); return customer; } catch (SQLException e) { throw new RuntimeException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { } } } } public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } @Override public List<Customer> queryCustomer() throws Exception { // TODO 自动生成的方法存根 Connection conn = dataSource.getConnection(); String sql = "Select c.CUST_ID, c.NAME, c.AGE from customer c"; System.out.println(sql); Statement smt = conn.createStatement(); ResultSet rs = smt.executeQuery(sql); List<Customer> list = new ArrayList<Customer>(); while (rs.next()) { int cID = rs.getInt("CUST_ID"); String cName = rs.getString("NAME"); int cAge = rs.getInt("AGE"); Customer cust = new Customer(cID, cName, cAge); list.add(cust); } return list; } }
配置文件spring-dao.xml spring-datasource.xml spring-module.xml都放置在(特别重要)包com.yiibai.springjdbc下面:
spring-datasource.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.xsd"> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/yiibai?useSSL=false" /> <property name="username" value="your-user" /> <property name="password" value="your-passwd" /> </bean> </beans>
这里需要修改用户密码来适应你的数据库环境
spring-dao.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.xsd"> <bean id="customerDAO" class="com.yiibai.springjdbc.daoimpl.CustomerImplDAO"> <property name="dataSource" ref="dataSource" /> </bean> </beans>
spring-module.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.xsd"> <!-- Using Mysql datasource --> <import resource="spring-datasource.xml" /> <import resource="spring-dao.xml" /> </beans>
测试(主)类
package com.yiibai.springjdbc; import java.util.List; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.yiibai.springjdbc.bean.Customer; import com.yiibai.springjdbc.dao.CustomerDAO; public class CustTest { private static ApplicationContext ctx; public static void main(String[] args) throws Exception { ctx = new ClassPathXmlApplicationContext("com/yiibai/springjdbc/spring-module.xml"); CustomerDAO customerDAO = (CustomerDAO) ctx.getBean("customerDAO"); Customer customer = new Customer(1, "yiibai",29); customerDAO.insert(customer); Customer customer1 = customerDAO.findByCustomerId(1); System.out.println(customer1); List<Customer> custList = customerDAO.queryCustomer(); for(Customer cs : custList){ System.out.println("Customer ID " + cs.getCustId()); System.out.println("Customer Name " + cs.getName()); System.out.println("Customer Age" + cs.getAge()); System.out.println("----------------------------"); } } }
运行结果:表customer加了一条记录,并输出如下信息:
(执行前把表customer中id为1的记录删除,不然插入异常)
三、使用 JdbcTemplate、JdbcDaoSupport实现
Customer和DAO接口不变,主要变化是DAO实现:CustomerImplDAO类改为JdbcCustomerDAO
package com.yiibai.springjdbc.daoimpl; import java.util.List; import org.springframework.jdbc.core.support.JdbcDaoSupport; import com.yiibai.springjdbc.bean.Customer; import com.yiibai.springjdbc.bean.CustomerRowMapper; import com.yiibai.springjdbc.dao.CustomerDAO; public class JdbcCustomerDAO extends JdbcDaoSupport implements CustomerDAO { @Override public void insert(Customer customer) { // TODO 自动生成的方法存根 String sql = "INSERT INTO customer " + "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)"; getJdbcTemplate().update(sql, new Object[] { customer.getCustId(), customer.getName(),customer.getAge() }); } @Override public Customer findByCustomerId(int custId) { // TODO 自动生成的方法存根 /* * 这种写法也可以 String sql = "SELECT * FROM customer WHERE CUST_ID = '"+custId+"' "; return getJdbcTemplate().queryForObject(sql,new CustomerRowMapper()); */ String sql = "SELECT * FROM customer WHERE CUST_ID = ?"; return getJdbcTemplate().queryForObject(sql,new Object[] { custId },new CustomerRowMapper()); } @Override public List<Customer> queryCustomer() throws Exception { // TODO 自动生成的方法存根 String sql = "SELECT * FROM customer"; return getJdbcTemplate().query(sql, new CustomerRowMapper()); } }
需要说明2点:
1、本实现继承JdbcDaoSupport,而 JdbcDaoSupport定义了 JdbcTemplate和DataSource 属性,只需在配置文件中注入DataSource 即可,然后会创建jdbcTemplate的实例,不必像前面的实现CustomerImplDAO那样,需要显式定义一个DataSource成员变量。
2、这里出现了CustomerRowMapper类:本来应该这样写的queryForObject(sql,Customer.class);但Spring并不知道如何将结果转成Customer.class。所以需要写一个CustomerRowMapper 继承RowMapper接口 ,其代码如下:
package com.yiibai.springjdbc.bean; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; public class CustomerRowMapper implements RowMapper<Customer> { @Override public Customer mapRow(ResultSet rs, int rowNum) throws SQLException { // TODO 自动生成的方法存根 return new Customer(rs.getInt("CUST_ID"),rs.getString("NAME"),rs.getInt("AGE")); } }
文件spring-dao.xml里bean的定义修改为(变化的是class):
<bean id="customerDAO" class="com.yiibai.springjdbc.daoimpl.JdbcCustomerDAO"> <property name="dataSource" ref="dataSource" /> </bean>
其他配置文件和主类都不变、运行结果少了Select c.CUST_ID, c.NAME, c.AGE from customer c
,这是因为CustomerImplDAO版本人为地插入一句 System.out.println(sql);以示和JDBC模板实现版本JdbcCustomerDAO的区别。
可以看出采用JDBC模板大大简化代码。
参考:
Spring JdbcTemplate+JdbcDaoSupport实例
Spring与Dao-Jdbc模板实现增删改查
使用Jdbc Template的基本操作步骤
Spring进行面向切面编程的一个简单例子