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; } }