[go: up one dir, main page]
Scribd
Upload
21 views35 pages

08 JDBC

Uploaded by

Trần Vũ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
21 views35 pages

08 JDBC

Uploaded by

Trần Vũ
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 35

JDBC

JDBC Overview
• What is JDBC?
• Features
• Architecture
• Development process
• JDBC data access statements
• Java JDBC Transactions
What is JDBC?
• JDBC
– Allows a Java application to connect to a relational
database.
– The major databases are supported such as
Oracle, Microsoft SQL Server, DB2 and many
others.
Features
• The main feature of JDBC is that it is a standard API.
You develop your application code to the JDBC API
and you can connected to various databases.
• JDBC supported a large number of databases
– Oracle, Microsoft SQL server, MySQL, SyBase, DB2,
PostgreSQL …
• You can build your own custom SQL statement:
– select, insert, update and delete.
– Complex SQL queries: inner and outer joins.
– Call stored procedures.
JDBC architecture
JDBC architecture
• JDBC driver
– Provides a connection to a database.
– Converts JDBC calls to for specific database.
• JDBC driver implementations
– Provided by database vendor.
JDBC Driver manager
• Driver manager helps connect an application
based on a database connection string.
• In JDBC is version 4.0, the JDBC drivers are
automatically loaded based on the classpath.
• Legacy JDBC 3.0 drivers have to be explicitly
loaded with Java code is
Class.forName(theDriverName)
JDBC API
• The JDBC API is defined in two packages.
– java.sql and javax.sql.
• Key classes
– java.sql.DriverManager
– java.sql.Connection
– java.sql.statement
– java.sql.ResultSet
– java.sql.DataSource
Development Process
• Get a Connection to database.
• Create a Statement object.
• Execute SQL query.
• Process Results set.
• Close connection
Step 1: Get a Connection to database
• In order to connect to database
– Need to connection string in form of JDBC URL.
• Basic syntax
– jdbc:<driver protocol>:<driver connection details>
• Examples
Database JBDC URL
MS SQL Server jdbc:sqlserver://<HOST>:<PORT>;DatabaseName=DB
Oracle jdbc:oracle:thin:@<HOST>:<PORT>:<DB>
MySQL jdbc:mysql://<HOST>:<PORT>/<DB>
Step 1: Get a Connection to database
Step 2: Create a Statement object
• The Statement object is based on connection.
– It will be used later to execute SQL query.
Step 3: Execute SQL query.
• Pass in your SQL query
Step 4: Process Results set
• Results set is initially placed before first now.
• Method: boolean next()
– Moves forward one row
– Return true if there are more rows to process
• Looping through a result set
Step 4: Process Results set
• Collection of methods for reading data
– getXXX(columnName)
– getXXX(columnIndex)
Step 5: Close connection
• By closing connection object statement and
ResultSet will be closed automatically.
• The close() method of Connection interface is
used to close the connection.
public void close()throws SQLException
• Example
con.close();
JDBC data access statements
// Statement creation
Statement statement = connection.createStatement();
// Query string
String query = <SQLQuery>; // CRUD
// for retrieve data
ResultSet result = statement.executeQuery(query);
while (result.next()){
result.getString(<ColName>);
result.getInt(<ColName>);
result.getFloat(<ColName>);
}
// for data modification: insert, update, delete
int nbUpdated = statement.executeUpdate(query);
JDBC ResultSet update preparation
// for use with ResultSet only
// No “previous” method using, no update
connection.createStatement(
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);

// with “previous” method using, update


connection.createStatement(
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
Prepared Statements
• What are Prepared Statements
• Create a Prepared Statement
• Setting Parameter Values
• Executing a Prepared Statement
• Reusing a Prepared Statement
Prepared Statements
• A Prepared Statement is simply a precompiled
SQL statement.
• Prepared Statements provide the following
benefits.
– Makes it easier to set SQL parameters.
– Prevent against SQL dependency injection attacks
– May improve application performance
• SQL statement is precompiled.
Using Prepared Statements
• Instead of hard coding your SQL values

• Set parameter placeholders


– Use a question mark for placeholder: ?

• Can also use prepared statement s for


– Insert, update and delete
Using Prepared Statements
Calling SQL Stored Procedures
• What are Stored Procedures
• Using callable Statements
• Call Stored Procedures that take parameters.
– IN parameters
– INOUT parameters
– OUT parameters
– Return a result set
What are Stored Procedures
• A stored procedure is a group of SQL
statements that perform a particular task.
• The stored procedures are created in a SQL
language that supported by the native
database.
• The stored procedures can also have any
combination of input and output parameters.
Using callable Statements
• To call stored procedures from Java
– The JDBC API provides the CallableStatement
• Use a special syntax to call stored procedures
– CallableStatement myCall = con.prepareCall(
"{call stored_proc_name()}");
Using callable Statements
• Stored procedure

• Java coding
Java JDBC Transactions
• What are Transactions?
• How to develop transactions with JDBC
What are Transactions?
• A transaction is basically a unit of work.
• One or more SQL statements executed
together.
– Either all of the statements are executed – Commit
– Or none of the statements are executed – Rollback
JDBC Transactions
• By default, the database connection is to
auto-commit
– Need to explicitly turn off auto-commit

• Developer controls commit or rollback


JDBC Transactions
• Developer controls commit or rollback
JDBC batch processing
• Batch processing allows you to group related SQL
statements into a batch and submit them with
one call to the database.
• The addBatch() is used to add individual
statements to the batch.
• The executeBatch() is used to start the execution
of all the statements grouped together.
• The clearBatch() is used to removes all the
statements you added to batch.
JDBC Batch Processing
• Create a Statement object.
• Set auto-commit to false.
• Add as many as SQL statements into batch.
• Execute all the SQL statements.
• Finally, commit all the changes.
JDBC Batch Processing
con.setAutoCommit(false);
// replace executeQuery by addBatch
…
stmt.setString(1, “Titi”); stmt.setInt(2, 25);
stmt.addBatch(); // Insert 1
stmt.setString(1, “Tata”); stmt.setInt(2, 28);
stmt.addBatch(); // Insert 2
// then call batch processing statement
stmt.executeBatch();
// also applied for normal statement (not prepared
one)
con.commit();
con.setAutoCommit(true);
JDBC Batch with string query
connect.setAutoCommit(false);
Statement statement = connect.createStatement();
statement.addBatch(<Insert query>);
statement.addBatch(<Insert query>);
statement.addBatch(<Update query>);
statement.addBatch(<Delete query>);
int[] updateCounts = statement.executeBatch();
connect.commit();
statement.close();
connect.setAutoCommit(true);

You might also like