javaEE之图书列表(运用数据库连接池)

javaEE之图书列表(运用数据库连接池) 

1.使用自己的话表述MVC。
2.掌握JDBC的基本操作。
  对book表中的数据进行增删改查；
  book表：id int,bookName varchar,price int,description varchar
  Book类：id int,bookName String,price int,description String
  提示：创建数据库连接工具类DBUtil类。

自己理解的MVC：当发生了某些事情（control）的时候，执行某些代码（model），跳转到某些页面（view）

例子：当我想修改密码，就进入修改密码页面(view)，修改密码时先查询数据库近三个月的密码，确定不重复后，再执行数据库更新代码(control)，其中数据库增删改查就是(model)。

package com.mpl.dao;

import java.util.ArrayList;

import com.mpl.entity.BookBean;

/**
 * dao层
 * @author 17软工莫培文
 *
 */
public interface BookDao {
	/**
	 * 获得指定 id 的内容 精确查询
	 * @param id book的id
	 * @return 返回 BookBean
	 */
	public BookBean selectBook(int id);
	
	/**
	 * 获得指定 bookName 的内容 模糊查询
	 * @param bookName 书名
	 * @return 返回 BookBean
	 */
	public BookBean selectBook(String bookName);
	
	/**
	 * 删除指定 id 的内容
	 * @param id book的id
	 */
	public void delBook(int id);
	
	/**
	 * 添加指定的内容到数据库
	 * @param BookBean 添加的内容
	 */
	public void addBook(BookBean bookBean);
	
	/**
	 * 修改指定 id 的内容
	 * @param id book的id
	 * @param bookBean 修改的内容
	 */
	public void updateBook(BookBean bookBean);
	
	/**
	 * 获得book列表
	 * @return book列表
	 */
	public ArrayList<BookBean> selectBooks();
}

package com.mpl.dao.impl;

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

import com.mpl.dao.BookDao;
import com.mpl.entity.BookBean;
import com.mpl.util.DBUtil;

/**
 * dao层实现类
 * @author 17软工莫培文
 *
 */
public class BookDaoImpl implements BookDao {

	@Override
	public BookBean selectBook(int id) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt;
		BookBean bookBean = new BookBean();
		try {
			pstmt = conn.prepareStatement("select * from book where id = ?");
			pstmt.setInt(1, id);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				bookBean.setId(rs.getInt("id"));
				bookBean.setBookName(rs.getString("bookName"));
				bookBean.setPrice(rs.getInt("price"));
				bookBean.setDescription(rs.getString("description"));

				//System.out.println(bookBean);
				return bookBean;
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return null;
	}

	@Override
	public void delBook(int id) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement("delete from book where id=?");
			pstmt.setInt(1, id);
			int rs = pstmt.executeUpdate();
			//System.out.println(rs);
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

