MyBatis入门学习

最新推荐文章于 2023-01-05 19:03:53 发布

coding小强

最新推荐文章于 2023-01-05 19:03:53 发布

阅读量1.3k

点赞数 1

分类专栏： oracle mybaits 文章标签： mybatis oracle 分页

本文链接：https://blog.csdn.net/u014292162/article/details/52016571

版权

oracle 同时被 2 个专栏收录

10 篇文章 0 订阅

订阅专栏

mybaits

6 篇文章 0 订阅

订阅专栏

一、Mybatis背景介绍

MyBatis 是支持普通 SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。

MyBatis 使用简单的 XML或注解用于配置和原始映射，将接口和 Java 的POJOs映射成数据库中的记录。

二、环境准备

2.1 提示

本案例使用servlet+mybaits+oracle实现一些简单操作，准备mybatis的jar,和其他如下图所示jstl.jar，standrd.jar用于jsp页面标准和使用jstl和el表达式，ojdbc14.jar用于数据库oracle的连接，servlet-api.jar用于servlet的使用

注：本文的一些案例是来自视频学习

2.2 建表以及插入数据

create table message(
id number(10)  constraint message_pk_id primary key,
command varchar2(20) not null,
description varchar2(20) not null,
content varchar2(200)
);

insert into message values(0001,'段子','精彩段子','精彩段子内容1');
insert into message values(0002,'段子','精彩段子','精彩段子内容2');
insert into message values(0003,'段子','精彩段子','精彩段子内容3');
insert into message values(0004,'段子','精彩段子','精彩段子内容3');
insert into message values(0005,'段子','精彩段子','精彩段子内容4');
insert into message values(0006,'段子','精彩段子','精彩段子内容5');
insert into message values(0007,'笑话','笑话段子','精彩段子内容1');
insert into message values(0008,'笑话','笑话段子','精彩段子内容2');
insert into message values(0009,'笑话','笑话段子','精彩段子内容3');
insert into message values(0010,'笑话','笑话段子','精彩段子内容4');
insert into message values(0011,'笑话','笑话段子','精彩段子内容5');
insert into message values (0012, '查看', '精彩内容', '精彩内容');
insert into message values (0013, '段子', '精彩段子', '如果你的月薪是3000块钱，请记得分成五份，一份用来买书，一份给家人，一份给女朋友买化妆品和衣服，一份请朋友们吃饭，一份作为同事的各种婚丧嫁娶的份子钱。');
insert into message values(0014, '新闻', '今日头条', '7月17日，马来西亚一架载有298人的777客机在乌克兰靠近俄罗斯边界坠毁。另据国际文传电讯社消息，坠毁机型为一架波音777客机，机载约280名乘客和15个机组人员。');
insert into message values (0015, '娱乐', '娱乐新闻', '昨日,邓超在微博分享了自己和孙俪的书法。夫妻同样写幸福，但差距很大。邓超自己都忍不住感慨字丑：左边媳妇写的。右边是我写的。看完我再也不幸福了。');
insert into message values(0018, '电影', '近日上映大片', '《忍者神龟》[2]真人电影由美国派拉蒙影业发行，《洛杉矶之战》导演乔纳森·里贝斯曼执导');
insert into messagevalues (0017, '彩票', '中奖号码', '查啥呀查,你不会中奖的！');

2.3开始使用Mybatis

工程目录结构

Mybatis默认的配置文件是Configuration.xml里边可以配置数据库的连接信息，mapper映射文件，和分页拦截器等等

如下是Configuration.xml的内容，配置了数据库的连接信息

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
  <environments default="development">
    <environment id="development">
      <-- 配置数据库的相关连接信息-->
<transactionManager type="JDBC">
        <property name="" value=""/>
      </transactionManager>
      <dataSource type="UNPOOLED">
<-- oracle数据库连接驱动-->     
  <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
      <-- orcle 11g url-->
<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
<--数据库用户名-->
  <property name="username" value="caojx"/>
        <--密码-->
<property name="password" value="caojx"/>
      </dataSource>
    </environment>
  </environments>
