java - 将嵌入式数据库打包成jar文件

Question

我在我的eclipse项目中创建了一个derby嵌入式数据库，它在eclipse上运行良好，但是当将项目打包到Runnable jar文件中时，它无法连接数据库。

我做过与此视频类似的事情 http://vinayakgarg.wordpress.com/2012/03/07/packaging-java-application-with-apache-derby-as-jar-executable-using-eclipse/

这是我的 Communicate.java

import java.io.File;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Communicate {

private static final String dbURL = "jdbc:derby:imagesDB;create=true";
private static final String tableName = "imageDB";
private static Connection conn = null;
private static Statement stmt = null;

public void insert(String path, String hash, long FileSize,
        String label_name) throws NoSuchAlgorithmException, Exception {
    try {
        stmt = conn.createStatement();
        stmt.execute("insert into " + tableName + " values (\'" + path
                + "\'," + FileSize + ",\'" + hash + "\'" + ",\'"
                + label_name + "\')");
        stmt.close();
    } catch (SQLException sqlExcept) {
        sqlExcept.printStackTrace();
    }
}

public void createConnection() {
    try {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
        // Get a connection
        conn = DriverManager.getConnection(dbURL);
    } catch (Exception except) {
        except.printStackTrace();
    }
}

public void createTable() throws SQLException {
    Statement st = conn.createStatement();
    st.execute("CREATE TABLE "
            + tableName
            + " (fullPath VARCHAR(512), fileSize INTEGER, md5 VARCHAR(512), label_name VARCHAR(100))");
}

public void indexTable() throws SQLException {
    Statement st = conn.createStatement();
    st.execute("CREATE INDEX imageDBIndex ON imageDB (fullPath, label_name)");
}

public void deleteTable() throws SQLException {
    Statement st = conn.createStatement();
    st.execute("drop table " + tableName);
}

public String searchBySizeAndMD(String file_path, long size, String hash)
        throws SQLException {
    StringBuilder sb = new StringBuilder();
    Statement st = conn.createStatement();
    ResultSet rs = st
            .executeQuery("SELECT fullPath, label_name FROM (SELECT * FROM imageDB im WHERE im.fileSize = "
                    + size + " ) as A WHERE A.md5 = " + "\'" + hash + "\'");
    while (rs.next()) {
        sb.append("Image: (" + rs.getString("fullPath")
                + ") is at label: (" + rs.getString("label_name") + ")\n");
    }
    return sb.toString();
}

public String searchByImageName(String fileName) throws SQLException {
    StringBuilder sb = new StringBuilder();
    Statement st = conn.createStatement();
    ResultSet rs = st
            .executeQuery("SELECT fullPath, label_name FROM imageDB im WHERE im.fullPath like \'%"
                    + fileName + "%\'");
    while (rs.next()) {
        File out_path = new File(rs.getString("fullPath"));
        if (!fileName.equals(out_path.getName())) continue;
        sb.append("Image: (" + out_path.getPath()
                + ") is at label: (" + rs.getString("label_name") + ")\n");
    }

    return sb.toString();
}

public void deleteLabel(String label) throws SQLException {
    Statement st = conn.createStatement();
    st.execute("DELETE FROM " + tableName + " WHERE label_name = \'" + label + "\'");       
}
 }

在这个问题上有什么帮助吗？

Answer 1

数据库应该位于运行 jar 的文件夹中。如果不是，请查看文档如何指定 connectionURL。如果项目导出到可运行的 jar 文件指定依赖库不被提取只是添加到 jar 或到本地 lib 文件夹。这些库是 derby.jar 和 derbytools.jar 应该在类路径或 list 类路径中。使用下面的代码来测试你的

Communicate 类。

