seke blog: how to connect to Java DB from Java application or Java EE container

假設 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，都會關閉連線，歸還給連線池
      } 
   } 
}

連線池管理，可供連線重複使用，減少頻繁開關連線的成本。
資源管理，伺服器可管理連線生命週期，確保連線正常關閉，返回連線池。
組態管理，伺服器設定資料庫連線組態細節，程式只要呼叫資料來源即可。

如果願意喪失上述好處，也可採用

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>

seke blog

2025年3月19日星期三

how to connect to Java DB from Java application or Java EE container

沒有留言:

2025年3月19日 星期三

how to connect to Java DB from Java application or Java EE container

沒有留言:

2025年3月19日星期三