本文介绍了在Java项目中遇到的三个问题：数据库查询中的逗号错误处理、实体类属性名称与JSON解析的不匹配以及前端后端通信时的服务器超时。通过set标签避免了逗号错误，解释了实体类属性命名对JSON解析的影响，以及排查前端500错误时关注数据库连接和后端登录接口。
目录
复杂查询（一对一和一对多可嵌套使用）
建立好所需数据库
create database if not exists my_Sql;
use my_Sql;
create table if not exists mytable
(
userID int primary key auto_increment not null,
clazz varchar(20),
email varchar(20),
username varchar(20) not null,
password varchar(20) not null,
pID int not null
);
insert into mytable value
(1, '2201', '34518868659', 'sean', '123456', 1),
(2, '2202', '34518868659', '李四', '123456', 2),
(3, '2201', '34518868659', '王五', '123456', 3);
create table if not exists permissions
(
pID int primary key auto_increment,
type varchar(50) default 'guest' not null
);
insert into permissions
value (1, 'admin'),
(2, 'guest'),
(3, 'vip');
select m.userID, m.clazz, m.username, m.password, m.pID, p.pID, p.type
from mytable m
left join permissions p on m.pID = p.pID;
create table if not exists mybook
(
bookID int primary key not null,
name varchar(20) comment '书名',
img varchar(30) comment '图书照片',
press varchar(30) not null comment '出版社',
username varchar(50) not null comment '作者',
number int not null comment '库存',
tid int not null comment '借书人'
);
insert into mybook
values (1, '西游记', '', '北京出版社', '张三', 8, 1),
(2, '水浒传', '', '北京出版社', '张三', 3, 3),
(3, '三国志', '', '北京出版社', '张三', 4, 2),
(4, 'Java', '', '北京出版社', '张三', 2, 1),
(5, 'python', '', '北京出版社', '张三', 5, 3);
select t.userID,
t.username,
t.clazz,
t.email,
t.password,
m.bookID,
m.name,
m.img,
m.press,
m.username,
m.press
from mytable t
left join mybook m on t.userID = m.tid;
将每个数据库分别建实体类
接口中，还是返回最外层。
v层定义好路径、属性、返回值
接口中要将所需数据全查出来，进行映射，在resultMap中，先写association一对一，后写collection一对多。
<select id="getUserAndBookAndType" resultMap="getMap">
select t.userID,
t.username,
t.clazz,
t.email,
t.password,
t.pID,
m.bookID,
m.name,
m.img,
m.press,
m.username,
m.press,
m.number,
m.tid,
p.pID,
p.type
from mytable t
left join mybook m on t.userID = m.tid
left join permissions p on t.pID = p.pID;
</select>
<resultMap id="getMap" type="UserEntity">
<id column="userID" property="userID"></id>
<result column="username" property="username"></result>
<result column="clazz" property="clazz"></result>
<result column="email" property="email"></result>
<result column="password" property="password"></result>
<result column="pID" property="pID"></result>
<association property="getPermissionsList" javaType="Permissions">
<id column="pID" property="pID"></id>
<result column="type" property="type"></result>
</association>
<collection property="getBookList" ofType="BookEntity">
<id column="bookID" property="bookID"></id>
<result column="name" property="name"></result>
<result column="img" property="img"></result>
<result column="press" property="press"></result>
<result column="number" property="number"></result>
<result column="username" property="username"></result>
<result column="tid" property="tid"></result>
</collection>
</resultMap>测试结果
既可以显示所借图书，也可以显示所有权限
{
"code": 200,
"msg": "查询成功",
"result": [
{
"userID": 1,
"username": "sean",
"clazz": "2201",
"email": "34518868659",
"password": "123456",
"getBookList": [
{
"bookID": 1,
"name": "西游记",
"img": "",
"press": "北京出版社",
"username": "sean",
"number": 8,
"tid": 1
},
{
"bookID": 4,
"name": "Java",
"img": "",
"press": "北京出版社",
"username": "sean",
"number": 2,
"tid": 1
}
],
"getPermissionsList": {
"type": "admin",
"pid": 1
},
"pid": 1
},
{
"userID": 3,
"username": "王五",
"clazz": "2201",
"email": "34518868659",
"password": "123456",
"getBookList": [
{
"bookID": 2,
"name": "水浒传",
"img": "",
"press": "北京出版社",
"username": "王五",
"number": 3,
"tid": 3
},
{
"bookID": 5,
"name": "python",
"img": "",
"press": "北京出版社",
"username": "王五",
"number": 5,
"tid": 3
}
],
"getPermissionsList": {
"type": "vip",
"pid": 3
},
"pid": 3
},
{
"userID": 2,
"username": "李四",
"clazz": "2202",
"email": "34518868659",
"password": "123456",
"getBookList": [
{
"bookID": 3,
"name": "三国志",
"img": "",
"press": "北京出版社",
"username": "李四",
"number": 4,
"tid": 2
}
],
"getPermissionsList": {
"type": "guest",
"pid": 2
},
"pid": 2
}
]
}项目中小问题总结
1.数据库语句逗号报错
在不知道有传入几个值时，我们常常用where标签和if标签，但是会出现在只传入第一个值或最后一个值时，逗号报错，检测出的SQL语句错误出在逗号上，这是需要用到set标签。
示例：
<update id="updateUserByID" parameterType="UserEntity">
update mytable
<set>
<if test="clazz!=null and clazz!=''">
clazz=#{clazz},
</if>
<if test="email!=null and email!=''">
email=#{email},
</if>
<if test="username!=null and username!=''">
username=#{username},
</if>
<if test="password!=null and password!=''">
password=#{password},
</if>
<if test="pID!=null and pID!=''">
pID=#{pID}
</if>
</set>
where userID=#{userID};
</update>set标签，会自动删除逗号，不会出现逗号报错情况。
2.明明写的是pID,可传值就变成了pid
错误：
我的实体类中写的为
@Data
public class UserEntity {
private Integer userID;
private String username;
private String clazz;
private String email;
private String password;
private Integer pID;但实际查询出来的为
{
"code": 200,
"msg": "查询成功",
"result": [
{
"userID": 1,
"username": "sean",
"clazz": "2201",
"email": "34518868659",
"password": "123456",
"getBookList": null,
"getPermissionsList": null,
"pid": 1
},
]
}我写的明明为pID,但最后查询到的确是pid。
原因：
SpringMVC默认使用jackson解析json，jackson又依赖于实体类的get/set方法，通过lombok生成的方法是：
而手写的是：
两个方法名不一样，所以出现了json解析出i变小写的问题
3.前端报500，后端服务器超时
这个错误，前端找到后端，后端错误在登录接口，往进找，则没发现错误。
这时，检查数据库有没有连接，看看数据库有没有启动。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
