Java 學習記錄121 — Query Albums by Artist Method

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;
}
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("SELECT ");
stringBuilder.append(TABLE_ALBUMS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ALBUM_NAME);
stringBuilder.append(" FROM ");
stringBuilder.append(TABLE_ALBUMS);
stringBuilder.append(" INNER JOIN ");
stringBuilder.append(TABLE_ARTISTS);
stringBuilder.append(" ON ");
stringBuilder.append(TABLE_ALBUMS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ALBUM_ARTIST);
stringBuilder.append(" = ");
stringBuilder.append(TABLE_ARTISTS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ARTIST_ID);
stringBuilder.append(" WHERE ");
stringBuilder.append(TABLE_ARTISTS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ARTIST_NAME);
stringBuilder.append(" = \"");
stringBuilder.append(artistName);
stringBuilder.append("\"");
if (sortOrder != SortOrder.ORDER_BY_NONE) {
stringBuilder.append(" ORDER BY ");
stringBuilder.append(TABLE_ALBUMS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ALBUM_NAME);
stringBuilder.append(" COLLATE NOCASE ");
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;
}
}}
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;
}
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("SELECT ");
stringBuilder.append(TABLE_ALBUMS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ALBUM_NAME);
stringBuilder.append(" FROM ");
stringBuilder.append(TABLE_ALBUMS);
stringBuilder.append(" INNER JOIN ");
stringBuilder.append(TABLE_ARTISTS);
stringBuilder.append(" ON ");
stringBuilder.append(TABLE_ALBUMS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ALBUM_ARTIST);
stringBuilder.append(" = ");
stringBuilder.append(TABLE_ARTISTS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ARTIST_ID);
stringBuilder.append(" WHERE ");
stringBuilder.append(TABLE_ARTISTS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ARTIST_NAME);
stringBuilder.append(" = \"");
stringBuilder.append(artistName);
stringBuilder.append("\"");
if (sortOrder != SortOrder.ORDER_BY_NONE) {
stringBuilder.append(" ORDER BY ");
stringBuilder.append(TABLE_ALBUMS);
stringBuilder.append(".");
stringBuilder.append(COLUMN_ALBUM_NAME);
stringBuilder.append(" COLLATE NOCASE ");
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;
}
}}

Connect to music.db success!

SQL statement = 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

Powerslave

Seventh Son Of A Seventh Son

The Number of the Beast

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 ";
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;
}
}}

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store