</configuration>

2.4建立javaBean（pojo） Message消息实体类

package com.imooc.bean;

/**
 * 与消息表对应的实体类
 */
public class Message {
	/**
	 * 主键
	 */
	private String id;
	/**
	 * 指令名称
	 */
	private String command;
	/**
	 * 描述
	 */
	private String description;
	/**
	 * 内容
	 */
	private String content;
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getCommand() {
		return command;
	}
	public void setCommand(String command) {
		this.command = command;
	}
	public String getDescription() {
		return description;
	}
	public void setDescription(String description) {
		this.description = description;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
}

2.5Message.xml

在config下的sqlxml中建立Message.xml映射文件

Mybatis中对数据库的操作的一些sql语句一般就写在该文件中

<?xml version="1.0" encoding="UTF-8"?>
<!--

       Copyright 2009-2012 the original author or authors.

       Licensed under the Apache License, Version 2.0 (the "License");
       you may not use this file except in compliance with the License.
       You may obtain a copy of the License at

          http://www.apache.org/licenses/LICENSE-2.0

       Unless required by applicable law or agreed to in writing, software
       distributed under the License is distributed on an "AS IS" BASIS,
       WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
       See the License for the specific language governing permissions and
       limitations under the License.

-->

<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 
	1.namespce用于定义命名空间，该命名空间是必须的，一般写对应实体类的全类名
	2.避免sql语句标签中id冲突问题，Mybatis中的所有相同命名空间下的的所有sql标签中的id命名不能重复
 -->
<mapper namespace="com.imooc.bean.Message">

