Database primer:
Integrating Database Commands into Servlets

There are four basic operations that one applies to a database:

• Create a new table (in which you must name the columns and state the "type" of each column)
• Drop a table (thereby removing all data it contains)
• Insert a new row in a table which gets interesting when inserting dynamic data
• Select some information from a table usually a subset of cols and rows, and display it in an html table

See the Hypersonic SQL database documentation for more detailed information about making SQL queries.

Several examples of the servlets below are available in this folder

Creating a table

Here are some examples of creating an sql table:

• table from hw3

  create table cs2a(unetid varchar(100),   key varchar(100),   value varchar(10000),  num integer)
  

  This creates a table with four columns (also known as fields): unetid, key, value, num. The first three can hold strings of text of lengths at most 100,100, and 10000 respectively. The last can hold an integer. We can embed this in a servlet as follows:

  (servlet(pw)
   (if (not (equal? pw "zz11aa*"))
  
     {Sorry, you need to know the password to create this table}
  
     (begin ; in this case, they do know the password, so create the table!
      (define result 
        (dbquery {create table cs2a(unetid varchar(100), key varchar(100), value varchar(10000), num integer)}))
      {We have created the table and the result is [result]}
      )
   )
  )
  

• Table of numeric values:

  create table tjh_survey(q1 integer, q2 integer, q3 integer, q4 integer, q5 integer, q6 integer)
  

  This creates a table that lets us store the answers to six quiz questions. The answers must all be whole numbers (i.e. no decimals). Note that I have prefixed the table name with my initials so as to not conflict with other members of the class. We all have access to each others databases in this class.

  (servlet(pw)
   (if (not (equal? pw "zz11aa*"))
  
     {Sorry, you need to know the password to create this table}
  
     (begin ; in this case, they do know the password, so create the table!
      (define result 
        (dbquery {create table tjh_survey(q1 integer, q2 integer, q3 integer, q4 integer, q5 integer, q6 integer)}
      {We have created the table and the result is [result]}
      )
   )
  )
  

• a registration table:

  create table tjh_reg(email varchar(100),  password varchar(100),  status varchar(100))
  

  Here is a table that could be used for a website that requires a password. The table would store the users email, their website-password, and the status of their registration.

  (servlet(pw)
   (if (not (equal? pw "zz11aa*"))
  
     {Sorry, you need to know the password to create this table}
  
     (begin ; in this case, they do know the password, so create the table!
      (define result 
        (dbquery {create table tjh_reg(email varchar(100),  password varchar(100),  status varchar(100))}
      {We have created the table and the result is [result]}
      )
   )
  )
  

  For example, the first state of registering would be to submit an email address and pick a password, The servlet could then send an email with a link back to a page on this site and only make the registration active after the user has visited this site from the email link.... One tecnnique here is to store a random number in the status field and put that number in the email. When the user gets the email, they then connect back to the site and enter the registration number. This would confirm that they had indeed received the email....

In general, to create a table you need to do the following:

1. Decide on a name for your table. Remember to prefix it with your intials or your unetid, so as not to conflict with anyone else in the class.
2. Decide on the names of the columns in your table. What will your table be storing? Think of good names (each consisting of a single word with no punctuation).
3. Decide what type of values will be stored in each column. Your choices are
   • varchar(N) where N is the maximum number of characters
   • integer for whole numbers
   • double for decimal numbers

More choices can be found in the HSQLDB documentation on Data Types.

Dropping a table

Dropping a table will remove it from the database and will permanently delete all data stored in that database..... The SQL query to drop a table is very simple:

drop table NAME

where NAME is the name of the table, e.g. cs2a or tjh_reg

(servlet(pw)
 (if (not (equal? pw "zz11aa*"))

   {Sorry, you need to know the password to drop this table}

   (begin ; in this case, they do know the password, so create the table!
    (define result 
      (dbquery {drop table tjh_reg}))
    {We have dropped the table and the result is [result]}
    )
 )
)

Inserting a new row into a table - doc

The SQL to insert a new row into a table has the following form:

insert into NAME values( VALUE1, VALUE2, ..., VALUEN);

where NAME is the name of the table and VALUE1, VALUE2, ... are the values to be stored in that row. Below are some examples using our tables created above:

insert into cs2a values( 'tjhickey', 'role', 'instructor')
insert into cs2a values( 'cfuchs', 'role', 'ta')
insert into tjh_reg ( 'tjhickey@brandeis.edu', 'z*3$$9a!', 0)
insert into tjh_survey( 3, 1, 5, 5, 5, 2)
insert into tjh_survey( 2, 2, 2, 3, 4, 5)

Note that textual data must be enclosed in single quotes but numeric data is not enclosed in single quotes...

Inserting dynamic content into a new row into a table

Most of the time, we will want to insert dynamic data into a role, that is data that we have obtained from the user. We can do this in the usual way with curly braces denoting the query and square braces indicating the dynamic content, but for textual data we will also need to preprocess the users dynamic data to make sure it does not contain any inappropriate characters (e.g. single quotes...). This is done using the toSQL procedure.

(servlet(unetid key value)
  (define quoted-unetid (toSQL unetid))
  (define quoted-key (toSQL key))
  (define quoted-value (toSQL value))

  (define result (dbquery {insert into cs2a values([quoted-unetid],[quoted-key],[quoted-value])}))

  {the data has been inserted into the table with result=[result]}
)

It is a good idea to display the result of the dbquery in the response page because it will let you see if there is an error in the SQL you used. Numeric data does not have to be quoted...

(servlet (q1 q2 q3 q4 q5 q6)
 (define a1 (Integer. q1))
 (define a2 (Integer. q2))
 (define a3 (Integer. q3))
 (define a4 (Integer. q4))
 (define a5 (Integer. q5))
 (define a6 (Integer. q6))

 (define result (dbquery {insert into tjh_survey values([a1],[a2],[a3],[a4],[a5],[a6])}))

 {The following values have been inserted into the survey:
  [(ol (list a1 a2 a3 a4 a5 a6))]
  with result = [result]
  }
)

try it.

All of these methods can be mixed...

(servlet(key value)
  (define quoted-key (toSQL key))
  (define numeric-value (Integer. value))

  (define result (dbquery {insert into cs2a values('tjhickey',[quoted-key],[numeric-value])}))

  {the data has been inserted into the table with result=[result]}
)

You can also skip the "define"s and use a slightly more complex dbquery instead:

(servlet(key value)

  (define result (dbquery 
       {insert into cs2a values('tjhickey',[(toSQL key)],[(Integer. value)])}
     ))

  {the data has been inserted into the table with result= [result]}
)

Selecting information from the table - doc

The subset of the SQL language for selecting information from a table is very expressive, but we will only look at a few of its features.

Selecting all columns

select * from tjh_survey

This selects all of the data from the survey and returns it as a list of lists. Each inner list represents a row of data (in this case six integers).

(servlet()
 (define result (dbquery {select * from tjh_survey}))
 {The raw survey data is [(table result)]}

)

try it.

Selecting some columns

select q1,q3,q5 from tjh_survey

This selects three of the six columns from the tjh_survey table and returns a list of triples, one for each row of the table.

(servlet()
 (define result (dbquery {select q1,q3,q5 from tjh_survey}))
 {The odd numbered question data is [(table result)]}
)

try it.

Selecting some columns and sorting them

select q1,q3,q5 from tjh_survey order by q1,q3,q5 asc

This displays the columns in lexicographically increasing order.

Selecting some rows

select * from tjh_survey where q1=1 and q2+q3+q4+q5+q6 < 10

This selects all columns from those rows where the specified conditions are met. Thus it only shows those rows where q1 is 1 and the sum of the other q's is less than 10.

(servlet()
 (define result (dbquery {select * from tjh_survey where q1=1 and q2+q3+q4+q5+q6 < 10}))
 {The data with q1 = 1 and all other answers summing to less than 10 is [(table result)]}
)

try it.

Counting the number of rows

select count(*) from tjh_survey where q1=1 

This returns the number of rows in the tjh_survey in which the q1 field is 1. Actually, this returns a list of lists that has the following form:

(("count(*)") (12))

where the first sublist is the headers of the columns displayed and the second sublist is the real data. If you want to actually access that count and do something with it, you need to extract it from the dbquery output. Notice that the number (12) is the first element in the second sublist, thus we could do something like the following:

(servlet()
 (define result (dbquery {select count(*) from tjh_survey where q1=1}))
 (define the-count (first (second result)))

 {The number of entries with q1=1 is [the-count]<br/> 
  <!-- the result of the query was [result] -->
  The entire table is [(table (dbquery "select * from tjh_survey order by q1 asc"))]
 }
)

try it. Note that I have put the result of the query inside an HTML comment so that you can see it if you use the "view source" browser option, but it won't be visible to the casual website visitor.

Summarizing a table

select q1,count(*) from tjh_survey group by q1

This returns a result where the entries show how many rows of tjh_survey had each particular value of q1, e.g.

(("q1" "count(*)")
 (0 10)
 (1 5)
 (2 13)
 (3 24)
 (5 3)
)

This would indicate that 13 of the surveys had q1=2 and none of the surveys had q1=4.

(servlet()
 (define result (dbquery {select q1,count(*) from tjh_survey group by q1}))
 {The breakdown of answers to question 1 is as follows:
   [(table result)]<br/> 
 }
)

try it.