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查询用户,查到就返回该对象，没有返回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;
	}
	
	//根据userName，密码查询用户,查到就返回该对象，没有返回null
	public 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;
//	}
}