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.
