MyBatis中#{}占位符与${}拼接符的用法说明

<?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= "test" >

   <! -- 根据用户编号，查询单个用户实体 -->

   < select id= "findUserById" parameterType= "int" resultType= "com.pjb.mybatis.po.User" >

     SELECT * FROM tb_user WHERE id = #{id}

   </ select >

   <! -- 新增用户 -->

   < insert id= "insertUser" parameterType= "com.pjb.mybatis.po.User" >

     INSERT INTO tb_user(user_name,blog_url,remark)

     VALUES (#{userName},#{blogUrl},#{remark});

   </ insert >

</mapper>

<?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= "test" >

   <! -- 根据用户名称，模糊查询用户列表 -->

   < select id= "findUserByUserName" parameterType= "java.lang.String" resultType= "com.pjb.mybatis.po.User" >

     SELECT * FROM tb_user WHERE user_name LIKE '%${value}%'

   </ select >

</mapper>

jdbc.driver=com.mysql.cj.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/db_admin?useSSL= false &amp

#jdbc.username=root

#jdbc. password =123456

<!-- 加载用于数据库配置的属性文件 -->

< properties resource = "db.properties" >

   <!-- 启用默认值特性，这样${}拼接符才可以设置默认值 -->

   < property name = "org.apache.ibatis.parsing.PropertyParser.enable-default-value" value = "true" />

</ properties >

package com.lanou3g.mybaties.bean;

import lombok.Getter;

import lombok.Setter;

@Getter

@Setter

public class Teacher {

   private Integer id;

   private String tname;

   private Integer age;

   private Integer salary;

public Teacher(){}

   @Override

   public String toString() {

     return "Teacher{" +

         "id=" + id +

         ", tname='" + tname + '\ '' +

         ", salary=" + salary +

         ", remark='" + remark + '\ '' +

         ", age=" + age +

         '}' ;

   }

   private String remark;

   public Teacher(Integer id, String tname, Integer age, Integer salary, String remark) {

     this .id = id;

     this .tname = tname;

     this .age = age;

     this .salary = salary;

     this .remark = remark;

   }

   public Teacher(Integer id) {

     this .id = id;

   } 

}

package com.lanou3g.mybaties.dao;

import com.lanou3g.mybaties.bean.Teacher;

import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface TeacherDao {

   List<Teacher> queryAll();

   Teacher queryById( int id);

   Teacher queryByIdAndAge( @Param ( "id" ) int id, @Param ( "age" ) int age);

   int insertTeacher(Teacher teacher);

   int insertTeacherByParam( @Param ( "tname" ) String tname, @Param ( "age" ) int age);

   int updateTeacherById(Teacher teacher);

   int deleteTeacherById( int id);

}

<? 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" />

   < settings >

   < setting name = "mapUnderscoreToCamelCase" value = "false" />

   </ settings >

   < typeAliases >

     <!--这样我们就可以在mybatis的的上下文中使用Teacher来代替全路径名了，减少配置的复杂度。 -->

     < typeAlias type = "com.lanou3g.mybaties.bean.Teacher" alias = "Teacher" />

   <!--default属性，这个属性作用就是指定当前情况下使用哪个数据库配置，

   也就是使用哪个<environment>节点的配置，

   default的值就是配置的<environment>标签元素的id值。-->

   < environments default = "test" >

     < environment id = "test" >

       <!-- 事务管理器：

      MANAGED: 这个配置就是告诉mybatis不要干预事务，具体行为依赖于容器本身的事务处理逻辑。

      JDBC: 这个配置就是直接使用了 JDBC 的提交和回滚设置，它依赖于从数据源得到的连接来管理事务作用域。

     -->

       <!-- 使用jdbc事务管理 -->

       < transactionManager type = "JDBC" />

       <!-- 数据库连接池 -->

       < dataSource type = "POOLED" >

         < property name = "driver" value = "${jdbc.driver}" />

         < property name = "url" value = "${jdbc.url}" />

         < property name = "username" value = "${jdbc.user}" />

         < property name = "password" value = "${jdbc.password}" />

       </ dataSource >

     </ environment >

   </ environments >

   <!--<mappers>用来在mybatis初始化的时候，告诉mybatis需要引入哪些Mapper映射文件。-->

   < mappers >

     <!--通过class属性指定mapper接口名称，此时对应的映射文件必须与接口位于同一路径下，

     并且名称相同-->

     <!--通过resource属性引入classpath路径的相对资源-->

     < mapper resource = "mapper/TeacherMapper.xml" />

   </ mappers >

</ configuration >

<? 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">

<!-- namespace对应空Dao接口的全名 -->

<!--namespace属性

在MyBatis中，Mapper中的namespace用于绑定Dao接口的，即面向接口编程。

它的好处在于当使用了namespace之后就可以不用写接口实现类，

业务逻辑会直接通过这个绑定寻找到相对应的SQL语句进行对应的数据处理-->

< mapper namespace = "com.lanou3g.mybaties.dao.TeacherDao" >

   <!-- 此处的id是查询语句的名称，对应接口中的方法名 -->

   <!--指定 resultType 返回值类型时 Teacher 类型的，

     Teacher 在这里是一个别名，代表的是 com.lanou3g.mybaties.bean.Teacher

     对于引用数据类型，都是将大写字母转小写，比如 HashMap 对应的别名是 'hashmap'

     基本数据类型考虑到重复的问题，会在其前面加上 '_'，比如 byte 对应的别名是 '_byte'

-->

   < select id = "queryAll" resultType = "Teacher" >

     <!--

      通过 resultType 指定查询的结果是 Teacher 类型的数据

      只需要指定 resultType 的类型，MyBatis 会自动将查询的结果映射成 JavaBean 中的属性

       -->

   select * from teacher;

  </ select >

   <!-- 带一个简单类型的参数， 这种情况下parameterType属性可以省略，

   mybatis可以自动推断出类型 -->

   < select id = "queryById" resultType = "Teacher" >

         select * from teacher where id = #{id};

   </ select >

   <!-- 带两个参数，需要在接口中通过@Param注解指定名称（因为编译时参数名不会保留） -->

   < select id = "queryByIdAndAge" resultType = "Teacher" >

     select * from teacher where id = #{id} and age &lt;= #{age};

   </ select >

   <!-- insert、update、delete的返回值都是int（影响行数） -->

   <!-- 自定义类型参数，通过#{属性名}可以直接获取引入类型参数的属性值 -->

   < insert id = "insertTeacher" parameterType = "Teacher" >

     insert into teacher(tname) values (#{tname});

   </ insert >

   < insert id = "insertTeacherByParam" >

     insert into teacher(tname, age) values (#{tname}, #{age});

   </ insert >

   < update id = "updateTeacherById" parameterType = "Teacher" >

     update teacher set tname = #{tname}, age = #{age} where id = #{id}

   </ update >

   < delete id = "deleteTeacherById" >

     delete from teacher where id = #{id};

   </ delete >

</ mapper >

package com.lanou3g.mybaties;

import lombok.extern.slf4j.Slf4j;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.session.ExecutorType;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.InputStream;

import java.util.concurrent.ConcurrentHashMap;

/**

  * 封装Mybatis初始化操作

  * 支持创建多env sqlSessionFactory

  * 整个应用生命周期内相同env的sqlSessionFactory对象只有一个

  */

@Slf4j

public class MyBatisTools {

   private static ConcurrentHashMap<String, SqlSessionFactory> factoryMap = new MyConcurrentHashMap();

   private static MyBatisTools myBatisTools;

   private MyBatisTools() {}

   public static MyBatisTools getInstance() {

     if (myBatisTools == null ) {

       synchronized (MyBatisTools. class ) {

         if (myBatisTools == null ) {

           myBatisTools = new MyBatisTools();

         }

       }

     }

     log.debug( "当前一共有: " + factoryMap.size() + "个SqlSessionFactory实例" );

     log.debug( "他们分别是: " + factoryMap);

     return myBatisTools;

   }

   public SqlSessionFactory getSessionFactory(String env) {

     try {

       // 1. 读入配置文件

       InputStream in = Resources.getResourceAsStream( "mybatis_conf.xml" );

       // 2. 构建SqlSessionFactory(用于获取sqlSession)

       SqlSessionFactory sessionFactory = null ;

       synchronized (factoryMap) {

         if (factoryMap.containsKey(env)) {

           return factoryMap.get(env);

         } else {

           sessionFactory = new SqlSessionFactoryBuilder().build(in, env);

           factoryMap.put(env, sessionFactory);

         }

       }

       return sessionFactory;

     } catch (Exception e) {

       log.error( "初始化SqlSessionFactory失败" , e);

       return null ;

     }

   }

   public SqlSession openSession() {

     return getSessionFactory( null ).openSession();

   }

   public SqlSession openSession( boolean autoCommit) {

     return getSessionFactory( null ).openSession(autoCommit);

   }

   public SqlSession openSession(ExecutorType executorType, boolean autoCommit) {

     return getSessionFactory( null ).openSession(executorType, autoCommit);

   }

}

/**

  * 继承原生ConcurrentHashMap，处理null key问题

  */

class MyConcurrentHashMap extends ConcurrentHashMap {

   @Override

   public Object put(Object key, Object value) {

     if (key == null ) {

       key = "null" ;

     }

     return super .put(key, value);

   }

   @Override

   public boolean containsKey(Object key) {

     if (key == null ) {

       key = "null" ;

     }

     return super .containsKey(key);

   }

   @Override

   public Object get(Object key) {

     if (key == null ) {

       key = "null" ;

     }

     return super .get(key);

   }

}

@Slf4j

public class AppTest

{

   /**

    * Rigorous Test :-)

    */

   TeacherDao teacherDao = null ;

   @Before

   public void setUp() { 

     teacherDao = MyBatisTools.getInstance().openSession( true ).getMapper(TeacherDao. class );

   }

   /**

    * 练习查询多个库(用到了多环境配置)

    */

   @Test

   public void textqueryById(){

     Teacher teacher=teacherDao.queryById( 1 );

     System.out.println(teacher);

   }

   @Test

   public void text(){

     List<Teacher> teachers=teacherDao.queryAll();

     System.out.println(teachers);

   }

   /**

    * 多个参数查询语句

    */

   @Test

   public void testQueryByIdAndAge() {

     Teacher teacherList = teacherDao.queryByIdAndAge( 1 , 65 );

     log.info( "查询结果：" + teacherList);

   }

   @Test

   public void testInsert() {

     // 新增Teacher表

     System.out.println( "--------------插入前：" );

     List<Teacher> teacherList = teacherDao.queryAll();

     System.out.println(teacherList);

     int ret = teacherDao.insertTeacher( new Teacher( "好人" ));

     log.info( "影响的行数: " + ret);

     // 比较low的写法（不推荐）

     //int ret = teacherDao.insertTeacherByParam("哈哈哥", 99);

     //log.info("影响的行数: " + ret);

     System.out.println( "--------------插入后：" );

     teacherList = teacherDao.queryAll();

     System.out.println(teacherList);

   }

   @Test

   public void testUpdate() {

     Teacher teacher = new Teacher();

     teacher.setId( 6 );

     teacher.setAge( 99 );

     teacher.setTname( "乔巴老师" );

     int rows = teacherDao.updateTeacherById(teacher);

     log.info( "更新行数：" + rows);

   }

   @Test

   public void testDelete() {

     int rows = teacherDao.deleteTeacherById( 13 );

     log.info( "删除行数：" + rows);

   }

   @Test

   public void testinsertTeacherByParam(){

int row=teacherDao.insertTeacherByParam( "hh" , 22 );

     System.out.println(row);

   }

}