Java & Local Databases

Question

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.

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.

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 )

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.

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 ).

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 ).

Answer 1

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

You don't need a server, you can serve web pages directly from your local machine ( you just have to point to 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

Install tomcat

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

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

Open a terminal and go to the tomcat bin directory and type catalina.sh run that will start tomcat, you need to have Java installed on your system

Open your browser in http://localhost:8080

It should look like this:

Create a JSP file

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

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

Create a new one, for instance your or whatever and inside create a file Hello.jsp with the following:

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

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

Should look like:

Create a JDBC program

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

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

<%@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

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

Shudown tomcat, but pressing contrl-c

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

Start tomcat again

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

<%@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

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):

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

IMPORTANT The 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. )

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.

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