mysql list变字符串_mybatis自定义TypeHandler实现list转string

1. 创建项目

完整的pom.xml文件如下

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

org.springframework.boot

spring-boot-starter-parent

2.2.6.RELEASE

com.example

mybatis

0.0.1-SNAPSHOT

mybatis

Demo project for Spring Boot

1.8

org.springframework.boot

spring-boot-starter-web

org.mybatis.spring.boot

mybatis-spring-boot-starter

2.1.2

org.springframework.boot

spring-boot-starter-jdbc

mysql

mysql-connector-java

runtime

org.projectlombok

lombok

true

org.springframework.boot

spring-boot-starter-test

test

org.junit.vintage

junit-vintage-engine

org.springframework.boot

spring-boot-maven-plugin

2. 配置application.yml

server:

port: 8083

spring:

datasource:

url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai

username: root

password: 123456

driver-class-name: com.mysql.cj.jdbc.Driver

3. 编写User pojo

@Data

public class User {

private Integer id;

private String name;

private Integer age;

private List hobbies;

}

4. 编写控制器,service,mapper

@RestController

public class UserController {

@Autowired

UserService userService;

@GetMapping("/users")

private List getAllUser(){

return userService.getAllUser();

}

@PostMapping("/users")

private Integer addUser(@RequestBody User user){

return userService.addUser(user);

}

}

@Service

public class UserService {

@Autowired

UserMapper userMapper;

public List getAllUser() {

return userMapper.getAllUser();

}

public Integer addUser(User user) {

return userMapper.addUser(user);

}

}

@Component

public interface UserMapper {

@Results({

@Result(column = "id", property = "id"),

@Result(column = "name", property = "name"),

@Result(column = "age", property = "age"),

@Result(column = "hobby", property = "hobbies" ,typeHandler= HobbyTypehandler.class),

})

@Select("select * from user")

List getAllUser();

@Insert("insert into user " +

"(name, age, hobby) " +

"values (#{user.name}, #{user.age},"

+ "#{user.hobbies, typeHandler=com.example.mybatis.typeHandler.HobbyTypehandler})")

@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")

Integer addUser(@Param("user") User user);

}

5. 编写自定义Typehandler

@Component

public class HobbyTypehandler implements TypeHandler> {

@Override

public void setParameter(PreparedStatement preparedStatement, int i, List strings, JdbcType jdbcType) throws SQLException {

StringBuffer sb = new StringBuffer();

for (String s : strings) {

sb.append(s).append(",");

}

preparedStatement.setString(i, sb.toString().substring(0, sb.toString().length() - 1));

}

@Override

public List getResult(ResultSet resultSet, String s) throws SQLException {

String[] arr = resultSet.getString(s).split(",");

return Arrays.asList(arr);

}

@Override

public List getResult(ResultSet resultSet, int i) throws SQLException {

String[] arr = resultSet.getString(i).split(",");

return Arrays.asList(arr);

}

@Override

public List getResult(CallableStatement callableStatement, int i) throws SQLException {

String[] arr = callableStatement.getString(i).split(",");

return Arrays.asList(arr);

}

}

6. 编写启动类

@SpringBootApplication

@MapperScan("com.example.mybatis.mapper")

public class MybatisApplication {

public static void main(String[] args) {

SpringApplication.run(MybatisApplication.class, args);

}

}

7. mysql 表结构

CREATE TABLE `user` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

`age` int(6) DEFAULT NULL,

`hobby` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

8. 测试

9. 代码