This application searches files in a database.
This example application is designed for programers that are new to JDBC. The functionality is kept low, so it should be possible to understand what is going on. It connects, execute queries and inserts against the database.
First, the database must be initialized with a path. The file names of this directory and all its subdirectories will be stored in the database:
java FindFile -init .
Re-creating the database...
Finished
After this is made, the database can be searched for files that matches a specific pattern, for example all files containing 'stat' in the path or filename:
java FindFile stat
Files like 'stat'
.\org\hsql\jdbcPreparedStatement.class
.\org\hsql\jdbcStatement.class
Here is the source code for this application. This source code
is colorized using Java2HTML (see Links).
/*
* FindFile.java
*/
import java.sql.*;
import java.io.*;
class FindFile {
// The entry point of this class
public static void main(String arg[]) {
// Exceptions may occur
try {
// Load the Hypersonic SQL JDBC driver
Class.forName("org.hsql.jdbcDriver");
// Connect to the database
// It will be create automatically if it does not yet exist
// 'testfiles' in the URL is the name of the database
// "sa" is the user name and "" is the (empty) password
Connection conn=DriverManager.getConnection(
"jdbc:HypersonicSQL:testfiles","sa",""
);
// Check the command line parameters
if(arg.length==1) {
// One parameter:
// Find and print the list of files that are like this
listFiles(conn,arg[0]);
} else if(arg.length==2 && arg[0].equals("-init")) {
// Command line parameters: -init pathname
// Init the database and fill all file names in
fillFileNames(conn,arg[1]);
} else {
// Display the usage info
System.out.println("Usage:");
System.out.println("java FindFile -init .");
System.out.println(" Re-create database from directory '.'");
System.out.println("java FindFile name");
System.out.println(" Find files like 'name'");
}
// Finally, close the connection
conn.close();
} catch(Exception e) {
// Print out the error message
System.out.println(e);
}
}
// Search in the database and list out files like this
static void listFiles(Connection conn,String name)
throws SQLException {
System.out.println("Files like '"+name+"'");
// Convert to upper case, so the search is case-insensitive
name=name.toUpperCase();
// Create a statement object
Statement stat=conn.createStatement();
// Now execute the search query
// UCASE: This is a case insensitive search
// ESCAPE ':' is used so it can be easily searched for '\'
ResultSet result=stat.executeQuery(
"SELECT Path FROM Files WHERE "+
"UCASE(Path) LIKE '%"+name+"%' ESCAPE ':'");
// Moves to the next record until no more records
while(result.next()) {
// Print the first column of the result
// could use also getString("Path")
System.out.println(result.getString(1));
}
// Close the ResultSet - not really necessary, but recommended
result.close();
}
// Re-create the database and fill the file names in
static void fillFileNames(Connection conn,String root)
throws SQLException {
System.out.println("Re-creating the database...");
// Create a statement object
Statement stat=conn.createStatement();
// Try to drop the table
try {
stat.executeUpdate("DROP TABLE Files");
} catch(SQLException e) {
// Ignore Exception, because the table may not yet exist
}
// For compatibility to other database, use varchar(255)
// In Hypersonic SQL, length is unlimited, like Java Strings
stat.execute("CREATE TABLE Files"+
"(Path varchar(255),Name varchar(255))");
// Close the Statement object, it is no longer used
stat.close();
// Use a PreparedStatement because Path and Name could contain '
PreparedStatement prep=conn.prepareCall(
"INSERT INTO Files (Path,Name) VALUES (?,?)"
);
// Start with the 'root' directory and recurse all subdirectories
fillPath(root,"",prep);
// Close the PreparedStatement
prep.close();
System.out.println("Finished");
}
// Fill the file names, using the PreparedStatement
static void fillPath(String path,String name,
PreparedStatement prep) throws SQLException {
File f=new File(path);
if(f.isFile()) {
// Clear all Parameters of the PreparedStatement
prep.clearParameters();
// Fill the first parameter: Path
prep.setString(1,path);
// Fill the second parameter: Name
prep.setString(2,name);
// Its a file: add it to the table
prep.execute();
} else if(f.isDirectory()) {
if(!path.endsWith(File.separator)) {
path+=File.separator;
}
String list[]=f.list();
// Process all files recursivly
for(int i=0;i<list.length;i++) {
fillPath(path+list[i],list[i],prep);
}
}
}
}