![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
|
Glassfish JPA JSONB |
$ 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,0,1,0,0,0
01101,"064 ","0640820","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｵｵﾄﾞｵﾘﾆｼ(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,0,1,0,0,0
01101,"064 ","0640821","ﾎｯｶｲﾄﾞｳ","ｻｯﾎﾟﾛｼﾁｭｳｵｳｸ","ｷﾀ1ｼﾞｮｳﾆｼ(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,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 |
