[ Back to contents ]

3. SQLG runtime library

1. Introduction - first example continued

After preprocessor has generated files, we can finally use them (see example):

1    package example1; 
3    import example1.dao.Example1; 
4    import example1.dao.IExample1; 
5    import sqlg2.db.*; 
6    import sqlg2.db.client.SafeDBInterface; 
8    import java.rmi.RemoteException; 
9    import java.sql.SQLException; 
10   import java.sql.Timestamp; 
11   import java.util.List; 
13   /** 
14    * Simple example of SQLG-generated code usage. 
15    */ 
16   public class Example1Test { 
18       public static void insertScott(IExample1 iex1) throws SQLException, RemoteException { 
19           // Calling business method 
20           iex1.insert(7788, "SCOTT", "ANALYST", null, new Timestamp(System.currentTimeMillis())); 
21       } 
23       public static void print(IExample1 iex1) throws SQLException, RemoteException { 
24           // Calling business method 
25           List<Example1.EmpRow> list = iex1.selectAll(); 
26           // Printing result 
27           for (Example1.EmpRow emp : list) { 
28               System.out.println(emp.empNo() + "\t" + emp.empName()); 
29           } 
30       } 
32       public static void main(String[] args) throws SQLException, RemoteException { 
33           /** 
34            * JDBC URL to use 
35            */ 
36           String url; 
37           if (args.length <= 0) { 
38               System.err.println("No JDBC URL specified"); 
39               return; 
40           } else { 
41               url = args[0]; 
42           } 
43           // Creating root object for local DB connections ('local' means without RMI). 
44           // It should be done once for the whole application. 
45           IConnectionFactory conf = new LocalConnectionFactory( 
46               new DBAuthenticationImpl( // it uses DriverManager.getConnection for authentication 
47                   "oracle.jdbc.driver.OracleDriver", // JDBC driver class name 
48                   url                                // JDBC URL 
49               ), 
50               new OracleDBSpecific()  // corresponds to the type of database 
51           ); 
52           // Opening connection for user sqlg2 with password sqlg2 
53           IDBInterface db = conf.openConnection("sqlg2", "sqlg2"); 
54           // You can wrap your connection into SafeDBInterface, but really 
55           // it is helpful only for remote connections. 
56           db = new SafeDBInterface(db); 
57           // All the code above was a preparation required to setup database 
58           // connection, now real work begins: 
59           try { 
60               // Getting data access interface 
61               IExample1 iex1 = db.getSimpleTransaction().getInterface(IExample1.class); 
62               // Running business methods 
63               insertScott(iex1); 
64               print(iex1); 
65           } finally { 
66               db.close(); // closing DB connection 
67           } 
68       } 
69   }

2. Connections and transactions

The root object of SQLG library objects hierarchy is IConnectionFactory. It can be local (LocalConnectionFactory obtained by its constructor) or remote (ConnectionFactory - obtained by Naming).

You can create DB connection (IDBInterface) using its method openConnection(), providing user name and password. You can think of IConnectionFactory as of DataSource, and IDBInterface as of Connection. But, unlike in JDBC, you cannot directly invoke business methods on connection: there is another level of hierarchy - transaction objects. It was introduced for better control of transactions.

Most of the time you need transactional behavior only on business methods - whole business method is either committed or rolled back. But in some cases you will need long-running transactions consisting of multiple business method calls. So, there are two types of transactions:

You can obtain data access objects using getInterface() method from both of them, but transactional behavior of these objects differ. These data access objects are similar to stateless EJB beans (with declarative transactions for ISimpleTransaction and client-demarcated transactions for ITransaction).

3. Connection management

IConnectionFactory object owns a reference to AuthenticationHelper object. It is responsible for connection pool allocation and user authentication. When user tries to log in calling openConnection() method, AuthenticationHelper.getConnectionManager method is called first. It returns newly created connection pool for the user, and one connection out of this pool is allocated. Then AuthenticationHelper.authenticate method is run on this connection and user credentials. If everything is ok, connection is released to the pool and client gets IDBInterface object associated with this pool, else the pool is closed and exception is thrown.

Simple transactions allocate connection from a pool before business method call and release it after the call completes. Non-simple transactions allocate connection at transaction start (when any statement is executed) and release it only when they are committed or rolled back. Since it is possible to start more than one transaction in a single IDBInterface object, this behavior may lead to deadlock between transactions (see javadoc and example).

SQLG library contains only the simplest implementation of AuthenticationHelper, which creates single-connection pool and does authentication using DB authentication. But it's easy to write your own implementation, for example, multi-connection pool implementation exploiting some pooling library (see example).

4. Custom mappers

You can use mapping between database columns and user-defined classes. Simple database types (such as NUMBER) can be mapped as well as complex database types (arrays or objects). Since these mappings should be known to preprocessor, they are implemented as preprocessor plugin extending standard mapper sqlg2.MapperImpl. To establish such a mapping you should provide:

Mapping uses special syntax in SQL queries - "column_name#column_type{columns...}" or "column_type{columns...}" (column name is the same as type name in the latter case). See two examples - mapping of two simple columns to user-defined class and mapping of database object type to user-defined class (Oracle-specific).

5. Exotic possibilities

[ Back to contents ] [ To API JavaDoc ]