目录
mybatis普通增删改
SQL映射文件
<insert id="addEmp" parameterType="com.atguigu.mybatis.bean.Employee">
insert into tbl_employee (last_name,email,gender) values (#{lastName},#{email},#{gender})
</insert>
<update id="updateEmp">
update tbl_employee set last_name = #{lastName},email = #{email},gender=#{gender}
</update>
<delete id="deleteEmpById">
delete from tbl_employee where id = #{id}
</delete>
编写接口类
package com.atguigu.mybatis.dao;
import com.atguigu.mybatis.bean.Employee;
public interface EmployeeMapper {
public Employee getEmpById(Integer id);
public void addEmp(Employee employee);
public void updateEmp(Employee employee);
public void deleteEmpById(Integer id);
}
编写测试类
public class MyBatisTest {
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void test03() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee(null, "Jack", "Jack@guigu.com", "1");
mapper.addEmp(employee);
//mapper.updateEmp(new Employee(1,"Jack","Jack@guigu.com","1"));
//mapper.deleteEmpById(2);
openSession.commit();
}finally{
openSession.close();
}
}
}
mybatis普通增删改 -增加返回值
我们想要在执行后,查看更新了几行,增加了几行,删除了几行。只需要修改接口类返回值即可。
package com.atguigu.mybatis.dao;
import com.atguigu.mybatis.bean.Employee;
public interface EmployeeMapper {
public Employee getEmpById(Integer id);
public Integer addEmp(Employee employee);
public Integer updateEmp(Employee employee);
public Integer deleteEmpById(Integer id);
}
测试类测试
@Test
public void test03() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee(3, "Tom", "Jack@guigu.com", "1");
Integer result = mapper.updateEmp(employee);
System.out.println(result);
//mapper.updateEmp(new Employee(1,"Jack","Jack@guigu.com","1"));
//mapper.deleteEmpById(2);
openSession.commit();
}finally{
openSession.close();
}
}
测试结果:
insert获得自增的主键值
mysql获取自增主键值
mysql可以设置自增。
修改SQL映射文件
<!-- parameterType:参数类型,可以省略,
获取自增主键的值:
mysql支持自增主键,自增主键值的获取,mybatis也是利用statement.getGenreatedKeys();
useGeneratedKeys="true";使用自增主键获取主键值策略
keyProperty;指定对应的主键属性,也就是mybatis获取到主键值以后,将这个值封装给javaBean的哪个属性
-->
<insert id="addEmp" parameterType="com.atguigu.mybatis.bean.Employee"
useGeneratedKeys="true" keyProperty="id" databaseId="mysql">
insert into tbl_employee(last_name,email,gender)
values(#{lastName},#{email},#{gender})
</insert>
修改测试类
@Test
public void test03() throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee(null, "Jack", "Jack@guigu.com", "1");
mapper.addEmp(employee);
System.out.println(employee.getId());
//mapper.updateEmp(new Employee(1,"Jack","Jack@guigu.com","1"));
//mapper.deleteEmpById(2);
openSession.commit();
}finally{
openSession.close();
}
}
Oracle获取自增主键
Oracle没有像Mysql一样的自增设置。
<!--
获取非自增主键的值:
Oracle不支持自增;Oracle使用序列来模拟自增;
每次插入的数据的主键是从序列中拿到的值;如何获取到这个值;
-->
<insert id="addEmp" databaseId="oracle">
<!--
keyProperty:查出的主键值封装给javaBean的哪个属性
order="BEFORE":当前sql在插入sql之前运行
AFTER:当前sql在插入sql之后运行
resultType:查出的数据的返回值类型
BEFORE运行顺序:
先运行selectKey查询id的sql;查出id值封装给javaBean的id属性
在运行插入的sql;就可以取出id属性对应的值
AFTER运行顺序:
先运行插入的sql(从序列中取出新值作为id);
再运行selectKey查询id的sql;
-->
<selectKey keyProperty="id" order="BEFORE" resultType="Integer">
<!-- 编写查询主键的sql语句 -->
<!-- BEFORE-->
select EMPLOYEES_SEQ.nextval from dual
<!-- AFTER:
select EMPLOYEES_SEQ.currval from dual -->
</selectKey>
<!-- 插入时的主键是从序列中拿到的 -->
<!-- BEFORE:-->
insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL)
values(#{id},#{lastName},#{email<!-- ,jdbcType=NULL -->})
<!-- AFTER:
insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL)
values(employees_seq.nextval,#{lastName},#{email}) -->
</insert>
注:最好是使用before,如果是批量插入的话,有可能只会获取最后的序列值。有可能获取的不对。
参数处理_的单个参数,多个参数,命名参数
单个参数
mybatis不会做特殊处理, #{参数名/任意名}:取出参数值。
多个参数
多个参数:mybatis会做特殊处理。
多个参数会被封装成 一个map,
key:param1...paramN,或者参数的索引也可以
value:传入的参数值
#{}就是从map中获取指定的key的值;
操作:
方法:public Employee getEmpByIdAndLastName(Integer id,String lastName);
取值:#{id},#{lastName}
操作出现异常:
org.apache.ibatis.binding.BindingException:
Parameter 'id' not found.
Available parameters are [1, 0, param1, param2]
映射文件取值如下:
<select id="getEmpById" resultType="com.atguigu.mybatis.bean.Employee">
select id,last_name lastName,email,gender from tbl_employee where id = 1 and last_name = 0
</select>
<select id="getEmpById" resultType="com.atguigu.mybatis.bean.Employee">
select id,last_name lastName,email,gender from tbl_employee where id = #{param1} and last_name = #{param2}
</select>
多个参数-命名参数
明确指定封装参数时map的key;@Param("id") 多个参数会被封装成 一个map, key:使用@Param注解指定的值 value:参数值 #{指定的key}取出对应的参数值。
实体类中修改:
public Employee getEmpByIdAndLastName(@Param("id")Integer id,@Param("lastName")String lastName);
SQL映射文件修改:
<select id="getEmpByIdAndLastName" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where id = #{id} and last_name=#{lastName}
</select>
其他
POJO:
如果多个参数正好是我们业务逻辑的数据模型,我们就可以直接传入pojo;
#{属性名}:取出传入的pojo的属性值
Map:
如果多个参数不是业务模型中的数据,没有对应的pojo,不经常使用,为了方便,我们也可以传入map
#{key}:取出map中对应的值
TO:
如果多个参数不是业务模型中的数据,但是经常要使用,推荐来编写一个TO(Transfer Object)数据传输对象
Page{
int index;
int size;
}
========================思考================================
public Employee getEmp(@Param("id")Integer id,String lastName);
取值:id==>#{id/param1} lastName==>#{param2}
public Employee getEmp(Integer id,@Param("e")Employee emp);
取值:id==>#{param1} lastName===>#{param2.lastName/e.lastName}
##特别注意:如果是Collection(List、Set)类型或者是数组,
也会特殊处理。也是把传入的list或者数组封装在map中。
key:Collection(collection),如果是List还可以使用这个key(list)
数组(array)
public Employee getEmpById(List<Integer> ids);
取值:取出第一个id的值: #{list[0]}
遇到的问题
Cause: java.lang.UnsupportedOperationException
我的代码:
public List<Employee> getEmpByIds(List<Integer> ids);
<select id="getEmpByIds" resultType="list">
select * from tbl_employee where id = #{list[0]}
</select>
按照这样的执行,就会出现错误。
修改为
public List<Employee> getEmpByIds(List<Integer> ids);
<select id="getEmpByIds" resultType="com.atguigu.mybatis.bean.Employee">
select * from tbl_employee where id = #{list[0]}
</select>
这样就没有错误了,需要的是每条记录的类型