数据库名:test。
使用给定sql文件恢复数据的表userinfo和userdesp。
使用JDBC实现如下数据操作:
- 从控制台输入一个用户姓名和密码;
- 验证该用户是否存在,密码是否正确,分别返回验证信息;
- 如果用户不存在,但是在userdesp表有该用户记录,则在userinfo插入该用户信息,初始密码123456,用户类型从控制台输入,显示更新成功提示信息。
- 时间:60分钟
代码粘贴区:
import java.sql.*;
import java.util.Scanner;
public class UserInfoManager {
// JDBC连接信息
private static final String JDBC_URL = "jdbc:mysql://localhost/mysql";
private static final String JDBC_USER = "root";
private static final String JDBC_PASSWORD = "123456";
public static void main(String[] args) {
try {
// 连接数据库
Connection connection = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD);
System.out.println("成功连接到数据库!");
// 从控制台输入用户姓名和密码
String userName = inputUserNameFromConsole();
String password = inputPasswordFromConsole();
// 验证用户是否存在且密码是否正确
boolean isUserExists = checkUserExists(connection, userName, password);
if (isUserExists) {
System.out.println("用户 " + userName + " 存在且密码正确!");
} else {
// 在userdesp表中查找用户记录
boolean hasUserDespRecord = checkUserDespRecord(connection, userName);
if (hasUserDespRecord) {
// 在userinfo表中插入用户信息
String userType = inputUserTypeFromConsole();
String uid = inputUidFromConsole();
insertUserInfo(connection,uid,userName, password, userType);
System.out.println("成功插入用户信息!");
} else {
System.out.println("用户 " + userName + " 不存在且无userdesp记录!");
}
}
// 关闭数据库连接
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 从控制台输入用户名
private static String inputUserNameFromConsole() {
System.out.print("请输入用户名:");
Scanner scanner = new Scanner(System.in);
return scanner.nextLine();
}
// 从控制台输入密码
private static String inputPasswordFromConsole() {
System.out.print("请输入密码:");
Scanner scanner = new Scanner(System.in);
return scanner.nextLine();
}
// 验证用户是否存在且密码是否正确
private static boolean checkUserExists(Connection connection, String userName, String password) throws SQLException {
String sql = "SELECT * FROM userinfo WHERE username = ? AND userpasswd = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, userName);
statement.setString(2, password);
ResultSet resultSet = statement.executeQuery();
return resultSet.next();
}
// 在userdesp表中查找用户记录
private static boolean checkUserDespRecord(Connection connection, String uid) throws SQLException {
String sql = "SELECT * FROM userdesp WHERE uid = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, uid);
ResultSet resultSet = statement.executeQuery();
return resultSet.next();
}
// 从控制台输入用户类型
private static String inputUserTypeFromConsole() {
System.out.print("请输入用户类型:");
Scanner scanner = new Scanner(System.in);
return scanner.nextLine();
}
private static String inputUidFromConsole() {
System.out.print("请输入用户id:");
Scanner scanner = new Scanner(System.in);
return scanner.nextLine();
}
// 在userinfo表中插入用户信息
private static void insertUserInfo(Connection connection,String uid, String userName, String password, String userType) throws SQLException {
String sql = "INSERT INTO userinfo (uid,username, userpasswd, usertype) VALUES (?,?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, uid); // 或者设置为一个有效的uid值
statement.setString(2, userName);
statement.setString(3, password);
statement.setString(4, userType);
statement.executeUpdate();
}
}
运行结果截图: