Java 學習記錄122 — Query Artists for Song method

承接 — 121 — Query Albums by Artist Method

更新 Main.java

package musicDB;

import musicDB.model.Artist;
import musicDB.model.Datasource;
import javax.swing.*;
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("Pink Floyd", Datasource.SortOrder.ORDER_BY_DESC);
        for (String album : albumsForArtist) {
            System.out.println(album);
        }
        dataSource.close();

    }
}

輸出結果：

Connect to music.db success!

SQL statement = SELECT albums.name FROM albums INNER JOIN artists ON albums.artist = artists._id WHERE artists.name = “Pink Floyd” ORDER BY albums.name COLLATE NOCASE DESC

Wish You Were Here

Winter Tour 74

Ummagumma

The Wall

The Division Bell

Relics

Pulse

Obscured By Clouds

Meddle

Dark Side Of The Moon

Atom Heart Mother

A Saucerful Of Secrets

更新 Main.java

List<String> albumsForArtist = dataSource.queryAlbumsForArtist("Carole King", Datasource.SortOrder.ORDER_BY_ASC);

輸出結果：

Connect to music.db success!

SQL statement = SELECT albums.name FROM albums INNER JOIN artists ON albums.artist = artists._id WHERE artists.name = “Carole King” ORDER BY albums.name COLLATE NOCASE ASC

Tapestry

打開 SQLite 來測試 sql 語句

輸入：

SELECT * FROM songs WHERE title = "Go Your Own Way"

輸出：

2186 5 Go Your Own Way 416

3418 2 Go Your Own Way 238

3844 1 Go Your Own Way 232

4028 15 Go Your Own Way 41

輸入：

SELECT * FROM albums WHERE _id = 416

輸出：

416 Rumours 92

輸入：

SELECT * FROM artists WHERE _id = 92

輸出：

92 Fleetwood Mac

輸入：

SELECT artists.name, albums.name, songs.track FROM songs
INNER JOIN albums ON songs.album = albums._id
INNER JOIN artists ON albums.artist = artists._id
WHERE title = "Go Your Own Way"
ORDER BY artists.name, albums.name
COLLATE NOCASE ASC

輸出：

Fleetwood Mac Greatest Hits 2

Fleetwood Mac Rumours 5

Fleetwood Mac The Dance 15

Fleetwood Mac The Very Best Of 1

上面代碼全都紀錄在我的 Github