JDBC中的事务处理学习笔记

JDBC中的事务处理学习笔记

              事务处理保证所有的事务都作为一个工作单元来执行,即使出现了硬件故障或系统失灵,都不能改变这种执行方式.当在一个事务中执行多个操作时,要么所有的操作都被提交(commit),要么整个事务回滚(rollback)到最初状态.

            当一个连接被创建时,默认情况下是设置为自动提交事务,这意味着每次执行一个SQL语句时,如果执行成功,就会向数据库自动提交,也就不能回滚了.为了将所个SQL语句作为一个事务执行,可以调用Connection对象的setAutoCommit()方法,传入false来取消自动提交事务,然后在所有的SQL语句成功执行后,调用Connection对象的commit()方法提交事务,或者在执行出错时,调用Connection对象的rollback()方法来回滚事务

 

           下面这个例子中,用户选定购买的图书和数量后,提交订单,程序把更新bookinfo表中的图书数量的操作和更新account表中的余额的操作作为一个事务处理,只有在两个操作都完成的情况下,才提交事务,否则,回滚事务.

 

1, 编写buy.html文件

 

 

   

    购买《MySQL数据库编程》

   

       用户名  :

       购买数量:

       重填">

       购买">

   

 

 

 

 

 

2, 编写TradeServlet:

    TradeServlet负责完成用户购买图书的交易操作

   

 

 

package cn.apache.pl;

 

import java.io.*;

import java.sql.*;

import javax.servlet.*;

import javax.servlet.http.*;

 

public class TradeServlet extends HttpServlet

{

    private String url;

    private String user;

    private String password;

   

    public void init() throws ServletException

    {

       ServletContext sc = getServletContext();

      

       //从web.xml文件中的中获取以下参数值

       String driverClass  = sc.getInitParameter("driverClass");

       url = sc.getInitParameter("url");

       user = sc.getInitParameter("user");

       password = sc.getInitParameter("password");

      

       try

       {

           Class.forName(driverClass);

       }catch(ClassNotFoundException ce)

       {

           throw new UnavailableException("数据库驱动加载失败");

       }

    }

   

    public void doGet(HttpServletRequest req, HttpServletResponse resp)

       throws IOException, ServletException

    {

       Connection conn = null;

       Statement stmt = null;

       PreparedStatement pstmt = null;

       ResultSet rs = null;

      

       //设置响应页面的MIME类型及字符编码

       resp.setContentType("text/html; charset=gb2312");

       PrintWriter out = resp.getWriter();

      

       //设置请求页面的字符编码

       req.setCharacterEncoding("GB2312");

      

       //从提交的表单中获取用户名和购买数量

       String userid = req.getParameter("userid");

       String quantity = req.getParameter("quantity");

      

       if(null == userid || userid.equals("") || null == quantity || quantity.equals(""))

       {

           out.println("Error: 请输入用户名和购买数量!");

           out.close();

       }

       else

       {

           try

           {

              conn = DriverManager.getConnection(url, user, password);

             

              //调用Connection对象的setAutoCommit()方法,传入false参数取消自动提交事务

              conn.setAutoCommit(false);

             

              stmt = conn.createStatement();

             

              rs = stmt.executeQuery("select price,amount from bookinfo where id=3");

              /*

                  mysql> select price,amount from bookinfo where id=3;

                  +-------+--------+

                  | price | amount |

                  +-------+--------+

                  | 78.00 |     10 |

                  +-------+--------+

              */

             

              rs.next();

             

              //获取图书的价格和库存量

              float price = rs.getFloat(1);   //相当于rs.getFloat("price");

              int amount = rs.getInt(2);      //相当于rs.getInt("amount");

             

              int num = Integer.parseInt(quantity);

             

              //如果库存量大于用户购买的数量,则更新库存量

              if(amount >= num)

              {

                  pstmt = conn.prepareStatement("update bookinfo set amount=? where id=3");

                  pstmt.setInt(1,amount-num);

                  pstmt.executeUpdate();

              }

              else

              {

                  out.println("您所购买的图书库存量不足");

                  out.close();

                  return;

              }

             

              //查询该用户帐户余额

              pstmt = conn.prepareStatement("select balance from account where userid=?");

              /*

               *先将上面这句错写成"pstmt = ...where user=?");",执行servlet时Tomcat控制台报

               *java.sql.SQLException: Unknown column 'user' in 'where clause'

               *检查后,发现"where"子句中的"user"应该为"userid",

               *即buy.html文件中表单中的用户名为userid,而不是user

               */

               

              pstmt.setString(1,userid);

              rs = pstmt.executeQuery();

             

              rs.next();

              float balance = rs.getFloat(1);

             

              //总金额=图书价格*购买数量

              float totalPrice = price*num;

             

              //如果帐户余额大于总金额,则更新帐户余额(帐户余额-总金额)

              if(balance >= totalPrice)

              {

                  pstmt = conn.prepareStatement("Update account set balance=? where userid=?");

                  pstmt.setFloat(1,balance-totalPrice);

                  pstmt.setString(2,userid);

                  pstmt.executeUpdate();

              }

              //否则执行回滚,返回到购买前状态(图书数量未发生改变)

              else

              {

                  conn.rollback();

                  out.println("您的余额不足");

                  out.close();

                  return;

              }

              

              //提交事务,执行购买

              conn.commit();

              out.println("交易成功");

              out.close();

           }catch(SQLException se)

           {

              if(conn != null)

              {

                  try

                  {  

                     //如果购买过程中出现异常,则调用Connection对象的rollback()方法回滚所有改变

                     conn.rollback();

                  }catch(SQLException sex)

                  {

                     sex.printStackTrace();

                  }

              }

              se.printStackTrace();

           }finally

           {

              if(rs != null)                  //注意各对象的关闭顺序

              {

                  try

                  {

                     rs.close();

                  }catch(SQLException se)

                  {

                     se.printStackTrace();

                  }

                  rs = null;

              }

             

              if(stmt != null)

              {

                  try

                  {

                     stmt.close();

                  }catch(SQLException se)

                  {

                     se.printStackTrace();

                  }

                  stmt = null;

              }

             

              if(pstmt != null)

              {

                  try

                  {

                     pstmt.close();

                  }catch(SQLException se)

                  {

                     se.printStackTrace();

                  }

                  pstmt = null;

              }

             

              if(conn != null)

              {

                  try

                  {

                     conn.close();

                  }catch(SQLException se)

                  {

                     se.printStackTrace();

                  }

                  conn = null;

              }

           }  

       }

    }

   

