三(一)、mybatis映射文件-增删改和参数处理

一、映射文件中的增删改的实现：

1.说明：

mybatis 允许增删改直接定义以下返回值类型 Ingeger Long Boolean；Integer 和Long 表示增删改成功的数量；Boolean表示增删改成功数量大于0时，返回true，否则返回false；
手动提交 openSession.commit();sqlSessionFactory.openSession(); 不会自动提交数据；sqlSessionFactory.openSession(true); 自动提交数据 ,不需要手动提交

2.测试项目目录结构和全局配置文件：

全局配置文件mybatis-config.xml：

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration
 3   PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4   "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5 <configuration>
 6     <properties resource="dbconfig.properties"></properties>
 7     
 8     <environments default="development">
 9         <environment id="development">
10             <transactionManager type="JDBC" />
11             <dataSource type="POOLED">
12                 <property name="driver" value="${jdbc.driver}" />
13                 <property name="url" value="${jdbc.url}" />
14                 <property name="username" value="${jdbc.username}" />
15                 <property name="password" value="${jdbc.password}" />
16             </dataSource>
17         </environment>
18         <environment id="development_mysql2">
19             <transactionManager type="JDBC" />
20             <dataSource type="POOLED">
21                 <property name="driver" value="${jdbc2.driver}" />
22                 <property name="url" value="${jdbc2.url}" />
23                 <property name="username" value="${jdbc2.username}" />
24                 <property name="password" value="${jdbc2.password}" />
25             </dataSource>
26         </environment>
27     </environments>
28     
29     
30     <mappers>
31         <package name="dao"/>
32     </mappers>
33 </configuration>

View Code

3.添加：

接口EmployeeMapper.java:

1 package dao;
2 
3 import entity.Employee;
4 
5 public interface EmployeeMapper {
6     void addEmp(Employee employee);
7 }

View Code

映射文件EmployeeMapper.xml;

parameterType 可以省略
mybatis支持自增主键，自增主键的获取，mybatis也是利用statement.getGenreatedKeys()
useGeneratedKeys ="true",使用自增主键获取主键值的策略；
keyProperty:指定对应的主键属性，也就是mybatis获取到主键以后，将这个值封装给javabean的那个属性

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3   PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4   "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 <mapper namespace="dao.EmployeeMapper">
 6     <insert id="addEmp" parameterType="entity.Employee"
 7         useGeneratedKeys="true" keyProperty="id">
 8         insert into
 9         tbl_employee(last_name,gender,email) values(
10         #{lastName},#{gender},#{email}
11         )
12     </insert>
13 </mapper>

View Code

junit 测试类：

 1 @Test
 2     public void test01() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession();
10             EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
11             Employee employeeAdd = new Employee();
12             employeeAdd.setLastName("joy222");
13             employeeAdd.setGender("女");
14             employeeAdd.setEmail("joy521125@icloud.com");
15             mapper.addEmp(employeeAdd);// 测试新增
16             System.out.println(employeeAdd.getId());
17             openSession.commit();
18         } catch (Exception e) {
19             // TODO: handle exception
20         } finally {
21             if (openSession != null) {
22                 openSession.close();
23             }
24 
25         }
26     }

oracle中的添加数据的说明：

selectKey标签

keyProperty:查出的主键键值对封装给javaBean的哪个属性
order="BEFORE":当前SQL在插入SQL语句前运行；
resultType：查出数据的返回值类型；

运行顺序：

先运行selectKey查询ID的SQL，查出ID值封装给javabean对应的属性
再运行插入的SQL语句

 1  <insert id="addEmp" databaseId="oracle" parameterType="entity.Employee">
 2           
 3          <selectKey keyProperty="id" order="BEFORE" resultType="Integer">
 4          <!-- 编写查询主键的SQL语句 -->
 5              select EMPLOYEES_SEQ.NEXTVAL from dual
 6          </selectKey>
 7          <!-- 插入时的主键是从序列中拿到的 -->
 8             insert into tbl_employee(ID,last_name,gender,email) values(
 9             #{id},#{lastName},#{gender},#{email}
10             )
11     </insert>

after方式：

