假設 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>