Java 學習記錄119 — Write Java Query for Artists

package musicDB.model;public class Album {
private int id;
private String name;
private int artistId;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getArtistId() {
return artistId;
}
public void setArtistId(int artistId) {
this.artistId = artistId;
}
}
package musicDB.model;public class Artist {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Artist{" +
" ID = " + id +
", Name = " + name +
'}';
}
}
package musicDB.model;public class Song {
private int id;
private int track;
private String name;
private int albumId;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getTrack() {
return track;
}
public void setTrack(int track) {
this.track = track;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAlbumId() {
return albumId;
}
public void setAlbumId(int albumId) {
this.albumId = albumId;
}
}
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 String TABLE_ARTISTS = "artists";
public static final String COLUMN_ARTIST_ID = "_id";
public static final String COLUMN_ARTIST_NAME = "name";
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";
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(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;
}
}
}
package musicDB;
import musicDB.model.Artist;
import musicDB.model.Datasource;
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();
if (artists == null) {
System.out.println("No artists!");
return;
}
for (Artist artist : artists) {
System.out.println(artist);
}
dataSource.close();
}
}

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

張小雄

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