第15周作业

题目1：

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

二、源代码：

package cn.edu.ccut;
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 Test1{ 
public static void main(String[]args){
    Scanner reader=new Scanner(System.in);
    System.out.println("输入用户名和密码");
    String username=reader.next();
    String password=reader.next();
    try{
        Class.forName("com.mysql.jdbc.Driver");
        Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","1234");
        Statement st=con.createStatement();
        ResultSet rs=st.executeQuery("select * from t_login where username='"+username+"' and password='"+password+"'");
        if(rs.next()){
            System.out.println("登陆成功");
        }else{
            System.out.println("登录失败");
        }
        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表进行一条记录的添加操作。

二、源代码：

1.DB.java

package cn.edu.ccut;
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;

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

    private DB() {
        try {
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test", "root", "1234");
        } 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();
        }
    }
}

2.Test2

package cn.edu.ccut;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Test2 {

    public static void main(String[] args) {
        Scanner reader = new Scanner(System.in);
        System.out.println("输入用户名和密码");
        String username = reader.next();
        String password = reader.next();
        DB db = DB.getInstance();
        try {
            ResultSet rs = db.executeSelect("select * from t_login where username='" + username+ "' and password='" + password + "'",new Object[] {});
            if (rs.next()) {
                System.out.println("登陆成功");
                
                System.out.println("下列是t_user表里面的信息:");
                ResultSet rs1 = db.executeSelect("select * from t_user",new Object[] {});
                while (rs1.next()) {
                    System.out.println("id:'"+rs1.getString(1)+"'\t\tname:'"+rs1.getString(2)+"'\tsex:'"+rs1.getString(3)+"'\t\tbirthday:'"+rs1.getString(4)+"'");
                }
                System.out.println("请添加用户的姓名、性别、生日:");
                String name=reader.next();
                int sex=reader.nextInt();
                String birthday=reader.next();
                int rs2 = db.executeModify("insert into t_user(name,sex,birthday) values('"+name+"','"+sex+"','"+birthday+"')", new Object[]{});
                if (rs2>0) {
                    System.out.println("添加成功！");
                }else{
                    System.out.println("添加失败！");
                }    
            } else {
                System.out.println("登录失败");
            }
        } catch (Exception e) {
            // TODO: handle exception
            System.out.println("抛出异常");
        }
        
        db.close();

    }
}

三、运行结果：