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();
_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();
If you create table in a connection with crypt properites, those table 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:
?encode('adsdfsdf');
?decode(encode('adsdfsdf'))+'';
?decode(encode('dfdffd233','12345','trides'),'12345','trides')+':('
?decrypt(encrypt('25355','12345','trides'),'12345','trides')+':('
?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 Excel supports such an unadvisable operation, for instance,"insert into table1 select * from table1;".
Bulk Insert A ResultSet from any JDBC driver
The HXTT Excel 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 Excel 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);
}
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;
/* show how to use dateFormat for text table */
public class testRowSet{
public static void main(String argv[]){
try{
Class.forName("com.hxtt.sql.excel.ExcelDriver").newInstance();
HxttDataSource ds=new HxttDataSource();
ds.setUrl("jdbc:dbf:/f:/dbfiles");
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:dbf:/f:/dbffiles");
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 ExcelServer'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 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 yourClassesPath com.hxtt.sql.admin.Admin to start a HXTT ExcelServer with SSL Connection capability. If you wish to use HXTT ExcelServer as Linux(Solaris) dameon or Windows Service without GUI, you should read Run HXTT ExcelServer as Windows Service or Linux(Solaris) Daemon too.
java -Djavax.net.ssl.trustStore=yourTruststore -Djavax.net.ssl.trustStorePassword=yourTrustStorePassword -Dhxtt.socketclass=ssl -cp yourClassesPath yourApplication will let your application to use SSL for remote connection.
If you wish to write customer connection, please click Customer Connection.
Run HXTT ExcelServer 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:/.
In windows,you can use JavaService.exe(Here to download) 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.
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 Excel 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.
How to Use Memory-only Table, Physical Table, Url table, Compressed 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:excel:/c:/test/testexcel.zip ,then you can use slect * from aTable to visit aTable table in testexcel.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:excel:/c:/test", select * from "testexcel.zip".a; select * from "testexcel.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 "testexcel.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:excel:/_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 2MB 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, ftp protocol)
jdbc:excel:http://httpURL
dbc:excel:ftp://ftpURL
For example, "jdbc:excel:http://www.hxtt.com/test", then you can use "slect * from aTable to visit aTable table. Because both of http and ftp protocol are case-sensitive, you have to take care of your sql, and use proper table file suffix to avoid FileNotFound exception.
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"
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.
HXTT Excel supports seamlessly data mining on memory-only table, physical table, url table, compressed table in a sql. A compressed database can be a URL 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 creates a physical table and a memory-only table from a HTTP stream.
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 testInputCreate{
private static void test(String url){
System.out.println(url);
try {
Class.forName("com.hxtt.sql.text.TextDriver").newInstance();
Properties properties=new Properties();
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/a.csv");
URLConnection urlConnection=httpurl.openConnection();
InputStream is=urlConnection.getInputStream();
pstmt.setObject(1,is);//create a table from a HTTP stream
pstmt.executeUpdate();
pstmt.close();
in.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();
}
}
public static void main(String argv[]) {
test("jdbc:csv:/f:/textfiles/");
test("jdbc:csv:/_memory_/");
}
}