Spring整合JDBC
导包
image.png
创建一个测试类
在下表中插入一条数据
image.png
package com.cuzz.jdbcTemplate;
import java.beans.PropertyVetoException;
import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class Demo {
@Test
public void connection() throws PropertyVetoException {
// 准备连接池
ComboPooledDataSource dataSource = new ComboPooledDataSource();
// 创建模板
dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql:///jdbc");
dataSource.setUser("root");
dataSource.setPassword("123456");
// 创建JDBC模板对象
JdbcTemplate jt = new JdbcTemplate();
jt.setDataSource(dataSource);
// 书写sql
String sql = "INSERT INTO login VALUES(null, 'xiaoMing', '111111')";
jt.update(sql);
}
}
成功插入一条数据
Sring管理连接池
- 新建一个
User类
package com.cuzz.pojo;
public class User {
private Integer Id;
private String username;
private String passowrd;
public Integer getId() {
return Id;
}
public void setId(Integer id) {
Id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassowrd() {
return passowrd;
}
public void setPassowrd(String passowrd) {
this.passowrd = passowrd;
}
}
- 新建一个
UserService的接口
package com.cuzz.service;
import java.util.List;
import com.cuzz.pojo.User;
public interface UserService {
// 添加
void add(User user);
// 删除
void delete(Integer id);
// 修改
void update(User user);
// 查找
User get(Integer id);
// 返回总数
int getTotalcount();
// 返回所有
List<User> list();
}
- 新建一个实现
UserService接口的类
package com.cuzz.service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import com.cuzz.pojo.User;
public class UserServiceImpl implements UserService {
private JdbcTemplate jt;
// 注入属性
public JdbcTemplate getJt() {
return jt;
}
public void setJt(JdbcTemplate jt) {
this.jt = jt;
}
@Override
public void add(User user) {
String sql = "INSERT INTO login VALUES(null, ?, ?)";
jt.update(sql, user.getUsername(), user.getPassowrd());
}
@Override
public void delete(Integer id) {
String sql = "DELETE FROM login WHERE id = ?";
jt.update(sql, id);
}
@Override
public void update(User user) {
String sql = "UPDATE login SET username = ?, password = ? WHERE id = ?";
jt.update(sql, user.getUsername(), user.getPassowrd(), user.getId());
}
@Override
public User get(Integer id) {
String sql = "SELETE * FROM login WHERE id = ?";
return jt.queryForObject(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setUsername(rs.getString("username"));
u.setPassowrd(rs.getString("password"));
return u;
}
},id);
}
@Override
public int getTotalcount() {
String sql = "SELETE COUNT(*) FROM login";
Integer count = jt.queryForObject(sql, Integer.class);
return count;
}
@Override
public List<User> list() {
String sql = "SELETE * FROM login";
List<User> list = jt.query(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setId(rs.getInt("id"));
u.setUsername(rs.getString("username"));
u.setPassowrd(rs.getString("password"));
return u;
}});
return list;
}
}
- 配置文件
<?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:aop="http://www.springframework.org/schema/aop"
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-3.0.xsd
http://www.springframework.org/schema/aop
http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.0.xsd">
<!-- 指定spring读取db.properties配置 -->
<context:property-placeholder location="classpath:db.properties" />
<!-- 将连接池放入spring容器 -->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
<property name="jdbcUrl" value="${jdbc.jdbcUrl}" ></property>
<property name="driverClass" value="${jdbc.driverClass}" ></property>
<property name="user" value="${jdbc.user}" ></property>
<property name="password" value="${jdbc.password}" ></property>
</bean>
<!-- 将JDBCTemplate放入spring容器 -->
<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
<property name="dataSource" ref="dataSource" ></property>
</bean>
<!-- 将UserService放入spring容器 -->
<bean name="userService" class="com.cuzz.service.UserServiceImpl" >
<property name="jt" ref="jdbcTemplate" ></property>
</bean>
</beans>
db.properties配置
jdbc.jdbcUrl=jdbc:mysql:///jdbc
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=123456
注意：后面不能有空格
- 测试
package com.cuzz.test;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.cuzz.pojo.User;
import com.cuzz.service.UserService;
public class TestDemo {
@Test
public void fun1() {
// 创建spring工厂
ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
// 通过工厂解析xml获取Bean的实例
UserService userService = (UserService) applicationContext.getBean("userService");
User u = new User();
u.setUsername("admin");
u.setPassowrd("admin");
userService.add(u);
}
}
可以看出在数据库中新增了一条数据
image.png
拓展JDBCDaoSupport
- 继承一个
JdbcDaosupport类
package com.cuzz.service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.cuzz.pojo.User;
public class UserServiceImpl extends JdbcDaoSupport implements UserService {
@Override
public void add(User user) {
String sql = "INSERT INTO login VALUES(null, ?, ?)";
super.getJdbcTemplate().update(sql, user.getUsername(), user.getPassowrd());
}
@Override
public void delete(Integer id) {
String sql = "DELETE FROM login WHERE id = ?";
super.getJdbcTemplate().update(sql, id);
}
@Override
public void update(User user) {
String sql = "UPDATE login SET username = ?, password = ? WHERE id = ?";
super.getJdbcTemplate().update(sql, user.getUsername(), user.getPassowrd(), user.getId());
}
@Override
public User get(Integer id) {
String sql = "SELETE * FROM login WHERE id = ?";
return super.getJdbcTemplate().queryForObject(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setUsername(rs.getString("username"));
u.setPassowrd(rs.getString("password"));
return u;
}
},id);
}
@Override
public int getTotalcount() {
String sql = "SELETE COUNT(*) FROM login";
Integer count = super.getJdbcTemplate().queryForObject(sql, Integer.class);
return count;
}
@Override
public List<User> list() {
String sql = "SELETE * FROM login";
List<User> list = super.getJdbcTemplate().query(sql, new RowMapper<User>(){
@Override
public User mapRow(ResultSet rs, int arg1) throws SQLException {
User u = new User();
u.setId(rs.getInt("id"));
u.setUsername(rs.getString("username"));
u.setPassowrd(rs.getString("password"));
return u;
}});
return list;
}
}
- 配置
<!-- 将连接池放入spring容器 -->
<bean name="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
<property name="jdbcUrl" value="${jdbc.jdbcUrl}" ></property>
<property name="driverClass" value="${jdbc.driverClass}" ></property>
<property name="user" value="${jdbc.user}" ></property>
<property name="password" value="${jdbc.password}" ></property>
</bean>
<!-- 将JDBCTemplate放入spring容器 -->
<!-- 注释模板 从父类得到 -->
<!--
<bean name="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" >
<property name="dataSource" ref="dataSource" ></property>
</bean>
-->
<!-- 将UserService放入spring容器 -->
<bean name="userService" class="com.cuzz.service.UserServiceImpl" >
<property name="dataSource" ref="dataSource" ></property>
</bean>
Spring实务管理
什么是事务
事务逻辑上的一组操作，组成这组操作的各个逻辑单元，要么一起成功,要么一起失败
事务特性
- 原子性 ：强调事务的不可分割
- 一致性 ：事务的执行的前后数据的完整性保持一致
- 隔离性：一个事务执行的过程中，不应该受到其他事务的干扰
- 持久性：事务一旦结束，数据就持久到数据库
如果不考虑隔离性引发安全性问题
- 脏读 ：一个事务读到了另一个事务的未提交的数据
- 不可重复读：一个事务读到了另一个事务已经提交的
update的数据导致多次查询结果不一致. - 虚幻读 ：一个事务读到了另一个事务已经提交的
insert的数据导致多次查询结果不一致
解决读问题：设置事务隔离级别
- 未提交读：脏读，不可重复读，虚读都有可能发生
- 已提交读：避免脏读。但是不可重复读和虚读有可能发生
- 可重复读：避免脏读和不可重复读.但是虚读有可能发生.
- 串行化的：避免以上所有读问题
Mysql 默认：可重复读
Oracle 默认：读已提交
