接續 116 — JDBC and SQLite GUI Browser
Main.java
package testDB;import java.sql.*;public class Main { public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection("jdbc:sqlite:C:\\software\\JetBrains\\IdeaProjects\\java-the-complete-java-developer-course\\chapter19\\src\\testDB\\testjava.db");
Statement statement = conn.createStatement()) { // create table
statement.execute("CREATE TABLE IF NOT EXISTS contacts(name TEXT, phone INTEGER , email TEXT)");// // add data
// statement.execute("INSERT INTO contacts(name,phone,email) VALUES ('Tim',6545678,'tim@email.com') ");
// statement.execute("INSERT INTO contacts(name,phone,email) VALUES ('Joe',45632,'joe@anywhere.com') ");
// statement.execute("INSERT INTO contacts(name,phone,email) VALUES ('Jane',4829484,'jane@somewhere.com') ");
// statement.execute("INSERT INTO contacts(name,phone,email) VALUES ('Fido',9038,'dog@email.com') ");// // update data
// statement.execute("UPDATE contacts SET phone=5566789 WHERE name='Jane'");// // delete data
// statement.execute("DELETE FROM contacts WHERE name='Joe'");// // query data
// statement.execute("SELECT * FROM contacts");
// ResultSet results = statement.getResultSet();
ResultSet results = statement.executeQuery("SELECT * FROM contacts");
while (results.next()) {
System.out.println(results.getString("name") + " "
+ results.getInt("phone") + " "
+ results.getString("email"));
} results.close();
} catch (SQLException e) {
System.out.println("Something went wrong " + e.getMessage());
} }
}
輸出結果：
Tim 6545678 tim@email.com
Jane 5566789 jane@somewhere.com
Fido 9038 dog@email.com
query data 那邊兩句改成一句
更新 Main.java
package testDB;import java.sql.*;public class Main {
public static final String DB_NAME = "testjava.db";
public static final String CONNECTION_STRING = "jdbc:sqlite:C:\\software\\JetBrains\\IdeaProjects\\java-the-complete-java-developer-course\\chapter19\\src\\testDB\\" + DB_NAME; public static final String TABLE_CONTACTS = "contacts"; public static final String COLUMN_NAME = "name";
public static final String COLUMN_PHONE = "phone";
public static final String COLUMN_EMAIL = "email"; public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(CONNECTION_STRING);
Statement statement = conn.createStatement()) { statement.execute("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
// create table
statement.execute("CREATE TABLE IF NOT EXISTS " + TABLE_CONTACTS +
"(" +
COLUMN_NAME + " text, " +
COLUMN_PHONE + " integer, " +
COLUMN_EMAIL + " text " +
")"); // create data
statement.execute("INSERT INTO " + TABLE_CONTACTS +
" (" +
COLUMN_NAME + ", " +
COLUMN_PHONE + ", " +
COLUMN_EMAIL +
" )" +
"VALUES('Tim',6545678,'tim@email.com')"); statement.execute("INSERT INTO " + TABLE_CONTACTS +
"(" +
COLUMN_NAME + "," +
COLUMN_PHONE + "," +
COLUMN_EMAIL +
")" +
"VALUES('Joe',45632,'joe@anywhere.com')"); statement.execute("INSERT INTO " + TABLE_CONTACTS +
"(" +
COLUMN_NAME + "," +
COLUMN_PHONE + "," +
COLUMN_EMAIL +
")" +
"VALUES('Jane',4829484,'jane@somewhere.com')"); statement.execute("INSERT INTO " + TABLE_CONTACTS +
"(" +
COLUMN_NAME + "," +
COLUMN_PHONE + "," +
COLUMN_EMAIL +
")" +
"VALUES('Fido',9038,'dog@somewhere.com')"); // update data
statement.execute("UPDATE " + TABLE_CONTACTS + " SET " +
COLUMN_PHONE + "=5566789" +
" WHERE " + COLUMN_NAME + "='Jane'");
// delete data
statement.execute("DELETE FROM " + TABLE_CONTACTS +
" WHERE " + COLUMN_NAME + "='Joe'"); // query data
ResultSet results = statement.executeQuery("SELECT * FROM " + TABLE_CONTACTS); while (results.next()) {
System.out.println(results.getString(COLUMN_NAME) + " "
+ results.getInt(COLUMN_PHONE) + " "
+ results.getString(COLUMN_EMAIL));
} results.close();
} catch (SQLException e) {
System.out.println("Something went wrong " + e.getMessage());
e.printStackTrace();
} }
}
輸出結果：
Tim 6545678 tim@email.com
Jane 5566789 jane@somewhere.com
Fido 9038 dog@somewhere.com
Tim 老師說：寫成之前那樣，如果之後欄位名稱要修改
那全部有寫到的地方都要修改，所以弄成常數會比較方便，改一個地方就好
但改成這樣我看得好辛苦…
更新 Main.java
package testDB;import java.sql.*;public class Main {
public static final String DB_NAME = "testjava.db";
public static final String CONNECTION_STRING = "jdbc:sqlite:C:\\software\\JetBrains\\IdeaProjects\\java-the-complete-java-developer-course\\chapter19\\src\\testDB\\" + DB_NAME; public static final String TABLE_CONTACTS = "contacts"; public static final String COLUMN_NAME = "name";
public static final String COLUMN_PHONE = "phone";
public static final String COLUMN_EMAIL = "email"; public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(CONNECTION_STRING);
Statement statement = conn.createStatement()) { statement.execute("DROP TABLE IF EXISTS " + TABLE_CONTACTS);
// create table
statement.execute("CREATE TABLE IF NOT EXISTS " + TABLE_CONTACTS +
"(" +
COLUMN_NAME + " text, " +
COLUMN_PHONE + " integer, " +
COLUMN_EMAIL + " text " +
")"); // create data
insertContact(statement, "Tim", 6545678, "tim@email.com");
insertContact(statement, "Joe", 45632, "joe@anywhere.com");
insertContact(statement, "Jane", 4829484, "jane@somewhere.com");
insertContact(statement, "Fido", 9038, "dog@somewhere.com"); // update data
statement.execute("UPDATE " + TABLE_CONTACTS + " SET " +
COLUMN_PHONE + "=5566789" +
" WHERE " + COLUMN_NAME + "='Jane'"); // delete data
statement.execute("DELETE FROM " + TABLE_CONTACTS +
" WHERE " + COLUMN_NAME + "='Joe'");
// query data
ResultSet results = statement.executeQuery("SELECT * FROM " + TABLE_CONTACTS); while (results.next()) {
System.out.println(results.getString(COLUMN_NAME) + " "
+ results.getInt(COLUMN_PHONE) + " "
+ results.getString(COLUMN_EMAIL));
} results.close();
} catch (SQLException e) {
System.out.println("Something went wrong " + e.getMessage());
e.printStackTrace();
} } private static void insertContact(Statement statement, String name, int phone, String email) throws SQLException {
statement.execute("INSERT INTO " + TABLE_CONTACTS +
"(" +
COLUMN_NAME + "," +
COLUMN_PHONE + "," +
COLUMN_EMAIL +
")" +
"VALUES('" + name + "'," + phone + ",'" + email + "')");
}
}
輸出結果：
Tim 6545678 tim@email.com
Jane 5566789 jane@somewhere.com
Fido 9038 dog@somewhere.com
把重複的部份獨立成一個 method
這樣看起來舒服多了
同學 Silambarasan 補充：
statement.execute(String.format("CREATE TABLE IF NOT EXISTS %s " +
"(%s text, %s integer, %s text)", TABLE_CONTACTS, COLUMN_NAME, COLUMN_PHONE, COLUMN_EMAIL));可以寫成這樣，又更直觀