MySQL JSON型をmybatis で読み書きするサンプル

MySQL バージョン 5.7.8 以降で使える JSON型に対する mybatis のハンドラ実装のサンプルです。
JSON型のデータが、単純な key-value だけでなくネストしたオブジェクトが入ったJSONでも、
Java のクラスのネストと対応できることを示すためのサンプルです。

https://github.com/yipuran/yipuran-mybatis
を使います。この yipuran-mybatis のバージョン4.7 以降から、
jackson-databind バージョン 2.12.4 以上を指定した依存関係を持つようになっており Jackson の
JSON変換を利用できます。
サンプルのMySQL テーブル構造

DELIMITER //
DROP TABLE IF EXISTS sakila.t_item
//
CREATE TABLE sakila.t_item(
  id                    INT  NOT NULL AUTO_INCREMENT
, name                  VARCHAR(40) NOT  NULL
, message               JSON NOT NULL
, PRIMARY KEY (id)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
//
DELIMITER ;

サンプルデータ格納

TRUNCATE TABLE t_item;
INSERT INTO t_item( name, message ) VALUES
('オレンジ', '{ "price": 200, "limit": "2021-12-07" }')
,('レモン', JSON_OBJECT("price", 180, "limit", "2021-12-08"))
,('メロン', '{ "price": 1200, "limit": "2021-12-16" , "other":{ "label":"ABC", "width": 230 } }')
;
commit;

SELECT * FROM t_item
;

↑↑↑↑↑
メロンのレコード、message は、JSON_OBJECT関数を使わずに、
ネストしたJSON を '{ }' の中に、"other":{ } で、作成することができる。

テーブル t_item のエンティティクラス
JSON データを格納するクラス sample.entity.Messageを含む

import lombok.Data;
import sample.entity.Message;
/**
 * Titem
 */
@Data
public class Titem{
   private int id;
   private String name;
   private Message message;
}

JSONとしてテーブルに格納されている Message クラス

package sample.entity;
import java.time.LocalDate;
import lombok.Data;
/**
 * Message
 */
@Data
public class Message{
   private int price;
   private LocalDate limit;
   private MessageNest1 other;
}

Message のネスト other のクラス

import lombok.Data;
/**
 * MessageNest1
 */
@Data
public class MessageNest1{
   public String label;
   public int width;
}

JSONタイプハンドラを準備
yipuran-mybatis の AbstractJsonTypeHandler を継承して用意する。
sample.types.JsonTypeHandler.java

package sample.types;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import org.yipuran.mybatis.types.AbstractJsonTypeHandler;
import com.fasterxml.jackson.databind.module.SimpleModule;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateDeserializer;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateSerializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalTimeSerializer;
/**
 * JsonTypeHandler
 */
public class JsonTypeHandler<T> extends AbstractJsonTypeHandler<T> {
   public JsonTypeHandler(Class<T> clazz){
      super(clazz);
   }
   @Override
   public SimpleModule getModule(){
      JavaTimeModule jtm = new JavaTimeModule();
      jtm.addSerializer(new LocalDateTimeSerializer(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS")))
     .addSerializer(new LocalDateSerializer(DateTimeFormatter.ofPattern("yyyy-MM-dd")))
      .addSerializer(LocalTimeSerializer.INSTANCE)
      .addDeserializer(LocalDateTime.class, new LocalDateTimeDeserializer(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS")))
     .addDeserializer(LocalDate.class, new LocalDateDeserializer(DateTimeFormatter.ofPattern("yyyy-MM-dd")))
      .addDeserializer(LocalTime.class, new LocalTimeDeserializer(DateTimeFormatter.ISO_LOCAL_TIME));
      return jtm;
   }
}

.addSerializer(LocalDateSerializer.INSTANCE) を使ってしまうと、[yyyy, MM, dd] シリアライズになってしまうので、
.addSerializer(new LocalDateSerializer(DateTimeFormatter.ofPattern("yyyy-MM-dd")))
を使った方が良い

mybatis のDB接続設定とSQLMap宣言
上記、JsonTypeHandler を、JSON型の Message に指定する。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
   <settings>
      <setting name="mapUnderscoreToCamelCase" value="true"/>
   </settings>
   <typeHandlers>
     <typeHandler handler="org.labo.dbtest.types.JsonTypeHandler" javaType="sample.entity.Message"/>
   </typeHandlers>
   <environments default="deployment">
      <environment id="deployment">
         <transactionManager type="JDBC" />
         <dataSource type="POOLED">
         <property name="driver" value="com.mysql.cj.jdbc.Driver" />
            <property name="url" value="jdbc:mysql://localhost:3306/testDB" />
            <property name="username" value="root" />
            <property name="password" value="admin123" />
            <property name="defaultAutoCommit" value="false" />
            <property name="poolPingQuery" value="SELECT 1 FROM DUAL" />
            <property name="poolPingEnabled" value="true" />
            <property name="poolMaximumActiveConnections" value="10"/>
            <property name="poolMaximumIdleConnections"   value="10" />
            <property name="poolTimeToWait" value="60000" />
         </dataSource>
      </environment>
   </environments>
   <mappers>
      <mapper resource="sqlmap.xml"/>
   </mappers>
</configuration>

sqlmap.xmlSQL Mapper を定義、

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="sample.mapper.SimpleMapper">

<update id="updateTitem" parameterType="org.labo.dbtest.entity.Titem">
UPDATE t_item SET message = #{message} WHERE id = #{id}
</update>

</mapper>

アノテーションで SELET と INSERT の SQL
INSERT のマッピングでは、message列 にタイプハンドラ JsonTypeHandlerを指定する

package sample.mapper.SimpleMapper;
import java.util.List;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.labo.dbtest.entity.Titem;

public interface SimpleMapper{
   @Select("SELECT * FROM t_item ")
   public List<Titem> getTItems();

   @Insert("INSERT INTO t_item(name, message)VALUES( #{name},#{message, typeHandler=sample.types.JsonTypeHandler} )")
   public void insertM(Titem item);
}

↑ INSEERT で、typeHandlerを指定した書き方であるが、タイプハンドラをコンフィグで全体に指定するならここで
書かなくても良い
Java のロジック
SampleLogic,java

import java.util.List;
public interface SampleLogic{
   public List<Titem> getTItems();
   public void insertM(Titem item);
   public void updateTitem(Titem item);
}

SampleLogic の実装

import java.util.List;
import org.labo.dbtest.entity.Titem;
import org.labo.dbtest.mapper.SimpleMapper;
import org.yipuran.mybatis.IBatisDao;
import org.yipuran.mybatis.QueryLimited;
import org.yipuran.mybatis.Transaction;
public class SampleLogicImpl extends IBatisDao implements SampleLogic{
   @QueryLimited
   @Override
   public List<Titem> getTItems(){
      return getMapper(SimpleMapper.class).getTItems();
   }
   @Transaction
   @Override
   public void insertM(Titem item){
      getMapper(SimpleMapper.class).insertM(item);
   }
   @Transaction
   @Override
   public void updateTitem(Titem item){
      getSqlSession().update(bindId(SimpleMapper.class, "updateTitem"), item);
   }
}

参照クエリの実行例
yipuran-mybatisIBatisModule で Configuration.xml で記述したSQLMapの sqlmap.xml を読ませる。
Google guice でインジェクトした SampleLogic を実行して
MySQL のテーブル t_item を SELECT した結果を標準出力する。

import java.util.List;
import org.yipuran.mybatis.IBatisModule;
import com.google.inject.Guice;
import com.google.inject.Injector;
Injector injector = Guice.createInjector(new IBatisModule());
SampleLogic logic = injector.getInstance(SampleLogicImpl.class);
List<Titem> list = logic.getTItems();
list.stream().forEach(e->{
   System.out.println(e.getId()+" "+e.getName()+ "  "+e.getMessage() );
});

SELECT 標準出力結果

1 オレンジ  Message(price=200, limit=2021-12-07, other=null)
2 レモン  Message(price=180, limit=2021-12-08, other=null)
3 メロン  Message(price=1200, limit=2021-12-16, other=MessageNest1(label=ABC, width=230))

更新の例、

Injector injector = Guice.createInjector(new IBatisModule());
SampleLogic logic = injector.getInstance(SampleLogicImpl.class);
List<Titem> list = logic.getTItems();
list.stream().filter(e->e.getId()==3).findAny().ifPresent(e->{
   Titem t = e;
   System.out.println(t);
   Message m = t.getMessage();
   MessageNest1 n = m.getOther();
   n.setLabel("testA");
   m.setOther(n);
   t.setMessage(m);
   // 更新実行
   logic.updateTitem(t);
});
System.out.println("\"other\":{\"label\":\"testA\"\"testA\"  ");
list.stream().filter(e->e.getId()==3).findAny().ifPresent(e->{
   System.out.println(e);
});

更新実行の標準出力結果

Titem(id=3, name=メロン, message=Message(price=1200, limit=2021-12-16, other=MessageNest1(label=ABC, width=230)))
"other":{"label":"testA" ⇒ "testA"  
Titem(id=3, name=メロン, message=Message(price=1200, limit=2021-12-16, other=MessageNest1(label=testA, width=230)))

paramiko で対話式の処理

paramiko で対話式を処理するにはどうしたら良いのか?
paramiko-pexpect とかを使えば良いのかもしれないが、invoke_shell() 、シェルとして実行する方法で
踏み台の先の telnet 接続してコマンドを流す方法を考えた。

以下の例は最初のSSH接続の後、もう1台 SSH で踏み台に入り、そこで telnet でコマンド実行するサンプル

まずは、最後に使う telnet 接続してコマンド実行するためのクラス・メソッドの定義
expectshell.py

# -*- coding: utf-8 -*-
import re

class ExpectShell:
    def __init__(self, client, recvsize=1000):
        self.size = recvsize
        self.shell = client.invoke_shell()
        self.shell.recv(self.size)
    def send(self, command, until=None):
        self.shell.send('{}\n'.format(command))
        if until==None:
            return command
        output = ''
        while True:
            output = output + self.shell.recv(self.size).decode('utf-8')
            if re.search(until, output):
                break
        return output

踏み台1→2→ telnet 実行サンプル
踏み台1は、pem 鍵ファイルで接続する。

# -*- coding: utf-8 -*-
import paramiko
import time
from expectshell import ExpectShell

jump1_host = '1xx.1xx.1xx.1xx'
jump1_user = 'admin'
jump1_port = 22
key_file = 'admin.pem'

# jump1 client
pkey = paramiko.Ed25519Key.from_private_key_file(key_file)
jump1Cl = paramiko.SSHClient()
jump1Cl.set_missing_host_key_policy(paramiko.AutoAddPolicy())

print("jump1 connecting...")
jump1Cl.connect(hostname=jump1_host, port=jump1_port, username=jump1_user, pkey=pkey)
print('jump1 connected')

(stdin, stdout, stderr) = jump1Cl.exec_command('hostname')
time.sleep(1)
hostname = stdout.read().decode()
print('hostname = %s' % hostname)

# jump2 ⇒ jump2
jump2_host = '2xx.2xx.2xx.2xx'
jump1 = (jump1_host, jump1_port)
jump2 = (jump2_host, 22)
transport1 = jump1Cl.get_transport()
channel1 = transport1.open_channel("direct-tcpip", jump2, jump1)
jump2Cl = paramiko.SSHClient()
jump2Cl.set_missing_host_key_policy(paramiko.AutoAddPolicy())
print("jump2 connecting...")

jump2Cl.connect(hostname=jump2_host, username='admin', password='XXXXXX', sock=channel1)
print('jump2 connected')

shell = ExpectShell(jump2Cl)
res = shell.send('telnet 3xx.3xx.3xx.33', 'login: ')
print(res)

res = shell.send('root', 'Password: ')
print(res)

res = shell.send('XYZ112233', 'Prompt\([0-9]+\)# ')
print(res)

# telnet 接続して実行したいコマンド
res = shell.send('ls -la', 'Prompt\([0-9]+\)# ')
print(res)

res = shell.send('exit')
print(res)

jump2Cl.close()
jump1Cl.close()

文字列 format で辞書を指定

文字列フォーマットのキー名による置換はよく以下のように書くであろう。

dburl="{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?charset={charset_type}"\
    .format(dialect='mysql', driver='pymysql', username='admin', password='lra21opS941',
            host='localhost', port=3306, database='testDB', charset_type='utf8')
print(dburl)
mysql+pymysql://admin:lra21opS941@localhost:3306/testDB?charset=utf8

以下も可能であることを忘れずに。。。

辞書で指定する。

config = { 'dialect':'mysql', 'driver':'pymysql', 'username':'admin', 'password':'lra21opS941',
            'host':'localhost', 'port':3306, 'database':'testDB', 'charset_type':'utf8' }

dburl="{dialect}+{driver}://{username}:{password}@{host}:{port}/{database}?charset={charset_type}"\
    .format(**config)

Handsontable の DataSchema と afterCreateRow

Handsontable は、DataSchema を定義しておけば
行挿入(row_above)で生成される行の初期値が定義した値になる。
DataSchema での定義は、Handsontable インスタンス生成のタイミングで決まる。
後からこれを変更するなんてナンセンスである。
また、DataSchema で、function() 関数を定義してもインスタンス生成でその関数が
実行されることを留意しなければならない

afterCreateRow イベントとの併用

<script src="https://momentjs.com/downloads/moment-with-locales.js"></script>
moment.locale('ja');
var hot = new Handsontable(document.getElementById("table"), {
   data: data,
   language: 'ja-JP',
   columns:[
      { data:'item',  type: 'text' },
      { data:'price', type: 'numeric' },
      { data:'limit', type: 'date', dateFormat: 'YYYY/MM/DD' },
   ],
   colHeaders: [ "A", "B", "C" ],
   copyPaste: true,
   autoColumnSize: true,
   maxCols: 3,
   filters: true,
   dropdownMenu: ['filter_by_condition', 'filter_by_value', 'filter_action_bar'],
   dataSchema: { item:'aaa', price:120, limit:null },
   contextMenu: {
      items:{
         'row_above': { name: '1行挿入' },
         'remove_row': { name: '1行削除', disabled: function(){ return hot.countRows() < 2; }  },
         "hsep": "---------",
         'undo': { name: '戻る' },
      },
   },
   afterCreateRow: function(row, amount){
      this.setDataAtCell(row, 2, moment().format("YYYY/MM/DD"));
   },
   licenseKey: 'non-commercial-and-evaluation',
});

もしくは、イベントフック追加として書くなら、

hot.addHook('afterCreateRow', function(row, amount){
    this.setDataAtCell(row, 2, moment().format("YYYY/MM/DD"));
});

このように afterCreateRow を定義した時に、
注意すべき点は、コピー&Paste で貼り付け時に行生成されて
 行生成 → DataSchema 実行 → 貼り付け → afterCreateRow 実行
となってしまうことである。

TeraTerm の SSH 接続の記憶場所

TeraTerm は、はじめてSSH接続するホストに対しては、必ずこのウィンドウを開いて
問い合わせてくる。f:id:posturan:20210925134234j:plain

この記憶を消したい場合、TeraTerm のどうするのか?
ssh_known_hosts という TeraTermが管理するファイルを見つけて、IPアドレスとキーのペアが
書かれているものを消せば、再度この問い合わせウィンドウが出るようになる。
この ssh_known_hosts というファイルの場所がどこにあるのか?

これが、TeramTerm と同じ場所にあるなら良いが、そこに存在しない場合がある。
ユーザの AppData\Local\VirtualStore\Program Files (x86)\teraterm にある。
変数、%LOCALAPPDATA% を使って
cd %LOCALAPPDATA%\VirtualStore\Program Files (x86)\teraterm で移動するのが簡単であろう。

Handsontable の getData~ と、getSourceData

Handsontable インスタンスメソッドの getData~ は、よく使うもので、
これは、あくまでも表示データを取得するもの。

getDataAtRow(row) 指定行インデックスの表示データを取得
getDataAtCell(row, col) セルの表示データを取得
getDataAtProp('name') テーブルの列名全てを配列として取得
getData() テーブルの表示データ全てを取得
getData(fromRow, fromCol, toRow, toCol) from~to 範囲の表示データを取得

でも、columns 定義で data 属性を指定して表示する Handsontable で、
columns 定義で指定しないデータを参照するのは、
上のメソッドではできない。

 getDataAtRowProp(row, string) : 行インデックスと列名でデータを取得
を使うか、
 getSourceData() で、Handsontable がデータとして
使用するデータそのものを取得して参照するしかない。
Core - API Reference - Handsontable Documentation

Handsontable Datepicker の日本語化で、

Handsontable Datepicker の日本語化は、datePickerConfig 属性を以下のように、
長ったらしい書き換えが必要だ。

columns:[
    {   type: 'date',
        width: 120,
        dateFormat: 'YYYY/MM/DD',
        datePickerConfig: {
            yearSuffix: '年',
            showMonthAfterYear: true,
            showDaysInNextAndPreviousMonths: true,
            i18n: {
                previousMonth: '前月',
                nextMonth: '次月',
                months: moment.localeData()._months,
                weekdays: moment.localeData()._weekdays,
                weekdaysShort: moment.localeData()._weekdaysShort
            }
        }
    },
],

いくつもの列で DATE型が出てくる場合、沢山これを書かないようにするのは、
案外、地味な方法でよい。

const jpDatePickerConfig = {
    yearSuffix: '年',
    showMonthAfterYear: true,
    showDaysInNextAndPreviousMonths: true,
    i18n: {
        previousMonth: '前月',
        nextMonth: '次月',
        months: moment.localeData()._months,
        weekdays: moment.localeData()._weekdays,
        weekdaysShort: moment.localeData()._weekdaysShort
    }
};

この jpDatePickerConfig を、以下のとおり使えば良い。

columns:[
    {   type: 'date',
        width: 120,
        dateFormat: 'YYYY/MM/DD',
        datePickerConfig: jpDatePickerConfig
    },
],