事物控制随笔

1 建立链接

2设置为手动提交模式

3各种操作代码

4如果失败则回滚 rollbake()

5成功则提交 commit()

需要注意的是:要保证多个操作使用的是同一个事物，否则会出异常

例如：更新商品表中id为3和4的数量：

首先我们先创建商品表：

CREATE TABLE t_user(
    id NUMBER(11),
    username VARCHAR2(25),
    password VARCHAR2(25),
    sex NUMBER(1) DEFAULT 0,
    id_number VARCHAR2(18),
    tel VARCHAR2(11),
    addr VARCHAR2(100),
    CONSTRAINT t_user_id_pk PRIMARY KEY(id),
    CONSTRAINT t_user_username_uk UNIQUE(username),
    CONSTRAINT t_user_id_number_uk UNIQUE(id_number)
);

CREATE TABLE t_product_category(
    id NUMBER(11),
    name VARCHAR2(30),
    parent_id NUMBER(9),
    CONSTRAINT t_product_category_id_pk PRIMARY KEY(id),
    CONSTRAINT t_prod_cate_parent_id_fk FOREIGN KEY(parent_id) REFERENCES t_product_category(id)
);

CREATE TABLE t_product(
    id NUMBER(11),
    name VARCHAR2(30),
    price NUMBER(10,2),
    stock NUMBER(9),
    cate_id NUMBER(11),
    cate_child_id NUMBER(11),
    CONSTRAINT t_product_id_pk PRIMARY KEY(id),
    CONSTRAINT t_product_cate_id_fk FOREIGN KEY(cate_id) 
        REFERENCES t_product_category(id),
    CONSTRAINT t_product_cate_child_id_fk FOREIGN KEY(cate_child_id)
        REFERENCES t_product_category(id)
);

CREATE TABLE t_order(
    id NUMBER(11),
    user_id NUMBER(11),
    total NUMBER(12,2),
    order_date DATE,
    name VARCHAR2(25),
    tel VARCHAR2(11),
    addr VARCHAR2(100),
    status NUMBER(3) DEFAULT 0,
    CONSTRAINT t_order_id_pk PRIMARY KEY(id),
    CONSTRAINT t_order_user_id_fk FOREIGN KEY(user_id) 
        REFERENCES t_user(id)
);

CREATE TABLE t_order_detail(
    id NUMBER(11),
    order_id NUMBER(11),
    product_id NUMBER(11),
    price NUMBER(11,2),
    quantity NUMBER(9),
    cost NUMBER(11,2),
    CONSTRAINT t_order_detail_id_pk PRIMARY KEY(id),
    CONSTRAINT t_order_detail_order_id_fk FOREIGN KEY(order_id)
        REFERENCES t_order(id),
    CONSTRAINT t_order_detail_product_id_fk FOREIGN KEY(product_id)
        REFERENCES t_product(id)
);



--添加数据
INSERT INTO t_user (id,username,password,sex,id_number,tel,addr) 
VALUES(1,'tony','111',0,'112233445566778899','13012341234','上海市黄浦区宁波路200号');
INSERT INTO t_user (id,username,password,sex,id_number,tel,addr) 
VALUES(2,'ben','111',0,'235623245323464234','15025236424','上海市浦东新区上南路100号');
INSERT INTO t_user (id,username,password,sex,id_number,tel,addr) 
VALUES(3,'lucy','111',1,'74302565475389935X','18095672743','上海市黄浦区天津路180号');
INSERT INTO t_user (id,username,password,sex,id_number,tel,addr) 
VALUES(4,'xiaoming','111',0,'325602345664234643','13329521034','上海市徐汇区桂林路150号');
INSERT INTO t_user (id,username,password,sex,id_number) 
VALUES(5,'xiaohong','111',1,'323560234566423476');

INSERT INTO t_product_category (id,name,parent_id) 
VALUES(1,'家居用品',null);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(2,'办公用品',null);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(3,'手机数码',null);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(4,'运动户外',null);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(5,'酒水饮料',null);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(6,'食品',null);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(7,'毛巾',1);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(8,'被子',1);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(9,'U盘',2);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(10,'笔',2);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(11,'手机',3);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(12,'充电器',3);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(13,'运动鞋',4);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(14,'羽毛球拍',4);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(15,'牛奶',5);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(16,'饮料',5);
INSERT INTO t_product_category (id,name,parent_id) 
VALUES(17,'方便食品',6);

INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(1,'洁丽雅毛巾小',7,100,1,7);
INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(2,'金号毛巾条纹',15,5,1,7);
INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(3,'金士顿U盘64G',70,8,2,9);
INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(4,'晨光黑色中性笔',2,100,2,10);
INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(5,'晨光红色中性笔',2,50,2,10);
INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(6,'晨光蓝色中性笔',2,50,2,10);
INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(7,'康师傅红烧牛肉面',4.5,120,6,17);
INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(8,'统一老坛酸菜牛肉面',4,110,6,17);
INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(9,'华为P10',3788,15,3,11);
INSERT INTO t_product (id,name,price,stock,cate_id,cate_child_id) 
VALUES(10,'华为MATE9',3888,100,3,11);


