package Library;
import java.sql.*;
import java.util.Scanner;
public class BookSystem {
//数据库连接常量
public static final String usersname="root";
public static final String password="123456";
public static final String URL="jdbc:mysql://localhost:3306/db0905?useSSL=true";
public static final String driver="com.mysql.jdbc.Driver";
//主循环控制变量
boolean flag=true;
Scanner input = new Scanner(System.in);
public void run(){
// 运行的流程
while (flag){
System.out.println("请输入要进行的操作:");
System.out.println("1. 注册 2.登录");
try {
int i = input.nextInt();
if (i == 1){
register();
} else if (i == 2){
if (login()){
// 正常运行登录成功之后的方法
manageBooks();
}
} else {
System.out.println("输入有误, 请重新输入");
}
} catch (Exception e) {
e.printStackTrace();
System.out.println("输入有误, 请重新输入");
}
}
}
// 登录之后做图书管理
public void manageBooks(){
Scanner input = new Scanner(System.in);
while (true){
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.退出系统");
int i = input.nextInt();
if (i == 1){
add();
} else if (i == 2){
remove();
} else if (i == 3){
update();
} else if (i == 4){
search();
} else if (i == 5){
listAllBooks();
} else {
System.out.println("程序正在退出...");
flag = false;
break;
}
}
}
//修改图书信息
public void update(){
listAllBooks();
System.out.println("请输入要修改的图书序号:");
int i = input.nextInt();
System.out.println("请输入修改后的图书名称");
String name = input.next();
System.out.println("请输入修改后的图书作者:");
String author = input.next();
System.out.println("请输入修改后的图书编号:");
String ISBN = input.next();
Connection conn=null;
PreparedStatement stmt=null;
try {
//数据库连接
Class.forName(driver);
conn=DriverManager.getConnection(URL,usersname,password);
//更新图书记录
String s = "update book set name=?,author=?,ISBN=? where bid=?";
stmt=conn.prepareStatement(s);
stmt.setString(1,name);
stmt.setString(2,author);
stmt.setString(3,ISBN);
stmt.setInt(4,i);
int i1 = stmt.executeUpdate();
if (i1>0){
System.out.println("修改成功!");
}else {
System.out.println("修改失败!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
//删除图书
public void remove(){
listAllBooks();
System.out.println("请输入要删除的图书编号:");
int i = input.nextInt();
Connection conn=null;
PreparedStatement stmt=null;
try {
//数据库连接
Class.forName(driver);
conn=DriverManager.getConnection(URL,usersname,password);
//删除记录
String s = "delete from book where bid=?";
stmt=conn.prepareStatement(s);
stmt.setInt(1,i);
int i1 = stmt.executeUpdate();
if (i1>0){
System.out.println("删除成功!");
}else{
System.out.println("删除失败!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//用户登录检测
public boolean login(){
boolean b=false;
System.out.println("请输入您的账号名称:");
String name = input.next();
System.out.println("请输入您的账号密码:");
String psvd = input.next();
Connection conn=null;
PreparedStatement stmt=null;
try {
Class.forName(driver);
try {
//数据库连接
conn=DriverManager.getConnection(URL,usersname,password);
String s = "select *from users where uname=? and psvd=?";
stmt=conn.prepareStatement(s);
stmt.setString(1,name);
stmt.setString(2,psvd);
ResultSet rs = stmt.executeQuery();
while (rs.next()){
String uname = rs.getString("uname");
String psvd1 = rs.getString("psvd");
while (name.equals(uname)&&psvd.equals(psvd1)){
System.out.println("登录成功!");
b=true;
return b;
}
}
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}finally {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
System.out.println("登录失败!");
return b;
}
//图书查询
public void search(){
System.out.println("请输入要查找的书名:");
String name = input.next();
Connection conn=null;
PreparedStatement stmt=null;
try {
//数据库连接
Class.forName(driver);
conn=DriverManager.getConnection(URL,usersname,password);
String s ="select *from book where name=?" ;
stmt=conn.prepareStatement(s);
stmt.setString(1,name);
ResultSet rs = stmt.executeQuery();
while (rs.next()){
String bname = rs.getString("name");
String author = rs.getString("author");
String isbn = rs.getString("ISBN");
if (name.equals(bname)){
System.out.println("书名:"+bname+"\t"+"作者:"+author+"\t"+"图书编号:"+isbn);
continue;
}
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//用户注册
public void register(){
System.out.println("请输入注册姓名:");
String name = input.next();
System.out.println("请输入注册密码:");
String psvd = input.next();
Connection conn=null;
PreparedStatement stmt=null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(URL, usersname, password);
String s1 = "select * from users where uname =?";
stmt=conn.prepareStatement(s1);
stmt.setString(1,name);
ResultSet rs = stmt.executeQuery();
while (rs.next()){
String uname = rs.getString("uname");
if (name.equals(uname)){
System.out.println("该用户名已存在!");
return;
}
}
String s = "insert into users(uname ,psvd)value (?,?)";
stmt = conn.prepareStatement(s);
stmt.setString(1, name);
stmt.setString(2, psvd);
int i = stmt.executeUpdate();
if (i==1){
System.out.println("添加成功!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//添加图书
public void add(){
System.out.println("请输入书名:");
String name = input.next();
System.out.println("请输入作者:");
String author = input.next();
System.out.println("请输入ISBN:");
String ISBN = input.next();
Connection conn=null;
PreparedStatement stmt=null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(URL, usersname, password);
String s1 = "select * from book where name=? and author=? and ISBN=?";
stmt=conn.prepareStatement(s1);
stmt.setString(1,name);
stmt.setString(2,author);
stmt.setString(3,ISBN);
ResultSet rs = stmt.executeQuery();
while (rs.next()){
String name1 = rs.getString("name");
String author1 = rs.getString("author");
String isbn = rs.getString("ISBN");
if (name.equals(name1)&&author.equals(author1)&&ISBN.equals(isbn)){
System.out.println("该书已存在!");
return;
}
}
String s = "insert into book(name, author, ISBN) value (?,?,?)";
stmt = conn.prepareStatement(s);
stmt.setString(1, name);
stmt.setString(2, author);
stmt.setString(3, ISBN);
int i = stmt.executeUpdate();
if (i!=0){
System.out.println("添加成功!");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void listAllBooks() {
Connection conn=null;
PreparedStatement stmt=null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(URL, usersname, password);
String s = "select *from book";
stmt = conn.prepareStatement(s);
ResultSet resultSet = stmt.executeQuery();
while (resultSet.next()){
int bid = resultSet.getInt("bid");
String name = resultSet.getString("name");
String author = resultSet.getString("author");
String isbn = resultSet.getString("ISBN");
System.out.println("序号:"+bid+"\t"+"书名:\t"+name+"作者:\t"+author+"图书编号:\t"+isbn);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
jdbc增删改查练习
最新推荐文章于 2023-06-11 16:50:03 发布