Java 學習記錄128 — Insert Albums, Artists, and Songs

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 LOWER (" + 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 ";
public static final String TABLE_ARTIST_SONG_VIEW = "artist_list";
public static final String CREATE_ARTIST_FOR_SONG_VIEW = "CREATE VIEW IF NOT EXISTS " +
TABLE_ARTIST_SONG_VIEW + " AS SELECT " + TABLE_ARTISTS + "." + COLUMN_ARTIST_NAME + ", " +
TABLE_ALBUMS + "." + COLUMN_ALBUM_NAME + " AS " + COLUMN_SONG_ALBUM + ", " +
TABLE_SONGS + "." + COLUMN_SONG_TRACK + ", " + TABLE_SONGS + "." + COLUMN_SONG_TITLE +
" 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_ARTIST_ID +
" ORDER BY " +
TABLE_ARTISTS + "." + COLUMN_ARTIST_NAME + ", " +
TABLE_ALBUMS + "." + COLUMN_ALBUM_NAME + ", " +
TABLE_SONGS + "." + COLUMN_SONG_TRACK;
public static final String QUERY_VIEW_SONG_INFO = " SELECT " + COLUMN_ARTIST_NAME + ", " +
COLUMN_SONG_ALBUM + ", " + COLUMN_SONG_TRACK + " FROM " + TABLE_ARTIST_SONG_VIEW +
" WHERE LOWER (" + COLUMN_SONG_TITLE + ") = \"";
public static final String QUERY_VIEW_SONG_INFO_PREP = "SELECT " + COLUMN_ARTIST_NAME + ", " +
COLUMN_SONG_ALBUM + ", " + COLUMN_SONG_TRACK + " FROM " + TABLE_ARTIST_SONG_VIEW +
" WHERE LOWER (" + COLUMN_SONG_TITLE + ") = ?";
public static final String INSERT_ARTIST = "INSERT INTO " + TABLE_ARTISTS +
"(" + COLUMN_ARTIST_NAME + ") VALUES(?)";
public static final String INSERT_ALBUMS = "INSERT INTO " + TABLE_ALBUMS +
"(" + COLUMN_ALBUM_NAME + ", " + COLUMN_ALBUM_ARTIST + ") VALUES(?, ?)";
public static final String INSERT_SONGS = "INSERT INTO " + TABLE_SONGS +
"(" + COLUMN_SONG_TRACK + ", " + COLUMN_SONG_TITLE + ", " + COLUMN_SONG_ALBUM +
") VALUES(?, ?, ?)";
public static final String QUERY_ARTIST = "SELECT " + COLUMN_ARTIST_ID + " FROM " +
TABLE_ARTISTS + " WHERE " + COLUMN_ARTIST_NAME + " =?";
public static final String QUERY_ALBUM = "SELECT " + COLUMN_ALBUM_ID + " FROM " +
TABLE_ALBUMS + " WHERE " + COLUMN_ALBUM_NAME + " =?";
private Connection conn; private PreparedStatement querySongInfoView; private PreparedStatement insertIntoArtists;
private PreparedStatement insertIntoAlbums;
private PreparedStatement insertIntoSongs;
private PreparedStatement queryArtist;
private PreparedStatement queryAlbum;
public boolean open() {
try {
conn = DriverManager.getConnection(CONNECTION_STRING);
querySongInfoView = conn.prepareStatement(QUERY_VIEW_SONG_INFO_PREP);
System.out.println("Connect to " + DB_Name + " success!");
insertIntoArtists = conn.prepareStatement(INSERT_ARTIST, Statement.RETURN_GENERATED_KEYS);
insertIntoAlbums = conn.prepareStatement(INSERT_ALBUMS, Statement.RETURN_GENERATED_KEYS);
insertIntoSongs = conn.prepareStatement(INSERT_SONGS);
queryArtist = conn.prepareStatement(QUERY_ARTIST);
queryAlbum = conn.prepareStatement(QUERY_ALBUM);
return true; } catch (SQLException e) {
System.out.println("Couldn't connect ot database: " + e.getMessage());
return false;
}
}
public void close() {
try {
if (querySongInfoView != null) {
querySongInfoView.close();
}
if (insertIntoArtists != null) {
insertIntoArtists.close();
}
if (insertIntoAlbums != null) {
insertIntoAlbums.close();
}
if (insertIntoSongs != null) {
insertIntoSongs.close();
}
if (queryArtist != null) {
queryArtist.close();
}
if (queryAlbum != null) {
queryAlbum.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
System.out.println("Couldn't close connection: " + e.getMessage());
}
}

private int insertArtist(String name) throws SQLException {
queryArtist.setString(1, name);
ResultSet resultSet = queryArtist.executeQuery();
if (resultSet.next()) {
return resultSet.getInt(1);
} else {
// Insert the artist
insertIntoArtists.setString(1, name);
int affectRows = insertIntoArtists.executeUpdate();
if (affectRows != 1) {
throw new SQLException("Couldn't insert artist");
}
ResultSet generateKeys = insertIntoArtists.getGeneratedKeys();
if (generateKeys.next()) {
return generateKeys.getInt(1);
} else {
throw new SQLException("Couldn't get _id for artist");
}
}
}
private int insertAlbum(String name, int artistId) throws SQLException {
queryAlbum.setString(1, name);
ResultSet resultSet = queryAlbum.executeQuery();
if (resultSet.next()) {
return resultSet.getInt(1);
} else {
// Insert the album
insertIntoAlbums.setString(1, name);
insertIntoAlbums.setInt(2, artistId);
int affectRows = insertIntoAlbums.executeUpdate();
if (affectRows != 1) {
throw new SQLException("Couldn't insert album");
}
ResultSet generateKeys = insertIntoAlbums.getGeneratedKeys();
if (generateKeys.next()) {
return generateKeys.getInt(1);
} else {
throw new SQLException("Couldn't get _id for album");
}
}
}

public void insertSong(String title, String artist, String album, int track) {
try {
conn.setAutoCommit(false);
int artistId = insertArtist(artist);
int albumId = insertAlbum(album, artistId);
insertIntoSongs.setInt(1, track);
insertIntoSongs.setString(2, title);
insertIntoSongs.setInt(3, albumId);
int affectRows = insertIntoSongs.executeUpdate();
if (affectRows == 1) {
conn.commit();
} else {
throw new SQLException("The song insert failed");
}
} catch (SQLException e) {
System.out.println("Insert song exception: " + e.getMessage());
try {
System.out.println("Performing rollback");
conn.rollback();
} catch (SQLException e2) {
System.out.println("Things are really bad!! " + e2.getMessage());
}
} finally {
try {
System.out.println("Resetting default commit behavior");
conn.setAutoCommit(true);
} catch (SQLException e) {
System.out.println("Couldn't reset auto-commit!" + e.getMessage());
}
}
}
}
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;
import java.util.Scanner;
public class Main {
public static void main(String[] args) {
Datasource dataSource = new Datasource();
if (!dataSource.open()) {
System.out.println("Can't open datasource");
return;
}
dataSource.insertSong("Touch of Grey", "Grateful Dead", "In The Dark", 1);
dataSource.close();
}
}
public void insertSong(String title, String artist, String album, int track) {
try {
conn.setAutoCommit(false);
int artistId = insertArtist(artist);
int albumId = insertAlbum(album, artistId);
insertIntoSongs.setInt(1, track);
insertIntoSongs.setString(2, title);
insertIntoSongs.setInt(8, albumId);
int affectRows = insertIntoSongs.executeUpdate();
if (affectRows == 1) {
conn.commit();
} else {
throw new SQLException("The song insert failed");
}
} catch (SQLException e) {
System.out.println("Insert song exception: " + e.getMessage());
try {
System.out.println("Performing rollback");
conn.rollback();
} catch (SQLException e2) {
System.out.println("Things are really bad!! " + e2.getMessage());
}
} finally {
try {
System.out.println("Resetting default commit behavior");
conn.setAutoCommit(true);
} catch (SQLException e) {
System.out.println("Couldn't reset auto-commit!" + e.getMessage());
}
}
}

上面代碼全都紀錄在我的 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
張小雄

張小雄

記錄成為軟體工程師的過程