JDBC操作数据库

一、BaseDao

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

public class BaseDao {

	private Connection con;
	private PreparedStatement pre;
	private ResultSet res;
	
	//连接数据库
	public void getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/booksys";
			con = DriverManager.getConnection(url,"root","root");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//关闭资源
	public void close() {
		if(con!=null)
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		if(pre!=null)
			try {
				pre.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		if(res!=null)
			try {
				res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	
	//更新--删除，添加，改
	public int executeUpdata(String sql,Object...objects) {
		this.getConnection();
		try {
			pre = con.prepareStatement(sql);
			if(objects!=null)
				for(int i = 1;i<=objects.length;i++) {
					pre.setObject(i,objects[i-1]);
				}
			return pre.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close();
		}
		return -1;
		
	}
	
	//查询
	public ResultSet executeQuery(String sql,Object...objects) {
		this.getConnection();
		try {
			pre = con.prepareStatement(sql);
			for(int i = 1;i<=objects.length;i++) {
				pre.setObject(i,objects[i-1]);
			}
			return res = pre.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
		
	}
	
}

二、BookDao

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.yuchao.entity.Book;
import com.yuchao.util.BaseDao;

public class BookDao  extends BaseDao{
	
	//查询全部书籍
	public List<Book> getAll() { 
		List<Book> list = new ArrayList<Book>();
		ResultSet res = this.executeQuery("select * from book");

		if(res!=null)
			try {
				while(res.next())
					list.add(new Book(res.getInt(1),res.getString(2),res.getDouble(3),res.getString(4),res.getDate(5)));
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				this.close();
			}
		return list;
	}
	
	//添加书籍
	public int add(Book book) {
		String sql = "insert into book (name,price,author,pubDate) values (?,?,?,?)";
		return this.executeUpdata(sql,book.getName(),book.getPrice(),book.getAuthor(),new SimpleDateFormat("yyyy-MM-dd").format(book.getPubDate()));
	}
	
	//查询书籍
	public Book query(int id) {
		Book book = null;
		ResultSet res = this.executeQuery("select * from book where id = ?", id);
		
		if(res!=null)
			try {
				while(res.next())
					book = new Book(res.getInt(1),res.getString(2),res.getDouble(3),res.getString(4),res.getDate(5));
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				this.close();
			}
		return book;
	}
	
	//更新书籍
	public int update(Book book) {
		String sql = "Update book set name=?,price=?,author=?,pubDate=? where id=?";
		return this.executeUpdata(sql, book.getName(),book.getPrice(),book.getAuthor(),new SimpleDateFormat("yyyy-MM-dd").format(book.getPubDate()),book.getId());
	}
	
	//删除书籍
	public int delete(int id) {
		String sql = "delete from book where id=?";
		return this.executeUpdata(sql, id);
	}
}