Transcript DBMS (II)
\Tomcat 5.5
• Download current
version of Tomcat from:
http://jakarta.apache.or
g/tomcat/
• Install it in:
• C:\Program
Files\Apache Software
Foundation\Tomcat 5.5\
• Test it:
• http://localhost:8080/
• Directory structure
\bin
\common
\ROOT
1
\webapps
\lib
…
…
An simple JSP example:
test.jsp
<html>
<%java.util.Date date=new java.util.Date();%>
<head> A Test of JSP </head>
<body>
<p><b> Welcome! Today is: </b></p>
<%=date.getYear()+1900%>
. <%=date.getMonth()+1%>
. <%=date.getDate()%>
</body>
</html>
2
An simple JSP example (con’t)
Edit it using an editor, e.g. WordPad.
Save it in:
C:\Program Files\Apache Software
Foundation\Tomcat5.5\webapps\ROOT\test.jsp
Start Tomcat:
C:\Program Files\Apache Software
Foundation\Tomcat5.5\bin\tomcat5
Open a Web browser and go to:
http://localhost:8080/test.jsp
3
Running JSP
JSP Page Request
no
JSP
Servlet
Current?
• JSP compilation
process: parse,
compile, load and
execute
Parse JSP and
Create Java
Source File
yes
JSP
Servlet in
Memory?
no
yes
Load the JSP servlet
Execute the JSP
servlet
4
JSP Page Response
Compile Java
Source File
Figure 14-6: GeneralTable.jsp
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<%@ page import="java.sql.*" %>
<HTML>
<HEAD>
<TITLE> Table Display Using JDBC and MySQL</TITLE>
<META NAME="author" CONTENT="Oscar LIN">
<META NAME="Keywords" CONTENT="JSP, JDBC, Database Access.">
<META NAME="description" CONTENT="an example of displaying a table
using JSP.">
<LINK REL=STYLESHEET HREF="JSP-Style.css" TYPE="text/css">
</HEAD>
<BODY>
<H2> Database Access Example</H2>
<%String varTableName= request.getParameter("Table");
varTableName = varTableName.toUpperCase(); %>
<H3> Showing Data from MySQL Database db1</H3>
5
<%
try {
// Load JDBC driver
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String connString="jdbc:odbc:test";
System.out.println("Trying connection with "+connString);
Connection conn = DriverManager.getConnection(connString);
Statement stmt = conn.createStatement();
String varSQL = "SELECT * FROM " + varTableName;
ResultSet rs = stmt.executeQuery(varSQL);
ResultSetMetaData rsMeta = rs.getMetaData();
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACE=5><FONT
FACE="Arial" COLOR=#000000>
<CAPTION><B> <%=varTableName%> </B></CAPTION></FONT>
<THEAD>
6
<TR> <%
String varColNames = "";
int varColCount = rsMeta.getColumnCount();
for (int col =1; col <= varColCount; col++) {
%> <TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000
><FONT SIZE=2 FACE="Arial" COLOR=#000000>
<%=rsMeta.getColumnName(col) %> </FONT> </TH>
<% }%>
</TR>
</THEAD>
<TBODY> <%
while (rs.next()) {
%> <TR VALIGN=TOP> <%
for (int col=1; col<=varColCount; col++) {
%> <TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial"
COLOR=#000000
> <%=rs.getString(col) %> <BR> </FONT> </TD>
<% }
}
7
//clean up
rs.close();
stmt.close();
conn.close();
}
catch (ClassNotFoundException e) {
out.println("Driver Exception " + e);
} %>
</TR>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>
Example: Run it!
8
Java Bean
Model 1.
9
JSP invoking Java Classes (Java Beans)
• Important and Useful:
– It separates the tasks of writing program logic
from generating HTML;
– It Reduces the complexity of managing a Web site.
• Java Bean
– A properly mannered Java class;
– A Java class that has three properties:
• there are no public instance variables;
• All persistent values xxx are accessed using
methods named getxxx and setxxx
• Bean classes must either have no constructors or
it must have one explicitly defined zeroargument constructor
10
An example using Java Bean
Save it into:
C:\Program Files\Apache Software Foundation\Tomcat
5.5\webapps\ROOT\WEB-INF\classes\
Compile it: >javac CustomerInsertBean.java
We get CustomerInsertBean.class
Edit NewCustomer.html
Save it into: C:\Program Files\Apache Software Foundation\Tomcat
5.5\webapps\ROOT\
Edit CustomerInsertUsingBean.jsp
Save it into
C:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\ROOT\
Start Tomcat
C:\Program Files\Apache Software Foundation\Tomcat 5.5\bin\tomcat5
11
An Example using Java Bean
and JDBC
JSP:
Display the result set
retrieved from Table
Testinfo in the database
db1
Use the Java Bean
DBUtil_MySQL.class
Java:
Load the JDBC driver
Set up the connection
Get the ResultSet
12
An
example
using
Java Bean
and
MySQL
// A Java Bean source code: DBUtil_MySQL.java
package lin;
import java.io.*;
import java.sql.*;
It is necessary to use a package,
Otherwise something will go wrong.
public class DBUtil_MySQL
{
// For MySQL
String sDBDriver="com.mysql.jdbc.Driver";
String sConnStr="jdbc:mysql://localhost:3307/"+"db1"+"?user=root";
// for MS ACCESS
//String sDBDriver="sun.jdbc.odbc.JdbcOdbcDriver";
//String sConnStr="jdbc:odbc:test";
Connection conn = null;
ResultSet rs = null;
public DBUtil_MySQL()
{
try
{ //java.sql.DriverManager.registerDriver();
Class.forName(sDBDriver);
}
catch(java.lang.ClassNotFoundException e)
{ System.err.println("testJDBC(): " + e.getMessage());
13
}
}
An
example
using
Java Bean
and
MySQL
public ResultSet executeQuery(String testJDBC)
{
rs = null;
try
{
conn=DriverManager.getConnection(sConnStr);
Statement stmt = conn.createStatement();
rs=stmt.executeQuery(testJDBC);
}
catch(SQLException ex)
{
System.err.println("aq.executeQuery:"+ex.getMessage());
}
return rs;
}
}
Save it in: \webapps\ROOT\WEB-INF\classes\DBUtil_MySQL.java
>javac –d \ DBUtil_MySQL.java
Copy the file we got from the compilation DBUtil_MySQL.class in C:\lin to
C:\Program Files\Apache Software Foundation\Tomcat 5.5\webapps\ROOT\WEBINF\classes\lin\
14
testDB_MySQL.jsp
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
<%
charset=gb2312">
while (rs.next())
<title> MY FAQ</title>
{ str_name=rs.getString("name");
</head>
str_gender = rs.getString("gender");
<body>
out.println(" name is: " + str_name);
<p><b> This is my name! </b> </p>
out.println(" gender is: " +
<%@ page language="java" import ="java.sql.*"
pageEncoding="gb2312"
str_gender);
%>
%> <p> </p> <%
<jsp:useBean id='workM' class="lin.DBUtil_MySQL" /> }
<jsp:setProperty name="workM" property="*" />
} RS.close();
%>
<%
</body>
ResultSet RS = workM.executeQuery("SELECT *
</html>
FROM testinfo");
while (RS.next())
{
ResultSet rs = workM.executeQuery("SELECT * Save it to:
FROM testinfo");
C:\Program Files\
String str_name, str_gender;
Apache Software Foundation
out.println("Get data from a database:: ");
%>
\Tomcat 5.5\webapps\ROOT\
<p> </p>
Go to:
15
http://localhost:8080/testDB_MySQL.jsp
MySQL
• Downloading MySQL
– An open-source DBMS product that runs on
Unix, Linux, and Windows
• Provides quick and efficient query
handling
• Allows you to create users, databases,
tables, auto incrementing fields, etc.
– The MySQL open-source driver may be
downloaded from:
http://worldserver.com/mm.mysql
16
• Get and Install a Recent Version
• Download the 4.0 and/or 4.1 versions of
the MySQL win32 distribution.
• Find the downloaded file, unzip it and
start the setup program:
• By referring to
http://www.analysisandsolutions.com/co
de/mybasic.htm
• Install it to C:\Program Files\mysql40\
• Start it: C:\Program Files\mysql40\bin\
17
– Limitations
• While MySQL is extremely inexpensive
and provides many capabilities, it is not as
powerful as commercial products, such as
Oracle and SQL Server
– Using My SQL
• MySQL commands: use, show, describe
18
Using MySQL
•
•
•
•
•
•
•
>mysql –P 3307 –u root mysql
>Show databases;
>sHow databases; (OK)
>Use db1;
>Show tables;
>Describe testinfo;
>Select * from testinfo;
Or
• >\q
• Edit sql.txt
• >bin>mysql –P 3307 –u root mysql <C:\wan\sql.txt
19
MySQL Connector/J
– The latest version of the driver is MySQL Connector/J
(formerly MM.MySQL driver)
• is a Java driver that converts JDBC calls into the network protocol
used by the MySQL database.
• is a Type 4 JDBC driver and has a complete JDBC feature set that
supports the capabilities of MySQL.
•Download the driver from
•
http://dev.mysql.com/downloads/connector/j/3.1.html
•Unzip it to get the jar file: mysql-connector-java3.0.16-ga-bin.jar
• copy it to a public place:
•…\Tomcat 5.5\common\lib\mysql-connector-java-3.0.16-ga-bin.jar
20
– JDBC connections
• JDBC connection to MySQL is
different from other user
connections
– Same machine: via socket
– Different machines: via TCP/IP
21
– A work-around allows you to grant access to
the database@’%’ for the specific account
» The wildcard % states that the database
may be accessed from any host
» Alternatively, you may supply the specific
IP address for your local machine –this
provides better security
– Concurrency control
• Limited support: read/write locks
– Backup and recovery
• Limited support: database/table saving, log files
22
Summary
• JDBC
–
–
–
–
JDBC driver types
Java Servlet and Applets
JDBC architecture and components
Applications of JDBC
• JSP (Java Server Pages)
– JSP, Servlets and Apache Tomcat
• MySQL
– MySQL and JDBC Connections; Concurrency control,
and backup and recovery
• Questions?
23