SQLiteを使う（検索）

◆PROCESSING　逆引きリファレンス

　カテゴリー：ファイル操作

【概要】

PROCESSINGはJavaをベースにした言語ですので、Javaと同じ手順でSQLiteを利用することが可能です。

SQLiteの利用準備とDBのOPEN/CLOSE処理については「SQLiteを使う（オープン・クローズ）」記事で紹介しました。

今回はOPEN（生成）したDBファイルからデータを検索する処理を紹介したいと思います。

なお表の作成については「SQLiteを使う（テーブル生成と削除）」記事を参照してください。

またデータを表に追加（挿入）する手順については「SQLiteを使う（データ挿入と削除）」記事を参照してください。

【詳細】

データを検索する

SQLといえばコレと言いたくなるほど、検索処理はメジャーですよね。表からデータを検索するには、SQLのSELECT文を利用します。

SELECT 文は、基本的には以下のような形式で記述します。

SELECT カラム名1, カラム名2, ...
FROM テーブル名
WHERE 取得条件 ;

SELECT カラム名1, カラム名2, ...

FROM テーブル名

WHERE 取得条件 ;

上記を基本形とし、データの並び変え（ORDER BY）を指定したり、データをグループとしてまとめて取得する（GROUP BY）ことが可能です。

SELECT文はデータをどのように集めたいかによって、いろいろな記述が可能ですので、詳しくは他の解説サイト様などを参照してください。

多くの場合、表に１レコード（１行）しかデータが無いということは稀だと思います。ある検索処理（SQL文）では、複数件のデータが取得できるのが一般的でしょう。

表から複数件のデータを集めてくることを「カーソルを組む」と言いますが、カーソルの処理にはResultSetクラスを利用します。

ResultSetクラスのインスタンスは、PreparedStatementまたはStatementクラスに対してexecuteQuery()メソッドでSQL文を発行した際に戻ってくるものを利用します。

検索結果を得るResultSet rs = stm.executeQuery( String sql );

rs : 検索結果
stm : Statementクラス
sql : 検索用SQL文

例えば以下のような感じです。

String sql = "SELECT _DATETIME, _IMAGE, _NAME FROM TEST ";
sql = sql + "WHERE _NAME = 'JK'";

Statement  stm = con.createStatement();
ResultSet rs = stm.executeQuery( sql );

String sql = "SELECT _DATETIME, _IMAGE, _NAME FROM TEST ";

sql = sql + "WHERE _NAME = 'JK'";

Statement stm = con.createStatement();

ResultSet rs = stm.executeQuery( sql );

上記のSQL文では、TEST表から_NAME列に ‘JK’ という文字があるレコードを探しています。

検索結果は、executeQuery()メソッドで得られたResultSetから取得することが可能です。

ResultSetが持つ代表的なメソッドには、以下のようなものがあります。

№	メソッド	説明
1	boolean next();	カーソル（検索結果）から、データの取得位置を１つ進めます。これ以上データがない場合は false が戻されます。
2	int getRow();	現在の行番号（データ取得位置）を取得します。
3	String getString(int columnIndex);	columnIndex番目の列から文字列を取得します。最初の列は1番から始まります。
4	int getInt(int columnIndex);	columnIndex番目の列からint値を取得します。最初の列は1番から始まります。
5	byte getByte(int columnIndex);	columnIndex番目の列からbyte値を取得します。最初の列は1番から始まります。
6	float getFloat(int columnIndex);	columnIndex番目の列からfloat値を取得します。最初の列は1番から始まります。
7	void close();	ResultSetを開放します。

基本はnext()メソッドが true を返す間、検索結果を取得するループ処理を作ってデータを取得し、最後に close() する流れになります。

例えば以下のような感じです。

//検索する
Statement stm = con.createStatement();
ResultSet rs = stm.executeQuery( sql );
    
//結果を取得する
while (rs.next()) {
  dateTime = rs.getString("_DATETIME");
  bits = rs.getBytes("_IMAGE");
  title = rs.getString("_NAME");
  println(dateTime + " " + title );
}

//開放する
rs.close();
stm.close();

//検索する

Statement stm = con.createStatement();

ResultSet rs = stm.executeQuery( sql );

//結果を取得する

