Many web sites are database-backed web sites.
A database is a collection of (persistent) data, accessibile via software that can query and update the data.
There are many database vendors (Oracle, IBM, Informix, Microsoft) that sell zillions of database management systems (Oracle 8, DB2, Universal Server, SQL Server). You can download and install free (with restrictions) database systems on your computer too (e.g. Postgres, MySQL).
It helps to pick a particular setup and show examples that you can build, run, and experiment with on your own. I'll assume the following:
You should need only minor changes to use a different web server (such as Apache) or a different database (such as Postgres).
Download and install Resin, if you haven't already.
Locate the Resin configuration file (resin.conf) and make any changes you want. The default configuration should be fine if you are just playing around on your own computer.
Start the web server and see that it is working by pointing your browser to "http://localhost:8080/". If you do not like the 8080, change the resin.conf file to run the server at port 80.
Put a new JSP page in the documents directory called
hello.jsp that looks like this:
<html> <body> <p>Hello, two plus two is <%= 2 + 2 %></p> </body> </html>
then point your browser to "http://localhost:8080/hello.jsp" to make sure the page gets served up.
Download and install MySQL, if you haven't already.
You'll probably installit in a directory called mysql.
You will get subdirectories called bin (containing
the server, client, and other tools), and data (containing
further subdirectories for your databases).
If you're installing MySQL, you're probably following the instructions that come with the documentation. Make sure you go through the section entitled "Post-installation Setup and Testing" (unless you're an expert at this kind of stuff).
Run the database server, it will be a command like
mysqld. By default, the server runs on port 3306.
Note that you'll eventually need to stop the server, with
a command such as mysqladmin -u root shutdown.
Do the tutorial at http://mysql.com/documentation/mysql/bychapter/manual_Tutorial.html
Let's make a little database for a university. We'll name the database "university". If you already took the tutorial, you already know how to do this. You first startup the client, then enter
create database university;
Now execute the following
#
# Simple script to create a few tables and fill them with
# sample data.
#
# ----------------------------------------------------------------------------
# Person table
# ----------------------------------------------------------------------------
drop table if exists Person;
create table Person (
id char(9) not null primary key,
name varchar(100),
birthdate date);
insert into Person (id, name, birthdate)
values ('231342453', 'joey', '1988-04-22');
insert into Person (id, name, birthdate)
values ('2894576503', 'svetlana', '1968-11-20');
insert into Person (id, name, birthdate)
values ('111223333', 'irina', '1977-01-29');
insert into Person (id, name, birthdate)
values ('987654321', 'raisa', '1967-02-16');
# ----------------------------------------------------------------------------
# Department table
# ----------------------------------------------------------------------------
drop table if exists Department;
create table Department (
id char(4) not null primary key,
name varchar(100),
budget double,
chair char(9) not null references Person(id));
insert into Department (id, name, budget, chair)
values ('ELEC', 'Electrical Engineering', 60000.0, '987654321');
insert into Department (id, name, budget, chair)
values ('PHIL', 'Philosophy', 100000.0, '111223333');
# ----------------------------------------------------------------------------
# Professor table
# ----------------------------------------------------------------------------
drop table if exists Professor;
create table Professor (
id char(9) not null primary key references Person(id),
degree varchar(10),
department char(4) references Department(id));
insert into Professor (id, degree, department)
values ('111223333', 'PhD', 'ELEC');
# ----------------------------------------------------------------------------
# Student table
# ----------------------------------------------------------------------------
drop table if exists Student;
create table Student (
id char(9) not null primary key references Person(id),
major char(4) references Department(id));
insert into Student (id, major)
values ('231342453', 'ELEC');
You can check that the database was succcessfully created and loaded:
mysql> show tables; +----------------------+ | Tables_in_university | +----------------------+ | department | | person | | professor | | student | +----------------------+ 4 rows in set (0.00 sec) mysql> select * from person; +-----------+----------+------------+ | id | name | birthdate | +-----------+----------+------------+ | 231342453 | joey | 1988-04-22 | | 289457650 | svetlana | 1968-11-20 | | 111223333 | irina | 1977-01-29 | | 987654321 | raisa | 1967-02-16 | +-----------+----------+------------+ 4 rows in set (0.05 sec)
Now let's see how to access this data from a web application.
While it is possible to put all information about the database, including the database name, port, user name and password directly into a web page, doing so is bad for maintainability and security reasons.
The best thing to do is to tell Resin about your database by placing the following element inside the <caucho.com> element in the Resin configuration file:
<resource-ref> <res-ref-name>jdbc/university</res-ref-name> <res-type>javax.sql.DataSource</res-type> <init-param driver-name="com.caucho.jdbc.mysql.Driver"/> <init-param url="jdbc:mysql_caucho://localhost:3306/university"/> <init-param user=""/> <init-param password=""/> </resource-ref>
You'll want to fill in the actual database user name and password, and make other adjustments specific to your own site, of course.
Here is the source code of a JSP page that connects to the university database, executes a query, writes the result to an HTML table, and disconnects.
<%@ page language=java %>
<%@ page import='java.sql.*' %>
<%@ page import='javax.sql.*' %>
<%@ page import='javax.naming.*' %>
<html>
<body>
<p>Here is a table from the university database</p>
<%
Context env = (Context)new InitialContext().lookup("java:comp/env");
DataSource source = (DataSource)env.lookup("jdbc/university");
Connection conn = source.getConnection();
try {
Statement statement = conn.createStatement();
%>
<table border="1">
<tr>
<%
ResultSet rs = statement.executeQuery("select * from Department");
ResultSetMetaData metaData = rs.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
out.print("<th>" + metaData.getColumnName(i) + "</th>");
}
%>
</tr>
<%
while (rs.next()) {
%>
<tr>
<td><%= rs.getString(1) %></td>
<td><%= rs.getString(2) %></td>
<td align="right"><%= rs.getDouble(3) %></td>
<td><%= rs.getString(4) %></td>
</tr>
<%
}
%>
</table>
<p>Now here is the result of a more complex query</p>
<table border="1">
<tr>
<%
statement = conn.createStatement();
rs = statement.executeQuery(
"select d.id, d.name, d.budget, p.name "
+ "from Department d, Person p "
+ "where d.chair = p.id "
+ "and d.budget > 500.0");
metaData = rs.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
out.print("<th>" + metaData.getColumnName(i) + "</th>");
}
%>
</tr>
<%
while (rs.next()) {
%>
<tr>
<td><%= rs.getString(1) %></td>
<td><%= rs.getString(2) %></td>
<td align="right"><%= rs.getDouble(3) %></td>
<td><%= rs.getString(4) %></td>
</tr>
<%
}
} finally {
conn.close();
}
%>
</table>
</body>
</html>
Well, let's look at what's important and what you really need to know first.
Know the basic ideas behind the web server (i.e. Resin, Apache, ...) and web browser (i.e. Netscape Navigator, Lynx, Apache, MSIE...), and behind a database server (i.e. mysqld) and a database client (i.e. mysql).
Know that when a web server is set up, there is a configuration file that you can tweak to do many things, like set default ports, name virtual directories, specify external resources, etc.
Know that to query and update a database, you don't have to use a specialized client like mysql; instead, you can write Java code to query or access. Naturally, then, if you use JSPs, you can embed the database code in a web page.
The Java code that accesses the database may look sophisticated, but it is based on a few simple concepts: (1) connection logic, (2) statements, which are executed, (3) result sets and result sets' metadata.
When accessing a database from a JSP, much of the connection logic is specified in the web server's configuration file. The syntax depends on the database. If you are using MySQL, just "follow the example" above. The only thing likely to change from my example is the name of the database!!
Databases, like web servers, have to be administered. Usually a person known as a DBA does this. The DBA will assign user names and passwords, set up databases, back them up, and do all other sorts of maintentance. In a more realistic site you would call the DataSource.getConnection() method with two arguments -- a user name and password -- that you got from a login form.
When accessing a database from a JSP, the real work
is done by making a statement object and calling executeQuery
on it. (This is not the only way, but it's the simplest.
There are better and more efficient ways, but you should learn this
way first.) The main kinds of queries are select,
insert, update, delete, create table
and drop table. There are dozens more, but you
can get by fine with just these! To learn the exact syntax
of these SQL statements, you can visit any SQL tutorial web site,
or get a book on SQL. And practice, practice, practice.
Note that a result set represents the content of the result of a query, and its metadata describes the names and types of the result's columns (among other things).