MyBatis笔记二-增删改查-事务-简单的多表联查

Title: MyBatis笔记 二

Date: 2017-07-03

内容简介:

   接着笔记一，测试了MyBatis对数据库表的增删改查，事务处理，简单的多表联查功能。

正文：

直接贴源代码

spring-servlet.xml

<?xml version="1.0" encoding="UTF-8"?><beans xmlns="http://www.springframework.org/schema/beans"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:p="http://www.springframework.org/schema/p"xmlns:aop="http://www.springframework.org/schema/aop"xmlns:context="http://www.springframework.org/schema/context"xmlns:mvc="http://www.springframework.org/schema/mvc"xmlns:tx="http://www.springframework.org/schema/tx"xmlns:mongo="http://www.springframework.org/schema/data/mongo"xmlns:cache="http://www.springframework.org/schema/cache"xmlns:c="http://www.springframework.org/schema/c"xmlns:amq="http://activemq.apache.org/schema/core"xmlns:websocket="http://www.springframework.org/schema/websocket"xmlns:jms="http://www.springframework.org/schema/jms"xmlns:util="http://www.springframework.org/schema/util"   xsi:schemaLocation="http://www.springframework.org/schema/beanshttp://www.springframework.org/schema/beans/spring-beans.xsdhttp://www.springframework.org/schema/websockethttp://www.springframework.org/schema/websocket/spring-websocket.xsdhttp://www.springframework.org/schema/mvchttp://www.springframework.org/schema/mvc/spring-mvc-3.2.xsdhttp://www.springframework.org/schema/contexthttp://www.springframework.org/schema/context/spring-context.xsdhttp://www.springframework.org/schema/data/mongo       http://www.springframework.org/schema/data/mongo/spring-mongo-1.0.xsd   http://www.springframework.org/schema/txhttp://www.springframework.org/schema/tx/spring-tx.xsdhttp://www.springframework.org/schema/aophttp://www.springframework.org/schema/aop/spring-aop.xsdhttp://www.springframework.org/schema/cachehttp://www.springframework.org/schema/cache/spring-cache.xsdhttp://activemq.apache.org/schema/core http://activemq.apache.org/schema/core/activemq-core.xsdhttp://www.springframework.org/schema/jms http://www.springframework.org/schema/jms/spring-jms.xsdhttp://www.springframework.org/schema/utilhttp://www.springframework.org/schema/util/spring-util.xsd">             <!-- Scan Controller Range -->  <context:component-scan base-package="com.nuoke.controller" /><!-- HandlerMapping -->      <bean          class="org.springframework.web.servlet.handler.BeanNameUrlHandlerMapping" />        <!-- HandlerAdapter -->      <bean          class="org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter" />    <!-- enable spring mvc annotation driven--><mvc:annotation-driven/>    <bean id="jspViewResolver"class="org.springframework.web.servlet.view.InternalResourceViewResolver"><property name="prefix" value="/WEB-INF/view/" /><property name="suffix" value=".jsp" /></bean><websocket:handlers>    <!-- ws://localhost:8080/DAS2017/websocket.do映射到/websocket.do-->        <websocket:mapping path="/websocket.do" handler="demoWSHandler"/>     </websocket:handlers>    <bean id="demoWSHandler" class="com.nuoke.ws.WSHandler"/>    <bean id="wsClientManager" class="com.nuoke.ws.WSClientManager"/>        <!-- 事务管理器 -->    <bean id="txManager"      class="org.springframework.jdbc.datasource.DataSourceTransactionManager">      <property name="dataSource" ref="dataSource" />    </bean>     <!-- 数据库 -->    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property><property name="url" value="jdbc:mysql://127.0.0.1/das2017"></property><property name="username" value="root"></property><property name="password" value="kagula123456"></property></bean><bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"><!--dataSource属性指定要用到的连接池--><property name="dataSource" ref="dataSource"/><!--configLocation属性指定mybatis的核心配置文件--><property name="configLocation" value="classpath:mybatis-config.xml"/><!-- 扫描 entity 包,使用别名 com.nuoke.model.UserInfo->UserInfo 有多个用;符号分隔-->        <property name="typeAliasesPackage" value="com.nuoke.model.UserInfo;"></property>                 <!-- 扫描 sql 配置文件: mapper 需要的 xml 文件 -->        <property name="mapperLocations" value="classpath:mapping/*.xml"></property></bean><!-- mapper bean --><bean id="userInfoMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">      <property name="mapperInterface" value="com.nuoke.dao.UserInfoMapper" />      <property name="sqlSessionFactory" ref="sqlSessionFactory" />  </bean>   </beans>

新建加的model

package com.nuoke.model;public class UserInfo_1to1 extends UserInfo{private String roleTypes;public String getRoleTypes() {return roleTypes;}public void setRoleTypes(String roleTypes) {this.roleTypes = roleTypes;}}

测试MyBatis用的java文件

