Chapter 5. Advanced Programming

Index:

  1. Sending Very Large IN Parameters
  2. Set Record Lock Manually
  3. Table Level Encryption
  4. Bulk Insert
  5. Bulk Insert A ResultSet from any JDBC driver
  6. SELECT ... INTO OUTFILE Syntax
  7. Transaction Processing
  8. RowSet
  9. PooledConnection
  10. SSL Connection
  11. Run HXTT XMLServer as Windows Service or Linux(Solaris) Daemon
  12. DBAdmin (A GUI Dtabase Server Manager)
  13. How to Use Memory-only Table, Physical Table, Url table, Compressed table, SAMBA table in a SQL.
  14. Create Table from any java.io.InputStream object.
  15. Create Database from any compressed java.io.InputStream object.

Sending Very Large IN Parameters

The methods setBytes, setString, setBinaryStream, setAsciiStream, setCharacterStream, setBlob, and setClob are capable of sending unlimited amounts of data. The following code illustrates using a stream to send the contents of a file as an IN parameter.

            String sql="update test SET clob1 = ?, blob1=? WHERE float1>=?*PI()%5 or float1=0";
            java.sql.PreparedStatement pstmt = con.prepareStatement(sql);

            java.io.File file = new java.io.File(dir+"/somechar.txt");
            int fileLength =(int) file.length();
            java.io.InputStream fin = new java.io.FileInputStream(file);
            pstmt.setCharacterStream(1,new java.io.InputStreamReader(fin), fileLength);
            pstmt.setObject(2, "A serialized class");
            pstmt.setFloat(3,0);
            pstmt.executeUpdate();
            pstmt.close();
			

Set Record Lock Manually

SELECT ... FOR UPDATE will lock all the selected rows so that other users cannot lock or update the rows until you close that ResultSet. You can specify FOR UPDATE clause only in a top-level SELECT statement (not in subqueries).

_LockFlag_ is a virtual column for row lock flag. You can use "select _LockFlag_,* from yourTable" to get an Updatable ResultSet, then use three functions below:
boolean ResultSet.setBoolean("_LockFlag_",true)//Lock the current row.
boolean ResultSet.setBoolean("_LockFlag_",false);//Unlock the current row.
boolean ResultSet.getBoolean("_LockFlag_")//indicates whether the current row has been locked by other process or application.
If ResultSet.close() is called, all pending record locks will be released automatically. "update yourTable set _LockFlag_=true where condition", and "update yourTable set _LockFlag_=false where condition" can lock/unlock records too, but you have to take care of every record lock.

            Connection connection1= DriverManager.getConnection("jdbc:DBF:/.",properties);

            Statement stmt1 = connection1.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
            stmt1.executeUpdate("CREATE TABLE IF NOT EXISTS testlock(int1 int,char1 varchar(100));"
                               +"INSERT INTO testlock VALUES(1,'DFFDFSDF');"
                               +"INSERT INTO testlock VALUES(2,'aaaa');"
                               );

            ResultSet rs=stmt1.executeQuery("select _lockFlag_,* from testlock where int1=1");
//            ResultSet rs=stmt1.executeQuery("select recno(),_lockFlag_,* from testlock where int1=1");

            rs.next();

            boolean lockResult=rs.getBoolean("_LockFlag_");//indicates whether the current row has been locked by other process or application
            if(lockResult){
                System.out.println("Maybe other application has locked it!");
            }

            //Through moving the cursor of ResultSet, many rows can be locked  at the same time.
            rs.updateBoolean("_LockFlag_",true);//Lock Row
            rs.updateRow();

            boolean isLockedResult=rs.getBoolean("_lockFlag_");//indicates whether the current row has been locked by other process or application
            if(!isLockedResult){
                System.out.println("It's impossible since the current row is just locked!");
            }

            Connection connection2= DriverManager.getConnection("jdbc:DBF:/.",properties);
            Statement stmt2 = connection2.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
            if(true){//Whether show a wrong code block.
                try{
                    int result = stmt2.executeUpdate(
                        "UPDATE testlock set int1=1 where int1=1");
                    System.out.println("update count:" + result);
                }catch(SQLException e){
                    System.out.println("update error:"+e);//lock error
                }

                rs.updateInt("int1",1);
                rs.updateRow();//Pass since it's locked by rs.
            }else{
                int result=stmt2.executeUpdate("UPDATE testlock set int1=1 where int1=1 and not rowlocked()");
                System.out.println("update count:"+result);

                rs.updateInt("int1",1);
                rs.updateRow();//Pass since it's locked by rs.

                result= connection1.createStatement().executeUpdate("UPDATE testlock set int1=1 where int1=1");//Pass since it's a statement of the same connection.
                System.out.println("update count:"+result);


                rs.updateBoolean("_LockFlag_",false);////Unlock Row
                rs.updateRow();
                isLockedResult=rs.getBoolean("_lockFlag_");//indicates whether the current row has been locked by other process or application
                if(isLockedResult){
                    System.out.println("Falied to unlock the current row!");
                }

                result=stmt2.executeUpdate("UPDATE testlock set int1=1 where int1=1");
                //BTW, you can use "UPDATE testlock set int1=int1+1 where ..." in a multi-user. DBF will fetch the latest int1 value for calculation.
                System.out.println("update count:"+result);
            }

            rs.close();


            stmt2.close();
            connection2.close();

            stmt1.close();
            connection1.close();

