<%@page import="java.sql.*,java.util.*,java.text.*"%> <% response.setHeader("Cache-Control","no-cache"); %> <%! private boolean isSchemaCreated(Connection connection) throws SQLException { // ask the name of all the tables in the database // compare against a list of known tables DatabaseMetaData meta = connection.getMetaData(); ResultSet rs = meta.getTables(null,null,null,new String[] { "TABLE" }); int found = 0; while(rs.next()) { String tableName = rs.getString("TABLE_NAME"); if(tableName.equalsIgnoreCase("resource") || tableName.equalsIgnoreCase("booking")) found++; } rs.close(); return 2 == found; } protected void doUpdates(Connection connection, String[] statements) throws SQLException { Statement stmt = connection.createStatement(); SQLException e = null; try { for(int i = 0;i < statements.length;i++) try { stmt.executeUpdate(statements[i]); } catch(SQLException x) { e = e != null ? e : x; } if(null != e) { throw e; } } finally { stmt.close(); } } private boolean isEmpty(String st) { if(null != st) return st.trim().length() == 0; else return true; } private void doInsertResource(Connection connection, String name, String description) throws SQLException { PreparedStatement stmt = connection.prepareStatement( "insert into resource (name, description) values (?,?)"); try { stmt.setString(1,name); stmt.setString(2,description); stmt.executeUpdate(); } finally { stmt.close(); } } %> <% DateFormat dateFormat = DateFormat.getDateInstance(DateFormat.MEDIUM, Locale.US); Class.forName("org.hsqldb.jdbcDriver"); Connection connection = DriverManager.getConnection("jdbc:hsqldb:db/resourceful", "sa", null); try { connection.setAutoCommit(true); %> Business center Intranet

Business center Intranet

<% String todo = request.getParameter("todo"), message = null; if(todo != null) { if(todo.equals("drop")) doUpdates(connection,new String[] { "drop index resource.idx_name", "drop index booking.idx_start", "drop index booking.idx_end", "drop table resource", "drop table booking", }); else if(todo.equals("create")) doUpdates(connection,new String[] { "set ignorecase true", "create table resource (id integer not null " + "identity primary key, name varchar(50) not " + "null, description varchar(100) not null, " + "unique (name))", "create table booking (id integer not null " + "identity primary key, resourceid integer not " + "null, email varchar(80), start datetime not " + "null, end datetime not null, foreign key " + "(resourceid) references resource (id))", "create index idx_name on resource (name)", "create index idx_start on booking (start)", "create index idx_end on booking (end)", }); else if(todo.equals("insert")) doInsertResource(connection, request.getParameter("name"), request.getParameter("description")); } %> <% if(isSchemaCreated(connection)) { %> <%-- a table with the list of data --%> <% Statement stmt = connection.createStatement(); PreparedStatement pstmt = connection.prepareStatement("select id, start, end, " + "email from booking where resourceid = ?"); try { ResultSet resources = stmt.executeQuery("select id, name, " + "description from resource"); while(resources.next()) { %> <% pstmt.setInt(1,resources.getInt(1)); try { ResultSet bookings = pstmt.executeQuery(); while(bookings.next()) { %> <% } } finally { pstmt.close(); } %> <% } } finally { stmt.close(); } %>

Name	Description
<%= resources.getString(2) %>	<%= resources.getString(3) %>
booked by <%= bookings.getString(4) %> (<%= dateFormat.format(bookings.getTimestamp(2)) %> - <%= dateFormat.format(bookings.getTimestamp(3)) %>)

<%-- a form to create entries --%> <% } %> <%-- a small form to create/drop tables in the database --%> <% } finally { connection.close(); } %>

Name:
Description: