mybatis使用xml进行增删改查代码解析

2019-11-26 10:18:40

字体：大中小

来源：转载

供稿：网友

MyBatis是支持普通sql查询、存储过程和高级映射的持久层框架。

MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装。

MyBatis可以使用简单的XML或注解用于配置和原始映射，将接口和Java的POJO(Plain Old Java Objects 普通的Java对象)映射成数据库中的记录。

每一个Mybatis应用程序都以一个sqlSessionFactory对象的实例为核心。

sqlSessionFactory对象的实例可以通过sqlSessionFactoryBuilder对象来获得。sqlSessionFactoryBuilder对象可以通过xml配置文件，或从以往使用管理中准备好的Configuration类实例中来构建sqlSessionFactory对象。

【示例：使用配置类获取sqlSessionFactory】

DataSource dataSource = BlogDataSourceFactory.getBlogDataSource();TransactionFactory transactionFactory = new JdbcTransactionFactory();//环境Environment environment = new Environment("development", transactionFactory, dataSource);Configuration configuration = new Configuration(environment);//映射器类configuration.addMapper(BlogMapper.class);SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);

注意这种情况下配置是添加映射器类。映射器类是Java类，这些类包含SQL映射语句的注解从而避免了xml文件的依赖，但是xml映射仍然在大多数高级映射(比如：嵌套join映射)时需要。

出于这样的原因，如果存在xml配置文件的话，MyBatis将会自动查找和加载一个对等的XML文件(这种情况下，基于类路径下的BlogMapper.class类的类名，那么BlogMapper.xml将会被加载即class 与 XML在同一个文件目录下。如果非，则需要手动配置加载xml)。

【1】基本增删改查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.web.mapper.userMapper">   <!-- 可以解决model属性名与数据表中column列名不一致问题 jdbcType一定要大写 -->     <resultMap type="User" id="UserMap">    <id property="id" column="id" javaType="int" jdbcType="INTEGER"/>    <result property="name" column="username" javaType="string" jdbcType="VARCHAR"/>    <result property="age" column="age" javaType="int" jdbcType="INTEGER"/>   </resultMap>   <!--   注意这里的result，如果column == property 则可以直接返回Java object。   如果属性名与列名不一致，解决方法如下：   1. 使用resultMap;    2.返回hashmap ;    3.查询语句使用别名   -->   <select id="getUser" parameterType="int" resultMap="UserMap">    select * from t_user where id=#{id}   </select>   <delete id="deleteUser" parameterType="int" >    delete from t_user where id=#{id}   </delete>   <update id="updateUser" parameterType="User" >    update t_user set username=#{name},age=#{age} where id=#{id}   </update>   <insert id="insertUser" parameterType="User" >    insert into t_user(username,age) values(#{name},#{age})   </insert>   <!-- model's attr(name) different from column(username), so the result use UserMap -->   <select id="getUsers" resultMap="UserMap">    select * from t_user   </select></mapper>

注册到mybatis.xml [当与spring结合时，将不需要这个配置文件]

mybatis的配置文件

<?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>  <properties resource="jdbc.properties"/>  <!-- 配置实体类的别名 -->  <typeAliases>    <!-- <typeAlias type="com.web.model.User" alias="User"/> -->    <package name="com.web.model"/>  </typeAliases><!--   development : 开发模式  work : 工作模式 -->  <environments default="development">    <environment id="development">      <transactionManager type="JDBC" />      <dataSource type="POOLED">        <property name="driver" value="${driver}" />        <property name="url" value="${url}" />        <property name="username" value="${username}" />        <property name="password" value="${password}" />      </dataSource>    </environment>  </environments>  <mappers>    <mapper resource="com/web/mapper/userMapper.xml"/>    <mapper resource="com/web/mapper/orderMapper.xml"/>    <mapper class="com.web.mapperClass.UserMapper"/>  </mappers></configuration>

【2】通过SqlSessionFactory拿到session

这里使用xml文件获取sqlSessionFactory和sqlSession。

public static SqlSessionFactory getFactory(){	/* flow the src dir*/	String resource = "mybatis.xml";	/*MybatisUtils.class.getResourceAsStream(resource)----- it's wrong !!!!     * please distinguish the two up and down      * */	InputStream inputStream = MybatisUtils.class.getClassLoader().getResourceAsStream(resource);	SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);	return factory;}SqlSession session = factory.openSession(true);//默认手动提交；/*  两种解决方式：  1.factory.opensession(true);  2.session.commit();  */

【3】增删改查后台测试代码

