Table features to develop Derby

What's Derby?

Derby is a java-based, lightweight relational database with comprehensive transactional support capabilities. IBM's LinuxWorld in San Francisco in 2004 announced that it was releasing Cloudscape as an open source to Apache Software Foundation (ASF), which created an incubator project called "Derby." The current version is 10.4.2.0.

Derby is a lightweight database. It's about 2M, including the basic database engine and the built-in JDBC driver.

It is based on Java, JDBC, and SQL standards.

It provides an embedded JDBC driver that allows developers to embed Derby in a java-based application solution.

It also has the Derby client JDBC driver and Derby server to support the traditional client/server model of the database.

It is easy to install and use.

Table functions for Derby (derby-style table functions)

In some database products, there are some different implementations of table functions. such as Oracle's table function, it can use pl/sql to produce a result set. However, because Derby itself is based on Java implementation, developers can more easily use the Java language publishing capabilities, so that it has a certain degree of flexibility.

The table feature of Derby enables you to wrap external data into tables in Derby. The external data can be an XML file, a text file, a table in another database, or an RSS subscription file. These information sources can be represented as a JDBC result set (ResultSet).

While developers implement table functionality, most ResultSet methods can be implemented without implementation, but a legitimate Derby-style table function must implement the following methods.

Next ()

Close ()

Wasnull ()

GetXXX ()--when calling a Derby table feature, Derby calls the get*** () method for the corresponding column. The so-called get*** () method is based on the data type of the corresponding column.

The development of Derby table function

Create a table feature

Listing 1. Create a table feature

CREATE　FUNCTION　externalStudents
　 ()
　 RETURNS　TABLE
　 (
　 Id　INT,
　 Name　VARCHAR(　50　),
　 )
　 LANGUAGE　JAVA
　 PARAMETER　STYLE　DERBY_JDBC_RESULT_SET
　 READS　SQL　DATA
　 EXTERNAL　NAME　'com.test.StudentTable.read'

　 public　static　ResultSet　read()　{...}

Using table Features

In order to invoke a table function, we need to put the constructor of a table into the FROM clause of a query. It is important to note that the alias S of this table is the necessary part of the syntax.

INTO　students　SELECT　s.*
　 FROM　TABLE　(externalStudents()　)　s;