	@Override
	public void addBook(BookBean bookBean) {
		Connection conn = DBUtil.getConnection();// 创建连接
		PreparedStatement pstmt;// 创建声明sql入口
		try {
			pstmt = conn.prepareStatement("insert into book(id,bookName,price,description) values(?,?,?,?)");
			pstmt.setInt(1, bookBean.getId());
			pstmt.setString(2, bookBean.getBookName());
			pstmt.setInt(3, bookBean.getPrice());
			pstmt.setString(4, bookBean.getDescription());
			int rs = pstmt.executeUpdate();// 提交sql语句并得到提交结果
			//System.out.println(rs);
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

	@Override
	public void updateBook(BookBean bookBean) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt;
		try {
			pstmt = conn.prepareStatement("update book set bookName=?,price=?,description=? where id=?");
			pstmt.setString(1, bookBean.getBookName());
			pstmt.setInt(2, bookBean.getPrice());
			pstmt.setString(3, bookBean.getDescription());
			pstmt.setInt(4, bookBean.getId());
			int rs = pstmt.executeUpdate();
			//System.out.println(rs);
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

	@Override
	public ArrayList<BookBean> selectBooks() {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt;
		BookBean bookBean = new BookBean();
		ArrayList<BookBean> books  = new ArrayList<BookBean>();
		try {
			pstmt = conn.prepareStatement("select * from book");
			ResultSet rs = pstmt.executeQuery();
			
			while (rs.next()) {
				bookBean = new BookBean();
				bookBean.setId(rs.getInt("id"));
				bookBean.setBookName(rs.getString("bookName"));
				bookBean.setPrice(rs.getInt("price"));
				bookBean.setDescription(rs.getString("description"));
				
				//System.out.println(bookBean);
				
				books.add(bookBean);
				
			}
			return books;
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return null;
	}

	@Override
	public BookBean selectBook(String bookName) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt;
		BookBean bookBean = new BookBean();
		try {
			pstmt = conn.prepareStatement("select * from book where bookName like ?");
			pstmt.setString(1, "%"+bookName+"%");
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				bookBean.setId(rs.getInt("id"));
				bookBean.setBookName(rs.getString("bookName"));
				bookBean.setPrice(rs.getInt("price"));
				bookBean.setDescription(rs.getString("description"));

				//System.out.println(bookBean);
				return bookBean;
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return null;
	}

}

package com.mpl.entity;

/**
 * 实体层
 * @author 17软工莫培文
 *
 */
public class BookBean {
	private int id;
	private String bookName;
	private int price;
	private String description;

	public BookBean() {
		super();
	}

	public BookBean(int id, String bookName, int price, String description) {
		super();
		this.id = id;
		this.bookName = bookName;
		this.price = price;
		this.description = description;
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getBookName() {
		return bookName;
	}

	public void setBookName(String bookName) {
		this.bookName = bookName;
	}

	public int getPrice() {
		return price;
	}

	public void setPrice(int price) {
		this.price = price;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	@Override
	public String toString() {
		return "BookBean [id=" + id + ", bookName=" + bookName + ", price=" + price + ", description=" + description
				+ "]";
	}

}

package com.mpl.util;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

/**
 * 数据库操作池工具类
 * 
 * @author mopeiwen
 * @version 2019年10月10日 下午2:46:09
 */
public class DBUtil{
	// 声明一个DataSource对象
	private static DataSource ds = null;

	// 类加载只执行一次
	static {
		try {
			// 加载配置文件并读取
			Properties p = new Properties();
			FileInputStream in = new FileInputStream("resource/dbcp.properties");
			p.load(in);
			ds = BasicDataSourceFactory.createDataSource(p);
		} catch (Exception e) {

			e.printStackTrace();
		}
	}

	/*
	 * 获取连接对象方法
	 */
	public static Connection getConnection() {

		Connection conn;
		try {
			conn = ds.getConnection();
			return conn;
		} catch (SQLException e) {

			e.printStackTrace();
			return null;
		}

	}

	public static void close(Connection conn) {
		try {
			conn.close();

		} catch (SQLException e) {

			e.printStackTrace();
		}
	}

	public static void close(PreparedStatement ps) {
		try {
			ps.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

	public static void close(ResultSet rs) {
		try {
			rs.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

}

package com.mpl.main;

import java.util.ArrayList;
import java.util.Iterator;

import com.mpl.dao.impl.BookDaoImpl;
import com.mpl.entity.BookBean;

/**
 * 对book表中的数据进行增删改查
 * 
 * @author mopeiwen
 * @version 2019年10月10日 下午3:40:54
 */
public class Main {

	static BookDaoImpl bookDaoImpl = new BookDaoImpl();
	static BookBean bookBean = new BookBean();
	
	public static void main(String[] args) {
		// 增加书本
		bookBean = new BookBean(3, "数据库命令集合", 99, "包括了sql和mysql的集合");
		bookDaoImpl.addBook(bookBean);
		printlnOf();// 输出book集合
		
		//修改书本信息
		bookBean = new BookBean(3, "数据库命令集合", 120, "包括了sql、mysql和甲骨文的集合");
		bookDaoImpl.updateBook(bookBean);
		printlnOf();// 输出book集合
		
		//删除书本
		bookDaoImpl.delBook(3);
		printlnOf();// 输出book集合
		
		//按序号查询书本信息
		System.out.println(bookDaoImpl.selectBook(2));
		
		//按书名查询书本信息
		System.out.println(bookDaoImpl.selectBook("书"));

	}

	/**
	 * 输出book集合
	 */
	public static void printlnOf() {
		ArrayList<BookBean> books = new ArrayList<BookBean>();
		books = bookDaoImpl.selectBooks();

		for (Iterator<BookBean> iterator = books.iterator(); iterator.hasNext();) {
			BookBean bookBean = (BookBean) iterator.next();
			System.out.println(bookBean);
		}
		System.out.println("#################################");
	}
}