DBのプロトコルとJDBC
おはなしすること
きっかけ
DBプロトコル
・MySQL
・Postgres
SELECTを送り、ResultSetが戻ってくるまでの部分
JDBC
・Types(1,2,3,4)
・Version
きっかけ
ChromeAppsではJSで直接TCP叩けると聞き
Postgresのドライバ書いて遊んでいた
(Trust認証のみ、SSLは非対応)
なお、ChromeAppsは廃止予定のもよう
Postgres
Postgres
tag 1byte
length 4byte
body
- tag
データの種類を示す
'Q':クエリ
'T':結果メタ情報 'D':行データ
これらは、送信受信で意味が異なる - length
length + bodyのバイト数
（つまり必ず>=4） - body
tagに応じて可変
Postgres
51 00 00 00 19 53 45 4c 45 43 54 20 2a 20 46 52
4f 4d 20 42 4f 4f 4b 53 3b 00 SELECT * FROM BOOKS;
をpsqlから実行した時のパケット
細かく分解してみる
5100 00 00 1953 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 42 4f 4f 4b 53 3b 00= 'Q'
= 25
SELECT * FROM BOOKS;<NULL>
(6 + 1 + 1 + 1 + 4 + 1 + 5 + 1+ 1= 21)
http://www.utf8-chartable.de/
結果セット
以下の順でパケットが来る
-
メタデータ (全カラム分)
tag: 'T' - 行データ x (行数)
tag: 'D' - 完了
tag: 'C' - 待機
tag: 'Z'
メタデータ(全カラム)
54 00 00 00 4b 00 03 69 64 00 00 00 40 0e 00 01
00 00 00 17 00 04 ff ff ff ff 00 00 6e 61 6d 65
00 00 00 40 0e 00 02 00 00 00 19 ff ff ff ff ff
ff 00 00 61 75 74 68 65 72 00 00 00 40 0e 00 03
00 00 00 19 ff ff ff ff ff ff 00 00PostgreSQL
Type: Row description
Length: 75
Field count: 3
Column name: id
Table OID: 16398
Column index: 1
Type OID: 23
Column length: 4
Type modifier: -1
Format: Text (0)
Column name: name
Column name: auther
結果行データ(1行分)
44 00 00 00 3f 00 03 00 00 00 01 31 00 00 00 1e
52 65 2d 45 6e 67 69 6e 65 65 72 69 6e 67 20 4c
65 67 61 63 79 20 53 6f 66 74 77 61 72 65 00 00
00 0e 43 68 72 69 73 20 42 69 72 63 68 61 6c 6cPostgreSQL
Type: Data row
Length: 63
Field count: 3
Column length: 1
Data: 31
Column length: 30
Data: 52652d456e67696e656572696e67204c656761637920536f...
Column length: 14
Data: 4368726973204269726368616c6c
完了
43 00 00 00 0d 53 45 4c 45 43 54 20 33 00PostgreSQL
Type: Command completion
Length: 13
Tag: SELECT 3
待機
5a 00 00 00 05 49PostgreSQL
Type: Ready for query
Length: 5
Status: Idle (73)
MySQL
MySQL
seq 1byte
length 3byte
body
-
length
bodyのバイト数 - seq
リクエストを起点とし
パケット毎に+1 - body
可変
(先頭バイトや
その時の状況で判断)
MySQL
14 00 00 00 03 53 45 4c 45 43 54 20 2a 20 46 52
4f 4d 20 42 4f 4f 4b 53 SELECT * FROM BOOKS;
をmysqlから実行した時のパケット
細かく分解してみる
14 00 000053 45 4c 45 43 54 20 2a 20 46 52 4f 4d 20 42 4f 4f 4b 53= 20 (little endian)
= 0 (SEQ)
SELECT * FROM BOOKS
(6 + 1 + 1 + 1 + 4 + 1 + 5= 19)
セミコロンとNULL文字含まない
03= type query
結果セット
以下の順でパケットが来る
Postgresと異なり、
パケット単体で種別の判別は厳しい
-
カラム数
- メタデータ x (カラム数)
- 行データ x (行数)
- EOF
カラム数
01 00 00 01 03MySQL Protocol
Packet Length: 1
Packet Number: 1
Number of fields: 3メタデータ(1カラム分)
28 00 00 02 03 64 65 66 04 74 65 73 74 05 42 4f
4f 4b 53 05 42 4f 4f 4b 53 02 69 64 02 69 64 0c
3f 00 0b 00 00 00 03 03 42 00 00 00 MySQL Protocol
Packet Length: 40
Packet Number: 2
Catalog: def
Database: test
Table: BOOKS
Original table: BOOKS
Name: id
Original name: id
Charset number: binary COLLATE binary (63)
Length: 11
Type: FIELD_TYPE_LONG (3)
Flags: 0x4203
.... .... .... ...1 = Not null: Set
.... .... .... ..1. = Primary key: Set
.... .... .... .0.. = Unique key: Not set
.... .... .... 0... = Multiple key: Not set
.... .... ...0 .... = Blob: Not set
.... .... ..0. .... = Unsigned: Not set
.... .... .0.. .... = Zero fill: Not set
.... .... 0... .... = Binary: Not set
.... ...0 .... .... = Enum: Not set
.... ..1. .... .... = Auto increment: Set
.... .0.. .... .... = Timestamp: Not set
.... 0... .... .... = Set: Not set
Decimals: 0行データ(1カラム分)
30 00 00 05 01 31 1e 52 65 2d 45 6e 67 69 6e 65
65 72 69 6e 67 20 4c 65 67 61 63 79 20 53 6f 66
74 77 61 72 65 0e 43 68 72 69 73 20 42 69 72 63
68 61 6c 6c MySQL Protocol
Packet Length: 48
Packet Number: 5
Catalog: 1
Database: Re-Engineering Legacy Software
Table: Chris BirchallこのWireSharkの解析は嘘。
PackertNumber以降は、以下の形式の繰り返し。
[len byte][char ...] ...
EOF
07 00 00 08 fe 00 00 22 00 00 00MySQL Protocol
Packet Length: 7
Packet Number: 8
EOF marker: 254
Warnings: 0
Server Status: 0x0022
Payload: 0000Body部が fe で始まるパケット
package com.mysql.jdbc;
...
public class Buffer {
...
public static final short TYPE_ID_EOF = 0xFE;やりたいことは
（ほぼ）同じ
- SQLの文字列を送信
- 表形式（として解釈可能な）データが返ってくる
なのに
データの並び方が違う
そこでJDBC
共通する概念を
APIに抽出
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM BOOKS");
while ( rs.next() ) {
resultSet.getString(0);
}
ResultSetMetaData meta = rs.getMetaData();こういう単純なクエリならば
接続先が何だろうと同じように動く
SQLの方言の吸収はORマッパー、ライブラリの仕事
実現する方式も様々
Driver Types
Type 1
ODBCドライバをJNI経由で呼び出す
OpenDataBaseConnectivity
C言語用のDBアクセス統一API
sun.jdbc.odbc.jdbcodbcdriver
JDK8から同梱されなくなった模様
各種DBの違いはODBCで吸収されるため、事実上これが唯一の実装
ODBCとは
実装例
Type 2
DB固有ネイティブドライバをJNI経由で呼び出す
Oracle OCIドライバ
IBM DB2 にも存在したが、廃止された模様
実行環境にクライアントライブラリが
インストールされている必要がある
実装例
Type 3
DBに直接アクセスする中間サーバがまずあり、
そのサーバにより変換されたプロトコルを
PureJava実装されたクライアントドライバで解釈する
DB2
com.ibm.db2.jdbc.net.DB2Driver
Java アプレット用に設計された物らしい
忘れていいと思います
実装例
Type 4
PureJavaでDBに直接アクセス
(ネットワーク java.net.Socketなど)
送信パケット構築
受信パケット解析
全てJavaで実装
MySQL, PostgreSQL, Oracle, MSSQL...
実装例
Write once
Run anywhere
ドライバのコード
チラ見
private void sendSimpleQuery(SimpleQuery query, SimpleParameterList params) throws IOException {
String nativeSql = query.toString(params);
LOGGER.log(Level.FINEST, " FE=> SimpleQuery(query=\"{0}\")", nativeSql);
Encoding encoding = pgStream.getEncoding();
byte[] encoded = encoding.encode(nativeSql);
pgStream.sendChar('Q');
pgStream.sendInteger4(encoded.length + 4 + 1);
pgStream.send(encoded);
pgStream.sendChar(0);
pgStream.flush();
pendingExecuteQueue.add(new ExecuteRequest(query, null, true));
pendingDescribePortalQueue.add(query);
}Postgres
final ResultSetInternalMethods sqlQueryDirect(StatementImpl callingStatement, String query, String characterEncoding, Buffer queryPacket, int maxRows,
int resultSetType, int resultSetConcurrency, boolean streamResults, String catalog, Field[] cachedMetadata) throws Exception {
this.statementExecutionDepth++;
try {
if (this.statementInterceptors != null) {
ResultSetInternalMethods interceptedResults = invokeStatementInterceptorsPre(query, callingStatement, false);
if (interceptedResults != null) {
return interceptedResults;
}
}
long queryStartTime = 0;
long queryEndTime = 0;
String statementComment = this.connection.getStatementComment();
if (this.connection.getIncludeThreadNamesAsStatementComment()) {
statementComment = (statementComment != null ? statementComment + ", " : "") + "java thread: " + Thread.currentThread().getName();
}
if (query != null) {
// We don't know exactly how many bytes we're going to get from the query. Since we're dealing with Unicode, the max is 2, so pad it
// (2 * query) + space for headers
int packLength = HEADER_LENGTH + 1 + (query.length() * 3) + 2;
byte[] commentAsBytes = null;
if (statementComment != null) {
commentAsBytes = StringUtils.getBytes(statementComment, null, characterEncoding, this.connection.getServerCharset(),
this.connection.parserKnowsUnicode(), getExceptionInterceptor());
packLength += commentAsBytes.length;
packLength += 6; // for /*[space] [space]*/
}
if (this.sendPacket == null) {
this.sendPacket = new Buffer(packLength);
} else {
this.sendPacket.clear();
}
this.sendPacket.writeByte((byte) MysqlDefs.QUERY); // == 3 /////////////
if (commentAsBytes != null) {
this.sendPacket.writeBytesNoNull(Constants.SLASH_STAR_SPACE_AS_BYTES);
this.sendPacket.writeBytesNoNull(commentAsBytes);
this.sendPacket.writeBytesNoNull(Constants.SPACE_STAR_SLASH_SPACE_AS_BYTES);
}
if (characterEncoding != null) {
if (this.platformDbCharsetMatches) {
this.sendPacket.writeStringNoNull(query, characterEncoding, this.connection.getServerCharset(), this.connection.parserKnowsUnicode(),
this.connection);
} else {
if (StringUtils.startsWithIgnoreCaseAndWs(query, "LOAD DATA")) {
this.sendPacket.writeBytesNoNull(StringUtils.getBytes(query));
} else {
this.sendPacket.writeStringNoNull(query, characterEncoding, this.connection.getServerCharset(),
this.connection.parserKnowsUnicode(), this.connection);
}
}
} else {
this.sendPacket.writeStringNoNull(query);
}
queryPacket = this.sendPacket;
}
MySQL
JDBC Versions
3.0
ネット上のサンプルはこれが意外と多い
4.0 (Java6)
Class.forName(DriverName) 不要化
(META-INF/services/java.sql.Driver)
4.1 (Java7)
try-with-resources 対応
Connection,Statement,ResultSetなどが
AutoCloseableになった
ちょっと余談
Postgres互換DB
- RedShift
- H2Database(ServerMode)
Postgresドライバでアクセスできる
＝
Postgresと同じ規則でTCPパケットを入出力
もっと余談
PHPにはPDO！
方式的にはJDBCでいうType2みたい
libpq.so, libmysqlclient を参照していた
みんな安心してRDBを使っていきましょう！
ご清聴ありがとうございました
DBProtocols-JDBC
By yohei yamana
DBProtocols-JDBC
- 1,315
