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 Access supports such an unadvisable operation, for instance,"insert into table1 select * from table1;".
Bulk Insert A ResultSet from any JDBC driver
The HXTT Access 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 Access 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.access.AccessDriver").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 AccessServer'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 AccessServer with SSL Connection capability. If you wish to use HXTT AccessServer as Linux(Solaris) dameon or Windows Service without GUI, you should read Run HXTT AccessServer 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 AccessServer 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 Access 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.