JDBC 框架
SQL 的存储过程
编程式事务管理
声明式事务管理
maven仓库的配置
<properties>
<spring-version>4.3.17.RELEASE</spring-version>
</properties>
<!-- spring jdbc的依赖包 -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring-version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring-version}</version>
</dependency>
<!--mysql数据库驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
数据库前置操作
创建表:
CREATE TABLE Student(
ID INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
AGE INT NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE Marks(
SID INT NOT NULL,
MARKS INT NOT NULL,
YEAR INT NOT NULL
);
创建存储过程:
DELIMITER $$
DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
CREATE PROCEDURE `TEST`.`getRecord` (
IN in_id INTEGER,
OUT out_name VARCHAR(20),
OUT out_age INTEGER)
BEGIN
SELECT name, age
INTO out_name, out_age
FROM Student where id = in_id;
END $$
DELIMITER ;
JDBC的基本使用
创建student表
public class Student {
private Integer age;
private String name;
private Integer id;
public void setAge(Integer age) {
this.age = age;
}
public Integer getAge() {
return age;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getId() {
return id;
}
}
创建student的dao接口
import java.util.List;
import javax.sql.DataSource;
public interface StudentDAO {
public void setDataSource(DataSource ds);
public void create(String name, Integer age);
public Student getStudent(Integer id);
public List<Student> listStudents();
public void delete(Integer id);
public void update(Integer id, Integer age);
}
创建一个用于查询的对象
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class StudentMapper implements RowMapper<Student> {
public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
student.setAge(rs.getInt("age"));
return student;
}
}
创建dao接口的实现类
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class StudentJDBCTemplate implements StudentDAO {
private DataSource dataSource;
private JdbcTemplate jdbcTemplateObject;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
public void create(String name, Integer age) {
String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, name, age);
System.out.println("Created Record Name = " + name + " Age = " + age);
}
public Student getStudent(Integer id) {
String SQL = "select * from Student where id = ?";
Student student = jdbcTemplateObject.queryForObject(SQL,
new Object[]{id}, new StudentMapper());
return student;
}
public List<Student> listStudents() {
String SQL = "select * from Student";
List <Student> students = jdbcTemplateObject.query(SQL,
new StudentMapper());
return students;
}
public void delete(Integer id){
String SQL = "delete from Student where id = ?";
jdbcTemplateObject.update(SQL, id);
System.out.println("Deleted Record with ID = " + id );
}
public void update(Integer id, Integer age){
String SQL = "update Student set age = ? where id = ?";
jdbcTemplateObject.update(SQL, age, id);
System.out.println("Updated Record with ID = " + id );
}
}
bean文件的配置
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://IP地址:端口号/数据库名"/>
<property name="username" value="用户名"/>
<property name="password" value="登录密码"/>
</bean>
<bean id="studentJDBCTemplate"
class="com.xxx.xxx.StudentJDBCTemplate">
<property name="dataSource" ref="dataSource" />
</bean>
执行
public static void main(String[] args) {
ApplicationContext context =
new ClassPathXmlApplicationContext("Beans.xml");
StudentJDBCTemplate studentJDBCTemplate =
(StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
System.out.println("------创建数据--------" );
studentJDBCTemplate.create("Zara", 11);
studentJDBCTemplate.create("Nuha", 2);
studentJDBCTemplate.create("Ayan", 15);
System.out.println("------查询所有数据--------" );
List<Student> students = studentJDBCTemplate.listStudents();
for (Student record : students) {
System.out.print("ID : " + record.getId() );
System.out.print(", Name : " + record.getName() );
System.out.println(", Age : " + record.getAge());
}
System.out.println("----更新 ID = 2 -----" );
studentJDBCTemplate.update(2, 20);
System.out.println("----查询 ID = 2 -----" );
Student student = studentJDBCTemplate.getStudent(2);
System.out.print("ID : " + student.getId() );
System.out.print(", Name : " + student.getName() );
System.out.println(", Age : " + student.getAge());
}
SQL 的存储过程
SimpleJdbcCall 类可以被用于调用一个包含 IN 和 OUT 参数的存储过程
修改StudentJDBCTemplate.class
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
public class StudentJDBCTemplate implements StudentDAO {
private DataSource dataSource;
private SimpleJdbcCall jdbcCall;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcCall = new SimpleJdbcCall(dataSource).
withProcedureName("getRecord");
}
public void create(String name, Integer age) {
JdbcTemplate jdbcTemplateObject = new JdbcTemplate(dataSource);
String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, name, age);
System.out.println("Created Record Name = " + name + " Age = " + age);
}
public Student getStudent(Integer id) {
SqlParameterSource in = new MapSqlParameterSource().
addValue("in_id", id);
Map<String, Object> out = jdbcCall.execute(in);
Student student = new Student();
student.setId(id);
student.setName((String) out.get("out_name"));
student.setAge((Integer) out.get("out_age"));
return student;
}
public List<Student> listStudents() {
String SQL = "select * from Student";
List <Student> students = jdbcTemplateObject.query(SQL,
new StudentMapper());
return students;
}
}
事务管理
原子性:事务应该当作一个单独单元的操作,这意味着整个序列操作要么是成功,要么是失败的。
一致性:这表示数据库的引用完整性的一致性,表中唯一的主键等。
隔离性:可能同时处理很多有相同的数据集的事务,每个事务应该与其他事务隔离,以防止数据损坏。
持久性:一个事务一旦完成全部操作后,这个事务的结果必须是永久性的,不能因系统故障而从数据库中删除。
编程式 and 声明式
编程式事务管理 :这意味着你在编程的帮助下有管理事务。这给了你极大的灵活性,但却很难维护。
声明式事务管理 :这意味着你从业务代码中分离事务管理。你仅仅使用注释或 XML 配置来管理事务。
接口PlatformTransactionManager | 描述 |
---|---|
TransactionStatus getTransaction(TransactionDefinition definition) | 根据指定的传播行为,该方法返回当前活动事务或创建一个新的事务。 |
void commit(TransactionStatus status) | 该方法提交给定的事务和关于它的状态。 |
void rollback(TransactionStatus status) | 该方法执行一个给定事务的回滚 |
接口TransactionDefinition | 描述 |
---|---|
int getPropagationBehavior() | 该方法返回传播行为。Spring 提供了与 EJB CMT 类似的所有的事务传播选项。 |
int getIsolationLevel() | 该方法返回该事务独立于其他事务的工作的程度 |
String getName() | 该方法返回该事务的名称。 |
int getTimeout() | 该方法返回以秒为单位的时间间隔,事务必须在该时间间隔内完成 |
boolean isReadOnly() | 该方法返回该事务是否是只读的。 |
隔离级别的可能值 | 描述 |
---|---|
TransactionDefinition.ISOLATION_DEFAULT | 这是默认的隔离级别。 |
TransactionDefinition.ISOLATION_READ_COMMITTED | 表明能够阻止误读;可以发生不可重复读和虚读。 |
TransactionDefinition.ISOLATION_READ_UNCOMMITTED | 表明可以发生误读、不可重复读和虚读。 |
TransactionDefinition.ISOLATION_REPEATABLE_READ | 表明能够阻止误读和不可重复读;可以发生虚读。 |
TransactionDefinition.ISOLATION_SERIALIZABLE | 表明能够阻止误读、不可重复读和虚读。 |
传播类型的可能值 | 描述 |
---|---|
TransactionDefinition.PROPAGATION_MANDATORY | 支持当前事务;如果不存在当前事务,则抛出一个异常。 |
TransactionDefinition.PROPAGATION_NESTED | 如果存在当前事务,则在一个嵌套的事务中执行。 |
TransactionDefinition.PROPAGATION_NEVER | 不支持当前事务;如果存在当前事务,则抛出一个异常。 |
TransactionDefinition.PROPAGATION_NOT_SUPPORTED | 不支持当前事务;而总是执行非事务性。 |
TransactionDefinition.PROPAGATION_REQUIRED | 支持当前事务;如果不存在事务,则创建一个新的事务。 |
TransactionDefinition.PROPAGATION_REQUIRES_NEW | 创建一个新事务,如果存在一个事务,则把当前事务挂起。 |
TransactionDefinition.PROPAGATION_SUPPORTS | 支持当前事务;如果不存在,则执行非事务性。 |
TransactionDefinition.TIMEOUT_DEFAULT | 使用默认超时的底层事务系统,或者如果不支持超时则没有。 |
接口TransactionStatus | 描述 |
---|---|
boolean hasSavepoint() | 该方法返回该事务内部是否有一个保存点,也就是说,基于一个保存点已经创建了嵌套事务。 |
boolean isCompleted() | 该方法返回该事务是否完成,也就是说,它是否已经提交或回滚。 |
boolean isNewTransaction() | 在当前事务时新的情况下,该方法返回 true。 |
boolean isRollbackOnly() | 该方法返回该事务是否已标记为 rollback-only。 |
void setRollbackOnly() | 该方法设置该事务为 rollback-only 标记。 |
编程式事务管理
修改studentdao.intergace的create方法
public void create(String name, Integer age, Integer marks, Integer year);
增加StudentMarks.class
private Integer age;
private String name;
private Integer id;
private Integer marks;
private Integer year;
private Integer sid;
public void setAge(Integer age) {
this.age = age;
}
public Integer getAge() {
return age;
}
public void setName(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getId() {
return id;
}
public void setMarks(Integer marks) {
this.marks = marks;
}
public Integer getMarks() {
return marks;
}
public void setYear(Integer year) {
this.year = year;
}
public Integer getYear() {
return year;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public Integer getSid() {
return sid;
}
修改StudentMarksMapper.class
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class StudentMarksMapper implements RowMapper<StudentMarks> {
public StudentMarks mapRow(ResultSet rs, int rowNum) throws SQLException {
StudentMarks studentMarks = new StudentMarks();
studentMarks.setId(rs.getInt("id"));
studentMarks.setName(rs.getString("name"));
studentMarks.setAge(rs.getInt("age"));
studentMarks.setSid(rs.getInt("sid"));
studentMarks.setMarks(rs.getInt("marks"));
studentMarks.setYear(rs.getInt("year"));
return studentMarks;
}
}
修改StudentJDBCTemplate .class
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;
public class StudentJDBCTemplate implements StudentDAO {
private DataSource dataSource;
private JdbcTemplate jdbcTemplateObject;
private PlatformTransactionManager transactionManager;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
public void setTransactionManager(
PlatformTransactionManager transactionManager) {
this.transactionManager = transactionManager;
}
public void create(String name, Integer age, Integer marks, Integer year){
TransactionDefinition def = new DefaultTransactionDefinition();
TransactionStatus status = transactionManager.getTransaction(def);
try {
String SQL1 = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL1, name, age);
// 手动创建一天student数据
String SQL2 = "select max(id) from Student";
int sid = jdbcTemplateObject.queryForInt( SQL2 );
String SQL3 = "insert into Marks(sid, marks, year) " +
"values (?, ?, ?)";
jdbcTemplateObject.update( SQL3, sid, marks, year);
System.out.println("Created Name = " + name + ", Age = " + age);
transactionManager.commit(status);
} catch (DataAccessException e) {
System.out.println("Error in creating record, rolling back");
transactionManager.rollback(status);
throw e;
}
return;
}
public List<StudentMarks> listStudents() {
String SQL = "select * from Student, Marks where Student.id=Marks.sid";
List <StudentMarks> studentMarks = jdbcTemplateObject.query(SQL,
new StudentMarksMapper());
return studentMarks;
}
}
修改bean文件配置
<!-- Initialization for data source -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://IP地址:端口号/数据库名"/>
<property name="username" value="用户名"/>
<property name="password" value="登录密码"/>
</bean>
<bean id="transactionManager1"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="studentJDBCTemplate"
class="com.xxx.xxx.StudentJDBCTemplate">
<property name="dataSource" ref="dataSource" />
<property name="transactionManager" ref="transactionManager1" />
</bean>
声明式事务管理
修改StudentJDBCTemplate .class
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
public class StudentJDBCTemplate implements StudentDAO{
private JdbcTemplate jdbcTemplateObject;
public void setDataSource(DataSource dataSource) {
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
public void create(String name, Integer age, Integer marks, Integer year){
try {
String SQL1 = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL1, name, age);
// 这里先添加一条student数据
String SQL2 = "select max(id) from Student";
int sid = jdbcTemplateObject.queryForInt( SQL2 );
String SQL3 = "insert into Marks(sid, marks, year) " +
"values (?, ?, ?)";
jdbcTemplateObject.update( SQL3, sid, marks, year);
System.out.println("Created Name = " + name + ", Age = " + age);
// 这里手动抛出异常
throw new RuntimeException("simulate Error condition") ;
} catch (DataAccessException e) {
System.out.println("Error in creating record, rolling back");
throw e;
}
}
public List<StudentMarks> listStudents() {
String SQL = "select * from Student, Marks where Student.id=Marks.sid";
List <StudentMarks> studentMarks=jdbcTemplateObject.query(SQL,
new StudentMarksMapper());
return studentMarks;
}
}
修改bean配置文件:
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://IP地址:端口号/数据库名"/>
<property name="username" value="用户名"/>
<property name="password" value="登录密码"/>
</bean>
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="create"/>
</tx:attributes>
</tx:advice>
<aop:config>
<aop:pointcut id="createOperation"
expression="execution(* com.xxx.xxx.StudentJDBCTemplate.create(..))"/>
<aop:advisor advice-ref="txAdvice" pointcut-ref="createOperation"/>
</aop:config>
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<bean id="studentJDBCTemplate"
class="com.xxx.xxx.StudentJDBCTemplate">
<property name="dataSource" ref="dataSource" />
</bean>