Using jsonb postgres format into Spring data jdbc

user_4834223 0
Viewed 3542

I have the following test table:

CREATE TABLE user (
  id UUID NOT NULL PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(100),
  address jsonb
)

What I want to do is loading this table into an entity

public class Buddy{
  @Id
  private UUID id;
  private String name;
  private Address address;

  //getter and setter
}

where the Address is something like

public class Address {
    @JsonProperty("name")
    public String name;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

Is not clear to me how can I convert my json string into a java Pojo? is possible doing this, or is out of the scope?

spring-data-jdbc jsonb spring-data postgresql spring
1 Answers

With Spring Data JDBC

Sample data -- You can't create a database table named 'user' in PostgreSQL

drop table users;

CREATE TABLE users (
    id VARCHAR(10) NOT NULL PRIMARY KEY,
    name VARCHAR(100),
    address jsonb
);


insert into users values('1', 'Jhon Doe', '{ "name": "Main St Anytown, USA"}');

Repository

@Repository
public interface BuddyRepository extends CrudRepository<Buddy, String>{

}

You can create and register a Converter

@Configuration
@ComponentScan("com.example.demo")
public class Config extends AbstractJdbcConfiguration {

    @Bean
    public DataSource pgDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.postgresql.Driver");
        dataSource.setUrl("jdbc:postgresql://localhost:5432/db");
        dataSource.setUsername("postgres");
        dataSource.setPassword("postgres");

        return dataSource;
    }

    @Bean
    public JdbcCustomConversions jdbcCustomConversions() {
        final List<Converter<?, ?>> converters = new ArrayList<>();
        converters.add(EntityWritingConverter.INSTANCE);
        converters.add(EntityReadingConverter.INSTANCE);
        return new JdbcCustomConversions(converters);
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }

    @WritingConverter
    enum EntityWritingConverter implements  Converter<Address, PGobject> {
        INSTANCE;
        @Override
        public PGobject convert(Address source) {
            ObjectMapper objectMapper = new ObjectMapper();

            PGobject jsonObject = new PGobject();
            jsonObject.setType("json");
            try {
                jsonObject.setValue(objectMapper.writeValueAsString(source));
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
            return jsonObject;
        }
    }

    @ReadingConverter
    enum EntityReadingConverter implements  Converter<PGobject, Address> {
        INSTANCE;
        @Override
        public Address convert(PGobject pgObject) {
             ObjectMapper objectMapper = new ObjectMapper();
            String source = pgObject.getValue();
            try {
                return objectMapper.readValue(source, Address.class);
            } catch (JsonProcessingException e) {
                e.printStackTrace();
            }
            return null;
        }
    }
}

Run

@Autowired
    BuddyRepository repository;

    @Override
    public void run(String... arg0) throws Exception {
        Optional<Buddy> pojo = repository.findById("1");
        System.out.println(pojo.get().id);
        System.out.println(pojo.get().address.getName());
    }

Results

1 Main St Anytown, USA

With JPA and Hibernate

Try this way

Maven dependency

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

Or Gradle dependency

compile group: 'com.vladmihalcea', name: 'hibernate-types-52', version: '1.0.0'

--

import com.vladmihalcea.hibernate.type.json.JsonBinaryType
import org.hibernate.annotations.TypeDef

@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Buddy{
  @Id
  private UUID id;
  private String name;

  @Type(type = "jsonb")
  @Column(columnDefinition = "jsonb")
  private Address address;

  //getter and setter
}


@JsonInclude(JsonInclude.Include.NON_NULL)
public class Address {
    @JsonProperty("name")
    public String name;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}
user_82379700
Related