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

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

1.说明:

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

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

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

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

配置说明可以参考mybatis全局配置文件说明

三(一)、mybatis映射文件-增删改和参数处理
 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:

三(一)、mybatis映射文件-增删改和参数处理
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的那个属性
三(一)、mybatis映射文件-增删改和参数处理
 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}

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

上一篇:蓝桥杯python:倍数问题


下一篇:三(二)、映射文件之select返回和resultMap