在Mybatis中使用PostgreSQL Jsonb类型示例

引入mybatis

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-core</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.19</version>
</dependency>

application.yml 中为MyBatis指定TypeHandler包地址

1
2
3
mybatis-plus:
mapper-locations: classpath:mapper/*/*.xml
type-handlers-package: com.seaboxdata.mds.common.mybatis.typehandler

application.xml 中配置postgreSQL地址

1
2
3
4
5
spring:
datasource:
url: jdbc:postgresql://localhost:5432/mydatabase
username: postgres
password: password

Person 表中使用 pocket 字段存储不同的结构: CoinFruits 使用 type 字段区分

id name pocket type
11 水果人1 {“color”: “RED”} fruits
21 金币人1 {“coinAmount”: 128} coin
12 水果人2 {“color”: “RED”} fruits
22 金币人2 {“coinAmount”: 128} coin
10 张三 {“type”: “a”, “tableName”: “table1”}

pocket类型 Coin.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

@Data
@Builder
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class Coin {
private Integer coinAmount;
}

Mybatis 对于Coin类型的TypeHandler CoinTypeHandler.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.jetbrains.annotations.Nullable;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CoinTypeHandler extends BaseTypeHandler<Coin> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Coin coin, JdbcType jdbcType) throws SQLException {
String jsonb = JacksonHelper.writeToJson(coin);
preparedStatement.setString(i, jsonb);
}

@Override
public Coin getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
String json = resultSet.getString(columnName);
return read(json);
}

@Override
public Coin getNullableResult(ResultSet resultSet, int i) throws SQLException {
String json = resultSet.getString(i);
return read(json);
}

@Override
public Coin getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
String json = callableStatement.getString(i);
return read(json);
}

@Nullable
private Coin read(String json) {
try {
return StringUtils.isBlank(json) ? null : JacksonHelper.mapper.readValue(json, Coin.class);
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
}

pocket类型 Fruits

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

/**
* @author hualong.jia(at)seaboxdata.com
* @since 1.0
*/
@Data
@Builder
@Accessors(chain = true)
@NoArgsConstructor
@AllArgsConstructor
public class Fruits {
private String color;
}

Mybatis 对于Coin类型的TypeHandler FruitsTypeHander.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.jetbrains.annotations.Nullable;

import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class FruitsTypeHandler extends BaseTypeHandler<Fruits> {

@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, Fruits fruits, JdbcType jdbcType) throws SQLException {
String jsonb = JacksonHelper.writeToJson(fruits);
preparedStatement.setString(i, jsonb);
}

@Override
public Fruits getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
String json = resultSet.getString(columnName);
return read(json);
}

@Override
public Fruits getNullableResult(ResultSet resultSet, int i) throws SQLException {
String json = resultSet.getString(i);
return read(json);
}

@Override
public Fruits getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
String json = callableStatement.getString(i);
return read(json);
}

@Nullable
private Fruits read(String json) {
try {
return StringUtils.isBlank(json) ? null : JacksonHelper.mapper.readValue(json, Fruits.class);
} catch (IOException e) {
e.printStackTrace();
return null;
}
}
}

包含Coin的person类 PersonCoin.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

@TableName(value = "person")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@JsonIgnoreProperties(ignoreUnknown = true)
public class PersonCoin {
@TableId(type = IdType.INPUT)
private Long id;
private String name;
private String type;
@TableField(typeHandler = CoinTypeHandler.class)
private Coin pocket;
}

PersonCoin的Resp PersonCoin.java

1
2
3
4
5
6
7
8
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
public interface PersonCoinResp extends BaseMapper<PersonCoin> {
}

包含Fruits的Person类 PersonFruits.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;

@TableName(value = "person")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@JsonIgnoreProperties(ignoreUnknown = true)
public class PersonFruits {
@TableId(type = IdType.INPUT)
private Long id;
private String name;
private String type;
@TableField(typeHandler = FruitsTypeHandler.class)
private Fruits pocket;
}

PersonFruits的Resp PersonFruitsResp.java

1
2
3
4
5
6
7
8
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
public interface PersonFruitsResp extends BaseMapper<PersonFruits> {
}

测试方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
@ActiveProfiles("local")
@SpringBootTest(classes = App.class, webEnvironment = SpringBootTest.WebEnvironment.DEFINED_PORT)
@RunWith(SpringRunner.class)
public class PersonTest {

@Autowired
PersonCoinResp personCoinResp;
@Autowired
PersonFruitsResp personFruitsResp;

@Test
public void insertJsonTest() {
for(int i = 1 ; i < 3; i++) {
personFruitsResp.insert(PersonFruits.builder()
.id(10L + i)
.type("fruits")
.name("水果人" + i)
.pocket(Fruits.builder().color("RED").build())
.build());

personCoinResp.insert(PersonCoin.builder()
.id(20L + i)
.type("coin")
.name("金币人" + i)
.pocket(Coin.builder().coinAmount(128).build())
.build());
}
}

@Test
public void findByIdTest() {
PersonFruits personFruits = personFruitsResp.selectById(11L);
PersonCoin personCoin = personCoinResp.selectById(21L);
System.out.println(personCoin);
System.out.println(personFruits);

personFruits = personFruitsResp.selectById(12L);
personCoin = personCoinResp.selectById(22L);
System.out.println(personCoin);
System.out.println(personFruits);

List<PersonFruits> personFruitsList = personFruitsResp.selectList(
new LambdaQueryWrapper<PersonFruits>().eq(PersonFruits::getType, "fruits")
);
System.out.println(personFruitsList);
}
}