一对多关系的配置
如果每次输入某个指令，机器人回复的都是同一内容那就太尴尬了 原先数据库只有一张表Message，因为出现了一对多关系，现将其拆分成command表和command_content表command表{id,name,description} command_content表{id,content,command_id}
CommandContent.xml:
<mapper namespace="CommandContent">
<resultMap type="com.csdn.bean.CommandContent" id="commandcontent">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="content" jdbcType="VARCHAR" property="content"/>
<result column="command_id" jdbcType="INTEGER" property="command_id"/>
</resultMap>
</mapper>
Command.xml:
<mapper namespace="Command">
<resultMap type="com.csdn.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.commandcontent"/>
</resultMap>
<select id="queryCommandList" parameterType="com.csdn.bean.Command" resultMap="command">
select a.id c_id,a.name,a.description,b.content,b.id,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>
CommandDao.java:
public class CommandDao {
public List<Command> queryCommandList(String name,String description){
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession=null;
List<Command> selectList =new ArrayList<Command>();
try {
sqlSession=dbAccess.getSqlSession();
Command comm = new Command();
comm.setName(name);
comm.setDescription(description);
//通过sqlSession执行SQL语句
selectList = sqlSession.selectList("Command.queryCommandList",comm);
} catch (Exception e) {
e.printStackTrace();
}finally{
if(sqlSession!=null){
sqlSession.close();
}
}
return selectList;
}
}
QueryService.java:
将原先代码中的MessageDao替换成CommandDao
public class QueryService {
public List<Command> queryCommandList(String name,String description){
CommandDao commandDao=new CommandDao();
return commandDao.queryCommandList(name,description);
}
public String queryByCommand(String command){
CommandDao commandDao=new CommandDao();
List<Command> commandList;
if(Iconst.HELP.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();
return contentList.get(new Random().nextInt(contentList.size())).getContent();
}
return Iconst.NO_MATCHING_CONTENT;
}
}
contentList.get(new Random().nextInt(contentList.size())).getContent();实现了随机获取command对应的content，这样就不会每次返回统一内容
AutoReplyServlet:
protected void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
req.setCharacterEncoding("UTF-8");
res.setContentType("text/html;charset=utf-8");
PrintWriter out = res.getWriter();
QueryService queryservice = new QueryService();
out.write(queryservice.queryByCommand(req.getParameter("content")));
out.flush();
out.close();
}
实现效果:
Mapper映射文件常用标签:
容易混淆的概念:
我觉得就介绍一下#{}和${}就好，这个用的比较多 #{}有预编译效果，会被替换成?；而${}不会，它会直接在解析sql时直接用传进来的参数值替代，使用时别忘了用单引号括起来 '${}'使用${}的场景一般是 order by '${xxxx}'，总不能order by ? 把
常见问题:
如果你的Command表的主键是自增的，在做新增一个command功能时你会碰到一个问题，新增时你不知道command主键
<insert id="insert" useGeneratedKeys="true" keyProperty="id" parameterType="com.csdn.bean.Command">useGeneratedKeys="true" 使用自增主键
insert into command(name,description) values(#{name},#{description})
</insert>
keyProperty="id" 将获得的自增主键值传给command对象的id属性
一开始传入的command对象中id为空，但是在sqlSession执行到此sql时command对象中id就有值了