Package Com.dgh.dao;
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;
Import java.util.List;
Import Com.dgh.model.BookPO;
Import Com.dgh.model.UserPO; public class Dbutil {Connection conn; Database Connection object PreparedStatement pstmt; Preprocessing Object ResultSet RS;
Result set object//Get database Connection object Public Dbutil () {try {class.forname ("oracle.jdbc.driver.OracleDriver");
conn = Drivermanager.getconnection ("Jdbc:oracle:thin:@127.0.0.1:1521:dbname", "UserName", "password");
catch (ClassNotFoundException e) {e.printstacktrace ();
}catch (SQLException e) {e.printstacktrace ();
}///Close individual connection objects public void Closeconn () {try{if (rs!= null) {rs.close ();
}}catch (Exception e) {} try{if (pstmt!= null) {pstmt.close (); }}catch (Exception e) {} try{if (conn!= null) {Conn.close ();
}catch (Exception e) {}}///query user based on UserName, returns the object when found, does not return null public Userpo CheckUser (String userName) {try {
String sql = "SELECT * from t_user where userName =?";
pstmt = conn.preparestatement (sql);
Pstmt.setstring (1, userName);
rs = Pstmt.executequery ();
Userpo user = new Userpo ();
while (Rs.next ()) {//userpo.setid (rs.getstring ("Id"));
Userpo.setusername (rs.getstring ("UserName"));
Userpo.setpassword (rs.getstring ("password"));
return user;
return null;
catch (Exception e) {e.printstacktrace ();
} finally{Closeconn ();
return null;
//According to UserName, the password queries the user, returns the object when it is found, does not return null public USERPO login (string userName, string password) {Userpo = null;
Userpo = CheckUser (userName);
if (Userpo!= null && password.equals (Userpo.getpassword ())) {return userpo;
}else{return null; }//Query All Books public list<bookpo> findall () {try{list<bookpo> Booklist = new ArraylIst<bookpo> ();
pstmt = Conn.preparestatement ("SELECT * from T_book");
ResultSet rs = Pstmt.executequery ();
while (Rs.next ()) {Bookpo Bookpo = new Bookpo ();
Bookpo.setid (rs.getstring ("id"));
Bookpo.setbookname (rs.getstring ("BookName"));
Booklist.add (book);
return Booklist;
}catch (Exception e) {e.printstacktrace ();
}finally{Closeconn ();
return null; To determine the number of books by ID public int findbooknum (String id) {try {pstmt = conn.preparestatement (' select * from T_book whe
Re id =? ");
Pstmt.setstring (1,id);
ResultSet rs = Pstmt.executequery ();
while (Rs.next ()) {return Rs.getint ("Amount");
} catch (Exception e) {e.printstacktrace ();
} finally{Closeconn ();
return 0; Check books according to ID and update the number of books public int lendbook (String id) {try{pstmt = conn.preparestatement ("update t_book Set num =
num-1 WHERE id =? ");
Pstmt.setstring (1, id);
int count = Pstmt.executeupdate ();
return count; }catch (Exception e) {e.printstacktrace ();
}finally{Closeconn ();
return 0; Check the book Public list<bookpo> findbyidorname (string ID, string name) {StringBuffer sql = new Stringbuf by ID or book name
Fer ();
list<string> paramlist = new arraylist<string> ();
Sql.append ("select * from T_book where 1 = 1");
Piecing together SQL statements if (ID!= null) {Sql.append ("and id like?") based on query criteria;
Paramlist.add (ID);
} if (name!= null) {Sql.append ("and name Linke?");
Paramlist.add (name);
} try{pstmt = Conn.preparestatement (sql.tostring ());
for (int i = 0; i < paramlist.size (); i++) {pstmt.setstring (i+1, "%" +paramlist.get (i) + "%");
}//Processing result set ResultSet rs = Pstmt.executequery ();
list<bookpo> Booklist = new arraylist<bookpo> ();
while (Rs.next ()) {Bookpo Bookpo = new Bookpo ();
Bookpo.setid (rs.getstring ("id")); Bookpo.setbooknaMe (rs.getstring ("BookName"));
Booklist.add (book);
return Booklist;
catch (Exception e) {e.printstacktrace ();
} finally{Closeconn ();
return null; //////book ID based on user ID (USERID) the public int givebackbook (string bookid) {try{pstmt = conn.preparestatement ("Sele
CT * from T_book b,t_user u,t_book_uer bu where ");
Pstmt.executequery ();
return 0;
return Booklist;
}catch (Exception e) {e.printstacktrace ();
}finally{Closeconn ();
return 0; /////According to User ID query book lending//Public List Findbyuserid () {//try{//list<bookpo> Booklist = new Arraylist<bookpo
> ();
pstmt = Conn.preparestatement ("select * from T_book b,t_user u,t_book_uer bu where");
ResultSet rs = Pstmt.executequery (); while (Rs.next ()) {//Bookpo Bookpo = new Bookpo ();////bookpo.setid (rs.getstring ("id"));////bookpo.setbo
Okname (rs.getstring ("BookName"));
Booklist.add (book);
}//Return booklist; }catch (Exception e)
{//E.printstacktrace ();//}finally{//Closeconn ();//}//return null;
// }
}