1.数据库中建立表(user,role,menu,funs,role_menu)
user表
建表:
CREATE TABLE `user` (
`id` varchar(200) NOT NULL DEFAULT '',
`username` varchar(20) DEFAULT NULL,
`password` varchar(20) DEFAULT NULL,
`roleid` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`));
添加数据:
INSERT INTO `user` VALUES ('00001', 'zhangsan', '1231230', '1');
INSERT INTO `user` VALUES ('00002', 'lisi', '123123', '2');
INSERT INTO `user` VALUES ('00003', 'admin', '123', '1');
INSERT INTO `user` VALUES ('00004', 'wangwu', '123', '3');
INSERT INTO `user` VALUES ('00005', 'zhaoyun', '123', '1');
INSERT INTO `user` VALUES ('00006', 'lixin', '123', '1');
INSERT INTO `user` VALUES ('00007', 'zhuge', '123456', '1');
INSERT INTO `user` VALUES ('00009', 'liangliang', '123123', '1');
INSERT INTO `user` VALUES ('b7baf1f948c64b56b7e56803e14fe41d', 'zhaosi', '123123', '3');
role表
建表:
CREATE TABLE `role` (
`roleid` varchar(20) NOT NULL DEFAULT '0',
`rolename` varchar(20) DEFAULT NULL,
PRIMARY KEY (`roleid`));
添加数据:
INSERT INTO `role` VALUES ('1', '管理员');
INSERT INTO `role` VALUES ('2', '普通员工');
INSERT INTO `role` VALUES ('3', '普通用户');
menu表
建表
CREATE TABLE `menu` (
`menuid` varchar(20) NOT NULL DEFAULT '0',
`menuName` varchar(20) DEFAULT NULL,
`menuURL` varchar(200) DEFAULT NULL,
`fatherid` varchar(20) DEFAULT NULL,
PRIMARY KEY (`menuid`));
添加数据:
INSERT INTO `menu` VALUES ('1', '系统菜单', 'null', '-1');
INSERT INTO `menu` VALUES ('2', '用户管理', 'null', '1');
INSERT INTO `menu` VALUES ('3', '添加用户', 'register.jsp', '2');
INSERT INTO `menu` VALUES ('4', '查询用户', 'body.jsp', '2');
INSERT INTO `menu` VALUES ('5', '商品管理', 'null', '1');
INSERT INTO `menu` VALUES ('6', '添加商品', 'addGoods.jsp', '5');
INSERT INTO `menu` VALUES ('7', '查询商品', 'findGoods.jsp', '5');
INSERT INTO `menu` VALUES ('8', '查看购物车', 'null', '1');
INSERT INTO `menu` VALUES ('9', '购物车', 'shopping.jsp', '8');
funs表:
建表:
CREATE TABLE `funs` (
`funid` varchar(20) NOT NULL DEFAULT '0',
`funName` varchar(20) DEFAULT NULL,
`funURL` varchar(200) DEFAULT NULL,
`menuid` varchar(20) DEFAULT NULL,
PRIMARY KEY (`funid`));
添加数据:
INSERT INTO `funs` VALUES ('1', '主页', 'index.jsp', '1');
INSERT INTO `funs` VALUES ('2', '头页面', 'header.jsp', '1');
INSERT INTO `funs` VALUES ('3', '菜单页面', 'menu.jsp', '1');
INSERT INTO `funs` VALUES ('4', '操作页面', 'body.jsp', '1');
INSERT INTO `funs` VALUES ('5', '底页面', 'footer.jsp', '1');
INSERT INTO `funs` VALUES ('50', '添加用户页面', 'register.jsp', '3');
role_menu表
建表:
CREATE TABLE `role_menu` (
`roleid` varchar(20) NOT NULL DEFAULT '0',
`menuid` varchar(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`roleid`,`menuid`));
添加数据:
INSERT INTO `role_menu` VALUES ('1', '1');
INSERT INTO `role_menu` VALUES ('1', '2');
INSERT INTO `role_menu` VALUES ('1', '3');
INSERT INTO `role_menu` VALUES ('1', '4');
INSERT INTO `role_menu` VALUES ('1', '5');
INSERT INTO `role_menu` VALUES ('1', '6');
INSERT INTO `role_menu` VALUES ('1', '7');
INSERT INTO `role_menu` VALUES ('1', '8');
INSERT INTO `role_menu` VALUES ('1', '9');
INSERT INTO `role_menu` VALUES ('2', '1');
INSERT INTO `role_menu` VALUES ('2', '5');
INSERT INTO `role_menu` VALUES ('2', '6');
INSERT INTO `role_menu` VALUES ('2', '7');
INSERT INTO `role_menu` VALUES ('2', '8');
INSERT INTO `role_menu` VALUES ('2', '9');
INSERT INTO `role_menu` VALUES ('3', '1');
INSERT INTO `role_menu` VALUES ('3', '8');
INSERT INTO `role_menu` VALUES ('3', '9');
表与表之间的关系:
user表与role表
一个用户对应一个角色 一对一关系
一个角色对应多个用户 一对多关系role表与menu表
一个角色对应多个菜单 一对多关系
一个菜单对应多个角色 一对多关系menu表与funs表
一个菜单对应多个功能 一对多关系
一个功能对应一个菜单 一对一关系
2.通过maven生成代码:
1)建立一个Maven项目
2)在pom.xml文件中添加所需jar文件的代码
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.open</groupId>
<artifactId>MyBatisManenDemo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>MyBatisManenDemo</name>
<!-- FIXME change it to the project's website -->
<url>http://www.example.com</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.7</maven.compiler.source>
<maven.compiler.target>1.7</maven.compiler.target>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
</dependencies>
<build>
<plugins>
<!-- MyBatis代码生成 -->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</project>
3)在resours中建立配置文件
(1)db.properties文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/demo
jdbc.username=root
jdbc.password=123456
initialSize=10
maxTotal=30
maxIdle=10
minIdle=5
maxWaitMillis=1000
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
removeAbandonedTimeout=1
mode.package=com.openlab.pojo
dao.package=com.openlab.mapper
xml.mapper.package=com.openlab.mapper
target.project=MeMavenDemo
注意 :注意自己数据库的名字和自己建立项目的名字是否与改代码一直,若不同需要修改。
(2)generatorconfig.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<properties resource="db.properties"></properties>
<context id="context1" targetRuntime="Mybatis3">
<jdbcConnection
driverClass="${jdbc.driver}"
connectionURL="${jdbc.url}"
userId="${jdbc.username}"
password="${jdbc.password}"/>
<javaModelGenerator targetPackage="${mode.package}" targetProject="./src/main/java"></javaModelGenerator>
<sqlMapGenerator targetPackage="${xml.mapper.package}" targetProject="./src/main/java"></sqlMapGenerator>
<javaClientGenerator targetPackage="${dao.package}" targetProject="./src/main/java" type="XMLMAPPER">
<property name="enableSubPackages" value="false"/>
</javaClientGenerator>
<table schema="???" tableName="user"
enableSelectByExample="false" enableDeleteByExample="false" enableUpdateByExample="false"
enableCountByExample="false" selectByExampleQueryId="false">
</table>
<table schema="???" tableName="role"
enableSelectByExample="false" enableDeleteByExample="false" enableUpdateByExample="false"
enableCountByExample="false" selectByExampleQueryId="false">
</table>
<table schema="???" tableName="menu"
enableSelectByExample="false" enableDeleteByExample="false" enableUpdateByExample="false"
enableCountByExample="false" selectByExampleQueryId="false">
</table>
<table schema="???" tableName="funs"
enableSelectByExample="false" enableDeleteByExample="false" enableUpdateByExample="false"
enableCountByExample="false" selectByExampleQueryId="false">
</table>
<table schema="???" tableName="role_menu"
enableSelectByExample="false" enableDeleteByExample="false" enableUpdateByExample="false"
enableCountByExample="false" selectByExampleQueryId="false">
</table>
</context>
</generatorConfiguration>
(3)mybatis-config.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource = "db.properties"></properties>
<settings>
<setting name="cacheEnabled" value="true"/>
</settings>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/openlab/mapper/UserMapper.xml"/>
<mapper resource="com/openlab/mapper/RoleMapper.xml"/>
<mapper resource="com/openlab/mapper/RoleMenuMapper.xml"/>
<mapper resource="com/openlab/mapper/MenuMapper.xml"/>
<mapper resource="com/openlab/mapper/FunsMapper.xml"/>
</mappers>
</configuration>
(4)运行generatorconfig.xml文件
3.对生成代码进行修改的代码
1)在User类中添加代码
private Role role;
public Role getRole() {
return role;
}
public void setRole(Role role) {
this.role = role;
}
2)在Role类中添加代码
private List<Menu> menu;
public List<Menu> getMenu() {
return menu;
}
public void setMenu(List<Menu> menu) {
this.menu = menu;
}
3)在Menu类中添加代码
private List<Funs> funs;
public List<Funs> getFuns() {
return funs;
}
public void setFuns(List<Funs> funs) {
this.funs = funs;
}
4) 在UserMapper类中添加一个查询接口
User selectUsername(String username);
5)在UserMapper.xml里面进行修改
(1)修改resultMap标签里面的语句(将原有的改为下面的)
<id column="id" property="id" jdbcType="VARCHAR" />
<result column="username" property="username" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="roleid" property="roleid" jdbcType="VARCHAR" />
<association property="role" javaType="com.openlab.pojo.Role" >
<id column="roleid" property="roleid" jdbcType="VARCHAR" />
<result column="rolename" property="rolename" jdbcType="VARCHAR" />
<collection property="menu" ofType="com.openlab.pojo.Menu">
<id column="menuid" property="menuid" jdbcType="VARCHAR" />
<result column="menuName" property="menuname" jdbcType="VARCHAR" />
<result column="menuURL" property="menuurl" jdbcType="VARCHAR" />
<result column="fatherid" property="fatherid" jdbcType="VARCHAR" />
<collection property="funs" ofType="com.openlab.pojo.Funs">
<id column="funid" property="funid" jdbcType="VARCHAR" />
<result column="funName" property="funname" jdbcType="VARCHAR" />
<result column="funURL" property="funurl" jdbcType="VARCHAR" />
<result column="menuid" property="menuid" jdbcType="VARCHAR" />
</collection>
</collection>
</association>
(2)添加查询语句(通过username查询)
<select id="selectUsername" resultMap="BaseResultMap" parameterType="java.lang.String" >
select
user.*,role.*,menu.*,funs.*
from user,role,menu,role_menu,funs
where user.roleid= role.roleid
and role.roleid=role_menu.roleid and menu.menuid=role_menu.menuid
and menu.menuid = funs.menuid
and user.username = #{user.username,jdbcType=VARCHAR}
</select>
4,测试:
自己建里个包进行测试下
package com.openlab.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Scanner;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.openlab.mapper.FunsMapper;
import com.openlab.mapper.MenuMapper;
import com.openlab.mapper.RoleMapper;
import com.openlab.mapper.UserMapper;
import com.openlab.pojo.Funs;
import com.openlab.pojo.Menu;
import com.openlab.pojo.Role;
import com.openlab.pojo.User;
public class Test {
public static void main(String[] args) throws IOException {
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlsessionfactor = new SqlSessionFactoryBuilder().build(is);
SqlSession session = sqlsessionfactor.openSession();
UserMapper mapper = session.getMapper(UserMapper.class);
System.out.println("请输入你要查询信息的人的姓名:");
Scanner input = new Scanner(System.in);
User user = mapper.selectUsername(input.next());
System.out.println(user + "\n");
Role role = user.getRole();
System.out.println(role + "\n");
List<Menu> list = role.getMenu();
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i) + "\n");
}
for (int i = 0; i < list.size(); i++) {
System.out.println(list.get(i).getFuns() + "\n");
}
}
}
结果: