承接 — 124 — Functions and Views
更新 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 "; 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 static final String QUERY_VIEW_SONG_INFO = " SELECT " + COLUMN_ARTIST_NAME + ", " +
COLUMN_SONG_ALBUM + ", " + COLUMN_SONG_TRACK + " FROM " + TABLE_ARTIST_SONG_VIEW +
" WHERE LOWER (" + COLUMN_SONG_TITLE + ") = \"";
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(*) AS count FROM " + tableName;
try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(sql);) { return resultSet.getInt("count");
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
return -1;
}
} 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;
}
} public List<SongArtist> querySongInfoView(String song_title) {
StringBuilder stringBuilder = new StringBuilder(QUERY_VIEW_SONG_INFO);
stringBuilder.append(song_title);
stringBuilder.append("\""); System.out.println(stringBuilder); try (Statement statement = conn.createStatement();
ResultSet resultSet = statement.executeQuery(stringBuilder.toString())) { List<SongArtist> songArtists = new ArrayList<>();
while (resultSet.next()) {
SongArtist songArtist = new SongArtist();
songArtist.setArtistName(resultSet.getString(1));
songArtist.setAlbumName(resultSet.getString(2));
songArtist.setTrack(resultSet.getInt(3));
songArtists.add(songArtist);
}
return songArtists;
} catch (SQLException e) {
System.out.println("Query failed: " + e.getMessage());
return null;
}
}}
更新 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.createViewForSongArtists(); List<SongArtist> songArtists2 = dataSource.querySongInfoView("go your own way");
if (songArtists2.isEmpty()) {
System.out.println("Couldn't find the artist for the song");
return;
} for (SongArtist artist : songArtists2) {
System.out.println("FROM VIEW - " + artist);
} dataSource.close();
}
}
輸出結果：
Connect to music.db success!
SELECT name, album, track FROM artist_list WHERE LOWER (title) = “go your own way”
FROM VIEW — SongArtist{artistName = Fleetwood Mac, albumName = Greatest Hits, track = 2}
FROM VIEW — SongArtist{artistName = Fleetwood Mac, albumName = Rumours, track = 5}
FROM VIEW — SongArtist{artistName = Fleetwood Mac, albumName = The Dance, track = 15}
FROM VIEW — SongArtist{artistName = Fleetwood Mac, albumName = The Very Best Of, track = 1}
上一篇創建了 View
現在則是新增 querySongInfoView() 透過創好的 View 來查詢
我對搜尋關鍵字做了小寫的處理，歌名的參數只要小寫就能查了
其他歌名查詢
List<SongArtist> songArtists = dataSource.querySongInfoView("heartless");
輸出結果：
Connect to music.db success!
SELECT name, album, track FROM artist_list WHERE LOWER (title) = “heartless”
FROM VIEW — SongArtist{artistName = Heart, albumName = Dog & Butterfly, track = 9}
List<SongArtist> songArtists = dataSource.querySongInfoView("she's on fire");
輸出結果：
Connect to music.db success!
SELECT name, album, track FROM artist_list WHERE LOWER (title) = “she’s on fire”
FROM VIEW — SongArtist{artistName = Aerosmith, albumName = Done With Mirrors, track = 7}