2024/02/13

有关图书的简单管理

bean：

package Beans;
 
public class Books {
 
    private int bookId;
    private String bookName;
    private double price;
    
     
    public int getBookId() {
        return bookId;
    }
    public void setBookId(int bookId) {
        this.bookId = bookId;
    }
    public String getBookName() {
        return bookName;
    }
    public void setBookName(String bookName) {
        this.bookName = bookName;
    }
    public double getPrice() {
        return price;
    }
    public void setPrice(double price) {
        this.price = price;
    }
}

数据库操作

package Beans;
 
import java.util.List;
 
public interface DAO {
 
    //查询书单
    public List<Books> findAllBooks();
    //find book by id
    public Books findBookByName(String name);
    //update book
    public void updateBook(Books book);
    //add a new book
    public void addBook(Books book);
     
    //delete a book by id
    public void deleteBookByName(String name);
}

package Beans;
 
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
 
 
public class DAOImpl implements DAO {
 
    public List<Books> findAllBooks(){
        List<Books> list = new ArrayList();
        Books book = null;
        DBUtil util = new DBUtil();
        Connection conn = util.getConnection();
        String sql = "select * from Book";
        try {
            Statement  stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
         
            while(rs.next()) {
                book = new Books();
                book.setBookId( rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setPrice(rs.getDouble("price"));
                list.add(book);
            }
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
             util.closeConnection(conn);
        }
         
        return list;
    }
 
     
    
    public Books findBookByName(String name) {
        // TODO Auto-generated method stub
        DBUtil util = new DBUtil();
        Connection conn = util.getConnection();
         
        Books b = null;
        String sql = "select * from Book where bookName=? ";
        try {
            PreparedStatement pstmt=
                    conn.prepareStatement(sql);
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            if(rs.next()) {
                 b = new Books();
                 b.setBookId(rs.getInt("bookId"));
                 b.setBookName(rs.getString("bookName"));
                 b.setPrice(rs.getInt("Price"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return b;
    }
 
     
    public void updateBook(Books book) {
        // TODO Auto-generated method stub
        DBUtil util = new DBUtil();
        Connection conn = util.getConnection();
        Books b = null;
        String sql = "update Book set bookName=?,price=?,bookId=?";
        
        try {
            b=new Books();
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, b.getBookName());
            pstmt.setDouble(2, b.getPrice());
            pstmt.setInt(3, b.getBookId());
            pstmt.executeUpdate();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    public void addBook(Books book) {
        // TODO Auto-generated method stub
        DBUtil util = new DBUtil();
        Connection conn = util.getConnection();
         System.out.println(conn);
        String sql = 
        "insert into Book(bookId,bookName,price) values(?,?,?)";
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, book.getBookId());
            pstmt.setString(2, book.getBookName());
            pstmt.setDouble(3,book.getPrice());
            pstmt.executeUpdate();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
 
     
 
    
    public void deleteBookByName(String name) {
        // TODO Auto-generated method stub
        DBUtil util = new DBUtil();
        Connection conn = util.getConnection();
        String sql = "delete from Book where bookName = ? ";
        try {
            PreparedStatement  pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, name);
            pstmt.executeUpdate();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    
}
 

连接数据库

package Beans;
 
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
 
public class DBUtil {
 
     
    public Connection getConnection() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mysql://localhost:3306/demo?serverTimezone=UTC","root","zxc123456");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        return null;
    }
    
     
    
    public void closeConnection(Connection conn) {
        
        if(conn!=null)
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
    }
 
}