optimization

1. Aliases (generally not used, easy to write to see the origin of the class)

Previous wording: 

<insert id="insert"parameterType="com.luogg.domain.Person">

Under the config.xml file

<!--Alias-->
    <typeAliases>
        <typeAlias type="com.luogg.domain.Person" alias="Person"/>
    </typeAliases>
    

Under Mapper.xml file

<select id="selById" parameterType="int" resultType="Person">
        SELECT * FROM person WHERE id = #{id}
    </select>
    

2. Namespace optimization (generally not used, easy to write to see the origin of the class)

Namespaces can be modified at will, but there may be the same entity classes under different packages, so it is easy to distinguish them by adding package names.

3.sql fragment

Often we write a bunch of column names, such as all fields of select. We put frequently occurring fields into sql tags and refer to them directly in the future.

<!--sql fragment-->
    <sql id="cols">id,user_name,age,sex</sql>
    
<select id="find" resultMap="personRM">
        SELECT <include refid="cols"/> from person
    </select>
    

Dynamic sql statements (queries based on user filling conditions, such as name search, age search, or both)

Set parameterType to map
Where > represents a conditional query and automatically adds a 1 = 1 to the back
The < set > tag, which indicates the comma used when modifying, automatically deletes the last condition
< foreach > tag, circular tag, for in sub-query

<!--dynamic sql Sentence,where Label equivalent where 1=1 -->
    <select id="selDongtai" parameterType="map" resultMap="personRM">
      SELECT <include refid="cols"/> FROM person
      <where>
          <if test="name != null">and user_name like #{name}</if>
          <if test="sex != null">and sex = #{sex}</if>
      </where>
    </select>
    

@Test //dynamic sql statement
    public void selDongtai(){
        SqlSession session = factory.openSession();
        Map<String,Object> map = new HashMap<String, Object>();
        map.put("name","l%");
        map.put("sex",1);
        List<Person> list = session.selectList("com.luogg.mapper.PersonMapper.selDongtai",map);
        System.out.println(list.size());
        for(Person p : list){
            System.out.println(p);
        }
    }
    

Special Character Processing CDATA

When we encounter special characters, we need to output them as they are, such as <>, and then add <![CDATA [the content of the output as it is]>. 

<!--Processing of Special Characters,Query people aged 20 to 22-->
    <select id="selTeshu" parameterType="map" resultMap="personRM">
        SELECT <include refid="cols"/> FROM person
        <where>
            <if test="name != null">and user_name like #{name}</if>
            <if test="ageStart !=null"> and age >= #{ageStart}</if>
            <if test="ageEnd !=null"> <![CDATA[and age <=]]> #{ageEnd}</if>
        </where>
    </select>
    

@Test  //Special Character Processing
    public void testTeshu(){
        SqlSession session = factory.openSession();
        Map<String,Object> map = new HashMap<String, Object>();
        map.put("ageStart",20);
        map.put("ageEnd",22);
        List<Person> list = session.selectList("com.luogg.mapper.PersonMapper.selTeshu",map);
        System.out.println(list.size());
        for(Person p : list){
            System.out.println(p);
        }
    }