Java 學習記錄117 — executeQuery() and using Constants

張小雄
12 min readFeb 4, 2022

--

接續 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));

可以寫成這樣,又更直觀

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

--

--