Mybatis--使用xml进行增删改查

来源：互联网发布：忘记root密码 linux 编辑：程序博客网时间：2024/05/24 02:04

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

    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>

0 0