Glassfish JPA JSONB

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
 xmlns="http://xmlns.jcp.org/xml/ns/persistence"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
  <persistence-unit name="com.mycompany_JSONBExam_jar_1.0-SNAPSHOTPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <class>com.mycompany.jsonbexam.PostalTbl</class>
    <class>com.mycompany.jsonbexam.PostaljTbl</class>
    <class>com.mycompany.jsonbexam.JSONBConverter</class>
    <properties>
      <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://192.168.38.101:5432/sample"/>
      <property name="javax.persistence.jdbc.user" value="docker"/>
      <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
      <property name="javax.persistence.jdbc.password" value="docker"/>
    </properties>
  </persistence-unit>
</persistence>

@Data
@NoArgsConstructor
@Entity
@Table(name = "postal_tbl")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "PostalTbl.findAll", query = "SELECT p FROM PostalTbl p"),
    @NamedQuery(name = "PostalTbl.findById", query = "SELECT p FROM PostalTbl p WHERE p.id = :id"),
    @NamedQuery(name = "PostalTbl.findByOncreate", query = "SELECT p FROM PostalTbl p WHERE p.oncreate = :oncreate"),
    @NamedQuery(name = "PostalTbl.findByLocalagencycode", query = "SELECT p FROM PostalTbl p WHERE p.localagencycode = :localagencycode"),
    @NamedQuery(name = "PostalTbl.findByPostalcode", query = "SELECT p FROM PostalTbl p WHERE p.postalcode = :postalcode"),
    @NamedQuery(name = "PostalTbl.findByProvince", query = "SELECT p FROM PostalTbl p WHERE p.province = :province"),
    @NamedQuery(name = "PostalTbl.findByCity", query = "SELECT p FROM PostalTbl p WHERE p.city = :city"),
    @NamedQuery(name = "PostalTbl.findByArea", query = "SELECT p FROM PostalTbl p WHERE p.area = :area"),
    @NamedQuery(name = "PostalTbl.findByProvinceKana", query = "SELECT p FROM PostalTbl p WHERE p.provinceKana = :provinceKana"),
    @NamedQuery(name = "PostalTbl.findByCityKana", query = "SELECT p FROM PostalTbl p WHERE p.cityKana = :cityKana"),
    @NamedQuery(name = "PostalTbl.findByAreaKana", query = "SELECT p FROM PostalTbl p WHERE p.areaKana = :areaKana")})
public class PostalTbl implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    @Column(name = "oncreate")
    private Timestamp oncreate;
    @Column(name = "onupdate")
    @Version
    private Timestamp onupdate;
    @Column(name = "localagencycode")
    private String localagencycode;
    @Column(name = "postalcode")
    private String postalcode;
    @Column(name = "province")
    private String province;
    @Column(name = "city")
    private String city;
    @Column(name = "area")
    private String area;
    @Column(name = "province_kana")
    private String provinceKana;
    @Column(name = "city_kana")
    private String cityKana;
    @Column(name = "area_kana")
    private String areaKana;

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof PostalTbl)) {
            return false;
        }
        PostalTbl other = (PostalTbl) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }
}

public class ColMain {

    public static void main(final String args[]) {
        try (BufferedReader br = new BufferedReader(
                                   new InputStreamReader(
                                     new FileInputStream("src/main/resources/KEN_ALL.CSV"),"Windows-31J"));
        ){
            
            List<String[]> postals = new ArrayList<>();
            String line;
            while((line = br.readLine()) != null) {
                String[] row = line.replaceAll("\"", "").split(",");
                postals.add(row);
            }
            
            EntityManagerFactory emf = Persistence.createEntityManagerFactory("com.mycompany_JSONBExam_jar_1.0-SNAPSHOTPU");
            EntityManager em = emf.createEntityManager();
            EntityTransaction tx = em.getTransaction();
            
            long start = System.currentTimeMillis();
            
            tx.begin();
            
            Timestamp now = new Timestamp(System.currentTimeMillis());
            for (String[] row : postals) {
                PostalTbl entity = new PostalTbl();
                entity.setOncreate(now);
                entity.setOnupdate(now);
                entity.setArea(row[8]);
                entity.setAreaKana(row[5]);
                entity.setCity(row[7]);
                entity.setCityKana(row[4]);
                entity.setLocalagencycode(row[0]);
                entity.setPostalcode(row[2]);
                entity.setProvince(row[6]);
                entity.setProvinceKana(row[3]);
                
                em.persist(entity);                
            }
            
            tx.commit();
            
            System.out.println((System.currentTimeMillis() - start));
            
            start = System.currentTimeMillis();
            TypedQuery query = em.createNamedQuery("PostalTbl.findByProvince", PostalTbl.class);
            query.setParameter("province", "東京都");
            List<PostalTbl> res = query.getResultList();
            System.out.println((System.currentTimeMillis() - start));            
            System.out.println(res.size());

            em.close();
            emf.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
}

package com.mycompany.jsonbexam;

import java.io.IOException;
import java.io.Serializable;
import java.sql.Timestamp;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Version;
import javax.xml.bind.annotation.XmlRootElement;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@Entity
@Table(name = "postalj_tbl")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "PostaljTbl.findAll", query = "SELECT p FROM PostaljTbl p"),
    @NamedQuery(name = "PostaljTbl.findById", query = "SELECT p FROM PostaljTbl p WHERE p.id = :id"),
    @NamedQuery(name = "PostaljTbl.findByOncreate", query = "SELECT p FROM PostaljTbl p WHERE p.oncreate = :oncreate"),
})
public class PostaljTbl implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    @Column(name = "oncreate")
    private Timestamp oncreate;
    @Column(name = "onupdate")
    @Version
    private Timestamp onupdate;
    @Column(name = "contents")
    @Convert(converter = JSONBConverter.class)
    private String contents;

    public PostalBean getContentsAsBean() throws IOException {
        return Jackson.objectMapper.readValue(contents, PostalBean.class);
    }
    
    public void setContents(final PostalBean bean) throws IOException {
        contents = Jackson.objectMapper.writeValueAsString(bean);
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof PostaljTbl)) {
            return false;
        }
        PostaljTbl other = (PostaljTbl) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }
}

