图书管理（Dao模式）Java---MySql

使用DAO模式来对图书进行增删盖查：

表单：

                        

文件分布：

                                            

实体类

package com.yht.bean;

public class Book {
	
	
	private int id;
	private String booknum;
	private String bookname;
	private String bookauthor;
	private String bookpublish;
	private String bookdate;
	private double bookprice;
	public Book() {
		super();
	}
	
	public String getBookauthor() {
		return bookauthor;
	}

	public void setBookauthor(String bookauthor) {
		this.bookauthor = bookauthor;
	}

	public Book(int id, String booknum, String bookname, String bookauthor, String bookpublish, String bookdate,
			double bookprice) {
		super();
		this.id = id;
		this.booknum = booknum;
		this.bookname = bookname;
		this.bookauthor = bookauthor;
		this.bookpublish = bookpublish;
		this.bookdate = bookdate;
		this.bookprice = bookprice;
	}

	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getBooknum() {
		return booknum;
	}
	public void setBooknum(String booknum) {
		this.booknum = booknum;
	}
	public String getBookname() {
		return bookname;
	}
	public void setBookname(String bookname) {
		this.bookname = bookname;
	}
	public String getBookpublish() {
		return bookpublish;
	}
	public void setBookpublish(String bookpublish) {
		this.bookpublish = bookpublish;
	}
	public String getBookdate() {
		return bookdate;
	}
	public void setBookdate(String bookdate) {
		this.bookdate = bookdate;
	}
	public double getBookprice() {
		return bookprice;
	}
	public void setBookprice(double bookprice) {
		this.bookprice = bookprice;
	}

	@Override
	public String toString() {
		return "Book [id=" + id + ", booknum=" + booknum + ", bookname=" + bookname + ", bookauthor=" + bookauthor
				+ ", bookpublish=" + bookpublish + ", bookdate=" + bookdate + ", bookprice=" + bookprice + "]";
	}
	
	
	
}

工具类

package com.yht.utils;

import java.util.List;
import java.util.Scanner;

import com.yht.bean.Book;
import com.yht.dao.BookDao;
import com.yht.dao.impl.BookDaoImpl;

public class BookUtil {
	
	//打印数据库中所有信息
	public static void showBook(){
		BookDao bd = new BookDaoImpl();
		List<Book> list = bd.findAllBook();
		for(Book book:list){
			System.out.println(book);
		}
	}
	
	//为插入提供输入信息
	public static Book insertString(){
		Book book = new Book();
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入你要插入的图书编号");
		String booknum = sc.nextLine();
		book.setBooknum(booknum);
		System.out.println("请输入你要插入的图书名称");
		String bookname = sc.nextLine();
		book.setBookname(bookname);
		System.out.println("请输入你要插入的图书作者");
		String bookauthor = sc.nextLine();
		book.setBookauthor(bookauthor);
		System.out.println("请输入你要插入的图书出版社");
		String bookpublish = sc.nextLine();
		book.setBookpublish(bookpublish);
		System.out.println("请输入你要插入的图书出版时间");
		String bookdate = sc.nextLine();
		book.setBookdate(bookdate);
		System.out.println("请输入你要插入的图书价格");
		String bookprice = sc.nextLine();
		book.setBookprice(Double.parseDouble(bookprice));
		return book;
	}
	
	//为更新提供输入信息
	public static Book updateString(Book book){
		Scanner sc = new Scanner(System.in);
		
			System.out.println("[1]修改编号 [2]修改名称 [3]修改作者 [4]修改出版社 [5]修改出版时间 [6]修改价格 ");
			String choice = sc.nextLine();
			if("1".equals(choice)){
				System.out.println("请输入你要修改的图书编号");
				String booknum = sc.nextLine();
				book.setBooknum(booknum);
			}else if("2".equals(choice)){
				System.out.println("请输入你要修改的图书名称");
				String bookname = sc.nextLine();
				book.setBookname(bookname);
			}else if("3".equals(choice)){
				System.out.println("请输入你要修改的图书作者");
				String bookauthor = sc.nextLine();
				book.setBookauthor(bookauthor);
			}else if("4".equals(choice)){
				System.out.println("请输入你要修改的图书出版社");
				String bookpublish = sc.nextLine();
				book.setBookpublish(bookpublish);
			}else if("5".equals(choice)){
				System.out.println("请输入你要修改的图书出版时间");
				String bookdate = sc.nextLine();
				book.setBookdate(bookdate);
			}else if("6".equals(choice)){
				System.out.println("请输入你要修改的图书价格");
				String bookprice = sc.nextLine();
				book.setBookprice(Double.parseDouble(bookprice));
			}else{
				System.out.println("输入错误请重新输入:");
			}
		

		return book;
	}
	