    public void doPost(HttpServletRequest req, HttpServletResponse resp)

       throws IOException, ServletException

    {

       doGet(req, resp);

    }

}

 

 

3,部署Servlet:

 

 

 

    driverClass

    com.mysql.jdbc.Driver

 

    url

    jdbc:mysql://localhost:3306/bookstore

 

    user

    pl

 

    password

    123

 

 

    TradeServlet

    cn.apache.pl.TradeServlet

 

    TradeServlet

    /trade

 

 

 

4,运行GetDBInfoServlet

1,启动Tomact服务器,在浏览器中打开:

 

2,购买之前,我们先查看用户”彭磊”的帐户余额和《MySQL数据库编程》的库存量:

 

mysql> select * from account;

+--------+---------+

| userid | balance |

+--------+---------+

| 彭磊   |  588.00 |

| 徐璐   | 1000.50 |

+--------+---------+

 

mysql> select * from bookinfo;

+----+------------------+--------+----------------+--------------+-------+--------+------

| id | title            | author | bookconcern    | publish_date | price | amount | remark |

+----+------------------+--------+----------------+--------------+-------+--------+------

|  1 | JAVA从入门到精通 | 孙卫琴 | 电子工业出版社 | 2004-06-01   | 34.00 |     35 | NULL   |

|  2 | JSP应用开发详解  | 刘小华 | 清华大学出版社 | 2005-10-03   | 56.00 |     20 | NULL   |

|  3 | MySQL数据库编程  | 孙鑫   | 人民邮电出版社 | 2006-06-29   | 78.00 |     56 | NULL   |

|  4 | JAVA 国际认证    | SCJP   | 清华大学出版社 | 2005-02-03   | 46.00 |     55 | NULL   |

|  5 | JAVA编程思想     | Sun    | 电子工业出版社 | 2004-11-16   | 56.00 |     55 | NULL   |

+----+------------------+--------+----------------+--------------+-------+--------+------

 

3,输入用户名”彭磊”,购买数量”4”后,点”购买”,

 

 

页面显示: 交易成功

 

4,此时,再次查看用户”彭磊”的帐户余额和《MySQL数据库编程》的库存量:

 

mysql> select * from account;

+--------+---------+

| userid | balance |

+--------+---------+

| 彭磊   |  276.00 |

| 徐璐   | 1000.50 |

+--------+---------+

 

mysql> select * from bookinfo;

+----+------------------+--------+----------------+--------------+-------+--------+------

| id | title            | author | bookconcern    | publish_date | price | amount | remark |

+----+------------------+--------+----------------+--------------+-------+--------+------

|  1 | JAVA从入门到精通 | 孙卫琴 | 电子工业出版社 | 2004-06-01   | 34.00 |     35 | NULL   |

|  2 | JSP应用开发详解  | 刘小华 | 清华大学出版社 | 2005-10-03   | 56.00 |     20 | NULL   |

|  3 | MySQL数据库编程  | 孙鑫   | 人民邮电出版社 | 2006-06-29   | 78.00 |     52 | NULL   |

|  4 | JAVA 国际认证    | SCJP   | 清华大学出版社 | 2005-02-03   | 46.00 |     55 | NULL   |

|  5 | JAVA编程思想     | Sun    | 电子工业出版社 | 2004-11-16   | 56.00 |     55 | NULL   |

+----+------------------+--------+----------------+--------------+-------+--------+------

 

余额为276.00(购买前的余额588-图书价格78*购买数量4=276.00),

库存量为52(购买前库存量56-购买数量4=52),说明图书购买后,更新数据库成功!

 

 

5,异常测试:

1, 输入用户名”彭磊”,购买数量”412”后,点”购买”,

 

    页面输出: 您所购买的图书库存量不足

 

2,    仅仅输入用户名”彭磊”, 点”购买”,

 

       页面输出: Error: 请输入用户名和购买数量!

 

3,    输入用户名”彭磊”, 购买数量”52”后,点”购买”,

 

       页面输出:您的余额不足