为什么删除分类后连同商品一起删了啊
来源：5-1 删除分类修改关联商品的外键
慕仔0431810
2020-01-10 09:30:12
package com.zt.service.impl;
import java.util.List;
import com.zt.dao.CategoryDao;
import com.zt.dao.ProductDao;
import com.zt.dao.impl.CategoryDaoImpl;
import com.zt.dao.impl.ProductDaoImpl;
import com.zt.domain.Category;
import com.zt.domain.Product;
import com.zt.service.CategoryService;
public class CategoryServiceImpl implements CategoryService {
@Override
public List<Category> findAll() {
//调用categoryDao 方法
CategoryDao categoryDao=new CategoryDaoImpl();
return categoryDao.findAll();
}
@Override
public void save(Category category) {
//调用categoryDao 方法
CategoryDao categoryDao=new CategoryDaoImpl();
categoryDao.save(category);
}
@Override
public Category findOne(Integer cid) {
CategoryDao categoryDao=new CategoryDaoImpl();
return categoryDao.findOne(cid);
}
@Override
public void update(Category category) {
CategoryDao categoryDao=new CategoryDaoImpl();
categoryDao.update(category);
}
@Override
public void delete(Integer cid) {
//根据cid将某个product信息查询出来，返回给list
//循环遍历list 将cid设置为null
ProductDao productDao=new ProductDaoImpl();
List<Product> list=productDao.getcid(cid);
for(Product product:list) {
product.getCategory().setCid(null);
productDao.update(product);
}
CategoryDao categoryDao=new CategoryDaoImpl();
categoryDao.delete(cid);
}
}package com.zt.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.zt.dao.ProductDao;
import com.zt.domain.Product;
import com.zt.utils.JDBCUtils;
public class ProductDaoImpl implements ProductDao {
@Override
public List<Product> findAll() {
Connection con=null;
PreparedStatement prep=null;
ResultSet res =null;
List <Product> list=null;
try {
con=JDBCUtils.getConnection();
String sql="select *from product p,category c where p.cid=c.cid ORDER BY p.pid ASC";
prep=con.prepareStatement(sql);
res=prep.executeQuery();
list=new ArrayList<Product>();
while(res.next()) {
Product product=new Product();
product.setPid(res.getInt("pid"));
product.setPname(res.getString("pname"));
product.setAuthor(res.getString("author"));
product.setPrice(res.getDouble("price"));
product.setDescription(res.getString("description"));
product.setFilename(res.getString("filename"));
product.setPath(res.getString("path"));
//封装商品所属分类
product.getCategory().setCid(res.getInt("cid"));
product.getCategory().setCname(res.getString("cname"));
product.getCategory().setCdesc(res.getString("cdesc"));
list.add(product);
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(res, prep, con);
}
return list;
}
@Override
public void save(Product product) {
Connection con=null;
PreparedStatement prep=null;
try {
con=JDBCUtils.getConnection();
String sql="insert product values(null,?,?,?,?,?,?,?)";
prep=con.prepareStatement(sql);
prep.setString(1, product.getPname());
prep.setString(2, product.getAuthor());
prep.setDouble(3, product.getPrice());
prep.setString(4, product.getDescription());
prep.setString(5, product.getFilename());
prep.setString(6, product.getPath());
prep.setInt(7, product.getCategory().getCid());
prep.executeUpdate();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(prep, con);
}
}
@Override
public Product edit(Integer pid) {
Connection con=null;
PreparedStatement prep=null;
ResultSet res =null;
try {
con=JDBCUtils.getConnection();
String sql="select * from product p,category c where p.cid=c.cid AND p.pid=?";
prep=con.prepareStatement(sql);
prep.setInt(1,pid);
res=prep.executeQuery();
if(res.next()) {
Product product=new Product();
product.setPid(res.getInt("pid"));
product.setPname(res.getString("pname"));
product.setAuthor(res.getString("author"));
product.setPrice(res.getDouble("price"));
product.setDescription(res.getString("description"));
product.setFilename(res.getString("filename"));
product.setPath(res.getString("path"));
product.getCategory().setCid(res.getInt("cid"));
product.getCategory().setCname(res.getString("cname"));
product.getCategory().setCdesc(res.getString("cdesc"));
return product;
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(res, prep, con);
}
return null;
}
@Override
public void update(Product product) {
Connection con=null;
PreparedStatement prep=null;
try {
con=JDBCUtils.getConnection();
String sql="update product set pname=?,author=?,price=?,description=?,filename=?,path=?,cid=? where pid=?";
prep=con.prepareStatement(sql);
prep.setString(1, product.getPname());
prep.setString(2, product.getAuthor());
prep.setDouble(3, product.getPrice());
prep.setString(4, product.getDescription());
prep.setString(5, product.getFilename());
prep.setString(6, product.getPath());
prep.setObject(7, product.getCategory().getCid());
prep.setInt(8, product.getPid());
prep.executeUpdate();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(prep, con);
}
}
@Override
public void delete(Integer pid) {
Connection con=null;
PreparedStatement prep=null;
try {
con=JDBCUtils.getConnection();
String sql="delete from product where pid=?";
prep=con.prepareStatement(sql);
prep.setInt(1, pid);
prep.executeUpdate();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(prep, con);
}
}
@Override
public List<Product> getcid(Integer cid) {
Connection con=null;
PreparedStatement prep=null;
ResultSet res =null;
List <Product> list=null;
try {
con=JDBCUtils.getConnection();
String sql="select *from product p,category c where p.cid=c.cid and p.cid=? ORDER BY p.pid ASC";
prep=con.prepareStatement(sql);
prep.setInt(1, cid);
res=prep.executeQuery();
list=new ArrayList<Product>();
while(res.next()) {
Product product=new Product();
product.setPid(res.getInt("pid"));
product.setPname(res.getString("pname"));
product.setAuthor(res.getString("author"));
product.setPrice(res.getDouble("price"));
product.setDescription(res.getString("description"));
product.setFilename(res.getString("filename"));
product.setPath(res.getString("path"));
//封装商品所属分类
product.getCategory().setCid(res.getInt("cid"));
product.getCategory().setCname(res.getString("cname"));
product.getCategory().setCdesc(res.getString("cdesc"));
list.add(product);
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(res, prep, con);
}
return list;
}
}package com.zt.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.zt.dao.CategoryDao;
import com.zt.domain.Category;
import com.zt.utils.JDBCUtils;
public class CategoryDaoImpl implements CategoryDao {
//查询所有
@Override
public List<Category> findAll() {
Connection con=null;
PreparedStatement prep=null;
ResultSet res=null;
List <Category> list=null;
try {
con=JDBCUtils.getConnection();
String sql="select * from category";
prep=con.prepareStatement(sql);
res=prep.executeQuery();
list=new ArrayList<Category>();
while(res.next()) {
Category category=new Category();
category.setCid(res.getInt("cid"));
category.setCname(res.getString("cname"));
category.setCdesc(res.getString("cdesc"));
list.add(category);
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(res, prep, con);
}
return list;
}
//添加
@Override
public void save(Category category) {
Connection con=null;
PreparedStatement prep=null;
try {
con=JDBCUtils.getConnection();
String sql="insert category values(null,?,?)";
prep=con.prepareStatement(sql);
prep.setString(1, category.getCname());
prep.setString(2, category.getCdesc());
prep.executeUpdate();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(prep, con);
}
}
@Override
public Category findOne(Integer cid) {
Connection con=null;
PreparedStatement prep=null;
ResultSet res=null;
try {
con=JDBCUtils.getConnection();
String sql="select * from category where cid=?";
prep=con.prepareStatement(sql);
prep.setInt(1, cid);
res=prep.executeQuery();
if(res.next()) {
Category c=new Category();
c.setCid(res.getInt("cid"));
c.setCname(res.getString("cname"));
c.setCdesc(res.getString("cdesc"));
return c;
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(res, prep, con);
}
return null;
}
@Override
public void update(Category category) {
Connection con=null;
PreparedStatement prep=null;
try {
con=JDBCUtils.getConnection();
String sql="update category set cname=?,cdesc=? where cid=?";
prep=con.prepareStatement(sql);
prep.setString(1, category.getCname());
prep.setString(2, category.getCdesc());
prep.setInt(3, category.getCid());
prep.executeUpdate();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(prep, con);
}
}
@Override
public void delete(Integer cid) {
Connection con=null;
PreparedStatement prep=null;
try {
con=JDBCUtils.getConnection();
String sql="delete from category where cid=?";
prep=con.prepareStatement(sql);
prep.setInt(1, cid);
prep.executeUpdate();
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}finally {
JDBCUtils.release(prep, con);
}
}
}写回答
1回答
-
同学你好，其实这里并没有真正的把商品删了，
因为分类的cid与商品的cid是外键关系，如果该分类下有商品，直接删除该分类是会报错的。
所以我们先将商品的cid设置为null，与要删除的分类解绑。这样就可以成功删除分类了。
而展示商品的时候，查询时是用商品的cid=分类的cid的，因为之前删除的分类下的商品cid被置为null了，所以也没有展示出来。如果同学像显示出来，可以添加一个默认的分类，如果要删除商品，就把cid赋值为默认分类的cid，也是可以的。这样查询的时候，就都会显示出来了。
如果我的回答解决了你的疑惑，请采纳！祝学习愉快！
2
相似问题