IMOOC：http://www.imooc.com/video/4780
previous: MyBatis_3_SQL动态拼接next:MyBatis_5_容易混淆的概念
---5-1 一对多关系配置1--------------------------------------------------------------
getter and setter does not create,for space saving.
command
package com.imooc.bean; import java.util.List; /** * 与指令表对应的实体类 */ public class Command { /** * 主键 */ private String id; /** * 指令名称 */ private String name; /** * 描述 */ private String description; /** * 一条指令对应的自动回复内容列表 */ private List<CommandContent> contentList; }
commandcontent
package com.imooc.bean; /** * 与指令内容表对应的实体类 */ public class CommandContent { /** * 主键 */ private String id; /** * 自动回复的内容 */ private String content; /** * 关联的指令表主键 */ private String commandId; }
---5-1 一对多关系配置2--------------------------------------------------------------
一对多：一command 主表 ，多commandContent 子表。
command配置文件：
1. 集合标签<collection>。<collection property="contentList" resultMap="CommandContent.Content"/>
引用其他文件的ID，写法如下nameSpace.id
2. select a.ID C_ID,a.NAME,a.DESCRIPTION,b.ID,b.CONTENT,b.COMMAND_ID
from COMMAND a left join COMMAND_CONTENT b
on a.ID=b.COMMAND_ID
注意：left join 见<mysql left join 左连接查询关联n多张表>
以ID为例SQL的查询结果集的列名与resultMap的Colom的名必须一致，如select a.ID test //test为别名
<id column="test" jdbcType="INTEGER" property="id"/>
注意2： （如select a.ID 时候，a.不作为列名的一部分)select a.ID ,a.NAME,a.DESCRIPTION,b.ID
所以此时a.ID 和b.ID就有问题，已经重复了，而SQL中不可以出现colom重复
改为 select a.ID C_ID,a.NAME, C_ID为a.ID别名
<id column="C_ID" jdbcType="INTEGER" property="id"/>
*********
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Command">
<resultMap type="com.imooc.bean.Command" id="Command">
<id column="C_ID" jdbcType="INTEGER" property="id"/>
<result column="NAME" jdbcType="VARCHAR" property="name"/>
<result column="DESCRIPTION" jdbcType="VARCHAR" property="description"/>
<collection property="contentList" resultMap="CommandContent.Content"/>
</resultMap>
<select id="queryCommandList" parameterType="com.imooc.bean.Command" resultMap="Command">
select a.ID C_ID,a.NAME,a.DESCRIPTION,b.ID,b.CONTENT,b.COMMAND_ID
from COMMAND a left join COMMAND_CONTENT b
on a.ID=b.COMMAND_ID
<where>
<if test="name != null and !"".equals(name.trim())">
and a.NAME=#{name}
</if>
<if test="description != null and !"".equals(description.trim())">
and a.DESCRIPTION like '%' #{description} '%'
</if>
</where>
</select>
</mapper>
***********
commandContent配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="CommandContent">
<resultMap type="com.imooc.bean.CommandContent" id="Content">
<id column="ID" jdbcType="INTEGER" property="id"/>
<result column="CONTENT" jdbcType="VARCHAR" property="content"/>
<result column="COMMAND_ID" jdbcType="VARCHAR" property="commandId"/>
</resultMap>
</mapper>
***************
Configuration.xml主配置文件也添加新的table配置文件。
<mappers>
<mapper resource="com/imooc/config/sqlxml/Message.xml"/>
<mapper resource="com/imooc/config/sqlxml/Command.xml"/>
<mapper resource="com/imooc/config/sqlxml/CommandContent.xml"/>
</mappers>
没有加时候，确认下会抛出什么异常。
---5-1 一对多关系配置3--------------------------------------------------------------
CommandDao.java
package com.imooc.dao; import java.io.IOException; import java.util.ArrayList; import java.util.List; import org.apache.ibatis.session.SqlSession; import com.imooc.bean.Command; import com.imooc.db.DBAccess; /** * 与指令表对应的数据库操作类 */ public class CommandDao { /** * 根据查询条件查询指令列表 */ public List<Command> queryCommandList(String name,String description) { DBAccess dbAccess = new DBAccess(); List<Command> commandList = new ArrayList<Command>(); SqlSession sqlSession = null; try { sqlSession = dbAccess.getSqlSession(); Command command = new Command(); command.setName(name); command.setDescription(description); // 通过sqlSession执行SQL语句 commandList = sqlSession.selectList("Command.queryCommandList", command); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if(sqlSession != null) { sqlSession.close(); } } return commandList; } }
SERVICE：
package com.imooc.util; /** * 共通的常量定义 */ public interface Iconst { /** * 当指令没有匹配的自动回复内容时，用此内容代替。 */ public static final String NO_MATCHING_CONTENT = "客官，你没按套路出牌……我听不懂你在说什么哎！"; public static final String HELP_COMMAND = "帮助"; }
返回随机的一条
import com.imooc.util.Iconst;
/** * 通过指令查询自动回复的内容 * @param command 指令 * @return 自动回复的内容 */ public String queryByCommand(String command) { CommandDao commandDao = new CommandDao(); List<Command> commandList; if(Iconst.HELP_COMMAND.equals(command)) { commandList = commandDao.queryCommandList(null, null); StringBuilder result = new StringBuilder(); for(int i = 0; i < commandList.size(); i++) { if(i != 0) { result.append("<br/>"); } result.append("回复[" + commandList.get(i).getName() + "]可以查看" + commandList.get(i).getDescription()); } return result.toString(); } commandList = commandDao.queryCommandList(command, null); if(commandList.size() > 0) { List<CommandContent> contentList = commandList.get(0).getContentList(); int i = new Random().nextInt(contentList.size()); return contentList.get(i).getContent(); } return Iconst.NO_MATCHING_CONTENT; }
---5-4 常用标签--------------------------------------------------------------
<where>标签 作用
1. 当内部的<if>都不满足时，不输出where
2. 如果两个<if>条件都满足，应该是where and condition1 and conditiion2，此时会把第一个and/or截掉
变成where condition1 and conditiion2
<select id="queryCommandList" parameterType="com.imooc.bean.Command" resultMap="Command">
select a.ID C_ID,a.NAME,a.DESCRIPTION,b.ID,b.CONTENT,b.COMMAND_ID
from COMMAND a left join COMMAND_CONTENT b
on a.ID=b.COMMAND_ID
<where>
<if test="name != null and !"".equals(name.trim())">
and a.NAME=#{name}
</if>
<if test="description != null and !"".equals(description.trim())">
and a.DESCRIPTION like '%' #{description} '%'
</if>
</where>
</select>
---------------------------------
<sql>:定义常量，用于引用，项目越复杂，引用频率越高。
<sql id="columns">ID,COMMAND,DESCRIPTION,CONTENT</sql>
select <include refid="columns"/> from MESSAGE
-------------------------------------------
<set> 与where 相似:代替set关键字,条件满足，输出set关键字。
<update id ="">
update MESSAGE
<set>
<if test="name != null and !"".equals(name.trim())">
NAME=#{name}
</if>
<if test="description != null and !"".equals(description.trim())">
DESCRIPTION = #{description} '%'
</set>
</update>
--------------------------------------------------------
<trim>标签，代替where或者set,很灵活：如内部条件满足时，输出where
prefix前追 suffix后缀（在后面输出）
prefixOverride 最前面出现and/or去掉(<trim prefix="where" prefixOverride = "and/or" >写法就可代替where标签)
suffixOverride 后面出现？？就切掉(<trim prefix="set" surfixOverride = "," >,有内容输出set，后面出现","就切掉。写法就可代替set标签)
<trim prefix="where" suffix="test" prefixOverride = "and/or">
</trim>
--------------------------------------------------------
<choose>标签与<if>关联：相当于if elseif else if（或者switch case）
<choose>
<when test="">
</when>
<when test="">
</when>
<when test="">
</when>
</choose>
--------------------------------------------------------
<association>标签与<collection>相反，但用法类似：查询到子表的数据，希望关联到主表。
<></>
<mapper namespace="Command">
<resultMap type="com.imooc.bean.Command" id="Command">
<id column="C_ID" jdbcType="INTEGER" property="id"/>
<result column="NAME" jdbcType="VARCHAR" property="name"/>
<result column="DESCRIPTION" jdbcType="VARCHAR" property="description"/>
<association property="command" resultMap="Command.command"/>
</resultMap>
<select id="queryCommandList" parameterType="com.imooc.bean.Command" resultMap="Command">
select a.ID C_ID,a.NAME,a.DESCRIPTION,b.ID,b.CONTENT,b.COMMAND_ID
from COMMAND a left join COMMAND_CONTENT b
on a.ID=b.COMMAND_ID
<where>
<if test="name != null and !"".equals(name.trim())">
and a.NAME=#{name}
</if>
<if test="description != null and !"".equals(description.trim())">
and a.DESCRIPTION like '%' #{description} '%'
</if>
</where>
</select>
</mapper>
--------------------------------------------------------
标签：
