JAVA实现对MySQL数据库的增删改查(学生信息管理系统案例)
Student类
public class Student {
private String id;
private String name;
private String gender;
private String birthday;
public Student(){}
public Student(String id,String name,String gender,String birthday){
super();
this.id=id;
this.name=name;
this.gender=gender;
this.birthday=birthday;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getBirthday() {
return birthday;
}
public void setBirthday(String birthday) {
this.birthday = birthday;
}
public String toString() {
return id + "\t" + name + "\t" + gender + "\t" + birthday;
}
}
JdbcUtils类
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class JdbcUtils {
private static String driver;
private static String url;
private static String username;
private static String password;
private static Properties p = null;
static {
try {
// 加载配置文件内容到p对象中
InputStream is = JdbcUtils.class.getResourceAsStream("/dbconfig.properties");
p = new Properties();
p.load(is);
// 获取JDBC参数
driver = p.getProperty("driver");
url = p.getProperty("url");
username = p.getProperty("username");
password = p.getProperty("password");
Class.forName(driver);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
// 封装获取Connection对象
public static Connection getConnection() throws SQLException {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
// 封装获取PreparedStatement对象
public static PreparedStatement getPreparedStatement(Connection conn, String sql) throws SQLException {
PreparedStatement ps = null;
try {
ps = getConnection().prepareStatement(sql);
} catch (SQLException E) {
E.printStackTrace();
}
return ps;
}
// 封装关闭资源方法
public static void clossAll(ResultSet rs, PreparedStatement ps, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Fuction类
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
public class Function {
private static Student student;
final static Scanner input = new Scanner(System.in);
// 查询所有记录
public static List<Student> query(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException {
String sql = "select * from student;";
List<Student> list = new ArrayList<Student>();
ps = JdbcUtils.getPreparedStatement(conn, sql);
rs = ps.executeQuery();
System.out.println("学号" + "\t" + "姓名" + "\t" + "性别" + "\t" + "出生年月");
System.out.print("-------------------------------------\n");
try {
while (rs.next()) {
Student student = new Student(rs.getString("id"), rs.getString("name"), rs.getString("gender"),
rs.getString("birthday"));
list.add(student);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.clossAll(rs, ps, conn);
}
return list;
}
// 查询学生信息
public static Student select(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException {
String sql = "select * from student where name=?;";
System.out.println("请输入你要查询的学生姓名>>");
String a = input.next();
try {
ps = JdbcUtils.getPreparedStatement(conn, sql);
ps.setString(1, a);
rs = ps.executeQuery();
while (rs.next()) {
student = new Student(rs.getString("id"), rs.getString("name"), rs.getString("gender"),
rs.getString("birthday"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.clossAll(rs, ps, conn);
}
return student;
}
// 插入一条学生信息
public static void insert(PreparedStatement ps, Connection conn) throws SQLException {
System.out.println("请输入学号>>");
student.setId(input.next());
System.out.println("请输入姓名>>");
student.setName(input.next());
System.out.println("请输入性别>>");
student.setGender(input.next());
System.out.println("请输入出生年月>>");
student.setBirthday(input.next());
String sql = "insert into student values(?,?,?,?)";
ps = JdbcUtils.getPreparedStatement(conn, sql);
try {
ps.setString(1, student.getId());
ps.setString(2, student.getName());
ps.setString(3, student.getGender());
ps.setString(4, student.getBirthday());
System.out.println("插入学生信息成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.clossAll(null, ps, conn);
}
}
// 修改学生信息
public static void update(PreparedStatement ps, Connection conn) throws SQLException {
System.out.println("请输入需要被修改的学生姓名>>");
String oldName = input.next();
System.out.println("你要修改该学生的哪些信息>>\n" + "【a】 -> 姓名\n" + "【b】 -> 性别\n" + "【c】 -> 出生年月日");
String x = input.next();
try {
switch (x) {
case "a":
System.out.println("请输入将被替换的姓名>>");
String newName = input.next();
String sql = "update student set name=? where name=?";
ps = JdbcUtils.getPreparedStatement(conn, sql);
ps.setString(1, newName);
ps.setString(2, oldName);
;
break;
case "b":
System.out.println("请输入将被替换的性别>>");
String newGender = input.next();
String sql2 = "update student set gender=? where name=?";
ps = JdbcUtils.getPreparedStatement(conn, sql2);
ps.setString(1, newGender);
ps.setString(2, oldName);
;
break;
case "c":
System.out.println("请输入将被替换的出生年月>>");
String newBirthday = input.next();
String sql3 = "update student set birthday=? where name=?";
ps = JdbcUtils.getPreparedStatement(conn, sql3);
ps.setString(1, newBirthday);
ps.setString(2, oldName);
;
break;
default:
System.out.print("输入正确序号");
;
break;
}
System.out.println("修改学生信息成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.clossAll(null, ps, conn);
}
}
// 删除数据，以姓名输入！
public static void delete(PreparedStatement ps, Connection conn) throws SQLException {
System.out.println("请输入需要被删除学生的姓名>>");
student.setName(input.next());
String sql = "delete from student where name=?";
try {
ps = JdbcUtils.getPreparedStatement(conn, sql);
ps.setString(1, student.getName());
System.out.println("删除学生信息成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.clossAll(null, ps, conn);
}
}
// 分页显示学生信息
public static List<Student> TestPage(ResultSet rs, PreparedStatement ps, Connection conn, int index, int size)
throws SQLException {
String sql = "select * from student limit ?,?";
List<Student> list = new ArrayList<Student>();
ps = JdbcUtils.getPreparedStatement(conn, sql);
ps.setInt(1, (index - 1) * size);
ps.setInt(2, size);
rs = ps.executeQuery();
System.out.println("学号" + "\t" + "姓名" + "\t" + "性别" + "\t" + "出生年月");
System.out.print("-------------------------------------\n");
try {
while (rs.next()) {
Student student = new Student(rs.getString("id"), rs.getString("name"), rs.getString("gender"),
rs.getString("birthday"));
list.add(student);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.clossAll(rs, ps, conn);
}
return list;
}
}
Test类
import java.util.List;
import java.sql.*;
import java.util.Scanner;
public class main {
final static Scanner input = new Scanner(System.in);
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
boolean flag = false;
while (true) {
try {
conn = JdbcUtils.getConnection();
System.out.println("" +
"~~~~~~~~~~~~~~~~~学生 信息 管理 系统~~~~~~~~~~~~~~~\n" +
"~~~~~~~~~~~~~~~~~ 请选择相关功能 ~~~~~~~~~~~~~~~\n" +
"【1】 -> 显示所有学生信息；\n" +
"【2】 -> 查询学生信息；\n" +
"【3】 -> 添加新的学生信息；\n" +
"【4】 -> 修改学生信息；\n" +
"【5】 -> 删除学生信息；\n" +
"【6】 -> 分页显示学生信息；\n" +
"【0】 -> 退出程序！\n>>");
int x = input.nextInt();
switch (x) {
case 1:
List<Student> list=Function.query(rs,ps,conn);
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i).getId()+"\t"+list.get(i).getName()+"\t"+list.get(i).getGender()+"\t"+list.get(i).getBirthday());
}
break;
case 2:
Student student=Function.select(rs,ps,conn);
if (student!=null) {
System.out.println("该学生的信息为：");
System.out.println(student.getId()+"\t"+student.getName()+"\t"+student.getGender()+"\t"+student.getBirthday());
}
break;
case 3: Function.insert(ps,conn);break;
case 4: Function.update(ps,conn);break;
case 5: Function.delete(ps,conn);break;
case 6:
int index=1;
int size=3;
System.out.println("输入‘n’显示第一页，再次输入显示下一页（一页3行）");
while(true){
String anyKey=input.next();
if (anyKey.equalsIgnoreCase("n")) {
List<Student> list2= Function.TestPage(rs,ps,conn, index, size);
for (int i = 0; i < list2.size(); i++) {
System.out.println(list2.get(i).getId()+"\t"+list2.get(i).getName()+"\t"+list2.get(i).getGender()+"\t"+list2.get(i).getBirthday());
}
index++;
}
else {
break;
}
}
break;
case 0: flag = true;break;
default:
System.out.println("！！！！！请输入正确的功能序号！！！！！");
break;
}
} catch (SQLException e) {
e.printStackTrace();
}
if (flag==true) break;//退出无线循环
}
System.out.println("！！！！！谢谢使用！！！！！");
}
}
dbconfig.properties 配置文件
driver=com.mysql.cj.jdbc.Driver
url =jdbc:mysql://localhost:3306/mydb1?serverTimezone=UTC
username=你的账户名
password=你的密码
创建数据库：
CREATE TABLE `student` (
`id` int NOT NULL,
`name` varchar(20) NOT NULL,
`gender` char(1) NOT NULL,
`birthday` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO student (`id`, `name`, `gender`, `birthday`) VALUES ('1', '马盈辉', '男', '1999-10-03');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('2', '李龙飞', '男', '1992-11-21');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('3', '郑子科', '男', '1899-01-02');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('4', '何春阳', '女', '1993-10-22');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('5', '陈冠希', '男', '1999-10-03');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('6', '小刚', '男', '2001-05-10');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('7', '刘一手', '男', '2003-10-16');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('8', '孙悟空', '男', '1992-10-23');
INSERT INTO student(`id`, `name`, `gender`, `birthday`) VALUES ('9', '猪八戒', '男', '1998-10-23');
版权声明：本文为qq_44694720原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。