java.jpg
第一章 案例简介
设计类似微信公众号的自动回复功能
准备工作
JSP JSTL EL JS/JQUERY
Servlet JavaBean
JDBC MySQL
第二章 实战第一部--黎明前的黑暗
2.1 黎明前的黑暗
案例分析
基本功能 :接受发送指令 根据指令自动回复对应的内容
模块划分 :
- 回复内容维护
- 对话功能
- 回复内容列表
- 回复内容删除
2.2 案例演示
2.3 页面跳转
2.4 连接数据库
2.5 数据展示
第三章 实战第二部--Mybatis来袭
3.1 Mybatis的下载并搭建核心架构
https://github.com/mybatis/mybatis-3/releases下载Mybatis jar包及源码包
**演示代码中的配置文件的详细路径 : **
src/test/java/org/apache/ibatis/submitted/complex_property/Configuration.xml
复制到项目的src/com/qezhhnjy/config/Configuration.xml
Mybatis的相关知识
Dao的需求
- 对象能与数据库交互
- 能执行SQL语句
Mybatis中提供给Dao层的处理对象为SqlSession
SqlSession的作用 :
- 向SQL语句传入参数
- 执行SQL语句
- 获取执行SQL 语句的结果
- 事务的控制
如何获得SqlSession :
1. 通过配置文件获取数据库连接相关信息
com.qezhhnjy.config.Configuration.xml
<configuration>
<!-- <settings>
<setting name="useGeneratedKeys" value="false"/>
<setting name="useColumnLabel" value="true"/>
</settings>
<typeAliases>
<typeAlias alias="UserAlias" type="org.apache.ibatis.submitted.complex_property.User"/>
</typeAliases>-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC">
<property name="" value=""/>
</transactionManager>
<dataSource type="UNPOOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/micro_message?useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="root" />
<!--property标签对应本机数据库连接相关信息-->
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/qezhhnjy/config/sqlxml/Message.xml"/>
<!--mapper标签为配置好的各个实体类与数据表映射xml文件位置-->
</mappers>
</configuration>
2. 通过配置信息构建SqlSessionFactory
com.qezhhnjy.db.DBAccess.getSession();
public class DBAccess {
public SqlSession getSqlSession() throws IOException {
//通过配置文件获取数据库连接信息
Reader reader = Resources.getResourceAsReader("com/qezhhnjy/config/Configuration.xml");
//通过配置信息构建一个SqlSessionFactory
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
return ssf.openSession();
}
}
3. 通过SqlSessionFactory打开数据库会话
com.qezhhnjy.dao.MessageListDao.mybatisQuery(...);
public static List<Message> mybatisQuery(String command,String description){
DBAccess dbAccess = new DBAccess();
List<Message> messageList = new ArrayList<>();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
messageList = sqlSession.selectList("Message.find");
//Message对应message.xml中mapper的属性namespace
//find对应message.xml中select标签的id
} catch (IOException e) {
e.printStackTrace();
}finally {
if(sqlSession !=null)sqlSession.close();
}
return messageList;
}
3.2 SQL基本配置与执行
src/test/java/org/apache/ibatis/submitted/complex_property/User.xml
将该文件复制到项目的
src/com/qezhhnjy/config/sqlxml/Message.xml
<mapper namespace="Message">
<!--由于项目中所有select标签的id都不能重名.
为了加以区分, 使用namespace属性来设置,不同namespace下的id可以相同.
类似java中包的定义及使用-->
<resultMap type="UserAlias" id="UserResult">
<id column="id" jdbcType="INTEGER" property="id"/>//主键
<result column="username" jdbcType="VARCHAR" property="username"/>
<!--column对应数据表列名 property对应实体类属性名 jdbcType对应java.sql.Types类中的常量名-->
<result column="password" jdbcType="VARCHAR" property="password.encrypted"/>
<result column="administrator" jdbcType="BOOLEAN" property="administrator"/>
</resultMap>
<select id="find" parameterType="long" resultMap="UserResult">
SELECT * FROM user WHERE id = #{id:INTEGER}
</select>
<!--原文档中有多个功能各异的select查询标签-->
</mapper>
Message类对应的全查询语句和实体类映射
<resultMap type="com.qezhhnjy.bean.Message" id="MessageResult">
<id column="id" jdbcType="VARCHAR" property="id"/>
<result column="command" jdbcType="VARCHAR" property="command"/>
<result column="description" jdbcType="VARCHAR" property="description"/>
<result column="content" jdbcType="VARCHAR" property="content"/>
</resultMap>
<select id="find" resultMap="MessageResult">
SELECT id,command,description,content FROM MESSAGE WHERE 1=1
</select>
第四章 实战第三部
4.1 动态SQL拼接
OGNL是一个强大的表达式语言
OGNL表达式语言详解
http://blog.csdn.net/yu102655/article/details/52179695
http://blog.csdn.net/hello_xusir/article/details/53423399
Paste_Image.png
Paste_Image.png
配置SQL拼接实现字段查询和模糊查询
1. 将用户输入的查询内容封装并传递给mybatis
com.qezhhnjy.dao.MessageListDao.mybatisQuery(...);
...
Message temp = new Message();
temp.setCommand(command);
temp.setDescription(description);
try {
sqlSession = dbAccess.getSqlSession();
messageList = sqlSession.selectList("Message.find",temp);
...
//只截取了与全查询不同的部分. 通过封装对应属性的输入值到实体类, 然后传递给mybatis,之后配置Message.xml来映射对应属性到sql语句中.
2. 完善配置Message.xml
Message.xml
<select id="find" parameterType="com.qezhhnjy.bean.Message" resultMap="MessageResult">
//parameterType属性即表示传递进来的对象类型
SELECT id,command,description,content FROM MESSAGE WHERE 1=1
<if test="command != null && !"".equals(command.trim())">
AND command = #{command}
</if>
<!--if标签test属性为OGNL语言, 可以识别java属性及方法,但是""和&(&)要进行转义,&&也可以使用and 关键字代替.
if标签内容为衔接前面的sql语句, 为mybatis来识别,且不必刻意空格来避免错误.-->
<if test="description != null and !"".equals(description.trim())">
AND description LIKE '%' #{description} '%'
</if>
<!--模糊查询的写法容易出现错误,%前后要加上'',并且#{...}前后仍然要注意空格-->
</select>
4.2 应用log4j调试动态SQL
在官网下载的mybatis文件目录下有log4j的jar包
Mybatis 3.4.4/mybatis-3.4.4/lib/log4j-1.2.17.jar
log4j的配置文件: log4j.properties(mybatis源码包)
Mybatis 3.4.4/mybatis-3-mybatis-3.4.4/src/test/java/log4j.properties
配置文件默认加载到src/根目录下.
### Global logging configuration
log4j.rootLogger=ERROR, stdout
//配置日志输出级别和位置,级别(debug<info<warn<error),如果设置为INFO,则会输出info,warn,error的信息,如此.
### Uncomment for MyBatis logging
log4j.logger.org.apache=INFO
### 将org.apache包的日志输出级别单独设置为INFO,也可以再单独设置输出位置.
### Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %5p [%t] - %m%n
### 格式化输出, %d表示当前时间, %p(priority)输出优先级,%t表示当前线程, %m表示具体信息, %n换行.
在mybatis的org.apache.ibatis.logging.jdbc包中,有jdbc各大类对象对应的日志输出类.
org.apache.ibatis.logging.jdbc.CollectionLogger类的源码
public final class ConnectionLogger extends BaseJdbcLogger implements InvocationHandler {
private Connection connection;
private ConnectionLogger(Connection conn, Log statementLog, int queryStack) {
super(statementLog, queryStack);
this.connection = conn;
}
public Object invoke(Object proxy, Method method, Object[] params) throws Throwable {
try {
if(Object.class.equals(method.getDeclaringClass())) {
return method.invoke(this, params);
} else {
PreparedStatement stmt;
if("prepareStatement".equals(method.getName())) {
if(this.isDebugEnabled()) {
this.debug(" Preparing: " + this.removeBreakingWhitespace((String)params[0]), true);
}
stmt = (PreparedStatement)method.invoke(this.connection, params);
stmt = PreparedStatementLogger.newInstance(stmt, this.statementLog, this.queryStack);
return stmt;
} else if("prepareCall".equals(method.getName())) {
if(this.isDebugEnabled()) {
this.debug(" Preparing: " + this.removeBreakingWhitespace((String)params[0]), true);
}
stmt = (PreparedStatement)method.invoke(this.connection, params);
stmt = PreparedStatementLogger.newInstance(stmt, this.statementLog, this.queryStack);
return stmt;
} else if("createStatement".equals(method.getName())) {
Statement stmt = (Statement)method.invoke(this.connection, params);
stmt = StatementLogger.newInstance(stmt, this.statementLog, this.queryStack);
return stmt;
} else {
return method.invoke(this.connection, params);
}
}
} catch (Throwable var5) {
throw ExceptionUtil.unwrapThrowable(var5);
}
}
public static Connection newInstance(Connection conn, Log statementLog, int queryStack) {
InvocationHandler handler = new ConnectionLogger(conn, statementLog, queryStack);
ClassLoader cl = Connection.class.getClassLoader();
return (Connection)Proxy.newProxyInstance(cl, new Class[]{Connection.class}, handler);
}
public Connection getConnection() {
return this.connection;
}
}
调试阶段设置为DEBUG级别, 可以输出最多的信息
4.3 实现单条信息删除
1. 配置Message.xml
<delete id="deleteOne" parameterType="int" >
DELETE FROM message WHERE id = #{_parameter}
<!--String及基本类型用_parameter代替, 类似OGNL-->
</delete>
**2. 设计Dao层方法 **MessageListDao.mybatisDeleteOne(int);
public static void mybatisDeleteOne(int id){
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
sqlSession.delete("Message.deleteOne", id);
sqlSession.commit();
//mybatis对数据库增删改需要手动提交
} catch (IOException e) {
e.printStackTrace();
}finally {
if(sqlSession !=null)sqlSession.close();
}
}
3. Message维护类Service中的逻辑方法MessageMaintainService.deletOne(String);
public static void deleteOne(String id) {
//service层中主要处理类似的参数逻辑问题
if (id != null &&!"".equals(id.trim())) {
MessageListDao.mybatisDeleteOne(Integer.parseInt(id));
}
}
4. 设计Servlet类servlet.DeleteOneServlet
@WebServlet(name = "DeleteOneServlet",value = "/deleteone.action")
public class DeleteOneServlet extends HttpServlet {
private static final long serialVersionUID = -1163248074005443694L;
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request, response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//设置编码
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String id = request.getParameter("id");
System.out.println(id);
//数据库查询
//servlet中尽量不要设计对数据为null及类型错误==的判定,在service中进行.
MessageMaintainService.deleteOne(id);
//跳转
System.out.println("删除成功-----");
request.getRequestDispatcher("/list.action").forward(request, response);
//跳转不能直接跳转到list.jsp页面.
//因为跳转的request并没有添加messageList属性,jsp中逻辑语言会出现空指针.
//要重新跳转到ListServlet重新查询数据再跳转到实际页面.
}
}
5. 编写list.jsp中删除链接的href
<a href="${basePath}deleteone.action?id=<%=message.getId()%>">删除</a>
比较简单的方法是通过get方法传值.但这种方法容易泄漏信息, 且只能传递少量信息.
在类似查询指定内容或模糊内容数据后进行单条删除时, 由于无法传递查询输入框中的值. 每次删除后会刷新为空并显示全部条数据,影响用户体验.
这时可以使用js设置为post方法传递信息.
<a href="javascript:(function(id) {
document.getElementById('deleteOneId').value = id;
var form=document.getElementById('mainForm');
form.action='${pageContext.request.contextPath}/deleteOne.action';
form.submit(); }) ('<%=message2.getId()%>')" >删除</a>
<!--第二个括号里面的jsp代码要加上' '.这里由于代码的输出是数字, 不加也不会出错.但其他时候则会导致出错, 且没有报错, 很难被发现!!-->
同时在table循环输出message以外设置一个隐藏标签:
<input type="hidden" name="messageId" id="deleteOneId" value="" />
4.4 实现信息批量删除
1. 配置Message.xml
<delete id="deleteBatch" parameterType="java.util.List" >
DELETE FROM message WHERE ID in(
<foreach collection="list" item="item" separator=",">
#{item}
</foreach>
<!--foreach实现将接收到的list对象遍历出来, 并且用separator内的符号进行分隔-->
)
</delete>
2. 设计Dao层方法 MessageListDao.mybatisDeleteBatch(List idList);
3. Message维护类Service中的逻辑方法MessageMaintainService.deletBatch(String[] ids);
4. 设计Servlet类servlet.DeleteBatchServlet
@WebServlet(name = "DeleteBatchServlet",value = "/deleteBatch.action")
public class DeleteBatchServlet extends HttpServlet {
private static final long serialVersionUID = -6881762699937663920L;
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//设置编码
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String[] ids = request.getParameterValues("choose");
MessageMaintainService.deleteBatch(ids);
System.out.println("批量删除成功");
response.sendRedirect("/list.action");
//这里使用的是请求重定向到list页. 不会传递req,resp对象.查询删除后会跳转到全部数据页面.
// request.getRequestDispatcher("/list.action").forward(request, response);
}
}
5. list.jsp页面checkbox传值及全选全不选
<input type="checkbox" name="choose" value="<%=message1.getId()%>" />
<!--删除按钮-->
<a class="btn03" href="javascript:(function() {var form=document.getElementById('mainForm');
form.action='deleteBatch.action';form.submit();})()">删 除</a>
<!--全选checkbox-->
<input type="checkbox" id="all" onclick="(function() {
var chooses=document.getElementsByName('choose');
if(document.getElementById('all').checked) for(var i=0;i<chooses.length;i++) chooses[i].checked=true;
else for(var j=0;j<chooses.length;j++) chooses[j].checked=false;})()" />
4.5 实现自动回复功能
模仿微信对话页面html
WEB-INF/jsp/front/talk.jsp
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ page language="java" contentType="text/html;charset=UTF-8" %>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>微信公众号</title>
<!--讨论区滚动条begin-->
<link rel="stylesheet" type="text/css" href="<%= basePath %>resources/css/jscrollpane1.css" />
<script src="<%= basePath %>resources/js/common/jquery-1.8.0.min.js" type="text/javascript"></script>
<!-- the mousewheel plugin -->
<script type="text/javascript" src="<%= basePath %>resources/js/common/jquery.mousewheel.js"></script>
<!-- the jScrollPane script -->
<script type="text/javascript" src="<%= basePath %>resources/js/common/jquery.jscrollpane.min.js"></script>
<script type="text/javascript" src="<%= basePath %>resources/js/common/scroll-startstop.events.jquery.js"></script>
<!--讨论区滚动条end-->
<script type="text/javascript" src="<%= basePath %>resources/js/front/talk.js"></script>
</head>
<body>
<input type="hidden" value="<%= basePath %>" id="basePath"/>
<br/>
<div class="talk">
<div class="talk_title"><span>正在与公众号对话</span></div>
<div class="talk_record">
<div id="jp-container" class="jp-container">
</div>
</div>
<div class="talk_word">
<input class="add_face" id="facial" type="button" title="添加表情" value="" />
<input id="content" class="messages emotion" />
<input class="talk_send" onclick="send();" type="button" title="发送" value="发送" />
</div>
</div>
<div style="text-align:center;margin:50px 0; font:normal 14px/24px 'MicroSoft YaHei';"></div>
</body>
</html>
talk.jsp对应的数据处理js方法
resources/js/front/talk.js
/**
* 页面加载
*/
$(function(){
render();
var content = "客官，来啦，坐吧！<br/>回复[查看]收取更多精彩内容。";
content += "<br/>回复[帮助]可以查看所有可用的指令。";
// 添加公众号的开场白
appendDialog("talk_recordbox","公众号",content);
render();
});
/**
* 发送消息
* @param basePath
*/
function send() {
var content = $("#content").val();
/**
* content = ""/null/undefined/数字0 , 则判定为false.
*/
if(!content) {
alert("请输入内容！");
return;
}
$.ajax({
// <input type="hidden" value="<%= basePath %>" id="basePath"/>
//通过这个标签取值, 减少参数的传递.跳转到指定Servlet
url : $("#basePath").val() + "AutoReplyServlet.action",
type : "POST",
dataType : "text",
timeout : 10000,
success : function (data) {
appendDialog("talk_recordboxme","My账号",content);
appendDialog("talk_recordbox","公众号",data);
//清空文本框内容
$("#content").val("");
render();
},
data : {"content":content}
});
}
/**
* 渲染方法，加载滚动条
*/
function render() {
// the element we want to apply the jScrollPane
var $el= $('#jp-container').jScrollPane({
verticalGutter : -16
}),
// the extension functions and options
extensionPlugin = {
extPluginOpts : {
// speed for the fadeOut animation
mouseLeaveFadeSpeed : 500,
// scrollbar fades out after hovertimeout_t milliseconds
hovertimeout_t : 1000,
// if set to false, the scrollbar will be shown on mouseenter and hidden on mouseleave
// if set to true, the same will happen, but the scrollbar will be also hidden on mouseenter after "hovertimeout_t" ms
// also, it will be shown when we start to scroll and hidden when stopping
useTimeout : true,
// the extension only applies for devices with width > deviceWidth
deviceWidth : 980
},
hovertimeout : null, // timeout to hide the scrollbar
isScrollbarHover: false,// true if the mouse is over the scrollbar
elementtimeout : null, // avoids showing the scrollbar when moving from inside the element to outside, passing over the scrollbar
isScrolling : false,// true if scrolling
addHoverFunc : function() {
// run only if the window has a width bigger than deviceWidth
if( $(window).width() <= this.extPluginOpts.deviceWidth ) return false;
var instance = this;
// functions to show / hide the scrollbar
$.fn.jspmouseenter = $.fn.show;
$.fn.jspmouseleave = $.fn.fadeOut;
// hide the jScrollPane vertical bar
var $vBar = this.getContentPane().siblings('.jspVerticalBar').hide();
/*
* mouseenter / mouseleave events on the main element
* also scrollstart / scrollstop - @James Padolsey : http://james.padolsey.com/javascript/special-scroll-events-for-jquery/
*/
$el.bind('mouseenter.jsp',function() {
// show the scrollbar
$vBar.stop( true, true ).jspmouseenter();
if( !instance.extPluginOpts.useTimeout ) return false;
// hide the scrollbar after hovertimeout_t ms
clearTimeout( instance.hovertimeout );
instance.hovertimeout = setTimeout(function() {
// if scrolling at the moment don't hide it
if( !instance.isScrolling )
$vBar.stop( true, true ).jspmouseleave( instance.extPluginOpts.mouseLeaveFadeSpeed || 0 );
}, instance.extPluginOpts.hovertimeout_t );
}).bind('mouseleave.jsp',function() {
// hide the scrollbar
if( !instance.extPluginOpts.useTimeout )
$vBar.stop( true, true ).jspmouseleave( instance.extPluginOpts.mouseLeaveFadeSpeed || 0 );
else {
clearTimeout( instance.elementtimeout );
if( !instance.isScrolling )
$vBar.stop( true, true ).jspmouseleave( instance.extPluginOpts.mouseLeaveFadeSpeed || 0 );
}
});
if( this.extPluginOpts.useTimeout ) {
$el.bind('scrollstart.jsp', function() {
// when scrolling show the scrollbar
clearTimeout( instance.hovertimeout );
instance.isScrolling = true;
$vBar.stop( true, true ).jspmouseenter();
}).bind('scrollstop.jsp', function() {
// when stop scrolling hide the scrollbar (if not hovering it at the moment)
clearTimeout( instance.hovertimeout );
instance.isScrolling = false;
instance.hovertimeout = setTimeout(function() {
if( !instance.isScrollbarHover )
$vBar.stop( true, true ).jspmouseleave( instance.extPluginOpts.mouseLeaveFadeSpeed || 0 );
}, instance.extPluginOpts.hovertimeout_t );
});
// wrap the scrollbar
// we need this to be able to add the mouseenter / mouseleave events to the scrollbar
var $vBarWrapper = $('<div/>').css({
position : 'absolute',
left : $vBar.css('left'),
top : $vBar.css('top'),
right : $vBar.css('right'),
bottom : $vBar.css('bottom'),
width : $vBar.width(),
height : $vBar.height()
}).bind('mouseenter.jsp',function() {
clearTimeout( instance.hovertimeout );
clearTimeout( instance.elementtimeout );
instance.isScrollbarHover = true;
// show the scrollbar after 100 ms.
// avoids showing the scrollbar when moving from inside the element to outside, passing over the scrollbar
instance.elementtimeout = setTimeout(function() {
$vBar.stop( true, true ).jspmouseenter();
}, 100 );
}).bind('mouseleave.jsp',function() {
// hide the scrollbar after hovertimeout_t
clearTimeout( instance.hovertimeout );
instance.isScrollbarHover = false;
instance.hovertimeout = setTimeout(function() {
// if scrolling at the moment don't hide it
if( !instance.isScrolling )
$vBar.stop( true, true ).jspmouseleave( instance.extPluginOpts.mouseLeaveFadeSpeed || 0 );
}, instance.extPluginOpts.hovertimeout_t );
});
$vBar.wrap( $vBarWrapper );
}
}
},
// the jScrollPane instance
jspapi = $el.data('jsp');
// extend the jScollPane by merging
$.extend( true, jspapi, extensionPlugin );
jspapi.addHoverFunc();
}
/**
* 向聊天记录中添加聊天内容
* @param myClass 添内容的样式
* @param name 发送消息的账号名称
* @param content 发送的内容
*/
function appendDialog(myClass,name,content) {
var div = "";
div += "<div class='" + myClass + "'>";
div += "<div class='user'>![](" + $("#basePath").val() + "resources/images/thumbs/" + myClass + ".jpg)" + name + "</div>";
div += "<div class='talk_recordtextbg'> </div>";
div += "<div class='talk_recordtext'>";
div += "<h3>" + content + "</h3>";
div += "<span class='talk_time'>" + getCurrentDate() + "</span>";
div += "</div>";
div += "</div>";
$('#jp-container').children().eq(0).children().eq(0).append(div);
}
/**
* 获取当前系统时间
* @returns {String} 当前系统时间
*/
function getCurrentDate() {
var date = new Date();
return date.getFullYear() + "-" + (date.getMonth() + 1) + "-" + date.getDate() + " " + (date.getHours() < 10 ? "0" + date.getHours() : date.getHours()) + ":" + (date.getMinutes() < 10 ? "0" + date.getMinutes() : date.getMinutes());
}
Service层queryBycommand(String);方法
com/qezhhnjy/service/MessageQueryService.java
public static String queryByCommand(String command) {
List<Message> messageList;
//设置输入'帮助'返回全部数据
//Iconst.HELP_COMMAND为自定义帮助字符串常量
if (Iconst.HELP_COMMAND.equals(command)){
messageList = MessageListDao.mybatisQuery(null, null);
StringBuilder sb = new StringBuilder();
for (Message message : messageList) {
sb.append("回复[").append(message.getCommand()).append("],").append("查看[").append(message.getDescription()).append("]").append("<br/>");
}
return sb.toString();
}
messageList = MessageListDao.mybatisQuery(command, null);
if (messageList.size() > 0) return messageList.get(0).getContent();
//Iconst.NO_MATCHING_CONTENT为自定义无匹配的返回字符串常量.
return Iconst.NO_MATCHING_CONTENT;
}
第五章 实战第四部
5.1 一对多关系的配置 Ⅰ
如果回复"段子", 而机器人返回的始终是一个笑话,就很无聊了.
所以需要实现一个指令能够随机回复多条内容中的一条的功能.
重新设计数据表
command 表 : ID /commandname/description
content表: ID/content/command_ID(froeign key)
5.2 一对多关系的配置 Ⅱ
Content.xml
<mapper namespace="Content">
<resultMap type="com.qezhhnjy.bean.Content" id="ContentResult">
<id column="id" jdbcType="VARCHAR" property="id"/>
<result column="cid" jdbcType="VARCHAR" property="cid"/>
<result column="content" jdbcType="VARCHAR" property="content"/>
</resultMap>
</mapper>
Command.xml
<mapper namespace="Command">
<resultMap type="com.qezhhnjy.bean.Command" id="CommandResult">
<id column="c_id" jdbcType="VARCHAR" property="id"/>
<!--这里的列名对应下面select标签中的结果. 不包括前面的a./b.==,可以使用别名.-->
<result column="command" jdbcType="VARCHAR" property="command"/>
<result column="description" jdbcType="VARCHAR" property="description"/>
<collection property="contents" resultMap="Content.ContentResult" />
<!--contents为Command实体类中的content集合的属性名-->
</resultMap>
<select id="query" parameterType="com.qezhhnjy.bean.Command" resultMap="CommandResult" >
SELECT a.id c_id,a.command,a.description,b.id,b.content
FROM command a LEFT JOIN content b ON a.id=b.cid WHERE 1=1
<if test="command != null && !"".equals(command.trim())">
AND command = #{command}
</if>
<if test="description != null and !"".equals(description.trim())">
AND description LIKE '%' #{description} '%'
</if>
</select>
</mapper>
最后在Configuration.xml中添加这两个实体类的xml文件映射.
5.3 一对多关系的配置 Ⅲ
5.4 常用标签
<where></where>标签
Message.xml
SELECT id,command,description,content FROM MESSAGE WHERE 1=1
<if test="command != null && !"".equals(command.trim())">
AND command = #{command}
</if>
<if test="description != null and !"".equals(description.trim())">
AND description LIKE '%' #{description} '%'
</if>
等效于
SELECT id,command,description,content FROM message
<where>
<if test="command != null and !"".equals(command.trim())">
AND command = #{command}
</if>
<if test="description !=null and !"".equals(description.trim())">
AND description LIKE '%' #{description} '%'
</if>
</where>
where标签可以在没有可选项时去除WHERE关键字. 在有可选项时去除第一个条件前面的and或者or.
<sql></sql>标签(与select标签同一级)
SELECT <include refid="columns" /> FROM message
<where>
<if test="command != null and !"".equals(command.trim())">
AND command = #{command}
</if>
<if test="description !=null and !"".equals(description.trim())">
AND description LIKE '%' #{description} '%'
</if>
</where>
</select>
<sql id="columns">id,command,description,content</sql>
<set></set>标签
<update id="">
UPDATE message
<set>
<if test="command != null and !"".equals(command.trim())">command=#{command},
</if>
<if test="description != null and !"".equals(description.trim())">description=#{description},
</if>
</set>
</update>
<set>标签可以不必考虑<if>中内容的','的取舍问题.
<trim></trim>标签与where/set同级
<trim prefix="" suffix="" prefixOverrides="and/or" suffixOverrides=""></trim>
表示如果标签里有内容输出, 则 分别加上prefix/suffix指定的前缀和后缀.
prefixOverrides表示输出内容最前面有指定内容则切除. 上面就表示有and或者or时切掉and或者or.
suffixOverrides同理.
<choose></choose>标签
<choose>
<when test=""></when>
<when test=""></when>
<when test=""></when>
<otherwise></otherwise>
</choose>
类似java中的if...else及switch语句.
<association></association>标签(与collection相反)
用于在子表中映射父表
| 功能 | 标签名称 |
|---|---|
| 定义SQL语句 | insert |
| delete | |
| update | |
| select | |
| 配置java对象属性与查询结果集中列名对应关系 | resultMap |
| 控制动态SQL拼接 | foreach |
| if | |
| choose | |
| 格式化输出 | where |
| set | |
| trim | |
| 配置关联关系 | collection |
| association | |
| 定义常量 | sql |
| 引用常量 | include |
第六章 战斗总结
6.1 容易混淆的概念
| reslutMap | resultType |
|---|
resultType指直接设定结果集映射为某个工具类或实体类.
为实体类时列名与类中属性名需一一对应,忽略大小写.
为工具类, 如java.util.Map时.以列名为Key, 取值为value. 大小写敏感.
| parameterMap | parameterType |
|---|
parameterMap用法与resultMap类似. 不推荐使用deprecated.
| #{} | ${} |
|---|
#{}有预编译效果,在console中会以?显示,
${}没有预编译效果, 在console中会以其值显示
${段子} 显示为: WHERE command=段子 .这个sql语句片段是错误的.段子前后没有单引号.正确的用法应该是'${段子}'.
${}的用法主要在于order by ×××时. 如果用预编译则会以?替代.没有效果 .在表头onclick事件按照该列进行排列时, 通过传递该列列名然后利用${×××}编译到sql语句中进行动态排序.
| #{} | ognl |
|---|
如果parameterType为String及8中基本数据类型. #{}可以写成#{_parameter}.也可以#{}或者中间可以随便写别的. 都不会影响.此时,这个值是确定的. 而ognl表达式则依然只能写_parameter.
但依然不推荐省略或者乱写.
6.2 常见问题解析
1. 获取自增主键值
在对command进行插入时, 由于command.id为mysql自增. 不需要进行设置. 那么在插入command后, 应该怎样获取到这个自增的id,作为content的外键添加command包含的contents.
<!--插入一对多单方时设置实体类获取数据库中单方自增生成的主键来设置多方的外键
useGeneratedKeys为true表示主键为mysql自增.keyProperty表示mybatis获取的主键值添加到实体类的该属性-->
<insert id="insert" parameterType="com.qezhhnjy.bean.Command"
useGeneratedKeys="true" keyProperty="id" >
INSERT INTO command(command,description) VALUES(#{command},#{description})
</insert>
2. 找不到namespace.id的异常效果
需要在Configuration.xml中映射各个实体类的xml文件. 否则运行就会出现这个异常.
3. sql语句编写错误
4. 不要过度使用${}
5. 乱码