while (rs.next()) {

dateTime = rs.getString("_DATETIME");

bits = rs.getBytes("_IMAGE");

title = rs.getString("_NAME");

println(dateTime + " " + title );

}

//開放する

rs.close();

stm.close();

getString()やgetByte()は列位置を数値で指定することも可能ですが、上記のように名前で指定することも可能となっています。

【関連記事】

サンプルプログラム

検索を行う例：

import java.sql.*;
import java.io.*;
import java.awt.*;
import java.awt.image.BufferedImage;
import javax.imageio.ImageIO;
 
/**
 * PROCESSING3.0 SQLIte SELECT Sample
 * @auther MSLABO
 * @version 2019/10 1.0
 */
Connection con = null;
int  getIndex = 0;
SerchData  serchData;
PFont  pFont;

/**
 * 検索結果管理クラス 
 */
class SerchData {
  ArrayList<PImage> imageList; //画像
  ArrayList<String> titleList; //名前
  
  //コンストラクタ
  SerchData(){
    imageList = new ArrayList<PImage>(); 
    titleList = new ArrayList<String>(); 
  }  
  
  //格納件数取得処理
  int getCount(){
    return imageList.size();
  }
  
  //イメージ取得処理
  PImage getImage( int index ){
    if( getCount() > 0 && getCount() > index ){
      //indexの位置にある画像を返却する
      return imageList.get( index );
    }
    return null;
  }
  
  //名前取得処理
  String getName( int index ){
    if( getCount() > 0 && getCount() > index ){
      //indexの位置にある名前を返却する
      return titleList.get( index );
    }
    return null;
  }  
}
 
void setup(){ 
  size(250,500);
      
  //文字の準備
  pFont = loadFont("IPAGothic-48.vlw");
  textFont( pFont, 24 );
  textAlign( LEFT, TOP );
      
  //DBファイルはスケッチフォルダにある
  String dbName = sketchPath("test.db");
  
  //DBをOPENする
  dbOpen( dbName );
   
  //名前に[イ]を含むものを検索する
  serchData = readImage( "イ" );
  
  //DB CLOSE
  dbClose();
}
 
void draw(){
  background(128);
  //データが有効なら、getIndex番目のデータを
  //表示する
  if( serchData.getImage( getIndex ) != null ){
    image( serchData.getImage( getIndex ), 0, 0 );
    text( serchData.getName( getIndex ), 0, 0 );
  }
}

void mouseClicked(){
    //検索結果の表示位置を加算する
    getIndex++;
    if( getIndex >= serchData.getCount() ){
      //検索件数以上になったら初期値に戻す
      getIndex = 0;
    }
}

/**
 * 画像データ検索処理
 * @param keyWord 検索キーワード
 * @return 検索結果管理クラス
 */
SerchData readImage( String keyWord ){
  SerchData gData = new SerchData();
  PreparedStatement psm = null;
  
  try{
    //SQL文を組み立てる
    String sql = "SELECT _ID, _IMAGE, _NAME FROM TEST ";
    sql = sql + "WHERE _NAME like ?";  
    
    psm = con.prepareStatement( sql ); 
        
    //データを埋め込む
    psm.setString( 1, "%" + keyWord + "%" );
   
    //検索する
    ResultSet rs = psm.executeQuery();
    
    int id;
    byte[] bits = null;
    String title;
    
    //画像データ取得してListに詰める
    while (rs.next()) {
      id = rs.getInt(1);
      bits = rs.getBytes(2);
      title = rs.getString(3);
      
      println( "画像取得 ID=" + id + "/名前=" + title );
      
      //byte[] -> BufferedImage変換
      InputStream is = new ByteArrayInputStream( bits );
      BufferedImage bi = ImageIO.read(is);
      
      PImage p5Image = new PImage( bi ); 
      gData.imageList.add( p5Image );
      gData.titleList.add( title );
    }
    rs.close();
    psm.close();
    
    return gData; 
    
  } catch( SQLException e ){
    e.printStackTrace();
    return null;
  } catch( IOException e ){
    e.printStackTrace();
    return null;
  }
}
 
