?? dao.java
字號:
reader.setSex(rs.getString("sex"));
reader.setAge(rs.getString("age"));
reader.setIdentityCard(rs.getString("identityCard"));
reader.setDate(rs.getDate("date"));
reader.setMaxNum(rs.getString("maxNum"));
reader.setTel(rs.getString("tel"));
reader.setKeepMoney(rs.getDouble("keepMoney"));
reader.setZj(rs.getInt("zj"));
reader.setZy(rs.getString("zy"));
reader.setISBN(rs.getString("ISBN"));
reader.setBztime(rs.getDate("bztime"));
list.add(reader);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
public static int UpdateReader(String id,String name,String sex,String age,String identityCard,Date date,String maxNum,String tel,Double keepMoney,String zj,String zy,Date bztime,String ISBN){
int i=0;
try{
String sql="update tb_reader set name='"+name+"',sex='"+sex+"',age='"+age+"',identityCard='"+identityCard+"',date='"+date+"',maxNum='"+maxNum+"',tel='"+tel+"',keepMoney="+keepMoney+",zj='"+zj+"',zy='"+zy+"',bztime='"+bztime+"'where ISBN='"+ISBN+"'";
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
public static int DelReader(String ISBN){
int i=0;
try{
String sql="delete from tb_reader where ISBN='"+ISBN+"'";
//System.out.println(sql);
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
/*
* 對訂購信息表操作
*/
public static int InsertBookOrder(String ISBN,Date date,String number,String operator,String checkAndAccept,Double zk){
int i=0;
try{
String sql="insert into tb_order(ISBN,date,number,operator,checkAndAccept,zk) values('"+ISBN+"','"+date+"','"+number+"','"+operator+"',"+checkAndAccept+",'"+zk+"')";
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
public static List selectBookOrder() {
List list=new ArrayList();
String sql = "SELECT * FROM tb_order a INNER JOIN tb_bookInfo b ON a.ISBN = b.ISBN";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
OrderAndBookInfo order=new OrderAndBookInfo();
order.setISBN(rs.getString(1));
order.setOrderdate(rs.getDate(2));
order.setNumber(rs.getString(3));
order.setOperator(rs.getString(4));
order.setCheckAndAccept(rs.getString(5));
order.setZk(rs.getDouble(6));
order.setTypeId(rs.getString(8));
order.setBookname(rs.getString(9));
order.setWriter(rs.getString(10));
order.setTraslator(rs.getString(11));
order.setPublisher(rs.getString(12));
order.setDate(rs.getDate(13));
order.setPrice(rs.getDouble(14));
list.add(order);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
public static List selectBookOrder(String ISBN) {
List list=new ArrayList();
String sql = "SELECT * FROM tb_order where ISBN='"+ISBN+"'";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
Order order=new Order();
order.setISBN(rs.getString("ISBN"));
order.setDate(rs.getDate("date"));
order.setNumber(rs.getString("number"));
order.setOperator(rs.getString("operator"));
order.setZk("zk");
order.setCheckAndAccept("checkAndAccept");
list.add(order);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
public static int UpdateCheckBookOrder(String ISBN){
int i=0;
try{
String sql="update tb_order set checkAndAccept=0 where ISBN='"+ISBN+"'";
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
/*
* 對借閱表進行操作
*/
public static int InsertBookBorrow(String bookISBN,String readerISBN,String operatorId,Timestamp borrowDate,Timestamp backDate){
int i=0;
try{
String sql="insert into tb_borrow(bookISBN,readerISBN,operatorId,borrowDate,backDate)values('"+bookISBN+"','"+readerISBN+"','"+operatorId+"','"+borrowDate+"','"+backDate+"')";
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
public static List selectBorrow(String readerISBN) {
List list=new ArrayList();
String sql = "select * from tb_borrow where readerISBN='"+readerISBN+"'";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
Borrow borrow=new Borrow();
borrow.setId(rs.getInt("id"));
borrow.setBookISBN(rs.getString("bookISBN"));
borrow.setReaderISBN(rs.getString("readerISBN"));
borrow.setBorrowDate(rs.getString("borrowDate"));
borrow.setBackDate(rs.getString("backDate"));
borrow.setBookName(rs.getString("borrowBookName"));
list.add(borrow);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
/*
* 查詢還書內容，tb_bookinfo tb_reader tb_borrow之間的查詢
*/
public static List selectBookBack(String readerISBN) {
List list=new ArrayList();
String sql = "SELECT a.ISBN AS bookISBN, a.bookname, a.typeId ,b.id,b.operatorId, b.borrowDate, b.backDate, c.name AS readerName, c.ISBN AS readerISBN FROM tb_bookInfo a INNER JOIN tb_borrow b ON a.ISBN = b.bookISBN INNER JOIN tb_reader c ON b.readerISBN = c.ISBN WHERE (c.ISBN = '"+readerISBN+"' and isback=1)";
System.out.println(sql);
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
Back back=new Back();
back.setBookISBN(rs.getString("bookISBN"));
back.setBookname(rs.getString("bookname"));
back.setTypeId(rs.getInt("typeId"));
back.setOperatorId(rs.getString("operatorId"));
back.setBorrowDate(rs.getString("borrowDate"));
back.setBackDate(rs.getString("backDate"));
back.setReaderName(rs.getString("readerName"));
back.setReaderISBN(rs.getString("readerISBN"));
back.setId(rs.getInt("id"));
list.add(back);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
public static int UpdateBookBack(String bookISBN,String readerISBN,int id){//歸還圖書操作
int i=0;
try{
String sql="update tb_borrow set isback=0 where bookISBN='"+bookISBN+"'and readerISBN='"+readerISBN+"' and id="+id+"";
System.out.println(sql);
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
//new
public static List selectbookserch() {
List list=new ArrayList();
String sql = "select * from tb_bookInfo";
ResultSet s = Dao.executeQuery(sql);
try {
while (s.next()) {
BookInfo bookinfo=new BookInfo();
bookinfo.setISBN(s.getString(1));
bookinfo.setTypeid(s.getString(2));
bookinfo.setBookname(s.getString(3));
bookinfo.setWriter(s.getString(4));
bookinfo.setTranslator(s.getString(5));
bookinfo.setPublisher(s.getString(6));
bookinfo.setDate(s.getDate(7));
bookinfo.setPrice(s.getDouble(8));
list.add(bookinfo);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
public static List selectbookmohu(String bookname){
List list=new ArrayList();
String sql="select * from tb_bookInfo where bookname like '%"+bookname+"%'";
System.out.print(sql);
ResultSet s=Dao.executeQuery(sql);
try {
while(s.next()){
BookInfo bookinfo=new BookInfo();
bookinfo.setISBN(s.getString(1));
bookinfo.setTypeid(s.getString(2));
bookinfo.setBookname(s.getString(3));
bookinfo.setWriter(s.getString(4));
bookinfo.setTranslator(s.getString(5));
bookinfo.setPublisher(s.getString(6));
bookinfo.setDate(s.getDate(7));
bookinfo.setPrice(s.getDouble(8));
list.add(bookinfo);
}
} catch (SQLException e) {
// TODO 自動生成 catch 塊
e.printStackTrace();
}
return list;
}
public static List selectbookmohuwriter(String writer){
List list=new ArrayList();
String sql="select * from tb_bookInfo where writer like '%"+writer+"%'";
System.out.print(sql);
ResultSet s=Dao.executeQuery(sql);
try {
while(s.next()){
BookInfo bookinfo=new BookInfo();
bookinfo.setISBN(s.getString(1));
bookinfo.setTypeid(s.getString(2));
bookinfo.setBookname(s.getString(3));
bookinfo.setWriter(s.getString(4));
bookinfo.setTranslator(s.getString(5));
bookinfo.setPublisher(s.getString(6));
bookinfo.setDate(s.getDate(7));
bookinfo.setPrice(s.getDouble(8));
list.add(bookinfo);
}
} catch (SQLException e) {
// TODO 自動生成 catch 塊
e.printStackTrace();
}
return list;
}
public static int Insertoperator(String name,String sex,int age,String identityCard,Date workdate,String tel,String password){
int i=0;
try{
String sql="insert into tb_operator(name,sex,age,identityCard,workdate,tel,password) values('"+name+"','"+sex+"',"+age+",'"+identityCard+"','"+workdate+"','"+tel+"','"+password+"')";
System.out.println(sql);
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
public static List selectuser() {
List list=new ArrayList();
String sql = "select id,name,sex,age,identityCard,workdate,tel,password from tb_operator where admin=0";
ResultSet rs = Dao.executeQuery(sql);
try {
while (rs.next()) {
user user=new user();
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setSex(rs.getString(3));
user.setAge(rs.getInt(4));
user.setIdentityCard(rs.getString(5));
user.setWorkdate(rs.getDate(6));
user.setTel(rs.getString(7));
user.setPassword(rs.getString(8));
list.add(user);
}
} catch (Exception e) {
e.printStackTrace();
}
Dao.close();
return list;
}
public static int Deluser(int id){
int i=0;
try{
String sql="delete from tb_operator where id='"+id+"'";
//System.out.println(sql);
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
public static int Updateuser(int id,String name,String sex,int age,String identityCard,Date workdate,String tel,String password){
int i=0;
try{
String sql="update tb_operator set name='"+name+"',sex='"+sex+"',age="+age+",identityCard='"+identityCard+"',workdate='"+workdate+"',tel='"+tel+"',password='"+password+"' where id='"+id+"'";
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
public static int Updatepass(String password,String name){
int i=0;
try{
String sql="update tb_operator set password='"+password+"' where name='"+name+"'";
i=Dao.executeUpdate(sql);
}catch(Exception e){
e.printStackTrace();
}
Dao.close();
return i;
}
}?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -