参考:http://blog.csdn.net/alextan_/article/details/65447446
本系列是通过学习AlexTan这位博主的文章搭建一个图书管理系统,顺便再学习一下java这门语言。大二水了一学期java课,完全是菜鸡,所以注释很多自己的理解。
这篇文章要解决的是将数据存储到数据库中,数据库选用mysql。
一、创建数据库
create database books;
use books;
create table book(id int(6) primary key not null auto_increment,bookname varchar(255),author varchar(255),price float);
这是在mysql中创建
二、项目目录
三、Database.java
知识点–JDBC
1.加载驱动
首先要下载数据库的驱动jar文件,下载的版本要和自己的数据库版本对应,下载地址:https://dev.mysql.com/downloads/connector/j/
2.获取数据库连接
使用java.sql.DriverManager的getConnection(String url, String user, String pass)方法获取数据库连接Connectiond对象
3.通过Connection创建statement对象
通常有三种statement对象
- createStatement(),创建普通statement对象,可以直接执行sql语句
- prepareStatement(String sql),创建预编译的statement对象,支持SQL语句带参数
- prepareCall(String sql),创建能执行存储过程的statement对象
4.执行SQL语句,所有statement对象都有三个执行sql的方法 - execute()可执行任何SQL语句返回Boolean,表示是否返回了数据,需要通过statement对象的getResultSet()方法才能获取结果集,通过statement的getUpdateCount()获得影响的条数。
- executeQuery()返回ResultSet结果集
- executeUpdate()执行SQL语句返回受影响的条数
参考:https://www.cnblogs.com/fysola/p/6150686.html
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Database {
private static final String URL="jdbc:mysql://localhost:3306/books?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&useSSL=false";
private static final String NAME="root";
private static final String PASSWORD="pass993.";
private static Connection conn=null;
//静态代码块(将加载驱动,连接数据库放入静态块中)
static {
try {
//加载驱动程序
Class.forName("com.mysql.cj.jdbc.Driver");
}catch(ClassNotFoundException e) {
e.printStackTrace();
}
}
//对外提供一个方法来获取数据库连接
public static Connection getConnection() {
try {
conn = DriverManager.getConnection(URL,NAME,PASSWORD);
return conn;
}catch(SQLException e) {
e.printStackTrace();
return null;
}
}
//断开数据库连接
public static void close(Connection con,Statement stat,ResultSet rs)
{
try {
if(rs!=null)
rs.close();
if(stat!=null)
stat.close();
if(con!=null)
con.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
//重载close()函数,适用于没有ResultSet的情况
public static void close(Connection con,Statement stat)
{
close(con,stat,null);
}
}
四、Operator.java
修改图书和删除图书还是有bug。如果输入不存在的id号还是会显示修改成功但是其实什么都没改。
package control;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import jdbc.Database;
import model.Book;
public class Operator {
public ArrayList<Book> getBookList()
{
ArrayList<Book> booklist = new ArrayList<Book>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
//3.通过数据库的连接操作数据库,实现增删改查
try {
conn = Database.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select id,bookname,author,price from book");//选择import java.sql.ResultSet;
while(rs.next()){//如果对象中有数据,就会循环打印出来
String bookname = rs.getString("bookname");
String author = rs.getString("auhor");
float price = rs.getFloat("price");
Book book = new Book(bookname,author,price);
booklist.add(book);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
Database.close(conn, stmt, rs);
}
return booklist;
}
public boolean addBook(String bookname,String author,float price)
{
Connection conn = null;
Statement stmt = null;
try {
conn = Database.getConnection();
stmt = conn.createStatement();
String sql = "insert into book(bookname,author,price) values('"+bookname+"','"+author+"',"+price+")";
//System.out.println(sql);
stmt.execute(sql);
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
} finally {
Database.close(conn, stmt);
}
}
public boolean deleteBook(int id,String bookname)
{
Connection conn = null;
Statement stmt = null;
try {
conn = Database.getConnection();
stmt = conn.createStatement();
String sql;
if(id != -1)
{
sql = "delete from book where id="+id;
}
else
{
sql = "delete from book where bookname='"+bookname+"'";
}
stmt.execute(sql);
return true;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
Database.close(conn, stmt);
}
}
public boolean changeBoo(int id,String bookname,String changename)
{
Connection conn = null;
Statement stmt = null;
try {
conn = Database.getConnection();
stmt = conn.createStatement();
String sql;
if (id != -1)
{
sql = "update book set bookname='"+changename+"'"+" where id="+id;
//System.out.println(sql);
}
else
{
sql = "update book set bookname='"+changename+"'"+" where bookname='"+bookname+"'";
}
stmt.execute(sql);
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
} finally {
Database.close(conn,stmt);
}
}
public void findBoo(int id,String bookname,String author,String dimname,float minprice,float maxprice)
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = Database.getConnection();
stmt = conn.createStatement();
String sql;
if (id != -1)
{
sql = "select id,bookname,author,price from book"+" where id="+id;
//System.out.println(sql);
}
else if(bookname != null)
{
sql = "select id,bookname,author,price from book"+" where bookname='"+bookname+"'";
}
else if(author != null)
{
sql = "select id,bookname,author,price from book"+" where author='"+author+"'";
}
else if(dimname != null)
{
sql = "select id,bookname,author,price from book"+" where bookname like'%"+dimname+"%'";
}
else if(maxprice != 0)
{
sql = "select id,bookname,author,price from book where price>="+minprice+" and price<="+maxprice;
}
else
{
System.out.println("出现未知错误,请联系管理员!");
sql="";
}
rs = stmt.executeQuery(sql);
if(rs.next())
{
System.out.println("查找成功!您查找的结果为:\n");
do{//如果对象中有数据,就会循环打印出来
System.out.println("编号:"+rs.getInt("id")+" 书名:"+rs.getString("bookname")+",作者:"+rs.getString("author")+",价格:"+rs.getFloat("price"));
}while(rs.next());
}
else
System.out.println("未查找到您想要的图书!");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
Database.close(conn, stmt, rs);
}
}
public void printAllbook()
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
//3.通过数据库的连接操作数据库,实现增删改查
try {
conn = Database.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery("select id,bookname,author,price from book");//选择import java.sql.ResultSet;
if(rs.next())
{
do{//如果对象中有数据,就会循环打印出来
System.out.println("编号:"+rs.getInt("id")+" 书名:"+rs.getString("bookname")+",作者:"+rs.getString("author")+",价格:"+rs.getFloat("price"));
}while(rs.next());
}
else
{
System.out.println("图书库为空,请添加图书!");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
Database.close(conn, stmt, rs);
}
}
public boolean clearBook()
{
Connection conn = null;
Statement stmt = null;
try {
conn = Database.getConnection();
stmt = conn.createStatement();
String sql = "truncate table book";
stmt.execute(sql);
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return false;
} finally {
Database.close(conn, stmt);
}
}
}
四、MainClass.java
package ui;
import java.util.Scanner;
import control.Operator;
public class MainClass {
public MainClass()
{
Scanner scan = new Scanner(System.in);
while(true)
{
printMenu();
//读取用户输入
int choice = scan.nextInt();
if(choice == 6)
{
System.out.println("成功退出系统,欢迎再次光临!");
scan.close();
break;
}
switch(choice)
{
case 1: addBook(); break;
case 2: deleteBoo(); break;
case 3: changeBoo(); break;
case 4: findBoo(); break;
case 5: clearBoo(); break;
default: System.out.println("输入非法"); printMenu(); continue;
}
}
}
void printMenu()
{
//打印菜单
System.out.println("欢迎...");
System.out.println("增加图书...1");
System.out.println("删除图书...2");
System.out.println("修改图书...3");
System.out.println("查询图书...4");
System.out.println("清空图书...5");
System.out.println("退出系统...6");
}
void clearBoo()
{
Operator operator = new Operator();
boolean isSuccess = operator.clearBook();
if(isSuccess)
{
System.out.println("清空成功!");
}
else
{
System.out.println("清空失败!");
}
}
void addBook()
{
while(true)
{
Scanner scan = new Scanner(System.in);
System.out.println("请输入图书名:");
String bookname = scan.next();
System.out.println("请输入作者:");
String author = scan.next();
System.out.println("请输入单价:");
float price = scan.nextFloat();
Operator operator = new Operator();
boolean isSuccess = operator.addBook(bookname, author, price);
if(isSuccess)
{
System.out.println("增加成功!");
}
else
{
System.out.println("增加失败!");
}
operator.printAllbook();
System.out.println("是否继续增加? y/n");
char yno = scan.next().charAt(0);
if (yno == 'y')
continue;
else
{
break;
}
}
}
void deleteBoo()
{
Scanner scan = new Scanner(System.in);
while(true)
{
System.out.println("请输入按哪种方法删除图书:1、编号/2、书名/3、返回主菜单");
int choose = scan.nextInt();
int id = -1;
String name = "";
if(choose == 1)
{
System.out.println("请输入要删除的书的编号:");
id = scan.nextInt();
Operator operator = new Operator();
//System.out.println(id);
if(id > -1)
{
boolean isSuccess = operator.deleteBook(id,name);
if(isSuccess)
System.out.println("删除成功!");
else
System.out.println("删除失败!请检查您输入的是否正确!");
operator.printAllbook();
}
else
{
System.out.println("输入错误!");
}
}
else if(choose == 2)
{
System.out.println("请输入您要删除的书名:");
name = scan.next();
Operator operator = new Operator();
if(name != "")
{
boolean isSuccess = operator.deleteBook(id,name);
if(isSuccess)
System.out.println("删除成功!");
else
System.out.println("删除失败!");
operator.printAllbook();
}
else
{
System.out.println("未查找到您想要的书名");
}
}
else if(choose == 3)
{
printMenu();
break;
}
else
{
System.out.println("输入非法!");
}
}
}
void changeBoo()
{
Scanner scan = new Scanner(System.in);
while(true)
{
System.out.println("请输入按哪种方法修改图书:1、编号/2、书名/3、返回主菜单");
int choose = scan.nextInt();
int id = -1;
String name = "";
if(choose == 1)
{
System.out.println("请输入要修改的书的编号:");
id = scan.nextInt();
Operator operator = new Operator();
if(id > -1)
{
System.out.println("请输入你要修改为什么书名:");
String str = scan.next();
boolean isSuccess = operator.changeBoo(id,name,str);
if(isSuccess)
System.out.println("修改成功!");
else
System.out.println("修改失败!");
operator.printAllbook();
}
else
{
System.out.println("输入错误!");
}
}
else if(choose == 2)
{
System.out.println("请输入您要修改的书名:");
name = scan.next();
Operator operator = new Operator();
if(name != "")
{
System.out.println("请输入你要修改为什么书名:");
String str = scan.next();
boolean isSuccess = operator.changeBoo(id,name,str);
if(isSuccess)
System.out.println("修改成功!");
else
System.out.println("修改失败!");
operator.printAllbook();
}
}
else if(choose == 3)
{
printMenu();
break;
}
else
{
System.out.println("输入非法!");
}
}
}
void findBoo()
{
Scanner scan = new Scanner(System.in);
Operator operator = new Operator();
while(true)
{
String name = null;
int id = -1;
String author = null;
String dimname = null;
float minprice = 0;
float maxprice = -1;
System.out.println("请输入按哪种方法查找图书:1、编号/2、书名(精确查找)/3、作者/4、书名(模糊查找)/5、价格/6、返回主菜单");
int choose = scan.nextInt();
if(choose == 1)
{
System.out.println("请输入要查找的书的编号:");
id = scan.nextInt();
if(id > -1)
{
operator.findBoo(id,name,author,dimname,minprice,maxprice);
}
else
{
System.out.println("输入错误!");
}
}
else if(choose == 2)
{
System.out.println("请输入您要查找的书名:");
name = scan.next();
if(name != "")
{
operator.findBoo(id,name,author,dimname,minprice,maxprice);
}
else
{
System.out.println("您的输入不能为空!");
}
}
else if (choose == 3)
{
System.out.println("请输入您要查找的作者名:");
author = scan.next();
if(author != "")
{
operator.findBoo(id,name,author,dimname,minprice,maxprice);
}
else
{
System.out.println("您的输入不能为空!");
}
}
else if (choose == 4)
{
System.out.println("请输入您要模糊查找的书名:");
dimname = scan.next();
if(dimname != "")
{
operator.findBoo(id,name,author,dimname,minprice,maxprice);
}
else
{
System.out.println("您的输入不能为空!");
}
}
else if (choose == 5)
{
System.out.println("请输入您要查找的书的价格的最低价:");
minprice = scan.nextFloat();
System.out.println("请输入您要查找的书的价格的最高价:");
maxprice = scan.nextFloat();
if (maxprice>-1)
{
operator.findBoo(id,name,author,dimname,minprice,maxprice);
}
else
{
System.out.println("输入错误!");
}
}
else if(choose == 6)
{
printMenu();
break;
}
else
{
System.out.println("输入非法!");
}
}
}
public static void main(String[] args) {
new MainClass();
}
}
五、Book.java
package model;
public class Book {
private String bookname;
private String author;
private float price;
public Book(String bookname, String author, float price)
{
this.bookname = bookname;
this.author = author;
this.price = price;
}
public String getBookname() {
return bookname;
}
public void setBookname(String bookname) {
this.bookname = bookname;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public float getPrice() {
return price;
}
public void setPrice(float price) {
this.price = price;
}
}