$ head JSONBExam/src/main/resources/KEN_ALL.CSV | iconv -f SJIS -t UTF-8 01101,"060 ","0600000","ホッカイドウ","サッポロシチュウオウク","イカニケイサイガナイバアイ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0 01101,"064 ","0640941","ホッカイドウ","サッポロシチュウオウク","アサヒガオカ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0 01101,"060 ","0600041","ホッカイドウ","サッポロシチュウオウク","オオドオリヒガシ","北海道","札幌市中央区","大通東",0,0,1,0,0,0 01101,"060 ","0600042","ホッカイドウ","サッポロシチュウオウク","オオドオリニシ(1-19チョウメ)","北海道","札幌市中央区","大通西(1〜19丁目)",1,0,1,0,0,0 01101,"064 ","0640820","ホッカイドウ","サッポロシチュウオウク","オオドオリニシ(20-28チョウメ)","北海道","札幌市中央区","大通西(20〜28丁目)",1,0,1,0,0,0 01101,"060 ","0600031","ホッカイドウ","サッポロシチュウオウク","キタ1ジョウヒガシ","北海道","札幌市中央区","北一条東",0,0,1,0,0,0 01101,"060 ","0600001","ホッカイドウ","サッポロシチュウオウク","キタ1ジョウニシ(1-19チョウメ)","北海道","札幌市中央区","北一条西(1〜19丁目)",1,0,1,0,0,0 01101,"064 ","0640821","ホッカイドウ","サッポロシチュウオウク","キタ1ジョウニシ(20-28チョウメ)","北海道","札幌市中央区","北一条西(20〜28丁目)",1,0,1,0,0,0 01101,"060 ","0600032","ホッカイドウ","サッポロシチュウオウク","キタ2ジョウヒガシ","北海道","札幌市中央区","北二条東",0,0,1,0,0,0 01101,"060 ","0600002","ホッカイドウ","サッポロシチュウオウク","キタ2ジョウニシ(1-19チョウメ)","北海道","札幌市中央区","北二条西(1〜19丁目)",1,0,1,0,0,0 $ wc JSONBExam/src/main/resources/KEN_ALL.CSV 123712 189708 12233967 JSONBExam/src/main/resources/KEN_ALL.CSV
docker@6aebb77156f9:~$ psql psql (9.4beta2) Type "help" for help. docker=# CREATE DATABASE sample; CREATE DATABASE docker=# \c sample You are now connected to database "sample" as user "docker". sample=# CREATE TABLE postalj_tbl ( id bigserial primary key, onCreate timestamp default NULL, onUpdate timestamp default NULL, contents jsonb NOT NULL ); CREATE INDEX postalj_tbl_idx_contents ON postalj_tbl USING gin (contents); CREATE TABLE postal_tbl ( id bigserial primary key, onCreate timestamp default NULL, onUpdate timestamp default NULL, localAgencyCode char(5), postalCode char(7), province varchar(255), city varchar(255), area varchar(255), province_kana varchar(255), city_kana varchar(255), area_kana varchar(255) ); CREATE INDEX postal_tbl_idx_localAgencyCode ON postal_tbl (localAgencyCode); CREATE INDEX postal_tbl_idx_postalCode ON postal_tbl (postalCode); CREATE INDEX postal_tbl_idx_province ON postal_tbl (province); CREATE INDEX postal_tbl_idx_city ON postal_tbl (city); CREATE INDEX postal_tbl_idx_area ON postal_tbl (area); CREATE INDEX postal_tbl_idx_province_kana ON postal_tbl (province_kana); CREATE INDEX postal_tbl_idx_city_kana ON postal_tbl (city_kana); CREATE INDEX postal_tbl_idx_area_kana ON postal_tbl (area_kana);
<?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(); } } }
123712件登録 | 128,555ms |
3772件検索 | 73ms |
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(); } } }
123712件登録 | 125,522ms |
3772件検索 | 236ms |
Column | JSONB | JSONB/Column | |
123712件登録 | 128,555ms | 125,522ms | x0.98 |
3772件検索 | 73ms | 236ms | x3.23 |