public Integer user_id; public String user_name; public String password; public String user_email; public Integer user_role_id; public String friends; public String groups;创建数据库表，以user——id为主键

可以使用如下的SQL语句在数据库中创建一个名为user_info的表，其中user_id为主键：

CREATE TABLE user_info (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(255),
    password VARCHAR(255),
    user_email VARCHAR(255),
    user_role_id INT,
    friends TEXT,
    groups TEXT
);

这样就可以在数据库中创建一个名为user_info的表，其中包含了user_id、user_name、password、user_email、user_role_id、friends和groups七个字段，其中user_id为主键。

相关问题

public class User implements Serializable { private Integer id; //用户id private String name; //用户名称 private String password; //用户密码 private String email; //用户邮箱（用户账号） private String role; //用户角色 private String status; //用户状态 private String hiredate; //信息录入时间 private String departuredate;//毕业时间

这是一段 Java 代码，定义了一个 User 类，并包含了一些属性。其中 id、name、password、email、role、status、hiredate 和 departuredate 分别代表用户的 id、名称、密码、邮箱、角色、状态、信息录入时间和毕业时间。该类实现了 Serializable 接口，表示该类可以被序列化和反序列化。

帮我写一个简单的权限控制项目：springboot+shiro+MyBatis，数据库表1：user(id,username,password),表2：role(id,rolename),表3：permission(id,permission)，需要全部代码

好的，下面是一个简单的权限控制项目的代码，使用springboot+shiro+MyBatis，数据库表1：user(id,username,password),表2：role(id,rolename)，表3：permission(id,permission)。

首先，需要在pom.xml中添加相关依赖：

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<dependency>
    <groupId>org.apache.shiro</groupId>
    <artifactId>shiro-spring</artifactId>
    <version>1.4.2</version>
</dependency>

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

接着，创建数据库表：

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rolename` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `permission` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `permission` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_role` (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `fk_user_role_role_id` (`role_id`),
  CONSTRAINT `fk_user_role_role_id` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_role_user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `role_permission` (
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `fk_role_permission_permission_id` (`permission_id`),
  CONSTRAINT `fk_role_permission_permission_id` FOREIGN KEY (`permission_id`) REFERENCES `permission` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_role_permission_role_id` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后，配置application.yml文件：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/shiro?useUnicode=true&characterEncoding=utf8&useSSL=false
    username: root
    password: 123456
    driver-class-name: com.mysql.jdbc.Driver

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.shiro.entity

server:
  port: 8080

接着，创建实体类：

User.java

public class User implements Serializable {
    private Integer id;
    private String username;
    private String password;
    // getter、setter 略
}

Role.java

public class Role implements Serializable {
    private Integer id;
    private String rolename;
    // getter、setter 略
}

Permission.java

public class Permission implements Serializable {
    private Integer id;
    private String permission;
    // getter、setter 略
}

创建Mapper接口：

UserMapper.java

@Mapper
public interface UserMapper {
    User findByUsername(String username);
    Set<String> findRolesByUsername(String username);
    Set<String> findPermissionsByUsername(String username);
}

RoleMapper.java

@Mapper
public interface RoleMapper {
    Set<String> findRolesById(Integer id);
}

PermissionMapper.java

@Mapper
public interface PermissionMapper {
    Set<String> findPermissionsById(Integer id);
}

创建Mapper.xml文件：

UserMapper.xml

<mapper namespace="com.example.shiro.mapper.UserMapper">
    <select id="findByUsername" parameterType="string" resultType="com.example.shiro.entity.User">
        select * from user where username = #{username}
    </select>

    <select id="findRolesByUsername" parameterType="string" resultType="string">
        select r.rolename from user u
        join user_role ur on u.id = ur.user_id
        join role r on ur.role_id = r.id
        where u.username = #{username}
    </select>

    <select id="findPermissionsByUsername" parameterType="string" resultType="string">
        select p.permission from user u
        join user_role ur on u.id = ur.user_id
        join role r on ur.role_id = r.id
        join role_permission rp on r.id = rp.role_id
        join permission p on rp.permission_id = p.id
        where u.username = #{username}
    </select>
</mapper>

RoleMapper.xml

<mapper namespace="com.example.shiro.mapper.RoleMapper">
    <select id="findRolesById" parameterType="integer" resultType="string">
        select rolename from role where id in (
            select role_id from user_role where user_id = #{id}
        )
    </select>
</mapper>

PermissionMapper.xml

<mapper namespace="com.example.shiro.mapper.PermissionMapper">
    <select id="findPermissionsById" parameterType="integer" resultType="string">
        select permission from permission where id in (
            select permission_id from role_permission where role_id in (
                select role_id from user_role where user_id = #{id}
            )
        )
    </select>
</mapper>

创建Realm类：

UserRealm.java

public class UserRealm extends AuthorizingRealm {
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private RoleMapper roleMapper;
    @Autowired
    private PermissionMapper permissionMapper;

    @Override
    protected AuthorizationInfo doGetAuthorizationInfo(PrincipalCollection principals) {
        SimpleAuthorizationInfo authorizationInfo = new SimpleAuthorizationInfo();
        String username = (String) principals.getPrimaryPrincipal();
        Set<String> roles = userMapper.findRolesByUsername(username);
        Set<String> permissions = userMapper.findPermissionsByUsername(username);
        authorizationInfo.setRoles(roles);
        authorizationInfo.setStringPermissions(permissions);
        return authorizationInfo;
    }

    @Override
    protected AuthenticationInfo doGetAuthenticationInfo(AuthenticationToken token) throws AuthenticationException {
        String username = (String) token.getPrincipal();
        User user = userMapper.findByUsername(username);
        if (user == null) {
            throw new UnknownAccountException();
        }
        SimpleAuthenticationInfo authenticationInfo = new SimpleAuthenticationInfo(user.getUsername(), user.getPassword(), getName());
        return authenticationInfo;
    }
}

创建Controller类：

UserController.java

@RestController
public class UserController {
    @Autowired
    private UserService userService;

    @PostMapping("/login")
    public String login(String username, String password) {
        Subject subject = SecurityUtils.getSubject();
        UsernamePasswordToken token = new UsernamePasswordToken(username, password);
        try {
            subject.login(token);
            return "登录成功";
        } catch (AuthenticationException e) {
            return "登录失败";
        }
    }

    @GetMapping("/logout")
    public String logout() {
        Subject subject = SecurityUtils.getSubject();
        subject.logout();
        return "退出成功";
    }

    @GetMapping("/test")
    public String test() {
        return "测试成功";
    }

    @GetMapping("/admin")
    public String admin() {
        return "admin权限访问成功";
    }

    @GetMapping("/user")
    public String user() {
        return "user权限访问成功";
    }
}

创建Service类：

UserServiceImpl.java

@Service
public class UserServiceImpl implements UserService {
    @Autowired
    private UserMapper userMapper;

    @Override
    public User findByUsername(String username) {
        return userMapper.findByUsername(username);
    }
}

最后，创建启动类：

ShiroApplication.java

@SpringBootApplication
@MapperScan("com.example.shiro.mapper")
public class ShiroApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShiroApplication.class, args);
    }
}

至此，简单的权限控制项目的代码就完成了。