文章目录
三、Mybatis映射文件
1.使用mybatis完成crud操作
import beans.Employee;
public interface EmployeeDao {
//定义crud的相关方法
//根据id查询Employee
public Employee getEmployeeByPrice(Integer price);
//添加一个新的Employee
public void insertEmployee(Employee employee);
//修改一个Employee
public void updateEmployee(Employee employee);
//删除一个Employee
public void deleteEmployee(Integer price);
}
<1>insert
<!--public void insertEmployee(Integer price);
parameterType:指定参数类型,可以省略不配置,因为mybatis内部有类型推荐
-->
<insert id="insertEmployee" parameterType="beans.Employee">
insert into book1 (isbn,book_Name,price) values (#{isbn},#{book_Name},#{price})
</insert>
@Test
public void testinsertEmployee() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, "development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
//获取mapper接口的代理实现对象
EmployeeDao dao = sqlSession.getMapper(EmployeeDao.class);
Employee employee = new Employee("ISBN-001","book01",1);
dao.insertEmployee(employee);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
<2>update
<!--
public void updateEmployee(Integer price);
-->
<update id="updateEmployee">
update book1 set
isbn = #{isbn},
book_Name = #{book_Name},
where price = #{price}
</update>
@Test
public void testUpdateEmployee() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, "development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeDao dao = sqlSession.getMapper(EmployeeDao.class);
Employee employee = new Employee("ISBN-003", "book01",3);
dao.updateEmployee(employee);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
<3>delete
<!--
public void deleteEmployee(Integer price);
-->
<delete id="deleteEmployee" >
delete from book1 where price = #{price}
</delete>
@Test
public void testDeleteEmployee() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, "development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeDao dao = sqlSession.getMapper(EmployeeDao.class);
dao.deleteEmployee(1);
//提交操作
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
2.修改接口返回值类型
如果我们想要确切的得到增删改,进行的操作次数或者是否进行了操作,可以添加一个Integer或者boolean的返回值,会在test测试执行之后自动返回一个值。
3.主键
因为作者使用的是不支持主键自增的oracle,所以会等到后面更新了hive之后更新这部分内容
4.参数传递
<1>参数传递的方式
1) 单个普通(基本/包装+String)参数
这种情况MyBatis可直接使用这个参数,不需要经过任 何处理。
取值:#{随便写}
<insert id="insertEmployee" >
insert into book1 (isbn,book_Name,price) values (#{isbn},#{book_Name},#{price})
</insert>
参数的部位可以随意进行去写,因为mybatis不会处理那部分,所以不会影响到运行
2) 多个参数
任意多个参数,都会被MyBatis重新包装成一个Map传入。Map的key是param1,param2,或者0,1…,值就是参数的值
取值: #{0 1 2 …N / param1 param2 …… paramN}
当我们编写一个通过两个信息查找方法的时候
<!--public Employee getIsbnAndBookName(String isbn, String book_Name);-->
<select id="getIsbnAndBookName" resultType="beans.Employee">
select isbn,book_Name,price from book1 where isbn=#{isbn} and book_Name=#{bookName}
</select>
这时我们按照常规的方法去写java语句的时候就会报错
Cause: org.apache.ibatis.binding.BindingException: Parameter 'isbn' not found. Available parameters are [0, 1, param1, param2]
我们在写mybatis的时候,它默认会把多个参数以map的形式存储,而map的key值就是0 1 2 或者param1 param2这样的类型,所以我们就把查找的顺序改成相似的字串就好了.
@Test
public void testParameters() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, "development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeDao dao = sqlSession.getMapper(EmployeeDao.class);
dao.getIsbnAndBookName("ISBN-003", "book01");
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
3) 命名参数
为参数使用@Param起一个名字,MyBatis就会将这些参数封装进map中,key就是我们自己指定的名字
取值: #{自己指定的名字 / param1 param2 … paramN}
public Employee getIsbnAndBookName(@Param("isbn") String isbn, @Param("book_Name") String book_Name);
然后另外的把刚才设置的0 1改成现在Param内的参数
4) POJO
当这些参数属于我们业务POJO时,我们直接传递POJO
取值: #{POJO的属性名}
5) Map
我们也可以封装多个参数为map,直接传递
取值: #{使用封装Map时自己指定的key}
@Test
public void testMap() throws Exception{
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream,"development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeDao dao = sqlSession.getMapper(EmployeeDao.class);
Map<String, Object> map = new HashMap<>();
map.put("666", "ISBN-005");
map.put("999", "book05");
dao.getEmployeeByMap(map);
sqlSession.commit();
}catch(Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
仔细看Map的key就知道了
<!--public Employee getEmployeeByMap(Map<String, Object> map);-->
<select id="getEmployeeByMap" resultType="beans.Employee">
select isbn,book_Name,price from book1 where isbn=#{666} and book_Name=#{999}
</select>
6) Collection/Array
会被MyBatis封装成一个map传入, Collection对应的key是collection,Array对应的key是array. 如果确定是List集合,key还可以是list.
取值:
Array: #{array}
Collection(List/Set): #{collection}
List : #{collection / list}
<2>参数处理
当java的数据传入到数据库中,和数据库中传出数据回java 的时候都会产生数据的处理的问题,比如java的字符串叫string,oracle的字符串叫varchar。
在mybatis中null值的类型会被默认为other类型。而mysql懂得识别这个other类型,但是oracle不认识,所以我们要传入一个空数据给oracle的时候,要让oracle直接去识别null,而不是去识别他无法识别的other类型。
所以这时候就有了关键词jdbctype,当jdbcType和要赋值的参数绑定在一起,并且被赋值为null的时候就可以给他传入other了。
insert into orcl_employee(id,last_name,email,gender)
values(employee_seq.nextval,#{lastName,jdbcType=NULL },#{email},#{gender})
<3>参数的获取方式
1) #{key}:
可取单个普通类型、 POJO类型 、多个参数、 集合类型
获取参数的值,预编译到SQL中。安全。 PreparedStatement
2) ${key}:
可取单个普通类型、POJO类型、多个参数、集合类型.
注意: 取单个普通类型的参数,KaTeX parse error: Expected 'EOF', got '#' at position 95: …tatement 原则: 能用#̲{}取值就优先使用#{},#{…{}.
例如: 原生的JDBC不支持占位符的地方,就可以使用${}
Select column1 ,column2… from 表 where 条件group by 组标识 having 条件 order by 排序字段 desc/asc limit x, x
<4>select查询的几种情况
(1)查询单行数据,返回单个对象
<select id="getEmployeeByPrice" resultType="beans.Employee">
select isbn,book_Name bookName,price from book1 where price = #{price}
</select>
(2)查询多行数据,返回单个对象
因为我们虽然查询了多行数据,要把它聚集到一起展示,我们大家都知道一行数据就是一个Employee对象,但是聚集起来就是一个问题了,但它不是Collection对象,因为这就是存储,但是我们不是为了存储,所以他还是一歌Employee对象,所在在sql中resultType仍然要设置成Employee类型
<!--public List<Employee> getEmps();
resultType填的是想要封装成的类型
-->
<select id="getEmps" resultType="beans.Employee">
select isbn,book_Name,price from book1
</select>
@Test
public void testCollection() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, "development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeDao dao = sqlSession.getMapper(EmployeeDao.class);
List<Employee> list = dao.getEmps();
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
(3) 查询单行数据返回一个Map
@Test
public void getEmployeeByPriceReturnMap() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, "development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeDao dao = sqlSession.getMapper(EmployeeDao.class);
Map<String,Object> map = dao.getEmployeeByPriceReturnMap(5);
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}
//查询单行数据,返回一个Map
public Map<String, Object> getEmployeeByPriceReturnMap(Integer price);
(4)查询多条数据返回一个Map
@MapKey("isbn")//指定使用对象的哪个属性作为Map的Key
public Map<String, Employee> getEmpsReturnMap(String isbn);
@Test
public void getEmpsReturnMap() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream, "development");
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeDao dao = sqlSession.getMapper(EmployeeDao.class);
Map<String,Employee> map = dao.getEmpsReturnMap("ISBN-001");
sqlSession.commit();
}catch (Exception e){
e.printStackTrace();
}finally {
sqlSession.close();
}
}