mysql编程事务管理_Spring 编程式事务管理

编程式事务管理办法允许您管理与编程的源代码的帮助下事务。这就给了极大的灵活性，但它难以维护。

在我们开始之前，它至少有两个数据库表上，我们可以在事务的帮助下执行各种CRUD操作。让我们以Student表，它可以在MySQL数据库中测试用下面的DDL创建：

CREATE TABLEStudent(ID INT NOT NULL AUTO_INCREMENT,NAME VARCHAR(20)NOT NULL,AGE INT NOT NULL,PRIMARY KEY(ID));

第二个表是Marks，我们将保持标记为基于多年的学生。这里SID是表表的外键。

CREATE TABLEMarks(SID INT NOT NULL,MARKS INT NOT NULL,YEAR INT NOT NULL);

让我们使用PlatformTransactionManager直接实现编程的方法来实现事务。要开始一个新的事务，需要有TransactionDefinition 适当的事务属性的一个实例。在这个例子中，我们将简单地创建DefaultTransactionDefinition的实例使用默认的事务属性。

一旦TransactionDefinition被创建，你可以通过调用getTransaction()方法，它返回的TransactionStatus对象的一个实例开始事务。TransactionStatus对象有助于跟踪事务的当前状态，最后，如果一切顺利，可以使用提交(的PlatformTransactionManager的)方法来提交事务，否则可以使用rollback() 回滚完成操作。

现在我们编写Spring JDBC应用程序，将实现Student和Marks表简单的操作。使用Eclipse IDE，然后按照下面的步骤来创建一个Spring应用程序：

步骤

描述

1

Create a project with a name SpringExample and create a package com.yiibai under the src folder in the created project.

2

Add required Spring libraries using Add External JARs option as explained in the Spring Hello World Example chapter.

3

Add Spring JDBC specific latest libraries mysql-connector-java.jar,org.springframework.jdbc.jar and org.springframework.transaction.jar in the project. You can download required libraries if you do not have them already.

4

Create DAO interface StudentDAO and list down all the required methods. Though it is not required and you can directly write StudentJDBCTemplate class, but as a good practice, let's do it.

5

Create other required Java classes StudentMarks, StudentMarksMapper,StudentJDBCTemplate and MainApp under the com.yiibai package. You can create rest of the POJO classes if required.

6

Make sure you already created Student and Marks tables in TEST database. Also make sure your MySQL server is working fine and you have read/write access on the database using the give username and password.

7

Create Beans configuration file Beans.xml under the src folder.

8

The final step is to create the content of all the Java files and Bean Configuration file and run the application as explained below.

以下是数据访问对象接口文件StudentDAO.java的内容：

packagecom.yiibai;importjava.util.List;importjavax.sql.DataSource;publicinterfaceStudentDAO{/**

* This is the method to be used to initialize

* database resources ie. connection.

*/publicvoidsetDataSource(DataSourceds);/**

* This is the method to be used to create

* a record in the Student and Marks tables.

*/publicvoidcreate(Stringname,Integerage,Integermarks,Integeryear);/**

* This is the method to be used to list down

* all the records from the Student and Marks tables.

*/publicListlistStudents();}

以下是StudentMarks.java文件的内容：

packagecom.yiibai;publicclassStudentMarks{privateIntegerage;privateStringname;privateIntegerid;privateIntegermarks;privateIntegeryear;privateIntegersid;publicvoidsetAge(Integerage){this.age=age;}publicIntegergetAge(){returnage;}publicvoidsetName(Stringname){this.name=name;}publicStringgetName(){returnname;}publicvoidsetId(Integerid){this.id=id;}publicIntegergetId(){returnid;}publicvoidsetMarks(Integermarks){this.marks=marks;}publicIntegergetMarks(){returnmarks;}publicvoidsetYear(Integeryear){this.year=year;}publicIntegergetYear(){returnyear;}publicvoidsetSid(Integersid){this.sid=sid;}publicIntegergetSid(){returnsid;}}

以下是StudentMarksMapper.java文件的内容：

