Java 學習記錄119 — Write Java Query for Artists

承接 118 — The Music SQLite Database

Album.java

package musicDB.model;
public class Album {
    private int id;
    private String name;
    private int artistId;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getArtistId() {
        return artistId;
    }
    public void setArtistId(int artistId) {
        this.artistId = artistId;
    }
}

Artist.java

package musicDB.model;
public class Artist {
    private int id;
    private String name;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "Artist{" +
                " ID = " + id +
                ", Name = " + name +
                '}';
    }
}

Song.java

package musicDB.model;
public class Song {
    private int id;
    private int track;
    private String name;
    private int albumId;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getTrack() {
        return track;
    }
    public void setTrack(int track) {
        this.track = track;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAlbumId() {
        return albumId;
    }
    public void setAlbumId(int albumId) {
        this.albumId = albumId;
    }
}

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 String TABLE_ARTISTS = "artists";
    public static final String COLUMN_ARTIST_ID = "_id";
    public static final String COLUMN_ARTIST_NAME = "name";
    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";
    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() {
//        // close by manual
//        Statement statement = null;
//        ResultSet resultSet = null;
//
//        try {
//            statement = conn.createStatement();
//            resultSet = statement.executeQuery("SELECT * FROM " + TABLE_ARTISTS);
//
//            List<Artist> artists = new ArrayList<>();
//            while (resultSet.next()) {
//                Artist artist = new Artist();
//                artist.setId(resultSet.getInt(COLUMN_ARTIST_ID));
//                artist.setName(resultSet.getString(COLUMN_ARTIST_NAME));
//                artists.add(artist);
//            }
//            return artists;
//        } catch (SQLException e) {
//            System.out.println("Query failed: " + e.getMessage());
//            return null;
//        } finally {
//            try {
//                if (resultSet != null) {
//                    resultSet.close();
//                }
//            } catch (SQLException e) {
//                System.out.println("Error close ResultSet: " + e.getMessage());
//
//            }
//
//            try {
//                if (statement != null) {
//                    statement.close();
//                }
//            } catch (SQLException e) {
//                System.out.println("Error close Statement: " + e.getMessage());
//            }
//        }
        // close by auto
        try (Statement statement = conn.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT * FROM " + TABLE_ARTISTS);) {
            List<Artist> artists = new ArrayList<>();
            while (resultSet.next()) {
                Artist artist = new Artist();
                artist.setId(resultSet.getInt(COLUMN_ARTIST_ID));
                artist.setName(resultSet.getString(COLUMN_ARTIST_NAME));
                artists.add(artist);
            }
            return artists;
        } 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 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();
        if (artists == null) {
            System.out.println("No artists!");
            return;
        }
        for (Artist artist : artists) {
            System.out.println(artist);
        }
        dataSource.close();

    }
}

輸出結果：

Connect to music.db success!

Artist{ ID = 1, Name = Mahogany Rush}

Artist{ ID = 2, Name = Elf}

Artist{ ID = 3, Name = One Kitten}

（省略）

Artist{ ID = 200, Name = Stevie Ray Vaughan}

Artist{ ID = 201, Name = Chemical Brothers}

Artist{ ID = 202, Name = Beyonce}

用 Java 查詢 Artist 裡面的所有資料

一個 table 就創對應的 class

把 resultset 查到的資料丟入 List 裡面，可以減少以後換不同資料庫的變動

以後可能從 csv 或 txt 或其他地方獲取資料，若有換只要來 Datasource.java 裡面修改即可

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