CSC 224 Lecture Notes


Week 7: Java Database Connectivity



Questions on the homework or the quiz [1/21]
What is a database? [2/21]
SQL (Structured Query Language) [3/21]
Basic commands [4/21]
ODBC [5/21]
JDBC [6/21]
Registering our DB on Win32 [7/21]
Loading Your Driver [8/21]
Connect to the Database with DriverManager [9/21]
java.sql.Statement [10/21]
Creating a table [11/21]
Storing data in a table [12/21]
Retrieving data from a table [13/21]
.execute() Used for reads or writes [14/21]
DBTest.java [15/21]
PreparedStatement.java [16/21]
Using meta data [17/21]
DBTest2.java [18/21]
Most applications are a UI to a DB [19/21]
The LIKE SQL command [20/21]
Homework Due Before Class Next Week [21/21]

Questions on the homework or the quiz [1/21]


-How did the homework go?






-Any questions on the quiz?

Let's take a look at my solution for the homework, and then we'll do the quiz

What is a database? [2/21]


Schedule

Section#

Instructor

Course

Day
224902 Jungman Java Thursday
224904 Jungman Java Wednesday
224602 Kalin Java Tuesday
449301 Halstead Databases Tuesday
540301 Jeffrey Palm Monday
561302 Al-Shaer Networks Monday

SQL (Structured Query Language) [3/21]


Basic commands [4/21]


SELECT what will this return when executed on our table?
SELECT instructor, course FROM schedule; Optional WHERE clause allows comparison on =, <, >, <=, etc. SELECT instructor, course FROM schedule WHERE instructor='Jungman'; Optional AND SELECT instructor, course FROM schedule WHERE instructor='Jungman' AND day='Thursday'; CREATE CREATE table employee (first varchar(15), last varchar(20), age integer, address varchar(30), city varchar(20), state varchar(20)); INSERT INSERT into employee (first, last, age, address, city, state) values ('Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co', 'Georgia'); UPDATE UPDATE employee set age = 46 where last = Luke;

ODBC [5/21]


JDBC [6/21]


Registering our DB on Win32 [7/21]


It doesn't really make a difference which database you use for the assignment I give, so to simplify my grading either use MS-Access which is shipped with the professional version of MS-Office or Axion: http://axion.tigris.org/

Axion is an open source database written in Java, so it's platform independent, and it has a nice 'quick start' guide to setting up and calling the driver.

Here's a step-by-step tutorial on running a simple Java-to-MSAccess JDBC program.

If you don't have MS-Access (it comes with MS-Office) you can use the lab on the sixth floor of CTI. You are of course welcome to use a non-MS environment as well.

  1. We'll create an MS-Access DB called myDB.mdb
  2. Search on your Windows machine for your "ODBC Data Source"; it is probably in your "Control Panel" which you may be able to access from Start->Settings... otherwise do a search
  3. Open your ODBC and click 'Add'
  4. Choose "Microsoft Access Driver" and click 'Finish'
  5. Fill in your "Data Source Name" field (I chose "TheDataSourceName" and then click "Select"
  6. Now find your Database in the directory and press O.K.
  7. You're ready to roll, the tutorial listed above has screen dumps of all this

Loading Your Driver [8/21]


try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Class.forName("org.axiondb.jdbc.AxionDriver"); } catch(ClassNotFoundException e) { /* exception handling */ }

Connect to the Database with DriverManager [9/21]


try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection con1 = DriverManager.getConnection( "jdbc:odbc:CustomerDB","",""); Class.forName("org.axiondb.jdbc.AxionDriver"); Connection con2 = DriverManager.getConnection("jdbc:axiondb:GradeDB:dbFile"); } ... // catch exceptions
//or
try{ Class.forName("oracle.jdbc.driver.OracleDriver"); String host = "dbhost.yourcompany.com"; String dbName = "someName"; int port = 1234; String username = "epp"; String password = "dean1"; String URL = "jdbc:oracle:thin:@" + host + ":" + port + ":" dbName; Connection c = DriverManager.getConnection(URL, username, pasword); ...}

java.sql.Statement [10/21]


Creating a table [11/21]


