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.
Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
stmt.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=stmt.executeQuery("select _lockFlag_,* from testlock where int1=1");
// ResultSet rs=stmt.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!");
}
Statement stmt1 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
if(showWrong){
try{
int result = stmt1.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();//Still lock error
}else{
int result=stmt1.executeUpdate("UPDATE testlock set int1=1 where int1=1 and not rowlocked()");
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=stmt1.executeUpdate("UPDATE testlock set int1=1 where int1=1");
//BTW, you can use "UPDATE testlock set int1=int1+1 where ..." in a multi-user. The latest int1 value will be fetched for calculation.
System.out.println("update count:"+result);
}
rs.close();
stmt1.close();
stmt.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 DBF supports such an unadvisable operation, for instance,"insert into table1 select * from table1;".
Bulk Insert A ResultSet from any JDBC driver
The HXTT DBF 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 DBF 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);
}
HXTT DBF supports more than 180 SQL functions. Please email us if you wish
to complement some new SQL functions. HXTT DBF supports also user-defined SQL
functions, and you should use only this feature to provide special SQL functions
in your project.
First, you need to implement com.hxtt.sql.ExtendedFunctionInterface.
public interface ExtendedFunctionInterface {
/**
* Used to verify whether functionName is supported, and has a correct prarameter count.
* @param functionName the name of function
* @param parameters the parameter list of function, which can be null
* @return value
* @throws SQLException if has an incorrect parameter number
*/
public boolean isExtendedFunction(String functionName,Object[] parameters)throws SQLException;
/**
* Used to evaluate function value.
* @param functionName the name of function
* @param values the value list of function, which can be null
* @return value
* @throws SQLException if failed to calculate the function
*/
public Object evaluate(String functionName,Object[] values)throws SQLException;
/**
* Used to get the SQL type of the value that is expected to be returned when evaluate() is called.
* @param functionName
* @return the SQL type or Types.NULL if functionName is supported
*/
public int getType(String functionName);
/**
* Used to get the SQL types of the parameter values that are expected to be returned when evaluate() is called.
* return null if function hasn't any parameter, or you wish to use the default SQL types.
* use Types.NULL for that specific parameter if you wish to get the default SQL type.
* @param functionName
* @return the SQL type list or null if functionName is supported
*/
public int[] getParameterTypes(String functionName);
/**
* Used to estimate the maximum number of characters that should be contained in a String returned by evaluate(String functionName,Object[] values).
* Zero is returned if this value object does not represent Types.VARCHAR, Types.BINARY, Types.LONGVARCHAR, or Types.LONGBINARY.
* @param functionName
* @return maximum size
* @throws SQLException if functionName is supported
*/
public int estimateValueSize(String functionName) throws SQLException;
}
Let us see a sample:
import com.hxtt.sql.ExtendedFunctionInterface;
import java.sql.SQLException;
import java.sql.Types;
/**
* Show how to complement some sql functions.
* This sample complements tostring(value) and random() for demo purpose
*/
public class Functions implements ExtendedFunctionInterface {
public Functions() {
}
/**
* Used to verify whether functionName is supported, and has a correct prarameter count.
* @param functionName the name of function
* @param parameters the parameter list of function, which can be null
* @return value
* @throws SQLException if has an incorrect parameter number
*/
public boolean isExtendedFunction(String functionName, Object[] parameters) throws SQLException {
if (functionName.equalsIgnoreCase("tostring")) {
if (parameters != null && parameters.length == 1) {
return true;
}
else {
throw new SQLException("Invalid parameter value in tostring function");
}
}
else if (functionName.equalsIgnoreCase("random")) {
if (parameters == null) {
return true;
}
else {
throw new SQLException("Invalid parameter value in random function");
}
}
return false;
}
/**
* Used to evaluate function value.
* @param functionName the name of function
* @param values the value list of function, which can be null
* @return value
* @throws SQLException if failed to calculate the function
*/
public Object evaluate(String functionName, Object[] values) throws SQLException {
if (functionName.equalsIgnoreCase("tostring")) {
return values[0] + "";
}
else if (functionName.equalsIgnoreCase("random")) {
return new Double(Math.random());
}
throw new SQLException("Inner Error:(");
}
/**
* Used to get the SQL type of the value that is expected to be returned when evaluate() is called.
* @param functionName
* @return the SQL type or Types.NULL if functionName is supported
*/
public int getType(String functionName) {
if (functionName.equalsIgnoreCase("tostring")) {
return Types.VARCHAR;
}
else if (functionName.equalsIgnoreCase("random")) {
return Types.DOUBLE;
}
return Types.NULL;
}
/**
* Used to get the SQL types of the parameter values that are expected to be returned when evaluate() is called.
* return null if function hasn't any parameter, or you wish to use the default SQL types.
* use Types.NULL for that specific parameter if you wish to get the default SQL type.
* @param functionName
* @return the SQL type list or null if functionName is supported
*/
public int[] getParameterTypes(String functionName) {
if (functionName.equalsIgnoreCase("tostring")) {
return new int[] {
Types.VARCHAR};
}
return null;
}
/**
* Used to estimate the maximum number of characters that should be contained in a String returned by evaluate(String functionName,Object[] values).
* Zero is returned if this value object does not represent Types.VARCHAR, Types.BINARY, Types.LONGVARCHAR, or Types.LONGBINARY.
* @param functionName
* @return maximum size
* @throws SQLException if functionName is supported
*/
public int estimateValueSize(String functionName) throws SQLException {
if (functionName.equalsIgnoreCase("tostring")) {
return 20;
}
else if (functionName.equalsIgnoreCase("random")) {
return 8;
}
return 10;
}
}
Then you can use com.hxtt.sql.dbf.DBFDriver.registerExtendedFunction("Functions"); to regiester Functions class. Then you can use those user-defined functions in SQL. For instance, "select abs(random()),tostring(date) from test;".
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.dbf.DBFDriver").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();
}
}
}