/**
 * 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();
  }
}

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

import java.sql.*;

import java.io.*;

import java.awt.*;

import java.awt.image.BufferedImage;

import javax.imageio.ImageIO;

/**

* PROCESSING3.0 SQLIte SELECT Sample

* @auther MSLABO

* @version 2019/10 1.0

Connection con = null;

int getIndex = 0;

SerchData serchData;

PFont pFont;

/**

* 検索結果管理クラス

class SerchData {

ArrayList<PImage> imageList; //画像

ArrayList<String> titleList; //名前

//コンストラクタ

SerchData(){

imageList = new ArrayList<PImage>();

titleList = new ArrayList<String>();

}

//格納件数取得処理

int getCount(){

return imageList.size();

}

//イメージ取得処理

PImage getImage( int index ){

if( getCount() > 0 && getCount() > index ){

//indexの位置にある画像を返却する

return imageList.get( index );

}

return null;

}

//名前取得処理

String getName( int index ){

if( getCount() > 0 && getCount() > index ){

//indexの位置にある名前を返却する

return titleList.get( index );

}

return null;

}

void setup(){

size(250,500);

//文字の準備

pFont = loadFont("IPAGothic-48.vlw");

textFont( pFont, 24 );

textAlign( LEFT, TOP );

//DBファイルはスケッチフォルダにある

String dbName = sketchPath("test.db");

//DBをOPENする

dbOpen( dbName );

//名前に[イ]を含むものを検索する

serchData = readImage( "イ" );

//DB CLOSE

dbClose();

}

void draw(){

background(128);

//データが有効なら、getIndex番目のデータを

//表示する

if( serchData.getImage( getIndex ) != null ){

image( serchData.getImage( getIndex ), 0, 0 );

text( serchData.getName( getIndex ), 0, 0 );

}

void mouseClicked(){

//検索結果の表示位置を加算する

getIndex++;

if( getIndex >= serchData.getCount() ){

//検索件数以上になったら初期値に戻す

getIndex = 0;

}

/**

* 画像データ検索処理

* @param keyWord 検索キーワード

* @return 検索結果管理クラス

SerchData readImage( String keyWord ){

SerchData gData = new SerchData();

PreparedStatement psm = null;

try{

//SQL文を組み立てる

String sql = "SELECT _ID, _IMAGE, _NAME FROM TEST ";

sql = sql + "WHERE _NAME like ?";

psm = con.prepareStatement( sql );

//データを埋め込む

psm.setString( 1, "%" + keyWord + "%" );

//検索する

ResultSet rs = psm.executeQuery();

int id;

byte[] bits = null;

String title;

//画像データ取得してListに詰める

while (rs.next()) {

id = rs.getInt(1);

bits = rs.getBytes(2);

title = rs.getString(3);

println( "画像取得 ID=" + id + "/名前=" + title );

//byte[] -> BufferedImage変換

InputStream is = new ByteArrayInputStream( bits );

BufferedImage bi = ImageIO.read(is);

PImage p5Image = new PImage( bi );

gData.imageList.add( p5Image );

gData.titleList.add( title );

}

rs.close();

psm.close();

return gData;

} catch( SQLException e ){

e.printStackTrace();

return null;

} catch( IOException e ){

e.printStackTrace();

return null;

}

/**

* 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();

}

上記は、あらかじめスケッチフォルダに以下のようなtest.dbというDBファイルが作成済みで、画像データが格納してある前提です。

サンプルで利用するTEST表

列名	型	説明
_id	INTEGER	主キー
_image	BLOB	バイナリデータ格納用。デフォルト値はNULL
_NAME	TEXT	画像の名前

格納済みのデータ

名前	絵柄
メイド
アイドル
女子高生
おねーさん

（画像URL：illust-AC 様：南見まめこさん）

この中から、名前に「イ」を含むものを検索して表示します。結果は「メイド」と「アイドル」の２つがヒットします。

＜出力サンプル＞

（画像URL：illust-AC 様：南見まめこさん）

PROCESSING逆引きリファレンス一覧へ戻る

本ページで利用しているアイコン画像は、下記サイト様より拝借しております。各画像の著作権は、それぞれのサイト様および作者にあります。

iconka.com 様
ICOOON MONO 様

◆PROCESSING 逆引きリファレンス

カテゴリー：ファイル操作