1.5-Spring 数据库操作

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>
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值