第15周作业

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

import java.sql.*;
import java.util.*;
public class Test {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Scanner input=new Scanner(System.in);
        System.out.println("输入姓名：");
        String username=input.next();
        System.out.println("输入密码：");
        String password=input.next();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test","root","123456");
            Statement st=con.createStatement();
            ResultSet rs=st.executeQuery(
                    "select * from t_login where username='"+username+"' and password='"+password+"'");//sql语句
            if(rs.next()){
                System.out.println("yes!");
            }else{
                System.out.println("no!");
            }
            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表进行一条记录的添加操作。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DB {
    private Connection con;
    private PreparedStatement pre;
    private ResultSet rs;
    
    private static DB db;
    public static DB getInstance() {
        if(db ==null) {
            db = new DB();
        }
        return db;
        
    }


    DB(){
        try {
       
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test","root", "123456");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    

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

    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 executeUpdate(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();
            }
        
    }

}

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

public class Test3 {

    public static void main(String[] args) {
        Scanner input =new Scanner(System.in); 
        
        System.out.println("请输入名字");
        String  name = input.next();
        System.out.println("请输入密码");
        String password = input.next();
        DB test1  = new DB();
        DB test2  = new DB();
        Object[] object = {name,password};
        int getjudge = 0;
        String Select1 = "select * from t_login where username = ? and password = ?";
        try {
            ResultSet result1 =test1.executeSelect(Select1,object);
            if(result1.next()) {
                System.out.println("yes");
                getjudge = 1;
            }
            else {
                System.out.println("no");
                getjudge = 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        System.out.println("信息如下");
        if(getjudge == 1) {
             String Select2 = "select * from t_user";
             Object[] object2 = new Object[0];
             try {
                 ResultSet result2 =test2.executeSelect(Select2,object2);
                 while(result2.next()) {    
                     String getid = result2.getString(1);
                     String getusername = result2.getString(2);
                     String getsex = result2.getString(3);
                     String getbirthday = result2.getString(4);
                     System.out.println("-" + getid + "--" + getusername + "--" + getsex + "--" + getbirthday+"-");
                 }
            } catch (SQLException e) {
                e.printStackTrace();
            }
             System.out.println("添加记录:");
             String newName1 = input.next();
             String newSex1 = input.next();
             String newBirthday1 = input.next();
             Object[] object3 = {newName1,newSex1,newBirthday1};
             String Select = "insert into t_user (Name,Sex,Birthday) values(?,?,?)";
             int result3 =test2.executeUpdate(Select, object3);
             if(result3 > 0) {
                 System.out.println("修改成功!");
             }else {
                 System.out.println("修改失败!");
             }
        }       
        
        

        test1.close();
        test2.close();
        
    }

}

修改前：

修改后：