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

gistfile1.txt

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