import java.io.File;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Communicate {

  private static final String dbURL = "jdbc:derby:imagesDB;create=true";
  private static final String tableName = "imageDB";
  private static Connection conn = null;
  private static Statement stmt = null;

  public void insert(String path, String hash, long FileSize,
                     String label_name) throws NoSuchAlgorithmException, Exception {
    try {
      stmt = conn.createStatement();
      stmt.execute("insert into " + tableName + " values (\'" + path
        + "\'," + FileSize + ",\'" + hash + "\'" + ",\'"
        + label_name + "\')");
      stmt.close();
      System.out.println("Inserted into table "+ tableName+ " values (\'" + path
        + "\'," + FileSize + ",\'" + hash + "\'" + ",\'"
        + label_name + "\')");
    } catch (SQLException sqlExcept) {
      sqlExcept.printStackTrace();
    }
  }

  public void loadDriver() {
    try {
      Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
      System.out.println("Loaded the appropriate driver");
    } catch (Exception except) {
      except.printStackTrace();
    }
  }

  public void createConnection() {
    try {
      // Get a connection
      conn = DriverManager.getConnection(dbURL);
      System.out.println("Connected to and created database ");
    } catch (Exception except) {
      except.printStackTrace();
    }
  }

  public void createTable() throws SQLException {
    Statement st = conn.createStatement();
    st.execute("CREATE TABLE "
      + tableName
      + " (fullPath VARCHAR(512), fileSize INTEGER, md5 VARCHAR(512), label_name VARCHAR(100))");
    System.out.println("Created table "+ tableName);
  }

  public void indexTable() throws SQLException {
    Statement st = conn.createStatement();
    st.execute("CREATE INDEX imageDBIndex ON imageDB (fullPath, label_name)");
    System.out.println("Created index "+ "imageDBIndex");
  }

  public void deleteTable() throws SQLException {
    Statement st = conn.createStatement();
    st.execute("drop table " + tableName);
    System.out.println("Deleted table "+ tableName);
  }

  public String searchBySizeAndMD(String file_path, long size, String hash)
    throws SQLException {
    StringBuilder sb = new StringBuilder();
    Statement st = conn.createStatement();
    ResultSet rs = st
      .executeQuery("SELECT fullPath, label_name FROM (SELECT * FROM imageDB im WHERE im.fileSize = "
        + size + " ) as A WHERE A.md5 = " + "\'" + hash + "\'");
    while (rs.next()) {
      sb.append("Image: (" + rs.getString("fullPath")
        + ") is at label: (" + rs.getString("label_name") + ")\n");
    }
    return sb.toString();
  }

  public String searchByImageName(String fileName) throws SQLException {
    StringBuilder sb = new StringBuilder();
    Statement st = conn.createStatement();
    ResultSet rs = st
      .executeQuery("SELECT fullPath, label_name FROM imageDB im WHERE im.fullPath like \'%"
        + fileName + "%\'");
    while (rs.next()) {
      File out_path = new File(rs.getString("fullPath"));
      if (!fileName.equals(out_path.getName())) continue;
      sb.append("Image: (" + out_path.getPath()
        + ") is at label: (" + rs.getString("label_name") + ")\n");
    }

    return sb.toString();
  }

  public void deleteLabel(String label) throws SQLException {
    Statement st = conn.createStatement();
    st.execute("DELETE FROM " + tableName + " WHERE label_name = \'" + label + "\'");
  }

  public static void main(String[] args)
  {
    Communicate c = new Communicate();
    c.loadDriver();
    try {
      c.createConnection();
      c.createTable();
      c.indexTable();
      c.insert("/some/path", "12323423", 45656567, "label name");
      String s = c.searchBySizeAndMD("/some/path", 45656567, "12323423");
      System.out.println("Search result: "+ s);
      c.deleteTable();
      conn.commit();
      System.out.println("Committed the transaction");

      //Shutdown embedded database
      try
      {
        // the shutdown=true attribute shuts down Derby
        DriverManager.getConnection("jdbc:derby:;shutdown=true");

      }
      catch (SQLException se)
      {
        if (( (se.getErrorCode() == 50000)
          && ("XJ015".equals(se.getSQLState()) ))) {
          // we got the expected exception
          System.out.println("Derby shut down normally");
        } else {
          System.err.println("Derby did not shut down normally");
          System.err.println("  Message:    " + se.getMessage());
        }
      }

    } catch (Exception e) {
      System.err.println("  Message:    " + e.getMessage());
    } finally {
      // release all open resources to avoid unnecessary memory usage

      //Connection
      try {
        if (conn != null) {
          conn.close();
          conn = null;
        }
      } catch (SQLException e) {
        System.err.println("  Message:    " + e.getMessage());
      }
    }
    System.out.println("Communicate finished");
  }


}

这是输出:

Loaded the appropriate driver
Connected to and created database 
Created table imageDB
Created index imageDBIndex
Inserted into table imageDB values ('/some/path',45656567,'12323423','label name')
Search result: Image: (/some/path) is at label: (label name)

Deleted table imageDB
Committed the transaction
Derby shut down normally
Communicate finished