假設 Java DB 已建好 db_name 資料庫，帳密為 user/password。
以下展示 Java EE 後台組件如何善用資料來源定義，注入資料來源，定義執行SQL指令，回傳結果集合給前台。
// 定義資料來源
@DataSourceDefinition(
name = "java:global/jdbc/db_name",
className = "org.apache.derby.jdbc.ClientDataSource",
url = "jdbc:derby://localhost:1527/db_name",
databaseName = "db_name",
user = "user",
password = "password")
@Named("bean")
@javax.faces.view.ViewScoped
public class Bean implements Serializable
{
// 定義每筆記錄的欄位屬性 f1, f2, f3
// 要求伺服器注入資料來源成為 dataSource 物件
@Resource(lookup="java:global/jdbc/db_name")
DataSource dataSource;
// 回傳查詢結果集合
public ResultSet query() throws SQLException
{
// check whether dataSource was injected by the server
if (dataSource == null)
{
throw new SQLException("Unable to obtain DataSource");
}
// 透過注入的資料來源，從連線池取得連線
Connection connection = dataSource.getConnection();
// check whether connection was successful
if (connection == null)
{
throw new SQLException("Unable to connect to DataSource");
}
try
{
// 建立查詢指令
PreparedStatement getRecords = connection.prepareStatement(
"SELECT * FROM tbl");
// 建立存放結果的快取列集合
CachedRowSet rowSet =
RowSetProvider.newFactory().createCachedRowSet();
// 執行查詢指令，將結果填入列集合
rowSet.populate(getRecords.executeQuery());
// 回傳列集合，供前台呈現
return rowSet;
}
finally
{
connection.close(); // return this connection to pool
}
}
// 新增一筆記錄，回傳去處網頁名稱
public String insert() throws SQLException
{
// check whether dataSource was injected by the server
if (dataSource == null)
{
throw new SQLException("Unable to obtain DataSource");
}
// 透過注入的資料來源，從連線池取得連線
Connection connection = dataSource.getConnection();
// check whether connection was successful
if (connection == null)
{
throw new SQLException("Unable to connect to DataSource");
}
try
{
// 建立帶參數新增記錄指令
PreparedStatement addEntry =
connection.prepareStatement("INSERT INTO tbl " +
"(f1,f2,f3) VALUES (?, ?, ?)");
// 指定欄位值
addEntry.setString(1, getF1());
addEntry.setString(2, getF2());
addEntry.setString(3, getF3());
addEntry.executeUpdate(); // insert the entry
return ""; // 回傳空字串，停在原頁
}
finally
{
connection.close(); // 不管發生例外或return，都會關閉連線，歸還給連線池
}
}
}- 利用 Java EE 後台注入資料來源的好處包括
- 連線池管理，可供連線重複使用，減少頻繁開關連線的成本。
- 資源管理，伺服器可管理連線生命週期，確保連線正常關閉，返回連線池。
- 組態管理，伺服器設定資料庫連線組態細節，程式只要呼叫資料來源即可。
如果願意喪失上述好處，也可採用
A. 單機版如DriverManager操作資料庫作法，一切自己管理，如下。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JavaDBExample {
private static final String DB_URL = "jdbc:derby:myDB;create=true";
private static final String USER = "user";
private static final String PASSWORD = "password";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);
Statement stmt = conn.createStatement()) {
// Create a table
String createTableSQL = "CREATE TABLE addresses (" +
"id INT PRIMARY KEY, " +
"firstname VARCHAR(255), " +
"lastname VARCHAR(255), " +
"street VARCHAR(255), " +
"city VARCHAR(255), " +
"state VARCHAR(255), " +
"zip VARCHAR(255))";
stmt.execute(createTableSQL);
// Insert data
String insertSQL =
"INSERT INTO addresses VALUES " +
"(1, 'John', 'Doe', '123 Main St', 'Anytown', 'CA', '12345')";
stmt.execute(insertSQL);
// Query data
String querySQL = "SELECT * FROM addresses";
ResultSet rs = stmt.executeQuery(querySQL);
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id"));
System.out.println("First Name: " + rs.getString("firstname"));
System.out.println("Last Name: " + rs.getString("lastname"));
System.out.println("Street: " + rs.getString("street"));
System.out.println("City: " + rs.getString("city"));
System.out.println("State: " + rs.getString("state"));
System.out.println("ZIP: " + rs.getString("zip"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
或者，
B. 採用單機版Java Persistence API (JPA) 操作資料庫作法，如下。
// Address.java 住址個體類別
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "addresses")
public class Address {
@Id
private int id;
private String firstname;
private String lastname;
String city;
private String state;
private String zip;
// Getters and setters
}
由如下個體管理員建立查詢，取用資料庫:
// JpaExample.java
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import java.util.List;
public class JpaExample {
private static final EntityManagerFactory ENTITY_MANAGER_FACTORY =
Persistence.createEntityManagerFactory("myPersistenceUnit");
public static void main(String[] args) {
EntityManager entityManager = ENTITY_MANAGER_FACTORY.createEntityManager();
try {
// Create a query to fetch addresses
TypedQuery<Address> query =
entityManager.createQuery("SELECT a FROM Address a", Address.class);
List<Address> addresses = query.getResultList();
// Process the result
for (Address address : addresses) {
System.out.println("ID: " + address.getId());
System.out.println("First Name: " + address.getFirstname());
System.out.println("Last Name: " + address.getLastname());
System.out.println("Street: " + address.getStreet());
System.out.println("City: " + address.getCity());
System.out.println("State: " + address.getState());
();
}
} finally {
entityManager.close();
ENTITY_MANAGER_FACTORY.close();
}
}
}其中，建立個體管理員時，須要參考如下 META-INF/persistence.xml 持續組態檔，
內含 myPersistenceUnit 持續單元的定義:
<persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence" version="2.2">
<persistence-unit name="myPersistenceUnit">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<class>com.example.Address</class>
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:derby:myDB;create=true"/>
<property name="javax.persistence.jdbc.user" value="user"/>
<property name="javax.persistence.jdbc.password" value="password"/>
<property name="javax.persistence.jdbc.driver" value="org.apache.derby.jdbc.EmbeddedDriver"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.DerbyDialect"/>
<property name="hibernate.hbm2ddl.auto" value="update"/>
</properties>
</persistence-unit>
</persistence>
沒有留言:
張貼留言