Mybatis多表映射查询_权限_配置工程
Day53Mybatis
多表关联映射
在每一个实体自身中建立resultMap映射,
通过全类名+sql的id名,对应到一个sql,将查询的结果作为条件,对应到column与property中,
举例
UserMapper2.xml
<resultMap id="base" type="user"> //用户的基本信息映射 </resultMap> <resultMap id="sub" type="user"> //关联订单的基本信息映射 <collection property="对多属性ordersList" select="OrderMapper2名空间+select的id" column="主键字段u_id"/> </resultMap> <select id="sub"> select * from sys_user where u_id=#{uid} </select>OrderMapper2.xml
<resultMap id="base" type="orders"> //订单的基本信息映射 </resultMap> <select id="selectOrders" resultMap="base"> select * from sys_order where u_id=#{uid} </select> <resultMap id="sub" type="orders"> //订单明细关联映射 <collection property="itemsList" select="ItemMapper2.xml的名空间+select的id" column="o_id主键字段"/> </resultMap> <select id="selectOrdersItems" resultMap="sub"> select * from sys_order where u_id=#{uid} </select>ItemMapper2.xml
<resultMap id="base" type="items"> //映射订单明细的基本信息 </resultMap> <select id="" resultMap="base"> select * from sys_item where o_id=#{oid} </select>
角色权限表的建立
用户表user
DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `u_id` int(11) NOT NULL AUTO_INCREMENT, `u_username` varchar(50) DEFAULT NULL, `u_password` varchar(50) DEFAULT NULL, `u_tel` varchar(11) DEFAULT NULL, `u_mail` varchar(25) DEFAULT NULL, PRIMARY KEY (`u_id`) )
角色表roler
DROP TABLE IF EXISTS `roler`; CREATE TABLE `roler` ( `r_id` int(11) NOT NULL AUTO_INCREMENT, `r_name` varchar(10) DEFAULT NULL, PRIMARY KEY (`r_id`) )
用户角色表userroler
DROP TABLE IF EXISTS `userroler`; CREATE TABLE `userroler` ( `ur_id` int(11) NOT NULL AUTO_INCREMENT, `u_id` int(11) DEFAULT NULL, `r_id` int(11) DEFAULT NULL, PRIMARY KEY (`ur_id`), KEY `FK_Reference_7` (`u_id`), KEY `FK_Reference_6` (`r_id`), CONSTRAINT `FK_Reference_6` FOREIGN KEY (`r_id`) REFERENCES `roler` (`r_id`), CONSTRAINT `FK_Reference_7` FOREIGN KEY (`u_id`) REFERENCES `users` (`u_id`) )
权限表permission
DROP TABLE IF EXISTS `permission`; CREATE TABLE `permission` ( `p_id` int(11) NOT NULL AUTO_INCREMENT, `p_name` varchar(10) DEFAULT NULL, PRIMARY KEY (`p_id`) )
角色权限表rolerpermission
DROP TABLE IF EXISTS `rolerpermission`; CREATE TABLE `rolerpermission` ( `rp_id` int(11) NOT NULL AUTO_INCREMENT, `p_id` int(11) DEFAULT NULL, `r_id` int(11) DEFAULT NULL, PRIMARY KEY (`rp_id`), KEY `FK_Reference_4` (`p_id`), KEY `FK_Reference_5` (`r_id`), CONSTRAINT `FK_Reference_4` FOREIGN KEY (`p_id`) REFERENCES `permission` (`p_id`), CONSTRAINT `FK_Reference_5` FOREIGN KEY (`r_id`) REFERENCES `roler` (`r_id`) )
动态sql
根据条件生成sql
单条件判断–if
<delete id="delete" > delete from sys_user where id=1 <if test="arg0!=null"> or name like concat('%',#{arg0},'#') </if> <if test="arg1!=null"> or realname like concat('%',#{arg0},'#') </if> </delete> 接口： int delete(Sring key);
多条件判断–choose/when/otherwise
<choose> <when test="arg0!=null"> or name like concat('%',#{arg0},'#') </when> <when test="arg1!=null"> or realname like concat('%',#{arg0},'#') </when> </choose>
枚举查询–foreach
foreach标签的中参数
collection="集合参数名list/array/collection" item="变量名" open="(" close=")" separator=","
foreach举例
select * from sys_user where u_id in (1,3,5) <select id="" parameterType=""> select * from sys_user where u_id in <foreach item="item" index="index" collection="list" open="开始(" separator=",分隔符" close=")结束"> #{item} </foreach> </select>
解决动态sql语法出错
举例
<update id="update" parameterType="user实体"> update sys_user set u_name=#{uname},u_pass=#{upass},u_realname=#{realname} where u_id=#{uid} </update> 动态sql： <update id="update" parameterType="user实体"> update sys_user set <if test="uname!=null"> u_name=#{uname}, </if> <if test="upass!=null"> u_pass=#{upass}, </if> <if test="realname!=null"> u_realname=#{realname}, </if> where u_id=#{uid} </update>
where标签
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
<select id="select" parmeterType = "user实体"> select * from users <where> <if test="uname!=null"> u_name=#{uname}, </if> <if test="upass!=null"> u_pass=#{upass}, </if> <if test="realname!=null"> u_realname=#{realname}, </if> </where> </select>
set标签
set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）
<update id="update" parameterType="user实体"> update sys_user <set> <if test="uname!=null"> u_name=#{uname}, </if> <if test="upass!=null"> u_pass=#{upass}, </if> <if test="realname!=null"> u_realname=#{realname}, </if> </set> where u_id=#{uid} </update>
trim标签
可以设置开始关键字,比如where,set,
prefixoverride忽略开始/suffixoverride结束时的符号
<update id="update" parameterType="user实体"> update sys_user <trim prefix="set" suffixoverride=","> <if test="uname!=null"> u_name=#{uname}, </if> <if test="upass!=null"> u_pass=#{upass}, </if> <if test="realname!=null"> u_realname=#{realname}, </if> </trim> <trim prefix="where" prefixOverride="and"> <if test="tel!=null"> u_tel=#{tel} </if> <if test="mail!=null"> and u_mail=#{mail} </if> </trim> </update>
maven–web服务器部署
1,创建文件夹
在mian下创建webapp文件夹
在webapp下创建WEB-INF文件夹
WEB-INF中创建web.xml文件
2,配置相关依赖
更改项目
在pom.xml文件夹中
配置
<groupId>原来的包名</groupId>
<artifactId>原来的项目名</artifactId>
<version>1.0-SNAPSHOT</version>
加入下面这配置,更改为web项目
<!--更改当前工程为java web工程-->
<packaging>war</packaging>
加入相关依赖
<dependencies>
<!--mybatis的依赖-->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!--mysql的连接jdbc的依赖-->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.48</version>
</dependency>
<!--测试的依赖-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!--lombok的依赖 -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<!--加入httpServlet的依赖-->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
</dependency>
<!--加入Gson的依赖-->
<!--用于转为对象为json格式,传给前端-->
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.2.4</version>
</dependency>
</dependencies>
3,添加tomcat
1,Edit configurations添加本地tomcat
2,在Depoyment中将项目添加到tomcat中
一般添加war exploded
3,可以适当修改Depoyment中的application context的值
该值用于在浏览器上显示项目以及路径
3,测试项目