packagecom.yiibai;importjava.sql.ResultSet;importjava.sql.SQLException;importorg.springframework.jdbc.core.RowMapper;publicclassStudentMarksMapperimplementsRowMapper{publicStudentMarksmapRow(ResultSetrs,introwNum)throwsSQLException{StudentMarksstudentMarks=newStudentMarks();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"));returnstudentMarks;}}

下面是实现类文件StudentJDBCTemplate.java的定义DAO接口StudentDAO：

packagecom.yiibai;importjava.util.List;importjavax.sql.DataSource;importorg.springframework.dao.DataAccessException;importorg.springframework.jdbc.core.JdbcTemplate;importorg.springframework.transaction.PlatformTransactionManager;importorg.springframework.transaction.TransactionDefinition;importorg.springframework.transaction.TransactionStatus;importorg.springframework.transaction.support.DefaultTransactionDefinition;publicclassStudentJDBCTemplateimplementsStudentDAO{privateDataSourcedataSource;privateJdbcTemplatejdbcTemplateObject;privatePlatformTransactionManagertransactionManager;publicvoidsetDataSource(DataSourcedataSource){this.dataSource=dataSource;this.jdbcTemplateObject=newJdbcTemplate(dataSource);}publicvoidsetTransactionManager(PlatformTransactionManagertransactionManager){this.transactionManager=transactionManager;}publicvoidcreate(Stringname,Integerage,Integermarks,Integeryear){TransactionDefinitiondef=newDefaultTransactionDefinition();TransactionStatusstatus=transactionManager.getTransaction(def);try{StringSQL1="insert into Student (name, age) values (?, ?)";jdbcTemplateObject.update(SQL1,name,age);// Get the latest student id to be used in Marks tableStringSQL2="select max(id) from Student";intsid=jdbcTemplateObject.queryForInt(SQL2);StringSQL3="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(DataAccessExceptione){System.out.println("Error in creating record, rolling back");transactionManager.rollback(status);throwe;}return;}publicListlistStudents(){StringSQL="select * from Student, Marks where Student.id=Marks.sid";ListstudentMarks=jdbcTemplateObject.query(SQL,newStudentMarksMapper());returnstudentMarks;}}

现在让我们移动主应用程序文件MainApp.java，这是如下：

packagecom.yiibai;importjava.util.List;importorg.springframework.context.ApplicationContext;importorg.springframework.context.support.ClassPathXmlApplicationContext;importcom.yiibai.StudentJDBCTemplate;publicclassMainApp{publicstaticvoidmain(String[]args){ApplicationContextcontext=newClassPathXmlApplicationContext("Beans.xml");StudentJDBCTemplatestudentJDBCTemplate=(StudentJDBCTemplate)context.getBean("studentJDBCTemplate");System.out.println("------Records creation--------");studentJDBCTemplate.create("Zara",11,99,2010);studentJDBCTemplate.create("Nuha",20,97,2010);studentJDBCTemplate.create("Ayan",25,100,2011);System.out.println("------Listing all the records--------");ListstudentMarks=studentJDBCTemplate.listStudents();for(StudentMarksrecord:studentMarks){System.out.print("ID : "+record.getId());System.out.print(", Name : "+record.getName());System.out.print(", Marks : "+record.getMarks());System.out.print(", Year : "+record.getYear());System.out.println(", Age : "+record.getAge());}}}

以下是配置文件beans.xml文件：

<?xml version="1.0"encoding="UTF-8"?>

http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

创建源代码和bean配置文件完成后，让我们运行应用程序。如果一切顺利，这将打印以下信息：

------Records creation--------

Created Name = Zara, Age = 11

Created Name = Nuha, Age = 20

Created Name = Ayan, Age = 25

------Listing all the records--------

ID : 1, Name : Zara, Marks : 99, Year : 2010, Age : 11

ID : 2, Name : Nuha, Marks : 97, Year : 2010, Age : 20

ID : 3, Name : Ayan, Marks : 100, Year : 2011, Age : 25

¥ 我要打赏

纠错/补充

收藏

加QQ群啦，易百教程官方技术学习群

注意：建议每个人选自己的技术方向加群，同一个QQ最多限加 3 个群。