求助jdbc实现用户权限管理代码编写

Question

请问哪位大神可以帮在下写一个mysql+jdbc的简单的用户权限管理小项目，可以的话
加q1009647267细谈

Answer 1

以下是使用 MySQL 和 JDBC 实现简单用户权限管理的示例项目：

1. 创建相关表结构

在 MySQL 中创建相关用户和权限表结构：

CREATE TABLE users (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL
);

CREATE TABLE roles (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE permissions (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE users_roles (
  user_id INT(11) NOT NULL,
  role_id INT(11) NOT NULL,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users (id),
  FOREIGN KEY (role_id) REFERENCES roles (id)
);

CREATE TABLE roles_permissions (
  role_id INT(11) NOT NULL,
  permission_id INT(11) NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles (id),
  FOREIGN KEY (permission_id) REFERENCES permissions (id)
);

1. 使用 Java 实现用户注册和登录

用户登录和注册操作，可以用 Java 代码实现。下面是一个示例代码：

import java.sql.*;

public class UserAuth {
  
  // 数据库连接信息
  private static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/test";
  private static final String DB_USERNAME = "root";
  private static final String DB_PASSWORD = "password";
  
  // 数据库表名
  private static final String TABLE_NAME = "users";
  
  // 查询用户信息
  public static int[] auth(String username, String password) {
    int[] result = new int[2];
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
      String sql = "SELECT id, password FROM " + TABLE_NAME + " WHERE username = ?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, username);
      rs = pstmt.executeQuery();
      if (rs.next()) {
        if (rs.getString("password").equals(password)) {
          result[0] = rs.getInt("id");
          result[1] = 1;
        } else {
          result[1] = -1;
        }
      } else {
        result[1] = 0;
      }
    } catch (SQLException se) {
      se.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
    return result;
  }
  
  // 添加新用户
  public static boolean register(String username, String password, String email) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    boolean result = false;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
      conn.setAutoCommit(false);
      String sql = "INSERT INTO " + TABLE_NAME + " (username,password,email) VALUES (?,?,?)";
      pstmt = conn.prepareStatement(sql);
      pstmt.setString(1, username);
      pstmt.setString(2, password);
      pstmt.setString(3, email);
      pstmt.executeUpdate();
      conn.commit();
      result = true;
    } catch (SQLException se) {
      try {
        conn.rollback();
      } catch (SQLException sqle) {
        sqle.printStackTrace();
      }
      se.printStackTrace();
    } catch (Exception e) {
      try {
        conn.rollback();
      } catch (SQLException se) {
        se.printStackTrace();
      }
      e.printStackTrace();
    } finally {
      try {
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
    return result;
  }
}

1. 使用 Java 实现角色和权限管理

下面是 Java 代码示例实现角色和权限管理：

import java.sql.*;

public class RoleAuth {
  
  // 数据库连接信息
  private static final String DB_URL = "jdbc:mysql://127.0.0.1:3306/test";
  private static final String DB_USERNAME = "root";
  private static final String DB_PASSWORD = "password";
  
  // 查询用户角色
  public static String[] getRoles(int userId) {
    String[] result = new......

// 角色名称数组
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
      String sql = "SELECT r.name FROM roles r INNER JOIN users_roles ur ON r.id = ur.role_id WHERE ur.user_id = ?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, userId);
      rs = pstmt.executeQuery();
      List<String> list = new ArrayList<>();
      while (rs.next()) {
        list.add(rs.getString("name"));
      }
      result = list.toArray(new String[list.size()]);
    } catch (SQLException se) {
      se.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
    return result;
  }
  
  // 查询用户权限
  public static String[] getPermissions(int userId) {
    String[] result = new String[0];
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      Class.forName("com.mysql.jdbc.Driver");
      conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
      String sql = "SELECT p.name FROM permissions p INNER JOIN roles_permissions rp ON p.id = rp.permission_id INNER JOIN roles r ON rp.role_id = r.id INNER JOIN users_roles ur ON r.id = ur.role_id WHERE ur.user_id = ?";
      pstmt = conn.prepareStatement(sql);
      pstmt.setInt(1, userId);
      rs = pstmt.executeQuery();
      List<String> list = new ArrayList<>();
      while (rs.next()) {
        list.add(rs.getString("name"));
      }
      result = list.toArray(new String[list.size()]);
    } catch (SQLException se) {
      se.printStackTrace();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
        if (conn != null) conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
    return result;
  }
}

使用上面的代码，您可以查询用户的角色和权限信息。请注意，这里可以将用户信息的查询结果作为参数传递给这些方法。

这只是一个基本的示例项目，您可以据此扩展代码以支持更多的用户和权限管理功能。同时，您也可以基于这个项目，使用任何其他框架来实现更强大的用户权限管理系统。