如何实现MySQL数据库的增删查改操作并确保事务回滚的完整性和安全性？

本文共计654个文字，预计阅读时间需要3分钟。

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

public ShopCarDAOImpl() { // 初始化PreparedStatement }

@Override public List findAll() { List shopCars=new ArrayList(); try { // 创建SQL查询语句 String sql=SELECT * FROM shop_car; pstmt=DriverManager.getConnection(jdbc:mysql://localhost:3306/store, user, password).prepareStatement(sql); ResultSet rs=pstmt.executeQuery();

while (rs.next()) { ShopCar shopCar=new ShopCar(); shopCar.setId(rs.getInt(id)); shopCar.setUserId(rs.getInt(user_id)); shopCar.setProductId(rs.getInt(product_id)); shopCar.setQuantity(rs.getInt(quantity)); shopCars.add(shopCar); } rs.close(); } catch (SQLException e) { e.printStackTrace(); } return shopCars; }}

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