关于学生管理系统的简单操作（数据库应用）

简易学生信息管理系统


功能：通过此系统可以录入学生信息，查找所有学生，根据学生学号查找对应学生记录,根据id或姓名删除学生和修改学生记录。


要求:
1. 创建数据库scxh.db,建一张学生表students,表字段有:id,姓名(name)，学号(number)

        要求设置id字段为主键并且自动增长.

public class Students {
//数据库连接
public Connection mysqlConnect() throws Exception{
InputStream is = getClass().getClassLoader().getSystemResourceAsStream("jdbc.properties");
Properties ps = new Properties();
ps.load(is);
String driver = ps.getProperty("driver");
String url = ps.getProperty("jdbcUrl");
String user  = ps.getProperty("jdbcUser");
String password = ps.getProperty("jdbcPassword");
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, password);
return con;
}
//插入数据
public void insert(UserInfo ui) throws Exception{
Connection con = mysqlConnect();
String sql = "insert into students values(?,?,?)";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, ui.getId());
ps.setString(2, ui.getName());
ps.setInt(3, ui.getNumber());
ps.executeUpdate();
con.close();
}
//查询学生姓名
public ArrayList<String> selectName() throws Exception{
ArrayList<String> list = new ArrayList<>(); 
Connection con = mysqlConnect();
String sql = "select number from students";
PreparedStatement ps  = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String name = rs.getString("name");
list.add(name+"\t");
}
con.close();
return list;
}
//查询所有学生信息
public void selectAll() throws Exception{
Connection con = mysqlConnect();
String sql = "select * from students";
PreparedStatement ps  = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
System.out.println("姓名"+"\t"+"学号");
while (rs.next()) {
String name = rs.getString("name");
int number = rs.getInt("number");
System.out.println(name+"\t"+number);
}
con.close();
}
//查找所有id
public ArrayList selectAllId() throws Exception{
ArrayList list = new ArrayList<>();
Connection con = mysqlConnect();
String sql = "select * from students";
PreparedStatement ps  = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();

while (rs.next()) {

int number = rs.getInt("id");
list.add(number+"\t");
}

con.close();
return list;
}
//查找最后一个id
public int selectLastId() throws Exception{
ArrayList list = new ArrayList<>();
Connection con = mysqlConnect();
String sql = "select * from students";
PreparedStatement ps  = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
int number = 0;
while (rs.next()) {

number = rs.getInt("id");

}

con.close();
return number;
}
//删除记录
public void delete(String name) throws Exception{
Connection con = mysqlConnect();
String sql = "delete from students where name = '"+name+"'";
Statement sts = con.createStatement();
sts.executeUpdate(sql);
con.close();
}
//修改记录
public void alter(String name ,int num) throws Exception{
Connection con = mysqlConnect();
String sql = "update students set number = '"+num+"'where name = '"+name+"'";
Statement stsm = con.createStatement();
stsm.executeUpdate(sql);
con.close();
}
}

public static void main(String[] args) {
// TODO Auto-generated method stub
//登录前查询ID编号
Students st = new Students();
int num = 0;
try {
num = st.selectLastId();
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
Scanner sc = new Scanner(System.in);
System.out.println("欢迎进入学生管理系统");
System.out.println("请选择     1，插入学生信息    2，查询所有学生信息    3，修改学生信息   4，删除学生信息   5，退出");
while(true){
String grade = sc.next();

if (grade.equals("1")) {
num++;
System.out.println("请输入姓名");
String name = sc.next();
System.out.println("请输入学号");
int number = sc.nextInt();
UserInfo ui = new UserInfo(num, name, number);
try {
st.insert(ui);

} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
System.out.println("插入成功");
System.out.println("请选择操作项目");
continue;
}
}

if (grade.equals("2")) {
try {
st.selectAll();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
System.out.println("请选择操作项目");
continue;
}
}

if (grade.equals("3")) {
ArrayList<String> list = null;
try {
list = st.selectName();
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
System.out.println("请输入姓名");
String name = sc.next();
System.out.println("请输入修改后的学号");
int number = sc.nextInt();
if (!list.contains(name)) {
System.out.println("无此学生信息");
System.out.println("请选择操作项目");
continue;
}
try {
st.alter(name, number);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
System.out.println("修改成功");
System.out.println("请选择操作项目");
continue;
}
}

if (grade.equals("4")) {
ArrayList<String> list = null;
try {
list = st.selectName();
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
System.out.println("请输入姓名");
String name = sc.next();
if (!list.contains(name)) {
System.out.println("无此学生信息");
System.out.println("请选择操作项目");
continue;
}
try {
st.delete(name);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
System.out.println("删除成功");
System.out.println("请选择操作项目");
continue;
}
}

if (grade.equals("5")) {
System.out.println("谢谢使用！");
System.exit(0);
}
}
}
}

public class UserInfo {}//用户信息的get和set方法可自行添加

才运用写数据库，还有很多需要改进的地方，若有好的改进方法，希望大家提出，非常感激。