INSERT INTO t_order(id,user_id,total,order_date,name,tel,addr,status)
VALUES(1,1,20,to_date('2016-12-03','yyyy-mm-dd'),'小王','1234567','上海市浦东新区',2);
INSERT INTO t_order(id,user_id,total,order_date,name,tel,addr,status)
VALUES(2,2,3888,to_date('2017-01-02','yyyy-mm-dd'),'小李','1234567','上海市浦东新区',2);
INSERT INTO t_order(id,user_id,total,order_date,name,tel,addr,status)
VALUES(3,1,20,to_date('2017-02-03','yyyy-mm-dd'),'lucy','1234567','上海市虹口区',2);

INSERT INTO t_order_detail(id,order_id,product_id,price,quantity,cost)
VALUES(1,1,4,2,5,10);
INSERT INTO t_order_detail(id,order_id,product_id,price,quantity,cost)
VALUES(2,1,5,2,5,10);
INSERT INTO t_order_detail(id,order_id,product_id,price,quantity,cost)
VALUES(3,2,10,3888,1,3888);
INSERT INTO t_order_detail(id,order_id,product_id,price,quantity,cost)
VALUES(4,3,8,4,5,20);

COMMIT;

CREATE SEQUENCE t_user_id_seq START WITH 10 INCREMENT BY 1;
CREATE SEQUENCE t_product_category_id_seq START WITH 30 INCREMENT BY 1;
CREATE SEQUENCE t_product_id_seq START WITH 20 INCREMENT BY 1;
CREATE SEQUENCE t_order_id_seq START WITH 10 INCREMENT BY 1;
CREATE SEQUENCE t_order_detail_seq START WITH 10 INCREMENT BY 1;

封装一个连接数据库的通用方法：

package JDBC;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class ConnectionFactory {
    private static String driver;
    private static String url;
    private static String uname;
    private static String upass;
    //静态代码快
    static{
        //获取四个属性
        Properties prop = new Properties();
        //加载文件中的数据
        try {
            //load(流对象指向一个文件)把文件中的数据加载到prop对象中
            //Class对象的getResoureAsStream()传入同包下的资源，返回对应的流对象
            prop.load(ConnectionFactory.class.getResourceAsStream("jdbcinfo.properties"));
            
            //获取四个值
            driver = prop.getProperty("driver");
            url = prop.getProperty("url");
            uname = prop.getProperty("uname");
            upass = prop.getProperty("upass");
            
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    //封装一个注册驱动和链接数据库的通用方法
    public static Connection getConnection(){
        Connection conn = null;
        //注册驱动
        try {
            Class.forName(driver);
            //建立链接
            conn = DriverManager.getConnection(url,uname,upass);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }
    
    //封装一个关闭资源的方法
    public static void close(ResultSet rs,Statement stmt,Connection conn){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(stmt!=null){
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    public static void close(Statement stmt,Connection conn) {
        close(null,stmt,conn);
    }
    
    public static void close(Statement stmt) {
        close(null,stmt,null);
    }
    
    public static void close(Connection conn) {
        close(null,null,conn);
    }
    
}
创建一个file文件，用来保存

driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:XE
uname=easybuy
upass=easybuy

package Transaction;

import java.sql.Connection;
import java.sql.SQLException;
import JDBC.ConnectionFactory;

public class TransactionDemo {
    public static void main(String[] args) {
        TransactionDemo tran = new TransactionDemo();
        tran.updateStocks();
    }
    public void updateStocks(){
        //建立链接
        Connection conn = ConnectionFactory.getConnection();
        //设置为手动提交模式
        try {
            conn.setAutoCommit(false);            
            //执行需要 操作的代码
            ProductJDBC jdbc = new ProductJDBC();
            jdbc.updateProductStock(3, 5, conn);
            jdbc.updateProductStock(4, 10, conn);
            //如果都操作成功就提交
            conn.commit();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            try {
                //如果操作失败则回滚
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }finally {
                //最后关闭资源
                ConnectionFactory.close(conn);
            }
        }
    }

}

package Transaction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import JDBC.ConnectionFactory;

public class ProductJDBC {
    //更新商品的库存
    //注意:需要保证所有的操作都是在同一个事物下
    public int updateProductStock(long id,int addNum, Connection conn) throws SQLException{
        //执行sql添加语句
        String sql = "update t_product set stock=stock+? where id=?";
        //获取可执行的prepareStatement()
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, addNum);
        pstmt.setLong(2, id);
        int rows = pstmt.executeUpdate();
        //关闭资源:不能关闭所有的资源
        ConnectionFactory.close(pstmt);
        return rows;
    }

}