mybatis之旅第四篇-输入输出映射
程序员文章站
2023-12-06 13:40:22
一、引言 在日常开发用到mybatis时,因为实际的开发业务场景很复杂,不论是输入的查询条件,还是返回的结果,经常是需要根据业务来定制,这个时候我们就需要自己来定义一些输入和输出映射 二、parameterType(输入映射) 输入映射是在映射文件中通过parameterType指定输入参数的类型, ......
一、引言
在日常开发用到mybatis时,因为实际的开发业务场景很复杂,不论是输入的查询条件,还是返回的结果,经常是需要根据业务来定制,这个时候我们就需要自己来定义一些输入和输出映射
二、
输入映射是在映射文件中通过parametertype指定输入参数的类型,类型可以是简单类型、hashmap、pojo的包装类型,当我们去查询用户时,有些字段基本不会用作查询条件,还有一些时候我们需要连表查询,那么这个时候我们可以用到包装类。
新建pojo包,定义包装类:
public class queryvo { //pojo private user user; public user getuser() { return user; } public void setuser(user user) { this.user = user; } }
将usermapper.xml文件移至com.yuanqinnan.mapper包中,并增加一个查询方法
<select id="querybyqo" parametertype="com.yuanqinnan.pojo.queryvo" resulttype="com.yuanqinnan.model.user"> select * from user where username like '%${user.username}%' </select>
usermapper中增加接口:
list<user> querybyqo(queryvo queryvo);
结构如图:
将sqlmapconfig.xml 中其他的配置恢复原先配置,引入mapper方式进行修改
<mappers> <package name="com.yuanqinnan.mapper"/> </mappers>
测试方法:
@test public void testqueryuserbyusername2() { // 获取sqlsession,和spring整合后由spring管理 sqlsession sqlsession = this.sqlsessionfactory.opensession(); // 从sqlsession中获取mapper接口的代理对象 usermapper usermapper = sqlsession.getmapper(usermapper.class); // 执行查询方法 queryvo queryvo=new queryvo(); user user=new user(); user.setusername("张"); queryvo.setuser(user); list<user> list = usermapper.querybyqo(queryvo); for (user user2 : list) { system.out.println(user2); } // 和spring整合后由spring管理 sqlsession.close(); }
<build> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <filtering>false</filtering> </resource> </resources> </build>
得到测试结果:
输入映射比较简单,一般不会使用包装类,而是根据需要的条件去设置字段比较好
三、
输出类型有简单类型,pojo类,pojo列表,pojol类和列表在前面的例子中都有演示,下面看一个简单类型的
新增方法:
<select id="queryusercount" resulttype="int"> select count(*) from user </select>
接口:
int queryusercount();
测试:
@test public void testqueryusercount() { // 获取sqlsession,和spring整合后由spring管理 sqlsession sqlsession = this.sqlsessionfactory.opensession(); // 从sqlsession中获取mapper接口的代理对象 usermapper usermapper = sqlsession.getmapper(usermapper.class); // 执行查询方法 int count= usermapper.queryusercount() ; system.out.println(count); // 和spring整合后由spring管理 sqlsession.close(); }
结果:10
四、
resulttype可以指定将查询结果映射为pojo,但需要pojo的属性名和sql查询的列名一致方可映射成功。
如果sql查询字段名和pojo的属性名不一致,可以通过resultmap将字段名和属性名作一个对应关系 ,resultmap实质上还需要将查询结果映射到pojo对象中。
先新增一张订单表,sql如下:
drop table if exists `order`; create table `orders` ( `id` int(11) not null auto_increment, `user_id` int(11) not null comment '下单用户id', `number` varchar(32) not null comment '订单号', `createtime` datetime not null comment '创建订单时间', `note` varchar(100) default null comment '备注', primary key (`id`), key `fk_orders_1` (`user_id`), constraint `fk_order_id` foreign key (`user_id`) references `user` (`id`) on delete no action on update no action ) engine=innodb auto_increment=6 default charset=utf8; -- ---------------------------- -- records of order -- ---------------------------- insert into `order` values ('3', '1', '1000010', '2015-02-04 13:22:35', null); insert into `order` values ('4', '1', '1000011', '2015-02-03 13:22:41', null); insert into `order` values ('5', '10', '1000012', '2015-02-12 16:13:23', null);
实体:
public class order { // 订单id private int id; // 用户id private integer userid; // 订单号 private string number; // 订单创建时间 private date createtime; // 备注 private string note; public int getid() { return id; } public integer getuserid() { return userid; } public string getnumber() { return number; } public date getcreatetime() { return createtime; } public string getnote() { return note; } public void setid(int id) { this.id = id; } public void setuserid(integer userid) { this.userid = userid; } public void setnumber(string number) { this.number = number; } public void setcreatetime(date createtime) { this.createtime = createtime; } public void setnote(string note) { this.note = note; } @override public string tostring() { return "order{" + "id=" + id + ", userid=" + userid + ", number='" + number + '\'' + ", createtime=" + createtime + ", note='" + note + '\'' + '}'; } }
新增ordermapper.xml:
<?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="com.yuanqinnan.mapper.ordermapper"> <!-- 查询所有的订单数据 --> <select id="queryorderall" resulttype="com.yuanqinnan.model.order"> select id, user_id, number, createtime, note from `order` </select> </mapper>
新增ordermapper接口
public interface ordermapper { list<order> queryorderall(); }
测试:
@test public void testqueryall() { // 获取sqlsession sqlsession sqlsession = this.sqlsessionfactory.opensession(); // 获取ordermapper ordermapper ordermapper = sqlsession.getmapper(ordermapper.class); // 执行查询 list<order> list = ordermapper.queryorderall(); for (order order : list) { system.out.println(order); } }
结构如图:
结果:
发现userid为null,用resultmap解决,
<?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="com.yuanqinnan.mapper.ordermapper"> <!-- resultmap最终还是要将结果映射到pojo上,type就是指定映射到哪一个pojo --> <!-- id:设置resultmap的id --> <resultmap type="com.yuanqinnan.model.order" id="orderresultmap"> <!-- 定义主键 ,非常重要。如果是多个字段,则定义多个id --> <!-- property:主键在pojo中的属性名 --> <!-- column:主键在数据库中的列名 --> <id property="id" column="id" /> <!-- 定义普通属性 --> <result property="userid" column="user_id" /> <result property="number" column="number" /> <result property="createtime" column="createtime" /> <result property="note" column="note" /> </resultmap> <!-- 查询所有的订单数据 --> <select id="queryorderall" resulttype="com.yuanqinnan.model.order"> select id, user_id, number, createtime, note from `order` </select> <select id="queryorderall2" resultmap="orderresultmap"> select id, user_id, number, createtime, note from `order` </select> </mapper>
增加接口:
list<order> queryorderall2();
测试方法:
@test public void testqueryall2() { // 获取sqlsession sqlsession sqlsession = this.sqlsessionfactory.opensession(); // 获取ordermapper ordermapper ordermapper = sqlsession.getmapper(ordermapper.class); // 执行查询 list<order> list = ordermapper.queryorderall2(); for (order order : list) { system.out.println(order); } }
结果: