• 每日总结


    链接数据库的代码

    package connection;
    //连接数据库student

    import java.sql.Connection;
    import java.sql.DriverManager;
    //import java.sql.Statement;
    import java.sql.SQLException;

    public class xu{

    private static Connection conn = null;
    static {
    try {
    // 注册驱动
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    // 获得一个数据库连接
    conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=student","sa","123456");
    }catch(ClassNotFoundException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }

    public static Connection getConnection() {
    return conn;
    }
    }

    选课的操作代码

    package connection;

    import java.sql.*;
    import java.util.*;

    public class test1{

    private static String ID; // 此处的ID设为全局变量,在下面某些方法里会用到
    private static Scanner console = new Scanner(System.in);
    private static String managerPass = "123456";
    private static Connection conn = xu.getConnection();


    public static void main(String [] args) throws SQLException {

    System.out.println("Welcome to the student selection system! "
    +"if you are maneger please choose 6、7、8、9、10 "
    +"if you are student Please choose 1、2、3、4、5、10 ");
    ShowMenu();
    }

    public static void ShowMenu() throws SQLException {

    System.out.println("-----------option------------- ");

    System.out.println("1--Student login"); // 学生登录
    System.out.println("2--Change password"); // 学生修改密码
    System.out.println("3--Choose course"); // 学生选课
    System.out.println("4--Get course list"); // 学生查看自己的课程表
    System.out.println("5--Student rigistration"); // 学生注册账号
    System.out.println("6--Create student"); // 管理员创建学生
    System.out.println("7--Create course"); // 管理员创建课表
    System.out.println("8--Get student list"); // 管理员查看学生表
    System.out.println("9--Get course list1"); // 管理员查看选课表里的课程
    System.out.println("10--exit "); // 退出

    Test();
    }



    public static void Test() throws SQLException {

    System.out.println("manager choose 1 ,student choose 0");
    int choose1 = console.nextInt(); //int 用nextInt()

    if(choose1 == 1) {

    System.out.println("Please input your password :");
    String password = console.next(); //String 用next()

    if(password.equals(managerPass)) {

    System.out.println("Welcome manager! ");
    ManagerMenu1();
    }

    else {

    System.out.println("Your password is worry!"); }
    }

    else if(choose1 == 0) {

    System.out.println("Welcome Student!");
    StudentMenu1();
    }
    }



    /*管理员操作菜单*/
    public static void ManagerMenu1() throws SQLException {

    System.out.println("----------Manager---------- ");
    System.out.println("Please choose option");
    int choose2 = console.nextInt();

    switch(choose2) {

    case 1 :
    case 2 :
    case 3 :
    case 4 :
    case 5 :
    System.out.println("This is student`s option,try again!");
    ManagerMenu1();
    break;
    case 6 : CreateStu();break;
    case 7 : CreateCourse();break;
    case 8 : GetStudentList();break;
    case 9 : GetCourseList1();break;
    case 10 : System.exit(0);break;
    default : System.out.println("Please input an right number , try again!"); ManagerMenu1();

    }
    }



    /*学生操作菜单*/
    public static void StudentMenu1() throws SQLException {

    System.out.println("----------Student---------- ");
    System.out.println("Please input option");
    int choose3 = console.nextInt();

    switch(choose3) {

    case 1 : StuLogin();break;
    case 2 : ChangePass();break;

    //在进行Choose course,Get course list之前都要求学生进行StuLogin操作

    case 3 :
    case 4 : System.out.println("you must login before you can do it! ");StuLogin();break;
    case 5 : StuRigistration();break;
    case 6 :
    case 7 :
    case 8 :
    case 9 : System.out.println("This is manager`s option,try again!");StudentMenu1();break;
    case 10 : System.exit(0);break;
    default : System.out.println("Please input an right number,try again!");StudentMenu1();

    }
    }



    /*管理员创建课程*/
    public static void CreateCourse() throws SQLException{

    try {
    /* 通过connection数据库链接对象 创建一个statement对象数据库操作对象,stat对象再获取一个执行sql的语句。
    * stat对象就是java程序与Database的一个连接通道*/

    Statement stat = conn.createStatement();
    String sql1 = "insert into course(couID,couName,couTeacher)values(1,'软件工程','黄伟国')";
    String sql2 = "insert into course(couID,couName,couTeacher)values(2,'数据库','韦美雁')";
    String sql3 = "insert into course(couID,couName,couTeacher)values(3,'算法设计','黎明')";
    String sql4 = "insert into course(couID,couName,couTeacher)values(4,'大学英语','章琴')";
    String sql5 = "insert into course(couID,couName,couTeacher)values(5,'Java','唐雅媛')";

    int a = stat.executeUpdate(sql1);
    int b = stat.executeUpdate(sql2);
    int c = stat.executeUpdate(sql3);
    int d = stat.executeUpdate(sql4);
    int e = stat.executeUpdate(sql5);

    int sum = a + b + c + d + e;
    System.out.println("成功添加"+ sum + "行课程数据");
    ManagerMenu1();

    } catch (SQLException e) {
    // TODO Auto-generated catch block
    // e.printStackTrace();
    System.out.println("Course already exists");

    ManagerMenu1();
    }
    }



    /*管理员创建学生*/
    public static void CreateStu() throws SQLException {


    try {
    Statement stat = conn.createStatement();
    String sql1 = "insert into student(stuID,stuName) Values(20160501,'徐明正')";

    // 这里对学生信息的输入也必须一个一个输入,这是系统的缺陷,问题待解决

    int a = stat.executeUpdate(sql1);
    System.out.println("成功添加"+ a + "行学生数据");
    ManagerMenu1();

    }catch(SQLException e) {
    // TODO Auto-generated catch block
    System.out.println("The student already exists");

    ManagerMenu1();
    }
    }


    /*学生登录*/
    public static void StuLogin() throws SQLException {

    System.out.println("Please input your stuID"); // 输入账号
    ID = console.next();

    System.out.println("Please input your Password"); // 输入密码
    String stuPassword = console.next();

    String stuID = null;
    String Password = null;

    Statement stat ;
    ResultSet rs ;

    try {
    stat = conn.createStatement();
    String sql1 = "select * from login "; /* 从login表中查询其所有属性值,则stuID 和 Password都有了。
    rs是结果集。查询出的记录是一个列表,初始时指针指向的是第一条记录之前的。*/
    boolean flag;
    rs = stat.executeQuery(sql1);

    while(flag = rs.next()) { // rs.next(); //返回值为true or false

    stuID = rs.getString("stuID");
    Password = rs.getString("Password");

    if( ID.equals(stuID) && stuPassword.equals(Password)) // 注意这里,跳出循环的条件很重要

    break;

    // 如果在数据库里没有找到与用户输入的stuID和Password相匹配的stuID和Password,flag则变为false

    }

    if(!flag){ // 当flag 为 false 时

    System.out.println("The ID or Password is worry!Please input your ID and Password again ");
    StuLogin();
    }

    else {
    System.out.println("***"+ID+"***"+stuID +"***"+stuPassword+"***"+Password+"***");

    System.out.println("Login Successful! ");
    System.out.println("------------------Please choose option------------------ "
    // +"If you want to Change Password ,Please choose 2 "
    +"If you want to Choose Course,Please choose 3 "+"If you want to Get Course List,Please choose 4 "
    +"If you want to exit ,Please choose 8 ");

    int choose4 = console.nextInt();

    switch(choose4) {

    // case 2 : ChangePass();break;
    case 3 : ChooseCourse();break;
    case 4 : GetCourseList();break;
    case 10 : System.exit(0);break;
    default : System.out.println("You input an invalid number!");

    }

    }

    } catch (SQLException e) {

    // TODO Auto-generated catch block
    e.printStackTrace();
    }

    }

    /*在这里说明一下 方法execute 、executeQuery、executeUpdate三者的区别
    * 方法executeQuery 用于单个结果集的语句,如select。他会把查询的结果放入ResultSet类对象中供使用。
    * 方法executeUpdate 用于执行insert、update或delete语句<返回值为整数>以及SQL DDL(数据定义语言),如create table 和 drop table <返回值为零>。
    * 方法execute 用于执行返回多个结果集、多个更新计数或二者组合的语句。*/



    /*学生修改密码*/
    public static void ChangePass() throws SQLException {

    // System.out.println(ID);

    /*此处的ID应该必须要重新输入,否则当选择对数据库中某个ID更新的Password进行修改时,没有对应的ID*/

    System.out.println("Please input your ID");
    String ID = console.next();
    System.out.println("Please input your new Password");
    String NewPassword = console.next();

    Statement stat ;

    try {

    stat = conn.createStatement(); // 两种写法都可以
    // stat = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
    String sql ="UPDATE login SET Password =" + "" + NewPassword + " where stuID ="+ ID;

    //注意,此处的NewPassword 和 ID 没有要用''括起来

    // String sql1 ="UPDATE login SET Password = 111 where stuID = 20156022";

    int a = stat.executeUpdate(sql);
    System.out.println(a);

    if(a != 0) {

    System.out.println("成功修改"+a+"行记录");
    System.out.println("Your new Password is "+ NewPassword);

    StudentMenu1();
    }

    else System.out.println(" "+"Changing the Password is failed!");

    }catch(SQLException e) {

    // TODO Auto-generated catch block
    e.printStackTrace();
    }

    }



    /*学生选课*/
    public static void ChooseCourse() throws SQLException {

    System.out.println(ID);
    String couID = null;
    String couName = null;
    String couTeacher = null;

    Statement stat;
    ResultSet rs;

    try {

    stat = conn.createStatement();
    String sql = "select * from course";
    rs = stat.executeQuery(sql);

    System.out.println("--------可选课程的信息-------");
    while(rs.next()) {

    couID = rs.getString("couID");
    couName = rs.getString("couName");
    couTeacher = rs.getString("couTeacher");

    System.out.println(couID + " " + couName + " " + couTeacher);
    }

    System.out.println(" Please choose your course ");

    stat= conn.createStatement();

    // 此处stuID固定位学生登录时所用的ID,这样可以保证登录自己的账号却能选别的账号的课程

    System.out.println("input CourseID");
    int ID1 = console.nextInt();
    int ID2 = console.nextInt();
    int ID3 = console.nextInt();

    // 这样写 ,学生选课的数目必须固定为3门,问题待解决

    String sql1 = "insert into stuCourse(stuID,couID1,couID2,couID3)VALUES(" + ID + ","+ ID1 + "," + ID2 + "," +ID3 +")";
    int i = stat.executeUpdate(sql1);

    if(i != 0) {
    System.out.println("成功选入课程");

    GetCourseList();
    }
    else System.out.println("选课错误");

    } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }



    /*学生获取已选课程列表*/
    public static void GetCourseList() throws SQLException {

    System.out.println(" Here is your course that you have choose ");

    Statement stat = null;
    ResultSet rs = null;

    try {

    stat = conn.createStatement();
    String sql = "select * from stuCourse ";
    rs = stat.executeQuery(sql);

    String stuID = null;
    String couID1 = null;
    String couID2 = null;
    String couID3 = null;
    String couID4 = null;

    while(rs.next()) {

    stuID = rs.getString("stuID");
    couID1 = rs.getString("couID1");
    couID2 = rs.getString("couID2");
    couID3 = rs.getString("couID3");
    couID4 = rs.getString("couID4");


    if(stuID.equals(ID))

    System.out.println(stuID + " " + couID1 + " " + couID2 + " " + couID3 + " " + couID4 + " ");

    }

    }catch(SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }



    /*学生注册*/
    public static void StuRigistration() throws SQLException{

    System.out.println("Please input your ID ");
    String ID = console.next();
    System.out.println("Please input your Password");
    String Password = console.next();

    try {
    Statement stat = conn.createStatement();
    String sql1 = "insert into login(stuID,Password)Values(" + ID + ", " + Password + ")";
    //这里密码只能输入数字,问题待解决

    int a = stat .executeUpdate(sql1);
    System.out.println("成功注册"+ a +"个账号");

    StudentMenu1();

    }catch(SQLException e) {
    // TODO Auto-generated catch block
    System.out.println("ID already exists");
    }
    }


    /* 管理员查看学生表 */
    public static void GetStudentList() {

    System.out.println(" Here is student` information ");

    Statement stat = null;
    ResultSet rs = null;

    try {

    stat = conn.createStatement();
    String sql = "select * from student ";
    rs = stat.executeQuery(sql);

    String stuID = null;
    String stuName = null;

    while(rs.next()) {

    stuID = rs.getString("stuID");
    stuName= rs.getString("stuName");

    System.out.println(stuID + " " + stuName + " ");

    ManagerMenu1();
    }

    }catch(SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }


    /* 管理员查看选课表里的课程*/
    public static void GetCourseList1() {

    System.out.println(" Here is course`information ");

    Statement stat = null;
    ResultSet rs = null;

    try {

    stat = conn.createStatement();
    String sql = "select * from course ";
    rs = stat.executeQuery(sql);

    String couID = null;
    String couName = null;
    String couTeacher = null;


    while(rs.next()) {

    couID = rs.getString("couID");
    couName = rs.getString("couName");
    couTeacher = rs.getString("couTeacher");

    System.out.println(couID + " " + couName + " " + couTeacher + " ");

    ManagerMenu1();
    }

    }catch(SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }
    }

  • 相关阅读:
    ORACLE SQL性能优化系列 (十一)
    ORACLE SQL性能优化系列 (七)
    ORACLE SQL性能优化系列 (十三)
    Oracle绑定变量
    ORACLE SQL性能优化系列 (九)
    C#中&与&&的区别
    简单代码生成器原理剖析
    C#线程系列讲座(1):BeginInvoke和EndInvoke方法
    ClearCanvas DICOM 开发系列 一
    C# winform 获取当前路径
  • 原文地址:https://www.cnblogs.com/ldy2396/p/14218802.html
Copyright © 2020-2023  润新知