数据库的 “增删查改” 命令操作

前言

在MySQL数据库（四）底层NB-IoT数据流的数据库写入笔记中，已经成功在数据库中添加数据，接下来需要建立一个数据库的管理平台，主要为数据的"增删查改"功能。

提示：以下是本篇文章正文内容，下面案例可供参考

一、增_insert

public int add(Connection con,Book book)throws Exception{
          
   
		String sql="insert into t_book values(null,?,?,?,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3, book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setInt(5, book.getBookTypeId());
		pstmt.setString(6, book.getBookDesc());
		return pstmt.executeUpdate();
	}

二、删_delete

public int delete(Connection con,String id)throws Exception{
          
   
		String sql="delete from t_book where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, id);
		return pstmt.executeUpdate();
	}

三、查_select

public ResultSet list(Connection con,Book book)throws Exception{
          
   
		StringBuffer sb=new StringBuffer("select * from t_book b,t_bookType bt where b.bookTypeId=bt.id");
		if(StringUtil.isNotEmpty(book.getBookName())){
          
   
			sb.append(" and b.bookName like %"+book.getBookName()+"%");
		}
		if(StringUtil.isNotEmpty(book.getAuthor())){
          
   
			sb.append(" and b.author like %"+book.getAuthor()+"%");
		}
		if(book.getBookTypeId()!=null && book.getBookTypeId()!=-1){
          
   
			sb.append(" and b.bookTypeId="+book.getBookTypeId());
		}
		PreparedStatement pstmt=con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}

四、改_update

public int update(Connection con,Book book)throws Exception{
          
   
		String sql="update t_book set bookName=?,author=?,sex=?,price=?,bookDesc=?,bookTypeId=? where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, book.getBookName());
		pstmt.setString(2, book.getAuthor());
		pstmt.setString(3, book.getSex());
		pstmt.setFloat(4, book.getPrice());
		pstmt.setString(5, book.getBookDesc());
		pstmt.setInt(6, book.getBookTypeId());
		pstmt.setInt(7, book.getId());
		return pstmt.executeUpdate();
	}