jdbc连接oralce操作

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;    //数据库连接对象PreparedStatement pstmt;    //预处理对象ResultSet rs;    //结果集对象//获取数据库连接对象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();}}//关闭各个连接对象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) {}}//根据userName查询用户,查到就返回该对象，没有返回nullpublic 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;}//根据userName，密码查询用户,查到就返回该对象，没有返回nullpublic UserPO login(String userName, String password){UserPO userPO = null;userPO = checkUser(userName);if(userPO != null && password.equals(userPO.getPassword())){return userPO;}else{return null;}}//查询所有的书本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;}//根据id来判断书本的数量public int findBookNum(String id){try {pstmt = conn.prepareStatement("select * from t_book where 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;}//根据id查询借书，并更新书本的数量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;}//根据id或书籍名称查询书籍public List<BookPO> findByIdOrName(String id, String name){StringBuffer sql = new StringBuffer();List<String> paramList = new ArrayList<String>();sql.append("select * from t_book where 1 = 1 ");//根据查询条件，拼凑sql语句        if(id != null){sql.append(" and id like ? ");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)+"%");}                //处理结果集        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;}//根据用户id与书籍id还书public int giveBackBook(String userId, String bookId){try{pstmt = conn.prepareStatement("select * 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;}////根据用户id查询书籍借阅情况//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.setBookName(rs.getString("bookName"));////bookList.add(book);//}//return bookList;//}catch (Exception e) {//e.printStackTrace();//}finally{//closeConn();//}//return null;//}}