HXTT Excel packages include a Type 4 JDBC driver. Type 4 indicates that the driver
is written in Pure Java, and communicates in the database system's own network
protocol. Because of this, the driver is platform independent; once compiled,
the driver can be used on any system. HXTT Excel can run on any platforms with
Java VM, which includes Microsoft Windows, Novell Netware, OS2, UNIX, and LINUX.
HXTT Excel supports Personal Java, JDK1.0.X, JDK1.1.X, JDK1.2.X, JDK1.3.X, JDK1.4.X, JDK1.5.X, JDK1.6.X, and JDK1.7.X. HXTT Excel includes a database engine which
can support multi-user access. It supports { UNION | INTERSECT | EXCEPT | MINUS
} [ ALL ] query , INNER JOIN, FULL JOIN, LEFT JOIN, RIGHT JOIN, NATURAL JOIN,
CROSS JOIN, and subquery which includes single-row subquery, multirow subquery,
multiple-column subquery, inline views, and correlated subquery.

When java loads any class, it searches a list known as the classpath. This
is a list of directories where classes are placed, or a list of jar files (archives
containing classes and other resources) or both. HXTT Excel driver is a Type
4 driver. You can do this in many different methods, but the most command are:

Setting the CLASSPATH environment variable.

putting it on the command line using the -cp parameter.

placing it in the JVM's lib/ext directory.

extract all files in jar file into the directory of your application.

You can know detailed information about "Setting the Classpath"
from your JDK Tools and Utilities. Let's use JDBC3.0 package as a simple sample.
To put Excel_JDBC30.jar into your class path, you should use "export
CLASSPATH=/usr/share/lib/Excel_JDBC30.jar:$CLASSPATH" on Solaris
and Linux, and "SET CLASSPATH=\javalib\Excel_JDBC30.jar;%classpath%"
on Windows.

Any source that uses JDBC needs to import the java.sql package by using "
import java.sql.*;".

HXTT Excel driver' name is com.hxtt.sql.excel.ExcelDriver, and you can uses it without
involving hard coding the driver into your code. You do this by setting the
jdbc.drivers system property. For example, for command line apps you can use:
java -Djdbc.drivers=com.hxtt.sql.excel.ExcelDriver yourApp
Then, the JVM upon startup will load the drivers automatically. Some applications
(JBoss, Tomcat etc) support a .properties file which they use to save putting
this on the command line.

The second method is the most common and involves you loading the driver yourself.
It's simple:Class.forName("com.hxtt.sql.excel.ExcelDriver");From then on you can get connections from DriverManager.Note: If Class.forName() throws ClassNotFoundException, you should check your classpath.

After the driver has been registered with the DriverManager, you can obtain
a Connection instance that is connected to a particular database by calling
DriverManager.getConnection(). With JDBC, a database is represented by a URL
(Uniform Resource Locator).