Table Level Encryption

If you create table in a connection with crypt properites, those tables will become encrypted tables. You needn't take care too much about encrypt/decrypt since it's a Table LEVEL Encryption.

            properties.setProperty("cryptType", "des");//To specify an crypt type for Table Encryption and Column Level Encryption. All new created table in this connection will become crypted table. You can use DES, TRIDES, and BLOWFISH now. Deafult:null
            properties.setProperty("cryptKey", "123 myKey 456");//To specify an encrypt key. Without encrypt key, CREATE TABLE won't create crypted table.
            properties.setProperty("storeCryptKey", "true");//Indicates whether crypt key is stored in crypted table. If stored, crypted table can be opened automatically in any connection without predefined crypt properites. If not stored, cryptd table can only be opened with correct key. Default:false

            Connection con = DriverManager.getConnection(url,properties);

You needn't encrypt/decrypt a total table sometimes, then you can used some crypt functions to protect your sensitive data:
ENCRYPT(content,cKey,cCryptMethod): Returns a crypted byte[]. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH' now. ENCRYPT function is used for VARBINARY column.
DECRYPT(content,cKey,cCryptMethod): Returns a decrypted byte[]. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH' now.
ENCODE(content): Encodes a BASE64 encoding string.
DECODE(content): Returns a byte[] from a BASE64 string.
ENCODE(content,cKey,cCryptMethod): Crypts and encodes content. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH'. ENCRYPT function is used for VARCHAR column.
DECODE(content,cKey,cCryptMethod): Decodes and decrypts content. cCryptMethod should be 'DES', 'TRIDES', or 'BLOWFISH' now.
For instance:

