1.Spring JDBC:是Spring所提供的持久层技术,它的主要目标是降低使用JDBC API的门槛,以 一种更直接,更简介,更简单的方式使用JDBC API.
作用:在完全依赖查询模型动态产生查询语句的综合查询系中,Hibernaye,MyBatis,JPA等框架都无法使用,这里Spring
JDBC是唯一的选择.
2.SpringJdbc使用:
2.1:JdbcTemplate Spring操作数据模板类(工具类)
常用方法:
jdbcTemplate.update(sql,Object...ob); //增加,修改,删除
jdbcTemplate.queryForObject(sql,Object[], RowMapper<T> var2); //查询单个对象
jdbcTemplate.query(sql,RowMapper<T> var2); //查询所有记录数
2.2:SpringJdbc的使用:
eg:/**
* 添加学生的方法
* @param s
* @return int
*/
public int addStudent(Student s){
//获得spring的jdbc的模板类的对象,将连接池作为参数
JdbcTemplate jt=new JdbcTemplate(new ComboPooledDataSource());
//准备sql语句
String sql="insert into t_student(name,PASSWORD,sex,email,hobby,city) "
+ "values(?,?,?,?,?,?)";
//用springjdbc的模板调用方法来执行操作
int result=jt.update(sql,s.getName(),s.getPassword(),s.getSex(),s.getEmail(),s.getHobby(),s.getCity());
return result;
}
/**
* 修改学生的方法
* @param s
* @return int
*/
public int updateStudent(Student s){
//获得spring的jdbc的模板类的对象,将连接池作为参数
JdbcTemplate jt=new JdbcTemplate(new ComboPooledDataSource());
//准备sql语句
String sql="update t_student set PASSWORD=? where name=?";
//用springjdbc的模板调用方法来执行操作
int result=jt.update(sql,s.getPassword(),s.getName());
return result;
}
/**
* 添加学生的方法
* @param name
* @return int
*/
public int deleteStudent(String name){
//获得spring的jdbc的模板类的对象,将连接池作为参数
JdbcTemplate jt=new JdbcTemplate(new ComboPooledDataSource());
//准备sql语句
String sql="delete from t_student where name=? ";
//用springjdbc的模板调用方法来执行操作
int result=jt.update(sql,name);
return result;
}
/**
* 查询单个值
* @return
*/
public int getTotal(){
//获得spring的jdbc的模板类的对象,将连接池作为参数
JdbcTemplate jt=new JdbcTemplate(new ComboPooledDataSource());
//准备sql语句
String sql="select count(name) from t_student ";
//用springjdbc的模板调用方法来执行操作
int result=jt.queryForObject(sql,Integer.class);
return result;
}
/**
* 查询单个对象
* @return
*/
public Student getStudent(String name){
//获得spring的jdbc的模板类的对象,将连接池作为参数
JdbcTemplate jt=new JdbcTemplate(new ComboPooledDataSource());
//准备sql语句
String sql="select name,PASSWORD,sex,email,hobby,city from t_student where name=?";
//用springjdbc的模板调用方法来执行操作
Student stu=jt.queryForObject(sql, new Object[]{name}, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet rs, int i) throws SQLException {
//声明学生对象,来存查询当前行每个列的值
Student stu2=new Student();
stu2.setName(rs.getString("name"));
stu2.setCity(rs.getString("city"));
stu2.setEmail(rs.getString("email"));
stu2.setHobby(rs.getString("hobby"));
stu2.setSex(rs.getString("sex"));
stu2.setPassword(rs.getString("password"));
return stu2;
}
});
return stu;
}
/**
* 查询单个对象
* @return
*/
public List<Student> allStudent(){
//获得spring的jdbc的模板类的对象,将连接池作为参数
JdbcTemplate jt=new JdbcTemplate(new ComboPooledDataSource());
//准备sql语句
String sql="select name,PASSWORD,sex,email,hobby,city from t_student";
//用springjdbc的模板调用方法来执行操作
List<Student> stuList=jt.query(sql, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet rs, int i) throws SQLException {
//声明学生对象,来存查询当前行每个列的值
Student stu2=new Student();
stu2.setName(rs.getString("name"));
stu2.setCity(rs.getString("city"));
stu2.setEmail(rs.getString("email"));
stu2.setHobby(rs.getString("hobby"));
stu2.setSex(rs.getString("sex"));
stu2.setPassword(rs.getString("password"));
return stu2;
}
});
return stuList;
}
2.3:SpringJdbc的JdbcDaoSupport 使用:
eg:public class StudentDaoImpl extends JdbcDaoSupport {
/**
* 添加学生的方法
* @param s
* @return int
*/
public int addStudent(Student s){
//准备sql语句
String sql="insert into t_student(name,PASSWORD,sex,email,hobby,city) "
+ "values(?,?,?,?,?,?)";
//用springjdbc的模板调用方法来执行操作
int result=this.getJdbcTemplate().update(sql,s.getName(),s.getPassword(),s.getSex(),s.getEmail(),s.getHobby(),s.getCity());
return result;
}
/**
* 修改学生的方法
* @param s
* @return int
*/
public int updateStudent(Student s){
//准备sql语句
String sql="update t_student set PASSWORD=? where name=?";
//用springjdbc的模板调用方法来执行操作
int result=this.getJdbcTemplate().update(sql,s.getPassword(),s.getName());
return result;
}
/**
* 添加学生的方法
* @param name
* @return int
*/
public int deleteStudent(String name){
//准备sql语句
String sql="delete from t_student where name=? ";
//用springjdbc的模板调用方法来执行操作
int result=this.getJdbcTemplate().update(sql,name);
return result;
}
/**
* 查询单个值
* @return
*/
public int getTotal(){
//准备sql语句
String sql="select count(name) from t_student ";
//用springjdbc的模板调用方法来执行操作
int result=this.getJdbcTemplate().queryForObject(sql,Integer.class);
return result;
}
/**
* 查询单个对象
* @return
*/
public Student getStudent(String name){
//准备sql语句
String sql="select name,PASSWORD,sex,email,hobby,city from t_student where name=?";
//用springjdbc的模板调用方法来执行操作
Student stu=this.getJdbcTemplate().queryForObject(sql, new Object[]{name}, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet rs, int i) throws SQLException {
//声明学生对象,来存查询当前行每个列的值
Student stu2=new Student();
stu2.setName(rs.getString("name"));
stu2.setCity(rs.getString("city"));
stu2.setEmail(rs.getString("email"));
stu2.setHobby(rs.getString("hobby"));
stu2.setSex(rs.getString("sex"));
stu2.setPassword(rs.getString("password"));
return stu2;
}
});
return stu;
}
/**
* 查询单个对象
* @return
*/
public List<Student> allStudent(){
//准备sql语句
String sql="select name,PASSWORD,sex,email,hobby,city from t_student";
//用springjdbc的模板调用方法来执行操作
List<Student> stuList=this.getJdbcTemplate().query(sql, new RowMapper<Student>() {
@Override
public Student mapRow(ResultSet rs, int i) throws SQLException {
//声明学生对象,来存查询当前行每个列的值
Student stu2=new Student();
stu2.setName(rs.getString("name"));
stu2.setCity(rs.getString("city"));
stu2.setEmail(rs.getString("email"));
stu2.setHobby(rs.getString("hobby"));
stu2.setSex(rs.getString("sex"));
stu2.setPassword(rs.getString("password"));
return stu2;
}
});
return stuList;
}
}
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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd">
<!--加载数据库连接池的配置-->
<context:property-placeholder location="c3p0.properties"></context:property-placeholder>
<!--让Spring管理连接池:将配置文件中信息配置连接池中-->
<bean name="dataSources" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${c3p0.driverClass}"></property>
<property name="jdbcUrl" value="${c3p0.jdbcUrl}"></property>
<property name="user" value="${c3p0.user}"></property>
<property name="password" value="${c3p0.password}"></property>
</bean>
<!--声明springJdbc工具类-->
<bean name="JdbcTemplate1" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSources"></property>
</bean>
<!--声明Dao-->
<bean name="userDao1" class="demo2.UserDaoImpl">
<property name="jdbcTemplate" ref="JdbcTemplate1"></property>
</bean>
</beans>
//SpringJDBC结合配置文件测试调用
public class StudentDaoImplTest2 {
/**
* 测试添加学生功能
*/
@Test
public void addStudentTest(){
//加载spring容器
ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext1.xml");
//创建学生访问类对象
StudentDaoImpl stuDao=ac.getBean("studentDao", demo2.StudentDaoImpl.class);
Student stu1=new Student("慧姐","123456","woman","java@qq.com","[eat,sleep]","东京");
//调用添加学生方法
int result= stuDao.addStudent(stu1);
if (result>0){
System.out.println("添加成功");
}else{
System.out.println("添加失败");
}
}
/**
* 测试修改学生功能
*/
@Test
public void updateStudentTest(){
//加载spring容器
ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext1.xml");
//创建学生访问类对象
StudentDaoImpl stuDao=ac.getBean("studentDao", demo2.StudentDaoImpl.class);
Student stu1=new Student("慧姐","666666","woman","java@qq.com","[eat,sleep]","东京");
//调用修改学生方法
int result= stuDao.updateStudent(stu1);
if (result>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
}
/**
* 测试删除学生功能
*/
@Test
public void deleteStudentTest(){
//加载spring容器
ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext1.xml");
//创建学生访问类对象
StudentDaoImpl stuDao=ac.getBean("studentDao", demo2.StudentDaoImpl.class);
//调用删除学生方法
int result= stuDao.deleteStudent("java之父");
if (result>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}
/**
* 测试查询总记录的方法
*/
@Test
public void getTotalTest(){
//加载spring容器
ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext1.xml");
//创建学生访问类对象
StudentDaoImpl stuDao=ac.getBean("studentDao", demo2.StudentDaoImpl.class);
//调用删除学生方法
int result= stuDao.getTotal();
System.out.println("总学生数为:"+result);
}
/**
* 测试查询单个学生的方法
*/
@Test
public void getStudentTest(){
//加载spring容器
ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext1.xml");
//创建学生访问类对象
StudentDaoImpl stuDao=ac.getBean("studentDao", demo2.StudentDaoImpl.class);
//调用学生方法
Student stu= stuDao.getStudent("慧姐");
System.out.println("学生为:"+stu);
}
/**
* 测试查询所有学生的方法
*/
@Test
public void allStudentTest(){
//加载spring容器
ApplicationContext ac=new ClassPathXmlApplicationContext("applicationContext1.xml");
//创建学生访问类对象
StudentDaoImpl stuDao=ac.getBean("studentDao", demo2.StudentDaoImpl.class);
//调用学生方法
List<Student> stuList= stuDao.allStudent();
stuList.stream().forEach(System.out::println);
}
}
3.Spring的声明式事务
事务传播性有七种:
PROPAGATION_REQUIRED 表示当前方法必须运行在事务中。有事务就使用,没事务就创建一个事务来使用.
PROPAGATION_SUPPORTS 表示当前方法不需要事务上下文,有事务就使用,没有就不使用事务
PROPAGATION_MANDATORY 表示该方法必须在事务中运行,如果当前事务不存在,则会抛出一个异常
PROPAGATION_REQUIRED_NEW 表示当前方法必须运行在它自己的事务中.创建一个新事务来使用.
PROPAGATION_NOT_SUPPORTED 表示该方法不应该运行在事务中。如果存在当前事务,在该方法运行期间,当前事务将被挂起.
PROPAGATION_NEVER 表示当前方法不应该运行在事务上下文中。如果当前正有一个事务在运行,则会抛出异常
PROPAGATION_NESTED 表示如果当前已经存在一个事务,那么该方法将会在嵌套事务中运行。嵌套的事务可以独立于当前事务进行单独地提交或回滚。如果当前事务不存在,那么其行为与PROPAGATION_REQUIRED一样。注意各
厂商对这种传播行为的支持是有所差异的。可以参考资源管理器的文档来确认它们是否支持嵌套事务
3.1:配置版:在业务实现类的方法上起作用
<?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:p="http://www.springframework.org/schema/p"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<!--配置注解扫描-->
<context:component-scan base-package="demo3"></context:component-scan>
<!--启动cglib动态代理-->
<aop:config proxy-target-class="true"></aop:config>
<!--将数据源的配置文件加载spring容器中-->
<context:property-placeholder location="jdbc.properties"></context:property-placeholder>
<!--在spring容器中配置连接池-->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="${c3p0.jdbcUrl}"></property>
<property name="driverClass" value="${c3p0.driverClass}"></property>
<property name="user" value="${c3p0.user}"></property>
<property name="password" value="${c3p0.password}"></property>
</bean>
<!--spring jdbc的JdbcTemplate模板,版注入到BankDaoImpl的属性中-->
<bean name="jdbcTemplate1" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--配置版spring声明式事务-->
<!--1.配置事务管理器,事务管理器3种:DataSourceTransactionManager,HibernateTransactionManager,JPATransactionManager-->
<bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!--2.按spring封装的底层来配置增强处理-->
<tx:advice id="myAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="save*" propagation="REQUIRED"/>
<tx:method name="update*" propagation="REQUIRED"/>
<tx:method name="delete*" propagation="REQUIRED"/>
<tx:method name="query*" propagation="REQUIRED" read-only="true"/>
<tx:method name="get*" propagation="REQUIRED" read-only="true"/>
<tx:method name="transferAccount" propagation="REQUIRED"/>
</tx:attributes>
</tx:advice>
<!--3.将增强处理织入切点-->
<aop:config>
<!--声明切点-->
<aop:pointcut id="cut1" expression="execution(* demo3.BankServiceImpl.*(..))"/>
<!--将增强处理绑定到切点上-->
<aop:advisor advice-ref="myAdvice" pointcut-ref="cut1"></aop:advisor>
</aop:config>
</beans>
3.2:Spring声明式事务注解版
/**
* 银行业务实现类
* @version 1.0
* @auth sx
* @date 2019/12/26
*/
@Transactional//事务注解
@Service("bankServer1")
public class BankServiceImpl implements IbankService {
/**
* 声明银行数据访问对象
*/
@Autowired
private IbankDAO bankDAO;
@Override
@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.REPEATABLE_READ)//配置事务处理机制
public boolean updateTransferAccounts(String fromAcount, String toAcount, double money) {
int result1=bankDAO.updateAddMoney(toAcount,money);//转入账号
//制造一个异常
//int num=8/0;
int result2=bankDAO.updateMinusMoney(fromAcount,money);//转出账号
if (result1>0&&result2>0){
return true;
}
return false;
}
}
spring配置文件
<?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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<!--加载数据库连接池的配置-->
<context:property-placeholder location="c3p0.properties"></context:property-placeholder>
<!--让Spring管理连接池:将配置文件中信息配置连接池中-->
<bean name="dataSources" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="driverClass" value="${c3p0.driverClass}"></property>
<property name="jdbcUrl" value="${c3p0.jdbcUrl}"></property>
<property name="user" value="${c3p0.user}"></property>
<property name="password" value="${c3p0.password}"></property>
</bean>
<!--声明springJdbc工具类-->
<bean name="JdbcTemplate1" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSources"></property>
</bean>
<!--声明事务管理器-->
<bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSources"></property>
</bean>
<!--配置动态代理用CGLib-->
<aop:config proxy-target-class="true"></aop:config>
<!--扫描指定包中注解-->
<context:component-scan base-package="demo4"></context:component-scan>
<!--配置事务注解驱动-->
<tx:annotation-driven transaction-manager="transactionManager"></tx:annotation-driven>
</beans>