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

packagejxc.book.dao.impl;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importjxc.book.dao.dao.BookDao;importjxc.book.vo.BookModel;importjxc.book.vo.BookQueryModel;public class BookImpl implementsBookDao {static{try{

//装载数据库的驱动程序

Class.forName("oracle.jdbc.driver.OracleDriver");

}catch(ClassNotFoundException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}

@OverridepublicBookModel 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();

}

}returnbook;

}private intnextid() {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();

}

}returnnextid;

}

@OverridepublicBookModel 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();

}

}returnbook;

}

@OverridepublicBookModel 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();

}

}returnbook;

}

@OverridepublicBookModel query(BookModel book) {//TODO Auto-generated method stub

return null;

}

@Overridepublic ListgetAll() {

List booklist = new ArrayList();

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= newBookModel();

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();

}

}returnbooklist;

}

@Overridepublic ListgetByCondition(BookQueryModel qm) {

List booklist = new ArrayList();

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= newBookModel();

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();

}

}returnbooklist;

}private void setValues(PreparedStatement psst, BookQueryModel qm) throwsSQLException {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()+"%");

}

}privateString generateWhere(BookQueryModel qm) {

StringBuilder s= newStringBuilder();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 ?");

}returns.toString();

}

}