select encode('adsdfsdf');
select decode(encode('adsdfsdf'))+'';
select decode(encode('dfdffd233','12345','trides'),'12345','trides')+':('
select decrypt(encrypt('25355','12345','trides'),'12345','trides')+':('
select decrypt(encrypt('25355','12345','des'),'12345','des')+':('
select decrypt(passwd,'12345','des') from test;
insert into users (user,passwd) values('abc',encode('abcpasswd','a key','trides');
select count(*) from user where users=? and passwd=encode(?,'a key','trides');
select count(*) from user where users=? and decode(passwd,'a key','trides')=?;

VARBINARY's Encrypted Data Column Length=Maximum length of the non-encrypted data + 1 byte + The number of bytes to the next 8-byte boundary. For instance, your data is 8 byte, you can use varbinary of 9 byte length (or binary of 8 byte) to stored the encrypted data. Your data is 12 byte, you can use varbinary of 17 byte length to stored the encrypted data. VARCHAR's Encrypted Data Column Length= (VARBINARY's Encrypted Data Column Length)*4/3. For instance, your data is 8 byte, you need 12 byte to stored the BASE64 encoding encrypted data.

Bulk Insert

"CREATE TABLE [IF NOT EXISTS] table-name [(column-identifier data-type [constraint] [,...])] [AS] [SELECT query]", and "INSERT INTO table-name [ ( column-identifier [,...] ) ] SELECT query" can copy a table to another table or allow insert of multiple rows in one statement. For instance, "CREATE TABLE newtable select * from table1 where column1!=null order by column2;", and "insert into test (int1,char1) select id1,name1 from abc where id1>50 and value1>300". SQL does't permit that table1 is the same table as table2 when INSERT INTO table1 select * from table2, but the HXTT XML supports such an unadvisable operation, for instance,"insert into table1 select * from table1;".

Bulk Insert A ResultSet from any JDBC driver

The HXTT XML supports to insert data from other JDBC drivers. "CREATE TABLE [IF NOT EXISTS] table-name [(column-identifier data-type [constraint] [,...])] ?", and "INSERT INTO table-name [ ( column-identifier [,...] ) ] ?" is for that purpose.

            //rs is an open ResultSet from any JDBC driver.
            String sql="insert into test ?;";

            PreparedStatement pstmt = con.prepareStatement(sql);

            pstmt.setObject(1,rs);//insert a resultSet into table test.
            pstmt.executeUpdate();

            pstmt.close();


            sql="create table if not exists abcd ?;";
            pstmt = con.prepareStatement(sql);

            pstmt.setObject(1,rs);//insert a resultSet into a new table abcd
            pstmt.executeUpdate();
			
            pstmt.close();

Notes: If your ResultSet.getType()==ResultSet.TYPE_FORWARD_ONLY, and you have used ResultSet.next() to browsed some rows, you won't insert those browsed rows. Other conditions, all rows will be inserted.

BTW, the HXTT XML driver's result set is Serializable.

    // serialize the resultSet
    try {
        java.io.FileOutputStream fileOutputStream = new java.io.FileOutputStream("yourfile.tmp");
        java.io.ObjectOutputStream objectOutputStream = new java.io.ObjectOutputStream(fileOutputStream);
        objectOutputStream.writeObject(rs);
        objectOutputStream.flush();
        objectOutputStream.close();
        fileOutputStream.close();
    }
    catch (Exception e) {
        System.out.println(e);
        e.printStackTrace();
        System.exit(1);
    }


    // deserialize the resultSet
    try {
        java.io.FileInputStream fileInputStream = new java.io.FileInputStream("yourfile.tmp");
        java.io.ObjectInputStream objectInputStream = new java.io.ObjectInputStream(fileInputStream);
        rs = (ResultSet) objectInputStream.readObject();
        objectInputStream.close();
        fileInputStream.close();
    }
    catch (Exception e) {
        System.out.println(e);
        e.printStackTrace();
        System.exit(1);
    }

 

SELECT ... INTO OUTFILE Syntax

SELECT ... INTO OUTFILE writes the selected rows to a file. SELECT [ALL | DISTINCT [ ON ( expression [, ...] ) ] ] | DISTINCTROW [TOP n [PERCENT]] select_list [INTO variable [, ...] | INTO OUTFILE 'outfileName' ] FROM table_reference_list [WHERE condition_expression] [[START WITH initial-condition] CONNECT BY [NOCYCLE] recurse-condition] [group_by_clause] [HAVING condition_expression] [union_clause] [order_by_clause] [LIMIT [offset,] [limit] ] [FOR UPDATE]
'outfileName' support CSV and XML format. For instance:
    SELECT * into OUTFILE 'A' FROM USER_PLANS; /* Default suffix: csv */
    SELECT * into OUTFILE 'A.xml' FROM USER_PLANS; /* It will use XML format now. */
    SELECT 'A''A' AS BB,12345 AS C,now() as e into OUTFILE 'b.CSV' ;
    SELECT * into OUTFILE 'c.CSV' FROM USER_PLANS LEFT OUTER JOIN (SELECT * FROM USER_FUNCTION_DESCRIPTIONS WHERE UFD_IS_PLAN='Y' AND UFD_LANG='EN')PLAN_DESC ON USER_PLANS.UPL_ID=PLAN_DESC.UFD_USF_ID WHERE UPL_UFL_ID=-1.0 ORDER BY UPL_NAME
Because that SELECT ... INTO OUTFILE Syntax need to write file, so that it will throw SQLException if Connection.isReadOnly(). ResultSet.getRow() will show the number of rows exported to the file. The ResultSet object can be reused in code if it's not ResultSet.TYPE_FORWARD_ONLY type. Connection properties can be specified to produce a specific output format.
Property Name
Definition
Default Value
charSet To specify a Character Encoding Scheme other than the client default. You can find a Supported Encodings list of file:///c|/jdk1.2/docs/guide/internat/encoding.doc.html. Cp895(Czech MS - DOS 895), Cp620(Polish MS - DOS 620) and Mazovia are extra supported although JVM doesn't support those. null
csvfileExtension To specify other suffix as default csv file extension. CSV
_CSV_Separator To specify a character sequence used to separate the values of the fields. It supports escape processing so that you can use \t, \r, \u001A, \x32, and so on. ,
_CSV_EOL To specify 1~2 character sequence to terminate one line. An end-of-line line sequence is any one of a line feed ('\n', 0x0A), a carriage return ('\r', 0x0D), or a carriage return followed immediately by a linefeed. In most occasions, you needn't to care that connection property, since HXTT CSV supports even to mix three styles in one file. For data update, HXTT CSV can detect automatically OS version and choose the suitable EOL style in UNIX-style, DOS-style, and Mac-style. If you wish to create unix-style file on Windows, then you need to assign that connection porperty. It supports escape processing so that you can use \t, \r, \u001A, \x32, and so on. For instance, your data file is using $ as EOL, HXTT CSV can support that file. null
_CSV_Quoter To specify a character used to quote the string value. It supports escape processing so that you can use \t, \r, \u001A, \x32, and so on. "
_CSV_Header Indicates whether the first record of text file to consist of the names of the fields in the data following. true
_CSV_Comment To specify whether there're some comment lines before csv header and data rows. If you use n (integer values), the first n lines will be ingored as comment. If you use some paragraphs, these paragraphs will be used for csv file creation, the total line number of comment will be used for existent file. null
locale locale is used to specify a default local for parse. You can use CANADA, CANADA_FRENCH, CHINA, CHINESE, ENGLISH, FRANCE, FRENCH, GERMAN, GERMANY, ITALIAN, ITALY, JAPAN, JAPANESE, KOREA, KOREAN, PRC, ROOT, SIMPLIFIED_CHINESE, TAIWAN, TRADITIONAL_CHINESE, UK, or US. null
dateFormat dateFormat is used to specify a default parse sequence of date(Default: 'yyyy-MM-dd') format. yyyy-MM-dd
timeFormat timeFormat is used to specify a default parse sequence of time(Default: 'hh:mm:ss') format. hh:mm:ss
timestampFormat timestampFormat is used to specify a default parse sequence of timestamp(Default: 'yyyy-MM-dd hh:mm:ss') format. yyyy-MM-dd hh:mm:ss
decimalFormat decimalFormat is used to specify a default parse sequence of decimal number format. null
decimalSeparator decimalSeparator is used to specify a default character for decimal sign. Different for French, etc. null
groupingSeparator groupingSeparator is used to specify a default character for thousands separator. Different for French, etc. null

 

RowSet

com.hxtt.sql.HxttRowSet can work with any descendent class of java.sql.DataSource. For instance:



import java.sql.*;
import java.util.Properties;


import com.hxtt.sql.HxttDataSource;
import com.hxtt.sql.HxttRowSet;

public class testRowSet{
    public static void main(String argv[]){
        try{
            Class.forName("com.hxtt.sql.xml.XMLDriver").newInstance();

            HxttDataSource ds=new HxttDataSource();
            ds.setUrl("jdbc:xml:/f:/xmliles");

            HxttRowSet rowSet=new HxttRowSet(ds);
            /*
             Another way:
              HxttRowSet rowSet=new HxttRowSet();
              rowSet.setDataSourceName(dsName);
              will use
                         Context ctx = new InitialContext();
                         return (DataSource) ctx.lookup(dataSourceName);
               to load the ds.
             */

            rowSet.setCommand("select * from test");

            rowSet.execute();

            ResultSetMetaData resultSetMetaData = rowSet.getMetaData();
            int iNumCols = resultSetMetaData.getColumnCount();
            for (int i = 1; i <= iNumCols; i++) {
                System.out.println(resultSetMetaData.
                    getColumnLabel(i)
                    + "  " +
                    resultSetMetaData.getColumnTypeName(i));
            }

            rowSet.beforeFirst();
            while (rowSet.next()) {
                for (int i = 1; i <= iNumCols; i++) {
                    System.out.print(rowSet.getObject(i) + "  ");
                }
                System.out.println();
            }

            rowSet.close();

        }
        catch( SQLException sqle )
        {
            do
            {
                System.out.println(sqle.getMessage());
                System.out.println("Error Code:"+sqle.getErrorCode());
                System.out.println("SQL State:"+sqle.getSQLState());
                sqle.printStackTrace();
            }while((sqle=sqle.getNextException())!=null);
        }
        catch( Exception e )
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}

 

PooledConnection

For instance:

            com.hxtt.sql.HxttConnectionPoolDataSource pds=new com.hxtt.sql.HxttConnectionPoolDataSource();
            pds.setUrl("jdbc:dbf:/f:/dbffiles");
            javax.sql.PooledConnection pc=pds.getPooledConnection();

 

SSL Connection

SSL Connection has been provided since JDK1.4.X. To use SSL Connection, you should know how to use javax.net.ssl package first. With hxtt.socketclass=SSL system property, all of HXTT XMLServer's receiving connections in one JVM will become SSL connection. For client side, using hxtt.socketclass=SSL or hxtt.socketclass=null as connection property will overlay hxtt.socketclass system property so that it's possible that some connections are SSL encryption connection, but other connections are common connections or customer connections.
For instnace, you can use java -Djavax.net.ssl.keyStore=yourKeyStore -Djavax.net.ssl.keyStorePassword=yourKeyStorePassword -Djavax.net.ssl.trustStore=yourTruststore -Djavax.net.ssl.trustStorePassword=yourTrustStorePassword -Dhxtt.socketclass=ssl -cp yourClassPath com.hxtt.sql.admin.Admin to start a HXTT XMLServer with SSL Connection capability. If you wish to use HXTT XMLServer as Linux(Solaris) dameon or Windows Service without GUI, you should read Run HXTT XMLServer as Windows Service or Linux(Solaris) Daemon too.
java -Djavax.net.ssl.trustStore=yourTruststore -Djavax.net.ssl.trustStorePassword=yourTrustStorePassword -Dhxtt.socketclass=ssl -cp yourClassPath yourApplication will let your application to use SSL for remote connection.
If you wish to write customer connection, please click Customer Connection.

 

Run HXTT XMLServer as Windows Service or Linux(Solaris) Daemon

In Linux(Solaris), we assume that you save it to /jdbclib directory.
In Windows, we assume it is c:/ . You should have built the database server configuration by com.hxtt.sql.admin.Admin program. It will create a file named
urlconfig.properties which locate on the user home directory.
For example, in Linux(Solaris), you build the database server configuration in root user, the urlconfig.properties will located
at /root directory if the root's home directory is /root; in windows, it will be the C:\Documents and Settings\Administrator.
You should copy the file to other directory for the service program maybe not access the file. In Linux(Solaris), we assume you copy it
to /jdbclib;in windows,we assume it is c:/.

For Windows NT/2000/2003/XP system, you can use JavaService.exe(Here to download, here is its forge) to register a window service to start the servers.
   Here is a simple bat file to tell you how to register a service, you should change some options
   accord your enviromnent. After you download these two files, you can run the bat file to register and start the
   service at the Control Panel.

For Windows 7, Windows 2008, and Vista system, you can copy jsl64.exe(Here to download, here is its home) and jsl64.ini(Here to download) into somewhere on your disk.
   Modify the jsl64.ini to reflect your desired settings. Specially the last line cmdline parameters.
   Use Administrator Role to run jsl64.exe -install jsl64.ini
   Start it in the NT service manager or by calling NET start "HXTTService"

In Linux(Solaris),you can use jsvc(Here to download) as a daemon to start the servers for remote connection.
1.You should download the Apache common daemons package(Here to download).
We assume that you save this two files to /commondaemon directory.
2.please run the follows command to enable the exec file property.
chmod +x /commondaemon/jsvc
Attention,the jsvc program has tested at RedHat 9.0 and Sun Open Desktop System 1.0.If it don't work at
your enviroment,please download the jsvc source and make a binary program or tell us your environment.
3.run the follows command to know the default run level of your machine.
cat /etc/inittab | grep :initdefault
it's result will be as follows: id:3:initdefault
or
runlevel
it's result will be as follows:N 3
In common,the default run level should be 3 or 5.
4.Please download the hxttjsvcserv script to save it to /etc/init.d directory and run the follows command to enable the file exec-able bit mask .
chmod +x /etc/init.d/hxttjsvcserv
Attension ,if you don't put HXTT XML Package to /jdbclib directory or jsvc and commons-daemon.jar to /commondaemon directory,you should modify the
hxttjsvcserv file to fit your configuration.
BTW,the default user run this service is root,maybe you should changed it to another low right user.Please see the dbfjsvcserv for more detail information.
5.cd /etc/rcx.d (x is the run level,in some os,the rcx.d is not directly located in /etc directory,you can use find . -name rcx.d to find where is it)
At first you should list all the file for find the new service's running sequence number;
run the command
ls
You will see some files which starts with K or S,for example,S99local and K99local.
S99local is the run script file when start this machine.
K99local is the stop script file when shut down this machine.
local is the service name.K represent kill and S represent the start.
This two files all are a file linked to /etc/init.d/local.This is,when starting machine,OS will run local
script with start parameter and when stopping with stop parameter.
99 is the run sequence number when start this machine.
For example,httpd service will start before this local service and stop after the local service for its start
script file name is S15httpd and end script file name is K15httpd.
Find the max running sequence number,in my machine,it is 99,so the new service's running sequence number will be 100.
run the command to build this two file.
ln -s /etc/init.d/hxttjsvcserv S100hxttjsvcserv
ln -s /etc/init.d/hxttjsvcserv K100hxttjsvcserv
now you can run /etc/init.d/hxttjsvcserv start to start the service or reboot your machine to test if this service can auto start.
You can use "java com.hxtt.sql.admin.Admin TCPCLIENT [host:]port [remoteControlPassword]" to start your remote control when XMLServer is running as Windows service or Linux(Solaris) Daemon.
For Novell Netware OS console without GUI, you can also run directly com.hxtt.sql.admin.HxttService with above same parameters.
On LINUX and UNIX, if you got "Cannot connect to X11 window server. The environment variable DISPLAY is not set.", you should use -Djava.awt.headless=true to run Java in headless mode.
If startup using jsvc is not implemented on a specific operation system, you can also run directly com.hxtt.sql.admin.HxttService with above same parameters on SCO OpenServer, OS/400, and so on.

How to Use Memory-only Table, Physical Table, Url table, Compressed table, SAMBA table in a SQL.

1. Compressed Database:(.ZIP, .JAR, .GZ, .TAR, .BZ2, .TGZ, .TAR.GZ, .TAR.BZ2)
    jdbc url format is the same as embedded url and remote url.For example, "jdbc:xml:/c:/test/testxml.zip ,then you can use slect * from aTable to visit aTable table in testxml.zip file.
    No special requirement for sql. Both of the compressed file name and directory name in compressed file are also used as catalog name. For instance, "jdbc:xml:/c:/test", select * from "testxml.zip".a; select * from "testxml.zip/files/a.csv"; select * from "b.tar.bz2/java"."history.txt";
    For TAR and BZ2 support, you should download Apache's tarbz2.jar package.
    You can use compressed table in sql with the common table. For instance, select * from "testxml.zip/files/a.csv",test;
    For case-insensitive sql compatibility, all name of directory and file in compressed file are case-insensitive too.
    Compressed database is reaonly, and all data modification won't be flushed into compressed file.

2. Memory-only Database:
    jdbc url: jdbc:xml:/_memory_/
    No special requirement for sql. For instance, create table abc (a char(10));insert into abc values(333);select * from abc;drop table abc;
    Memory-only database is hold commonly in memory, but it will be stored into temporary directory if its length exceed 8MB limitation to avoid memory overburden.
    _memory_ is a speical catalog name for memory-only database. Through _memory_ catalog, memory-only database is visible for all applications in the same JVM. For instance, in an embedded connection, you can use create table _memory_.abc (a char(10));insert into _memory_.abc values(333);select * from _memory_.abc;drop table _memory_.abc; to do the same things.
    You can use memory-only table in sql with the common table. For instance, select * from _memory_.abc,test;
    Memory-only database is volatile, and you can't see your old data any more after restart a JVM.

3. URL Database:(http protocol, https protocol, ftp protocol, sftp protocol)
    jdbc:xml:http://httpURL
    jdbc:xml:https://httpsURL
    jdbc:xml:ftp://ftpURL
    jdbc:xml:sftp://sftpURL
    For example, "jdbc:xml:http://www.hxtt.com/test", then you can use "slect * from aTable to visit aTable table. Because All of http, https, ftp protocol, and sftp protocol are case-sensitive, you have to take care of your sql, and use proper table file suffix to avoid FileNotFound exception. //Note: FTP site's user/password should be set in ftpURL, and cannot be set in JDBC connection property because user/password JDBC connection property belongs to server/client connection.
    Without URL database url, you can access url database in an embedded connection too. For instance, select * from "http://www.hxtt.com/test/a.tar".a; select * from "http://www.hxtt.com/test/a.jsp?aaa=33";select * from "sftp://testa:123456@localhost/testabc";
    You can use url table in sql with the common table. For instance, select * from "http://www.hxtt.com/test/a.tar".a,abc;
    URL database is reaonly, and all data modification won't be flushed into URL content. If you're using a dial-up network, don't waste time to access too big URL database.
    For https support in JDK 1.2.x and 1.3.x, you should download JSSE 1.0.3 package.
    For sftp support, you should download JSch - Java Secure Channel Library or JSch for J2ME if you are using olderJDBC1.2 or JDBC2.0 package, which is developed by Atsuhiko Yamanaka of JCraft, Inc. .
    For fpt url, HXTT XML supports extra ftp mode=active|passive and detectsize=true|false parameters. For instance jdbc:xml:ftp://ausername:apasswordd@127.0.0.1/test/abc.xml;mode=passive;detectsize=true",
jdbc:xml:ftp://192.168.1.1:5005/test/abc.xml;mode=active",

4. SAMBA Database:(smb protocol)
    jdbc:xml:smb://[[[domain;]username[:password]@]server[:port]/[[share/[dir/]file]]][?[param=value]]
        For example, "jdbc:xml:smb://test1:123@100.100.13.94/xmlfiles", then you can use "slect * from aTable to visit aTable table. Note: SAMBA user/password should be set in SMB URL, and cannot be set in JDBC connection property because user/password JDBC connection property belongs to server/client connection.
    Without SAMBA database url, you can access SAMBA database in an embedded connection too. For instance, select * from "smb://test1:123@100.100.13.94/xmlfiles/zone"
    You can use SAMBA table in sql with the common table. For instance, select * from "smb://test1:123@100.100.13.94/xmlfiles".zone,abc;
    For SAMBA support, you should download Java CIFS Client Library, which is developed by Michael B. Allen.

    HXTT XML supports seamlessly data mining on memory-only table, physical table, url table, compressed table, SAMBA table in a sql. A compressed database can be a URL database or SAMBA database at the same time. It's powerful, and you should ask for HXTT's support if you don't know how to use it for special purpose.

Create Table from any java.io.InputStream object

At Bulk Insert A ResultSet from any JDBC driver, we discuss how to use "CREATE TABLE [IF NOT EXISTS] table-name [(column-identifier data-type [constraint] [,...])] ?" to create a table from any JDBC ResultSet. In fact, that sql syntax can be used to copy and create a table from any java.io.InputStream object. Let's see a sample, which can create a physical table or a memory-only table from an HTTP stream.

package test.jdbc.xml;

import java.net.URL;
import java.net.URLConnection;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class testInputCreateTable{
    public static void main(String argv[]) {
        try {
            Class.forName("com.hxtt.sql.xml.XMLDriver").newInstance();

            String url="jdbc:xml:///_memory_/";//Enabled it if you have not disk access right.
//            String url="jdbc:xml:///xmlfiles/";

            
            Properties properties=new Properties();
            properties.setProperty("tmpdir","_memory_");//Enabled it if you have not disk access right.
            properties.setProperty("delayedClose","-1");//Release database at once.
            Connection con = DriverManager.getConnection(url,properties);

            String sql;
            PreparedStatement pstmt;

            sql="create table testaaa ?";
            pstmt = con.prepareStatement(sql);

            URL httpurl=new URL("http://www.hxtt.com/test/abc.xml");

            URLConnection urlConnection=httpurl.openConnection();
            InputStream is=urlConnection.getInputStream();
            pstmt.setObject(1,is);//create a table from a HTTP stream
            pstmt.executeUpdate();

            pstmt.close();
            is.close();

            sql = "select * from testaaa";

            Statement stmt=con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            ResultSetMetaData resultSetMetaData = rs.getMetaData();
            int iNumCols = resultSetMetaData.getColumnCount();
            for (int j = 1; j <= iNumCols; j++) {
                System.out.println(resultSetMetaData.getColumnLabel(j)
                    + "  " + resultSetMetaData.getColumnTypeName(j)
                    + "  " + resultSetMetaData.getColumnDisplaySize(j)
                    );
            }
            Object colval;

            rs.beforeFirst();
            long ncount = 0;
            while (rs.next()) {
                ncount++;
                for (int j = 1; j <= iNumCols; j++) {
                    colval = rs.getObject(j);
                    System.out.print(colval + "  ");
                }
                System.out.println();
            }
            System.out.println("row count:"+ncount);

            rs.close();

            
            stmt.execute("drop table testaaa");//remove that testaaa table.

            stmt.close();

            con.close();
        }
        catch( SQLException sqle )
        {
            do
            {
                System.out.println(sqle.getMessage());
                System.out.println("Error Code:"+sqle.getErrorCode());
                System.out.println("SQL State:"+sqle.getSQLState());
                sqle.printStackTrace();
            }while((sqle=sqle.getNextException())!=null);
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}

Create Database from any compressed java.io.InputStream object

"CREATE DATABASE [IF NOT EXISTS] file-name ?" sql can be used to create a database from any compressed java.io.InputStream object. Let's see a sample, which can create a physical database or a memory-only database from a compressed HTTP stream.

package test.jdbc.xml;

import java.net.URL;
import java.net.URLConnection;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class testInputCreateDatabase{
    public static void main(String argv[]) {
        try {
            Class.forName("com.hxtt.sql.xml.XMLDriver").newInstance();

            String url="jdbc:xml:///_memory_/";//Enabled it if you have not disk access right.
//            String url="jdbc:xml:///xmlfiles/";

            
            Properties properties=new Properties();
            properties.setProperty("tmpdir","_memory_");//Enabled it if you have not disk access right.
            properties.setProperty("delayedClose","-1");//Release database at once.

            Connection con = DriverManager.getConnection(url,properties);

            String sql;
            PreparedStatement pstmt;

            sql="create database [testaaa.zip] ?";

            pstmt = con.prepareStatement(sql);

            URL httpurl=new URL("http://www.hxtt.com/test/xmldb.zip");

            URLConnection urlConnection=httpurl.openConnection();
            InputStream is=urlConnection.getInputStream();
            pstmt.setObject(1,is);//create a table from a HTTP stream
            pstmt.executeUpdate();

            pstmt.close();
            is.close();

            sql = "select * from [testaaa.zip].abc";

            Statement stmt=con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            ResultSetMetaData resultSetMetaData = rs.getMetaData();
            int iNumCols = resultSetMetaData.getColumnCount();
            for (int j = 1; j <= iNumCols; j++) {
                System.out.println(resultSetMetaData.getColumnLabel(j)
                    + "  " + resultSetMetaData.getColumnTypeName(j)
                    + "  " + resultSetMetaData.getColumnDisplaySize(j)
                    );
            }
            Object colval;

            rs.beforeFirst();
            long ncount = 0;
            while (rs.next()) {
                ncount++;
                for (int j = 1; j <= iNumCols; j++) {
                    colval = rs.getObject(j);
                    System.out.print(colval + "  ");
                }
                System.out.println();
            }
            System.out.println("row count:"+ncount);

            rs.close();
            
            stmt.execute("drop database [testaaa.zip]");//remove that testaaa database.

            stmt.close();

            con.close();
        }
        catch( SQLException sqle )
        {
            do
            {
                System.out.println(sqle.getMessage());
                System.out.println("Error Code:"+sqle.getErrorCode());
                System.out.println("SQL State:"+sqle.getSQLState());
                sqle.printStackTrace();
            }while((sqle=sqle.getNextException())!=null);
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}
Copyright © 1999-2012 Hongxin Technology & Trade Ltd. | All Rights Reserved. |