Open in app

Sign in

Write

Sign in

Java 學習記錄120 — Executing SQL in DB Browser

張小雄
15 min readFeb 11, 2022

--

承接 — 119 — Write Java Query for Artists

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

輸出結果：

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}

改成用 index 來找對應 col

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

更新 Main.java

package musicDB;
import musicDB.model.Artist;
import musicDB.model.Datasource;import javax.swing.*;
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(Datasource.SortOrder.ORDER_BY_ASC);
        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 = 11, Name = 1000 Maniacs}

Artist{ ID = 86, Name = 10cc}

（省略）

Artist{ ID = 148, Name = Yardbirds}

Artist{ ID = 179, Name = Yngwie Malmsteen}

Artist{ ID = 23, Name = ZZ Top}

新增了排列順序的選項

Tim 老師是簡單的用個 int，他說也可以用 Enum

所以我自己嘗試了一下，感覺也不錯但很少用，試了半天才成功

Tim 老師說用 Java 操作前，可以先用 SQLite 試試看

打開 SQLite，連接資料庫，如果之前連接過的話，可以點左上 “檔案” 下方會有曾開過的路徑

連接看到資料出現後，點上面的中間 “執行 SQL”

測試一：

SELECT * FROM Albums WHERE artist = 8

輸出結果：

236 The Number of the Beast 8

412 Powerslave 8

420 Seventh Son Of A Seventh Son 8

輸入框的左上角點”打開標籤頁”，可以多一個 tab

測試二：

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

測試三：

SELECT albums.name FROM albums INNER JOIN artists ON albums.artist = artists._id WHERE artists.name = "Carole King" ORDER BY albums.name COLLATE NOCASE ASC

輸出結果：

Tapestry

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

Taiwan
Programming
Learning To Code
Coding
Java

--

--

張小雄
張小雄

Written by 張小雄

35 Followers
19 Following

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

No responses yet

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams