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

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

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

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

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
張小雄

張小雄

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