	<!-- 用于数据库中的字段与实体类中的属性对应 -->
  <resultMap type="com.imooc.bean.Message" id="MessageResult">
    <id column="id" jdbcType="INTEGER" 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标签用于书写查询语句
		resultMap引用上边的resultMap标签
		实际上MyBatis查询到结果后会将结果存放到map中
	-->
  <select id="queryMessageList" parameterType="com.imooc.bean.Message" resultMap="MessageResult">
    select id,command,description,content from message
    <where><!-- 
    		test中书写布尔表达式，如果成功则拼接下边的条件, command是parameterType中的成员变量
    		拼接sql的时候会将‘#{command}’替换成?,取值类似于ognl
    		下边的if标签中test中的内还可以是
    		command != null and command != ''  
    		-->
    	<if test="command != null and ''!=command.trim()">
	    	and command=#{command}
	    </if>
	    <!-- like 查询一般会拼接concat()拼接两个字符串 -->
	    <if test="description != null and ''!=description.trim()">
	    	and description like concat(concat('%',#{description}),'%')
	    </if>
    </where>
  </select>
</mapper>

2.6使用Mybatis与数据库建立连接

package com.imooc.db;

import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;


/**
 * 获取数据库连接
 * */
public class DBAccess {
	
	public SqlSession getSqlSession(){
		SqlSession session = null;
		try{
			//读取配置文件获取数据库连接信息
			Reader reader = Resources.getResourceAsReader("com/imooc/config/Configuration.xml");
			//获取sqlSessionFactory
			SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
			//获取session
			session = sessionFactory.openSession();
		}catch(Exception e){
			e.printStackTrace();
		}
		return session;
	}
	
	public static void main(String[] args) {
		System.out.println(new DBAccess().getSqlSession());
	}

}

2.7在dao成建立MessageDao.java类

package com.imooc.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.imooc.bean.Message;
import com.imooc.common.Page;
import com.imooc.db.DBAccess;

/**   
* Copyright: Copyright (c) 2016 caojx
* @ClassName: MessageDao.java
* @Description: 和message相关的一些操作
* @version: v1.0.0
* @author: caojx
* @date: 2016/7/19 20:53
*/
public class MessageDao {

	DBAccess dbAccess = new DBAccess();
	
	/**   
	* @Function: queryMessageList
	* @Description: 通过命令,和描述查询消息
	* @param:String command 命令
	* @param:String description 描述
	* @exception Exception
	* @retrun List<Message>
	* @version: v1.0.0
	* @author: caojx
	* @date: 2016/7/19 9:30
	*/
	public List<Message> queryMessageList(String command, String description) throws Exception {
		List<Message> messagesList = null;
		SqlSession session = null;
		try {
			//获取SqlSession
			session = dbAccess.getSqlSession();
			Message message = new Message();
			message.setCommand(command);
			message.setDescription(description);
			/*SqlSession中提供了很多数据库操作的方法，如selectXXX实现对数据库的查询
			 *的方法Message.class.getName().queryMessageList获取配置文件中id=queryMessageList
			 *对应的sql,message为传入的数据封装对象，一般只能传入一个对象，还可以常用map封装参数
			 **/
			messagesList = session.selectList(Message.class.getName()+".queryMessageList", message);
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} finally {
			if (session != null) {
				session.close();
			}
		}
		return messagesList;
	}

2.8在Service成中建立MessageService.java文件

package com.imooc.service;

import java.util.ArrayList;
import java.util.List;
import com.imooc.bean.Message;
import com.imooc.dao.MessageDao;

/**   
* Copyright: Copyright (c) 2016 caojx
* @ClassName: MessageService.java
* @Description: 和message相关的一业务操作
* @version: v1.0.0
* @author: caojx
* @date: 2016/7/19 20:53
*/
public class MessageService {

	MessageDao dao = new MessageDao();

	/**   
	* @Function: queryMessageList
	* @Description: 通过命令,和描述查询消息
	* @param:String command 命令
	* @param:String description 描述
	* @exception Exception
	* @retrun List<Message>
	* @version: v1.0.0
	* @author: caojx
	* @date: 2016/7/19 9:30
	*/
	public List<Message> queryMessageList(String command, String description) throws Exception {
		List<Message> messagesList = null;
		try {
			messagesList = dao.queryMessageList(command, description);
			
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		}
		return messagesList;
	}
}

2.9建立ListServlet.java类

package com.imooc.servlet;

import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.imooc.bean.Message;
import com.imooc.service.MessageService;

public class ListServlet extends HttpServlet {

	MessageService messageService = new MessageService();

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) {
		try {
			// 设置编码
			req.setCharacterEncoding("utf-8");
			// 接受参数
			String command = req.getParameter("command");
			String description = req.getParameter("description");
			messageList = messageService.queryMessageList(command, description);
			req.setAttribute("messages", messageList);
			// 向页面跳转
			req.getRequestDispatcher("/WEB-INF/jsp/back/list.jsp").forward(req,resp);
		} catch (Exception e) {
			e.printStackTrace();
			req.setAttribute("retMsg", "查询失败");
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doGet(req, resp);
	}

}

2.10编码过滤

package com.imooc.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;

/*
 * 编码过滤
 * */
public class EncodingFilter implements Filter{

	@Override
	public void destroy() {
		// TODO Auto-generated method stub
		
	}

	
	@Override
	public void doFilter(ServletRequest arg0, ServletResponse arg1, FilterChain arg2)
			throws IOException, ServletException {
		arg0.setCharacterEncoding("utf-8");
		arg1.setCharacterEncoding("utf-8");
		arg1.setContentType("text/html;charset=UTF-8");
		//过滤编码后放行
		arg2.doFilter(arg0, arg1);
	}

	@Override
	public void init(FilterConfig arg0) throws ServletException {
		
	}

}

3.0 web.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
	<display-name>mybatis</display-name>
	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
		<welcome-file>index.htm</welcome-file>
		<welcome-file>index.jsp</welcome-file>
		<welcome-file>default.html</welcome-file>
		<welcome-file>default.htm</welcome-file>
		<welcome-file>default.jsp</welcome-file>
	</welcome-file-list>
	<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>
<span style="white-space:pre">	<filter>
<span style="white-space:pre">		</span><filter-name>EncodingFilter</filter-name>
<span style="white-space:pre">		</span><filter-class>com.imooc.filter.EncodingFilter</filter-class>
<span style="white-space:pre">	</span></filter>
<span style="white-space:pre">	</span><filter-mapping>
<span style="white-space:pre">		</span><filter-name>EncodingFilter</filter-name>
<span style="white-space:pre">		</span><url-pattern>/*</url-pattern>
<span style="white-space:pre">	</span></filter-mapping></span>
</web-app>

2.11 list.jsp页面实现

list.jsp

<!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 %>css/all.css" rel="stylesheet" type="text/css" />
		<script src="<%= basePath %>js/common/jquery-1.8.0.min.js"></script>
		<script src="<%= basePath %>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" οnclick="javascript:selAllCheckbox('id')"/></th>
								    <th>序号</th>
								    <th>指令名称</th>
								    <th>描述</th>
								    <th>操作</th>
								</tr>
								<c:forEach items="${requestScope.messages}" 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> 条
							共<b>${page.totalPage }</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>

2.12结果

三、删除数据

3.1删除

  <!-- 单条数据删除,基本数据类一般使用_parameter取直 -->
  <delete id="deleteOne" parameterType="int">
  	delete from message where id=${_parameter}
  </delete>

java代码

/**   
	* @Function: deleteOne
	* @Description: 通过id删除单条信息
	* @param:int id 消息id
	* @param:String description 描述
	* @exception Exception
	* @retrun List<Message>
	* @version: v1.0.0
	* @author: caojx
	* @date: 2016/7/19 9:30
	*/
	public void deleteOne(int id) throws Exception{
		SqlSession session = null;
		try{
			session = dbAccess.getSqlSession();
			session.delete(Message.class.getName()+".deleteOne", id);
			session.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new Exception(e.getMessage());
		}finally{
			if (session != null) {
				session.close();
			}
		}
	}

3.2批量删除实现

  <!-- 
  		批量删除数据
  		如果是List,使用 foreach标签遍历出list中的值 collection="list" 一般是固定 的去list的值
  		item接收list中遍历出每一个值，sepatator中的值表示分割符号"," mybatis会自动去除最后一个","
   -->
  <delete id="deleteBatch" parameterType="java.util.List">
  	delete from message where id in( 
  		<foreach collection="list" item="item" separator=",">
  			${item}
  		</foreach>
  	)
  </delete>

java代码实现

/**   
	* @Function: deleteBatch
	* @Description: 批量删除信息
	* @param:List<Integer> idS 消息id
	* @exception Exception
	* @retrun List<Message>
	* @version: v1.0.0
	* @author: caojx
	* @date: 2016/7/19 9:30
	*/
	public void deleteBatch(List<Integer> idList)throws Exception{
		SqlSession session = null;
		try{
			session = dbAccess.getSqlSession();
			session.delete(Message.class.getName()+".deleteBatch", idList);
			session.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new Exception(e.getMessage());
		}finally{
			if (session != null) {
				session.close();
			}
		}
	}

多选实现参考list.jsp页面

/*
 * 批量删除数据
 */
function deleteBatch(basePath){
	$("#mainForm").attr("action",basePath+"DeleteBatch.action");
	$("#mainForm").submit();
}

/*
 * 全选
 * */
function selAllCheckbox(checkBoxName){
	var o = document.getElementsByName(checkBoxName);
	for(var i = 0; i < o.length; i++){
		o[i].checked=true;
	}
	
}

3.3 DeleteBatchServlet.java获取需要删除的数据

package com.imooc.servlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.imooc.service.MessageService;

public class DeleteBatchServlet extends HttpServlet{

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	MessageService messageService = new MessageService();
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		try {
			String[] idS =req.getParameterValues("id");
			messageService.deleteBatch(idS);
			// 向页面跳转
			req.setAttribute("retMsg", "批量删除成功");
			log.debug("删除成功debug");
		} catch (Exception e) {
			e.printStackTrace();
			req.setAttribute("retMsg", "批量删除出错");
		}finally{
			req.getRequestDispatcher("/WEB-INF/jsp/back/list.jsp").forward(req, resp);
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doGet(req, resp);
	}
	
}

四、常用的标签或属性

4.1 foreach标签遍历map,list,array

<pre name="code" class="html"><foreach collection="array"  index="i" item="item"  open="(" separator="," close=")">

collection 为集合， open为起始符号 separator为分割符号 close为结束符号

数组：

i：为索引下标 item为对象或值

 public void dynamicForeach2Test() {    
	session = dbAccess.getSqlSession();
        int[] ids = new int[] {1,3,6,9};
	session.delete(Message.class.getName()+".deleteBatch2", ids);
        session.close();
    }

 <delete id="deleteBatch2">
       delete from message where id in
        <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </delete>

List ：

index:list索引 item为list中村存放的对应或值

public void deleteBatch(List<Integer> idList)throws Exception{
		SqlSession session = null;
		try{
			session = dbAccess.getSqlSession();
			session.delete(Message.class.getName()+".deleteBatch", idList);
			session.commit();
		}catch(Exception e){
			e.printStackTrace();
			throw new Exception(e.getMessage());
		}finally{
			if (session != null) {
				session.close();
			}
		}
	}

 <delete id="deleteBatch" parameterType="java.util.List">
  	delete from message where id in( 
  		<foreach collection="list" item="item" separator=",">
  			${item}
  		</foreach>
  	)
  </delete>

Map：

index：map中存放的key item为对象或值

 public List<Message> queryMessageList() {    
    session = dbAccess.getSqlSession();
    List<Integer> ids = newArrayList<Integer>();
      ids.add(1);
      ids.add(2);
      ids.add(3);
      ids.add(6);
      ids.add(7);
      ids.add(9);
      Map<String, Object> params = newHashMap<String, Object>();
      params.put("ids", ids);
      params.put("command", "段子");
      List<Message> messagesList = session.selectList(Message.class.getName()+".queryMessage", params);
      session.close();
     return messageList;
}

<select id="queryMessage "  parameterType="java.util.Map">
       select * from message where title like concat(concat("%",#{command}),"%") and id in
      <foreach collection="ids" index="index" item="item"open="(" separator="," close=")">
         #{item}
      </foreach>
 </select>

4.2 resultMap与resultType

Mybatis中每一个查询返回的都是resultMap,只时使用了resultType的时候，Mybatist会将resultMap中的值映射到resultType指定类型的属性上

4.2.1 resultMap

使用select标签使用resultMap属性的时候，需要提供resultMap，生命数据库中的字段与实体类中的某种映射关系,主要在多表查询的时候使用

<resultMap type="com.imooc.bean.Message" id="MessageResult">
    <id column="id" jdbcType="INTEGER" 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标签用于书写查询语句-->
  <select id="queryMessageList" parameterType="com.imooc.bean.Message" resultMap="MessageResult">
    select id,command,description,content from message
    <where><!-- 
    		test中书写布尔表达式，如果成功则拼接下边的条件, command是parameterType中的成员变量
    		拼接sql的时候会将‘#{command}’替换成?,取值类似于ognl
    		-->
    	<if test="command != null and !"".equals(command.trim())">
	    	and command=#{command}
	    </if>
	    <!-- like 查询一般会拼接concat()拼接两个字符串 -->
	    <if test="description != null and ''!=description.trim()">
	    	and description like concat(concat('%',#{description}),'%')
	    </if>
    </where>
  </select>

4.2.2 resultType

在查询标签select中使用resultType属性的时候，不需要生命resultMap标签生命映射关系，Mybatis会自动将查询到的值映射到resultType所指向的实体类中

<!-- select标签用于书写查询语句-->
  <select id="queryMessageList" parameterType="com.imooc.bean.Message" resultType="com.imooc.bean.Message">
    select id,command,description,content from message
    <where><!-- 
    		test中书写布尔表达式，如果成功则拼接下边的条件, command是parameterType中的成员变量
    		拼接sql的时候会将‘#{command}’替换成?,取值类似于ognl
    		-->
    	<if test="command != null and !"".equals(command.trim())">
	    	and command=#{command}
	    </if>
	    <!-- like 查询一般会拼接concat()拼接两个字符串 -->
	    <if test="description != null and ''!=description.trim()">
	    	and description like concat(concat('%',#{description}),'%')
	    </if>
    </where>
  </select>

4.3 where 标签

如4.2，如果if 中条件按成立 where会自动拼接上而且会去掉最前面的那个and 或者or

select id,command,description,content from message  where...

4.4 sql标签

相当于java中常量的定义

<sql id="colums">id,command,description,content</sql>

我们可以在其他地方引用该标签使用 <include refid="colums"/>

 <select id="queryMessageList" parameterType="com.imooc.bean.Message" resultType="com.imooc.bean.Message">
    select <include refid="<span style="font-family: Arial, Helvetica, sans-serif;">colums</span>"/> from message
    <where><!-- 
    		test中书写布尔表达式，如果成功则拼接下边的条件, command是parameterType中的成员变量
    		拼接sql的时候会将‘#{command}’替换成?,取值类似于ognl
    		-->
    	<if test="command != null and !"".equals(command.trim())">
	    	and command=#{command}
	    </if>
	    <!-- like 查询一般会拼接concat()拼接两个字符串 -->
	    <if test="description != null and ''!=description.trim()">
	    	and description like concat(concat('%',#{description}),'%')
	    </if>
    </where>
  </select>

4.5 set标签

一般用update标签中，如下可以去掉最后一个 “,”

 <!--更新   -->
  <update id="updateMessage">
  	update message
  	<set>
  		<if test="command != null and !''!=command.trim()">
	    	command=#{command},
	    </if>
	    <if test="description != null and ''!=description.trim()">
	    	description=#{description},
	    </if>
  	</set>
  </update>

4.6 insert 插入语句标签

  <insert id="saveMessage" parameterType="java.util.Map">
  	insert into message values(#{message.id},#{message.command},#{message.description},#{message.content})
  </insert>

4.7 choose标签中的<when></when> ..<otherwise></otherwise>

该标签相当于java中的swtich..case

 <update id="updateMessage">
  	update message
  	<set>	
  	<choose>
  		<when test="command != null and !''!=command.trim()">
  			command=#{command},
  		</when>
  		<when test="description != null and ''!=description.trim()">
  			description=#{description},
  		</when>
  		<otherwise>
  			<!-- 其他 -->
  		</otherwise>
  	</choose>
	</set>
  </update>

4.8 trim标签

trim标签可以个性化定制一些功能如下边是常用的几个属性

prefix 前缀
suffix 后缀
prefixOverrides 去掉前边的内容
suffixOverrides 去掉后边的内容

具体用法，这里不讲解
可以参考点击打开链接 http://my.oschina.net/xiaozhiwen/blog/496224

4.9 association标签

对于该标签请查看博客这里讲的非常好

点击打开链接 http://my.oschina.net/u/1425545/blog/194647

五、一对多关系实现

5.1自动消息回复机器人

假设一条命令可以返回多种结果

5.1.1 建立command表和command_content

--一
create table command(
	id number(10)  constraint command_pk_id primary key,
	name varchar2(20) not null,
	description varchar2(20) not null
);

--多
create table command_content(
	id number(10)  constraint command_content_pk_id primary key,
	content varchar2(200) not null,
	command_id number(10) references command(id)
);

insert into command values(001,'段子','精彩段子');
insert into command values(002,'笑话','笑话大王');
insert into command values(003,'体育','体育新闻');

insert into command_content(id,command_id,content) values(001,001,'每个人都希望自己成为重要的人， 但大部分都只能成为让別人重要的人。');
insert into command_content(id,command_id,content) values(002,001,'距离之所以可怕，因为根本不知道对方是把你想念还是把你忘记。');
insert into command_content(id,command_id,content) values(003,001,'20岁的贪玩，造就了30岁的无奈，30岁的无奈，导致了40岁的无为，40岁的无为，奠定了50岁的失败，50岁的失败酿造了一辈子的悲哀');
insert into command_content(id,command_id,content) values(004,002,'后来妹妹真的把灯关了 忽然间一双萤火虫飞了进来 妹妹很紧张的说：姐惨了，蚊子提着灯笼来找我们了');
insert into command_content(id,command_id,content) values(005,002,'滴～老人卡～！全车人冻住，皆而望她。她一脸黑线说：看什么，天山童老，没见过啊？一大爷起身，说：来，大娘，您坐这。');
insert into command_content(id,command_id,content) values(006,002,'今天坐在我旁边，看着我的QQ作思考状，突然跳起大怒道：”你的QQ分组是不是根据胸大小来分的？，给我分在A组里“然后真没然后了');

insert into command_content(id,command_id,content) values(007,003,'国乒入住奥运村:很失望 伊娃恐因禁赛退役');
insert into command_content(id,command_id,content) values(008,003,'热身赛-皇马1-3负巴黎 20时播多特VS曼城');
insert into command_content(id,command_id,content) values(009,003,'新浪前方探营举重队 老干妈成为大明星');

5.1.2建立根据表建立相应的实体类

/**
 * 指令实体类-主表
 * 一对多单向关联，一的一方保存多的一方的集合
 **/
public class Command {

	private int id;
	/**
	 * 命令
	 * */
	private String name;
	/**
	 * 描述
	 * */
	private String description;

	/**
	 * 一条指令对应的自动回复内容列表
	 * */
	private List<CommandContent> contentList;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	
	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getDescription() {
		return description;
	}

	public void setDescription(String description) {
		this.description = description;
	}

	public List<CommandContent> getContentList() {
		return contentList;
	}

	public void setContentList(List<CommandContent> contentList) {
		this.contentList = contentList;
	}

}

package com.imooc.bean;

/**
 * 内容实体类-子表  
 * 多的一方
 **/
public class CommandContent {
	
	/**
	 * id
	 * */
	private int id;
	
	/**
	 * 内容
	 * */
	private String content;
	
	
	/**
	 * 指令id
	 * */
	private String commandId;


	public int getId() {
		return id;
	}


	public void setId(int id) {
		this.id = id;
	}

	public String getContent() {
		return content;
	}


	public void setContent(String content) {
		this.content = content;
	}


	public String getCommandId() {
		return commandId;
	}


	public void setCommandId(String commandId) {
		this.commandId = commandId;
	}
	
}

5.1.3 实体类对应的mapper文件

Command.xml

<?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="com.imooc.bean.Command">

  <resultMap type="com.imooc.bean.Command" id="commandReuslt">
  <!-- column不是数据库中的字段，而是我们写的查询语句中中写的字段 -->
    <id column="c_id" jdbcType="INTEGER" property="id"/>
    <result column="name" jdbcType="VARCHAR" property="name"/>
    <result column="description" jdbcType="VARCHAR" property="description"/>
    <!-- 
    	property是 com.imooc.bean.Command中子表的集合
    	resultMap映射到CommandContent.xml中的映射做一对多的关联查询
    -->
    <collection property="contentList" resultMap="com.imooc.bean.CommandContent.commandContentResult"></collection>
  </resultMap>
  
  <!-- 
  		使用关联查询，无论子表中是否有数据，主表都应该出现
  		resultMap中的column的值是查询语句中写的列名，且表名的别名a 或 b 在resultMap做映射的时候会被除去
  		即下边的查询语句将会出现两个相同的id,所以必须给一个取别名
  		我们给a表中的id取别名 c_id
   -->
  <select id="commandList" parameterType="com.imooc.bean.Command" resultMap="commandReuslt">
    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 ''!=name.trim()">
	    	and a.name=#{name}
	    </if>
	    <if test="description != null and ''!=description.trim()">
	    	and a.description like concat(concat('%',#{description}),'%')
	    </if>
    </where>
  </select>
  
</mapper>

CommandContent.xml

<?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="com.imooc.bean.CommandContent">

  <resultMap type="com.imooc.bean.CommandContent" id="commandContentResult">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="content" jdbcType="VARCHAR" property="content"/>
    <result column="command_id" jdbcType="VARCHAR" property="commandId"/>
  </resultMap>
</mapper>

5.1.4建立CommandDao.java

通过命令的名或描述查询一对多的实体对应关系

package com.imooc.dao;

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> queryComandList(String name,String description){
		List<Command> commandList = new ArrayList<Command>();
		SqlSession sqlSession = null;
		try{
			DBAccess dbAccess = new DBAccess();
			sqlSession = dbAccess.getSqlSession();
			Command command = new Command();
			command.setName(name);
			command.setDescription(description);
			commandList = sqlSession.selectList(Command.class.getName()+".commandList", command);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(sqlSession!=null){
			   sqlSession.close();
			}
		}
		return commandList;
	}
}

5.1.5 建立QueryService.java

通过该service,调用Dao层中查询的数据，随机选取一条返回

package com.imooc.service;

import java.util.List;
import java.util.Random;

import com.imooc.bean.Command;
import com.imooc.bean.CommandContent;
import com.imooc.dao.CommandDao;
import com.imooc.util.Iconst;

/*
 * 查询相关业务
 * */
public class QueryService {

	/*
	 * 通过查询相关的指令自动回复相关的内容
	 */
	public String queryByCommand(String command) throws Exception {
		List<Command> commandList = null;
		StringBuffer result = null;
		CommandDao commandDao = new CommandDao();
		if ("帮助".equals(command.trim())) {//输入帮助命令，返回命令集到页面
			commandList = commandDao.queryComandList(null, null);
			result = new StringBuffer();
			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.queryComandList(command, null);
		if (commandList.size() > 0) {
			Random random = new Random();
			List<CommandContent> contentList = commandList.get(0).getContentList();
			return contentList.get(random.nextInt(contentList.size())).getContent();
		}
		return "不好意思,您没有按照套路出牌";

	}
}

5.1.6自动回复AutoReplaySerlvet.java

package com.imooc.servlet;

import java.io.IOException;
import java.io.Writer;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.imooc.service.QueryService;

public class AutoReplyServlet extends HttpServlet{

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		try {
			//content是用户输入的内容
			String content = req.getParameter("content");
			QueryService queryService = new QueryService();
			Writer out = resp.getWriter();
			String str = queryService.queryByCommand(content);
			out.write(str);
			out.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doGet(req, resp);
	}
	
}

5.1.7 talk.jsp页面

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%
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 %>css/jscrollpane1.css" />
	<script src="<%= basePath %>js/common/jquery-1.8.0.min.js" type="text/javascript"></script>
	<!-- the mousewheel plugin -->
	<script type="text/javascript" src="<%= basePath %>js/common/jquery.mousewheel.js"></script>
	<!-- the jScrollPane script -->
	<script type="text/javascript" src="<%= basePath %>js/common/jquery.jscrollpane.min.js"></script>
	<script type="text/javascript" src="<%= basePath %>js/common/scroll-startstop.events.jquery.js"></script>
	<!--讨论区滚动条end-->
	<script type="text/javascript" src="<%= basePath %>js/front/talk.js"></script>
	</head>
	<body οnkeydοwn="keyDown()">
		<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" οnclick="send();" type="button" title="send" value="send" />
			</div>
		</div>
		<div style="text-align:center;margin:50px 0; font:normal 14px/24px '宋体';"></div>
	</body>
</html>

还有一些js文件这里就不贴上代码了，看结果图

、

总结：mybaits优缺点

1.sql语句与代码分离
优点：便于维护
缺点：不方便调试，需要借助日志工具

2.用标签控制动态sql的拼接
优点：用标签代理编写逻辑代码
缺点：拼接复杂的sql语句时，没有比代码灵活，比较复杂

3.结果集于java对象的自动映射
优点：保证名称相同即可以自动映射
缺点：对开发人员所写的sql依赖性强

4.编写原生的sql
优势：接近jdbc,很灵活
劣势：对sql语句依赖程度很高
对于不同的数据库，需要修改相应的sql

注：本案例思想由慕课网提供指导

coding小强

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MyBatis入门学习

一、Mybatis背景介绍MyBatis 是支持普通 SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML或注解用于配置和原始映射，将接口和 Java 的POJOs映射成数据库中的记录。二、环境准备2.1 提示本案例使用servlet+mybaits+
复制链接

扫一扫