I just wrote my first Java database program for the purpose of getting feedback on the implementation and coding. It has 1 table and 2 buttons and prompts the user to select a folder, lists the contents of the folder in the table and lists the hash of the files in the table and writes it to a database.
It works fine, but I have no idea if I coded it cleanly and split the program into the proper packages and classes. It's definitely a beginner level project so there is nothing too complex about it. I'm also using the h2 database because I was told its the most efficient for small databases. Could you all please give me feedback on this?
I used NetBeans 7.3 and uploaded the full project here. This is the class I'm using for the database. It contains most of the code I have in question, but there are other parts of the project as a whole I'm concerned if they were organized correctly:
public class CDatabaseLayer {
private static ArrayList<CFileObject> fileList = new ArrayList();
private static Server server;
private static JdbcDataSource ds = new JdbcDataSource();
private static Connection conn;
private static int lastId = 0;
private static Statement stat;
private static ResultSet rs;
private static String query;
static public ArrayList<CFileObject> getFileList() {
connectDatabase();
return fileList;
}
static public boolean connectDatabase()
{
System.out.println("Attempting to connect to database.");
if(server == null) {
try {
server = Server.createTcpServer();
server = server.start();
} catch (SQLException ex) {
System.out.println("connectDatabase createTcpServer() exception: "+ex);
return false;
}
}
//return false if connected
if(conn != null) {
System.out.println("Already established DB connection.");
return false;
} else {
try {
//connect to database
ds.setURL("jdbc:h2:test");
ds.setUser("sa");
ds.setPassword("");
conn = ds.getConnection();
if(conn.isClosed()) {
System.out.println("Connection not established.");
} else {
System.out.println("Connected.");
createFileListTable();
loadFileListTable();
}
} catch (SQLException ex) {
System.out.println("connectDatabase getConnection() exception: "+ex);
}
}
return true;
}
static private void loadFileListTable()
{
try {
stat = newStatement();
rs = stat.executeQuery("select * from fileList");
fileList.clear();
while(rs.next()) {
CFileObject objF = new CFileObject();
objF.fileName = rs.getString("fileName");
objF.filePath = rs.getString("filePath");
objF.fileHash = rs.getString("fileHash");
fileList.add(objF);
}
} catch (SQLException ex) {
System.out.println("loadFileListTable exception: "+ex);
}
}
static public void manipulateFiles() throws SQLException {
try {
if(conn.isClosed()) {
System.out.println("manipulateFiles: Connection is closed.");
}
} catch (SQLException ex) {
System.out.println("manipulateFiles connection exception: "+ex);
}
for(CFileObject f : fileList) {
String hash = new String();
try {
hash = hashFile.getMD5Checksum(f.filePath);
} catch (Exception ex) {
System.out.println("manipulateFiles hash exception: "+ex);
}
f.fileHash = hash;
query = "update filelist set filehash = '"+hash+"' where filepath = '"+f.filePath+"'";
stat = newStatement();
//QUESTION: Why does this return false??
stat.execute(query);
}
}
static public void updateDatabaseWithFilesFromPath(File path)
{
ArrayList<CFileObject> list;
list = CFileObject.getListFromPath(path);
for(CFileObject file : list) {
if(!entryExists("filelist", "filepath", file.filePath)) {
try {
lastId = nextUnusedId();
addFileEntry(lastId, file.fileName, file.filePath);
fileList.add(file);
} catch (SQLException ex) {
System.out.println("updateDatabaseWithFilesFromPath exception: "+ex);
}
}
}
}
/*
static public void updateDatabase() throws SQLException
{
lastId = nextUnusedId();
for (final CFileObject file : fileList) {
if(!entryExists("filelist", "filepath", file.filePath)) {
lastId = nextUnusedId();
addFileEntry(lastId, file.fileName, file.filePath);
}
}
}
*/
private static Statement newStatement()
{
try {
stat = conn.createStatement();
return stat;
} catch (SQLException ex) {
System.out.println("newStatement exception: "+ex);
}
return null;
}
//QUESTION: Is there a more efficient way to do this?
private static void createFileListTable()
{
try {
//see if fileList table exists. if not, catch error and create it
try {
stat = newStatement();
stat.executeQuery("select * from fileList");
} catch(SQLException e) {
if(e.toString().contains("Table \"FILELIST\" not found")) {
System.out.println("Creating filelist table.\n");
stat.execute("create table filelist(id int primary key, fileName varchar(255), filePath varchar(512), fileHash varchar(32))");
} else {
System.out.println(e);
}
}
} catch(SQLException ex) {
System.out.println("createFileListTable exception: "+ex);
}
}
private static int nextUnusedId()
{
try {
stat = newStatement();
rs = stat.executeQuery("select id from fileList");
if(rs.last()) {
lastId = rs.getInt("id");
lastId++;
}
} catch (SQLException ex) {
System.out.println("nextUnusedId exception: "+ex);
}
return lastId;
}
private static boolean entryExists(String table, String prop, String val)
{
try {
stat = newStatement();
query = "select 1 from "+table+" where "+prop+" = '"+val+"'";
rs = stat.executeQuery(query);
return rs.last();
} catch (SQLException ex) {
System.out.println("entryExists exception: "+ex);
}
return true;
}
private static void addFileEntry(int entryId, String fileName, String filePath) throws SQLException
{
query = "insert into fileList values("+entryId+", '"+fileName+"', '"+filePath+"', 0)";
stat = newStatement();
//QUESTION: Why does this return false??
stat.execute(query);
}
static public void disconnectDatabase()
{
server.stop();
}
}
