基于：
MySQL 8.0.3
JDK 1.8
数据库的创建
-- 创建学生数据库
CREATE DATABASE Student
-- 创建学生表
CREATE TABLE STU(
ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
NAME VARCHAR(200) NOT NULL,
SEX CHAR(2) DEFAULT('男'),
AGE INT NOT NULL,
SCORE DOUBLE
);
-- 插入五条学生
INSERT INTO STU(NAME,SEX,AGE,SCORE) VALUES
("张三",'男',20,100),
("李四",'女',17,99),
("王五",'女',24,50),
("赵六",'女',28,60),
("张一",'男',14,61),
("张二",'男',22,77);jdbc.properties配置文件
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/student
user = root
password = 123456package hw1;
public class Student {
private int id;
private String name;
private String sex;
private int age;
private double score;
public Student() {
super();
}
public Student(int id, String name, String sex, int age, double score) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.score = score;
}
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public double getScore() {
return score;
}
public void setScore(float score) {
this.score = score;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", sex=" + sex +
", age=" + age +
", score=" + score +
'}';
}
}
package hw1;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtil {
private static String DRIVER;
private static String URL;
private static String USER;
private static String PASSWORD;
static{
try {
//获取配置文件的字符流
FileInputStream fis = new FileInputStream(new File("src/hw1/jdbc.properties"));
//创建操作配置文件的工具对象
Properties properties = new Properties();
//将字节流加载进工具对像中
properties.load(fis);
//通过文件中的key获取对应的value
DRIVER = properties.getProperty("driver");
URL = properties.getProperty("url");
USER = properties.getProperty("user");
PASSWORD = properties.getProperty("password");
fis.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//数据库连接操作类
public static Connection getCoun(){
Connection connection = null;
try {
//注册驱动
Class.forName(DRIVER);
//获取连接
connection = DriverManager.getConnection(URL,USER,PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
}
package hw1;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
public class StudentManger {
private Connection connection;
private PreparedStatement ps;
//添加方法
public int add(Student student){
int res = 0;
//获取数据库连接
connection = JDBCUtil.getCoun();
//添加语句
String sql = "INSERT INTO stu (NAME,SEX,AGE,SCORE) VALUES (?,?,?,?)";
try {
ps = connection.prepareStatement(sql);
ps.setString(1,student.getName());
ps.setString(2,student.getSex());
ps.setInt(3,student.getAge());
ps.setDouble(4,student.getScore());
res = ps.executeUpdate();
ps.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return res;
}
public ArrayList<Student> selectAll(){
ArrayList<Student> arrayList = new ArrayList<>();
connection = JDBCUtil.getCoun();
String sql = "SELECT ID,NAME,SEX,AGE,SCORE FROM stu";
try {
ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()){
int id = rs.getInt("ID");
String name = rs.getString("NAME");
String sex = rs.getString("SEX");
int age = rs.getInt("AGE");
double score = rs.getDouble("SCORE");
Student student = new Student(id,name,sex,age,score);
arrayList.add(student);
}
ps.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return arrayList;
}
public boolean removeByID(int id){
int res = 0;
connection = JDBCUtil.getCoun();
String sql = "DELETE FROM stu WHERE ID = ?";
try {
ps = connection.prepareStatement(sql);
ps.setInt(1,id);
ps.executeUpdate();
ps.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return res == 0 ? false : true;
}
public boolean updateByName(String name,int age){
int res = 0;
connection = JDBCUtil.getCoun();
String sql = "UPDATE stu SET AGE = ? WHERE NAME = ?";
try {
ps = connection.prepareStatement(sql);
ps.setInt(1,age);
ps.setString(2,name);
ps.executeUpdate();
ps.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return res == 0 ? false : true;
}
}
package hw1;
import java.util.ArrayList;
import java.util.Scanner;
public class Menu {
private StudentManger studentManger = new StudentManger();
private Scanner scanner = new Scanner(System.in);
private String choose;
public void mainMenu(){
do {
System.out.println("请输入您的选择");
System.out.println("1、录入学生信息");
System.out.println("2、查询学生信息");
System.out.println("3、删除学生信息");
System.out.println("4、修改学生信息");
System.out.println("5、退出");
choose = scanner.next();
switch (choose){
case "1":
addMenu();
break;
case "2":
select();
break;
case "3":
removeById();
break;
case "4":
updateByName();
break;
case "5":
break;
default:
System.out.println("请输入重新输入！！！");
}
}while (!choose.equals("5"));
}
private void addMenu(){
do {
System.out.println("请输入姓名：");
String name = scanner.next();
System.out.println("请输入性别：");
String sex = scanner.next();
System.out.println("请输入年龄：");
int age = scanner.nextInt();
System.out.println("请输入成绩：");
double score = scanner.nextDouble();
Student student = new Student(0,name,sex,age,score);
int res = studentManger.add(student);
if (res == 0){
System.out.println("录入失败");
}else{
System.out.println("录入成功"+res+"条数据");
}
System.out.println("是否继续");
choose = scanner.next();
}while(!choose.equals("n"));
}
private void select(){
ArrayList<Student> arrayList = studentManger.selectAll();
for (Student student : arrayList) {
System.out.println(student);
}
}
private void removeById(){
do{
System.out.println("请输入要删除的id");
int id = scanner.nextInt();
boolean res = studentManger.removeByID(id);
if (res == true) {
System.out.println("删除失败");
}else{
System.out.println("删除成功");
}
System.out.println("是否继续");
choose = scanner.next();
}while(!choose.equals("n"));
}
private void updateByName(){
System.out.println("请输入要修改的姓名");
String name = scanner.next();;
System.out.println("请输入要修改的年龄");
int age = scanner.nextInt();
boolean res = studentManger.updateByName(name,age);
if (res == false){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
}
}
package hw1;
public class Test {
public static void main(String[] args) {
Menu menu = new Menu();
menu.mainMenu();
}
}
链接：https://pan.baidu.com/s/1SNiu9L7EfWEqJb8GPj55yg?pwd=1234
提取码：1234