第15周作业

题目一

1. 题目

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

2. 源程序

   Test.java

    

   import java.sql.*;
   import java.util.*;
   
   public class Test {
   
       public static void main(String[] args) throws SQLException {
           Scanner in = new Scanner(System.in);
           Connection con = null;
           PreparedStatement pre = null;
           ResultSet rs = null;
   
           try {
               Class.forName("com.mysql.jdbc.Driver");
               con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
               pre = con.prepareStatement("select * from t_login where username = ? and password = ?");
               System.out.println("请输入用户名：");
               String username = in.next();
               System.out.println("请输入密码：");
               String password = in.next();
               pre.setString(1, username);
               pre.setString(2, password);
               rs = pre.executeQuery();
               if (rs.next()) {
                   System.out.println("登录成功");
               }
   
           } catch (ClassNotFoundException e) {
   
               e.printStackTrace();
           } finally {
               if (rs != null) {
                   rs.close();
               }
               pre.close();
               con.close();
           }
   
       }
   
   }

    

3. 运行结果

    

题目二

1. 题目

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

2. 源程序

   Test.java

    

   import java.util.*;
   import java.sql.*;
   
   public class Test {
   
       public static void main(String[] args) {
           Scanner in = new Scanner(System.in);
           DB db = DB.getInstance();
           
           System.out.println("请输入用户名：");
           String username = in.next();
           System.out.println("请输入密码：");
           String password = in.next() ;
           Object[] args1 = { username, password};
           ResultSet rs1 = db.executeSelect("select * from t_login where username = ? and password = ?", args1);
           
           try {
               if (rs1.next()) {
                   
                   System.out.println("成功");
                   int id = rs1.getInt(1);
                   Object[] args2 = { id };
                   ResultSet rs2 = db.executeSelect("select * from t_user where id = ?", args2);
   
                   if (rs2.next()) {
   
                       System.out.println("姓名：" + rs2.getString("name"));
                       System.out.print("性别：");
                       if (rs2.getShort("sex") == 1) {
                           System.out.println("男");
                       } else {
                           System.out.println("女");
                       }
                       System.out.println("生日：" + rs2.getString("birthday"));
   
                       System.out.println("请输入姓名：");
                       String name = in.next();
                       System.out.println("请输入姓别：");
                       String temp = in.next();
                       int sex;
                       if (temp == "男") {
                           sex = 1;
                       } else {
                           sex = 0;
                       }
                       System.out.println("请输入生日：");
                       String date = in.next();
                       
                       Object[] args3 = { name, sex, date };
                       int n = db.executeModify("insert into t_user (name ,sex, birthday) values(?,?,?)", args3);
                       
                       if(n>0) {
                           System.out.println("增加成功");
                       }
                   }
   
                   rs2.close();
               }
           } catch (SQLException e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
           }
   
       }
   
   }

    

   DB.java

    

   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;
   
       public static DB getInstance() {
           if (db == null) {
               db = new DB();
           }
           return 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", "123456");
           } catch (SQLException 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) {
               e.printStackTrace();
           }
   
       }
   }

    

3. 运行结果