Transcript SQL Queries

Oracle Programming
Instructor: Xintao Wu
Database Management Systems
Xintao Wu
1
architecture

Database Management Systems
Xintao Wu
2
JDBC
Database Management Systems
Xintao Wu
3
JDBC (Java Database Connectivity )
JDBC enables programmers to use SQL
queries from within java programs to access
databases.
 JDBC provides transparent access to
relational databases from different vendors.

Database Management Systems
Xintao Wu
4
JDBC steps




Create the database in your dbms
Register the database as a data source
Import java.sql.* at the beginning of your java file.
Connect to a JDBC source
– Connection con =
DriverManager.getConnection(“jdbc:oracle:thin:@coitora01:1521:class”,”user”,”passwd”)

Create an SQL statement
– Statement stmt = con.createStatement();
– stmt.executeUpdate("INSERT INTO sailor
VALUES(22,'dustin',7,45.0)");
Database Management Systems
Xintao Wu
5
JDBC Step

Execute the statement
– ResultSet rs = stmt.executeQuery(“Select * from …”)

Parse the result
– rs.next(), rs.getFloat
– ResultSetMetaData contains the information about column

Close the statement and connection
– stmt.close()
– con.close
Database Management Systems
Xintao Wu
6
Useful resources
JDBC tutorial
http://java.sun.com/docs/books/tutorial/jd
bc/index.html
 UNCC COIT
http://coit-servlet01.uncc.edu:8080/support/?
 Example
http://www.cs.uncc.edu/~xwu/WEBINF/classes/SampleJDBC.java

Database Management Systems
Xintao Wu
7
Java Servlets


Web servers understand requests for pages in http
and return their response in HTML format
Static html vs. dynamic html
–
–
–
–

The webpage is based on data submitted by the user
The data changes frequently (a weather-report)
Web pages uses information from a database(stock)
Maintain separate data about each client for personalization
purposes(cookies).
Java servlets are programs that extend the
functionality of a web server and useful for building
dynamic web pages on the fly.
Database Management Systems
Xintao Wu
8
Java Servlet Advantage
Works as a lightweight thread directly with
the JVM rather than create a new process
 Load once to memory and used by all threads
 Share data among each other

Database Management Systems
Xintao Wu
9
How to run servlets
Your machine, Java servlet development
kit(JSDK)
 COIT

– consult the webpage http://coitservlet01.uncc.edu:8080/support/Examples.html
– Consult TA
Database Management Systems
Xintao Wu
10
Examples

HelloWorld
– http://coit-servlet01.uncc.edu:8080/xwu/servlet/HelloWorld
– http://www.cs.uncc.edu/~xwu/WEBINF/classes/HelloWorld.java

More examples
– http://coit-servlet01.uncc.edu:8080/support/Examples.html

DBServlet
– http://coit-servlet01.uncc.edu:8080/xwu/DBServlet.html
– http://www.cs.uncc.edu/~xwu/WEB-INF/classes/DBServlet.java
Database Management Systems
Xintao Wu
11
JDBC and Servlet







Summary:
1. Download PUTTY and WinSCP
2. Download HelloWorld, SampleJDBC and DBServlet source code
3. Modify them and upload to your own space
4. Compile and run it
http://coit-servlet01.uncc.edu:8080/xwu/ojdbc14.jar
export CLASSPATH=$CLASSPATH:~/public_html/WEBINF/classes/ojdbc14.jar
Database Management Systems
Xintao Wu
12
Some links
http://coit-servlet01.uncc.edu:8080/support
 http://www.chiark.greenend.org.uk/~sgtath
am/putty

Database Management Systems
Xintao Wu
13
Some files you need
http://www.cs.uncc.edu/~xwu/5160/ojdbc14.jar
 http://www.cs.uncc.edu/~xwu/5160/servlet-api.jar
 http://www.cs.uncc.edu/~xwu/5160/.bashrc
 Visit the following to set up JDBC and servlet
enviorment
Note: We don’t need bashrc file for the new system

Database Management Systems
Xintao Wu
14
Summary
3-tire architecture
 APIs such as JDBC and ODBC introduce a layer
of abstraction between application and DBMS
 Choose your language from JSP, Servlet, ASP,
VC++, VB.

Database Management Systems
Xintao Wu
15