第15周作业

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

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

 

建表sql

create table t_login(
`id` bigint not null AUTO_INCREMENT COMMENT '编号',
`username` varchar(120) not null COMMENT '用户名',
`password` VARCHAR(120) not null COMMENT '密码',
primary key (id),
key idx_name(username)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='登录表';

 

create table t_user(
`id` bigint not null AUTO_INCREMENT COMMENT '编号',
`name` varchar(120) not null COMMENT '姓名',
`sex` VARCHAR(120) not null COMMENT '性别',
`birthday` TIMESTAMP not null COMMENT '生日',
primary key (id),
key idx_name(name)
)ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COMMENT='用户信息表';

 

 

 

 

封装的数据库操作类 DB.class，此处应用的时 mysql8.0.17版本的数据库，连接驱动与连接字符串与mysql5.x版本不同。

package org.ccut.pack_17;

import java.sql.*;

public class DB {
    private Connection con;
    private PreparedStatement pre;
    private ResultSet rs;
    private static DB test;

    static{
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
        }catch(ClassNotFoundException e){
            e.printStackTrace();
        }
    }

    DB(){
        try{
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true","root","20194680");
        }catch(SQLException e){
            e.printStackTrace();
        }
    }

    public static DB getInstabce(){
        if(test==null){
            test=new DB();
        }
        return test;
    }

    public ResultSet executeSelect(String sql,Object[] args){
        try{
            pre=con.prepareStatement(sql);
            if(args.length!=0){
                for(int i=0;i<args.length;i++){
                    pre.setObject(i+1, args[i]);
                }
            }
            rs = pre.executeQuery();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return rs;
    }

    public int executeModify(String sql,Object[] args){
        int n=0;
        try{
            pre=con.prepareStatement(sql);
            if(args.length!=0){
                for(int i=0;i<args.length;i++){
                    pre.setObject(i+1, args[i]);
                }
            }
            n = pre.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return n;
    }

    public void close(){
        try{
            if(rs!=null){
                rs.close();
            }
            pre.close();
            con.close();
        }catch(SQLException e){
            e.printStackTrace();

        }
    }
}

 

 

测试类

 

 

package org.ccut.pack_17;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class MainTest {
    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        String username = input.next();
        String password = input.next();
        ResultSet a;
        ResultSet b;
        DB db = new DB();
        a = db.executeSelect("select * from t_login where username = '" + username + "' and password = '" + password + "'", args);
        try {
            if (a.next()) {
                System.out.println("登录成功");
                System.out.println("t_user表中的信息：");
                String sql = "select * from t_user";
                b = db.executeSelect(sql, args);
                while (b.next()) {
                    int id = b.getInt(1);
                    String name = b.getString(2);
                    String sex = b.getString(3);
                    String birthday = b.getString(4);
                    System.out.println("id:" + id + "\tname:" + name + "\tsex:" + sex + "\tbirthday:" + birthday);
                }
                System.out.println("请输入插入信息：");
                String name1 = input.next();
                String sex1 = input.next();
                String date1 = input.next();
                sql = "insert into t_user(name,sex,birthday) values('" + name1 + "','" + sex1 + "','" + date1 + "')";
                int n = db.executeModify(sql, args);
                if (n > 0) {
                    System.out.println("插入成功");
                } else {
                    System.out.println("插入失败");
                }
            } else {
                System.out.println("登陆失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 

运行结果：

 

 

 

 

 

 

插入信息

 

 

 

 

 

 

 

 

 

 

 

package org.ccut.pack_17;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class MainTest {
    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        String username = input.next();
        String password = input.next();
        ResultSet a;
        ResultSet b;
        DB db = new DB();
        a = db.executeSelect("select * from t_login where username = '" + username + "' and password = '" + password + "'", args);
        try {
            if (a.next()) {
                System.out.println("登录成功");
                System.out.println("t_user表中的信息：");
                String sql = "select * from t_user";
                b = db.executeSelect(sql, args);
                while (b.next()) {
                    int id = b.getInt(1);
                    String name = b.getString(2);
                    int sex = b.getInt(3);
                    String birthday = b.getString(4);
                    System.out.println("id:" + id + "\tname:" + name + "\tsex:" + sex + "\tbirthday:" + birthday);
                }
                System.out.println("请输入插入信息：");
                String name1 = input.next();
                String sex1 = input.next();
                String date1 = input.next();
                sql = "insert into t_user(name,sex,birthday) values('" + name1 + "','" + sex1 + "','" + date1 + "')";
                int n = db.executeModify(sql, args);
                if (n > 0) {
                    System.out.println("插入成功");
                } else {
                    System.out.println("插入失败");
                }
            } else {
                System.out.println("登陆失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}