第15周作业

一、题目一

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

二、代码实现

package kubiao;

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

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Scanner s=new Scanner(System.in);
        System.out.println("用户名：");
        String username= s.next();
        System.out.println("密码：");
        String password= s.next();
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "root");
            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("输入错误");
        }
        st.close();
        con.close();
        }catch (SQLException e) {
                e.printStackTrace();
            }
         catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
}

三、运行结果截图

四、题目二

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

五、代码实现

　　DB.java

/**
 * 连接数据库依次进行加载JDBC驱动程序
 * 创建数据库连接
 * 创建执行SQL语句的对象 
 * 预处理操作
 * 关闭数据库连接
 */
package xinxi;

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;
    }

    private DB() {
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "root");
        } 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 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) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
    }

　　User.java

/**
 * 创建DB对象，在t_login中验证信息的正确性
 * getString方法获得表内的数据
 * insert into将信息插入表中
 */
package xinxi;

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

public class User {

    public static void main(String[] args) {
        // TODO Auto-generated method stub
         Scanner reader =new Scanner(System.in);
         
         System.out.println("用户名：");
         String name = reader.next();
         System.out.println("密码：");
         String password = reader.next();
         
         DB db = DB.getInstance();
         String sele = "select * from t_login where username=? and password=?";
         Object[] object = {name, password};
         int flag = 0;
         
         try {
             ResultSet rs =db.executeSelect(sele, object);
            if(rs.next()) {
                System.out.println("欢迎！");
                flag = 1;
            }else {
                System.out.println("输入错误");
                flag = 0;
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
         
         System.out.println("表内信息：");
         if(flag == 1) {
         String sele1 = "select * from t_user";
        
          try {
            ResultSet rs1 = db.executeSelect(sele1 , new Object[0]);
            while(rs1.next()) {    
                 String id = rs1.getString(1);
                 String username = rs1.getString(2);
                 String sex = rs1.getString(3);
                 String birthday = rs1.getString(4);
                 System.out.println(" " + id + ", " + username + "," + sex + "," + birthday);
             }
        } catch (SQLException e) {
            e.printStackTrace();
            }
        }
         System.out.println("信息添加：");
         String name1 = reader.next();
         int sex1 = reader.nextInt();
         String birthday1 = reader.next();
         String sele2 = "insert into t_user(username, sex, birthday) values(?, ?, ?)";
         int rs2 = db.executeModify(sele2, new Object[] {name1, sex1, birthday1});
         if(rs2 > 0) {
                System.out.println("修改成功");
            }else {
                System.out.println("修改失败");
            }
         db.close();
    }
}

六、运行结果截图