先运行插入的SQL（从序列中取出新值作为ID）

再运行selectKey 查询ID的SQL；

     <insert id="addEmp" databaseId="oracle" parameterType="entity.Employee">
     
         <selectKey keyProperty="id" order="AFTER" resultType="Integer">
         <!-- 编写查询主键的SQL语句 -->
             select EMPLOYEES_SEQ.currval from dual
         </selectKey>
         <!-- 插入时的主键是从序列中拿到的 -->
            insert into tbl_employee(ID,last_name,gender,email) values(
            EMPLOYEES_SEQ.NEXTVAL,#{lastName},#{gender},#{email}
            )
    </insert>

4.修改：

接口EmployeeMapper.java:

void updateEmp(Employee employee);

映射文件EmployeeMapper.xml

parameterType 可以省略

<update id="updateEmp" parameterType="entity.Employee">
        update tbl_employee set last_name = #{lastName},gender = #{gender},email=#{email}
        where id = #{id}
    </update>

junit测试类：

 1 public void test01() {
 2         String resource = "mybatis-config.xml";
 3         SqlSession openSession = null;
 4         try {
 5             InputStream inputStream = Resources.getResourceAsStream(resource);
 6             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 7             // 获取openSession 不会自动提交数据
 8             openSession = sqlSessionFactory.openSession();
 9             EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
10             Employee employeeAdd2 = new Employee();
11             employeeAdd2.setLastName("joy33333");
12             employeeAdd2.setGender("女");
13             employeeAdd2.setEmail("joy52112225@iclound.com");
14             employeeAdd2.setId(1);
15             mapper.updateEmp(employeeAdd2);// 测试修改
16             // 2.手动提交
17             openSession.commit();
18         } catch (Exception e) {
19             // TODO: handle exception
20         } finally {
21             if (openSession != null) {
22                 openSession.close();
23             }
24 
25         }
26     }

5.删除：

接口EmployeeMapper.java:

void deleteEmpById(Integer id);

映射文件EmployeeMapper.xml

parameterType 可以省略

<delete id="deleteEmpById" parameterType="java.lang.Integer">
    delete from tbl_employee where id = #{id}
    </delete>

junit测试类：

 1 @Test
 2     public void test01() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession();
10             EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
11             mapper.deleteEmpById(24);// 测试删除
12             // 2.手动提交
13             openSession.commit();
14         } catch (Exception e) {
15             // TODO: handle exception
16         } finally {
17             if (openSession != null) {
18                 openSession.close();
19             }
20 
21         }
22     }

二、mybatis参数处理：

单个参数：

mybatis不会做特殊处理，参数名可以随便自定义
#{参数名}

接口EmployeeMapper.java:

Employee getEmpById(int id);

映射文件EmployeeMapper.xml

parameterType 可以省略

#{id}可以自定义名称，比如#{asdsdfsdf}

<select id="getEmpById" parameterType="Integer" resultType="entity.Employee">
        select LAST_NAME AS
        lastName,gender as gender,email as email from
        tbl_employee where id =#{id}
    </select>

junit测试类：

 1 @Test
 2     public void test03() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
11             // 查询单个参数
12             Employee employee = mapper.getEmpById(1);
13             System.out.println("查询1个参数测试1：" + employee);
14         } catch (Exception e) {
15             // TODO: handle exception
16         } finally {
17             if (openSession != null) {
18                 openSession.close();
19             }
20 
21         }
22     }

运行结果：

查询1个参数测试1：Employee [id=null, lastName=joy33333, email=joy52112225@iclound.com, gender=女]

多个参数：

1.直接传参：

多个参数会被封装成一个map。
key：param1.。。。paramN
value：传入的参数值
#{}就是从map中获取指定的key的值；即#{param1}....#{param10}

映射文件EmployeeMapper.xml：

parameterType 可以省略

<select id="getEmp" resultType="entity.Employee">
        select LAST_NAME AS
        lastName,gender as gender,email as email from
        tbl_employee where id =#{param1} and last_name = #{param2}
    </select>

接口EmployeeMapper.java:

Employee getEmp(int id, String lastName);

