Feb 24
6 min read
Java 學習記錄128 — Insert Albums, Artists, and Songs
承接 — 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
給最後一個 method 的 catch 來處理
之前應該沒用過這招還挺新鮮的
在 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 去了
