本文验证了通过mybatis访问数据库时的,增删改查的返回值情况。
直接看代码。
1、service层
/**
*@Author: Administrator on 2020/3/12 15:15
*@param:
*@return:
*@Description:查询同步情况
*/
@Override
public PageInfo getSyncstatusPages(Syncstatus vo, int pageNo, int pageSize) {
PageHelper.startPage(pageNo, pageSize);
/* //查看增删改查的返回值
//1新增:返回值自己定义,可以是void,int
//1-1新增一条数据:插入成功,返回值为1
int insert_success1 = yylfHttpServletMapper.insert("8", "2", "1");
//1-2新增多条数据:插入成功,返回值为插入的数据条数,当有一条数据错误时,所有数据都会插入失败
int insert_success2 = yylfHttpServletMapper.insert_duotiao("7");
String insert_success3 = yylfHttpServletMapper.insert_duotiao_String("7");//不支持返回值为String类型
//1-3新增一条数据:插入失败:主键冲突,会直接报异常
int insert_failed = yylfHttpServletMapper.insert("1", "2", "1");
//1-4插入null:属性为null,如果表中所有字段允许为null,插入一条所有值均为null的数据
Syncstatus syncstatus1 = null;
yylfHttpServletMapper.insertSyncstatus(syncstatus1);
//1-5插入一个没有赋值的对象:属性为null,如果表中所有字段允许为null,插入一条所有值均为null的数据
Syncstatus syncstatus2 = new Syncstatus();
yylfHttpServletMapper.insertSyncstatus(syncstatus2);*/
/*//2删除:返回值自己定义,可以是void,int
//2-1删除成功:没有数据:返回值为0
int delete_success1 = yylfHttpServletMapper.delete("0");
//2-2删除成功:有多条数据:返回值为删除的数据条数
int delete_success2 = yylfHttpServletMapper.delete_systemcode("2");
//2-3删除失败:例如有外键:报异常
int delete_fail = yylfHttpServletMapper.delete("1");*/
//3更新:返回值自己定义,可以是void,int
//3-1更新成功:没有数据,返回值为0
int update_no = yylfHttpServletMapper.update_no("0");
//3-2更新成功:有多条数据,返回更新的数据条数
int update_duotiao = yylfHttpServletMapper.update_duotiao_systemcode("2");
//3-3更新失败:例如有外键,报异常
//int update_fail = yylfHttpServletMapper.update_fail("1");
//4查询
//4-1 没数:String 类型返回null
Object object = yylfHttpServletMapper.select("0");
//4-1 没数:集合 类型返回[]空集合
Syncstatus syncstatus3 = new Syncstatus();
syncstatus3.setStatus("7");
List<Syncstatus> page0 = yylfHttpServletMapper.getSyncstatusList(syncstatus3);
//4-1 没数:int 类型返回null,如果定义为int会报错。因为没数时返回null,可以将返回类型改为String
String i = yylfHttpServletMapper.select_int(0);
//4-1:当返回值为对象时,若返回值为空,则返回null
//4-2 有数
List<Syncstatus> pages = yylfHttpServletMapper.getSyncstatusList(vo);
return new PageInfo<Syncstatus>(pages);
}
2、mapper
/**
* @author zs
* @date 2019/10/18 16:03
*/
@Repository
public interface YylfHttpServletMapper {
//删除预约临分未到期数据
void deleteZrjunearnedfac(Date startdate);
//保存预约临分未到期数据
void addZrjunearnedfac(List<Zrjunearnedfac> zrjunearnedfacList);
//删除据提取信息
void deleteSyncstatus(String uuid);
//保存数据提取信息
void insertSyncstatus(Syncstatus syncstatus);
//查询数据同步信息
List<Syncstatus> getSyncstatusList(@Param("vo")Syncstatus vo);
//获取日志信息
List<InterfaceLog> getYylfNewLog(@Param("uuid")String uuid, @Param("sendsystemcode")String sendsystemcode);
//新增
int insert(@Param("uuid")String uuid, @Param("systemcode")String systemcode, @Param("status")String status);
int insert_duotiao(@Param("uuid")String uuid);
String insert_duotiao_String(@Param("uuid")String uuid);
//删除
int delete(@Param("uuid")String uuid);
int delete_systemcode(@Param("systemcode")String systemcode);
//修改
int update_no(@Param("uuid")String uuid);
int update_duotiao_systemcode(@Param("systemcode")String systemcode);
int update_fail(@Param("uuid")String uuid);
//查询
Object select(@Param("uuid")String uuid);
String select_int(@Param("uuid")int uuid);
}
3、mapper.xml
<insert id="insert" parameterType="java.util.Map">
insert into aaa
(uuid,systemcode,status)
value
(#{uuid,jdbcType=VARCHAR},#{systemcode,jdbcType=VARCHAR},#{status,jdbcType=VARCHAR})
</insert>
<insert id="insert_duotiao" parameterType="java.util.Map">
insert into aaa
(uuid,systemcode,status)
value
('1','2','2'),('6','2','2')
</insert>
<insert id="insert_duotiao_String" parameterType="java.util.Map">
insert into aaa
(uuid,systemcode,status)
value
('5','2','2'),('6','2','2')
</insert>
<delete id="delete">
delete from aaa where uuid = #{uuid,jdbcType=VARCHAR}
</delete>
<delete id="delete_systemcode">
delete from aaa where systemcode = #{systemcode,jdbcType=VARCHAR}
</delete>
<update id="update_no">
UPDATE aaa SET status = '0' WHERE uuid = #{uuid,jdbcType=VARCHAR}
</update>
<update id="update_duotiao_systemcode">
UPDATE aaa SET systemcode = '3' WHERE systemcode = #{systemcode,jdbcType=VARCHAR}
</update>
<update id="update_fail">
UPDATE aaa SET uuid = '0' WHERE uuid = #{uuid,jdbcType=VARCHAR}
</update>
<select id="select">
select uuid from aaa where uuid = '0'
</select>
<select id="select_int" resultType="String">
select uuid from aaa where uuid = 0
</select>
<select id="getSyncstatusList" resultMap="syncstatusToRow">
select uuid,startdate as paydate ,date(createtimefordw) as syndate ,status,
sum(case when tablename='zrjunearnedfac' then zrjcount end) as zrjunearnedfacNum,
sum(case when tablename='zrjoutstandingfac' then zrjcount end) as zrjoutstandingfacNum
from syncstatus where 1=1
AND systemcode = 'YYLF'
<if test=" vo.startdate != null and vo.enddate !=null " >
AND startdate between #{vo.startdate} and #{vo.enddate}
</if>
<if test="vo.status != '' and vo.status != null " >
AND status = #{vo.status}
</if>
group by uuid,startdate,createtimefordw,status
order by startdate,date(createtimefordw) desc
</select>
4、sql语句
-- 创建aaa表用来验证增删改查的返回值
CREATE TABLE `reserve`.`aaa` (
`uuid` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`systemcode` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`status` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`uuid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- 新增
insert into aaa (uuid,systemcode,status)value ('1','2','2');
insert into aaa (uuid,systemcode,status)value ('2','2','2');
insert into aaa (uuid,systemcode,status)value ('3','2','2');
insert into aaa (uuid,systemcode,status)value ('4','2','2');
insert into aaa (uuid,systemcode,status)value ('5','2','2'),('6','2','2');
-- 删除
delete from aaa where uuid = '0';
delete from aaa where uuid != '1';
delete from aaa where uuid = '2';
-- 修改
UPDATE aaa SET systemcode = '3' WHERE uuid = '1';
UPDATE aaa SET uuid = '0' WHERE uuid = '2';
-- 查询
select * from aaa;
-- 创建bbb表用来关联aaa的uuid作外键
CREATE TABLE `reserve`.`bbb` (
`uuid` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`systemcode` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`status` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`uuid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
alter table bbb add constraint FK_T_POSITI_REFERENCE_T_COMPAN foreign key (uuid)references
aaa (uuid);
insert into bbb (uuid,systemcode,status)value ('1','2','2');
-- 创建ccc表用来验证插入的为null的数据
CREATE TABLE `reserve`.`ccc` (
`uuid` char(36) CHARACTER SET utf8 COLLATE utf8_general_ci ,
`systemcode` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci ,
`status` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
insert into ccc (uuid,systemcode,status)value (NULL,NULL,NULL);
select *from ccc;
-- 创建ddd表用来验证查询结果为空时的返回值
CREATE TABLE `reserve`.`ddd` (
`uuid` INT ,
`systemcode` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci ,
`status` varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
insert into ddd (uuid,systemcode,status)value (1,'2','2');
select *from ddd;