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

張小雄
6 min readFeb 24, 2022

--

承接 — 126 — SQL Injection Attacks and Prepared Statements

更新 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 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());
}
}
}
}

更新 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;
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();
}
}

輸出結果:

Connect to music.db success!

Resetting default commit behavior

打開 SQLite

執行前先看一下,song 的數量是 5350

執行後發現變 5351 代表新增歌曲成功

主要就是透過最下方的三個 method 來達成

前面兩個 method 有錯誤的話都是用 throw

給最後一個 methodcatch 來處理

之前應該沒用過這招還挺新鮮的

insertSong() 中,只要其中一步有問題

就會進到 catch 裡面做 rollback

等於取消這次活動的意思,當作沒這回事

因為我們要手動 commit 就把 autocommit 先給關掉

最後在 finally 裡面把其恢復成打開

更新 Datasource.java

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

更新 Main.java

dataSource.insertSong("Bird Dog", "Everyly Brothers", "All-Time Greatest Hits", 7);

輸出結果:

Connect to music.db success!

Resetting default commit behavior

Exception in thread “main” java.lang.ArrayIndexOutOfBoundsException: Index 7 out of bounds for length 3

at org.sqlite.core.CorePreparedStatement.batch(CorePreparedStatement.java:121)

at org.sqlite.jdbc3.JDBC3PreparedStatement.setInt(JDBC3PreparedStatement.java:324)

at musicDB.model.Datasource.insertSong(Datasource.java:377)

at musicDB.Main.main(Main.java:69)

insertIntoSongs.setInt(8, albumId);

故意改成 8,看看會如何

開 SQLite 能看到一些有趣的事

songs 的部份沒有新增但 artitst、albums 卻都新增了

奇怪了,不是已經寫進 catch 裡面,讓其回滾了,怎麼還會新增?

發現原來是因為之前寫的是 SQLException

但這次報錯的卻是 ArrayIndexOutOfBoundsException

所以就沒進到裡面走 rollback

而且最後因為 finally 原本關閉的自動更新又被開啟

所以才會新增前面沒問題的 artist 跟 album 上去

但這不是我們想要的結果

更新 Datasource.java

catch (Exception e)

輸出結果:

Connect to music.db success!

Insert song exception: Index 7 out of bounds for length 3

Performing rollback

Resetting default commit behavior

改正方法也很簡單,改成所有異常都捕捉,就能順利走到 rollback 去了

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

--

--

張小雄

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