previous：MyBatis_2_MyBatis下载并搭建框架 next：MyBatis_4_一对多关系配置
---4-1 SQL动态拼接--------------------------------------------------------------
1.parameterType可直接填写java.long包的类型，不需要包名
2. OGNL(与EL一样是一种语言)功能强大的表达式语言(直接支持JAVA对象的代码)。struts2也使用
"" 转为""
&&转为&&或者“and”
DAO
/** * 根据查询条件查询消息列表 */ public List<Message> queryMessageList(String command,String description) { DBAccess dbAccess = new DBAccess(); List<Message> messageList = new ArrayList<Message>(); SqlSession sqlSession = null; try { Message message = new Message(); message.setCommand(command); message.setDescription(description); sqlSession = dbAccess.getSqlSession(); // 通过sqlSession执行SQL语句 messageList = sqlSession.selectList("Message.queryMessageList", message); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if(sqlSession != null) { sqlSession.close(); } } return messageList; }
1）#{变量名}//此处不是OGNL，是mybatis处理替换成“？”
2）where 1=1不需要
3）parameterType只接受一个parameter。
4）'%' #{description} '%'。注意#{description}前后空格必须。
调试使用log4j辅助。
<select id="queryMessageList" parameterType="com.imooc.bean.Message" resultMap="MessageResult">
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>
</select>
<select id="queryMessageList" parameterType="com.imooc.bean.Message" resultMap="MessageResult"> 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> </select>
===LOG4J===========================
Log4j：日志输出 。Jar包和配置文件(log4j.properties放入src根目录下，(指定其他路径时，需要配置))
MyBatistsjar包的logging.LogFactory.class源码，定义了各种LOG的接口，其中包含Log4j，所以配置好Log4j 之后就可以打印LOG了
properties文件 key=value
//级别
logger log;
//级别由低到高
log.debug("adf");
log.info(message);
log.warn(message);
log.error(message);
//大于等于DEBUG级别的都输出
log4j.rootLogger=DEBUG,Console//整个工程的级别//Console，输出位置是控制台。DEBUG级别的原因：参照jar包的logging.jdbc下的类的源码，比如ConnectionLogger.class的源码里，是用debug()出力的，所以不能高于这个级别，否则不会打印Log。
log4j.appender.Console=org.apache.log4j.ConsoleAppender//配置此类才会输出到控制台(log4j.rootLogger=DEBUG,A
log4j.appender.A)
log4j.appender.Console.layout=org.apache.log4j.PatternLayout//布局
log4j.appender.Console.layout.ConversionPattern=%d [%t] %-5p [%c] - %m%n//布局自定义
(%d [%t] %-5p [%c] - %m%n,%开头的有特殊含义，[]空格-等都是原样输出。%d产生时间 %t所处线程名称，%-5p:%p输出的级别，-5表示输出的字符至少占5位，不足用空格补齐，“-”表示补齐的空格在右边，没有“-”的话，空格就在左边。%c输出日志时的类全名+包名 %n换行%m输出时附加的信息)
log4j.logger.org.apache=INFO//org.apache为包名，为org.apache包下配置为INFO级别
导入的包的源码加入方法(如mybatis的源码)：
properties->Java Build Path->Libraries->mybatis包-Source attachment ...-edit-下载的源码的根目录层mybatis-3-mybatis-3.4.4-OK
===LOG4J==============================
---4-4 单条删除--------------------------------------------------------------
Message.xml
<delete id="deleteOne" parameterType="int">
delete from MESSAGE where ID = #{_parameter}
</delete>
dao
/** * 单条删除 */ public void deleteOne(int id) { DBAccess dbAccess = new DBAccess(); SqlSession sqlSession = null; try { sqlSession = dbAccess.getSqlSession(); // 通过sqlSession执行SQL语句 sqlSession.delete("Message.deleteOne", id); sqlSession.commit(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { if(sqlSession != null) { sqlSession.close(); } } }
sevice
package com.imooc.service; import java.util.ArrayList; import java.util.List; import com.imooc.dao.MessageDao; /** * 维护相关的业务功能 */ public class MaintainService { /** * 单条删除 */ public void deleteOne(String id) { if(id != null && !"".equals(id.trim())) { MessageDao messageDao = new MessageDao(); messageDao.deleteOne(Integer.valueOf(id)); } } /** * 批量删除 */ public void deleteBatch(String[] ids) { MessageDao messageDao = new MessageDao(); List<Integer> idList = new ArrayList<Integer>(); for(String id : ids) { idList.add(Integer.valueOf(id)); } messageDao.deleteBatch(idList); } }
Servlet
/** * 单条删除控制层 */ @SuppressWarnings("serial") public class DeleteOneServlet extends HttpServlet{ @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // 设置编码 req.setCharacterEncoding("UTF-8"); // 接受页面的值 String id = req.getParameter("id"); MaintainService maintainService = new MaintainService(); maintainService.deleteOne(id); // 向页面跳转 req.getRequestDispatcher("/List.action").forward(req, resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { this.doGet(req, resp); } }
web.xml配置新增的servlet
<servlet> <servlet-name>ListServlet</servlet-name> <servlet-class>com.imooc.servlet.ListServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>ListServlet</servlet-name> <url-pattern>/List.action</url-pattern> </servlet-mapping> <servlet> <servlet-name>DeleteOneServlet</servlet-name> <servlet-class>com.imooc.servlet.DeleteOneServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>DeleteOneServlet</servlet-name> <url-pattern>/DeleteOneServlet.action</url-pattern> </servlet-mapping>
jsp:
get方式提交
<a href="${basePath}DeleteOneServlet.action?id=${message.id}">删除</a>
<table class="tab2" width="100%"> <tbody> <tr> <th><input type="checkbox" id="all" onclick="#"/></th> <th>序号</th> <th>指令名称</th> <th>描述</th> <th>操作</th> </tr> <c:forEach items="${messageList}" var="message" varStatus="status"> <tr <c:if test="${status.index % 2 != 0}">style='background-color:#ECF6EE;'</c:if>> <td><input type="checkbox" name="id" value="${message.id}"/></td> <td>${status.index + 1}</td> <td>${message.command}</td> <td>${message.description}</td> <td> <a href="#">修改</a> <a href="${basePath}DeleteOneServlet.action?id=${message.id}">删除</a> </td> </tr> </c:forEach> </tbody> </table>
---4-5 批量删除--------------------------------------------------------------
Message.xml
<delete id="deleteBatch" parameterType="java.util.List"> delete from MESSAGE where ID in( <foreach collection="list" item="item" separator=","> #{item} </foreach> ) </delete>
DAO:
/**
* 批量删除
*/
public void deleteBatch(List<Integer> ids) {
DBAccess dbAccess = new DBAccess();
SqlSession sqlSession = null;
try {
sqlSession = dbAccess.getSqlSession();
// 通过sqlSession执行SQL语句
sqlSession.delete("Message.deleteBatch", ids);
sqlSession.commit();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(sqlSession != null) {
sqlSession.close();
}
}
}
service：
/**
* 批量删除
*/
public void deleteBatch(String[] ids) {
MessageDao messageDao = new MessageDao();
List<Integer> idList = new ArrayList<Integer>();
for(String id : ids) {
idList.add(Integer.valueOf(id));
}
messageDao.deleteBatch(idList);
}
}
Servlet：
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
// 设置编码
req.setCharacterEncoding("UTF-8");
// 接受页面的值
String[] ids = req.getParameterValues("id");
MaintainService maintainService = new MaintainService();
maintainService.deleteBatch(ids);
// 向页面跳转
req.getRequestDispatcher("/List.action").forward(req, resp);
}
JSP：
<script src="<%= basePath %>resources/js/common/jquery-1.8.0.min.js"></script>
<script src="<%= basePath %>resources/js/back/list.js"></script>
//通过JS，进行POST提交
<p class="g_title fix">内容列表 <a class="btn03" href="#">新 增</a> <a class="btn03" href="javascript:deleteBatch('<%=basePath%>');">删 除</a></p>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> <% 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" /> <meta http-equiv="X-UA-Compatible"content="IE=9; IE=8; IE=7; IE=EDGE" /> <title>内容列表页面</title> <link href="<%= basePath %>resources/css/all.css" rel="stylesheet" type="text/css" /> <script src="<%= basePath %>resources/js/common/jquery-1.8.0.min.js"></script> <script src="<%= basePath %>resources/js/back/list.js"></script> </head> <body style="background: #e1e9eb;"> <form action="<%= basePath %>List.action" id="mainForm" method="post"> <input type="hidden" name="currentPage" id="currentPage" value="${page.currentPage}"/> <div class="right"> <div class="current">当前位置：<a href="javascript:void(0)" style="color:#6E6E6E;">内容管理</a> > 内容列表</div> <div class="rightCont"> <p class="g_title fix">内容列表 <a class="btn03" href="#">新 增</a> <a class="btn03" href="javascript:deleteBatch('<%=basePath%>');">删 除</a></p> <table class="tab1"> <tbody> <tr> <td width="90" align="right">指令名称：</td> <td> <input name="command" type="text" class="allInput" value="${command}"/> </td> <td width="90" align="right">描述：</td> <td> <input name="description" type="text" class="allInput" value="${description}"/> </td> <td width="85" align="right"><input type="submit" class="tabSub" value="查 询" /></td> </tr> </tbody> </table> <div class="zixun fix"> <table class="tab2" width="100%"> <tbody> <tr> <th><input type="checkbox" id="all" onclick="#"/></th> <th>序号</th> <th>指令名称</th> <th>描述</th> <th>操作</th> </tr> <c:forEach items="${messageList}" var="message" varStatus="status"> <tr <c:if test="${status.index % 2 != 0}">style='background-color:#ECF6EE;'</c:if>> <td><input type="checkbox" name="id" value="${message.id}"/></td> <td>${status.index + 1}</td> <td>${message.command}</td> <td>${message.description}</td> <td> <a href="#">修改</a> <a href="${basePath}DeleteOneServlet.action?id=${message.id}">删除</a> </td> </tr> </c:forEach> </tbody> </table> <div class='page fix'> 共 <b>${page.totalNumber}</b> 条 <c:if test="${page.currentPage != 1}"> <a href="javascript:changeCurrentPage('1')" class='first'>首页</a> <a href="javascript:changeCurrentPage('${page.currentPage-1}')" class='pre'>上一页</a> </c:if> 当前第<span>${page.currentPage}/${page.totalPage}</span>页 <c:if test="${page.currentPage != page.totalPage}"> <a href="javascript:changeCurrentPage('${page.currentPage+1}')" class='next'>下一页</a> <a href="javascript:changeCurrentPage('${page.totalPage}')" class='last'>末页</a> </c:if> 跳至 <input id="currentPageText" type='text' value='${page.currentPage}' class='allInput w28' /> 页 <a href="javascript:changeCurrentPage($('#currentPageText').val())" class='go'>GO</a> </div> </div> </div> </div> </form> </body> </html>
JS：通过submit()方法提交表单(post)
/**
* 调用后台批量删除方法
*/
function deleteBatch(basePath) {
//设定action
$("#mainForm").attr("action",basePath + "DeleteBatchServlet.action");
$("#mainForm").submit();
}
---4-1 SQL动态拼接--------------------------------------------------------------
---4-1 SQL动态拼接--------------------------------------------------------------