/*use sql xml not annotation*/@Test  public void testAdd(){	SqlSession session = MybatisUtils.getFactory().openSession();	String statement = "com.web.mapper.userMapper.insertUser";	/*return the effect rows*/	int insert= session.insert(statement, new User("tom5", 15));	/*default is not auto commit*/	session.commit(true);	session.close();	System.out.println("effect rows.."+insert);}@Test  public void testSelect(){	/*set auto commit ,which equals to the above*/	SqlSession session = MybatisUtils.getFactory().openSession(true);	String statement = "com.web.mapper.userMapper.getUser";	/*return the effect rows*/	User user = session.selectOne(statement, 3);	System.out.println("effect rows.."+user);}@Test  public void testUpdate(){	SqlSession session = MybatisUtils.getFactory().openSession(true);	String statement = "com.web.mapper.userMapper.updateUser";	/*return the effect rows*/	int update= session.update(statement, new User(3,"tom4", 13));	System.out.println("effect rows.."+update);}@Test  public void testDelete(){	SqlSession session = MybatisUtils.getFactory().openSession();	String statement = "com.web.mapper.userMapper.deleteUser";	/*return the effect rows*/	int delete= session.delete(statement, 6);	/* commit by yourself*/	session.commit();	System.out.println("effect rows.."+delete);	session.close();}@Test  public void testGetUsers(){	SqlSession session = MybatisUtils.getFactory().openSession();	String statement = "com.web.mapper.userMapper.getUsers";	/*return the List<User>*/	List<User> users= session.selectList(statement);	session.commit();	System.out.println("effect rows.."+users);	session.close();}

Tips ：

parameterType 和 resultType 为 hashmap ：

mapper.xml :

<select id="getUserForMap" parameterType="hashmap" resultType="hashmap">    select * from c_user where id=#{id};  </select>

test code :

@Test  public void getUserForMap(){	SqlSession session = MybatisUtils.getFactory().openSession();	String statement = "com.web.mapper.userMapper.getUserForMap";	HashMap<String, Object> map = new HashMap<String, Object>();	map.put("id", 1);	/*return the effect rows*/	Object selectOne = session.selectOne(statement, map);	/*default is not auto commit*/	session.commit(true);	session.close();	System.out.println("effect rows.."+selectOne+" ,class :"+selectOne.getClass());}

result as follows :

effect rows..{id=1, age=12, name=luli} ,class :class java.util.HashMap

综上可知：mybatis 会根据参数类型和结果类型，自动进行解析封装。

【扩展基本方法】

【1】分页列表

 <select id="getListPage" parameterType="hashmap" resultMap="siteExtendDaoMap">    select id,site_id,site_name,site_number,province,city,area,address,internal_number,longitude,latitude    from tb_site    --使用动态sql    <trim prefix="where" prefixOverrides="AND |OR ">       <if test="checkState!= null and checkState!=''">        and check_state = #{checkState,jdbcType=INTEGER}      </if>      <if test="siteId!= null and siteId!=''">        and site_id like concat('%',#{siteId},'%')      </if>      <if test="siteName!= null and siteName!=''">        and site_name like concat('%',#{siteName},'%')      </if>      <if test="siteNumber!= null and siteNumber!=''">        and site_number like concat('%', #{siteNumber},'%')      </if>      <if test="province!= null and province!=''">        and province = #{province}      </if>      <if test="city!= null and city!=''">        and city = #{city}      </if>      <if test="area!= null and area!=''">        and area = #{area}      </if>    </trim>     --添加排序    <if test="sortname!= null and sortname!='' and sortorder!= null and sortorder!=''">      order by ${sortname} ${sortorder}    </if>    --添加分页    limit ${(page-1)*pagesize},${pagesize} </select>

【2】删除方法根据对象或者Id

如果参数为pojo,mybatis会自动从对象里面获取id ;

  <delete id="delete" parameterType="User">    delete from tb_user    where    id = #{id}   </delete>  <delete id="deleteById" parameterType="long">    delete from tb_user    where    id = #{id}   </delete>

【3】根据 id list 删除数据

  <delete id="deleteByIds">    delete from tb_user    where id in    --使用foreach    <foreach collection="list" item="id" open="(" separator=","close=")"> #{id}     </foreach>  </delete>

【4】getRows

通常与getListPage联合使用。

<select id="getRows" parameterType="hashmap" resultType="long">    select count(*) from tb_sys_role    <if test="keySysRole!= null">      <trim prefix="WHERE" prefixOverrides="AND |OR ">        <if test="keySysRole.id!= null">        and id = #{keySysRole.id}        </if>        <if test="keySysRole.name!= null and keySysRole.name!=''">        and name = #{keySysRole.name}        </if>        <if test="keySysRole.available!= null and keySysRole.available!=''">        and available = #{keySysRole.available}        </if>      </trim>    </if>  </select>