将嵌入式数据库打包到JAR文件中

Question

我已经在我的eclipse项目中创建了一个Derby嵌入式数据库，在eclipse上它运行良好，但是将该项目打包成可运行的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 + "\'");       
}
 }

有什么关于这个问题的帮助吗？

- mohamedwahba

---

您想在程序运行时打包一个现有的数据库，还是创建一个新的数据库？ - user647772

@Tichodroma 数据库已经创建并嵌入到我的Eclipse项目中。 - mohamedwahba

您使用create=true，因此每次运行应该会创建一个新的数据库。 - user647772

导出 -> 可运行的Jar文件 -> 选择我的主类 -> 选择我的输出jar路径 -> 勾选“将所需库打包到生成的jar中”-> 确定 - mohamedwahba

如果有堆栈跟踪，那么它可能是由于连接字符串失败。 - ChadNC

Answer 1

数据库应该在您运行jar的文件夹中。如果不在，则请查看文档以了解如何指定connectionURL。如果项目导出到可运行的jar文件，则应指定依赖库不被提取，只需添加为jar或本地lib文件夹中的原样即可。这些库包括derby.jar和derbytools.jar应该在类路径或清单类路径中。使用以下代码测试您的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

- Roman C