Chapter 5. Advanced Programming
Index:
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();
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.
"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 Json supports such an unadvisable operation, for instance,"insert into table1 select * from table1;".
Bulk Insert A ResultSet from any JDBC driver
The HXTT Json 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 Json 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, JSON, 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 |
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.json.JsonDriver").newInstance();
HxttDataSource ds=new HxttDataSource();
ds.setUrl("jdbc:json:/f:/jsonfiles");
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();
}
}
}
For instance:
com.hxtt.sql.HxttConnectionPoolDataSource pds=new com.hxtt.sql.HxttConnectionPoolDataSource();
pds.setUrl("jdbc:json:/f:/jsonfiles");
javax.sql.PooledConnection pc=pds.getPooledConnection();
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 JsonServer'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 JsonServer with SSL Connection capability. If you wish to use HXTT JsonServer as Linux(Solaris) dameon or Windows Service without GUI, you should read Run HXTT JsonServer 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 JsonServer 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 Json 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 hxttjsvcserv 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 CobolServer 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, .7z)
jdbc url format is the same as embedded url and remote url.For example, "jdbc:json:/c:/test/testjson.zip ,then you can use slect * from aTable to visit aTable table in testjson.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 TAR and BZ2 support, you should download Apache's tarbz2.jar package.
For 7z support, you should download Apache's sevenz.jar package.
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.
You can union compressed table in sql with the common table.
For instance, "jdbc:json:/c:/test",
select * from "testjson.zip/files/a.json";
select * from "b.tar.bz2/java"."test.json";
2. Memory-only Database:
jdbc url: jdbc:json:/_memory_/
No special requirement for sql.
For instance, create database ajson; create table ajson.abc (a char(10));insert into ajson.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
For instance, create database _memory_.ajson; create table _memory_.ajson.abc (a char(10));insert into _memory_.ajson.abc values(333);select * from _memory_.ajson.abc;drop database _memory_.ajson;
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:json:http://httpURL
jdbc:json:https://httpsURL
jdbc:json:ftp://ftpURL
jdbc:json:sftp://sftpURL
For example, "jdbc:json:http://www.hxtt.com/test", then you can use
"
slect * from json.aTable;
". 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/b.tar/a.json"; select * from "http://www.hxtt.com/test/a.jsp?aaa=33";select * from "sftp://testa:123456@localhost/a.json";
You can use url table in sql with the common table. For instance,
select * from "http://www.hxtt.com/test/b.tar/a.json",aloclTable;
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 Cobol supports extra ftp mode=active|passive and detectsize=true|false parameters. For instance
jdbc:json:ftp://ausername:apasswordd@127.0.0.1/test/abc.json;mode=passive;detectsize=true",
jdbc:json:ftp://192.168.1.1:5005/test/abc.json;mode=active",
4. SAMBA Database:(smb protocol)
jdbc:json:smb://[[[domain;]username[:password]@]server[:port]/[[share/[dir/]file]]][?[param=value]]
For example, "jdbc:json:smb://test1:123@100.100.13.94/jsonfiles", then you can use "
slect * from json.aTable;
to visit 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/jsonfiles/zone.json"
You can use SAMBA table in sql with the common table. For instance,
select * from "smb://test1:123@100.100.13.94/jsonfiles/zone.json",aLocalTable
For SAMBA support, you can download Java CIFS Client Library v1.3.x, which is developed by Michael B. Allen. If you need also SMB2/SMB3 support, you should download Java CIFS Client Library v2.1.x.
HXTT Json 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 Database from any java.io.InputStream object
"CREATE DATABASE [IF NOT EXISTS] datbase-name ?" sql can be used to create a database from any java.io.InputStream object. Let's see a sample, which can create a physical database or a memory-only database from an HTTP stream.
package test.jdbc.json;
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.json.JsonDriver").newInstance();
String url="jdbc:json:///_memory_/";//Enabled it if you have not disk access right.
// String url="jdbc:json:///jsonfiles/";
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 ?";
pstmt = con.prepareStatement(sql);
URL httpurl=new URL("http://www.hxtt.com/test/abc.json");
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.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");//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();
}
}
}
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.json;
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.json.JsonDriver").newInstance();
String url="jdbc:json:///_memory_/";//Enabled it if you have not disk access right.
// String url="jdbc:json:///jsonfiles/";
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/jsondb.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";
sql = "select * from \"testaaa.zip\efg.json".page1";
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();
}
}
}