1.首先创建一个实列Book.
//书的属性
private int BookID;
private StringBookName;
private StringBookAuthor;
private double BookPrice;
2，创建工具包链接Mysql数据库
package util;
import java.sql.*;
public class Util {
private static final StringURL="jdbc:mysql://localhost:3306/bookdb";
private static final StringUSER="root";
private static final StringPASSWORD="123456";
//找到jdbc jar包
static {
try {
Class.forName("com.mysql.jdbc.Driver");
}catch ( ClassNotFoundException e){
e.printStackTrace();
}
}
//获取Connection
public static Connection getConn(){
Connection connection=null;
//Connection赋值
try {
connection= DriverManager.getConnection(URL,USER,PASSWORD);
}catch (SQLException e){
e.printStackTrace();
}
return connection;
}
//关闭
public static void Close(ResultSet rs,PreparedStatement ps,Connection conn){
try {
if (rs!=null){rs.close();}
if (ps!=null){ps.close();}
if (conn!=null){conn.close();}
}catch (SQLException e){
e.printStackTrace();
}
}
//测试
public static void main(String[] args) {
System.out.print(Util.getConn());
}
}
3.编写增删改查方法
package dao;
import bean.Book;
import jdk.nashorn.internal.ir.WhileNode;
import util.Util;
import javax.sound.midi.Soundbank;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Scanner;
public class CURD {
//系统输入
Scanner scanner=new Scanner(System.in);
//获取Connection
Connection connection= Util.getConn();
//添加图书
public int AddBook(){
//结果1是成功 0是失败
int rs=0;
//输入图书信息
System.out.println("请输入图书编号！");
int bookid=scanner.nextInt();
System.out.println("请输入图书名称！");
String bookname=scanner.next();
System.out.println("请输入图书作者！");
String bookauthor=scanner.next();
System.out.println("请输入图书价格！");
double bookprice=scanner.nextDouble();
//sql语句
String Sql="insert into book values(?,?,?,?)";
try {
PreparedStatement ps=connection.prepareStatement(Sql);
ps.setInt(1,bookid);
ps.setString(2,bookname);
ps.setString(3,bookauthor);
ps.setDouble(4,bookprice);
//运行并返回结果
rs=ps.executeUpdate();
if(rs>0){
System.out.println("添加成功！------");
}
}catch (Exception e){
e.printStackTrace();
}
return rs;
}
//查询所有图书
public ArrayList<Book> getAllBook(){
//Arraylist创建一个集合
ArrayList<Book> arrayList=new ArrayList<Book>();
//sql语句
String Sql="select * from book";
//结果集
ResultSet rs=null;
PreparedStatement ps=null;
try {
ps=connection.prepareStatement(Sql);
rs=ps.executeQuery();
//遍历结果集到book 在把book添加到ArrayList集合中
while (rs.next()) {
Book book=new Book();
book.setBookID(rs.getInt(1));
book.setBookName(rs.getString(2));
book.setBookAuthor(rs.getString(3));
book.setBookPrice(rs.getDouble(4));
arrayList.add(book);
}
System.out.println(arrayList);
}catch (Exception e){
e.printStackTrace();
}
return arrayList;
}
//删除图书
public int DelBookById(){
//0失败 1成功
int rs=0;
//删除语句
String Sql="delete from book where bookId=?";
System.out.println("请输入要删除的图书编号！");
//要删除的ID
int delId=scanner.nextInt();
PreparedStatement ps=null;
try {
ps=connection.prepareStatement(Sql);
ps.setInt(1,delId);
//执行删除
rs=ps.executeUpdate();
if (rs>0){
System.out.println("删除成功！-----");
}
}catch (Exception e){
e.printStackTrace();
}
return rs;
}
//修改图书
public int updateBook(){
int rs=0;
System.out.println("请输入要修改的图书！");
Book book=getOneBook();
if(book==null){
System.out.println("搜不到图书，请重试！");
}else {
String Sql="update book set bookName=?,bookAuthor=?,bookPrice=? where bookId=? or bookName=?";
PreparedStatement ps=null;
System.out.println("请输入图书名称！");
String name=scanner.next();
System.out.println("请输入图书作者！");
String author=scanner.next();
System.out.println("请输入图书价格！");
double price=scanner.nextDouble();
try {
ps=connection.prepareStatement(Sql);
ps.setString(1,name);
ps.setString(2,author);
ps.setDouble(3,price);
ps.setInt(4,book.getBookID());
ps.setString(5,book.getBookName());
rs=ps.executeUpdate();
if(rs>0){
System.out.println("修改成功！------");
}
}catch (Exception e){
e.printStackTrace();
}
}
return rs;
}
//查询单个图书
public Book getOneBook(){
Book book=new Book();
String Sql="select * from book where bookId=? or bookName=?";
System.out.println("请输入图书编号或者图书名字！");
String sidorsname=scanner.next();
//判断输入的是编号还是名字
Boolean flag=true;
try {
new BigDecimal(sidorsname);
flag=true;
}catch (Exception e){
flag=false;
}
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps=connection.prepareStatement(Sql);
if(flag){
ps.setInt(1,Integer.parseInt(sidorsname));
ps.setString(2,"0");
}else {
ps.setInt(1,0);
ps.setString(2,sidorsname);
}
rs=ps.executeQuery();
while (rs.next()) {
book.setBookID(rs.getInt(1));
book.setBookName(rs.getString(2));
book.setBookAuthor(rs.getString(3));
book.setBookPrice(rs.getDouble(4));
}
System.out.println(book);
}catch (Exception e){
e.printStackTrace();
}
return book;
}
//模糊查询
public ArrayList<Book> likeBook(){
ArrayList<Book> arrayList=new ArrayList<>();
//模糊查询语句的sql写法
String Sql="Select * from book where bookName like ? or bookAuthor like ?";
System.out.println("请输入图书关键词？");
String word=scanner.next();
PreparedStatement ps=null;
ResultSet rs=null;
try {
ps=connection.prepareStatement(Sql);
ps.setString(1,"%"+word+"%");
ps.setString(2,"%"+word+"%");
rs=ps.executeQuery();
while (rs.next()){
Book book=new Book();
book.setBookID(rs.getInt(1));
book.setBookName(rs.getString(2));
book.setBookAuthor(rs.getString(3));
book.setBookPrice(rs.getDouble(4));
arrayList.add(book);
}
System.out.println(arrayList);
}catch (Exception e){
e.printStackTrace();
}
return arrayList;
}
}
4.执行所写代码
package controller;
import dao.CURD;
import javax.sound.midi.Soundbank;
import javax.swing.text.rtf.RTFEditorKit;
import java.util.Scanner;
public class Start {
private static final String userName="admin";
private static final String password="123";
Scanner scanner=new Scanner(System.in);
CURD curd=new CURD();
public void login(){
String re="one";
while (true){
if(re.equals("one")||re.equals("r")||re.equals("R")){
System.out.println("请输入用户名！");
String name=scanner.next();
System.out.println("请输入密码！");
String pwd=scanner.next();
if(name.equals(userName)&&pwd.equals(password)){
A1();
break;
}else {
System.out.println("密码或用户名错误！请输入R重新登录-------");
re=scanner.next();
}
}else {
System.out.println("请输入R重新登录--------");
re=scanner.next();
}
}
}
public void A1(){
//String re="one";
while (true){
//if(re.equals("one")||re.equals("r")||re.equals("R")){
System.out.println("请输入数字序号进行操作---------");
System.out.println("1.查看所有图书\t2.添加图书\t3.删除图书\t4.查询图书\t5.修改图书\t6.关键查询\t7.退出系统");
//不是数字会报错，需要抛异常
int num=scanner.nextInt();
//if(num<=0||num>7){
// System.out.println("你输入的编号有误！请重新输入！");
// A1();
//}else {
switch (num){
case 1:
curd.getAllBook();
break;
case 2:
curd.AddBook();
break;
case 3:
curd.DelBookById();
break;
case 4:
curd.getOneBook();
break;
case 5:
curd.updateBook();
break;
case 6:
curd.likeBook();
break;
case 7:
System.out.println("正在退出系统----------");System.exit(0);
break;
}
// System.out.println("请输入r返回----------");
//re=scanner.next();
// }
// }else {
// System.out.println("请输入r返回----------");
// re=scanner.next();
//}
}
}
public static void main(String[] args) {
Start start=new Start();
System.out.println("欢迎来到图书管理系统-----------------");
start.login();
}
}
