简单学习 JDBC的DAO模式

DAO是一个操作数据库的设计模式(DAO是一种访问数据库的设计模式，大概的思路就是将对一个数据库（和一个数据表）的操作封装到一个类中，由其他的类来调用这个类的方法完成对数据库的操作，这样可以简化负责业务处理的类的复杂性和耦合性)

一个Dao模式应该包含以下几点：

1. VO:Value Object  创建一个类，该类中只有属性值，并且属性值对应数据表中的所有字段
2. Dao接口：Data Access Object  这是一个接口，接口中有对数据库进行操作的方法
3. dao接口的实现类：具体封装了对数据库的操作方法
4. （dao工厂）

VO对象

public class Book {private Integer bookid;private String bookName;private Integer price;public Integer getBookid() {return bookid;}public void setBookid(Integer bookid) {this.bookid = bookid;}public String getBookName() {return bookName;}public void setBookName(String bookName) {this.bookName = bookName;}public Integer getPrice() {return price;}public void setPrice(Integer price) {this.price = price;}public Book(String bookName, Integer price) {super();this.bookName = bookName;this.price = price;}@Overridepublic String toString() {return "Book [  bookid=  " + bookid + "\t bookName=" + bookName + "\t price=" + price + "  ]";}public Book() {super();}}

baseDao类（因为有很多对数据库操作相同的代码块，所以提取出来，便于以后的修改）

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;/** * 基础的Dao类 * @author dingshuangen * */public class BaseDao {private static final String URL="jdbc:mysql://localhost:3306/jdbc";private static final String USER="root";private static final String PASSWORD="0325";/** * 封装获得连接的方法 * @return */public  Connection getConnection() {Connection conn=null;try {//加载驱动Class.forName("com.mysql.jdbc.Driver");//获得连接conn=DriverManager.getConnection(URL, USER, PASSWORD);} catch (Exception e) {e.printStackTrace();}return conn;}/** * 封装对继承AutoCloseable接口类的关闭操作 * @param object */public void close(AutoCloseable object) {if(object!=null) {try {object.close();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}}/** * 执行sql语句，可变参数为sql语句中需要的参数 * @param sql * @param objs */public void executeSql(String sql,Object...objs) {Connection conn=this.getConnection();PreparedStatement ps=null;try {ps=conn.prepareStatement(sql);//循环为每一个变量设置参数for(int i=0;i<objs.length;i++) {ps.setObject(i+1, objs[i]);}//执行sqlps.execute();System.out.println("执行   "+sql+"  成功");}catch(Exception e) {e.printStackTrace();}finally {this.close(ps);this.close(conn);}}}

dao接口

import java.util.List;import com.oracle.vo.Book;/** * BookDao的接口，具有查询插入删除更新的功能 * @author dingshuangen * */public interface BookDao {//插入书籍信息public void save(Book book);//更新书籍信息public void update(Book book);//按照bookid删除对应书籍public void delete(Integer bookid);//查询所有书籍信息public List<Book> getAllBook();//根据bookid查询对应的书籍信息public Book getByBookid(Integer bookid);//根据页数查询，每页显示number条记录public List<Book> getByPage(Integer page,Integer number); }

dao接口的具体实现类同时继承了baseDao类

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.util.ArrayList;import java.util.List;import com.oracle.vo.Book;public class BookDaoImpl extends BaseDao implements BookDao {//插入记录@Overridepublic void save(Book book) {this.executeSql("insert into tb_book values(null,?,?)", book.getBookName(),book.getPrice());}//更新记录@Overridepublic void update(Book book) {this.executeSql("update tb_book set bookname=?,price=? where bookid=?", book.getBookName(),book.getPrice(),book.getBookid());}//删除记录@Overridepublic void delete(Integer bookid) {this.executeSql("Delete from tb_book where bookid=?", bookid);}//查询所有记录@Overridepublic List<Book> getAllBook() {List<Book> list=new ArrayList<Book>();Connection conn=this.getConnection();PreparedStatement ps=null;ResultSet rs=null;try {//查询出所有的记录，按照bookid排序ps=conn.prepareStatement("select bookid,bookname,price from tb_book order by bookid");rs=ps.executeQuery();while(rs.next()) {Book b=new Book();//创建一个vo对象存储记录的信息b.setBookid(rs.getInt("bookid"));b.setBookName(rs.getString("bookname"));b.setPrice(rs.getInt("price"));list.add(b);//每条记录添加到list中}return list;}catch(Exception e) {e.printStackTrace();}finally {this.close(rs);this.close(ps);this.close(conn);}return list;}//根据id查询单条记录@Overridepublic Book getByBookid(Integer bookid) {Connection conn=this.getConnection();PreparedStatement ps=null;ResultSet rs=null;Book b=new Book();try {ps=conn.prepareStatement("select bookid,bookname,price from tb_book where bookid=?");ps.setInt(1, bookid);rs=ps.executeQuery();if(rs.next()) {b.setBookid(rs.getInt("bookid"));b.setBookName(rs.getString("bookname"));b.setPrice(rs.getInt("price"));}return b;}catch(Exception e) {e.printStackTrace();}finally {this.close(rs);this.close(ps);this.close(conn);}return b;}//分页查找@Overridepublic List<Book> getByPage(Integer page, Integer number) {List<Book> list=new ArrayList<Book>();Connection conn=this.getConnection();PreparedStatement ps=null;ResultSet rs=null;try {ps=conn.prepareStatement("select bookid,bookname,price from tb_book order by bookid limit ?,?");ps.setInt(1,(page-1)*number);ps.setInt(2, number);rs=ps.executeQuery();while(rs.next()) {Book b=new Book();b.setBookid(rs.getInt("bookid"));b.setBookName(rs.getString("bookname"));b.setPrice(rs.getInt("price"));list.add(b);}return list;}catch(Exception e) {e.printStackTrace();}finally {this.close(rs);this.close(ps);this.close(conn);}return list;}}