第15周作业

# 题目

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

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

 # 源代码

　　## Test类

package jdbctext;

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

public class Test {
    public static void main(String[] args) {
        Scanner input = new Scanner(System.in);
        System.out.println("请输入用户名和密码：");
        final String userName = input.next();
        final String passWord= input.next();
        ResultSet rs1;        
        ResultSet rs2;   
        
        DB db = new DB();
        String sql = "select * from t_login where username = '"+userName+"' and password = '"+passWord+"'";
        rs1 = db.executeSelect(sql, args);
        
        try {
            if (rs1.next()) {
                System.out.println("登录成功");
                System.out.println("\nt_user表如下：");
                rs2 = db.executeSelect("select * from t_user", args);  
                
                while(rs2.next()) {
                    int id = rs2.getInt(1);
                    String name = rs2.getString(2);
                    String birthday =rs2.getString(3);
                    System.out.println(id +"\tname:"+name+"\tbirthday:"+birthday);    
                }
                
                System.out.println("请输入需要添加的数据：");
                int id = input.nextInt();
                String newName = input.next();
                String newDate =input.next();
                
                String sql2="insert into t_user(id,name,sex,birthday) "
                        + "values("+id+",'"+newName+"','"+newDate+"')";    
                int n =db.executeModify(sql2, args);    
                if(n>0) {
                    System.out.println("数据插入成功");
                }else {
                    System.out.println("数据插入失败");
                }
            }else {
                System.out.println("登陆失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

　　## BD类

package jdbctext;

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

public class DB {
    Connection con = null;
    PreparedStatement pre = null;
    ResultSet rs = null;
    static DB db = null;
    
    public DB() {            //利用默认构造方法链接数据库
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/testjdbc?serverTimezone=UTC","root","");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
    public static DB getInstance() {    
        if(db == null) {
            db = new DB();
        }
        return db;
    }
    
    public ResultSet executeSelect (String sql, Object[] args) {
        try {
            pre = (PreparedStatement) 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 = (PreparedStatement) 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();
        }
    }
}

　　## 测试结果