JDBC简单学生管理系统

//首先新建一个stu表，并添加相应的字段约束；

--创建学生表;
create table stu(
id number(20),
name varchar2(10),
sex char(2),
age number(10),
score number(10),
password number(10)
);
--给学生表字段添加约束;
--id 添加主键
alter table stu add primary key (id);
--name 不能为空
alter table stu modify name not null;
--性别为男或女
alter table stu add check(sex='男' or sex='女');
--年龄大于0
alter table stu add check(age>0);
--分数是0-100之间
alter table stu add check(score>=0 and score<=100);
--密码不能为空
alter table stu modify password not null;

//之后编写如下学生登录和注册等相应功能;

package jdbc.eduask.homework;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

//新建一个学生管理系统类;
public class StuManagerSystem {
public static void main(String[] args) throws Exception {
//JDBC的连接;
String user = "scott";// 数据库用户名;
String password = "tiger";// 数据库用户名密码;
String drivername = "oracle.jdbc.driver.OracleDriver";// 驱动包类的名字;
String url = "jdbc:Oracle:thin:@localhost:1521/orcl";// 数据库地址;
Connection conn = null;
PreparedStatement ps = null;
Statement stm=null;
Class.forName(drivername);// 通过反射机制加载驱动包类;
conn = DriverManager.getConnection(url, user, password);// 连接数据库;
while(true){
System.out.println("----------欢迎进入学生管理系统----------");
System.out.println("注册请按 1 登录请按2");// 用户选择登录和注册;
Scanner input = new Scanner(System.in);
int userInput = input.nextInt();
if (userInput == 1) {
// 选择1时调用用户注册功能;
new StuManagerSystem().userAdd(ps, conn,stm);
}
if (userInput == 2) {
// 选择2调用用户登录功能;
new StuManagerSystem().userLogin(ps, conn,stm);
}
}
}
// 用户注册信息写入到数据中去;
private void userAdd(PreparedStatement ps, Connection conn,Statement stm)
throws Exception {
/*
* id number(20), name varchar2(10), sex char(2), age number(10), score
* number(10), password number(10)
*/
Scanner input = new Scanner(System.in);
System.out.println("请输入学生编号");
int stuId = input.nextInt();
System.out.println("请输入学生姓名");
String stuName=input.next();
System.out.println("请输入学生性别");
String stuSex=input.next();
System.out.println("请输入学生年龄");
int stuAge=input.nextInt();
System.out.println("请输入学生分数");
int stuScore=input.nextInt();
System.out.println("请输入学生密码");
int stuPassword=input.nextInt();
//学生的添加;
String sql = "insert into stu values(?,?,?,?,?,?)";
ps = conn.prepareStatement(sql);
stm=conn.createStatement();
// 将用户输入的信息添加到数据库中;
ps.setObject(1, stuId);
ps.setObject(2, stuName);
ps.setObject(3,stuSex);
ps.setObject(4,stuAge);
ps.setObject(5,stuScore);
ps.setObject(6,stuPassword);
boolean isInserted = ps.execute();// 执行语句
boolean is=stm.equals(sql);
System.out.println("用户注册成功");
}

// 用户登录;
private void userLogin(PreparedStatement ps, Connection conn,Statement stm)
throws Exception {
Scanner input = new Scanner(System.in);
System.out.println("请输入学生姓名");
String stuLoginName=input.next();
System.out.println("请输入学生密码");
int stuLoginPassword=input.nextInt();
//用户登录的条件;
String sql = "select count(*) from stu where name=? and password=? ";
ps = conn.prepareStatement(sql);
stm=conn.createStatement();
// 查询用户输入的信息到数据库中;
ps.setObject(1, stuLoginName);
ps.setObject(2, stuLoginPassword);
ResultSet rs = ps.executeQuery();
// 统计查询的结果,如果不存在，则返回小于0，如果存在则返回大于0;
int count = 0;
if (rs.next()) {
count = rs.getInt(1);// 显示第一列的数;
}
if (count == 0) {
System.out.println("对不起，该学生信息不存在！");
} else {
System.out.println("恭喜你登录成功");
System.out.println("查询该同学信息 请输入1 其他业务请按2");
Scanner input1=new Scanner(System.in);
int choice=input1.nextInt();
if(choice==1){
String sql1="select id,name,sex,age,score,password from stu where name= '"+stuLoginName + "'";
ResultSet rs1=stm.executeQuery(sql1);
System.out.println("ID NAME SEX AGE SCORE PASSWORD");
while(rs1.next()){
System.out.print(rs1.getString("id")+" "+rs1.getString("name")+" "+rs1.getString("sex")+" "+rs1.getString("age")+" "+rs1.getString("score")+" "+rs1.getString("password")+"\n");
}
}
if(choice==2){
System.out.println("修改学生姓名请输入1 性别请输入2 年龄请输入3 分数请输入4 密码请输入5");
Scanner inputSet=new Scanner(System.in);
int num=inputSet.nextInt();
switch(num){
case 1:
System.out.println("请输入新的学生姓名");
Scanner inputNewStuName=new Scanner(System.in);
String newStuName=inputNewStuName.next();
String sqlNewName="update stu set name= '"+newStuName+"' where name= '"+stuLoginName+"'";
ResultSet rs2=stm.executeQuery(sqlNewName);
System.out.println("名字更改成功！");
case 2:
System.out.println("请输入新的学生性别");
Scanner inputNewStuSex=new Scanner(System.in);
String newStuSex=inputNewStuSex.next();
String sqlNewSex="update stu set sex= '"+newStuSex+"' where name= '"+stuLoginName+"'";
ResultSet rs3=stm.executeQuery(sqlNewSex);
System.out.println("性别更改成功！");
break;
case 3:
System.out.println("请输入新的学生年龄");
Scanner inputNewStuAge=new Scanner(System.in);
int newStuAge=inputNewStuAge.nextInt();
String sqlNewAge="update stu set age= "+newStuAge+" where name= '"+stuLoginName+"'";
ResultSet rs4=stm.executeQuery(sqlNewAge);
System.out.println("年龄更改成功！");
break;
case 4:
System.out.println("请输入新的学生分数");
Scanner inputNewStuScore=new Scanner(System.in);
int newStuScore=inputNewStuScore.nextInt();
String sqlNewScore="update stu set score= "+newStuScore+" where name= '"+stuLoginName+"'";
ResultSet rs5=stm.executeQuery(sqlNewScore);
System.out.println("分数更改成功！");
break;
case 5:
System.out.println("请输入新的学生密码");
Scanner inputNewPassword=new Scanner(System.in);
int newStuPassword=inputNewPassword.nextInt();
String sqlNewPassword="update stu set password= "+newStuPassword+" where name= '"+stuLoginName+"'";
ResultSet rs6=stm.executeQuery(sqlNewPassword);
System.out.println("密码更改成功！");
break;
}
}
}
}
}