	//判断是否成功
	public static void successOrFailure(int result){
		if(result!=0){
			System.out.println("成功");
		}else{
			System.out.println("失败");
		}
	}
}

接口类

package com.yht.dao;

import java.util.List;

import com.yht.bean.Book;

public interface BookDao {
	public List<Book> findAllBook();
	
	public int insertBook(Book book);
	
	public Book preUpdateBook();
	
	public int updateBook(Book book);
	
	public int deleteBook(int id);
}

实现类

package com.yht.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.yht.bean.Book;
import com.yht.dao.BookDao;
import com.yht.utils.C3P0Utils;

public class BookDaoImpl implements BookDao{

	@Override
	public List<Book> findAllBook() {
		List<Book> list = new ArrayList<Book>();
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		
		String sql = "select * from book";
		try {
			list = qr.query(sql,new BeanListHandler<>(Book.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int insertBook(Book book) {
		int result = 0;
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		
		String sql = "insert into book values (null,?,?,?,?,?,?)";
		try {
			result = qr.update(sql,book.getBooknum(),book.getBookname(),book.getBookauthor(),book.getBookpublish(),book.getBookdate(),book.getBookprice());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int updateBook(Book book) {
		int result = 0;
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		
		String sql = "update book set booknum=?,bookname=?,bookauthor=?,bookpublish=?,bookdate=?,bookprice=? where id =?";
		try {
			result = qr.update(sql,book.getBooknum(),book.getBookname(),book.getBookauthor(),book.getBookpublish(),book.getBookdate(),book.getBookprice(),book.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return result;
	}

	@Override
	public Book preUpdateBook() {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入你要修改的id");
		int id = Integer.parseInt(sc.nextLine());
		int result = 0;
		Book book = null;
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		
		String sql = "select * from book where id = ?";
		try {
			book = qr.query(sql, new BeanHandler<>(Book.class),id);
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return book;
	}

	@Override
	public int deleteBook(int id) {
		
		int result = 0;
		Book book = null;
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "delete from book where id = ?";
		try {
			result = qr.update(sql,id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
}

测试主类

package com.yht.main;

import java.util.List;
import java.util.Scanner;

import com.yht.bean.Book;
import com.yht.dao.BookDao;
import com.yht.dao.impl.BookDaoImpl;
import com.yht.utils.BookUtil;

public class Main {
	
	public static BookDao bd = new BookDaoImpl();
	public static void main(String[] args) {
		
		
		while(true){
			Scanner sc = new Scanner(System.in);
			System.out.println("[A]查询所有书籍	[B]增加书籍	[C]修改书籍	[D]删除书籍	[E]退出");
			System.out.println("请输入你的操作:");
			String str = sc.nextLine();
			
			if("A".equalsIgnoreCase(str)){
				
				BookUtil.showBook();
				
			}else if("B".equalsIgnoreCase(str)){
				
				BookUtil.successOrFailure(bd.insertBook(BookUtil.insertString()));
				
			}else if("C".equalsIgnoreCase(str)){
				
				BookDao bd = new BookDaoImpl();
				Book book = bd.preUpdateBook();
				int result = 0;
				if(book==null){
					System.out.println("该书不存在");
					continue;
				}else{
					result = bd.updateBook(BookUtil.updateString(book));
				}
				BookUtil.successOrFailure(result);
				
			}else if("D".equalsIgnoreCase(str)){
				
				System.out.println("请输入你要删除的id");
				int id = Integer.parseInt(sc.nextLine());
				int result = bd.deleteBook(id);
				BookUtil.successOrFailure(result);
				
			}else if("E".equalsIgnoreCase(str)){
				
				System.out.println("欢迎下次使用");
				break;
				
			}else{
				System.out.println("输入有误，请重新输入！");
				continue;
			}
		}
	}
}