package com.nuoke.controller;import java.io.IOException;import java.util.Iterator;import java.util.List;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.datasource.DataSourceTransactionManager;import org.springframework.stereotype.Controller;import org.springframework.transaction.TransactionDefinition;import org.springframework.transaction.TransactionStatus;import org.springframework.transaction.support.DefaultTransactionDefinition;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RequestParam;import org.springframework.web.bind.annotation.ResponseBody;import org.springframework.web.servlet.ModelAndView;import org.springframework.web.socket.TextMessage;import org.springframework.web.socket.WebSocketSession;import com.nuoke.dao.UserInfoMapper;import com.nuoke.model.UserInfo;import com.nuoke.model.UserInfo_1to1;import com.nuoke.ws.WSClientManager;@Controller  @RequestMapping(value = "/test")  public class TestController {private static Logger logger = LoggerFactory.getLogger(TestController.class);@Autowiredprivate WSClientManager wsClientManager;@Autowiredprivate UserInfoMapper userInfoMapper;@Autowiredprivate DataSourceTransactionManager txManager;//http://localhost:8080/DAS2017/test/testReturnJSON.do?data=kagula@RequestMapping(value = "/testReturnJSON.do")@ResponseBody    public Object testReturnJSON(@RequestParam("data") String data) {        logger.debug("我的测试<<"+data);        TextMessage toWSMsg = new TextMessage(data);                //把收到的消息群发到WS客户端try {Iterator<WebSocketSession> it= wsClientManager.setWSS.iterator();while(it.hasNext()){WebSocketSession client = it.next();client.sendMessage(toWSMsg);}} catch (IOException e) {// TODO Auto-generated catch blocke.printStackTrace();}        return "{\"data\":\""+data+"\"}";      }@RequestMapping(value = "/returnModelView.do")      public ModelAndView returnModelView() {                //如果用户访问的时候不带userID参数，就不会映射到这个handler中。在Web browser中会提示400错误。          ModelAndView mav = new ModelAndView();          mav.setViewName("returnModelView");          //mav.addObject("userID", userID);        return mav;      }//测试MyBatis的集成是否正确。//http://localhost:8080/DAS2017/test/testMyBatis.do@RequestMapping(value="/testMyBatis.do")@ResponseBodypublic Object testMyBatis(){ UserInfo entity = userInfoMapper.selectByPrimaryKey(1);   String strJSON = String.format("{\"name\":\"%s\"}", entity.getLoginname());  return strJSON;}//function//测试MyBatis的Add//http://localhost:8080/DAS2017/test/testMyBatisAdd.do@RequestMapping(value="/testMyBatisAdd.do")@ResponseBodypublic Object testMyBatisAdd(){ UserInfo entity = new UserInfo();  entity.setLoginname("test1"); entity.setPassword("123");  userInfoMapper.insertSelective(entity);   String strJSON = String.format("{\"name\":\"%s\"}", entity.getLoginname());  return strJSON;}//function//测试MyBatis的Del//http://localhost:8080/DAS2017/test/testMyBatisDel.do@RequestMapping(value="/testMyBatisDel.do")@ResponseBodypublic Object testMyBatisDel(){   userInfoMapper.deleteByPrimaryKey(3);   return "{}";}//function//测试MyBatis的Update//http://localhost:8080/DAS2017/test/testMyBatisUpdate.do@RequestMapping(value="/testMyBatisUpdate.do")@ResponseBodypublic Object testMyBatisUpdate(){ UserInfo entity = new UserInfo(); entity.setIduserinfo(2); entity.setPassword("123123");  userInfoMapper.updateByPrimaryKeySelective(entity);   return "{}";}//function//测试MyBatis的Transaction//http://localhost:8080/DAS2017/test/testMyBatisTransaction.do//也可以参考下面的资料，以注解方式提交事务。//http://www.jb51.net/article/85622.htm//含注解的java必须要被spring扫描到，要在spring-servlet.xml中配置Spring扫描范围。@RequestMapping(value="/testMyBatisTransaction.do")@ResponseBodypublic Object testMyBatisTransaction(){DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); TransactionStatus status = txManager.getTransaction(def); try{         UserInfo user = new UserInfo(1,"Transaction1","1231");              int affectedCount = userInfoMapper.updateByPrimaryKeySelective(user); // 因后面的异常而未执行commit语句            logger.info("affectedCount="+affectedCount);                        UserInfo user2 = new UserInfo(2,"Transaction2","1222");              int affectedCount2 = userInfoMapper.updateByPrimaryKeySelective(user2); // 因后面的异常而未执行commit语句            logger.info("affectedCount2="+affectedCount2);              int i = 2 / 0; // 触发运行时异常}catch(Exception ex){logger.error(ex.getMessage());return "{\"code\":1}";} txManager.commit(status);return "{\"code\":0}";}//function//由于上面的function是负样本，我们还需要个正样本@RequestMapping(value="/testMyBatisTransaction2.do")@ResponseBodypublic Object testMyBatisTransaction2(){DefaultTransactionDefinition def = new DefaultTransactionDefinition(); def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED); TransactionStatus status = txManager.getTransaction(def); try{         UserInfo user = new UserInfo(1,"TS1","111");              int affectedCount = userInfoMapper.updateByPrimaryKeySelective(user); // 因后面的异常而未执行commit语句            logger.info("affectedCount="+affectedCount);                        UserInfo user2 = new UserInfo(2,"TS2","222");              int affectedCount2 = userInfoMapper.updateByPrimaryKeySelective(user2); // 因后面的异常而未执行commit语句            logger.info("affectedCount2="+affectedCount2);              //和上个function的唯一区别是，去掉了触发运行时异常。}catch(Exception ex){logger.error(ex.getMessage());return "{\"code\":1}";} txManager.commit(status);return "{\"code\":0}";}//function//多表联查一对一//http://localhost:8080/DAS2017/test/testMyBatisMultiTable1to1.do@RequestMapping(value="/testMyBatisMultiTable1to1.do")@ResponseBodypublic Object testMyBatisMultiTable1to1(){//第一步：要定义一个新的model可以容纳从sql返回的更多字段。//       我这里定义了UserInfo_1to1 class//第二步：Mapper.java添加新的方法//第三步，Mapper.xml中添加新的sql，返回字段同新的model对应起来，并且同java的方法名对应起来。//最后一步：使用List<UserInfo_1to1> list = userInfoMapper.selectAll();//这里下个断点后，可以看到数据被取回来了。//如果UserInfo_1to1对应的字段值，SQL语句没返回，则为null。 return "{\"code\":0,\"size\":"+list.size()+"}";}}//class

UserInfoMapper.java

package com.nuoke.dao;import java.util.List;import com.nuoke.model.UserInfo;import com.nuoke.model.UserInfo_1to1;public interface UserInfoMapper {    int deleteByPrimaryKey(Integer iduserinfo);    int insert(UserInfo record);    int insertSelective(UserInfo record);    UserInfo selectByPrimaryKey(Integer iduserinfo);    int updateByPrimaryKeySelective(UserInfo record);    int updateByPrimaryKey(UserInfo record);        //在向导基础上新增一个方法，用来验证多表联查。    List<UserInfo_1to1> selectAll();}

UserInfoMapper.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.nuoke.dao.UserInfoMapper">  <resultMap id="BaseResultMap" type="com.nuoke.model.UserInfo">    <id column="idUserInfo" jdbcType="INTEGER" property="iduserinfo" />    <result column="loginName" jdbcType="VARCHAR" property="loginname" />    <result column="password" jdbcType="VARCHAR" property="password" />  </resultMap>  <sql id="Base_Column_List">    idUserInfo, loginName, password  </sql>  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">    select     <include refid="Base_Column_List" />    from userinfo    where idUserInfo = #{iduserinfo,jdbcType=INTEGER}  </select>  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">    delete from userinfo    where idUserInfo = #{iduserinfo,jdbcType=INTEGER}  </delete>  <insert id="insert" parameterType="com.nuoke.model.UserInfo">    insert into userinfo (idUserInfo, loginName, password      )    values (#{iduserinfo,jdbcType=INTEGER}, #{loginname,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}      )  </insert>  <insert id="insertSelective" parameterType="com.nuoke.model.UserInfo">    insert into userinfo    <trim prefix="(" suffix=")" suffixOverrides=",">      <if test="iduserinfo != null">        idUserInfo,      </if>      <if test="loginname != null">        loginName,      </if>      <if test="password != null">        password,      </if>    </trim>    <trim prefix="values (" suffix=")" suffixOverrides=",">      <if test="iduserinfo != null">        #{iduserinfo,jdbcType=INTEGER},      </if>      <if test="loginname != null">        #{loginname,jdbcType=VARCHAR},      </if>      <if test="password != null">        #{password,jdbcType=VARCHAR},      </if>    </trim>  </insert>  <update id="updateByPrimaryKeySelective" parameterType="com.nuoke.model.UserInfo">    update userinfo    <set>      <if test="loginname != null">        loginName = #{loginname,jdbcType=VARCHAR},      </if>      <if test="password != null">        password = #{password,jdbcType=VARCHAR},      </if>    </set>    where idUserInfo = #{iduserinfo,jdbcType=INTEGER}  </update>  <update id="updateByPrimaryKey" parameterType="com.nuoke.model.UserInfo">    update userinfo    set loginName = #{loginname,jdbcType=VARCHAR},      password = #{password,jdbcType=VARCHAR}    where idUserInfo = #{iduserinfo,jdbcType=INTEGER}  </update>    <!-- 如果数据库字段名同model字段名一样，直接用model -->  <select id="selectAll" resultType="com.nuoke.model.UserInfo_1to1">    select a.idUserInfo,a.loginName,group_concat(b.roleType) roleTypes    from userinfo a left join    (select b1.idUserInfo,b2.roleType from userrolerelation b1 left join roleinfo b2 on b1.idRoleInfo=b2.idRoleInfo)  b    on a.idUserInfo=b.idUserInfo    group by a.idUserInfo,a.loginName   </select>    </mapper>

注意,添加多表联查功能，涉及[1]新建model, [2]Mapper.java添加 方法，[3]Mapper.xml添加映射。