CSI 2132 Lab5

1
CSI 2132 Lab5

• JDBC Installation and Use
• March 2, 2009

2
Outline

• What is JDBC?
• PostgreSQL JDBC Driver
• Java Programming with JDBC
• SQL queries using JDBC

3
What is JDBC?

• Java Database Connectivity (JDBC) is an API for
  the Java programming language that defines how a
  client may access a database.
• It provides methods for querying and updating
  data in a database.
• JDBC is an API not a library. It needs to be
  implemented (as drivers) for a particular DB.
  i.e. PostgreSQL and MySQL have different JDBC
  drivers

4
PostgreSQL JDBC driver

• Website
• http//jdbc.postgresql.org
• Documents
• http//jdbc.postgresql.org/documentation/head/inde
  x.html
• Download the JDBC Driver
• http//jdbc.postgresql.org/download.html
• Version 8.3-604 JDBC 4
• postgresql-8.3-604.jdbc4.jar

5
Java Programming - Preparation

• Prepare the server
• Create a table and insert values.
• Example artist.sql
• CREATE TABLE artist
• (
• aname VARCHAR(20),
• birthplace VARCHAR(20),
• style VARCHAR(20),
• dateofbirth DATE,
• PRIMARY KEY (aname))
• INSERT INTO artist VALUES ('Caravaggio','Milan','B
  aroque','1571-09-28')
• INSERT INTO artist VALUES ('Smith', 'Ottawa',
  'Modern', '1977-12-12')
• INSERT INTO artist VALUES ('Picasso','Malaga','Cub
  ism','1881-10-25')

6
JDBC Coding in Java

• Import the JDBC driver
• import java.sql.
• Load the driver
• Connect to a Database
• Issue a Query and process the result
• Example MyJDBCEx.java

7
JDBC Coding in Java

• Import the JDBC driver
• Load the driver
• Class.forName("org.postgresql.Driver")
• or externally
• java -Djdbc.driversorg.postgresql.Driver
  MyJavaProgram
• or set CLASSPATH
• Connect to a Database
• Issue a Query and process the result

8
JDBC Coding in Java

• Import the JDBC driver
• Load the driver
• Connect to a Database
• Connection conn DriverManager.getConnection(url,
  username, password)
• URL is in the form of
• jdbcpostgresql//hostport/database
• jdbcpostgresql//web0.site.uottawa.ca15432/your_
  database_name
• Issue a Query and process the result

9
JDBC Coding in Java

• Import the JDBC driver
• Load the driver
• Connect to a Database
• Issue a Query and process the result
• Statement st conn.createStatement()
• ResultSet rs st.executeQuery("SELECT FROM
  artists)
• while (rs.next())
• rs.close()
• st.close()

10
Compile and Run
Set your own JDK or JRE path

• Setup Java environment
• PATH
• set PATHPATHE\dev\jdk\bin
• So we can use java.exe, javac.exe in command line
• CLASSPATH
• set CLASSPATHd\postgresql-8.3-604.jdbc4.jar
• Commands
• javac MyJDBCEx.java
• java MyJDBCEx

Set your own path of the jdbc driver
11
More on Querying and Processing results

• Use PreparedStatement
• PreparedStatement st conn.prepareStatement("SE
  LECT FROM artist WHERE aname
  ?")st.setString(1, foovalue) ResultSet rs
  st.executeQuery()
• Excute Updates
• PreparedStatement st conn.prepareStatement("DE
  LETE FROM artist WHERE aname ?")
  st.setString(1, foovalue)int numOfRowsDeleted
  st.executeUpdate()

12
Your Turn

• Write a Java program that can
• connect to your own database,
• retrieve the name and birthday of all artists,
  and
• print the result as a 2D table using
  System.out.print()

13
REFERENCE

• PostgreSQL JDBC Doc
• http//jdbc.postgresql.org/documentation/83/index.
  html
• Chapters
• 3. Initializing the Driver
• 5. Issuing a Query and Processing the Result
• JDBC Tutorials at sun.com
• JDBC Introduction (a short introduction)
• JDBC Basics (with many examples)
• JDBC Overview (architecture of JDBC)