SQLlite:検索

検索対象のデータベース

テーブルのデータ表示
ResultSet rs = stmt.executeQuery( "select * from data" );

表示：

Class.forName("org.sqlite.JDBC");
Connection connection = null;
// データベースに接続 なければ作成される
String databaseFullPass = "jdbc:sqlite:" + "E:/yamato/tvData/test.db";

connection = DriverManager.getConnection( databaseFullPass );
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery( "select * from data");

while( rs.next() ) {
    System.out.println("id = " + rs.getInt("id"));
    System.out.println("date = " + rs.getString("date"));
    System.out.println("name = " + rs.getString("name"));
　　System.out.println("price = " + rs.getInt("price"));
}

結果：
id = 1
date = 5/25
name = りんご
price = 100
id = 2
date = 5/24
name = いちご
price = 110
id = 3
date = 5/23
name = りんご
price = 101

WHERE句の使い方
ResultSet rs = stmt.executeQuery("SELECT * FROM data WHERE name = 'りんご'");

結果：
id = 1
date = 5/25
name = りんご
price = 100
id = 3
date = 5/23
name = りんご
price = 101

WHERE句+AND
ResultSet rs = stmt.executeQuery("SELECT * FROM data WHERE name = 'りんご' AND price = '101'");

結果：
id = 3
date = 5/23
name = りんご
price = 101

WHERE句+OR
ResultSet rs = stmt.executeQuery("SELECT * FROM data WHERE name = 'りんご' OR price = '110'");

結果：
id = 1
date = 5/25
name = りんご
price = 100
id = 2
date = 5/24
name = いちご
price = 110
id = 3
date = 5/23
name = りんご
price = 101

WHERE句+NOT
ResultSet rs = stmt.executeQuery("SELECT * FROM data WHERE NOT name = 'りんご' ");

結果：
id = 2
date = 5/24
name = いちご
price = 110

WHERE句+比較演算子
ResultSet rs = stmt.executeQuery("SELECT * FROM data WHERE name = 'りんご' AND price = '100'  ");

priceが50以上101未満のデータを抽出
priceのデータ型がINTEGERでないと範囲指定できない。
ResultSet rs = stmt.executeQuery("SELECT * FROM data WHERE name = 'りんご' AND price > 50 and price < 101 ");

結果：
id = 1
date = 5/25
name = りんご
price = 100

WHERE句+BETWEEN条件
ResultSet rs = stmt.executeQuery("SELECT * FROM data WHERE price BETWEEN 50 and 100 ");

結果：
id = 1
date = 5/25
name = りんご
price = 100

IN条件
指定した値のリストにマッチするか
ResultSet rs = stmt.executeQuery("SELECT * FROM data WHERE price IN ( 100 , 110) ");

結果：
id = 1
date = 5/25
name = りんご
price = 100
id = 2
date = 5/24
name = いちご
price = 110

LIKE条件
文字の検索条件を指定する。
%は「任意の文字数の任意の文字」
_は「1文字の任意の文字」,_は一文字にマッチ

__ ２文字の任意の文字列
a__b aで始まりbで終わる4文字の文字列

例：
name LIKE 'a%'は、nameがaで始まる任意の長さの文字列
name LIKE 'a%b'は、nameがaで始まりbで終わる任意の長さの文字列

name LIKE 'a_'は、nameがaで始まる2文字の文字列
name LIKE 'a_%b'は、nameが aで始まりbで終わる3文字以上の任意の長さの文字列

ResultSet rs = stmt.executeQuery("SELECT * FROM data WHERE name LIKE 'り%' ");

結果：
id = 1
date = 5/25
name = りんご
price = 100
id = 3
date = 5/23
name = りんご
price = 101