Java 學習記錄123 — Result Set Meta Data

承接 — 122 — Query Artists for Song method

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();

    }
}

輸出結果：

Connect to music.db success!

SQL statement = 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 songs.title = “Heartless” ORDER BY artists.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);

輸出結果：

Connect to music.db success!

SQL statement = 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 songs.title = “She’s On Fire” ORDER BY artists.name,albums.name COLLATE NOCASE ASC

SongArtist{artistName = Aerosmith, albumName = Done With Mirrors, track = 7}

List<SongArtist> songArtists = dataSource.queryArtistsForSong("Go Your Own Way", Datasource.SortOrder.ORDER_BY_ASC);

輸出結果：

Connect to music.db success!

SQL statement = 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 songs.title = “Go Your Own Way” ORDER BY artists.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();

輸出結果：

Connect to music.db success!

Column 1 in the songs table is name _id

Column 2 in the songs table is name track

Column 3 in the songs table is name title

Column 4 in the songs table is name album

用 JDBC 實現了 schema 的功能

查詢表的各個 col 名稱為何

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