十五次作业

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

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

 

package shiwuzuoye;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class zhu {

    public static void main(String[] args) {
        Scanner r =new Scanner(System.in);
        System.out.println("请输入用户名，密码");
         String username=r.next();
         String password=r.next();
         Object [] a={username,password};
         DB d=DB.getInstance();
         String sql = "select * from tlogin where username=? and password=?";
        
     
         ResultSet rs=d.executeSelect(sql,a);
         try {
            if(rs.next()){
                  System.out.println("登录成功\r");
                  String user="select * from tuser";
                  System.out.println("显示tuser表");
                  ResultSet usertable=d.executeSelect(user,args);
                  System.out.println("id\t"+"name\t"+"sex\t"+"birthday\t");
                    while(usertable.next()){
                        int id=usertable.getInt(1);
                        String name=usertable.getString(2);
                        String sex="女";
                        int temp=usertable.getInt(3);
                        if(temp==1){
                            sex="男";
                        }
                        
                        String birthday=usertable.getString(4);
                        System.out.println(id+"\t"+name+"\t"+sex+"\t"+birthday+"\t");
                    }
                    
                    System.out.println("添加新记录：");
                    String name=r.next();
                    int sex=r.nextInt();//0代表女，1代表男
                    String birthday=r.next();
                    String insert="insert into tuser(name,sex,birthday) values(? , ?, ?)";
                    Object[] in={name,sex,birthday};
                    int n=d.executeModify(insert, in);
                    if(n>0){
                        System.out.println("添加成功");
                    }
                        
                    else
                        System.out.println("添加失败");
                 
             }
             else
                    System.out.println("登录失败");
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally{
            d.close();
        }

    }

}

package shiwuzuoye;
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", "");
    } 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();
    }
}


}