图书馆项目基础逻辑层

package libraryproj;


import 图书馆项目.Book;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class BookDAO {
Connection conn;
Statement st;
// 实例化两个对象,记住后续要赋值
//狗仔函数是不用
public BookDAO() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=UTF-8";
String user="root";
String pwd="123456";
conn=DriverManager.getConnection(url,user,pwd);
st=conn.createStatement();
}

//添加书本
public void addBook(Book book) throws SQLException {
String sql="use book;";
st.executeUpdate(sql);
sql="insert into book(Name, Author, Price, Publisher) values(?,?,?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, book.getName());
ps.setString(2,book.getAuthor());
ps.setDouble(3, book.getPrice());
ps.setString(4, book.getPublisher());
ps.executeUpdate();
}
//删除书本
public void deleteBook(int id) throws SQLException {
String sql="use book;";
st.executeUpdate(sql);
sql="delete from book where id =?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1, id);
ps.executeUpdate();
}

//修改
public void modifyBook(int id,Book book) throws SQLException {
String sql="use book;";
st.executeUpdate(sql);
sql="update books set name =?,author=?,price=?,publisher=? where id =?;";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, book.getName());
ps.setString(2, book.getAuthor());
ps.setDouble(3, book.getPrice());
ps.setString(4, book.getPublisher());
ps.setInt(5, id);
ps.executeUpdate();
}

public ArrayList<Book> getBooks() throws SQLException{
String sql="use book;";
st.executeUpdate(sql);
ArrayList<Book> books=new ArrayList<Book>();

sql="select ID,Name, Author, Price, Publisher from books";
ResultSet rs=st.executeQuery(sql);

while(rs.next()) {
Book book=new Book();
book.setId(rs.getInt(1));
book.setName(rs.getString(2));
book.setAuthor(rs.getString(3));
book.setPrice(rs.getFloat(4));
book.setPublisher(rs.getString(5));
books.add(book);
}
return books;
}
//用来获取单本图书的
public Book getBook(int id) throws SQLException{
String sql="use book;";
st.executeUpdate(sql);
sql="select ID,Name, Author, Price, Publisher from books where ID=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1, id);
ResultSet rs=ps.executeQuery();

Book book=new Book();
rs.next();
book.setId(rs.getInt(1));
book.setName(rs.getString(2));
book.setAuthor(rs.getString(3));
book.setPrice(rs.getFloat(4));
book.setPublisher(rs.getString(5));
return book;
}
}