◆PROCESSING 逆引きリファレンス
カテゴリー：ファイル操作
SQLiteを使う（データ挿入と削除）
【概要】
PROCESSINGはJavaをベースにした言語ですので、Javaと同じ手順でSQLiteを利用することが可能です。
SQLiteの利用準備とDBのOPEN/CLOSE処理については「SQLiteを使う（オープン・クローズ）」記事で紹介しました。
今回はOPEN（生成）したDBファイルに以下のような表が作成済みである事を前提に、この表へデータを挿入・削除する処理を紹介したいと思います。
なお表の作成については「SQLiteを使う（テーブル生成と削除）」記事を参照してください。
サンプルで利用するTEST表
| 列名 | 型 | 説明 |
|---|---|---|
| _id | INTEGER | 主キー |
| _datetime | TEXT | 日付格納用テキスト |
| _image | BLOB | バイナリデータ格納用。デフォルト値はNULL |
| _aspect | REAL | アスペクト比格納用。デフォルト値は1.0で0より大きいこと |
【詳細】
データを挿入する
表にデータを挿入（追加）するには、SQLのINSERT文を利用します。
SQLiteでSQL文を利用するためには、Statementクラスのメソッドを利用します。Statementクラスには、単発のSQL文を実行するexecuteUpdate()メソッドがありますので、これを利用します。
例えば以下のような感じです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
//現在日時を取得 DateTimeFormatter f = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss.SSS"); LocalDateTime ldt = LocalDateTime.now(); String dateTime = ldt.format( f ); //SQL文を組み立てる String sql = "INSERT INTO TEST( _DATETIME, _IMAGE, _ASPECT) VALUES( " + "'" + dateTime + "'," + "0x7F, 1.0 )"; try{ //挿入する state = con.createStatement( ); state.executeUpdate( sql ); state.close(); } catch( SQLException e ){ e.printStackTrace(); } |
state は Statementクラスのインスタンスです。
Statementクラスを生成する方法とexecuteUpdate()メソッドについては、「SQLiteを使う（テーブル生成と削除）」記事を参照してください。
INSERT 文は、基本的には以下のような形式で記述します。
|
1 2 3 4 |
INSERT INTO テーブル名 ( カラム1, カラム2, ... ) VALUES ( 値1, 値2, ... ) |
今回データを追加するTESTテーブルは、_IDが主キー（データを一意に識別するための情報）です。
本来はデータを追加するときに、主キーにはユニークな値を指定すべきですが、SQLiteでは主キーにINTEGER型を指定した場合、自動的にユニークな値を振ってくれます。
ですので上記サンプルでは、あえて_IDに値を指定していません。もちろん自分でユニークな値を指定する事も可能です。
データを削除する
表からデータを削除するには、SQLのDELETE文を利用します。SQLの実行方法はINSERT文のときと同じです。
|
1 2 3 4 5 6 7 8 9 10 |
try{ //データを削除する String sql = "DELETE FROM TEST " + "WHERE _DATETIME > '2019/08/10 14:00:00.000'"; state.executeUpdate( sql ); } catch( SQLException e ){ e.printStackTrace(); } |
state は Statementクラスのインスタンスです。
DELETE文は、基本的には以下のような形式で記述します
|
1 2 |
DELETE FROM テーブル名 WHERE 条件式; |
動的にパラメータを与える
先程の例ではデータの挿入・削除ともに、SQL文の中に直接値を指定していました。例えばINSERT文では、以下のような文字列が出来上がります。
|
1 2 |
INSERT INTO TEST( _DATETIME, _IMAGE, _ASPECT) VALUES( '2019/08/10 13:58.14.931', 0x7F, 1.0 ); |
この日付や 0x7F のような部分を、SQL文に直接記述するのではなくプログラムから与えたい場合があります。
そのような場合は、StatementクラスではなくPreparedStatementクラスを利用してSQL文を指定します。
PreparedStatementクラスは、DBへ接続した際に戻されるConnectionクラス経由で利用します。
PreparedStatement作成PreparedStatement psm = con.prepareStatement( String sql );
sql : ベースとなるSQL文
con : Connectionクラスのインスタンス
ベースとなるSQL文には、値を埋め込みたい位置に “?” を記述します。例えば以下のような感じです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
//現在日時を取得 DateTimeFormatter f = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss.SSS"); LocalDateTime ldt = LocalDateTime.now(); String dateTime = ldt.format( f ); //SQL文の雛形 String sql = "INSERT INTO TEST( _DATETIME, _IMAGE, _ASPECT) " + "VALUES( ?, ?, ? )"; //SQL文を実行 PreparedStatement psm = null; try{ psm = con.prepareStatement( sql ); //データを埋め込む byte b = 127; psm.setString( 1, dateTime ); psm.setByte( 2, b ); psm.setDouble( 3, 1.0d ); //実行する psm.executeUpdate(); psm.close(); } catch( SQLException e ){ e.printStackTrace(); } |
上記ではINSERT文で値を入れたい箇所に “?” で埋め込み指定をしています。
その後、埋め込み指定をした場所に PreparedStatement クラスがもつsetメソッドで、値を埋め込みます。
setメソッドには、埋め込みたい値のデータ型に応じて様々なものが用意されています。
代表的なものは以下になります。詳しくは上記公式リファレンスを参照してください。
| メソッド | 用途 | |
|---|---|---|
| 1 | setBinaryStream(int index, InputStream x); | index番目にバイナリデータを埋め込みます。 |
| 2 | setDouble(int index, double x); | index番目に浮動小数点データを埋め込みます。 |
| 3 | setInt(int index, int x); | index番目に整数データを埋め込みます。 |
| 4 | setString(int index, String x) | index番目に文字列データを埋め込みます。 |
| 5 | setByte(int index, byte x) | index番目に1バイトデータを埋め込みます。 |
| 6 | setNull(int index, java.sql.Types.NULL) | index番目にNULLを埋め込みます。 |
注意点
INSERTやDELETEでデータ操作を行う際、注意すべき事が２つほどあります。
- カラムの型に適合したデータを挿入すること
- 基本はオートコミットされる
１．データ型とカラム型
１つ目の注意点は、データを挿入するときはテーブルの各カラムの型に適合した値を指定する事です。
SQLiteではカラムの型と挿入するデータの型に相違があっても、エラーにはなりません。指定されたデータがカラム型に変換可能であれば変換して格納し、不可能ならそのまま格納します。
その結果、1つのカラムに異なるデータ型が混在するような事になる場合もあります。ここはハマるポイントですので注意してください。
参考：Qiita様:【SQLite3】コマンド例と他データベースとの比較から始める SQLite 入門:@d-yokoiさん
２．オートコミット
もう１つの注意点は、デフォルトでオートコミットを採用している点です。
複数の表を整合性をもって更新したい場合は、オートコミットをOFFにして、自力でコミット（あるいはロールバック）を行います。
オートコミット制御void con.setAutoCommit( boolean flg );
con : Connectionクラスのインスタンス
flg : true オートコミットを有効化/ false オートコミット無効化
con はDBへ接続した際に戻されるConnectionクラスのインスタンスです。
オートコミットを無効化した場合の注意としては、自分でコミットしない限り、プログラム終了時にすべてのDB操作が「無かったこと」にされる事です。
CREATE TABLEやDROP TABLEも「無かったこと」にされますので、注意してください。
コミットとロールバックは、以下のように利用します。
コミット・ロールバックvoid con.commit( );
void con.rollback( );
con : Connectionクラスのインスタンス
以下はオートコミットを無効化し、自力でコミットする例です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Connection con= null; try{ //DB OPEN con = DriverManager.getConnection( "jdbc:sqlite:" + sketchPath("hoge.db") ); //オートコミットを無効化 con.setAutoCommit( false ); //なにかの操作 //コミットする con.commit(); //DB CLOSE con.close(); } catch( SQLException e ){ e.printStackTrace(); try{ //ロールバックする if( con != null ){ con.rollback(); } } catch( SQLException ex ){ ex.printStackTrace(); } } |
コミットとロールバックは、ともに例外を発生させることがあるため、例外処理を加えてください。
【関連記事】
サンプルプログラム
画像データを保存する例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 |
import java.sql.*; import java.io.*; import java.awt.*; import java.awt.image.BufferedImage; import javax.imageio.ImageIO; import javax.swing.ImageIcon; import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; /** * PROCESSING3.0 SQLIte INSERT Sample * @auther MSLABO * @version 2019/08 1.0 */ Connection con = null; DateTimeFormatter dtf = null; PImage pImg; void setup(){ size(300,300); //日付書式を指定 dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss.SSS"); //DBファイルはスケッチフォルダに生成する String dbName = sketchPath("test.db"); //DBをOPENする dbOpen( dbName ); //テーブルを生成する createTable(); //データを書き込む BufferedImage imageData = readFile( "girl.png" ); addImage( imageData ); //検証用に読み込む pImg = readImage(); //DB CLOSE dbClose(); } /** * 検証用レコード読み込み処理 */ PImage readImage(){ try{ String sql = "SELECT _DATETIME, _IMAGE, _ASPECT FROM TEST"; Statement stm = con.createStatement(); ResultSet rs = stm.executeQuery( sql ); String dateTime = ""; byte[] bits = null; double aspect = 0d; while (rs.next()) { dateTime = rs.getString("_DATETIME"); bits = rs.getBytes("_IMAGE"); aspect = rs.getDouble("_ASPECT"); } rs.close(); stm.close(); PImage p5Image = null; if( bits != null ){ println("----------画像読み込み----------"); println("日付：" + dateTime ); println("アスペクト比：" + aspect ); println("サイズ：" + bits.length ); //byte[] -> BufferedImage変換 InputStream is = new ByteArrayInputStream( bits ); BufferedImage bi = ImageIO.read(is); p5Image = new PImage( bi ); } return p5Image; } catch( SQLException e ){ e.printStackTrace(); return null; } catch( IOException e ){ e.printStackTrace(); return null; } } /** * 画像データINSERT処理 * @param 対象画像データ */ void addImage( BufferedImage imageData ){ //現在日時を取得 LocalDateTime ldt = LocalDateTime.now(); String dateTime = ldt.format( dtf ); //SQL文の雛形 String sql = "INSERT INTO TEST( _DATETIME, _IMAGE, _ASPECT) " + "VALUES( ?, ?, ? )"; try{ //BufferedImage -> byte[]変換 ByteArrayOutputStream outPutStream = new ByteArrayOutputStream(); ImageIO.write( imageData, "png", outPutStream ); byte[] byteData = outPutStream.toByteArray(); //日付、画像、アスペクト比を書き込む PreparedStatement pstm = con.prepareStatement( sql ); pstm.setString( 1, dateTime ); pstm.setBytes( 2, byteData ); pstm.setDouble( 3, imageData.getHeight()/imageData.getWidth() ); pstm.executeUpdate(); pstm.close(); } catch( SQLException e ){ e.printStackTrace(); } catch( IOException e ){ e.printStackTrace(); } } /** * 画像データ取得処理 * @param file 対象ファイル名 * @return イメージデータ */ BufferedImage readFile( String file ) { try{ //ファイルからBufferedImageを得る File f = new File( dataPath(file) ); FileInputStream fis = new FileInputStream( f ); BufferedImage readImage = ImageIO.read( fis ); return readImage; } catch (FileNotFoundException e) { e.printStackTrace(); return null; } catch (IOException e) { e.printStackTrace(); return null; } } void draw(){ background( pImg ); } /** * CREATE TABLE処理 */ void createTable(){ //SQL文作成 String sql = "CREATE TABLE IF NOT EXISTS TEST( " + "_id INTEGER PRIMARY KEY," + "_datetime TEXT," + "_image BLOB DEFAULT NULL ," + "_aspect REAL DEFAULT 1.0 CHECK( _aspect > 0 ) )"; //テーブルを作成する try { Statement stm = con.createStatement(); stm.executeUpdate( sql ); stm.close(); } catch ( SQLException e ) { e.printStackTrace(); } } /** * DB OPEN処理. * @param dbName : DB名 */ void dbOpen( String dbName ){ try{ //JDBCドライバを明示的にロードする Class.forName("org.sqlite.JDBC"); //DBをOPENする con = DriverManager.getConnection( "jdbc:sqlite:" + dbName ); } catch( ClassNotFoundException e){ e.printStackTrace(); } catch ( SQLException e ) { e.printStackTrace(); } } /** * DB CLOSE処理 */ void dbClose(){ try { if( con != null ) { //DBをクローズする con.close(); con = null; } } catch ( SQLException e ) { e.printStackTrace(); } } |
dataフォルダ配下に girl.png という画像データがある前提です。
画像データを読み込んで、DBにINSERTします。INSERT後に、検証のためにSELECTしていますが、検索処理については別記事で紹介させてもらいます。
サンプルなので、何度も実行すると同じ画像データが何度もINSERTされてしまいますが、そこは必要に応じて改変してください。
＜出力サンプル＞
（画像URL：illust-AC 様：えふ3776さん）
本ページで利用しているアイコン画像は、下記サイト様より拝借しております。各画像の著作権は、それぞれのサイト様および作者にあります。