DBCP连接池的最简单应用(用于ORACLE数据库)

鉴于有人问起DBCP直接用于JDBC连接的问题，我做了一个最简单的示例。所有资源来源于网上。它不需要什么Web容器，就是一简单的控制台应用。 

资源： 
http://apache.etoak.com//commons/pool/binaries/commons-pool-1.5.6-bin.zip 
http://labs.renren.com/apache-mirror//commons/dbcp/binaries/commons-dbcp-1.4-bin.zip 
http://download.java.net/maven/1/javaee/jars/javaee-api-5.jar 
当然，还有oracle jdbc要用的ojdbc14.jar (适用于oracle9i及以上版本) 

工程文件：放到这里了。http://dl.iteye.com/topics/download/210279f0-f752-37a6-969f-d58ba13cc394 

数据库连接信息： 
jdbc:oracle:thin:scott/tiger@sean-m700:1521:ora92 
sean-m700是主机名，ora92是oracle数据库的instance ID. 我手头的机器上没有安装oracle数据库，用的是很早以前的一个oracle9.2的拷贝，重新安装实例和相应服务得来的。 

源码如下：借化献佛，源码也是从网上得来的。(http://svn.apache.org/viewvc/commons/proper/dbcp/trunk/doc/BasicDataSourceExample.java?revision=1100136&view=markup) 

Java代码  

1. /* 
2. // 
3. 33  // Here's a simple example of how to use the BasicDataSource. 
4. 34  // 
5. 35   
6. 36  // 
7. 37  // Note that this example is very similiar to the PoolingDriver 
8. 38  // example. 
9. 39   
10. 40  // 
11. 41  // To compile this example, you'll want: 
12. 42  //  * commons-pool-1.5.6.jar 
13. 43  //  * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+) 
14. 44  //  * j2ee.jar (for the javax.sql classes) 
15. 45  // in your classpath. 
16. 46  // 
17. 47  // To run this example, you'll want: 
18. 48  //  * commons-pool-1.5.6.jar 
19. 49  //  * commons-dbcp-1.3.jar (JDK 1.4-1.5) or commons-dbcp-1.4 (JDK 1.6+) 
20. 50  //  * j2ee.jar (for the javax.sql classes) 
21. 51  //  * the classes for your (underlying) JDBC driver 
22. 52  // in your classpath. 
23. 53  // 
24. 54  // Invoke the class using two arguments: 
25. 55  //  * the connect string for your underlying JDBC driver 
26. 56  //  * the query you'd like to execute 
27. 57  // You'll also want to ensure your underlying JDBC driver 
28. 58  // is registered.  You can use the "jdbc.drivers" 
29. 59  // property to do this. 
30. 60  // 
31. 61  // For example: 
32. 62  //  java -Djdbc.drivers=oracle.jdbc.driver.OracleDriver \ 
33. 63  //       -classpath commons-pool-1.5.6.jar:commons-dbcp-1.4.jar:j2ee.jar:oracle-jdbc.jar:. \ 
34. 64  //       PoolingDataSourceExample 
35. 65  //       "jdbc:oracle:thin:scott/tiger@myhost:1521:mysid" 
36. 66  //       "SELECT * FROM DUAL" 
37. */  
38. /* 
39. The Oracle connection URL for the thin client-side driver ojdbc14.jar has the following format: 
40. jdbc:oracle:thin:[user/password]@[host][:port]:SID 
41. jdbc:oracle:thin:[user/password]@//[host][:port]/SID 
42.  
43.   user - The login user name defined in the Oracle server. 
44.  
45.   password - The password for the login user. 
46.  
47.   host - The host name where Oracle server is running.  
48.          Default is 127.0.0.1 - the IP address of localhost. 
49.  
50.   port - The port number where Oracle is listening for connection. 
51.          Default is 1521. 
52.  
53.   SID  - System ID of the Oracle server database instance.  
54.          SID is a required value. By default, Oracle Database 10g Express  
55.          Edition creates one database instance called XE. 
56. */  
57.   
58. import org.apache.commons.dbcp.BasicDataSource;  
59. import javax.sql.*;  
60. import java.sql.*;  
61.   
62. public class TestDataSource  
63. {  
64.   
65.     /** 
66.      * @param args 
67.      */  
68.     public static void main(String[] args)  
69.     {  
70.         System.out.println("Setting up data source.");  
71.         String url = "jdbc:oracle:thin:scott/tiger@sean-m700:1521:ora92";  
72.         DataSource dataSource = setupDataSource(url);  
73.         System.out.println("Done...");  
74.   
75.         // Now, we can use JDBC DataSource as we normally would.  
76.         //  
77.         Connection conn = null;  
78.         Statement stmt = null;  
79.         ResultSet rset = null;  
80.   
81.         try {  
82.             System.out.println("Creating connection.");  
83.             conn = dataSource.getConnection();  
84.             System.out.println("Creating statement.");  
85.             stmt = conn.createStatement();  
86.             System.out.println("Executing statement.");  
87.             rset = stmt.executeQuery("select 1 from DUAL");  
88.             System.out.println("Results:");  
89.             int numcols = rset.getMetaData().getColumnCount();  
90.             while(rset.next()) {  
91.                 for(int i=1;i<=numcols;i++) {  
92.                     System.out.print("\t" + rset.getString(i));  
93.                 }  
94.                 System.out.println("");  
95.             }  
96.         } catch(SQLException e) {  
97.             e.printStackTrace();  
98.         } finally {  
99.             try { if (rset != null) rset.close(); } catch(Exception e) { }  
100.             try { if (stmt != null) stmt.close(); } catch(Exception e) { }  
101.             try { if (conn != null) conn.close(); } catch(Exception e) { }  
102.         }  
103.     }  
104.   
105.     public static DataSource setupDataSource(String connectURI) {  
106.         BasicDataSource ds = new BasicDataSource();  
107.         ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");  
108.         ds.setUsername("scott");  
109.         ds.setPassword("tiger");  
110.         ds.setUrl(connectURI);  
111.         return ds;  
112.     }  
113.   
114.     public static void printDataSourceStats(DataSource ds) {  
115.         BasicDataSource bds = (BasicDataSource) ds;  
116.         System.out.println("NumActive: " + bds.getNumActive());  
117.         System.out.println("NumIdle: " + bds.getNumIdle());  
118.     }  
119.   
120.     public static void shutdownDataSource(DataSource ds) throws SQLException {  
121.         BasicDataSource bds = (BasicDataSource) ds;  
122.         bds.close();  
123.     }  
124.   
125. }  

源码下载地址：

http://dl.iteye.com/topics/download/210279f0-f752-37a6-969f-d58ba13cc394

Handling MobiLink server errors in Java through Implementing LogListener Interface

When scanning the log is not sufficient, you can monitor your applications programmatically. For example, you can send messages of a certain type in an email.
You can write methods that are passed a class representing every error or warning message that is printed to the log. This may help you monitor and audit a MobiLink server.
The following code installs a LogListener for all warning messages, and writes the information to a file.

class TestLogListener implements LogListener {
  FileOutputStream _out_file;
  public TestLogListener( FileOutputStream out_file ) {
    _out_file       = out_file;
  }

  public void messageLogged(  ServerContext   sc,
    LogMessage msg ) {
    String  type;
    String  user;
    try {
      if(msg.getType() == LogMessage.ERROR) {
        type = "ERROR";
      } else if(msg.getType() == LogMessage.WARNING) {
        type = "WARNING";
      } else {
        type = "UNKNOWN!!!";
      }

      user = msg.getUser();
      if( user == null ) {
        user = "NULL";
      }
      _out_file.write(
        ("Caught msg type=" + type +
         " user=" + user +
         " text=" +msg.getText() +
         "\n").getBytes() );
      _out_file.flush();
    } catch( Exception e ) {
      // Print some error output to the MobiLink log.
      e.printStackTrace();
    }
  }
}

The following code registers TestLogListener to receive warning messages. Call this code from anywhere that has access to the ServerContext such as a class constructor or synchronization script.

// ServerContext serv_context; serv_context.addWarningListener(       new MyLogListener( ll_out_file ));

========================
http://iihero.8800.org/
========================
Regards,
Sean.
▁▁▁▁▁
▕ █ ██ ▏
▕▔▔ ▔▔\
▕═╭╮══╭╮══
▔╰╯▔▔╰╯▔▔o
▔▔▔▔▔▔▔▔▔▔

ASA10中的Global increment default扩展(Important)

当我使用这个类型的时候：

create table Admin (
  admin_id      bigint default global autoincrement(1000000) primary key,
  data          varchar(30),
  last_modified timestamp default timestamp
);

直接insert into Admin(data) values(1)

失败，原因是没有设置一个选项：

public.Global_database_id的值。

set option public.Global_database_id = 10;

insert into Admin(data) values('21425.34');

select * from Admin;

admin_id,data,last_modified
10000001,'21425.34','2008-02-20 17:09:31.111'

它的起始值从global_database_id * autoincrement区段值 开始，最大增长到autoincrement。

详细说明如下：

The GLOBAL AUTOINCREMENT default is intended for use when multiple databases are used in a SQL Remote replication or MobiLink synchronization environment. It ensures unique primary keys across multiple databases.
This option is similar to AUTOINCREMENT, except that the domain is partitioned. Each partition contains the same number of values. You assign each copy of the database a unique global database identification number. SQL Anywhere supplies default values in a database only from the partition uniquely identified by that database's number.
The partition size can be any positive integer, although the partition size is generally chosen so that the supply of numbers within any one partition will rarely, if ever, be exhausted.
If the column is of type BIGINT or UNSIGNED BIGINT, the default partition size is 2³² = 4294967296; for columns of all other types, the default partition size is 2¹⁶ = 65536. Since these defaults may be inappropriate, especially if your column is not of type INT or BIGINT, it is best to specify the partition size explicitly.
When using this option, the value of the public option global_database_id in each database must be set to a unique, non-negative integer. This value uniquely identifies the database and indicates from which partition default values are to be assigned. The range of allowed values is np + 1 to (n + 1) p, where n is the value of the public option global_database_id and p is the partition size. For example, if you define the partition size to be 1000 and set global_database_id to 3, then the range is from 3001 to 4000.
If the previous value is less than (n + 1) p, the next default value is one greater than the previous largest value in column. If the column contains no values, the first default value is np + 1. Default column values are not affected by values in the column outside of the current partition; that is, by numbers less than np + 1 or greater than p(n + 1). Such values may be present if they have been replicated from another database via MobiLink synchronization.
Because the public option global_database_id cannot be set to a negative value, the values chosen are always positive. The maximum identification number is restricted only by the column data type and the partition size.
If the public option global_database_id is set to the default value of 2147483647, a NULL value is inserted into the column. If NULL values are not permitted, attempting to insert the row causes an error. This situation arises, for example, if the column is contained in the table's primary key.
NULL default values are also generated when the supply of values within the partition has been exhausted. In this case, a new value of global_database_id should be assigned to the database to allow default values to be chosen from another partition. Attempting to insert the NULL value causes an error if the column does not permit NULLs. To detect that the supply of unused values is low and handle this condition, create an event of type GlobalAutoincrement. See Understanding events.
Global autoincrement columns are typically primary key columns or columns constrained to hold unique values (see Enforcing entity integrity).
While using the global autoincrement default in other cases is possible, doing so can adversely affect database performance. For example, in cases where the next value for each column is stored as a 64-bit signed integer, using values greater than 2³¹ - 1 or large double or numeric values may cause wraparound to negative values.
You can retrieve the most recent value inserted into an autoincrement column using the @@identity global variable. For more information, see @@identity global variable.

ASA10(SQLAnywhere10)中的TIMESTAMP类型(蛮奇怪的)

TIMESTAMP indicates when each row in the table was last modified. When a column is declared with DEFAULT TIMESTAMP, a default value is provided for inserts, and the value is updated with the current date and time whenever the row is updated.

Data type

TIMESTAMP

Remarks

Columns declared with DEFAULT TIMESTAMP contain unique values so that applications can detect near-simultaneous updates to the same row. If the current timestamp value is the same as the last value, it is incremented by the value of the default_timestamp_increment option.
You can automatically truncate timestamp values in SQL Anywhere based on the default_timestamp_increment option. This is useful for maintaining compatibility with other database software that records less precise timestamp values.
The global variable @@dbts returns a TIMESTAMP value representing the last value generated for a column using DEFAULT TIMESTAMP

The main difference between DEFAULT TIMESTAMP and DEFAULT CURRENT TIMESTAMP is that DEFAULT CURRENT TIMESTAMP is set only at INSERT, while DEFAULT TIMESTAMP is set at both INSERT and UPDATE.