第15周作业

问题描述

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

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

源代码

package fifteen;

import java.sql.*;
import java.util.Scanner;

public class DB {
	public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
	public static final String URL = "jdbc:mysql://localhost:3306/test?user=root&password=123456&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";

	public static void main(String[] args) {
		try {
			Class.forName(DRIVER);
			Connection conn = DriverManager.getConnection(URL);

			System.out.println("请输入要登录的用户名与密码:");
			Scanner scanner = new Scanner(System.in);
			final String userName = scanner.nextLine();
			final String password = scanner.nextLine();
			String findUserByNameAndPwd = "select * from t_login where username=? and password=?";
			PreparedStatement statement = conn.prepareStatement(findUserByNameAndPwd);
			statement.setString(1, userName);
			statement.setString(2, password);
			System.out.println("登录" + (statement.executeQuery().next() ? "成功." : "失败."));

			System.out.print("\nt_user 表如下(ID,名字,性别,生日):\n");
			String findUsers = "select * from t_user";
			statement = conn.prepareStatement(findUsers);
			ResultSet result = statement.executeQuery();
			while (result.next()) {
				String sb = result.getInt("id") + " - " +
					result.getString("name") + " - " +
					(result.getInt("sex") == 1 ? "男" : "女") + " - " +
					result.getInt("birthday");
				System.out.println(sb);
			}

			System.out.print("\n请输入新增用户的信息(名字,性别,生日):\n");
			String addUser = "insert into t_user(name,sex,birthday) values (?,?,?)";
			statement = conn.prepareStatement(addUser);
			statement.setString(1, scanner.nextLine());
			statement.setInt(2, "男".equals(scanner.nextLine()) ? 1 : 0);
			statement.setInt(3, scanner.nextInt());
			statement.execute();
			System.out.println("增加成功.");

			result.close();
			statement.close();
			conn.close();
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
}

CREATE DATABASE IF NOT EXISTS `test`;

USE `test`;

CREATE TABLE IF NOT EXISTS `t_login` (
  `id` INT UNSIGNED AUTO_INCREMENT,
  `username` TEXT NOT NULL,
  `password` TEXT NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_login` (`username`, `password`) VALUES ('SimonMa','123456');

CREATE TABLE IF NOT EXISTS `t_user` (
  `id` INT UNSIGNED AUTO_INCREMENT,
  `name` TEXT NOT NULL,
  `sex` TINYINT NOT NULL,
  `birthday` INT NOT NULL,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_user` (`name`, `sex` , `birthday`) VALUES ('SimonMa',1,19971111);
INSERT INTO `t_user` (`name`, `sex` , `birthday`) VALUES ('Leann',0,19981212);

运行截图