第15周作业

题目：

编写一个应用程序，输入用户名和密码，访问test数据库中t_login表（字段包括id、username、password），验证登录是否成功。在上一题基础上，当登录成功后，将t_user表（id、name、sex、birthday）的信息进行显示（要求使用DB.java完成登录和获取t_user表中数据的操作），最后再对t_user表进行一条记录的添加操作。

代码：

DB类：

package 第15周作业;

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

import com.mysql.cj.xdevapi.Statement;
/**
 * 封装连接数据库以及增删改查操作。
 *
 */
public class DB {
    private static DB db;
    private Connection con;
    private PreparedStatement ps;
    private ResultSet rs;
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static DB getInstance() {
        if (db == null) {
            db = new DB();
        }
        return db;
    }

    private DB() {// 通过构造方法创建数据库链接
        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/java?serverTimezone=UTC", "root", "");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public ResultSet DBQuery(String sql, Object[] field) {
        try {
            ps = con.prepareStatement(sql);
            if (field.length > 0) {
                for (int i = 0; i < field.length; i++) {
                    ps.setObject(i + 1, field[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public ResultSet DBQuery(String sql) {
        try {
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public int DBModify(String sql, Object[] field) {
        int n = 0;
        try {
            ps = con.prepareStatement(sql);
            if (field.length > 0)
                for (int i = 0; i < field.length; i++) {
                    ps.setObject(i + 1, field[i]);
                }
            n = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return n;
    }

    public void close() {
        try {
            if (rs != null) {
                rs.close();
            }
            ps.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

测试类：

package 第15周作业;

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

import javax.swing.plaf.synth.SynthStyle;
import javax.xml.crypto.Data;

/**
 * 
 * 
 * 调用数据库进行增删改查
 *
 */
public class Test {

    public static void main(String[] args) {
        DB db = DB.getInstance();
        boolean flag = false;
        Scanner scan = new Scanner(System.in);
        String[] user = new String[2];
        user[0] = scan.next();
        user[1] = scan.next();
        String sql = "SELECT * FROM t_login where username = ? AND password=?";
        ResultSet rs = db.DBQuery(sql, user);
        try {
            if (rs.next()) {
                System.out.println("登录成功！");
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        if (flag) {
            sql = "select id,name,CASE sex WHEN 0 THEN '男' ELSE '女' END as sex,birthday from t_user";
            rs = db.DBQuery(sql);
            try {
                while (rs.next()) {
                    System.out.print(rs.getInt(1));
                    System.out.print("  ");
                    System.out.print(rs.getString(2));
                    System.out.print("  ");
                    System.out.print(rs.getString(3));
                    System.out.print("  ");
                    System.out.println(rs.getString(4));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            String name = "a";
            int sex = 0;
            String date = "1998-01-01";
            sql = "insert into t_user (name,sex,birthday)values(?,?,?)";
            if (db.DBModify(sql, new Object[] { name, sex, date }) > 0) {
                System.out.println("更新成功");
            } else {
                System.out.println("数据未更改");
            }
        } else {
            System.out.println("账号或密码错误");
        }

    }

}