JOOQ_中文教程（十）

// Procedures with more than 1 OUT parameter return the procedure // object (see above example) public static PAuthorExists_2 pAuthorExists_2(Configuration configuration, String authorName) { // [...] } } An sample invocation, equivalent to the previous example: assertEquals(BigDecimal.ONE, Procedures.pAuthorExists(configuration, "Paulo")); Page 36 第36页 The jOOQ User Manual 2.6. 2.6。 UDT's including ARRAY and ENUM types jOOQ is brought to you by Lukas Eder. Distributed under the Apache 2 licence Page 36 / 76 jOOQ's understanding of procedures vs functions jOOQ does not formally distinguish procedures from functions. jOOQ only knows about routines, which can have return values and/or OUT parameters. This is the best option to handle the variety of stored procedure / function support across the various supported RDBMS. For more details, read on about this topic, here: blog.jooq.org/2011/10/17/what-are-procedures-and-functions-after-all/ Packages in Oracle Oracle uses the concept of a PACKAGE to group several procedures/functions into a sort of namespace. The SQL standard talks about "modules", to represent this concept, even if this is rarely implemented. This is reflected in jOOQ by the use of Java sub-packages in the source code generation destination package. 包。 Every Oracle package will be reflected by - - A Java package holding classes for formal Java representations of the procedure/function in that package 包 - - A Java class holding convenience methods to facilitate calling those procedures/functions Apart from this, the generated source code looks exactly like the one for standalone procedures/ functions. 功能。 Member functions and procedures in Oracle Oracle UDT's can have object-oriented structures including member functions and procedures. With 同 Oracle, you can do things like this: CREATE OR REPLACE TYPE u_author_type AS OBJECT ( id NUMBER(7), first_name VARCHAR2(50), last_name VARCHAR2(50), MEMBER PROCEDURE LOAD, MEMBER FUNCTION count_books RETURN NUMBER ) ） -- The type body is omitted for the example These member functions and procedures can simply be mapped to Java methods: // Create an empty, attached UDT record from the Factory UAuthorType author = create.newRecord(U_AUTHOR_TYPE); // Set the author ID and load the record using the LOAD procedure author.setId(1); author.load(); // The record is now updated with the LOAD implementation's content assertNotNull(author.getFirstName()); assertNotNull(author.getLastName()); For more details about UDT's see the Manual's section on User Defined Types 2.6. 2.6。 UDT's including ARRAY and ENUM types Increased RDBMS support for UDT's In recent years, most RDBMS have started to implement some support for advanced data types. This 这 support has not been adopted very well by database users in the Java world, for several reasons: Page 37 第37页 The jOOQ User Manual 2.6. 2.6。 UDT's including ARRAY and ENUM types jOOQ is brought to you by Lukas Eder. Distributed under the Apache 2 licence Page 37 / 76 - - They are usually orthogonal to relational concepts. It is not easy to modify a UDT once it is referenced by a table column. - - There is little standard support of accessing them from JDBC (and probably other database connectivity standards). On the other hand, especially with stored procedures, these data types are likely to become more and more useful in the future. If you have a look at Postgres' capabilities of dealing with advanced data types ( （ ENUMs , ARRAYs , UDT's ), this becomes more and more obvious. It is a central strategy for jOOQ, to standardise access to these kinds of types (as well as to stored procedures , of course) across all RDBMS, where these types are supported. UDT types User Defined Types (UDT) are helpful in major RDMBS with lots of proprietary functionality. The biggest player is clearly Oracle. Currently, jOOQ provides UDT support for only two databases: - - Oracle 神谕 - - Postgres Apart from that, - - DB2 UDT's are not supported as they are very tough to serialise/deserialise. We don't think that this is a big enough requirement to put more effort in those, right now (see also the developers' discussion on #164 ) In Oracle, you would define UDTs like this: CREATE TYPE u_street_type AS OBJECT ( street VARCHAR2(100), no VARCHAR2(30) ) ） CREATE TYPE u_address_type AS OBJECT ( street u_street_type, zip VARCHAR2(50), city VARCHAR2(50), country VARCHAR2(50), since DATE, code NUMBER(7) ) ） These types could then be used in tables and/or stored procedures like such: CREATE TABLE t_author ( id NUMBER(7) NOT NULL PRIMARY KEY, -- [...] address u_address_type ) ） CREATE OR REPLACE PROCEDURE p_check_address (address IN OUT u_address_type); Standard JDBC UDT support encourages JDBC-driver developers to implement interfaces such as java.sql.SQLData , java.sql.SQLInput and java.sql.SQLOutput . Those interfaces are non-trivial to implement, or to hook into. Also access to java.sql.Struct is not really simple. Due to the lack of a well- defined JDBC standard, Oracle's JDBC driver rolls their own proprietary methods of dealing with these types. 类型。 jOOQ goes a different way, it hides those facts from you entirely. With jOOQ, the above UDT's will be generated in simple UDT meta-model classes and UDT record classes as such: Page 38 第38页 The jOOQ User Manual 2.6. 2.6。 UDT's including ARRAY and ENUM types jOOQ is brought to you by Lukas Eder. Distributed under the Apache 2 licence Page 38 / 76 // There is an analogy between UDT/Table and UDTRecord/TableRecord... public class UAddressType extends UDTImpl { // The UDT meta-model singleton instance public static final UAddressType U_ADDRESS_TYPE = new UAddressType(); // UDT attributes are modeled as static members. Nested UDT's // behave similarly public static final UDTField STREET = // [...] public static final UDTField ZIP = // [...] public static final UDTField CITY = // [...] public static final UDTField COUNTRY = // [...] public static final UDTField SINCE = // [...] public static final UDTField CODE = // [...] } } Now, when you interact with entities or procedures that hold UDT's, that's very simple as well. Here is an example: 例如： // Fetch any author from the T_AUTHOR table TAuthorRecord author = create.selectFrom(T_AUTHOR).fetchAny(); // Print out the author's address's house number System.out.println(author.getAddress().getStreet().getNo()); A similar thing can be achieved when interacting with the example stored procedure: // Create a new UDTRecord of type U_ADDRESS_TYPE UAddressTypeRecord address = new UAddressTypeRecord(); address.setCountry("Switzerland"); // Call the stored procedure with IN OUT parameter of type U_ADDRESS_TYPE address = Procedures.pCheckAddress(connection, address); ARRAY types The notion of ARRAY types in RDBMS is not standardised at all. Very modern databases (especially the Java-based ones) have implemented ARRAY types exactly as what they are. "ARRAYs of something". In 在 other words, an ARRAY OF VARCHAR would be something very similar to Java's notion of String[]. An 一个 ARRAY OF ARRAY OF VARCHAR would then be a String[][] in Java. Some RDMBS, however, enforce stronger typing and need the explicit creation of types for every ARRAY as well. These are example String[] ARRAY types in various SQL dialects supported by jOOQ 1.5.4: - - Oracle: VARRAY OF VARCHAR2. A strongly typed object encapsulating an ARRAY of a given type. See the documentation. - - Postgres: text[]. Any data type can be turned into an array by suffixing it with []. See the 看到 documentation 文档 - - HSQLDB: VARCHAR ARRAY. Any data type can be turned into an array by suffixing it with ARRAY. See the documentation - - H2: ARRAY. H2 does not know of typed arrays. All ARRAYs are mapped to Object[]. See the 看到 documentation 文档 Soon to be supported: - - DB2: Knows a similar strongly-typed ARRAY type, like Oracle 找好工作，来深圳英才网：http://0755.job1001.com