承接 — 122 — Метод запроса исполнителей для песни
SongArtist.java
package musicDB.model;
public class SongArtist {
private String artistName; private String albumName; private int track;
public String getArtistName() { return artistName; }
public void setArtistName(String artistName) { this.artistName = artistName; }
public String getAlbumName() { return albumName; }
public void setAlbumName(String albumName) { this.albumName = albumName; }
public int getTrack() { return track; }
public void setTrack(int track) { this.track = track; }
@Override public String toString() { return "SongArtist{" + "artistName = " + artistName + ", albumName = " + albumName + ", track = " + track + '}'; } }
Источник 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 " + 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; } } }
更新 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("Heartless", 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.close();
} }
輸出結果:
Успешное подключение к music.db!
Выражение SQL = ВЫБРАТЬ Artists.name, Albums.Name, Songs.track FROM song ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ альбомов ON Songs.album = Albums._id ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ исполнителей ON Albums.artist = Artists._id ГДЕ song.title = «Бессердечный» ORDER BY artist.name,albums.name COLLATE NOCASE ASC
SongArtist{artistName = Heart, AlbumName = Dog & Butterfly, track = 9}
新增了一個查詢語句 queryArtistsForSong()
也新增了對應的 class
這查詢語句拼了另外兩張表,讓使用者透過搜索歌名就能出現相關資料
List<SongArtist> songArtists = dataSource.queryArtistsForSong("She's On Fire", Datasource.SortOrder.ORDER_BY_ASC);
輸出結果:
Успешное подключение к music.db!
Выражение SQL = ВЫБРАТЬ Artists.name, Albums.Name, Songs.track FROM song ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ альбомов ON song.album = Albums._id ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ исполнителей ON Albums.artist = Artists._id ГДЕ song.title = «She's On» Огонь» ORDER BY artist.name,albums.name COLLATE NOCASE ASC
SongArtist{artistName = Aerosmith, AlbumName = Done With Mirrors, трек = 7}
List<SongArtist> songArtists = dataSource.queryArtistsForSong("Go Your Own Way", Datasource.SortOrder.ORDER_BY_ASC);
輸出結果:
Успешное подключение к music.db!
Выражение SQL = ВЫБЕРИТЕ Artists.name, Albums.Name, Songs.Track ИЗ песен ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ альбомов ON Songs.album = Albums._id ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ исполнителей ON Albums.artist = Artists._id ГДЕ song.title = «Go Your СОБСТВЕННЫЙ ПУТЬ» ЗАКАЗАТЬ ПО artist.name,albums.name COLLATE NOCASE ASC
SongArtist{artistName = Fleetwood Mac, AlbumName = Greatest Hits, track = 2}
SongArtist{artistName = Fleetwood Mac, AlbumName = Rumours, track = 5}
SongArtist{artistName = Fleetwood Mac, AlbumName = The Dance, track = 15}
SongArtist{artistName = Fleetwood Mac, AlbumName = The Very Best Of, track = 1}
補充:
搜尋歌名時,有時要打大小寫很麻煩
SELECT * FROM songs WHERE UPPER(songs.title) = "GO YOUR OWN WAY";
加上這個關鍵字,就可以只用全大寫或全小寫
小寫就在 WHERE
後面加 LOWER
記得等號後面也要全大寫或小寫
Источник Datasource.java
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()); } }
更新 Main.java
dataSource.querySongsMetadata();
輸出結果:
Успешное подключение к music.db!
Столбец 1 в таблице песен имеет имя _id
Столбец 2 в таблице песен – название трека
Столбец 3 в таблице песен — название названия
Столбец 4 в таблице песен — название альбома
Схема JDBC 實現了 的功能
查詢表的各個 col 名稱為何