java与Oracle数据库的连接 增删改查的源代码

package jxc.book.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jxc.book.dao.dao.BookDao;
import jxc.book.vo.BookModel;
import jxc.book.vo.BookQueryModel;

public class BookImpl implements BookDao {
    static {
        try {
//装载数据库的驱动程序
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    @Override
    public BookModel create(BookModel book) {
//调用方法nextid(),建立id序列生成
        book.setBookId(nextid());
//建立连接的url地址，lake是数据库名称
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:lake";
        String username = "lake";
        String password = "lake";
//声明一个连接
        Connection conn = null;
        try {
//建立连接，通过驱动管理对象的getConnection方法。
            conn = DriverManager.getConnection(url,username,password);
//设置不进行自动提交
            conn.setAutoCommit(false);
            //需要执行的sql语句，？是PreparedStatement的一个占位符，他的前身是Statement。
            String sql = "insert into tbl_book(bookId,bookName,inPrice,salePrice) values(?,?,?,?)";
//发送sql语句
            PreparedStatement psst = conn.prepareStatement(sql);
            //获取设置的参数
            int index = 1;
            psst.setInt(index++, book.getBookId());
            psst.setString(index++,book.getBookName());
            psst.setInt(index++, book.getInPrice());
            psst.setInt(index++, book.getSalePrice());
//执行sql语句
            psst.executeUpdate();
            
            psst.close();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
        return book;
    }

    private int nextid() {
        int nextid = 0;
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:lake";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,"lake","lake");
            conn.setAutoCommit(false);
            
            String sql = "select seq_user.nextval from dual";
            PreparedStatement psst = conn.prepareStatement(sql);
            
            ResultSet rs = psst.executeQuery();
            
            rs.next();
            nextid = rs.getInt(1);
            
            psst.close();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
        return nextid;
    }

    @Override
    public BookModel update(BookModel book) {
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:lake";
        Connection conn = null;
        
        try {
            conn = DriverManager.getConnection(url,"lake","lake");
            conn.setAutoCommit(false);
            
            String sql = "update tbl_book set bookName=?,inPrice=?,salePrice=? where bookId = ?";
            PreparedStatement psst = conn.prepareStatement(sql);

            int index = 1;
            psst.setString(index++, book.getBookName());
            psst.setInt(index++,book.getInPrice());
            psst.setInt(index++,book.getSalePrice());
            psst.setInt(index++, book.getBookId());
            
            psst.executeUpdate();
            psst.close();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally{
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return book;
    }

    @Override
    public BookModel delete(BookModel book) {
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:lake";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,"lake","lake");
            conn.setAutoCommit(false);
            
            String sql = "delete from tbl_book where bookId = ?";
            PreparedStatement psst = conn.prepareStatement(sql);
            
            psst.setInt(1,book.getBookId());
            psst.executeUpdate();
        
            psst.close();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally{
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return book;
    }

    @Override
    public BookModel query(BookModel book) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public List<BookModel> getAll() {
        List<BookModel>  booklist = new ArrayList<BookModel>();
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:lake";
        Connection conn = null;
        
        try {
            conn = DriverManager.getConnection(url,"lake","lake");
            conn.setAutoCommit(false);
            
            String sql = "select bookId,bookName,inPrice,salePrice from tbl_book";
            PreparedStatement psst = conn.prepareStatement(sql);
            
            ResultSet rs = psst.executeQuery();
            
            while(rs.next()){
                BookModel book = new BookModel();
            
                book.setBookId(rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setInPrice(rs.getInt("inPrice"));
                book.setSalePrice(rs.getInt("salePrice"));
                booklist.add(book);
            }
            rs.close();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            
            e.printStackTrace();
        }finally{
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return booklist;
    }

    @Override
    public List<BookModel> getByCondition(BookQueryModel qm) {
        List<BookModel> booklist = new ArrayList<BookModel>();
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:lake";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url,"lake","lake");
            conn.setAutoCommit(false);
            
            String sql = "select bookId,bookName,inPrice,salePrice from tbl_book where 1=1"+generateWhere(qm);
            PreparedStatement psst = conn.prepareStatement(sql);
            
            setValues(psst,qm);
            
            ResultSet rs = psst.executeQuery();
            
            while(rs.next()){
                int index=0;
                
                BookModel book = new BookModel();
                book.setBookId(rs.getInt("bookId"));
                book.setBookName(rs.getString("bookName"));
                book.setInPrice(rs.getInt("inPrice"));
                book.setSalePrice(rs.getInt("salePrice"));
                
                booklist.add(book);
                
            }
            psst.close();
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
        return booklist;
    }

    private void setValues(PreparedStatement psst, BookQueryModel qm) throws SQLException {
        
        int index = 1;
        if(qm.getStartid()>0){
            psst.setInt(index++, qm.getStartid());
        }
        if(qm.getEndid()>0){
            psst.setInt(index++, qm.getEndid());
        }
        if(qm.getBookName().trim().length()>0){
            psst.setString(index++, "%"+qm.getBookName()+"%");
        }
        
    }

    private String generateWhere(BookQueryModel qm) {
        StringBuilder s = new StringBuilder();
        
        if(qm.getStartid()>0){
            s.append(" and bookId>=?"); 
        }
        if(qm.getEndid()>0){
            s.append(" and bookId<=?");
        }
        if(qm.getBookName().trim().length()>0 && qm.getBookName() != null){
            s.append(" and bookName  like ?");
        }
        return s.toString();
    }

}