一、表(这里用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的情况
表的实体类Customer
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>
也可以使用DBCP连接池来配置数据源(需要导入commons-dbcp-1.4.jar包)
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/yiibai?useSSL=false" />
<property name="username" value="your-name" />
<property name="password" value="your-passwd" />
</bean>
这里需要修改用户密码来适应你的数据库环境
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模板大大简化代码。
四、 HibernateTemplate、HibernateDaoSupport实现版本
CustomerImplDAO类改为HibCustomerDao
package com.yiibai.springjdbc.daoimpl;
import java.util.List;
import org.springframework.orm.hibernate4.support.HibernateDaoSupport;
import com.yiibai.springjdbc.bean.Customer;
import com.yiibai.springjdbc.dao.CustomerDAO;
public class HibCustomerDao extends HibernateDaoSupport implements CustomerDAO {
@Override
public void insert(Customer customer) {
// TODO 自动生成的方法存根
this.getHibernateTemplate().save(customer);
}
@Override
public Customer findByCustomerId(int custId) {
// TODO 自动生成的方法存根
//或find("from Customer where CUST_ID = ?",custId).get(0);
return (Customer) getHibernateTemplate().get(Customer.class, custId);
}
@Override
public List<Customer> queryCustomer() throws Exception {
// TODO 自动生成的方法存根
return (List<Customer>) getHibernateTemplate().find("from com.yiibai.springjdbc.bean.Customer");
}
}
配置文件修改就比较复杂了:要配置SessionFactory、transactionManager、transactionInterceptor等。
,另外要在包com.yiibai.springjdbc.bean增加表对象Customer的Hibernate映射文件Customer.hbm.xml以供配置hibernate SessionFactory使用:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.yiibai.springjdbc.bean">
<class name="Customer" table="customer">
<id name="custId" type="java.lang.Integer">
<column name="CUST_ID" />
<generator class="native"/>
</id>
<property name="name" unique="true" type="java.lang.String">
<column name="NAME" />
</property>
<property name="age" unique="true" type="java.lang.Integer">
<column name="AGE" />
</property>
</class>
</hibernate-mapping>
修改后的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"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/jee
http://www.springframework.org/schema/jee/spring-jee.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd
http://www.springframework.org/schema/tool
http://www.springframework.org/schema/tool/spring-tool.xsd">
<!-- 把数据源注入给Session工厂 -->
<bean id="custsessionFactory"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource1" />
<property name="mappingResources">
<list>
<value>com/yiibai/springjdbc/bean/Customer.hbm.xml</value>
</list>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.hbm2ddl.auto">update</prop>
<prop key="hibernate.show_sql">true</prop>
<prop key="hibernate.generate_statistics">true</prop>
<prop key="hibernate.current_session_context_class">org.springframework.orm.hibernate4.SpringSessionContext</prop>
</props>
</property>
</bean>
<!-- Transaction manager for a single Hibernate SessionFactory (alternative
to JTA) -->
<bean id="transactionManager"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="custsessionFactory" />
</bean>
<!--define bean of transaction interceptor -->
<bean id="transactionInterceptor"
class="org.springframework.transaction.interceptor.TransactionInterceptor">
<property name="transactionManager" ref="transactionManager" />
<property name="transactionAttributes">
<props>
<prop key="delete*">PROPAGATION_REQUIRED</prop>
<prop key="update*">PROPAGATION_REQUIRED</prop>
<prop key="save*">PROPAGATION_REQUIRED,-Exception</prop>
<prop key="find*">PROPAGATION_REQUIRED,readOnly</prop>
<prop key="*">PROPAGATION_REQUIRED</prop>
</props>
</property>
</bean>
<bean
class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator">
<property name="beanNames">
<list>
<value>*Dao</value>
</list>
</property>
<property name="interceptorNames">
<list>
<value>transactionInterceptor</value>
</list>
</property>
</bean>
<bean id="customerDAO" class="com.yiibai.springjdbc.daoimpl.HibCustomerDao">
<property name="sessionFactory" ref="custsessionFactory" />
</bean>
</beans>
如果仅配置SessionFactory、而不配置transactionManager、transactionInterceptor,查询没问题,而插入不行,会出现下面的异常:
Exception in thread "main" org.springframework.dao.InvalidDataAccessApiUsageException: Write operations are not allowed in read-only mode (FlushMode.MANUAL): Turn your Session into FlushMode.COMMIT/AUTO or remove 'readOnly' marker from transaction definition.
有没有办修改SessionFactory的设置解决这个问题,求高人指点。
hibernate配置也可以用注解方式(无需Customer.hbm.xml):
修改Customer类如下( custId必须要改CUST_ID,和表格字段名完全一致):
package com.yiibai.springjdbc.bean;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "customer")
public class Customer {
@Id
int CUST_ID;
String name;
int age;
public Customer() {
super();
// TODO 自动生成的构造函数存根
}
public Customer(int custId, String name, int age) {
super();
this.CUST_ID = custId;
this.name = name;
this.age = age;
}
public int getCustId() {
return CUST_ID;
}
public void setCustId(int custId) {
this.CUST_ID = 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=" + CUST_ID + ", name=" + name + ", age=" + age + "]";
}
}
spring-dao.xml文件的custsessionFactory配置中
<property name="mappingResources">
<list>
<value>com/yiibai/springjdbc/bean/Customer.hbm.xml</value>
</list>
</property>
改为:
<property name="annotatedClasses">
<list>
<value>com.yiibai.springjdbc.bean.Customer</value>
</list>
</property>
另外经实践.hbm.xml版本(注射方式则不会,我也没搞明白其中的道理)的CUST_ID不是根据insert(customer)传递过来参数的值,而是会根据数据库表customer当前的ID“指针”;比如传递过来的参数是Customer(1, "yiibai",29),插入后有可能变(3, "yiibai",29)。
可用下面命令来复位ID“指针”
mysql> use yiibai;
mysql> ALTER TABLE customer AUTO_INCREMENT=0;
这样新插入的CUST_ID值就是:最后一条记录CUST_ID+1。
五、mybatis、SqlSessionDaoSupport版本
为了简单起见,使用注解方式使用mybatis(和XML配置可以混用的,详见该文),重写了dao接口放在com.yiibai.springjdbc.mybatisdao包下,为保证主类代码不变原来的接口CustomerDAO继续使用。
package com.yiibai.springjdbc.mybatisdao;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import com.yiibai.springjdbc.bean.Customer;
public interface ICustomer {
@Insert("insert into customer(CUST_ID,name,age) values(#{CUST_ID},#{name}, #{age})")
public void insert(Customer customer);
@Select("select * from customer where CUST_ID= #{CUST_ID}")
public Customer findByCustomerId(int custId);
@Select("select * from customer")
public List<Customer> queryCustomer();
@Delete("delete from customer where CUST_ID=#{CUST_ID}")
public int deleteCustomerById(int id);
}
所有的sql操作由该接口完成,后面的DAO实现类MybatisCustImpDao,实际上仅仅调用该接口的方法:
package com.yiibai.springjdbc.daoimpl;
import java.util.List;
import org.mybatis.spring.support.SqlSessionDaoSupport;
import com.yiibai.springjdbc.bean.Customer;
import com.yiibai.springjdbc.dao.CustomerDAO;
import com.yiibai.springjdbc.mybatisdao.ICustomer;
public class MybatisCustImpDao extends SqlSessionDaoSupport implements CustomerDAO {
@Override
public void insert(Customer customer) {
// TODO 自动生成的方法存根
this.getSqlSession().getMapper(ICustomer.class).insert(customer);;
}
@Override
public Customer findByCustomerId(int custId) {
// TODO 自动生成的方法存根
return this.getSqlSession().getMapper(ICustomer.class).findByCustomerId(custId);
}
@Override
public List<Customer> queryCustomer() throws Exception {
// TODO 自动生成的方法存根
return this.getSqlSession().getMapper(ICustomer.class).queryCustomer();
}
}
mybatis的配置文件mybatiscust.xml放在com.yiibai.springjdbc下
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<typeAlias alias="Customer" type="com.yiibai.springjdbc.bean.Customer" />
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://127.0.0.1:3306/yiibai?useSSL=false" />
<property name="username" value="your-user" />
<property name="password" value="your-passwd" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- XML的方式 注册映射配置文件-->
<!-- <mapper resource="com/yiibai/springjdbc/bean/CustMybatis.xml" /> -->
<!--接口的方式 注册接口-->
<mapper class="com.yiibai.springjdbc.mybatisdao.ICustomer"/>
</mappers>
</configuration>
bean必须注入sqlSessionFactory或sqlSessionTemplate。还是在中spring-dao.xml配置:
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation"
value="classpath:com/yiibai/springjdbc/mybatiscust.xml" />
</bean>
<bean id="CustomerDao" class="com.yiibai.springjdbc.daoimpl.MybatisCustImpDao">
<property name="sqlSessionFactory" ref="sqlSessionFactory" />
</bean>
或
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation"
value="classpath:com/yiibai/springjdbc/mybatiscust.xml" />
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg ref="sqlSessionFactory" />
</bean>
<bean id="CustomerDao" class="com.yiibai.springjdbc.daoimpl.MybatisCustImpDao">
<property name="sqlSessionTemplate" ref="sqlSession" />
</bean>
主程序还是不变。
参考:
Spring Mybatis实例SqlSessionDaoSupport混用xml配置和注解
HibernateTemplate、HibernateDaoSupport两种方法实现增删
Spring JdbcTemplate+JdbcDaoSupport实例
如何通过继承SqlSessionDaoSupport来编写DAO(一)
项目的代码和依赖包都在这里,下后解压到eclipse的workspace导入选择import Porojects from File System or Archive。