//首先新建一个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; } } } } }
