Java 和本地数据库

Question

提问by Akidi

TL DR; Want some Java help with connecting to a truly local database ( no access to server tech ), or if you can whip up code, that will work. All it has to do is query the DB ( MS Access, although can be changed ), and output a JSON string. See EDIT2 for more specificity.

特尔博士；需要一些 Java 帮助来连接到真正的本地数据库（无法访问服务器技术），或者如果您可以编写代码，那就行了。它所要做的就是查询数据库（MS Access，但可以更改），并输出一个 JSON 字符串。有关更多详细信息，请参阅 EDIT2。

EDIT: Before anyone says JDBC; I looked through the tutorials ( started reading in depth ), but most of it seems to be geared towards server tech, which I have no access to.

编辑：在任何人说 JDBC 之前；我浏览了教程（开始深入阅读），但其中大部分似乎都是针对服务器技术的，我无法访问。

EDIT2: Seems most the answers so far are requiring an installation of some kind which I unfortunately cannot do ( and failed to mention, so I apologize ). However, this is what is currently being used and I would like a solution similar for Java that would make it more cross-browser compatible as opposed to being HTA only (link: https://launchpad.net/accessdb)

EDIT2：到目前为止，似乎大多数答案都需要安装某种我不幸无法做到的（并且没有提到，所以我道歉）。但是，这是目前正在使用的，我想要一个类似于 Java 的解决方案，它可以使它更跨浏览器兼容，而不是仅使用 HTA（链接：https: //launchpad.net/accessdb）

Okay, for the long version. I'm trying to make use of a local database to create a desktop style application for work ( and possibly make use of the knowledge for other projects ). The database I can create without a problem ( MS Access 2003, just happens to be quickly available ). Currently I'm using ActiveX scripting to work with the database in a HTML Application (*.HTA file works only with Internet Explorer), I would really like to make this more cross browser ( in the event the company EVER switches to an actual browser ) by using JAVA to access the database, then output the results in JSON to a local variable JavaScript can call and make use of.

好的，对于长版。我正在尝试使用本地数据库来创建桌面样式的工作应用程序（并可能将知识用于其他项目）。我可以毫无问题地创建数据库（MS Access 2003，恰好可以快速使用）。目前我正在使用 ActiveX 脚本来处理 HTML 应用程序中的数据库（*.HTA 文件仅适用于 Internet Explorer），我真的很想让这个更跨浏览器（如果公司永远切换到实际浏览器) 通过使用 JAVA 访问数据库，然后将结果以 JSON 输出到本地变量 JavaScript 可以调用和使用。

Honestly would rather tutorial type information as I want to actually learn why this works so I can later modify it to suit my needs. I have Eclipse installed as well as JDK, and can right small programs in Java, so not completely brain dead ( but not far from :P ). I've been working with JavaScript so I can read quite a bit of Java code as it stands ( not the same syntax since they are not related, but the little I do know of Java I can translate back to JS without problem ).

老实说，我宁愿使用教程类型信息，因为我想真正了解它为什么有效，以便我以后可以修改它以满足我的需要。我安装了 Eclipse 和 JDK，并且可以用 Java 编写小程序，所以不是完全脑死亡（但离 :P 不远）。我一直在使用 JavaScript，所以我可以阅读相当多的 Java 代码（语法不一样，因为它们不相关，但我对 Java 知之甚少，我可以毫无问题地转换回 JS）。

