Web Resources for PosgreSQL

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

Introduction to PostgreSQL at the University

PostgreSQL is a free relational database management system. A PostgreSQL server has been installed on the department DB server (server8.cs.uofs.edu). Students may have their own databases created on this server and access the databases through psql and the Web using various techniques such as DBI/DBD, JDBC, and PHP.

PostgreSQL Tutorial

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

Becoming a User of PostgreSQL

To access PostgreSQL one must be a user of PostgreSQL and have permission to access a database. To become a user, you may send an email to the DBA. In your email, use "PostgreSQL Account" as the subject and in the body provide your department user id and an initial password you would like to use. The DBA will inform you when your account is set up via email.

If you are taking CMPS341 or SE521, your instructor may have told you which database(s) you should use. Go to Using Psql to see how to connect to and interact with the database. If you wish to have our own database, go to Creating a Database.

PostgreSQL Tutorial

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

Creating a Database

Currently no student is allowed to create databases under PostgreSQL. If you wish to have your own database for your senior project or thesis project, send the DBA a message describing the purpose of the database and provide a name for the database. Normally you want to use a name that describes the contents of your database or your project. The DBA will inform you via email when the database is created.

PostgreSQL Tutorial

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

Using psql via server1 or server2

Currently the PostgreSQL server only accepts connections from server1.cs.uofs.edu, server2.cs.uofs.edu, or the department Web server, www.cs.uofs.edu. No user is allowed to log in to the server. You can interact with your database from server1 or server2 using the psql client application. Assume the database you need and have permission to access is mydb and your Postgresql user ID is toc2.

To use psql, first log in to either server1 or server2. Then enter:

psql -h server8 -d mydb -U toc2

If you get an error message saying psql is not found, you can either modify your PATH environment variable or enter

/usr/local/bin/psql -h server8 -d mydb -U toc2

Here, option -h specifies the host on which PostgreSQL server is running, -d the database you want to connect to, and -U your postgresql user id. Note that your postgresql user id may not be the same as your department account user id.

If the psql command is executed successfully, the following should be displayed,

Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit
mydb=#

Now you are successfully connected to your database. Just in case you don't remember which database you are using, you can find its name in the command prompt.

Assume this is a new database, then it should be empty now. The next step is to create tables.

PostgreSQL Tutorial

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

Creating Tables

You may create tables in two different ways. The first is to create tables interactively.

Interactive
You need to type in SQL statements word by word. For example, to create a student table with three attributes, type in the following:

mydb=# create table student (
mydb=# id        char(5),
mydb=# name      varchar(20),
mydb=# address   varchar(30),
mydb=# primary key (id)
mydb=# );

For data types of PostgreSQL click here. You may type in a SQL statement on multiple lines. The statement is not executed until a simicolon(;) is entered. After a simicolon is entered, the following should be displayed.

CREATE
mydb=#

Now you can execute the \d psql command to list the tables in the database.

mydb=# \d

The following should be displayed. Owner will be replaced with your user ID.

    List of relations
   Name   | Type | Owner
----------+-------+-------
student   | table | toc2
(1 rows)

One disadvantage of the interactive method is that you have to type perfectly, without a single typing error, since it is very hard to correct or change what you have typed in.

Using Script Files

A script file can be created using any text editor and stored in any directory on either server1.cs.uofs.edu or server2.cs.uofs.edu. This should be done outside the psql client application.

For example, use vi (or pico or any text editor) to create a file and name it table.sql with the following statements as its contents.

create table student (
id        char(5),
name      varchar(20),
address   varchar(30),
primary key (id)
);
create table faculty (
id char(5),
name varchar(20),
office varchar(20),
phone varchar(11),
primary key (id)
);
create table course (
id char(5),
title varchar(30),
instructor char(5),
primary key (id),
foreign key (instructor) references faculty(id)
);
create table stakingc (
sid char(5),
cid char(5),
grade float4,
primary key (sid, cid),
foreign key (sid) references student(id),
foreign key (cid) references course(id)
);

After the file is created, change to the directory where the file is stored and then start psql

/usr/local/bin/psql -h server8 -d mydb -U toc2

and then at the prompt enter the following to execute the script,

\i table.sql

The following will be displayed.

            CREATE
            CREATE
            CREATE
            CREATE
            mydb=#