junit测试类：

 1 @Test
 2     public void test04() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
11             // 查询多个参数
12             Employee employee2 = mapper.getEmp(1, "joy33333");
13             System.out.println("查询多个参数测试1：" + employee2);
14         } catch (Exception e) {
15             // TODO: handle exception
16         } finally {
17             if (openSession != null) {
18                 openSession.close();
19             }
20 
21         }
22     }

运行结果：

查询多个参数测试1：Employee [id=null, lastName=joy33333, email=joy52112225@iclound.com, gender=女]

2.命名参数：明确指定封装参数是map的key@Param("id")

多个参数会被封装成一个map
key:使用@Param("id")注解指定的值
value:参数值

映射文件EmployeeMapper.xml：

parameterType 可以省略

<select id="getEmp2" resultType="entity.Employee">
        select LAST_NAME AS
        lastName,gender as gender,email as email from
        tbl_employee where id =#{id} and last_name = #{lastName}
    </select>

接口EmployeeMapper.java:

    Employee getEmp2(@Param("id") int id, @Param("lastName") String lastName);

junit测试类：

 1 @Test
 2     public void test04() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
11             // 查询多个参数
12             Employee employee3 = mapper.getEmp2(1, "joy33333");
13             System.out.println("查询多个参数测试2：" + employee3);
14 
15         } catch (Exception e) {
16             // TODO: handle exception
17         } finally {
18             if (openSession != null) {
19                 openSession.close();
20             }
21 
22         }
23     }

运行结果：

查询多个参数测试2：Employee [id=null, lastName=joy33333, email=joy52112225@iclound.com, gender=女]

3.pojo

如果多个参数正好是我们页面逻辑的数据模型，我们就可以直接传入pojo

#{属性名}：取出pojo的属性值

接口EmployeeMapper.java:

    Employee getEmp3(Employee emp);

映射文件EmployeeMapper.xml：

parameterType 可以省略

<select id="getEmp3" resultType="entity.Employee">
        select LAST_NAME AS
        lastName,gender as gender,email as email from
        tbl_employee where id =#{id} and last_name = #{lastName}
    </select>

junit 测试类：

 1 public void test04() {
 2         String resource = "mybatis-config.xml";
 3         SqlSession openSession = null;
 4         try {
 5             InputStream inputStream = Resources.getResourceAsStream(resource);
 6             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 7             // 获取openSession 不会自动提交数据
 8             openSession = sqlSessionFactory.openSession(true);
 9             EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
10             // 查询多个参数
11             Employee employee = new Employee();
12             employee.setId(1);
13             employee.setLastName("joy33333");
14             System.out.println(mapper.getEmp3(employee));
15 
16         } catch (Exception e) {
17             // TODO: handle exception
18         } finally {
19             if (openSession != null) {
20                 openSession.close();
21             }
22 
23         }
24     }

运行结果：

Employee [id=null, lastName=joy33333, email=joy52112225@iclound.com, gender=女]

4.map:

如果多个参数不是业务模型中的数据，没有对应的pojo，不经常使用为了方面，我们也可以传入map
#{key} 取出map中对应的key

接口EmployeeMapper.java:

    Employee getEmpByMap(Map<String, Object> map);

映射文件EmployeeMapper.xml：

parameterType 可以省略

<select id="getEmpByMap" parameterType="Map" resultType="entity.Employee">
        select LAST_NAME AS
        lastName,gender as gender,email as email from
        tbl_employee where id =#{id} and last_name = #{lastName}
    </select>

junit 测试类：

 1 @Test
 2     public void test04() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
11             // 查询多个参数
12             Map<String, Object> map = new HashMap<>();
13             map.put("id", 1);
14             map.put("lastName", "joy33333");
15             Employee employee4 = mapper.getEmpByMap(map);
16             System.out.println("查询多个参数测试3：" + employee4);
17         } catch (Exception e) {
18             // TODO: handle exception
19         } finally {
20             if (openSession != null) {
21                 openSession.close();
22             }
23 
24         }
25     }

5.TO

如果多个参数不是页面模型中的数据，但是经常要使用，推荐写一个TO(数据传输对象),推荐使用 AO DO TO命名。例如：EmployeeDO；传参方式就是以上的1~4；

