第15周作业

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

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 sql {
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Scanner reader=new Scanner(System.in);
        System.out.println("输入username");
        String username=reader.next();
        System.out.println("输入password");
        String password=reader.next();
        try {
            Class.forName("com.mysql.jdbc.Driver");//加载驱动器
            Connection con=DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test","root","0000");//链接数据库
            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) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        

    }

}

运行界面

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

import java.sql.*;
public class DB {
    private static DB db;
    private PreparedStatement pre;
    private ResultSet rs;
    private Connection con;
    public static DB getInstance(){
        if(db==null){
            db=new DB();
        }
        return db;
    }
    DB(){//链接数据库
        try {
            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","0000");
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    static {//加载连接桥
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public ResultSet exResultSet(String sql) {//查询
        try {
            pre=con.prepareStatement(sql);            
            rs=pre.executeQuery();
            if(rs.next()){
                String name=rs.getString(2);//获取结果
                String sex=rs.getString(3);
                String birthday=rs.getString(4);
                System.out.println("姓名: "+name+"   性别："+sex+"   生日："+birthday);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;        
    }
    public void modify(String sql){//插入
        int n=0;
        try {
            pre=con.prepareStatement(sql);            
            n=pre.executeUpdate();
            if(n>0){
                System.out.println("插入成功");
            }else{
                System.out.println("插入失败");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }        
        
    }
    public void login(String sql){//登录
        try {
            pre=con.prepareStatement(sql);
            rs=pre.executeQuery();
            if(rs.next()){
                System.out.println("yes");
            }else{
                System.out.println("no");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }    
        
    }
    public void close() {//关闭数据
        try {
            if(rs != null){
                rs.close();                
            }
            pre.close();
            con.close();                
            }catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }     

}

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class T {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Scanner reader=new Scanner(System.in);
        String sql;
        ResultSet rs;
        System.out.println("输入username");
        String username=reader.next();
        System.out.println("输入password");
        String password=reader.next();        
        sql="select * from t_login where username='"+username+"' and password='"+password+"'";//sql语句
        DB db =new DB();        
        db.login(sql);                
        sql="select * from t_user ";
        rs=db.exResultSet(sql);
        System.out.println("输入姓名 性别 生日");
        String name=reader.next();
        String sex=reader.next();
        String birth=reader.next();
        sql="INSERT INTO t_user ( `username`, `sex`, `birthday`) VALUES ( '"+name+"', '"+sex+"', '"+birth+"'); ";
        db.modify(sql);
        db.close();

    }

}

运行界面