《书城项目V1.0》————————通过Java语言,连接数据库,对数据库中的表进行CRUD(增删改查)

最新推荐文章于 2022-07-07 16:21:06 发布

cmj_971218

最新推荐文章于 2022-07-07 16:21:06 发布

阅读量222

点赞数

本文链接：https://blog.csdn.net/zx_666888/article/details/103303059

版权

知识点：DAO模式，c3p0连接池

准备工作：需要在项目中导入数据库驱动的jar包，c3p0的jar包，配置文件

C3p0的工具类

package com.offcn.utils;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Utils {

	private static DataSource ds = new ComboPooledDataSource();
	
	//获取数据源的方法
	public static DataSource getDataSource(){
		return ds;
	}
	
	//获取连接的方法
	public static Connection getConnection(){
		Connection conn=null;
		try {
			conn=ds.getConnection();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
}

2.c3p0-config.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<default-config>
		<!-- 必须项 -->
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="gdbcUrl">jdbc:mysql:///db1128</property>
		<property name="user">root</property>
		<property name="password">1234</property>
		
		<!-- 连接池参数 -->
		<property name="initialPoolSize">5</property>
		<property name="maxPoolSize">10</property>
		<property name="checkoutTimeout">2000</property>
		<property name="maxIdleTime">1000</property>
	</default-config>	
	
	<named-config name="offcn">
	<!-- 必须项 -->
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="gdbcUrl">jdbc:mysql:///day1127</property>
		<property name="user">root</property>
		<property name="password">1234</property>
		
		<!-- 连接池参数 -->
		<property name="initialPoolSize">5</property>
		<property name="maxPoolSize">10</property>
		<property name="checkoutTimeout">2000</property>
		<property name="maxIdleTime">1000</property>
	</named-config>
</c3p0-config>

3.Date类型和String类型转换工具类

package com.offcn.utils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class DateUtils {

	//---------日期转字符串
	public static String DateToString(Date date){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		return sdf.format(date);
	}
	
	//---------字符串转日期
	public static Date StringToDate(String str){
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = null;
		try {
			date = sdf.parse(str);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return date;
	}
}

1.根据需求在数据库中建表

2.根据数据库中的表写一个实体类

package com.offcn.bean;

import java.util.Date;

public class Book {

	private int id;
	private String booknum;
	private String bookname;
	private String bookauthor;
	private String bookpublish;
	private Date bookdate;
	private double bookprice;
	public Book(){}
	public Book(int id, String booknum, String bookname, String bookauthor, String bookpublish, Date 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 getBookauthor() {
		return bookauthor;
	}
	public void setBookauthor(String bookauthor) {
		this.bookauthor = bookauthor;
	}
	public String getBookpublish() {
		return bookpublish;
	}
	public void setBookpublish(String bookpublish) {
		this.bookpublish = bookpublish;
	}
	public Date getBookdate() {
		return bookdate;
	}
	public void setBookdate(Date 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 + "]";
	}
	
}

3.定义一个BookDao接口，定义CRUD的接口

package com.offcn.dao;

import java.util.List;

import com.offcn.bean.Book;

public interface BookDao {
	
	//-------查找全部----------
	public List findAllBook();
	
	//-------增加-----------
	public int InsertBook(Book book);
	
	//--------查找Id-------
	public Book findBookById(int id);
	
	//--------更新--------------
	public int updateBook(Book book);
	
	//----------删除-----------
	public int deleteById(int id);
}

4.定义BookDao的实现类 BookDaoImpl

package com.offcn.dao.impl;

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

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

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

public class BookDaoImpl implements BookDao {

	// --------------------查找----------------------
	@Override
	public List findAllBook() {
		List<Book> list = new ArrayList<>();

		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, new Object[] { book.getBooknum(), book.getBookname(), book.getBookauthor(),
					book.getBookpublish(), book.getBookdate(), book.getBookprice() });
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	// ---------根据id查找书籍是否存在----------------
	@Override
	public Book findBookById(int id) {
		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 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, new Object[] { book.getBooknum(), book.getBookname(), book.getBookauthor(),
					book.getBookpublish(), book.getBookdate(), book.getBookprice(),book.getId() });
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return result;
	}

	//-----------------删除-----------------------
	@Override
	public int deleteById(int id) {
		int result = 0;
		QueryRunner qr = new QueryRunner(C3P0Utils.getDataSource());
		String sql = "delete from book where id=?";
		try {
			result = qr.update(sql,id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

}

5.定义主程序（测试类）

package com.offcn.Test;

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

import com.offcn.bean.Book;
import com.offcn.dao.BookDao;
import com.offcn.dao.impl.BookDaoImpl;
import com.offcn.utils.DateUtils;

public class TestBook {

	static BookDao dao = new BookDaoImpl();

	static Scanner sc = new Scanner(System.in);

	public static void main(String[] args) {

		System.out.println("*********************欢迎进入图书系统*********************");

		while (true) {
			System.out.println("[A]查询所有书籍   [B]增加书籍   [C]修改书籍   [D]删除书籍   [E]退出");

			System.out.println("请选择您要进行的操作:");
			String str = sc.nextLine();// 用户选择

			if ("a".equalsIgnoreCase(str)) {// 查询全部
				showBook();
			} else if ("b".equalsIgnoreCase(str)) {// 增加
				addBook();
				showBook();
			} else if ("c".equalsIgnoreCase(str)) {// 修改
				preUpdateBook();
			} else if ("d".equalsIgnoreCase(str)) {// 删除
				preDeleteBook();
			} else if ("e".equalsIgnoreCase(str)) {// 退出
				System.out.println("已退出图书系统");
				System.exit(0);
			} else {
				System.out.println("您的输入格式不正确,请重新输入.");
			}
		}
	}

	// ------------------业务逻辑---------CRUD---------Dao------------

	// 定义方法展示查询结果
	public static void showBook() {
		List<Book> list = dao.findAllBook();
		for (Book book : list) {
			System.out.println(book);
		}
	}

	// 添加 输入书籍信息的方法
	public static void addBook() {
		Book book = new Book();

		System.out.println("请输入书籍编号:");
		String booknum = sc.nextLine();

		System.out.println("请输入书名:");
		String bookname = sc.nextLine();

		System.out.println("请输入作者:");
		String bookauthor = sc.nextLine();

		System.out.println("请输入出版社:");
		String bookpublish = sc.nextLine();

		System.out.println("请输入出版日期:");
		String strDate = sc.nextLine();
		Date bookdate = DateUtils.StringToDate(strDate);

		System.out.println("请输入价格:");
		String StrPrice = sc.nextLine();
		double bookprice = Double.parseDouble(StrPrice);

		book.setBooknum(booknum);
		book.setBookname(bookname);
		book.setBookauthor(bookauthor);
		book.setBookpublish(bookpublish);
		book.setBookdate(bookdate);
		book.setBookprice(bookprice);

		int result = dao.InsertBook(book);
		if (result > 0) {
			System.out.println("添加成功!");
		} else {
			System.out.println("添加失败!");
		}
	}

	// 输入修改编号,先查找是否存在该编号
	public static void preUpdateBook() {
		while(true){
		System.out.println("请输入您要修改的书籍ID:");
		String id = sc.nextLine();
		/*
		 * 如果输入的id不是数字类型的字符串,那么paseInt(id)将会出现类型转换异常 解决方式: 1.正则
		 * 2.将paseInt提取出来抛异常 3.直接将id定义成int类型
		 */
		try {
			int num = Integer.parseInt(id);
			Book book = dao.findBookById(num);
			if (book == null) {// 没有查询到该书籍
				System.out.println("请输入正确的书籍ID");
				//return;// 结束该方法
				//break;
			} else {// 更新书籍-----调用更新书籍方法
				updateBook(book);
				break;
			}
		} catch (Exception e) {
			System.out.println("您输入的ID格式不正确!");
		}
		}
	}

	// 更新书籍
	public static void updateBook(Book book) {
		while(true){
		System.out.println("请选择要更新的字段:");
		System.out.println("[A]编号   [B]书名  [C]作者  [D]出版社  [E]出版日期  [F]价格  [G]退出");

		String str = sc.nextLine();
		
		if ("a".equalsIgnoreCase(str)) {
			System.out.println("请输入编号:");
			String booknum = sc.nextLine();
			book.setBooknum(booknum);
		} else if ("b".equalsIgnoreCase(str)) {
			System.out.println("请输入书名:");
			String bookname = sc.nextLine();
			book.setBookname(bookname);
		} else if ("c".equalsIgnoreCase(str)) {
			System.out.println("请输入作者:");
			String bookauthor = sc.nextLine();
			book.setBookauthor(bookauthor);
		} else if ("d".equalsIgnoreCase(str)) {
			System.out.println("请输入出版社:");
			String bookpublish = sc.nextLine();
			book.setBookpublish(bookpublish);
		} else if ("e".equalsIgnoreCase(str)) {
			System.out.println("请输入出版日期:");
			String bookdate = sc.nextLine();
			book.setBookdate(DateUtils.StringToDate(bookdate));
		} else if ("f".equalsIgnoreCase(str)) {
			System.out.println("请输入价格:");
			String bookprice = sc.nextLine();
			book.setBookprice(Double.parseDouble(bookprice));
		} else if ("g".equalsIgnoreCase(str)) {
			System.out.println("已退出修改模式!");
			return;
		} else {
			System.out.println("您的输入格式不正确!");
			//return;
			break;
		}
		}

		int result = dao.updateBook(book);
		if (result > 0) {
			System.out.println("更新成功!");
		} else {
			System.out.println("更新失败!");
		}
	}
	
	//------------------删除判断--------------
	public static void preDeleteBook(){
		System.out.println("请输入您要删除的书籍ID:");
		String id = sc.nextLine();
		try {
			int num = Integer.parseInt(id);
			Book book = dao.findBookById(num);
			if (book == null) {// 没有查询到该书籍
				System.out.println("请输入正确的书籍ID");
				return;// 结束该方法
			} else {// 更新书籍-----调用更新书籍方法
				deleteBook(book.getId());
			}
		} catch (Exception e) {
			System.out.println("您输入的ID格式不正确!");
		}
	}

	//------------删除---------------------
	public static void deleteBook(int id){
		int result = dao.deleteById(id);
		if (result > 0) {
			System.out.println("删除成功!");
		} else {
			System.out.println("删除失败!");
		}
	}
}