(Say we wanted to create a new table, since we're doing a write to the database we use the .executeUpdate(String sql) method from the Statement interface) stmt.executeUpdate("CREATE TABLE Customers " + "(Cust_ID INTEGER, First_Name VARCHAR(20)," + " Last_Name VARCHAR(30), Street VARCHAR(30)," + " City VARCHAR(20), State VARCHAR(15)," + " Zip VARCHAR(10), Phone VARCHAR(15)," + + " Email VARCHAR(20), Credit_Card VARCHAR(16)," + " Exp_Month INTEGER, Exp_Year INTEGER)");

Storing data in a table [12/21]


stmt.executeUpdate("INSERT INTO Customers " + "VALUES (1,'J','C',‘Street','City','State'," + "'60604', 'Email', 'CC', 12, 2001)");

Retrieving data from a table [13/21]


ResultSet rs = stmt.executeQuery("SELECT * FROM Customers"); // rs.next() returns false if no more records while (rs.next()) System.out.println(rs.getString("Last_Name") + ", " + rs.getString("First_Name"));

 

T
I
N
Y
I
N
T

S
M
A
L
L
I
N
T

I
N
T
E
G
E
R

B
I
G
I
N
T

R
E
A
L

F
L
O
A
T

D
O
U
B
L
E

D
E
C
I
M
A
L

N
U
M
E
R
I
C

B
I
T

C
H
A
R

V
A
R
C
H
A
R

L
O
N
G
V
A
R
C
H
A
R

B
I
N
A
R
Y

V
A
R
B
I
N
A
R
Y

L
O
N
G
V
A
R
B
I
N
A
R
Y

D
A
T
E

T
I
M
E

T
I
M
E
S
T
A
M
P

getByte

X

x

x

x

x

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getShort

x

X

x

x

x

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getInt

x

x

X

x

x

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getLong

x

x

x

X

x

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getFloat

x

x

x

x

X

x

x

x

x

x

x

x

x

 

 

 

 

 

 

getDouble

x

x

x

x

x

X

X

x

x

x

x

x

x

 

 

 

 

 

 

getBigDecimal

x

x

x

x

x

x

x

X

X

x

x

x

x

 

 

 

 

 

 

getBoolean

x

x

x

x

x

x

x

x

x

X

x

x

x

 

 

 

 

 

 

getString

x

x

x

x

x

x

x

x

x

x

X

X

x

x

x

x

x

x

x

getBytes

 

 

 

 

 

 

 

 

 

 

 

 

 

X

X

x

 

 

 

getDate

 

 

 

 

 

 

 

 

 

 

x

x

x

 

 

 

X

 

x

getTime

 

 

 

 

 

 

 

 

 

 

x

x

x

 

 

 

 

X

x

getTimestamp

 

 

 

 

 

 

 

 

 

 

x

x

x

 

 

 

x

x

X

getAsciiStream

 

 

 

 

 

 

 

 

 

 

x

x

X

x

x

x

 

 

 

getUnicodeStream

 

 

 

 

 

 

 

 

 

 

x

x

X

x

x

x

 

 

 

getBinaryStream

 

 

 

 

 

 

 

 

 

 

 

 

 

x

x

X

 

 

 

getObject

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x

x


A more precise explanation is listed here: http://java.sun.com/docs/books/tutorial/jdbc/basics/_retrievingTable.html

.execute() Used for reads or writes [14/21]



.execute() returns true if has a result set

//... Statement stmt = con.createStatement(); if(stmt.execute(readOrWriteSql)) { stmt.getResultSet(); } else { int rowsUpdated = stmt.getUpdatedCount(); } //...

DBTest.java [15/21]


import java.sql.*; import java.net.*; import java.util.*; public class DBTest{ public static void main(String args[]){ try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String dataSource = "jdbc:odbc:TheDataSourceName"; Connection con = DriverManager.getConnection(dataSource, null, null); Statement stmt = con.createStatement(); try { stmt.executeUpdate("drop table employee");} catch (SQLException e){/*do nothing if table does note exist*/ } stmt.executeUpdate("CREATE table employee " + "(first varchar(15), last varchar(20), age integer, address varchar(30), " + " city varchar(20), state varchar(20))"); stmt.executeUpdate("INSERT into employee " + "(first, last, age, address, city, state) " + "values ('Luke', 'Duke', 45, '2130 Boars Nest', 'Hazard Co', 'Georgia')"); stmt.executeUpdate("INSERT into employee " + "(first, last, age, address, city, state) " + "values ('Arthur', 'Fonzerella', 47, 'Above Mr. C', 'Milwaukee', 'Wisconsin')"); stmt.executeUpdate("INSERT into employee " + "(first, last, age, address, city, state) " + "values ('Lucille', 'Ball', 62, '555 Main Street', 'Los Angeles', 'California')"); ResultSet rs = stmt.executeQuery(" Select first, last, address from employee where age > 46 "); while(rs.next()){ System.out.println("Name: "+rs.getString(1)+" "+ rs.getString(2)+" Address: "+rs.getString(3)); } }catch(Exception e){System.out.println(e.toString());} /* !!!!! WARNING !!!!!!! * in the real world you want to clean up * system resources in the finally block * see DBTest2.java below */ } } DBTest.java

PreparedStatement.java [16/21]


Using meta data [17/21]


DBTest2.java [18/21]


import java.sql.*; import java.net.*; import java.util.*; public class DBTest2{ public static void main(String args[]){ Connection con = null; PreparedStatement ps = null; Statement stmt = null; ResultSet rs = null; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); String dataSource = "jdbc:odbc:TheDataSourceName"; con = DriverManager.getConnection(dataSource, null, null); ps = con.prepareStatement( "update employee set age = ? where last = ?" ); ps.clearParameters(); ps.setObject(1, new Integer(50)); ps.setString(2, "Duke"); ps.executeUpdate(); stmt = con.createStatement(); rs = stmt.executeQuery(" Select first, last, address from employee where age > 46 "); while(rs.next()){ System.out.println("Name: "+rs.getString(1)+" "+ rs.getString(2)+" Address: "+rs.getString(3)); } }catch(Exception e){System.out.println(e.toString());} finally { try{ if(con != null) con.close(); if(ps != null) ps.close(); if(stmt != null) stmt.close();//also closes result set }catch(SQLException e1) { e1.printStackTrace(); } } } } DBTest2.java

Most applications are a UI to a DB [19/21]


The LIKE SQL command [20/21]


One quick sql command I want to make sure you know for your homeowork

Say you wanted to query the SCHEDULE TABLE from above to give you info on all instructors whose name started with 'S'

SELECT INSTRUCTOR, COURSE, DAY
FROM SCHEDULE
WHERE INSTRUCTOR LIKE 'A%';

The percent sign (%) is used to represent any possible character (number, letter, or punctuation) or set of characters that might appear after the "A". To find those people with LastName's ending in "A", use '%A', or if you wanted the "A" in the middle of the word, try '%A%'. NOT LIKE displays rows not fitting the given description.

Homework Due Before Class Next Week [21/21]


-Exam 7 Next Week
-Homework 7 Due Before Class