You may use command \d to list tables in the database. it would list

Now your tables have been successfully created in your mydb database. Our next step is to enter data into the tables.

PostgreSQL Tutorial

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

Insert Data into Tables

To insert data into a table, you may insert one entry at a time by entering a INSERT SQL statement or you can put multiple INSERT statements in a script file and then use \i to execute the script. The following shows both methods.

Enter One Entry at a Time

First use psql to connect to your database (mydb):

/usr/local/bin/psql -h server8 -d mydb -U toc2

then at the psql prompt enter:

INSERT INTO "student" VALUES ('S1001','John','100 Monroe, Scranton');

You may enter the statement on multiple lines, it would not be executed until a semicolon is entered.

Enter Data in Batch Mode

To enter data in a batch mode, put INSERT statements (the following statements) into a file (let's name it data.sql),
connect to your database (mydb):

/usr/local/bin/psql -h server8 -d mydb -U toc2

INSERT INTO "student" VALUES ('S1002','Tony','200 Adams, Scranton');
INSERT INTO "student" VALUES ('S1003','Alex','209 WiLlow, Dalton');
INSERT INTO "student" VALUES ('S1010','Some Name','AMH 118');
INSERT INTO "student" VALUES ('S1020','From the WWWW','WWW@Home');
INSERT INTO "student" VALUES ('F1040','G.W. Bush','The Whitel House');
INSERT INTO "student" VALUES ('S1050','Info System','amh 118-2001');
INSERT INTO "student" VALUES ('S2000','Mike Beckish','STT 414');

INSERT INTO "faculty" VALUES ('F3001','Yaodong Bi','STT 478','6108');
INSERT INTO "faculty" VALUES ('F3002','Dennis Martin','STT 477','6107');
INSERT INTO "faculty" VALUES ('F3003','Paul Jackowitz','STT 479','6109');
INSERT INTO "faculty" VALUES ('F3040','Dick Cheney','The White House','xxx-xxx-xxx');
INSERT INTO "faculty" VALUES ('F9999','TBA','Registrar','941-6108');
INSERT INTO "faculty" VALUES ('F0000','TBA','Registrar','941-6108');

INSERT INTO "course" VALUES ('C2001','Programming in Java','F3001');
INSERT INTO "course" VALUES ('C2002','Operating Systems','F3002');
INSERT INTO "course" VALUES ('C2003','Computer Archiecture','F3003');
INSERT INTO "course" VALUES ('C2004','Software Engineering','F3001');
INSERT INTO "course" VALUES ('C2020','Prog the WWW','F3001');
INSERT INTO "course" VALUES ('C2030','Prog the Web','F3001');

INSERT INTO "stakingc" VALUES ('S1001','C2001',3.5);
INSERT INTO "stakingc" VALUES ('S1001','C2002',3.9);
INSERT INTO "stakingc" VALUES ('S1002','C2003',3.2);
INSERT INTO "stakingc" VALUES ('S1003','C2002',3.9);
INSERT INTO "stakingc" VALUES ('S1003','C2003',4);
INSERT INTO "stakingc" VALUES ('S1001','C2004',NULL);
INSERT INTO "stakingc" VALUES ('S1002','C2001',3.99);

then execute the script using \i at the psql prompt:

\i data.sql

The above data will be inserted into the database in a batch.

Next section is to delete data from your database.

PostgreSQL Tutorial

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

Delete Data from Tables

The SQL DELETE statement may be used to delete rows of data from a table. The syntax of DELETE is:

DELETE FROM table [WHERE condition];

For example, suppose table stakingc has the following tuples,

mydb=# select * from stakingc;
sid | cid | grade
-------+-------+-------
S1001 | C2001 |   3.5
S1001 | C2002 |   3.9
S1002 | C2003 |   3.2
S1003 | C2002 |   3.9
S1003 | C2003 |     4
S1001 | C2004 |
S1002 | C2001 | 3.99
(7 rows)

and the following DELETE is executed,

mydb=# delete from stakingc where sid='S1002';

then, the table would have the following tuples left.

uofsis=# select * from stakingc;
sid | cid | grade
-------+-------+-------
S1001 | C2001 |   3.5
S1001 | C2002 |   3.9
S1003 | C2002 |   3.9
S1003 | C2003 |     4
S1001 | C2004 |
(5 rows)

Of course, you may put multiple DELETE statements in a script file and use \i command to execute all the DELETE in a batch.

PostgreSQL Tutorial

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

Update Data in Tables

You can use the UPDATE statement to replace values of columns (attributes) in a table. The syntax of UPDATE is as follows:

UPDATE table SET col = expression [, ...]
[ FROM fromlist ]
[ WHERE condition ]

For example, if the following UPDATE is executed on the above table stakingc

uofsis=# UPDATE stakingc SET grade=3.99 where sid='S1001' and cid='C2004';

after the statement, the stakingc table would look like:

uofsis=# select * from stakingc;
sid | cid | grade
-------+-------+-------
S1001 | C2001 |   3.5
S1001 | C2002 |   3.9
S1001 | C2004 | 3.99
S1003 | C2002 |   3.9
S1003 | C2003 |     4
(5 rows)

PostgreSQL Tutorial

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

Query Data from Database

Basic SQL Queries

The syntax of basic SQL queries:

    SELECT <attribute list>
    FROM   <table list>
    FROM   <condition>

Example: retrieve the name, office, and phone of faculty whose id is 'F3001'. The SELECT statement for this query can be as follows:

    SELECT name, office, phone
    FROM   faculty
    FROM   id = 'F3001';

Enter the SELECT statement in psql:

uofsis=# SELECT name, office, phone
uofsis-# FROM faculty
uofsis-# WHERE id = 'F3001';

The following result is displayed:

name | office | phone
------------+---------+-------
Yaodong Bi | STT 478 | 6108
(1 row)

SQL Queries with Multiple Tables

When an SQL statement need information from more than one table, the WHERE clause of the SELECT statement may be used to specify how the tables are to be JOINed together.

Example: retrieve for all student the student name, course title and the grade the student received for the course.

    SELECT s.name, c.title, stc.grade
    FROM   student s, stakingc stc, course c
    WHERE s.id = stc.sid AND stc.cid = c.id;

Enter the SELECT statement in psql:

uofsis=# SELECT s.name, c.title, stc.grade
uofsis-# FROM student s, stakingc stc, course c
uofsis-# WHERE s.id = stc.sid AND stc.cid = c.id;

The following result would be displayed:

PostgreSQL Tutorial

Introduction
Sign up
Create DB
Using psql
Create Tables
Insert Data
Delete Data
Update Data
Query DB
Resource

PostgreSQL On-Line Resources

Home page of PostgreSQL

PostgreSQL: Introduction and Concepts by Bruce Momjian. You may read the book online. It is the most authoritive book for PostgreSQL.

Servlets, JDBC, and PostgreSQL

I ntroduction
JDBC
Servlets
An Example
Resource

Introduction

Servlets are Java modules that can be programmed and added to a Web server as extensions. Servlets can be used to replace CGI scripts for server-side programming.

JDBC is an application programming interface (JDBC API) that enables Java programs to access databases that may be located on the local machine or a remote database server. JDBC is a trademark name and not an acronym.

With HTML, Servlets, JDBC, and DBMS, we can easily build Web-based three-tier systems -- client, Web server, and database server. HTML is used to design the client-side interface that is to be executed on a regular Web browser such as Netscape and Internet Explore. Servlets can be employed to do server-side programming and JDBC can be used to query a database(s) for the requests from clients. The database(s) may be located on a remote database server.

This section describe how to use Servlets and JDBC and PostgreSQL to implement three-tier Web-based applications.

Servlets, JDBC and PostgreSQL

I ntroduction
JDBC
Servlets
An Example
Resource

Your First JDBC Program: SimpleJDBC.java

Using JDBC to Access PostgreSQL Databases

Basic Steps:

Using JDBC to access databases can be described in five steps. The first step is to load the JDBC driver for the DBMS you need to interact with; the second step is to connect to the database; the third step is to create a statement, the fourth step is to interact with the database mainly via SQL statements; and when the database is no longer need to be connected, the last step is to disconnect from the database. This section will discuss the five steps in detail and conclude it with a complete example.

Step 1: Load the JDBC drivers

A JDBC driver is a JDBC API for a specific DBMS. A Java program can load several JDBC drivers at any time. This allows the program to interact with more one database running under different DBMS's. The following line of code loads the JDBC driver for PostgreSQL,

Class.forName("org.postgresql.Driver");

Step 2: Connect to the database

A connection can be established via the getConnection method of the DriverManager class. The getConnection method accepts three parameters -- the database, user name, and password. The following example connects to a database named "uofsis" which is under PostgreSQL DBMS on server8.cs.uofs.edu, the user is "bi", and password is null.

    String url = "jdbc:postgresql://server8.cs.uofs.edu/";
    String dbname = "uofsis";
    String usernm = "bi";
    String passwd = "";
    Connection db = DriverManager.getConnection(
                    url+dbname, usernm, passwd);

Step 3: Create a statement

Statement st = db.createStatement();

Step 4: Interact with the database

    String sql = "SELECT name, title " +
                 "FROM faculty f, course c " +
                 "WHERE f.id = c.instructor";
    ResultSet rs = st.executeQuery(sql);

Step 5: Disconnect from the database

st.close();
db.close();

An Example

The following is a link to a real Java program with JDBC. It basically puts all the steps mentioned above together.

Servlets, JDBC and PostgreSQL

Introduction
JDBC
Servlets
An Example
Resource

Your First Servlet: SimpleServlet.htm

Using Servlets for Web-Server-Side Programming

Introdtuction

Sevlets are normally used to enhance the functionality of WWW servers. Web-based servlets typically extend class HttpServlet that defines two methods to handle the two common types of requests. Method doGet() is designed to respond GET requests and method doPost() to respond POST requests. Methods doGet and doPost receive as arguments an HttpSerletRequest object and an HttpServletResponse object that enable interactions between the client and the server.

A servlet application normally consists of two parts. One is an HTML file which runs on the client side. It generally contains a HTML form for the user to enter any data that is to be passed tot he server as part of the request. The action for the form is a link to a servlet. The servlet acts like a CGI script to process the request and then responds by sending information back to the client. In the following an HTML file is shown and then the basic steps for designing servlets are described.

Basic Steps for HTML:

This HTML file invokes the SimpleServlet servlet designed above. It basically contains two parts. The second part includes a form which can accept input data from the user - user frist name and last name. The aciton for the form is to invoke the SimpleServlet servlet. Whent the user click the submit button, the servlet will be invoked by the web server.
The relevant lines are highlighted in blue. The first part uses JavaScript to do primitive data checking. for example, if you expect the user to enter the first name and last name, then JavaScript functions can be designed to check the two field before the request is sent to the Web server. Thus it can reduce unnecessary traffic on the Internet.

<html>
<head>
<title>UOFSIS 0.6: Registrar www Interface</title>
<SCRIPT LANGUAGE="JavaScript">

</SCRIPT>
</head>

<body>
<h1><center>Simple Servlet at UOFS</h1>
<form action="http://heineken.cs.uofs.edu/servlet/SimpleServlet" method="POST">
<pre>
* First Name: <input type="text" name="firstname" value="">
* Last Name: <input type="text" name="lastname"value="">
</pre>

Basic Steps for Servlets:

Step 1: Import Servlet Classes

Import the following classes:

   import java.io.*;
   import javax.servlet.*;
   import javax.servlet.http.*;

Step 2: Import Servlet Classes

Derive your servlet from HttpServlet:

public class SimpleServlet extends HttpServlet

Step 3: Put servlet initialization in the init function

The init() function is executed only once when the servlet is first time executed. You may put JDBC connection or other initialization functions here. If you don't have any thing special, the following suffices.

public void init(ServletConfig config)
throws ServletException
{
super.init(config);
}

Step 4: In doPost(), process request and print in HTML the response

When a POST request is made by the client, doPost() will be invoked the Web Server. You can process the request and response by sending back a HTML text.

     public void doPost (HttpServletRequest req, HttpServletResponse resp)
             throws ServletException, IOException
     {
       String fname = req.getParameter("firstname");
       String lname = req.getParameter("lastname");

displayHTML(resp, fname, lname);
}
The doPost() has two parameters - one for request and the other for response. The above segment of code shows how to get the value of a parameter passed from the HTML form. String literals "firstname" and "lastname" are the names for two text input boxes on the HTML form. Next will show how to print HTML text back the client.

     private void displayHTML(HttpServletResponse resp, String fn, String ln)
             throws ServletException, IOException
     {
       PrintWriter out = resp.getWriter();

       resp.setContentType("text/html");
       out.println("<HTML><HEAD><TITLE>");
       out.println("Registrar Office");
       out.println("</TITLE></HEAD><BODY>");
       out.println("<H1><CENTER>Hello, "+fn+" "+ln+"</CENTER></H1>");
       out.println("<H1><CENTER>Your first servlet works!</CENTER></H1>");
       out.println("</BODY></HTML>");
       out.close();
    }

The above function prints HTML text back to the client. The body of this function could be included in the doPost() rather than in a separate function. Its first parameter is passed in by the doPost function. First, we get a PrintWriter which can be used to output HTML statements. Second, we set up the ContentType, then use the PrintWriter (out) to print HTML statements.

Step 5: Compile the Servlet and put the class in the servlet directory

Compile the servlet program.

javac SimpleServlet.java

Copy SimpleServlet.class to /usr/local/jserv/servlets/, that is the default servlets direstory the Web server uses.

cp SimpleServlet.class /usr/local/jserv/servlets/

An Example: Source Code:

SimpleServlet.htm: This the HTML file. It contains a HTML form for user to pass parameters to the Servlets.
SimpleServlet.java: This is the Java Servlet that processes requests s

Run the program:

The HttpServletRequest and HttpServletResponse Interfaces:

HttpServletRequest Interface

The HttpServletRequest object passed by the WWW server to the doGet or doPost methods contains the request from the client. It provides a rich set of methods for accessing request parameters. A short list of those includes:

String getParameter(String name): return the value of a parameter indicated by the name parameter as part of the GET or POST requests
Enumeration getParameterNames(): return the names of all the parameters of a GET or POST request.
String[] getParameterValues(String name): return an array of strings containing the values for a specified servlet parameter.
Cookie[] getCookies(): return an array of Cookie objects stored on the client by the server. Cookies can be used to uniquely identify clients to the server.
HttpSession getSession(boolean create): return an HttpSession object associated with the client's current browsing session. HttpSession severs a similar purpose as cookies.

HttpServletResponse Interface

The doGet and doPost methods receive an object that implements the HttpServletResponse interface. The interface provides a set of methods for sending information back to the client. A list of commonly used methods are listed below:

void setContentType(String type): Specifies the type of response back to the browser. For example, "text/html" speecifies the response is an HTML document.
PrintWriter getWriter(): returns a text-based output stream for text data to be sent back to the browser. For example, it can be used to print the HTML statements to the browser..
ServletOutputStream getOutputStream(): return a binary output stream for binary data to be sent back to the browser..

void addCookie(Cookie cookie):

Servlets, JDBC and PostgreSQL

Introduction
JDBC
Servlets
An Example
Resource

UOFSIS: Registrar Web-Based Interface

An Example: UOFSIS Information System

The UOFSIS Information System is an example to show a Web-based three-tier architecture. A client can use any commonly used Web browser to interact with the system. User requests are sent to a Java servlet located on a Web server (in this example, the Web server is heineken.cs.uofs.edu). The servlet interacts with another program which accesses the database server to get the data for the request. The database server is the last tier and it hosts the PostgreSQL DBMS (in this example, the database server is server8.cs.uofs.edu). In the following, a slide presentation shows the requirements analysis, design, and deployment. Then the source code files are attached. Finally you may experience how the system works by selecting the HTML user interface link.

Analysis and Design

Slide Presentation (It is better to view this using Internet Explore.)

Source Code:

RegistrarFRM.htm: This the HTML file. It contains a HTML form for user to pass parameters to the Servlets. A user from the Registrar Office would start this file first.
RegistrarServ.java: This is the Java Servlet that processes requests sent from RegistrarFRM.htm. This Servlet itself does not access the database. It merely handles the input parameters from the form and then displays the output back to the client. Database access for the request is carried out by another program -- RegistrarMngr.java
RegistrarMngr.java: This program will be called by RegistrarServ.java. It would apply all business rules to requests from clients. This is a control class in the Unified Software Development Process while RegistrarServ.java is a boundary class because it only handles how the user is to interact with the system.

Run the program:

Servlets, JDBC and PostgreSQL

Introduction
JDBC
Servlets
An Example
Resource