Mybatis多表查询（四表查询）通过Maven生成代码

最新推荐文章于 2024-06-02 12:37:41 发布

[(-o-)]

最新推荐文章于 2024-06-02 12:37:41 发布

阅读量392

点赞数

分类专栏： javaweb 文章标签： java

本文链接：https://blog.csdn.net/lijin786/article/details/107685275

版权

javaweb 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

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");
		}
	}
		
}

结果：

[(-o-)]

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Mybatis多表查询（四表查询）通过Maven生成代码

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`));...
复制链接

扫一扫

专栏目录