package com.wsy.dao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import com.wsy.model.Back;
import com.wsy.model.BookInfo;
import com.wsy.model.BookType;
import com.wsy.model.Borrow;
import com.wsy.model.Operater;
import com.wsy.model.Order;
import com.wsy.model.OrderAndBookInfo;
import com.wsy.model.Reader;
import com.wsy.model.user;
public class Dao {
protected static String dbClassName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
protected static String dbUrl = "jdbc:microsoft:sqlserver://localhost:1433;"
+ "DatabaseName=db_library;SelectMethod=Cursor";
protected static String dbUser = "sa";
protected static String dbPwd = "";
protected static String second = null;
private static Connection conn = null;
private Dao() {
try {
if (conn == null) {
Class.forName(dbClassName).newInstance();
conn = DriverManager.getConnection(dbUrl, dbUser, dbPwd);
}
else
return;
} catch (Exception ee) {
ee.printStackTrace();
}
}
private static ResultSet executeQuery(String sql) {
try {
if(conn==null)
new Dao();
return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE).executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
}
}
private static int executeUpdate(String sql) {
try {
if(conn==null)
new Dao();
return conn.createStatement().executeUpdate(sql);
} catch (SQLException e) {
System.out.println(e.getMessage());
//if(e.getMessage().equals("[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]DELETE 语句与 COLUMN REFERENCE 约束 'FK_TB_BORRO_REFERENCE_TB_BOOKI' 冲突。该冲突发生于数据库 'db_library'，表 'tb_borrow', column 'bookISBN'。"))
return -1;
} finally {
}
}
public static void close() {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn = null;
}
}
/*
* 管理员登录方法
*/
public static Operater check(String name, String password) {
int i = 0;
Operater operater=new Operater();
String sql = "select * from tb_operator where name='" + name
+ "' and password='" + password + "'and admin=1";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
String names = rs.getString(1);
operater.setId(rs.getString("id"));
operater.setName(rs.getString("name"));
operater.setGrade(rs.getString("admin"));
operater.setPassword(rs.getString("password"));
if (names != null) {
i = 1;
}
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return operater;
}
/*
* 查询类别方法
*/
public static List selectBookCategory() {
List list=new ArrayList();
String sql = "select * from tb_bookType";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
BookType bookType=new BookType();
bookType.setId(rs.getString("id"));
bookType.setTypeName(rs.getString("typeName"));
bookType.setDays(rs.getString("days"));
bookType.setFk(rs.getString("fk"));
list.add(bookType);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
public static List selectBookCategory(String bookType) {
List list=new ArrayList();
String sql = "select days from tb_bookType where typeName='"+bookType+"'";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
BookType type=new BookType();
type.setDays(rs.getString("days"));
list.add(type);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
/*
* 图书类别表相关操作
*
*/
public static int InsertBookType(String bookTypeName,String days,Double fk){
int i=0;
try{
String sql="insert into tb_bookType(typeName,days,fk) values('"+bookTypeName+"','"+days+"',"+fk+")";
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
return i;
}
public static int UpdatebookType(String id,String typeName,String days,String fk){
int i=0;
try{
String sql="update tb_bookType set typeName='"+typeName+"',days='"+days+"',fk='"+fk+"' where id='"+id+"'";
//System.out.println(sql);
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
// public static int DelbookType(String id){
// int i=0;
// try{
// String sql="delete from tb_bookType where id='"+id+"'";
// //System.out.println(sql);
// i=Dao.executeUpdate(sql);
// }catch(Exception e){
// e.printStackTrace();
// }
// Dao.close();
// return i;
// }
public static List selectBookTypeFk(String bookType) {//取每种书超过规定时间罚款金额
List list=new ArrayList();
String sql = "select * from tb_bookType where typeName='"+bookType+"'";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
BookType type=new BookType();
type.setFk(rs.getString("fk"));
type.setDays(rs.getString("days"));
list.add(type);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
/*
* 图书信息表相关操作
*/
/*
* 插入图书信息方法
*/
public static int Insertbook(String ISBN,String typeId,String bookname,String writer,String translator,String publisher,Date date,Double price){
int i=0;
try{
String sql="insert into tb_bookInfo(ISBN,typeId,bookname,writer,translator,publisher,date,price) values('"+ISBN+"','"+typeId+"','"+bookname+"','"+writer+"','"+translator+"','"+publisher+"','"+date+"',"+price+")";
//System.out.println(sql);
i=Dao.executeUpdate(sql);
}catch(Exception e){
System.out.println(e.getMessage());
}
Dao.close();
return i;
}
/*
* 查询图书相关信息
*
*/
public static List selectBookInfo() {
List list=new ArrayList();
String sql = "select * from tb_bookInfo";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
BookInfo bookinfo=new BookInfo();
bookinfo.setISBN(rs.getString("ISBN"));
bookinfo.setTypeid(rs.getString("typeid"));
bookinfo.setBookname(rs.getString("bookname"));
bookinfo.setWriter(rs.getString("writer"));
bookinfo.setTranslator(rs.getString("translator"));
bookinfo.setPublisher(rs.getString("publisher"));
bookinfo.setDate(rs.getDate("date"));
bookinfo.setPrice(rs.getDouble("price"));
list.add(bookinfo);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
public static List selectBookInfo(String ISBN) {
List list=new ArrayList();
String sql = "select * from tb_bookInfo where ISBN='"+ISBN+"'";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
BookInfo bookinfo=new BookInfo();
bookinfo.setISBN(rs.getString("ISBN"));
bookinfo.setTypeid(rs.getString("typeid"));
bookinfo.setBookname(rs.getString("bookname"));
bookinfo.setWriter(rs.getString("writer"));
bookinfo.setTranslator(rs.getString("translator"));
bookinfo.setPublisher(rs.getString("publisher"));
bookinfo.setDate(rs.getDate("date"));
bookinfo.setPrice(rs.getDouble("price"));
list.add(bookinfo);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
/*
* 修改图书信息方法
*/
public static int Updatebook(String ISBN,String typeId,String bookname,String writer,String translator,String publisher,Date date,Double price){
int i=0;
try{
String sql="update tb_bookInfo set ISBN='"+ISBN+"',typeId='"+typeId+"',bookname='"+bookname+"',writer='"+writer+"',translator='"+translator+"',publisher='"+publisher+"',date='"+date+"',price="+price+" where ISBN='"+ISBN+"
基于Servlet+jsp+SQL Server实现的图书管理系统【完整源码+数据库】 （212个子文件） 
Dao.class 20KB ReaderModiAndDelIFrame.class 10KB BookModiAndDelIFrame.class 9KB BookBorrowIFrame.class 8KB newBookCheckIFrame.class 8KB BookBackIFrame.class 8KB newBookOrderIFrame.class 8KB UserModiAndDelIFrame.class 7KB ReaderAddIFrame.class 7KB BookAddIFrame.class 7KB Library.class 6KB BookTypeModiAndDelIFrame.class 6KB BookSearchIFrame.class 6KB UserAddIFrame.class 5KB GengGaiMiMa.class 5KB BookLoginIFrame.class 4KB ReaderModiAndDelIFrame$ModiButtonListener.class 4KB BookModiAndDelIFrame$addBookActionListener.class 4KB BookTypeAddIFrame.class 4KB ReaderAddIFrame$ButtonAddListener.class 4KB UserModiAndDelIFrame$3.class 3KB BookBorrowIFrame$bookISBNListenerlostFocus.class 3KB MenuActions.class 3KB BookAddIFrame$addBookActionListener.class 3KB OrderAndBookInfo.class 3KB UserAddIFrame$1.class 3KB BookBackIFrame$TableListener.class 3KB newBookOrderIFrame$ButtonAddLisenter.class 3KB Reader.class 3KB newBookOrderIFrame$ISBNListenerlostFocus.class 2KB BookTypeModiAndDelIFrame$ButtonAddListener.class 2KB BookBorrowIFrame$ISBNListenerlostFocus.class 2KB newBookCheckIFrame$CheckActionListener.class 2KB newBookOrderIFrame$ISBNListener.class 2KB ReaderModiAndDelIFrame$TableListener.class 2KB newBookCheckIFrame$TableListener.class 2KB ReaderModiAndDelIFrame$DelButtonListener.class 2KB BookBorrowIFrame$BorrowActionListener.class 2KB UserModiAndDelIFrame$1.class 2KB BookModiAndDelIFrame$TableListener.class 2KB BookSearchIFrame$1.class 2KB Back.class 2KB user.class 2KB BookInfo.class 2KB BookTypeAddIFrame$1.class 2KB BookBackIFrame$BookBackActionListener.class 2KB UserModiAndDelIFrame$4.class 2KB BookBorrowIFrame$ClearActionListener.class 2KB GengGaiMiMa$1.class 2KB BookLoginIFrame$BookLoginAction.class 2KB Borrow.class 2KB Library$1.class 2KB BookTypeModiAndDelIFrame$TableListener.class 2KB Order.class 2KB MenuActions$ReaderModiAction.class 1KB MenuActions$CheckAndAcceptNewBookAction.class 1KB MenuActions$ReaderAddAction.class 1KB MenuActions$UserModiAction.class 1KB MenuActions$BookTypeModiAction.class 1KB MenuActions$BookTypeAddAction.class 1KB MenuActions$BookSearchAction.class 1KB MenuActions$BoodOrderAction.class 1KB MenuActions$BorrowAction.class 1KB MenuActions$GiveBackAction.class 1KB MenuActions$BookModiAction.class 1KB MenuActions$PasswordModiAction.class 1KB MenuActions$UserAddAction.class 1KB MenuActions$BookAddAction.class 1KB BookBorrowIFrame$TimeActionListener.class 1KB BookBackIFrame$TimeActionListener.class 1KB MapPz.class 1KB ReaderModiAndDelIFrame$KeepmoneyListener.class 1KB BookAddIFrame$ISBNFocusListener.class 1KB BookLoginIFrame$BookResetAction.class 1KB ReaderAddIFrame$1.class 1KB newBookOrderIFrame$DateListener.class 1KB newBookCheckIFrame$DateListener.class 1KB ReaderAddIFrame$DateListener.class 1KB Operater.class 1KB BookType.class 1KB ReaderModiAndDelIFrame$NumberListener.class 955B ReaderModiAndDelIFrame$TelListener.class 947B BookModiAndDelIFrame$NumberListener.class 944B newBookOrderIFrame$NumberListener.class 931B BookTypeAddIFrame$NumberListener.class 926B GengGaiMiMa$2.class 920B ReaderAddIFrame$NumberListener.class 913B ReaderAddIFrame$TelListener.class 905B BookAddIFrame$NumberListener.class 902B MyDocument.class 899B BookAddIFrame$ISBNkeyListener.class 894B BookLoginIFrame$1.class 894B CreatecdIcon.class 890B BookBackIFrame$readerISBNListenerlostFocus.class 825B BookTypeModiAndDelIFrame$CloseActionListener.class 823B MenuActions$ExitAction.class 813B UserModiAndDelIFrame$CloseActionListener.class 799B newBookOrderIFrame$CloseActionListener.class 787B newBookCheckIFrame$CloseActionListener.class 787B BookSearchIFrame$CloseActionListener.class 775B共 212 条
- 1
- 2
- 3
资源评论
