使用TypeHandler 把一个 List 集合存入数据库
目录1、创建数据库2、创建entity3、TypeHandler4、mapper.xml5、mapper6、controller1、创建数据库CREATE TABLE `user` (`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键,其它表中的使用user_id',`name` varchar(100) NOT NULL DEFAULT '',
·
目录
1、创建数据库
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键,其它表中的使用user_id',
`name` varchar(100) NOT NULL DEFAULT '',
`address` varchar(100) NOT NULL DEFAULT '',
`favorites` varchar(200) NOT NULL DEFAULT '',
`create_user_id` bigint(20) DEFAULT NULL COMMENT '创建人userId',
`modified_user_id` bigint(20) DEFAULT NULL COMMENT '修改人userId',
`gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
`is_deleted` int(11) NOT NULL DEFAULT '0' COMMENT '1表示删除,0表示未删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
INSERT INTO `user` (`id`, `name`, `address`, `favorites`, `create_user_id`, `modified_user_id`, `gmt_create`, `gmt_modified`, `is_deleted`) VALUES ('4', 'test', '地址', '喜欢1,喜欢2,喜欢3', NULL, NULL, '2020-11-03 12:40:26', '2021-03-22 15:43:22', '0');
2、创建entity
@Data
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
private String name;
private String address;
private List<String> favorites;
}
3、TypeHandler
@MappedJdbcTypes(JdbcType.VARCHAR) //数据库类型
@MappedTypes(List.class) //java中类型
public class List2VarcharHandler implements TypeHandler<List<String>> {
/**
* @param ps
* @param i
* @param strings
* @param jdbcType
* @throws SQLException
*/
@Override
public void setParameter(PreparedStatement ps, int i, List<String> strings, JdbcType jdbcType) throws SQLException {
StringBuffer sb = new StringBuffer();
for (String temp : strings) {
sb.append(temp).append(",");
}
ps.setString(i, sb.toString());
}
@Override
public List<String> getResult(ResultSet resultSet, String columnName) throws SQLException {
String s = resultSet.getString(columnName);
if (s != null) {
return Arrays.asList(s.split(","));
}
return null;
}
@Override
public List<String> getResult(ResultSet resultSet, int i) throws SQLException {
String s = resultSet.getString(i);
if (s != null) {
return Arrays.asList(s.split(","));
}
return null;
}
@Override
public List<String> getResult(CallableStatement callableStatement, int i) throws SQLException {
String s = callableStatement.getString(i);
if (s != null) {
return Arrays.asList(s.split(","));
}
return null;
}
}
4、mapper.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.yg.springboot.mapper.UserMapper">
<resultMap id="BaseMap" type="com.yg.springboot.domain.User">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="address" column="address"/>
<result property="favorites" column="favorites" typeHandler="com.yg.springboot.typehandler.List2VarcharHandler"/>
</resultMap>
<select id="getAll" resultMap="BaseMap">
select
id,
name,
address,
favorites
from user
</select>
<insert id="addUser" parameterType="com.yg.springboot.domain.User">
insert into
user(`name` ,address,favorites)
values(
#{name},
#{address},
#{favorites,typeHandler=com.yg.springboot.typehandler.List2VarcharHandler})
</insert>
</mapper>
5、mapper
public interface UserMapper {
List<User> getAll();
Integer addUser(User user);
}
6、controller
@RequestMapping(value = {"getUserAll"})
public Object getUserAll(HttpServletRequest req, HttpServletResponse resp) {
return userMapper.getAll();
}
@RequestMapping(value = {"addUser"})
public Object addUser(User user) {
List<String> list = new ArrayList<>();
list.add("111");
list.add("222");
list.add("333");
user.setAddress("地址");
user.setName("姓名");
user.setFavorites(list);
return userMapper.addUser(user);
}
更多推荐
已为社区贡献1条内容
所有评论(0)