1. 安装mysql 安装navicate
参考网上资料
创库 创表 并放入3条测试数据
2. jdbc实现增删改查
book 实体类
package com.msb;
/**
* @Auther: jack.chen
* @Date: 2023/11/6 - 11 - 06 - 20:33
* @Description: com.msb
* @version: 1.0
*/
public class Book {
private int id;
private String name;
private String author;
private double price;
public Book() {
}
public Book(int id, String name, String author, double price) {
this.id = id;
this.name = name;
this.author = author;
this.price = price;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
@Override
public String toString() {
return "Book{" +
"id=" + id +
", name='" + name + '\'' +
", author='" + author + '\'' +
", price=" + price +
'}';
}
}
实现增删改查
package com.msb;
import javafx.beans.binding.When;
import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;
/**
* @Auther: jack.chen
* @Date: 2023/11/6 - 11 - 06 - 20:27
* @Description: com.msb
* @version: 1.0
*/
public class Test {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
while (true){
System.out.println("--欢迎来到书城--");
System.out.println("1.数据编号查询书籍");
System.out.println("2.查询所有书籍");
System.out.println("3.删除指定编号书籍");
System.out.println("4.退出");
Scanner scanner = new Scanner(System.in);
System.out.println("请输入编号：");
int choice = scanner.nextInt();
if (choice==1){
System.out.println("请输出需要查询的书籍编号:");
int bno = scanner.nextInt();
Book b = getBookByNo(bno);
if (b==null){
System.out.println("您查询的书籍不存在");
}else{
System.out.println("查询成功！！！");
System.out.println(b);
}
}
if (choice==2){
ArrayList books = getAllBooks();
if (books.size()==0){
System.out.println("书城里面没有书籍信息！");
}else{
System.out.println("查询成功：");
for (int i = 0; i < books.size(); i++) {
System.out.println(books.get(i));
}
}
}
if (choice==3){
}
if (choice==4){
System.out.println("退出...");
break;
}
}
}
public static Book getBookByNo(int bno) throws SQLException, ClassNotFoundException {
Book book = null;
//加载驱动：
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接：
String url = "jdbc:mysql://127.0.0.1:3306/mysql?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
//创建会话：
Statement sta = conn.createStatement();
//发送SQL：ResultSet结果集合 - 结果集
ResultSet rs = sta.executeQuery("select * from t_book where id = " + bno);
if(rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String author = rs.getString("Author");
double price = rs.getDouble("price");
book = new Book(id, name, author, price);
}
sta.close();
conn.close();
return book;
}
public static ArrayList getAllBooks() throws ClassNotFoundException, SQLException {
//加载驱动：
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接：
String url = "jdbc:mysql://127.0.0.1:3306/mysql?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
//创建会话：
Statement sta = conn.createStatement();
//发送SQL：ResultSet结果集合 - 结果集
ResultSet rs = sta.executeQuery("select * from t_book");
ArrayList<Book> books = new ArrayList<>();
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
String author = rs.getString("Author");
double price = rs.getDouble("price");
Book book = new Book(id, name, author, price);
books.add(book);
}
return books;
}
}
运行结果
