JDBC: A Java Library for Accessing Relational Databases, Slides of Computer Engineering and Programming

Download JDBC: A Java Library for Accessing Relational Databases and more Slides Computer Engineering and Programming in PDF only on Docsity!

JDBC

• Types of databases

--Hierarchical

--Relational

--Object Relational

Background: databases

SQL Basics: Structure of a SQL Statement

• When accessing a relational database, you must use the

“Structured Query Language” (SQL)

• Several types of SQL:

queries—for asking questions

updates—for making changes

insert—for adding new data

for creating tables

SQL Basics: Structure of a SQL Statement

• Queries: SELECT statements

SELECT columns FROM table ;

Or if we wish not to select all columns:

SELECT columns

FROM table

WHERE expression

SQL Basics: Structure of a SQL Statement

• Updates: UPDATE statements

UPDATE table SET column = value ;

Example:

UPDATE table

SET LastName = „Jones‟

WHERE ID = 2;

SQL Basics: Structure of a SQL Statement

• Insert: INSERT statements

INSERT INTO table VALUES( values );

Example:

INSERT INTO USER

VALUES( „6‟, „Anderson‟, „Joe‟, 44, „A‟)

JDBC Introduction

 JDBC provides a standard library for accessing relational

databases

• API standardizes

 Way to establish connection to database

 Approach to initiating queries

 Method to create stored (parameterized) queries

 The data structure of query result (table)

• Determining the number of columns
• Looking up metadata, etc.
• API does not standardize SQL syntax
• JDBC class located in java.sql package

 Note: JDBC is not officially an acronym; unofficially, “Java

Database Connectivity” is commonly used

On-line Resources

 Sun’s JDBC Site

• http://java.sun.com/products/jdbc/

 JDBC Tutorial

• http://java.sun.com/docs/books/tutorial/j

dbc/

 List of Available JDBC Drivers

• http://industry.java.sun.com/products/jdb

c/drivers/

Seven Basic Steps in

Using JDBC

1. Load the driver
2. Define the Connection URL
3. Establish the Connection
4. Create a Statement object
5. Execute a query
6. Process the results
7. Close the connection

JDBC: Details of Process

1. Load the driver

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

Class.forName("com.mysql.jdbc.Driver");

} catch { ClassNotFoundException cnfe) {

System.out.println("Error loading driver: " cnfe);

2. Define the Connection URL

String host = "dbhost.yourcompany.com";

String dbName = "someName";

int port = 1234;

String oracleURL = "jdbc:oracle:thin:@" + host +

":" + port + ":" + dbName;

String mysqlURL = "jdbc:mysql://" + host +

":" + port + "/" + dbName;

JDBC: Details of Process, cont.

4. Create a Statement

Statement statement = connection.createStatement();

5. Execute a Query

String query = "SELECT col1, col2, col3 FROM

sometable";

ResultSet resultSet = statement.executeQuery(query);

• To modify the database, use executeUpdate ,

supplying a string that uses UPDATE , INSERT , or

DELETE

• Use statement.setQueryTimeout to specify a

maximum delay to wait for results Docsity.com

JDBC: Details of Process, cont.

6. Process the Result

while(resultSet.next()) {

System.out.println(resultSet.getString(1) + " " +

resultSet.getString(2) + " " +

resultSet.getString(3));

• First column has index 1, not 0
• ResultSet provides various get methods

that take a column index or name and

returns the data

7. Close the Connection

connection.close(); Docsity.com

Basic JDBC Example, cont. Statement s = C.createStatement(); String sql="select * from pet"; s.execute(sql); ResultSet res=s.getResultSet(); if (res!=null) { while(res.next()){//note MySql start with 1 System.out.println("\n"+res.getString(1)

• "\t"+res.getString(2)); } } c.close(); } catch (SQLException E) { System.out.println("SQLException: " + E.getMessage()); System.out.println("SQLState: " + E.getSQLState()); System.out.println("VendorError: " + E.getErrorCode()); } }

} Docsity.com

JDBC Basics: Statements

• After you have a connection, you need to create a statement.
• There are three alternatives, each with plusses and minuses.

Statement —used for a query that will be executed once.

PreparedStatement —used for a query that will be executed multiple times

CallableStatement —used for a query that executes a stored procedure.