承接 — 123 — Result Set Meta Data
更新 Datasource.java
package musicDB.model;import java.sql.*;
import java.util.ArrayList;
import java.util.List;public class Datasource {
public static final String DB_Name = "music.db";
public static final String CONNECTION_STRING = "jdbc:sqlite:C:\\software\\JetBrains\\IdeaProjects\\java-the-complete-java-developer-course\\chapter19\\src\\musicDB\\" + DB_Name; public static final String TABLE_ALBUMS = "albums";
public static final String COLUMN_ALBUM_ID = "_id";
public static final String COLUMN_ALBUM_NAME = "name";
public static final String COLUMN_ALBUM_ARTIST = "artist";
public static final int INDEX_ALBUM_ID = 1;
public static final int INDEX_ALBUM_NAME = 2;
public static final int INDEX_ALBUM_ARTIST = 3; public static final String TABLE_ARTISTS = "artists";
public static final String COLUMN_ARTIST_ID = "_id";
public static final String COLUMN_ARTIST_NAME = "name";
public static final int INDEX_ARTIST_ID = 1;
public static final int INDEX_ARTIST_NAME = 2; public static final String TABLE_SONGS = "songs";
public static final String COLUMN_SONG_ID = "_id";
public static final String COLUMN_SONG_TRACK = "track";
public static final String COLUMN_SONG_TITLE = "title";
public static final String COLUMN_SONG_ALBUM = "album";
public static final int INDEX_SONG_ID = 1;
public static final int INDEX_SONG_TRACK = 2;
public static final int INDEX_SONG_TITLE = 3;
public static final int INDEX_SONG_ALBUM = 4; public enum SortOrder {
ORDER_BY_NONE,
ORDER_BY_ASC,
ORDER_BY_DESC;
} public static final String QUERY_ALBUMS_BY_ARTIST_START =
"SELECT " + TABLE_ALBUMS + "." + COLUMN_ALBUM_NAME + " FROM " + TABLE_ALBUMS +
" INNER JOIN " + TABLE_ARTISTS + " ON " + TABLE_ALBUMS + "." + COLUMN_ALBUM_ARTIST +
" = " + TABLE_ARTISTS + "." + COLUMN_ARTIST_ID +
" WHERE " + TABLE_ARTISTS + "." + COLUMN_ALBUM_NAME + " = \""; public static final String QUERY_ALBUMS_BY_ARTIST_SORT =
" ORDER BY " + TABLE_ALBUMS + "." + COLUMN_ALBUM_NAME + " COLLATE NOCASE ";
public static final String QUERY_ARTIST_FOR_SONG_START =
"SELECT " + TABLE_ARTISTS + "." + COLUMN_ALBUM_NAME + ", " +
TABLE_ALBUMS + "." + COLUMN_ALBUM_NAME + ", " +
TABLE_SONGS + "." + COLUMN_SONG_TRACK + " FROM " + TABLE_SONGS +
" INNER JOIN " + TABLE_ALBUMS + " ON " +
TABLE_SONGS + "." + COLUMN_SONG_ALBUM + " = " + TABLE_ALBUMS + "." + COLUMN_ALBUM_ID +
" INNER JOIN " + TABLE_ARTISTS + " ON " +
TABLE_ALBUMS + "." + COLUMN_ALBUM_ARTIST + " = " + TABLE_ARTISTS + "." + COLUMN_ALBUM_ID +
" WHERE LOWER (" + TABLE_SONGS + "." + COLUMN_SONG_TITLE + ") = \""; public static final String QUERY_ARTIST_FOR_SONG_SORT =
" ORDER BY " + TABLE_ARTISTS + "." + COLUMN_ARTIST_NAME + "," +
TABLE_ALBUMS + "." + COLUMN_ALBUM_NAME + " COLLATE NOCASE "; private Connection conn; public boolean open() {
try {
conn = DriverManager.getConnection(CONNECTION_STRING);
System.out.println("Connect to " + DB_Name + " success!");
return true; } catch (SQLException e) {
System.out.println("Couldn't connect ot database: " + e.getMessage());
return false;
}
} public void close() {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println("Couldn't close connection: " + e.getMessage());
}
} public List<Artist> queryArtists(SortOrder sortOrder) { StringBuilder stringBuilder = new StringBuilder("SELECT * FROM ");
stringBuilder.append(TABLE_ARTISTS);
if (sortOrder != SortOrder.ORDER_BY_NONE) {
stringBuilder.append(" ORDER BY ");
stringBuilder.append(COLUMN_ARTIST_NAME);
stringBuilder.append(" COLLATE NOCASE ");
if (sortOrder == SortOrder.ORDER_BY_DESC) {
stringBuilder.append("DESC");
} else {
stringBuilder.append("ASC");
}
}
try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(stringBuilder.toString())) { List<Artist> artists = new ArrayList<>();
while (resultSet.next()) {
Artist artist = new Artist();
artist.setId(resultSet.getInt(INDEX_ARTIST_ID));
artist.setName(resultSet.getString(INDEX_ARTIST_NAME));
artists.add(artist);
}
return artists;
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
return null;
}
} public List<String> queryAlbumsForArtist(String artistName, SortOrder sortOrder) { // SELECT albums.name FROM albums INNER JOIN artists ON albums.artist = artists._id WHERE artists.name = "Iron Maiden" ORDER BY albums.name COLLATE NOCASE ASC
StringBuilder stringBuilder = new StringBuilder(QUERY_ALBUMS_BY_ARTIST_START);
stringBuilder.append(artistName);
stringBuilder.append("\""); if (sortOrder != SortOrder.ORDER_BY_NONE) {
stringBuilder.append(QUERY_ALBUMS_BY_ARTIST_SORT);
if (sortOrder == SortOrder.ORDER_BY_DESC) {
stringBuilder.append("DESC");
} else {
stringBuilder.append("ASC");
}
} System.out.println("SQL statement = " + stringBuilder.toString()); try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(stringBuilder.toString())) {
List<String> albums = new ArrayList<>();
while (resultSet.next()) {
albums.add(resultSet.getString(1));
}
return albums;
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
return null;
}
} public List<SongArtist> queryArtistsForSong(String songName, SortOrder sortOrder) {
StringBuilder stringBuilder = new StringBuilder(QUERY_ARTIST_FOR_SONG_START);
stringBuilder.append(songName);
stringBuilder.append("\""); if (sortOrder != SortOrder.ORDER_BY_NONE) {
stringBuilder.append(QUERY_ARTIST_FOR_SONG_SORT);
if (sortOrder == SortOrder.ORDER_BY_DESC) {
stringBuilder.append("DESC");
} else {
stringBuilder.append("ASC");
}
} System.out.println("SQL statement = " + stringBuilder.toString()); try (Statement statement = conn.createStatement();
ResultSet results = statement.executeQuery(stringBuilder.toString())) { List<SongArtist> songArtists = new ArrayList<>();
while (results.next()) {
SongArtist songArtist = new SongArtist();
songArtist.setArtistName(results.getString(1));
songArtist.setAlbumName(results.getString(2));
songArtist.setTrack(results.getInt(3));
songArtists.add(songArtist);
}
return songArtists;
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
return null;
}
} public void querySongsMetadata() {
String sql = "SELECT * FROM " + TABLE_SONGS; try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql)) {
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
int numColumn = resultSetMetaData.getColumnCount();
for (int i = 1; i <= numColumn; i++) {
System.out.format("Column %d in the songs table is name %s\n",
i, resultSetMetaData.getColumnName(i));
}
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
}
} public int getCount(String tableName) {
String sql = "SELECT COUNT(*) FROM " + tableName;
try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);) { return resultSet.getInt(1);
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
return -1;
}
}}
更新 Main.java
package musicDB;
import musicDB.model.Artist;
import musicDB.model.Datasource;
import musicDB.model.SongArtist;import javax.swing.*;
import java.util.ArrayList;
import java.util.List;
public class Main {
public static void main(String[] args) {
Datasource dataSource = new Datasource();
if (!dataSource.open()) {
System.out.println("Can't open datasource");
return;
}// List<Artist> artists = dataSource.queryArtists(Datasource.SortOrder.ORDER_BY_ASC);
// if (artists == null) {
// System.out.println("No artists!");
// return;
// }// for (Artist artist : artists) {
// System.out.println(artist);
// }// List<String> albumsForArtist = dataSource.queryAlbumsForArtist("Carole King", Datasource.SortOrder.ORDER_BY_ASC);// for (String album : albumsForArtist) {
// System.out.println(album);
// }// List<SongArtist> songArtists = dataSource.queryArtistsForSong("go Your Own Way", Datasource.SortOrder.ORDER_BY_ASC);
//
// if (songArtists == null) {
// System.out.println("Couldn't find the artist for the song");
// return;
// }
//
// for (SongArtist artist : songArtists) {
// System.out.println(artist);
// }// dataSource.querySongsMetadata();
int count = dataSource.getCount(Datasource.TABLE_SONGS);
System.out.println("Number of songs is: " + count); dataSource.close();
}
}
輸出結果：
Connect to music.db success!
Number of songs is: 5350
做了一個查詢資料筆數的 getCount Method
更新 Datasource.java
public int getCount(String tableName) {
String sql = "SELECT COUNT(*), MIN(_ID) FROM " + tableName;
try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);) {
int count = resultSet.getInt(1);
int min = resultSet.getInt(2);
System.out.format("Count = %d, Min = %d\n", count, min);
return count;
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
return -1;
}
}輸出結果：
Connect to music.db success!
Count = 5350, Min = 1
Number of songs is: 5350
拓展了一點功能，順便查最小的ID是多少
更新 Datasource.java
public int getCount(String tableName) {
String sql = "SELECT COUNT(*) AS count, MIN(_ID) AS min_id FROM " + tableName;
try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);) {
int count = resultSet.getInt("count");
int min = resultSet.getInt("min_id");
System.out.format("Count = %d, Min = %d\n", count, min);
return count;
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
return -1;
}
}輸出結果：
Connect to music.db success!
Count = 5350, Min = 1
Number of songs is: 5350
有點像變量，賦予名子的意思
寫成 JAVA 的話，有點像這種感覺 count = COUNT(*), min_id = MIN(_ID)
我比較訝異的是 resultSet.getInt() 那邊不用改成 int 居然直接用 string 也能通
更新 Datasource.java
public static final String TABLE_ARTIST_SONG_VIEW = "artist_list";
public static final String CREATE_ARTIST_FOR_SONG_VIEW = "CREATE VIEW IF NOT EXISTS " +
TABLE_ARTIST_SONG_VIEW + " AS SELECT " + TABLE_ARTISTS + "." + COLUMN_ARTIST_NAME + ", " +
TABLE_ALBUMS + "." + COLUMN_ALBUM_NAME + " AS " + COLUMN_SONG_ALBUM + ", " +
TABLE_SONGS + "." + COLUMN_SONG_TRACK + ", " + TABLE_SONGS + "." + COLUMN_SONG_TITLE +
" FROM " + TABLE_SONGS +
" INNER JOIN " + TABLE_ALBUMS + " ON " + TABLE_SONGS +
"." + COLUMN_SONG_ALBUM + " = " + TABLE_ALBUMS + "." + COLUMN_ALBUM_ID +
" INNER JOIN " + TABLE_ARTISTS + " ON " + TABLE_ALBUMS + "." + COLUMN_ALBUM_ARTIST +
" = " + TABLE_ARTISTS + "." + COLUMN_ARTIST_ID +
" ORDER BY " +
TABLE_ARTISTS + "." + COLUMN_ARTIST_NAME + ", " +
TABLE_ALBUMS + "." + COLUMN_ALBUM_NAME + ", " +
TABLE_SONGS + "." + COLUMN_SONG_TRACK;public boolean createViewForSongArtists() {
try (Statement statement = conn.createStatement()) {
statement.execute(CREATE_ARTIST_FOR_SONG_VIEW);
System.out.println("View of " + TABLE_ARTIST_SONG_VIEW + " created success!");
return true; } catch (SQLException e) {
System.out.println("Create View failed: " + e.getMessage());
return false;
}
}
更新 Main.java
dataSource.createViewForSongArtists();
輸出結果：
Connect to music.db success!
View of artist_list created success!
用 JDBC 實現創建 View 的功能
打開 SQLite 可以看到多了一個視圖（View）名為 artist_list