Embedded:
jdbc:excel:[//]/[DatabasePath][?prop1=value1[;prop2=value2]] (You can omit that "//" characters sometimes)
For example:
"jdbc:excel:/."
"jdbc:excel:/c:/data" for Windows driver
"jdbc:excel:///c:/data" for Windows driver
"jdbc:excel:////usr/data" for unix or linux
"jdbc:excel://///192.168.10.2/sharedir" for UNC path
"jdbc:excel:/./data"
"jdbc:excel:/./data/test8.xls"
"jdbc:excel:/./data/report.xlsx"
Remote Access (client/server mode):
jdbc:excel://host:port/[DatabasePath]
For example: "jdbc:excel://domain.com:3099/c:/data" if one ExcelServer is run on the 3099 port of domain.com
"jdbc:excel://domain.com:3099/c:/data/mydata.xls"
"jdbc:excel://domain.com:3099/c:/data/test.xlsx"
"jdbc:excel://domain.com:3099/c:/data/mydata.xml"
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
Memory-only Database:
jdbc:excel:/_memory_/
URL Database:(http protocol, https protocol, ftp protocol, sftp protocol)
jdbc:excel:http://httpURL
jdbc:excel:https://httpsURL
jdbc:excel:ftp://ftpURL
jdbc:excel:sftp://sftpURL
For example:
"jdbc:excel:http://www.hxtt.com/test" //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.
"jdbc:excel:sftp://testa:123456@localhost"
SAMBA Database:(smb protocol)
jdbc:excel:smb://[[[domain;]username[:password]@]server[:port]/[[share/[dir/]file]]][?[param=value]]
For example:
"jdbc:excel:smb://test1:123@100.100.13.94/excelfiles/zone" //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.
UNC path JDBC url:
jdbc:excel:/uncpath
jdbc:excel:///uncpath
For example:
"jdbc:excel:/\\PC17\c$\values"
"jdbc:excel:/\\PC17\val"
Free JDBC url:(Warning: only use it for special project)
jdbc:excel:/" or "jdbc:excel:///". Then you can use some full UNC path names in SQL to visit anywhere where your Java VM has right to access.
For instance:
select * from \\amd2500\e$\excelfiles\test;
select * from "\\amd2500\d$\exceliles".test;
select * from ".".test;
HXTT Excel supports seamlessly data mining on memory-only table, physical table, url table, compressed table, SAMBA table in a sql. More details
is in Advanced Programming chapter.

To connect, you need to get a Connection instance from JDBC. To do this, you
use the DriverManager.getConnection() method:

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

There are a few different signatures for the getConnection() method. You should
see the API documentation that comes with your JDK for more specific information
on how to use them. You can specify additional properties to the JDBC driver
by placing them in a java.util.Properties instance and passing that instance
to the DriverManager when you connect.

Property Name

Definition

Default Value

host

The remote host on which one ExcelServer is running

null

port

The port on which one ExcelServer is listening

null

serverType

The type of ExcelServer on the remote host

null

user

The user to connect as

null

password

The password to use when connecting

null

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

lockType

To specify a compatible lock for other applications of Excel.
This function hasn't been complemented.

null

lockTimeout

To specify Excel driver's timeout in milliseconds to wait
until other processes or Excel applications released record lock or table
lock. 0 means a default value, and <0 means no wait.

1000

refreshInterval

To specify a a refresh interval setting in seconds for FTP/SFTP database file which determines how long it to discard the content cache.

60

cryptType

To specify a 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, BLOWFISH, and AES now. It is not compatiable with MS Excel.

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, and none include us can help you in cracking your data
without the correct key.

false

tmpdir

Indicates whether set a temp directory, Default: the value
of JVM's "java.io.tmpdir" property. If that value is incorrect,
uing the directory of JDBC url. _memory_ means large data in memory.

null

delayedClose

Indicates the delayed seconds for close transaction.
That option is used to avoid frequent close/open table operations for following sqls. Automatic temporary index is disabled when delayedClose<=60s.
You can use 0~120 seconds. Default: 3.

null

maxIdleTime

Indicates the max idle time in minute for remote connection. That option is mainly used to avoid closing automatically idle remote connection for connection pool. Embedded idle connectoin won't be closed automatically except for garbage collection. You can use 1~1440 minutes. Default: 30.

null

maxCacheSize

Indicates the max memory utilization for per table on automatic temporary index or matched result cache.
You can use 16~65536 kilo bytes. Default: 1024.

null

versionNumber

Excel Version Number. You can use null, "BIFF7"(MS Excel 95), "BIFF8" (MS Excel 97/98/2000/2001/XP/2002/2003/2004), "XLSX" (MS Excel 2007/2010/2013).
This parameter is only used for CREATE DATABASE .

BIFF8

otherExtension

Indicates whether Excel driver supports other extension beside 'xls', 'xlsx', and 'xlsm'. You can use comma to assign more than one extension, for instance, otherExtension=DB,ACR .

null

caseInsensitive

Indicates whether is case insensitve for string comparison. You can use null, true, false

false

firstRowHasNames

Indicates whether the first row of worksheet data contains column names. If you use an int value n, it will use the n row of worksheet data contains column names, and data row start from the n+1 row.

true

maxScanRows

Indicates how many rows should be scanned when determining the column types. If you set maxScanRows to 0, the entire file is scanned. If you set maxScanRows to a negative value, the file won't be scanned.

10

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.

When your code then tries to open a Connection, and you get a No driver available
SQLException being thrown, this is probably caused by the driver not being in
the class path, or the JDBC url not being correct.

To close the database connection, simply call the close() method to the Connection: