jdbc2 - Marc Grosz Wiki

indexadjustmentInternet and Web Development

Nov 13, 2013 (3 years and 7 months ago)

62 views

Database Management Systems

1


Xintao Wu

Oracle Programming

Instructor: Xintao Wu

Database Management Systems

2


Xintao Wu

architecture







Database Management Systems

3


Xintao Wu

JDBC


Database Management Systems

4


Xintao Wu

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

5


Xintao Wu

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:@coit
-
ora01: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

6


Xintao Wu

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

7


Xintao Wu

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/WEB
-
INF/classes/SampleJDBC.java



Database Management Systems

8


Xintao Wu

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

9


Xintao Wu

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

10


Xintao Wu

How to run servlets


Your machine, Java servlet development
kit(JSDK)


COIT


consult the webpage
http://coit
-
servlet01.uncc.edu:8080/support/Examples.html





Consult TA


Database Management Systems

11


Xintao Wu

Examples


HelloWorld


http://coit
-
servlet01.uncc.edu:8080/xwu/servlet/HelloWorld



http://www.cs.uncc.edu/~xwu/WEB
-
INF/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

12


Xintao Wu

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/WEB
-
INF/classes/ojdbc14.jar

Database Management Systems

13


Xintao Wu

Some links


http://coit
-
servlet01.uncc.edu:8080/support


http://www.chiark.greenend.org.uk/~sgtath
am/putty

Database Management Systems

14


Xintao Wu

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

15


Xintao Wu

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.