数据库准备
这里我使用的可视化数据库软件为 Wampserver64 如有需要可自行安装。
进入数据库新建一个status数据库,在该数据库下新建一个status_info数据表,然后添加字段如图:数据库准备完毕,开始编写Java实现基本的增删查改操作。
Java准备
使用软件:IDEA
- 新建一个java项目:
打开IDEA,file>new>project>java>next>next>finish
在src目录下建立如下类关系:
- Status>StuStatus.java:
StuStatus.java该类是用来声明方法和变量的,主要代码如下:
package Status;
public class StuStatus {
private Integer id;
private String name;
private String sex;
private String academy;
private String major;
private String grade;
private Integer classes;
public StuStatus(Integer id,String name,String sex,String academy,String major,String grade,Integer classes){
super();
this.id = id;
this.name = name;
this.sex = sex;
this.academy = academy;
this.major = major;
this.grade = grade;
this.classes = classes;
}
public StuStatus() {
}
//声明方法 在这里可以使用快捷键:Alt+Insert
//调出Generate 点击Getter and Setter 选择所有变量自动批量插入声明方法
public Integer getId() {return id;}
public String getName() { return name; }
public String getSex() {return sex; }
public String getAcademy() {return academy; }
public String getMajor() {return major; }
public String getGrade() { return grade; }
public Integer getClasses() {return classes; }
public void setId(int id) {this.id = id;}
public void setName(String name) { this.name = name; }
public void setSex(String sex) {this.sex = sex; }
public void setAcademy(String academy) {this.academy = academy; }
public void setMajor(String major) {this.major = major; }
public void setGrade(String grade) {this.grade = grade; }
public void setClasses(int classes) {this.classes = classes; }
}
- Status>statusMain.java
该类主要用来在窗口显示待操作信息
package Status;
import java.util.Scanner;
public class statusMain {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
statusManager statusManager = new statusManager();
boolean flag = true;
while (flag) {
//界面
System.out.println("------------");
System.out.println("欢迎来到学籍管理系统");
System.out.println("0 显示全部学籍信息");
System.out.println("1 按学号搜索学生信息");
System.out.println("2 增加学生信息");
System.out.println("3 修改学生信息");
System.out.println("4 删除学生信息");
System.out.println("5 退出");
System.out.println("------------");
System.out.println("请选择:");
int choice = sc.nextInt();
// try/catch
try {
if (choice == 5) {
System.out.println("您已退出该系统");
return;
} else
statusManager.Manage(choice);
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
sc.close();
}
}
- Status>statusManager.java
该类目的是用来实现main中的各个方法以及和Dao目录下实现对数据库的操作
package Status;
import Dao.stuDao;
import java.sql.SQLException;
import java.util.*;
public class statusManager {
Scanner sc = new Scanner(System.in);
public void Manage(int choice) throws Exception{
switch (choice){
case 0:
list();
break;
case 1:
find();
break;
case 2:
add();
break;
case 3:
change();
break;
case 4:
delete();
break;
default: {
throw new Exception("没有该功能,请重新选择!");}
}
}
//获取所有学籍信息
public void list() {
//查找数据库里的数据
stuDao dao = new stuDao();
List<StuStatus> list = dao.getAll();
System.out.println("学生信息列表如下:");
for(StuStatus stuStatus : list) {
System.out.println("学号:"+stuStatus.getId()+"姓名:"+stuStatus.getName()+" 性别:"+stuStatus.getSex()+" 学院:"+stuStatus.getAcademy()+"专业:"+stuStatus.getMajor()+"年级:"+stuStatus.getGrade()+"班级:"+stuStatus.getClasses());
}
}
//查询学生信息
public void find() throws SQLException {
System.out.println("请输入学生学号id:");
Integer findId = Integer.valueOf(sc.next());
stuDao dao = new stuDao();
if(!dao.judgeExist(findId))
System.out.println("该学生不存在!");
else{
StuStatus stuStatus = dao.find(findId);
System.out.println("学号:"+stuStatus.getId()+"姓名:"+stuStatus.getName()+" 性别:"+stuStatus.getSex()+" 学院:"+stuStatus.getAcademy()+"专业:"+stuStatus.getMajor()+"年级:"+stuStatus.getGrade()+"班级:"+stuStatus.getClasses());
}
}
//添加学生信息
public void add(){
stuDao dao = new stuDao();
System.out.println("请输入要添加的学生学号id:");
Integer addId = Integer.valueOf(sc.next());
if(dao.judgeExist(addId))
System.out.println("该学生已存在!");
else{
System.out.println("输入姓名:");
String name = sc.next();
System.out.println("输入性别:");
String sex = sc.next();
System.out.println("输入学院:");
String academy = sc.next();
System.out.println("输入专业:");
String major = sc.next();
System.out.println("输入年级:");
String grade = sc.next();
System.out.println("输入班级:");
Integer classes = Integer.valueOf(sc.next());
dao.add(new StuStatus(addId,name,sex,academy,major,grade,classes));
System.out.println("查询到如下信息:");
list();
}
}
//修改学生信息
public void change(){
stuDao dao = new stuDao();
System.out.println("请输入要修改的学生学号id:");
Integer changeId = Integer.valueOf(sc.next());
if(!dao.judgeExist(changeId))
System.out.println("该学生不存在!");
else {
StuStatus stuStatus = dao.find(changeId);
System.out.println("学号:"+stuStatus.getId()+"姓名:"+stuStatus.getName()+" 性别:"+stuStatus.getSex()+" 学院:"+stuStatus.getAcademy()+"专业:"+stuStatus.getMajor()+"年级:"+stuStatus.getGrade()+"班级:"+stuStatus.getClasses());
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、班级");
Integer changeChoice = Integer.valueOf(sc.next());
switch (changeChoice){
case 1:
System.out.println("请输入修改后的姓名:");
String name = sc.next();
dao.changeName(changeId,name);
break;
case 2:
System.out.println("请输入修改后的性别:");
String sex = sc.next();
dao.changeSex(changeId,sex);
break;
case 3:
System.out.println("请输入修改后的学院:");
String academy = sc.next();
dao.changeAcademy(changeId,academy);
break;
case 4:
System.out.println("请输入修改后的专业:");
String major = sc.next();
dao.changeMajor(changeId,major);
break;
case 5:
System.out.println("请输入修改后的年级:");
String grade = sc.next();
dao.changeGrade(changeId,grade);
break;
case 6:
System.out.println("请输入修改后的班级:");
String classes = sc.next();
dao.changeClasses(changeId,classes);
break;
}
StuStatus stuStatus1 = dao.find(changeId);//返回修改后的信息
System.out.println("学号:"+stuStatus1.getId()+"姓名:"+stuStatus1.getName()+" 性别:"+stuStatus1.getSex()+" 学院:"+stuStatus1.getAcademy()+"专业:"+stuStatus1.getMajor()+"年级:"+stuStatus1.getGrade()+"班级:"+stuStatus1.getClasses());
}
}
//删除学生信息
private void delete() {
stuDao dao = new stuDao();
System.out.println("请输入要删除的学生学号id:");
Integer deleteId = Integer.valueOf(sc.next());
if(!dao.judgeExist(deleteId))
System.out.println("该学生不存在!");
else{
dao.delete(deleteId);
list();
}
}
}
- Dao>MySQLUtils.java
该类用来连接数据库:
package Dao;
import java.sql.*;
public class MySQLUtils {
private static String url = "jdbc:mysql://127.0.0.1:3306/status?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false"; //url中的status为数据库库名
private static String user = "root"; //数据库登陆账号
private static String password = "123456"; //数据库登陆密码
private static Connection con = null;
//获取连接
public static Connection getConn() {
try {
// 1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.获取连接
con = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
//关闭连接(有结果集)
public static void closeConn(Connection conn, Statement stmt, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
//关闭连接(无结果集)
public static void closeConn(Connection conn, Statement stmt){
if(stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
//测试连接数据库
public static void main(String[] args) {
System.out.print(getConn());
}
}
- Dao>stuDao.java
该类用来实现数据库直接操作:
package Dao;
import Status.StuStatus;
import java.sql.*;
import java.util.*;
public class stuDao {
//添加学生信息
public void add(StuStatus stuStatus){
//需要连接数据库,使用sql语句进行添加
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = MySQLUtils.getConn();
String sql = "insert into status_info(id, name, sex, academy, major, grade, classes) values(?,?,?,?,?,?,?)";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, stuStatus.getId());
stmt.setString(2, stuStatus.getName());
stmt.setString(3, stuStatus.getSex());
stmt.setString(4, stuStatus.getAcademy());
stmt.setString(5, stuStatus.getMajor());
stmt.setString(6, stuStatus.getGrade());
stmt.setInt(7, stuStatus.getClasses());
stmt.executeUpdate();
}
catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt);
}
System.out.println("添加成功!");
}
/**修改学生信息
连接数据库,foreach进行sql语句替换*/
//修改学生姓名
public void changeName(Integer changeId,String name) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = MySQLUtils.getConn();
String sql = "update status_info set name = ? where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, name);
stmt.setInt(2, changeId);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt);
}
System.out.println("姓名修改成功!!");
}
//修改学生性别
public void changeSex(Integer changeId,String sex) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = MySQLUtils.getConn();
String sql = "update status_info set sex = ? where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, sex);
stmt.setInt(2, changeId);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt);
}
System.out.println("性别修改成功!!");
}
//修改学生学院
public void changeAcademy(Integer changeId, String academy) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = MySQLUtils.getConn();
String sql = "update status_info set academy = ? where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, academy);
stmt.setInt(2, changeId);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt);
}
System.out.println("学院修改成功!!");
}
//修改学生专业
public void changeMajor(Integer changeId, String major) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = MySQLUtils.getConn();
String sql = "update status_info set major = ? where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, major);
stmt.setInt(2, changeId);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt);
}
System.out.println("专业修改成功!!");
}
//修改学生年级
public void changeGrade(Integer changeId, String grade) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = MySQLUtils.getConn();
String sql = "update status_info set grade = ? where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, grade);
stmt.setInt(2, changeId);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt);
}
System.out.println("年级修改成功!!");
}
//修改学生班级
public void changeClasses(Integer changeId, String classes) {
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = MySQLUtils.getConn();
String sql = "update status_info set classes = ? where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, classes);
stmt.setInt(2, changeId);
stmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt);
}
System.out.println("班级修改成功!!");
}
//删除学生信息
public void delete(Integer deleteId) {
//连接数据库,sql语句删除
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = MySQLUtils.getConn();
String sql = "delete from status_info where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, deleteId);
stmt.executeUpdate();
}
catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt);
}
System.out.println("删除成功!!");
}
//数据库操作
public List<StuStatus> getAll() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List<StuStatus> stuList = new ArrayList<>();
try {
conn = MySQLUtils.getConn();
String sql = "select * from status_info";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
Integer id = rs.getInt("id");
String name = rs.getString("name");
String sex = rs.getString("sex");
String academy = rs.getString("academy");
String major = rs.getString("major");
String grade = rs.getString("grade");
Integer classes = rs.getInt("classes");
StuStatus stuStatus = new StuStatus(id,name, sex, academy, major, grade, classes);
stuList.add(stuStatus);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
MySQLUtils.closeConn(conn, stmt, rs);
}
return stuList;
}
//判断id是否存在
public boolean judgeExist(Integer stuId){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
int count = 0;
try {
conn = MySQLUtils.getConn();
String sql = "select count(*) as count from status_info where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, stuId);
rs = stmt.executeQuery();
while(rs.next()){
count = rs.getInt("count");
}
}
catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt, rs);
}
if(count==0) return false;
else
return true;
}
//数据库查找
public StuStatus find(Integer findId) {
Connection conn = null;
PreparedStatement stmt = null;
StuStatus stuStatus = null;
try{
conn = MySQLUtils.getConn();
String sql = "select * from status_info where id = ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, findId);
ResultSet tmpres = stmt.executeQuery();
stuStatus = new StuStatus();
while(tmpres.next()){
stuStatus.setId(tmpres.getInt("id"));
stuStatus.setName(tmpres.getString("name"));
stuStatus.setSex(tmpres.getString("sex"));
stuStatus.setAcademy(tmpres.getString("academy"));
stuStatus.setMajor(tmpres.getString("major"));
stuStatus.setGrade(tmpres.getString("grade"));
stuStatus.setClasses(tmpres.getInt("classes"));
}
}catch (SQLException e) {
e.printStackTrace();
}
finally{
MySQLUtils.closeConn(conn, stmt);
}
return stuStatus;
}
}
运行调试
目前数据库信息有:
运行程序在控制台输出:
修改部分,考虑到全部修改实际可能性不大,就改成单个信息修改。
该文章内容并非100%来自本人,有借阅其他优秀博客,但是写代码距写博客时间间隔较大,很难找回原来文章链接,如有相似之处请联系修改。
本篇文章也仅仅针对简单的Java+MySQL的增删改查操作,如有写的不正确的地方,欢迎指正。