DAO模式

DAO
1)DAO接口：把对数据库的所有操作定义成抽象方法，可以提供多种实现。
2)DAO实现类：针对不同数据库给出DAO接口定义方法的具体实现。
3)实体类：用于存放于传输对象数据。
4)数据库连接和关闭工具类：避免了数据库连接和关闭代码的重复使用，方便修改。

import java.util.Date;

public class Book {
	private int bid;
	private String bName;
	private String author;
	private String pubComp;
	private int inCount;
	private double price;
	private Date pubDate;
	public Book(Date pubDate) {
		super();
		this.pubDate = pubDate;
	}
	public Date getPubDate() {
		return pubDate;
	}
	public void setPubDate(Date pubDate) {
		this.pubDate = pubDate;
	}
	public Book(){}
	public Book(int bid, String bName, String author, String pubComp,
			int inCount, double price) {
		super();
		this.bid = bid;
		this.bName = bName;
		this.author = author;
		this.pubComp = pubComp;
		this.inCount = inCount;
		this.price = price;
	}
	public int getBid() {
		return bid;
	}
	public void setBid(int bid) {
		this.bid = bid;
	}
	public String getbName() {
		return bName;
	}
	public void setbName(String bName) {
		this.bName = bName;
	}
	public String getAuthor() {
		return author;
	}
	public void setAuthor(String author) {
		this.author = author;
	}
	public String getPubComp() {
		return pubComp;
	}
	public void setPubComp(String pubComp) {
		this.pubComp = pubComp;
	}
	@Override
	public String toString() {
		return "Book [bid=" + bid + ", bName=" + bName + ", author=" + author
				+ ", pubComp=" + pubComp + ", inCount=" + inCount + ", price="
				+ price + ", pubDate=" + pubDate + "]";
	}
	public int getInCount() {
		return inCount;
	}
	public void setInCount(int inCount) {
		this.inCount = inCount;
	}
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
	
}

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

import com.bdqn.dao.BookDao;
import com.bdqn.entity.Book;

public class BookDaoImpl extends BaseDao implements BookDao {

	@Override
	public int addBook(Book book) {
		// TODO Auto-generated method stub
		String sql="insert into book values(?,?,?,?,?,?,?)";
		return super.executeUpdate(sql, book.getBid(),book.getbName(),book.getAuthor(),book.getPubComp(),book.getPubDate(),book.getInCount(),book.getPrice());
		
	}
	@Override
	public int delBoook(int id) {
		// TODO Auto-generated method stub
		String sql="delete from book where bid=?";
		return super.executeUpdate(sql, id);
	}

	@Override
	public int updateBook(Book book) {
		// TODO Auto-generated method stub
		String sql="update book set bName=?,author=?,pubcomp=?,pubDate=?,InCount=?,price=?";
		return super.executeUpdate(sql, book.getbName(),book.getAuthor(),book.getPubComp(),book.getPubDate(),book.getInCount(),book.getPrice(),book.getBid());
	}

	@Override
	public List<Book> getAll() {
		// TODO Auto-generated method stub
		List<Book> list=new ArrayList<Book>();
		String sql="select*from book";
		Connection conn=super.getConnection();
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try {
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			Book book;
			while(rs.next()){
				book=new Book();
				book.setBid(rs.getInt("bid"));
				book.setbName(rs.getString("bname"));
				book.setAuthor(rs.getString("author"));
				book.setPubComp(rs.getString("pubcomp"));
				book.setPubDate(rs.getTimestamp("pubDate"));
				book.setInCount(rs.getInt("inCount"));
				book.setPrice(rs.getDouble("price"));
				list.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			super.cliseAll(conn,pstmt, rs);
		}
		return list;
	}

}

import java.util.List;

import com.bdqn.entity.Book;

public interface BookDao {
	//增加
	public int addBook(Book book);
	//删除
	public int delBoook(int id);
	//更新
	public int updateBook(Book book);
	//查询
	public List<Book> getAll();
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;



public class BaseDao {
	private String driver="com.mysql.jdbc.Driver";
	private String url="jdbc:mysql://localhost:3306/mybook?useUnicode=true&characterEncoding=utf8";
	//连接
	public Connection getConnection(){
		try {
			Class.forName(driver);
			Connection conn=DriverManager.getConnection(url,"root","123456");
			return conn;
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	//关闭
	public void cliseAll(Connection conn,Statement stem,ResultSet rs){
		try {
			if (rs!=null) {
				rs.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			try {
				if (conn!=null) {
				conn.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				if (stem!=null) {
					stem.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	//执行增删改
	public int executeUpdate(String sql,Object...objects){
		Connection conn=this.getConnection();
		PreparedStatement pstmt=null;
		//执行sql
		 try {
			pstmt=conn.prepareStatement(sql);
			for (int i = 0; i < objects.length; i++) {
				pstmt.setObject(i+1, objects[i]);
			}
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally{
			this.cliseAll(conn, pstmt, null);
		}
		 //如果前面出现异常则返回0
		return 0;
	}
	}

import static org.junit.Assert.*;

import java.util.List;

import org.junit.Test;

import com.bdqn.dao.BookDao;
import com.bdqn.dao.impl.BookDaoImpl;
import com.bdqn.entity.Book;

public class TestBook {

	@Test
	public void testQuery() {
		BookDao bookDao=new BookDaoImpl();
		List<Book> list=bookDao.getAll();
		for (Book book:list) {
			System.out.println(book);
		}
	}

}