第15周作业

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

一、代码

package nznd;

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 Try {

    public static void main(String[] args) {
        Scanner scan=new Scanner(System.in);
        System.out.println("请输入用户名和密码:");
        String username=scan.nextLine();
        String password=scan.nextLine();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mess","root","");//创建数据库连接
            Statement st=con.createStatement();//创建sql语句对象
            ResultSet rs=st.executeQuery("select * from t_login1 where username= '"+username+"' and "
                                          + "password = '"+password+"'");//查询t_login1表数据
            if(rs.next()) {
                System.out.print("登录成功\n");
            }else {
                System.out.print("登录失败\n");
            }
            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表进行一条记录的添加操作。

一、代码

package nznd;

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

public class Test {

    public static void main(String[] args) {
        DB db=DB.getInstance();
        Scanner scan=new Scanner(System.in);
        System.out.println("请输入用户名和密码：");
        String username=scan.nextLine();
        String password=scan.nextLine();
        ResultSet rs=db.executeSelect("select * from t_login1 where username=? and password=?",new Object[] {username,password});
        try {
            if(rs.next()) {
                System.out.println("登录成功！\n"+"t_user表的内容是\n");
                ResultSet rs1=db.executeSelect("select * from t_user", new Object[] {});
                while(rs1.next()) {
                    String name=rs1.getString(2);
                    String sex=rs1.getString(3);
                    String birthday=rs1.getString(4);
                    System.out.println("name:"+name);
                    System.out.println("sex:"+sex);
                    System.out.println("birthday:"+birthday);
                    }
                System.out.println("请输入你的name,sex,birthday");
                String name=scan.nextLine();
                String sex=scan.nextLine();
                String birthday=scan.nextLine();
                int count=db.executeModify("insert into t_user(name,sex,birthday)values(?,?,?)",new Object[] {name,sex,birthday});
                if(count>0) {
                    System.out.println("更新成功");
                }else {
                    System.out.println("更新失败");
                }
            }
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        db.close();

    }

}

package nznd;

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

public class DB {
    private static DB db;
    private Connection con;
    private PreparedStatement pre;
    private ResultSet rs;
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    private DB() {
        try {
            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mess","root","");
        } 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 = 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();
        }
    }
}

二、运行结果