Anyway, any assistance would be greatly appreciated. I can continue developing with ActiveX ( as I know that works on the system and I'm 99% sure they will continue using Internet Explorer, but, would like some flexibility ).

无论如何，任何帮助将不胜感激。我可以继续使用 ActiveX 进行开发（因为我知道这在系统上有效，我 99% 确定他们会继续使用 Internet Explorer，但是，想要一些灵活性）。

Answer 1

回答by bakkal

I am not sure I understood your requirements very well, however I did decipher some key points. What I am suggesting will let you deliver a complete working application in a single package(say a JAR) that will not require much (if any) configuration or administrationof servers.

我不确定我是否非常了解您的要求，但是我确实破译了一些关键点。我的建议是让您在单个包（比如 JAR）中交付一个完整的工作应用程序，不需要太多（如果有）配置或服务器管理。

Some Required skills:

一些必备技能：

Java programming langauge
JDBC, SQL
JSP and Servlets (for the Web tier)

Java编程语言
JDBC、SQL
JSP 和 Servlets（用于 Web 层）

I'm trying to make use of a local database to create a desktop style application [...] I Want some Java help with connecting to a truly local database ( no access to server tech )

我正在尝试使用本地数据库来创建桌面样式的应用程序 [...] 我需要一些 Java 帮助来连接到真正的本地数据库（无法访问服务器技术）

Datastore

数据存储

JDBC can be used with any database that has a JDBC driver, which isn't necessarily a database in "network mode", it can be used with embedded databases as well.

JDBC 可以与任何具有 JDBC 驱动程序的数据库一起使用，它不一定是“网络模式”下的数据库，它也可以与嵌入式数据库一起使用。

Here is an example with Derby in embedded mode: alt text

以下是嵌入模式下的Derby 示例：替代文字

When an application accesses a Derby database using the Embedded Derby JDBC driver, the Derby engine does not run in a separate process, and there are no separate database processes to start up and shut down. Instead, the Derby database engine runs inside the same Java Virtual Machine (JVM) as the application. So, Derby becomes part of the application just like any other jar file that the application uses. Figure 1 depicts this embedded architecture.

当应用程序使用嵌入式 Derby JDBC 驱动程序访问 Derby 数据库时，Derby 引擎不会在单独的进程中运行，也没有单独的数据库进程可以启动和关闭。相反，Derby 数据库引擎在与应用程序相同的 Java 虚拟机 (JVM) 中运行。因此，Derby 成为应用程序的一部分，就像应用程序使用的任何其他 jar 文件一样。图 1 描述了这种嵌入式架构。

Here are some 100% Java and embeddable databases:

以下是一些 100% Java 和可嵌入数据库：

http://www.h2database.com/html/main.html

http://db.apache.org/derby/

http://hsqldb.org/

Web tier

网络层

You can also embed a Web server like Jetty.

您还可以嵌入像Jetty这样的 Web 服务器。

Jetty has a slogan "Don't deploy your application in Jetty, deploy Jetty in your application". What this means is that as an alternative to bundling your application as a standard WAR to be deployed in Jetty, Jetty is designed to be a software component that can be instantiated and used in a Java program just like any POJO.

Jetty 有一句口号“不要在 Jetty 中部署您的应用程序，而是在您的应用程序中部署 Jetty”。这意味着，作为将您的应用程序捆绑为要部署在 Jetty 中的标准 WAR 的替代方案，Jetty 被设计为一个软件组件，可以像任何 POJO 一样在 Java 程序中实例化和使用。

Embedding Jetty.

嵌入码头。

Please note that there are other web servers that you can use this way.

请注意，您可以通过这种方式使用其他 Web 服务器。

Answer 2

回答by OscarRyz

Ok, so you need to serve JSON from a local database, right?

好的，所以您需要从本地数据库提供 JSON，对吗？

You don't need a server, you can serve web pages directly from your local machine ( you just have to point to localhost )

你不需要服务器，你可以直接从本地机器提供网页（你只需要指向 localhost ）

So, basically ( and I know this won't be complete, but I hope is a good start )

所以，基本上（我知道这不会完整，但我希望这是一个好的开始）

You have to:

你必须：

Install a servlet container ( Tomcat or Jetty ), they are very easy to use.
Create a servlet or JSP page to display the data ( JSP are also easy )
Create a connection using JDBC to a local database such as Derby
Use a library to transform your data into JSON

安装 servlet 容器（Tomcat 或 Jetty），它们非常易于使用。
创建 servlet 或 JSP 页面来显示数据（JSP 也很容易）
使用 JDBC 创建到本地数据库（例如 Derby）的连接
使用库将数据转换为 JSON

Install tomcat

安装tomcat

( I will describe for UNIX, but it's the same for Windows)

（我将针对 UNIX 进行描述，但对于 Windows 也是如此）

Download it from hereand then unzip the file in some directory you like ( eg. /home/you/ or C:\Users\you\ )

从这里下载，然后将文件解压缩到您喜欢的某个目录中（例如 /home/you/ 或 C:\Users\you\ ）

Open a terminal and go to the tomcat bindirectory and type catalina.sh runthat will start tomcat, you need to have Java installed on your system

打开终端并进入 tomcatbin目录并键入catalina.sh run将启动 tomcat，您需要在您的系统上安装 Java

Open your browser in http://localhost:8080

在浏览器中打开 http://localhost:8080

It should look like this:

它应该是这样的：

Create a JSP file

创建一个 JSP 文件

Next, go to the tomcat webappsdirectory, it should contain these folders:

接下来，转到 tomcat webapps目录，它应该包含以下文件夹：

ROOT/ docs/ examples/ host-manager/ manager/

Create a new one, for instance youror whatever and inside create a file Hello.jspwith the following:

创建一个新的，例如your或其他任何东西，并在里面创建一个Hello.jsp具有以下内容的文件：

Hello.jsp
----------
Hello, world

And then open in your browser: http://localhost:8080/your/Hello.jsp

然后在浏览器中打开： http://localhost:8080/your/Hello.jsp

Should look like:

应该看起来像：

Create a JDBC program

创建 JDBC 程序

Next, in your webapp yourcreate the directory: WEB-INF/liband save there the derby JDBC driver, you can get it from here

接下来，在您的 webapp 中your创建目录：WEB-INF/lib并将 derby JDBC 驱动程序保存在那里，您可以从这里获取它

Modify your Hello.jsp file to create a sample table like this:

修改您的 Hello.jsp 文件以创建如下示例表：

<%@page import="java.sql.*, java.util.*"%>
<%!
     public String getData() {
         List list = new ArrayList();
         try {
             Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
             Connection connection = DriverManager.getConnection("jdbc:derby:yourdb;create=true");
             // The first time:
             PreparedStatement pstmt = connection.prepareStatement(
                 "CREATE TABLE PEOPLE\n"+
                 "(PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY\n"+
                 "    CONSTRAINT PEOPLE_PK PRIMARY KEY, PERSON VARCHAR(26))");
            pstmt.executeUpdate();
            pstmt = connection.prepareStatement("INSERT INTO PEOPLE(PERSON) VALUES('OSCAR')");
            pstmt.executeUpdate();
         } catch( Exception e ) { 
             throw new RuntimeException( e );
         }
         return "";
     }
%>
:)
<%
    getData();
%>

And execute your jsp again by going to localhost:8080/your/Hello.jsp

并通过转到再次执行您的jsp localhost:8080/your/Hello.jsp

If you execute it twice the system will tell you the table already exists:

如果你执行两次，系统会告诉你表已经存在：

That's ok, we have created the table already.

没关系，我们已经创建了表。

Use a library to output JSON

使用库输出 JSON

Shudown tomcat, but pressing contrl-c

关闭tomcat，但按下contrl-c

Download and copy to your WEB-INF/lib directory the json-simple jar. You can get it from here

将 json-simple jar 下载并复制到您的 WEB-INF/lib 目录。你可以从这里得到它

Start tomcat again

再次启动tomcat

Comment the creation code in the JSP and replace it for a SQL query like this:

注释 JSP 中的创建代码并将其替换为 SQL 查询，如下所示：

<%@page import="java.sql.*, java.util.*, org.json.simple.JSONValue"%>

<%!
     public String getData() {
         List list = new ArrayList();
         Connection connection = null;
         try {
             Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
              connection = DriverManager.getConnection("jdbc:derby:yourdb;create=true");
             // The first time:
             //PreparedStatement pstmt = connection.prepareStatement(
             //    "CREATE TABLE PEOPLE\n"+
             //    "(PERSON_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY\n"+
             //    "    CONSTRAINT PEOPLE_PK PRIMARY KEY, PERSON VARCHAR(26))");
            //pstmt.executeUpdate();
            //pstmt = connection.prepareStatement("INSERT INTO PEOPLE(PERSON) VALUES('OSCAR')");
            //pstmt.executeUpdate();
            // execute select the second time
            PreparedStatement psmt = connection.prepareStatement("SELECT person FROM PEOPLE");
            ResultSet rs = psmt.executeQuery();
            while( rs.next() ){
                list.add( rs.getString("person"));
            }
         } catch( Exception e ) { 
             throw new RuntimeException( e );
         } finally {
             if( connection != null ) try {
                 connection.close();
             } catch( Exception e ){}
         }
         return JSONValue.toJSONString(list);
     }
%>
:)
<script>
 var list = <%=
    getData()
%>
</script>

Notice we are using a throw import, and at the end, we change the invocation of the method to put the result in a javascript variable list

注意我们使用的是 throw 导入，最后，我们更改方法的调用以将结果放入 javascript 变量中 list

When run, the JSP page would look like this ( you'll have to right click to see the HTML source code so see the <script>tag):

运行时，JSP 页面将如下所示（您必须右键单击才能查看 HTML 源代码，以便查看<script>标记）：

I hope you find this useful. I tried to make it extremely simple for you.

希望这个对你有帮助。我试图让它对你来说非常简单。

IMPORTANTThe sample above is full of bad practices, don't code like that ( for instance, creating web apps directly on tomcat webapps folder, or executing SQL directly from JSP page ( not to mention , not closing the resources etc. )

重要上面的示例充满了不好的做法，不要这样编码（例如，直接在 tomcat webapps 文件夹中创建 web 应用程序，或直接从 JSP 页面执行 SQL（更不用说，不关闭资源等）

The main idea was to give you enough information to get started.

主要思想是为您提供足够的信息以开始使用。

There are ways to integrate this with eclipse, and to use a SQL visor such as SquirrelSQL client to manipulate the data.

有多种方法可以将其与 eclipse 集成，并使用 SQL 管理程序（例如 SquirrelSQL 客户端）来操作数据。

This should be simple enough, I actually downloaded the files and create the test while writing this answer, so it should work.

这应该很简单，我实际上下载了文件并在编写此答案时创建了测试，因此它应该可以工作。

Answer 3

回答by Will Hartung

As a follow up to Oscar...

作为奥斯卡的后续...

Here's a simple "Type in the SQL" JSP page, using JSTL (Java Standard Tag Library) tags.

这是一个简单的“键入 SQL”JSP 页面，使用 JSTL（Java 标准标记库）标记。

All you need to make this work is toss in the derby.jar library.

完成这项工作所需要做的就是放入 derby.jar 库。

Download tomcat from Apache.

从 Apache 下载 tomcat。

Download derby from Apache

从 Apache 下载 derby

cd $TOMCAT_HOME/webapps

mkdir yourapp

cd yourapp

Take the following and put it in index.jsp:

将以下内容放入 index.jsp 中：

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>SQL Fun</title>
    </head>
    <body>
        <h1>Welcome to Derby SQL</h1>
        <!-- Form to prompt for SQL -->
        <form action="index.jsp" method="POST">
            <label for="sql">SQL Text:</label>
            <textarea cols="40" rows="10" name="sql"></textarea>
            <br/>
            <!-- click Execute query to execute a SELECT statement -->
            <input type="submit" name="doquery" value="Execute Query"/>
            <!-- click Execute DDL to execute a CREATE, UPDATE, DROP or DELETE statement -->
            <input type="submit" name="doddl" value="Execute DDL"/>
        </form>
        <c:if test="${!empty param.sql}">
            <!-- param is the default variable with the request parameters -->
            Executing: ${param.sql}
            <br/>
            <!-- This sets up the DB Connection to derby -->
            <sql:setDataSource driver="org.apache.derby.jdbc.EmbeddedDriver"
                url="jdbc:derby:derbyDB;create=true" scope="request"/>

            <c:choose>
                <c:when test="${!empty param.doddl}">
                    <sql:update var="result">
                    ${param.sql}
                    </sql:update>
                    Result = ${result}
                </c:when>
                <c:otherwise>
                    <sql:query var="result">
                    ${param.sql}
                    </sql:query>

                    <table border="1">
                        <!-- column headers -->
                        <tr>
                            <c:forEach var="columnName" items="${result.columnNames}">
                                <th><c:out value="${columnName}"/></th>
                            </c:forEach>
                        </tr>
                        <!-- column data -->
                        <c:forEach var="row" items="${result.rowsByIndex}">
                            <tr>
                                <c:forEach var="column" items="${row}">
                                    <td><c:out value="${column}"/></td>
                                </c:forEach>
                            </tr>
                        </c:forEach>
                    </table>
                </c:otherwise>
            </c:choose>
        </c:if>
    </body>
</html>

mkdir WEB-INF

take the following and put it in web.xml

将以下内容放入 web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
</web-app>

mkdir WEB-INF/lib

copy the derby.jarin to WEB-INF/lib

复制derby.jar到WEB-INF/lib

You should now have 3 files:

您现在应该有 3 个文件：

$TOMCAT_HOME/webapps/yourapp/index.jsp $TOMCAT_HOME/webapps/yourapp/WEB-INF/web.xml $TOMCAT_HOME/webapps/yourapp/WEB-INF/lib/derby.jar

Now fire up Tomcat, and point your browser at http://localhost:8080/yourapp

现在启动 Tomcat，并将浏览器指向 http://localhost:8080/yourapp

And you'll get this little box to type SQL in to.

你会得到这个小框来输入 SQL。

Derby will create the DB for you automagically.

Derby 将自动为您创建数据库。

With the JSTL and SQL tags you can do all you want from straight JSP.

使用 JSTL 和 SQL 标记，您可以从直接的 JSP 中执行您想要的所有操作。

Is it "best practice" to do everything in JSP? No.

在 JSP 中做所有事情是“最佳实践”吗？不。

Does it work? Yes.

它有效吗？是的。

Is it practical? Yes.

实用吗？是的。

You can always change it later.

您以后可以随时更改它。

Answer 4

回答by nokul

You might want to take a look at Apache Derby. Recent JDKs include it as JavaDB. On Windows, you will find it in ProgramFiles/Sun.

您可能想看看Apache Derby。最近的 JDK 将其包含为 JavaDB。在 Windows 上，您可以在 ProgramFiles/Sun 中找到它。

Java 和本地数据库

提问by Akidi

回答by bakkal

Datastore

数据存储

Web tier

网络层

回答by OscarRyz

回答by Will Hartung

回答by nokul

相关推荐

最近更新

标签

Java 和本地数据库

提问by Akidi

回答by bakkal

Datastore

数据存储

Web tier

网络层

回答by OscarRyz

回答by Will Hartung

回答by nokul

相关推荐

JAVA：将图像放入 jTable 单元格中

java 以实例状态保存自定义对象数组

java JavaEE6 DAO：应该是@Stateless 还是@ApplicationScoped？

Java：检查空值或允许异常处理

相关推荐

最近更新

标签