Chapter 11
Access Database
© 2002, Sun Microsystems, Inc. All rights reserved.
Objectives
• Be able to efficiently insert data into a SQL
database.
– use JDBC prepared statements.
– use JDBC transactions.
• Display query result by using JSP, JavaBeans,
and Servlets.
• Demo:
– Insert Data System, and display query result.
• Lab exercise:
– Use different JDBC APIs to access cloudscape
database.
Data Generation System
Data generation
Client
1 Browser:
pure HTML
http
2
Java Servlet
JDBC
RDMS
Web Server
Database Schema
chart id value label extra
1 0.75 "ABC"
id:
id: Integer
Integer maxID:
maxID: Integer
Integer
2 0.5 "Half"
value:
value: Double
Double 3… … maxID
label:
label: Char[10]
Char[10] … … … 11,497
11,497 0.785 "Sine"
• Two simple tables.
• id is primary key for chart table.
• Data values are double-precision.
• Each value has an associated label.
• Only one row in extra table.
Insert Data Demo
Form Based HTML Calls Servlet
<FORM
ACTION="/sample/servlet/ChartServlet"
METHOD="POST" ENCTYPE="application/x-www-form-urlencoded">
<H1>Add Chart Data</H1>
Number of data points to add: <INPUT TYPE="TEXT"
NAME="number" SIZE="7" MAXLENGTH="7"></H3>
<HR ALIGN="CENTER">
<INPUT TYPE="RADIO" NAME="RadioGroup" VALUE="random">
Random</P>
<P><INPUT TYPE="RADIO" NAME="RadioGroup" VALUE="sine">
Sine</P>
<P><INPUT TYPE="RADIO" NAME="RadioGroup" VALUE="half">
0.5</P>
<HR ALIGN="CENTER">
<INPUT TYPE="SUBMIT" NAME="Submit" VALUE="Submit"></H3>
</FORM>
• Calls servlet named "ChartServlet"
when Submit button is pushed.
• Servlet gets values of named
parameters.
ChartServlet Methods
public class ChartServlet extends HttpServlet
implements SingleThreadModel {
public void init(ServletConfig config);
public void destroy();
public void doPost(HttpServletRequest req,
HttpServletResponse res);
protected void openDBConnection();
protected void prepareStatements();
protected void displayInitParameters();
protected int getMaxID();
protected void fillRandom();
protected void fillSine();
protected void fillHalf();
}
ChartServlet.init() Method
public void init(ServletConfig config)
throws ServletException {
super.init(config);
fDriver ="COM.cloudscape.core.RmiJdbcDriver";
try {
openDBConnection();
prepareStatements();
fStart = 1+ getMaxID();
}
catch (SQLException e) {e.printStackTrace();}
catch (ClassNotFoundException e)
{e.printStackTrace();}
}
doPost() Action
public void doPost(HttpServletRequest req,
HttpServletResponse res)
throws ServletException, IOException {
fStop = fStart +
Integer.parseInt(req.getParameter("number"));
String fillMethod
= req.getParameter("RadioGroup");
try {
if (fillMethod.equals("random"))
fillRandom();
else if (fillMethod.equals("sine"))
fillSine();
else if (fillMethod.equals("half"))
fillHalf();
updateMaxID();
…// response stuff next slide
} catch (SQLException e) {e.printStackTrace();}
}
doPost() Response
public void doPost(HttpServletRequest req,
HttpServletResponse res)
throws ServletException, IOException {
...
res.setContentType("text/html");
PrintWriter toClient = res.getWriter();
toClient.println("<html>");
toClient.println(
"<title>Data Modified!</title>");
toClient.println(
"Num data points: " + getMaxID());
toClient.println("</html>");
toClient.close();
...
Inserting Data into Database
protected void fillRandom() throws SQLException {
Statement stmt = fConnection.createStatement();
for (int i = fStart; i<= fStop; i++) {
String cmd = "INSERT INTO chart " +
"VALUES ( " +
i + "," + Math.random() +
", 'rdm' )";
stmt.executeUpdate(cmd);
}
stmt.close();
}
• Simplest way to do it.
• May not be the fastest.
Better Ways
• Use Prepared Statements.
– Database can precompile parameterized
statements. Best for repeated statements.
• Use JDBC Transactions for groups of
statements.
– By default, each executeUpdate() statement
is treated as a separate transaction.
• Database may lock records.
• Commit is called each time.
– Better to group all updates into a single JDBC
transaction.
Create Prepared Statements
private final static String kInsert
= "INSERT INTO chart VALUES(?,?,?)";
private final static String kMaxIDQuery
= "SELECT max ID FROM extra";
//--------------------------------------------
protected void prepareStatements()
throws SQLException {
fInsertStmt =
fConnection.prepareStatement(kInsert);
fMaxIDQueryStmt =
fConnection.prepareStatement(kMaxIDQuery);
}
• Create precompiled SQL statements for later
execution.
Using PreparedStatment
• Faster than regular statement if execute it
many times.
for (int i = fStart; i<= fStop; i++) {
fInsertStmt.setInt(kID, i);
fInsertStmt.setDouble(kValue, 0.5);
fInsertStmt.setString(kLabel,"half" );
fInsertStmt.executeUpdate();
Use JDBC Transaction: try
fConnection.setAutoCommit(false);
for (int i = fStart; i<= fStop; i++) {
fInsertStmt.setInt(kID, i);
fInsertStmt.setDouble(kValue, Math.sin(i));
fInsertStmt.setString(kLabel, "sine");
fInsertStmt.executeUpdate();
}
fConnection.commit();
} catch (SQLException e) {
try {
fConnection.rollback();
e.printStackTrace();
} catch (SQLException ex) {
ex.printStackTrace(); }
} finally {
try {
fConnection.setAutoCommit(true);
} catch (Exception ex2) {
ex2.printStackTrace();
}
• All or nothing. May be the fastest.
Performance Comparison
Method / DB sqlAny 200 (sec) IDB 500 (sec)
Statement (random) 1.4 8.0
PrepStmt (half) 0.9 6.0
PrepStmt + Trans (sine) 0.5 3.0
• PreparedStatement faster than Statement.
• PreparedStatement + transactions is best in
this example.
How to Display Query Result?
• Want to display a table with employee ID,
first name, last name and extension.
• It is a pain to write HTML output code in
servlets.
• It is also hard to debug and maintain if we
put all the java code into a JSP.
Better Approach
• It is easier to use a JSP for displaying
query result —dynamic content.
• Use servlets only for business logic or flow
control.
• Write database related methods into a
Java utility class or custom tag libraries.
• Use JavaBeans to store all the data.
Use Servlets, JSP and JavaBeans
WEB Server
StartQuery.jsp
ControlServlet
Request Servlet
Web Form Redirect Instantiates
Response <JSP> Java Beans
ResultBean
Result Page
DataManager
Resultpage.jsp
Servlet: init()
public class ControllServlet extends HttpServlet
implements SingleThreadModel {
private DatabaseManager dbm = null;
public void init(ServletConfig config)
throws ServletException
{
super.init(config);
try {
dbm = new
DatabaseManager("oracle.jdbc.driver.OracleDriver,
"jdbc:oracle:thin:@129.144.76.60:1521:bunnies",
"oracle", "oracle");
} catch (SQLException e) {
e.printStackTrace();
}catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
}
Servlet: doPost()
public class ControllServlet extends HttpServlet
implements SingleThreadModel {
private DatabaseManager dbm = null;
public void doPost(HttpServletRequest req,
HttpServletResponse res)
throws ServletException {
try { //get data from ResultSet and set ResultBean.
ResultSet re=dbm.getResultSet();
Collection beanList = new ArrayList();
ResultBean rb = new ResultBean();
while (re.next()) {
rb.setEmpid(dbm.getEmpId(re));
rb.setFirstName(dbm.getFirstName(re));
rb.setLastName(dbm.getLastName(re));
rb.setExtension(dbm.getExtension(re));
beanList.add(rb);
}
ResultBean list =new ResultBean();
list.setBeanList(beanList);
//Store resultbean into request.
req.setAttribute("resultBean", list);
} catch (SQLException e) {...}
try {
//forward request to JSP for display.
getServletConfig().getServletContext().getRequestDispatcher(
"/resultspage.jsp").forward(req, res);
}catch(IOException ex){...} }
Database Manager:
DatabaseManager.java
public class DatabaseManager {
public DatabaseManager(String driver, String dburl,
String uname, String pwd)
throws SQLException {
try {
Class.forName(driver);
} catch(ClassNotFoundException ex) {
throw new SQLException("Driver not found");
}
con = DriverManager.getConnection(dburl, uname, pwd);
}
public ResultSet getResultSet() throws SQLException {
Statement stmt = con.createStatement();
ResultSet myResultSet = stmt.executeQuery("select
* from employee");
...
return myResultSet;
}...
}
JavaBeans: ReusltBean.java
public class ResultBean
{
private String empid;
private Collection beanList;
... //Use default constructor.
public String getEmpid()
{
return empid;
}
public void setEmpid(String fn)
{
empid = fn;
}
...
public Collection getBeanList() {
return beanList;
}
public void setBeanList(Collection beanList) {
this.beanList = beanList;
}
}
JSP for Display: resultpage.jsp
<%@ page import="java.util.*" %> ...
<body> ...
<jsp:useBean id=resultBean class="ResultBean"
scope="request"/>
<%
ArrayList list = (ArrayList)resultBean.getBeanList();
int size = list.size();
ResultBean bean = new ResultBean();
for (int i=0; i<size; i++) {
bean = (ResultBean)list.get(i);
%>
<TR>
<TD><%=bean.getEmpid()%></TD>
<TD><%=bean.getLastName()%></TD>
<TD><%=bean.getFirstName()%></TD>
<TD><%=bean.getExtension()%></TD>
</TR>
<%
}
%> ...
</body> ...
Display Query Result Demo
Summary
• Use Prepared Statement for repeated
commands.
• Use Transactions for grouped commands.
• Use JSP to display dynamic content like
query result.
• Use Java components to manage database
access, and database query.
• Use servlets for control and business logic.
