The most direct way to access the database in the Witrix platform is to use the edu.thu.db.sql class. The basic usage is as follows: // Setting the database connection parameters, the connection can pass through java.sql.driverManager and
//javax.sql.dataSource is established and supports the database connection buffer pool. TransactionMode DB = New TransactionMode ("default"); // Setting the database connection parameter SQL SQL = SQL.BEGIN (). SELECT (). Field ("ID") .from (). Table ("Other_Table") .where () .like ("name", "a%") ;.nd (); int n = sql.begin (). Select (). countall () .from (). TABLE ("my_table") .where ) .GT ("Value", New Integer (3)) // Value> 3 .and (). in ("Type", new string [] {"a", "b"}) // type in (' A ',' b ') .and (). in ("DATA_ID", SQL) // Data_ID in (Select ID from Other_Table Where Name Like' a% ') .end (). Query (db) .intValue () ;
In the design of SQL classes, three design principles reflect: 1. Oreal streaming design 2. Parameter objectification 3. Bug-free design
First, the SQL class will construct the SQL statement, the connection database, and the type of query result is broken down into three orthogonal parts. In general database programming, we need to access the following way: String sql = "select count (*) from my_table where value>?"; Connection conn = getConnection (); PreparedStatement stmt = null; try {PreparedStatement stmt = conn.prepareStatement (SQL); Stmt.SetParameter (1, Valuelimit); ResultSet RS = stmt.executeQuery (); rs.next (); int RET = rs.Getint (1); // ...} finally {Try {IF Stmt! = null) stmt.close ();} catch (exception e) {E.PrintStackTrace ();}
Try {conn.close ();} catch (exception e) {E.PrintStackTrace ();}}
From the above example, we can notice that in normal JDBC programming, set the parameters of the SQL statement, establish a database connection, and obtain the results in the result set and translate into the appropriate format These three are intertwined. In particular, open the connection to close the connection statement, causing the flow of the entire program not linear, because we can't help but connect after the connection, we must remember that all other things must be remembered. Close the connection. Through a range of package objects, the SQL class implements a orthogonized streaming design, which is as follows: SQL.BEGIN (). Splicing SQL statement .End (). Establish a connection and run the SQL statement. Conversion results ( Note It does not need to be explicitly closed after the SQL statement is executed. The second design point of the SQL class is the parameter objectification. In the Witrix platform, the engine function that actually runs the SQL statement is iDBEngine.execute (String SqlText, Int Sqlparameters, int result, int concurrency; here SQLText, SQLParameters, etc. are all parameters, but the process of constructing these parameters in the application is very complex And very flexible, a general UTIL class (static Utility function) cannot provide effective simplification. So constructing the SQL statement This process is not done through the UTIL class, but through a dedicated SQLBuilder object. The basic idea of SQL is to incorporate SQL statements into the Java program framework, so that the SQL statement becomes the controlled part of the program. First, the SQL class encapsulates the SQL text to the SQL parameter into a complete object so that the reuse of the SQL statement is possible. Second, the SQLBuilder class constructs a SQL statement through a controlled manner (all keywords correspond to a function), the entire process is similar to the direct writing SQL text, but the function package makes us to specify SQL text on the same line. The parameters arrived, not like the JDBC, set the specified parameters. At the same time, SQLBuilder supports an extension of Java language, such as directly supporting Java collection type, etc., greatly simplifies the structure of SQL statements, because the most confusing part in general SQL splicing is involved in cyclic and judgment. Parameter objectization methods can also be applied to those having a large number of default parameters. In the SQL class, if we want to select data in Scroll Insensitive, the call mode is as follows: sql.begin (). SELECT (). STAR () .FROM (). Table ("my_table") .end (). Scrollable (True) .List (db, 3,100); if we want to use the statement.cancel () feature, we can specify the CancelMonitor parameter sql.begin () .... End (). CancelMonitor (Monitor) .query (dB) .listvalue ();
The third design point of the SQL class is bug-free. The most frequent error in JDBC is to forget to turn off the connection caused resource leaks. In the SQL class, the connection is only available from the buffer pool when needed, and when the database operation is completed (regardless of success or failure), the database connection will be automatically closed. (Or return the connection pool) and automatically process the submission and rollback. The entire program code is generally not required to explicitly shut down the connection, thereby greatly reduces the chance of resource leakage and avoids the emergence of this error. This is like Java without requiring programmers to explicitly release memory, so that the BUG of MEMORY Leak is avoided in most cases.