package com.mycompany.jsonbexam;

import java.io.Serializable;
import javax.xml.bind.annotation.XmlRootElement;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@XmlRootElement
@NoArgsConstructor
public class PostalBean implements Serializable {

    private String localAgencyCode;
    private String postalCode;
    private KanaKanji province;
    private KanaKanji city;
    private KanaKanji area;

    @Data
    @XmlRootElement
    @NoArgsConstructor
    @AllArgsConstructor
    public static class KanaKanji implements Serializable {

        private String kana;
        private String kanji;
    }
}

package com.mycompany.jsonbexam;

import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.Converter;
import org.postgresql.util.PGobject;

@Converter
public class JSONBConverter implements javax.persistence.AttributeConverter<String, PGobject> {

    @Override
    public String convertToEntityAttribute(final PGobject y) {
        return y.getValue();
    }

    @Override
    public PGobject convertToDatabaseColumn(final String x) {
    
        PGobject pgObj = new PGobject();
        try {
            pgObj.setType("jsonb");
            pgObj.setValue(x);
        } catch (SQLException ex) {
            Logger.getLogger(JSONBConverter.class.getName()).log(Level.SEVERE, null, ex);
        }
        return pgObj;
    }
}

package com.mycompany.jsonbexam;

import com.fasterxml.jackson.databind.ObjectMapper;

/**
 * ObjectMapper.
 * Jackson FAQ: Thread-Safety http://wiki.fasterxml.com/JacksonFAQThreadSafety
 * The basic rule of Jackson thread-safety is that factories follow "thread-safe after configuration" philosophy. 
 * @author hondou
 */
public class Jackson {
    public static ObjectMapper objectMapper;    
    static {
        objectMapper = new ObjectMapper();
    }    
}

package com.mycompany.jsonbexam;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;

public class JsonMain {

    public static void main(final String args[]) {
        try (BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream("src/main/resources/KEN_ALL.CSV"), "Windows-31J"));) {

            List<PostalBean> postals = new ArrayList<>();
            String line;
            while ((line = br.readLine()) != null) {
                String[] row = line.replaceAll("\"", "").split(",");
                PostalBean postal = new PostalBean();
                postal.setLocalAgencyCode(row[0]);
                postal.setPostalCode(row[2]);
                postal.setProvince(new PostalBean.KanaKanji(row[3], row[6]));
                postal.setCity(new PostalBean.KanaKanji(row[4], row[7]));
                postal.setArea(new PostalBean.KanaKanji(row[5], row[8]));
                postals.add(postal);
            }

            EntityManagerFactory emf = Persistence.createEntityManagerFactory("com.mycompany_JSONBExam_jar_1.0-SNAPSHOTPU");
            EntityManager em = emf.createEntityManager();
            EntityTransaction tx = em.getTransaction();

            long start = System.currentTimeMillis();

            tx.begin();

            Timestamp now = new Timestamp(System.currentTimeMillis());
            for (PostalBean postal : postals) {
                PostaljTbl entity = new PostaljTbl();
                entity.setOncreate(now);
                entity.setOnupdate(now);
                entity.setContents(postal);
                em.persist(entity);

                em.persist(entity);
            }

            tx.commit();

            System.out.println((System.currentTimeMillis() - start));
            start = System.currentTimeMillis();

            Query query = em.createNativeQuery("select * from postalj_tbl where contents @> '{\"province\": {\"kanji\":\"東京都\"}}'", PostaljTbl.class);

            List<PostaljTbl> tokyo = query.getResultList();

            System.out.println(tokyo.size());
            System.out.println((System.currentTimeMillis() - start));

            em.close();
            emf.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    }
}