Mybatis中数据库的增删改查和动态Sql

数据库的增删改查:
马克- to-win:马克 java社区:防盗版实名手机尾号: 73203
马克-to-win:以下例子的配置文件中出现了一些mybatis特有的动态Sql的标签,比如trim,foreach或set标签。动态Sql是为了应对更多的情况。trim标签的功能是可以在自己包含的内容前加上某些前缀(prefix属性),也可以在自己包含的内容后加上某些后缀(suffix属性),可以把包含内容的首部某些内容删掉(prefixOverrides属性),也可以把包含内容的尾部的某些内容删掉(suffixOverrides属性)。



例 1.2

package com.mapper;
import com.Register;
import java.util.List;
import java.util.Map;
public interface RegisterMapper {
    public int insertBatch(List list);
    public List<Register> selectAll();
    int deleteByPrimaryKey(Integer id);
    int insert(Register record);
    int insertSelective(Register record);
    Register selectByPrimaryKey(Integer id);
    int updateByPrimaryKeySelective(Register record);
    int updateByPrimaryKey(Register record);
    List<Register> selectPageList(Map map);
    List<Register> selectList(Map map);
    int selectCount(Map map);
}


RegisterMapper.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.mapper.RegisterMapper">
  <select id="selectAll" resultType="com.Register">
        select * from register;
  </select>
    <!--
生成的结果是insert into register(Id,Name,Age) values (?,?,?) , (?,?,?)    -->
    <insert id="insertBatch" parameterType="java.util.List">
        insert into register(Id,Name,Age) 
        values
        <foreach collection="list" item="item" index="index" separator="," >
            (#{item.id},#{item.name},#{item.age})
        </foreach>
    </insert>  
  
  <resultMap id="BaseResultMap" type="com.Register">
    <id column="Id" jdbcType="INTEGER" property="id" />
    <result column="Name" jdbcType="VARCHAR" property="name" />
    <result column="Age" jdbcType="INTEGER" property="age" />
  </resultMap>
  <sql id="Base_Column_List">
    Id, Name, Age
  </sql>
  <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from register
    where Id = #{id,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from register
    where Id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.Register">
    insert into register (Id, Name, Age
      )
    values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.Register">
    insert into register
    <trim prefix="(" suffix=")" suffixOverrides=",">
      <if test="id != null">
        Id,
      </if>
      <if test="name != null">
        Name,
      </if>
      <if test="age != null">
        Age,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
      <if test="id != null">
        #{id,jdbcType=INTEGER},
      </if>
      <if test="name != null">
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="age != null">
        #{age,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.Register">
    update register
    <set>
      <if test="name != null">
        Name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="age != null">
        Age = #{age,jdbcType=INTEGER},
      </if>
    </set>
    where Id = #{id,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.Register">
    update register
    set Name = #{name,jdbcType=VARCHAR},
      Age = #{age,jdbcType=INTEGER}
    where Id = #{id,jdbcType=INTEGER}
  </update>

  <select id="selectList" parameterType="java.util.Map" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
     from
     test.register
    <where>
      <if test="id != null">
         and Id= #{id,jdbcType=INTEGER}
      </if>
      <if test="name != null">
         and Name= #{name,jdbcType=VARCHAR}
      </if>
      <if test="age != null">
         and Age= #{age,jdbcType=INTEGER}
      </if>
    </where>
  </select>
 
  <select id="selectCount" parameterType="java.util.Map" resultType="int">
     select count(1) from test.register
    <where>
      <if test="id != null">
         and Id= #{id,jdbcType=INTEGER}
      </if>
      <if test="name != null">
         and Name= #{name,jdbcType=VARCHAR}
      </if>
      <if test="age != null">
         and Age= #{age,jdbcType=INTEGER}
      </if>
    </where>
  </select>
</mapper>





mybatis-config.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>
    <typeAliases>
        <typeAlias
            alias="Register"
            type="com.Register" />              
    </typeAliases>
    <environments default="myEnv"> 
       <environment id="myEnv"> 
           <transactionManager type="jdbc"/> 
           <dataSource type="POOLED"> 
              <property name="driver" value="com.mysql.jdbc.Driver"/> 
              <property name="url" value="jdbc:mysql://localhost:3306/test"/> 
              <property name="username" value="root"/> 
              <property name="password" value="1234"/> 
           </dataSource> 
       </environment> 
    </environments>
    <mappers>
        <mapper resource="com/mapper/RegisterMapper.xml" />
    </mappers>
</configuration>


测试主程序:


import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.Register;
import com.mapper.RegisterMapper;
public class TestMark_to_win {
    private SqlSession sqlSession = null;
    private RegisterMapper registerMapper = null;

    public static void main(String[] args) {
        TestMark_to_win tmw = new TestMark_to_win();
        tmw.processData();
    }

    public void processData() {
        sqlSession = getSession();
        registerMapper = sqlSession.getMapper(RegisterMapper.class);
        dataInsert();
        dataUpdate();
        dataSelect();
        dataDelete();
        if (null != sqlSession) {
            sqlSession.commit();
            sqlSession.close();
        }
    }

    private void dataDelete() {
        System.out.println(registerMapper.deleteByPrimaryKey(51));
    }

    private void dataUpdate() {
        Register register = new Register();
        register.setId(50);
        register.setName("bbb");
        System.out.println("registerMapper.updateByPrimaryKeySelective 的返回值是 "+registerMapper.updateByPrimaryKeySelective(register));
      
        Register registerU = new Register();
        registerU.setId(52);
        registerU.setName("bbb2");
/*下面的话,去掉就会出现问题,Age列就会变成NULL*/      
//        registerU.setAge(80);
        System.out.println("registerMapper.updateByPrimaryKey 的返回值是 "+registerMapper.updateByPrimaryKey(registerU));
      
    }

    private void dataInsert() {
        Register register = new Register();
        register.setAge(80);
        register.setId(50);
        register.setName("aaa");
        registerMapper.insert(register);

        Register registerSel = new Register();
        registerSel.setId(49);
        registerMapper.insertSelective(registerSel);
      
        // 批量插入
        Register r1 = new Register();
        r1.setAge(80);
        r1.setId(51);
        r1.setName("aaa");

        Register r2 = new Register();
        r2.setAge(80);
        r2.setId(52);
        r2.setName("aaa");
        List<Register> l = new ArrayList<Register>();
        l.add(r1);
        l.add(r2);
        registerMapper.insertBatch(l);
    }

    private void dataSelect() {
        Register register = registerMapper.selectByPrimaryKey(50);
        System.out.println(register.toString());
        List<Register> list = registerMapper.selectAll();
        System.out.println("select all begin");
        for (Register r : list) {
            System.out.println(r.toString());
        }
        System.out.println("selectList begin");
        Map map = new HashMap();
        map.put("name", "aaa");
        map.put("age", 23);
        List<Register> listS = registerMapper.selectList(map);
        for (Register r : listS) {
            System.out.println(r.toString());
        }
        System.out.println("selectCount begin");
        Map mapC = new HashMap();
        mapC.put("name", "aaa");
        mapC.put("age", 23);
        int num = registerMapper.selectCount(mapC);
        System.out.println("selectCount result is "+num);
    }

    private SqlSession getSession() {
        try {
            InputStream is = Resources
                    .getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
                    .build(is);
            return sqlSessionFactory.openSession();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}