第15周作业

题目1：

编写一个应用程序，输入用户名和密码，访问test数据库中t_login表（字段包括id、username、password），验证登录是否成功。

package ccut.edu.sjk;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class TestA {

    public static void main(String[] args) {
        Scanner in = new Scanner(System.in);
        System.out.print("请输入username：");
        String username = in.nextLine();
        System.out.print("请输入password：");
        String password = in.nextLine();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root","123");
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(
                    "select * from t_login where username = '"+username+"' and password = '"+password+"'");
            if(rs.next()) {
                System.out.println("账号密码正确！");
            }else {
                System.out.println("账号密码错误！");
            }
            if(rs !=null) {
                rs.close();
            }
            st.close();
            con.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

运行结果：

题目2：

在上一题基础上，当登录成功后，将t_user表（id、name、sex、birthday）的信息进行显示（要求使用DB.java完成登录和获取t_user表中数据的操作），最后再对t_user表进行一条记录的添加操作。

源代码：TestB.java

package ccut.edu.cxx;
import java.util.Scanner;

public class TestB {
    String name;
    int sex;
    String birthday;
    public static void main(String[] args) {
        Scanner in = new Scanner(System.in);
        System.out.print("请输入username：");
        String username = in.nextLine();
        System.out.print("请输入password：");
        String password = in.nextLine();
        DB db1 = new DB();
        db1.username = username;
        db1.password = password;
        db1.CxDB();
    }
}

源代码：DB.java

package ccut.edu.cxx;

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 DB {
    String username;//成员变量
    String password;
    public void CxDB() {
        Scanner in = new Scanner(System.in);
        Connection con = null;//声明数据库连接对象
        PreparedStatement pre = null;//声明预处理对象
        try {
            Class.forName("com.mysql.jdbc.Driver");//加载驱动
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root","123");//链接数据库
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery(
                    "select * from t_login where username = '"+username+"' and password = '"+password+"'");
            String sql = null;
            if(rs.next()) {
                System.out.println("账号密码正确！登陆成功！");
                sql = "select * from t_user";
                rs = st.executeQuery(sql);//执行sql语句
                //while循环遍历结果集
                while(rs.next()){
                    String id = rs.getString("id");
                    String name = rs.getString("name");
                    String sex = rs.getInt("sex")==0?"男":"女";//三元运算符判断性别
                    String birthday = rs.getString("birthday");
                    System.out.println("读取信息："+"ID:"+id+".姓名："+name+"，性别："+sex+"，生日："+birthday);//输出信息
                }
                //插入记录操作
                sql = "insert into t_user(name,sex,birthday) values(?,?,?)";//sql预处理语句通配符占位
                pre = con.prepareStatement(sql);//执行sql预处理语句
                //用户输入添加的记录内容
                System.out.println("执行添加记录操作！");
                System.out.print("请输入姓名：");
                String name = in.nextLine();
                System.out.print("请输入性别代号(0代表男，1代表女)：");
                int sex = in.nextInt();
                System.out.print("请输入生日日期：");
                Scanner in1 = new Scanner(System.in);
                String birthday = in1.nextLine();
                pre.setString(1, name);
                pre.setInt(2, sex);
                pre.setString(3, birthday);
                //判断是否成功更新数据库
                int count = pre.executeUpdate();
                if (count>0) {
                    System.out.println("添加成功！");
                }else {
                    System.out.println("添加失败！");
                }
            }else {
                System.out.println("账号密码错误！请重新登录！");
            }
            if(rs !=null) {
                rs.close();//关闭结果集
            }
            st.close();
            con.close();//关闭数据库
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

运行结果：