Transcript Tuesday March 11

MC365
JDBC in Servlets
Today We Will Cover:
• DBVisualizer
• Using JDBC in servlets
• Using properties files
DBVisualizer
• DBVisualizer is a tool used to manipulate a
database.
– Other similar tools are SQL Plus, PL/SQL, Toad
– DBA’s use these tools to perform database functions
like setting up table spaces, tables, user access, triggers
and stored procedures.
– The focus of this class is not database administration
(this is usually the job of the DBA), but it is important
for software engineers to be familiar with some DBA
functions.
• Especially in a 3-tier architecture
DBVisualizer
• For this class, a DBA has already set up an Oracle instance
for us to use.
– The table spaces and user access have already been set up for us.
– In most organizations a DBA will be responsible for these
functions.
• As software engineers, we are going to use DBVisualizer
to:
–
–
–
–
Create and alter tables
Populate our tables
Test SQL statements
Write stored procedures (later)
• It is important for a software engineer to be comfortable
with database functions and tools, even though the DBA
will most likely be responsible for them.
Installing DBVisualizer
• Why use DBVisualizer?
– SQL Plus is command line.
– DBVisualizer, PL/SQL and Toad are more GUIoriented making them easier to use.
– It’s Java-based and uses JDBC itself – perfect for
testing in applications.
• If it works here, it will work in your servlet.
– It supports a variety of databases.
– It is free.
• You can download DBVisualizer from here:
http://www.minq.se/products/dbvis/
Connecting to Our DB Using
DBVisualizer
• Once you have installed DBVisualizer, you can
connect to the Oracle instance set up for this class:
– Go to File – JDBC Driver Manager
• Point to the classes12.zip file
– Go to Database – Add Database Connection
– Enter info:
•
•
•
•
•
Database alias: goanna
JDBC driver: oracle.jdbc.driver.OracleDriver
URL: jdbc:oracle:thin:@goanna.bc.edu:1521:BCACAD1
Userid:
your username
Password:
your password
– Try connecting using this password and make sure it works.
– If you have problems or want me to change the password, let me
know.
SQL in DBVisualizer
• Once you are connected, you can navigate to your
tablespace by clicking on the Database Objects
tab.
• You can also create and execute SQL statements in
your tablespace by clicking on the SQL
Commander tab.
• Here are some example SQL statements you can
use as models for creating, altering, updating and
querying your tables.
http://www2.bc.edu/~bernier/MC365/Lecture
Notes/SQLExamples.doc
Using JDBC in Servlets
• To see an example of a simple Java application
connecting to an Oracle instance via JDBC go to:
http://www2.bc.edu/~bernier/MC365/Lecture Notes/JDBCTest.java
Note: Do not put a semicolon at the end of SQL statements in Java.
• To see an example of a servlet connecting to an
Oracle instance via JDBC go to:
http://www2.bc.edu/~bernier/MC365/Lecture Notes/JDBCServlet.java
Note: To use the Oracle driver (instead of the ODBC driver) to
connect to an Oracle instance, you need to put the appropriate class
files into the application server. These class files are in a zip file called
Classes12.zip. You need to unzip the class files in this zip file into the
following directory:
c:\Program Files\Apache Group\Tomcat 4.1\common\classes.
Classes12.zip can be downloaded from Oracle’s site or here.
Properties Files
• What is a properties file?
– A properties file is a simple text file. You can create and maintain a
properties file with just about any text editor. You should always create a
default properties file. The name of this file begins with the base name of
your ResourceBundle and ends with the .properties suffix.
• Why use a properties file?
– Properties files allow you to keep hard-coded values that might change in
a text file. Text files are much easier to modify than java source code.
– Note: Any time you make a change to a properties file, you need to restart
the Tomcat for the changes to take effect.
• How do you reference a properties file in Java?
– Use the ResourceBundle object. See the JDBCServlet.java code for an
example of how to use a properties file
– Click on this link to see an example of a properties file:
http://www2.bc.edu/~bernier/MC365/Lecture Notes/db.properties.
– You can put the properties file used by the servlet in the same folder that
the class file is in under Tomcat (e.g. WEB-INF/classes)