package manager;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 該類用于連接數據庫,關閉數據庫
*
* @author Cute Code
*
*/
public class DBManager {
/**
* 無參構造函數
*
*/
public DBManager() {
}
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;
private String url = "jdbc:mysql://localhost:3306/bookshop?autoReconnect=true&UseUnicode=true&"
+ "characterEncoding=UTF-8";
private String username = "root";
private String password = "7684751";
/**
* 獲得數據庫連接對象
*
* @return conn 數據庫連接對象
*/
public Connection getConnection() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection(url, username, password);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
/**
* 關閉數據庫
*/
public void closeAll(ResultSet rs,Statement stmt,Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//DAO類
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import manager.*;
import beans.*;
/**
* 實現對BOOK的管理
* 版本1.0
* 作者：CuteCode
*
*/
public class BookDAO {
/**
* 根據編號來查詢BOOK
* @param id
* @return BOOK對象
*/
public Book querryUseId(int bookId){
DBManager manager = null;
String querry = "select * from book where id = "+ bookId +";";
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
Book book = null;
try{
book = new Book();
manager = new DBManager();
conn = manager.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(querry);
while(rs.next()){
book.setId(rs.getInt(1));
book.setName(rs.getString(2));
book.setAuthor(rs.getString(3));
book.setBookman(rs.getString(4));
book.setPrice(rs.getFloat(5));
book.setCategoryId(rs.getInt(6));
book.setIntroduction(rs.getString(7));
book.setOnSaleDate(rs.getDate(8));
book.setOnSaleNum(rs.getInt(9));
book.setRemainNum(rs.getInt(10));
return book;
}
return book;
}catch(Exception e ){
e.printStackTrace();
}finally{
manager.closeAll(rs, stmt, conn);
}
return book;
}
/**
* 查詢所有的圖書
* @return ARRAYLIST 查詢結果
*/
public ArrayList querryAllBooks(){
String querry = "select * from book;";
ArrayList al = null;
DBManager dbm = null;
Connection conn = null;
Statement stmt = null;
ResultSet rs= null;
try{
dbm = new DBManager();
conn = dbm.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(querry);
al = new ArrayList();
while(rs.next()){
Book bk = new Book();
bk.setId(rs.getInt(1));
bk.setName(rs.getString(2));
bk.setAuthor(rs.getString(3));
bk.setBookman(rs.getString(4));
bk.setPrice(rs.getFloat(5));
bk.setCategoryId(rs.getInt(6));
bk.setIntroduction(rs.getString(7));
bk.setOnSaleDate(rs.getDate(8));
bk.setOnSaleNum(rs.getInt(9));
bk.setRemainNum(rs.getInt(10));
al.add(bk);
}
return al;
}catch(Exception e){
e.printStackTrace();
}finally{
dbm.closeAll(rs, stmt, conn);
}
return al;
}
/**
* 插入一本書到數據庫
* @param book
*/
public void insert(Book book){
DBManager manager = null;
String insert = "insert into book(name,author,bookman,price,categoryId,introduction,onSaleDate,onSaleNum,remainNum)" +
"values(?,?,?,?,?,?,?,?,?);";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try{
manager = new DBManager();
conn = manager.getConnection();
pstmt = conn.prepareStatement(insert);
pstmt.setString(1, book.getName());
pstmt.setString(2, book.getAuthor());
pstmt.setString(3,book.getBookman());
pstmt.setDouble(4,book.getPrice());
pstmt.setInt(5,book.getCategoryId());
pstmt.setString(6,book.getIntroduction());
pstmt.setDate(7,book.getOnSaleDate());
pstmt.setInt(8,book.getOnSaleNum());
pstmt.setInt(9,book.getRemainNum());
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
manager.closeAll(rs, pstmt, conn);
}
}
/**
* 根據ID刪除一本書
* @param bookId
*/
public void delete(int bookId){
String delete = "delete from book where id = "+ bookId+ ";";
DBManager dbm = null;
Connection conn = null;
Statement stmt = null;
try{
dbm = new DBManager();
conn = dbm.getConnection();
stmt = conn.createStatement();
stmt.executeUpdate(delete);
}catch(Exception e){
e.printStackTrace()
;
}finally{
dbm.closeAll(null, stmt, conn);
}
}
/**
* 更新一本書的信息
* @param book
*/
public void update(Book book){
String update = "update book set name = ?,author =?, bookman =?,price=?," +
"categoryId=?,introduction=?,onSaleDate=?,onSaleNum =?,remainNum=? where id = "+ book.getId();
DBManager dbm = null;
Connection conn= null;
PreparedStatement pstmt = null;
try{
dbm = new DBManager();
conn= dbm.getConnection();
pstmt = conn.prepareStatement(update);
pstmt.setString(1,book.getName());
pstmt.setString(2,book.getAuthor());
pstmt.setString(3,book.getBookman());
pstmt.setFloat(4,book.getPrice());
pstmt.setInt(5, book.getCategoryId());
pstmt.setString(6,book.getIntroduction());
pstmt.setDate(7,book.getOnSaleDate());
pstmt.setInt(8, book.getOnSaleNum());
pstmt.setInt(9,book.getRemainNum());
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
dbm.closeAll(null, pstmt, conn);
}
}
/**
* 更新書的數量
* @param bookId
* @param saleNum
*/
public void updateNum(int bookId,int saleNum){
String querry = "select * from book where id="+bookId;
String sql;
DBManager dbm = null;
Connection conn = null;
Statement stmt = null;
try{
ResultSet rs = null;
dbm = new DBManager();
conn = dbm.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(querry);
int num=0;
if(rs.next()){
num = rs.getInt(10);
}
sql= "update book set remainNum ="+(num-saleNum)+" where id="+ bookId+ ";";
stmt.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace()
;
}finally{
dbm.closeAll(null, stmt, conn);
}
}
}