Lesson 1
Java Servlet Database Interaction
Servlets read from and write to Databases
Many people believe the most important use of servlets is to interact with databases, which is certainly a very important use.
In this module, you will see how to do that. The prerequisites for this course call for you to know some SQL and to have seen JDBC code before. If do not have this knowledge, you will need to take what you see here at face value.
We are using a number of classes that are part of the standard Java library for working with SQL, and you will learn how to use them for specific tasks. We will not explain every facet and detail of these classes and how they
work.
Database Access
Most web applications use data from a database. It is therefore important that you know how to access and manipulate data using the Java Database
Connectivity (JDBC) API. This section does not cover JDBC and I assume you have some working knowledge of it. Instead, this section explains the two important topics when working with the database: connection pooling and the Data Access Object pattern. An sample application
is given at the end of this section.
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
...
Connection connection = null;
try {
Context context = new InitialContext();
DataSource dataSource = (DataSource)
context.lookup(jndiName);
connection = dataSource.getConnection();
} catch (NamingException e) {
...
} catch (SQLException e) {
...
} catch (Exception e) {
...
}
Calling the getConnection method on a DataSource is fast because connections are never closed; closing a connection simply returns the
connection to the pool. However, JNDI lookups are slow and, as such, the returned DataSource is often cached.
To tell the servlet container to manage a connection pool, you need to configure the container. In Tomcat, this is done by declaring a Resource
element under the Context element of the application. For example, the following Tomcat context contains a DataSource resource with an internal connection pool.
<Context path="/appName" docBase="...">
<Resource name="jdbc/dataSourceName"
auth="Container"
type="javax.sql.DataSource"
username="..."
password="..."
driverClassName="..."
url="..."
/>
</Context>
You need to enter the correct database user name and password in the username and password attributes, respectively. You also need to supply
the JDBC driver class name and the database URL in the driverClassName and url attributes, respectively. In addition, you need to include the JDBC driver library in the WEB-INF/lib directory of your application directory.
For example, the following Tomcat Context declaration defines an application with a DataSource resource that maintains a
connection pool to a MySQL database named test. The JNDI name for the DataSource is jdbc/myDataSource and the MySQL database is assumed to be in the same computer as the one running Tomcat. The user name and password for accessing the database is testuser and secret, respectively.
<Context path="/app10d" docBase="/path/to/app" reloadable="true">
<Resource name="jdbc/myDataSource"
auth="Container"
type="javax.sql.DataSource"
username="testuser"
password="secret"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test"/>
</Context>
Even though more recent applications tend to use a dependency injection framework to manage database connections, many applications running today still depend on the older method of JNDI lookup, which is why we give it a try in the sample application.
When you finish this module, you will be able to:
- Explain how Java, servlets, JDBC, ODBC, and database software work together
- Set up an ODBC data source as a System DSN
- Decide whether it is appropriate to connect to your database in
init() or getPost()
- Write code to connect to a database
- Write code to read from a database
- Write code to update records in a database