6.其他使用场景

Employee getEmp2(@Param("id") int id, String lastName);取值：id==>#{id/param1} lastName==>#{param2}

Employee getEmp2(@Param("id") int id, @Param("e") Employee emp);取值：id==>#{id/param1} lastName==>#{param2.lastName/e.lastName}

7.特别注意

如果是Collection(List Set)类型或者是数组：也会特殊处理。也就是把传入的list或者数组封装在map中

key:Collection(collection) ,如果是List 还可以使用这个key(list)；数组（array）

Employee getEmpById(List<Ingeger> ids);

取值：取出第一个id值：#{list[0]}

三、#{}和${}区别和使用

1.相同点：

#{}:可以获取map中的值获取pojo对象属性的值

${}:可以获取map中的值获取pojo对象属性的值

2.区别：

#{}:以预编译的相识，将参数设置到SQL语句中：PrepaedStatement

${}:取出的值，直接拼装在SQL中，会有安全风险

大多数情况下，使用#{}

原生jdbc不支持占位符的地方就可以使用${}进行取值

比如分表、排序：按照年份拆分

select * from ${year}

select * from tbl_employee order by ${XXX} ${order}

举例：

接口EmployeeMapper.java:

    Employee getEmpByMap2(Map<String, Object> map);

映射文件EmployeeMapper.xml：

<select id="getEmpByMap2" parameterType="Map" resultType="entity.Employee">
        select LAST_NAME AS
        lastName,gender as gender,email as email from
        ${tableName} where id =#{id} and last_name = #{lastName}
    </select>

Junit测试类：

 1 @Test
 2     public void test05() {
 3         String resource = "mybatis-config.xml";
 4         SqlSession openSession = null;
 5         try {
 6             InputStream inputStream = Resources.getResourceAsStream(resource);
 7             SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
 8             // 获取openSession 不会自动提交数据
 9             openSession = sqlSessionFactory.openSession(true);
10             EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
11 
12             Map<String, Object> map = new HashMap<>();
13             map.put("id", 1);
14             map.put("lastName", "joy33333");
15             map.put("tableName", "tbl_employee");
16             Employee employee = mapper.getEmpByMap2(map);
17             System.out.println("测试 ${} #{}的区别：" + employee);
18         } catch (Exception e) {
19             // TODO: handle exception
20         } finally {
21             if (openSession != null) {
22                 openSession.close();
23             }
24 
25         }
26     }

运行结果：

测试 ${} #{}的区别：Employee [id=null, lastName=joy33333, email=joy52112225@iclound.com, gender=女]

3.#{}:更丰富的用法

a.规定参数的一些规则

javaType jdbcType mode(存储过程) numericScale resultMap typeHandler jdbcTypeName express(为了准备支持的功能) 中需要使用到#{};

b.jdbcType通常需要在某种特定的条件下被设置(经常使用)

在我们数据为null的时候，有些数据可能不能识别mybatis对null的默认处理。比如Oracle（报错 jdbctype other：无效的类型）；因为mybatis对所有的null都映射的是原始jdbc的OTHER,oracle 不能正确处理。

由于全局配置中，jdbcTypeForNull= OTHER oracle不支持，解决办法：

方法1.#{email,jdbcType=NULL} 指定 jdbcType

方法2.jdbcTypeForNull = NULL 即<setting name="jdbcTypeForNull" value="NULL"/>

posted @ 2022-03-02 11:25 啄木鸟伍迪阅读(191) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

啄木鸟伍迪

三(一)、mybatis映射文件-增删改和参数处理

一、映射文件中的增删改的实现：

1.说明：

2.测试项目目录结构和全局配置文件：

3.添加：

4.修改：

5.删除：

二、mybatis参数处理：

单个参数：

多个参数：

1.直接传参：

2.命名参数：明确指定封装参数是map的key@Param("id")

3.pojo

4.map:

5.TO

6.其他使用场景

7.特别注意

三、#{}和${}区别和使用

1.相同点：

2.区别：

3.#{}:更丰富的用法

公告