Java & Local Databases
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.
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 administration of servers.
Some Required skills:
- Java programming langauge
- JDBC, SQL
- JSP and Servlets (for the Web tier)
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 )
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.
Here is an example with Derby in embedded mode:
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.
Here are some 100% Java and embeddable databases:
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 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.
Embedding Jetty.
Please note that there are other web servers that you can use this way.
As a follow up to Oscar...
Here's a simple "Type in the SQL" JSP page, using JSTL (Java Standard Tag Library) tags.
All you need to make this work is toss in the derby.jar library.
Download tomcat from Apache.
Download derby from Apache
cd $TOMCAT_HOME/webapps
mkdir yourapp
cd yourapp
Take the following and put it in 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
<?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.jar in to WEB-INF/lib
You should now have 3 files:
$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
And you'll get this little box to type SQL in to.
Derby will create the DB for you automagically.
With the JSTL and SQL tags you can do all you want from straight JSP.
Is it "best practice" to do everything in JSP? No.
Does it work? Yes.
Is it practical? Yes.
You can always change it later.