java mysql购物车_java购物车提交定单的数据库操作（mysql和oracle）

java购物车提交定单的数据库操作(mysql和oracle),SET FOREIGN_KEY_CHECKS=0;

– —————————-

– Table structure for orders

– —————————-

CREATE TABLE `orders` (

`orderid` int(10) NOT NULL AUTO_INCREMENT COMMENT ‘bookid自增长’,

`userid` int(5) NOT NULL,

`orderdate` datetime NOT NULL,

`totalprice` float(10,0) NOT NULL,

PRIMARY KEY (`orderid`),

KEY `userid` (`userid`),

CONSTRAINT `userid` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS=0;

– —————————-

– Table structure for orderitem

– —————————-

CREATE TABLE `orderitem` (

`id` int(10) NOT NULL AUTO_INCREMENT COMMENT ‘id自增长’,

`orderid` int(10) NOT NULL,

`bookid` int(5) NOT NULL,

`booknum` int(10) NOT NULL DEFAULT ’0′,

PRIMARY KEY (`id`),

KEY `orderid` (`orderid`),

KEY `bookid` (`bookid`),

CONSTRAINT `bookid` FOREIGN KEY (`bookid`) REFERENCES `books` (`bookid`),

CONSTRAINT `orderitem_ibfk_1` FOREIGN KEY (`orderid`) REFERENCES `orders` (`orderid`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

1.mysql提交订单到orders表和orderitem表

package com.ru.domain;

import java.io.Serializable;

import java.sql.*;

import java.util.ArrayList;

//这是对mysql数据库的操作

public class OrderService {

private Connection ct=null;

private PreparedStatement ps=null;

private ResultSet rs=null;

public void submitorder(MyCart mycart,Users user){

try {

//1.将数据插入order表

String sql=”insert into orders(userid,orderdate,totalprice) values(‘”+user.getUserid()+”‘,’2012-07-26′,’”+mycart.totalprice()+”‘)”;

// 加载驱动

Class.forName(“com.mysql.jdbc.Driver”);

// 连接数据库

ct = DriverManager.getConnection(“jdbc:mysql://localhost:3306/ru”,”root”, “123456″);

//**********************关闭mysql的自动提交****************

ct.setAutoCommit(false);

//************************设置事物的隔断级别***************

ct.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);

// 创建表达式

ps= ct.prepareStatement(sql);

ps.executeUpdate();

//2.获取当前的orderid

String bookid = null;

rs = ps.getGeneratedKeys();

if(rs.next()) {

bookid = rs.getString(1);

}

//3.将数据插入orderitem中

ArrayListal=mycart.showcart();

for(Books book:al){

sql=”insert into orderitem(orderid,bookid,booknum) values(‘”+bookid+”‘,’”+book.getBookid()+”‘,’”+book.getBookcount()+”‘)”;

ps=ct.prepareStatement(sql);

ps.executeUpdate(sql);

}

//4.提交操作

ct.commit();

} catch (Exception e) {

//加入操作失败的话回滚

try {

ct.rollback();

} catch (SQLException e1) {

e1.printStackTrace();

}

e.printStackTrace();

} finally{

if (rs != null) {

try {

rs.close();

} catch (Exception e) {

// TODO: handle exception

}

} else if (ps != null) {

try {

ps.close();

} catch (Exception e) {

// TODO: handle exception

}

} else if (ct != null) {

try {

ct.close();

} catch (Exception e) {

// TODO: handle exception

}

}

}

}

}

2.oracle提交定单

其中使用到的两个序列

http://blog.sina.com.cn/s/blog_976e4957010131nk.html