After preprocessor has generated files, we can finally use them (see example):
1 package example1; 2 3 import example1.dao.Example1; 4 import example1.dao.IExample1; 5 import sqlg2.db.*; 6 import sqlg2.db.client.SafeDBInterface; 7 8 import java.rmi.RemoteException; 9 import java.sql.SQLException; 10 import java.sql.Timestamp; 11 import java.util.List; 12 13 /** 14 * Simple example of SQLG-generated code usage. 15 */ 16 public class Example1Test { 17 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 } 22 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 } 31 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 } |
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:
ISimpleTransaction
ITransaction, which extends ISimpleTransaction and adds usual commit and rollback methods.
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).
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).
sqlg2.MapperImpl. To establish such a mapping you should provide:
MappedType interface (containing single toSql method).
Mapper interface, usually extending standard mapper sqlg2.MapperImpl. For the latter you should override its methods getTestObject and getSpecialType.
java.sql.ResultSet, start column index and end column index (from 1).
NullSetter interface with no-parameter constructor;
TypeNullSetter annotation on your data class, pointing to this class.
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).
IDBInterface. In case of remote connection this process will run on the server side.
IConnectionFactory from current session with same login credentials using migrateTo() method
IDBCommon interface is always accessible for getInterface().