mysql数据库的增删查改（带事务回滚）

package store.dao.impl;

import java.util.List;

import store.vo.ShopCar;

import java.sql.*;
import java.util.ArrayList;
import store.dao.ShopCarDAO;
public class ShopCarDAOImpl implements  ShopCarDAO{
	private PreparedStatement pstmt=null;//数据库操作对象
	private Connection conn=null;//数据库连接对象
	public ShopCarDAOImpl(Connection conn){
		this.conn=conn;
	}
	//添加
	 public boolean addShopCar(ShopCar shopCar){
		 boolean flag=false;
		 String sql="insert into shopCar(userName,goodsId,goodsName,number,price,variety) values(?,?,?,?,?,?)";
         try{   
                //修改默认的自动提交数据(事务)
		        this.conn.setAutoCommit(false);
                this.pstmt=this.conn.prepareStatement(sql);
                this.pstmt.setString(1, shopCar.getUserName());
                this.pstmt.setInt(2, shopCar.getGoodsId());
                this.pstmt.setString(3, shopCar.getGoodsName());
                this.pstmt.setInt(4,shopCar.getNumber());
                this.pstmt.setInt(5, shopCar.getPrice());
                this.pstmt.setString(6, shopCar.getVariety());
                if(this.pstmt.executeUpdate()>0){
                    flag=true;
                    this.conn.commit();
                    //恢复默认的自动提交数据(事务)
		            this.conn.setAutoCommit(true);
                }
         }catch(Exception e){
             e.printStackTrace();
             this.conn.rollback();//事务回滚
             return false;
         }
		 return flag;
	 }
	//删除多条或者单条数据
	public boolean deleteShopCar(String userName,int[]id) throws Exception{
		boolean flag=false;
		String sql="delete from shopCar where userName=? and id=?";
		this.pstmt=this.conn.prepareStatement(sql);
		//修改默认的自动提交数据，执行多条数据
		this.conn.setAutoCommit(false);
		for(int i:id){
			this.pstmt.setString(1,userName);
			this.pstmt.setInt(2, i);
			if(this.pstmt.executeUpdate()>0){
				flag=true;
			}
			else{
				this.conn.rollback();
				return false;
			}
		}
		this.conn.commit();
		//恢复默认的自动提交数据
		this.conn.setAutoCommit(true);
		return flag;
	}
	//根据用户名字获取数量
	public int selectNumberByUserName(String userName) throws Exception{
		int number=0;
		String sql="select count(id) from shopCar where userName=?";
		this.pstmt=this.conn.prepareStatement(sql);
		this.pstmt.setString(1, userName);
		ResultSet rs=this.pstmt.executeQuery();
		if(rs.next()){
		   number=rs.getInt(1);
		}
		return number;
	}
	//根据用户名字获取数据
	public List
 
   selectShopCarByUserName(int start,int end,String userName) throws Exception{
		List
  
    list=new ArrayList
   
    (); String sql="select id,userName,goodsId,goodsName,number,price,variety from shopCar where userName=? order by id desc limit ?,?"; this.pstmt=this.conn.prepareStatement(sql); this.pstmt.setString(1, userName); this.pstmt.setInt(2, start); this.pstmt.setInt(3, end); ResultSet rs=this.pstmt.executeQuery(); ShopCar shopCar=null; while(rs.next()){ shopCar=new ShopCar(); shopCar.setId(rs.getInt(1)); shopCar.setUserName(rs.getString(2)); shopCar.setGoodsId(rs.getInt(3)); shopCar.setGoodsName(rs.getString(4)); shopCar.setNumber(rs.getInt(5)); shopCar.setPrice(rs.getInt(6)); shopCar.setVariety(rs.getString(7)); list.add(shopCar); } return list; } //修改number(数量) public boolean updateShopCarNumber(String userName,String goodsName,int number) throws Exception{ boolean flag=false; String sql="update shopCar set number=? where userName=? and goodsName=?"; this.pstmt=this.conn.prepareStatement(sql); this.pstmt.setInt(1, number); this.pstmt.setString(2, userName); this.pstmt.setString(3, goodsName); if(this.pstmt.executeUpdate()>0){ flag=true; } return flag; } }