Basic JDBC

1
Basic JDBC
Celsina Bignoli bignolic_at_smccd.net
2
What is JDBC

• Industry standard for database-connectivity
  between the Java language and a wide range of
  databases
• Generic
• Leverage existing database APIs
• Simple

3
JDBC Architecture
Java Application
DriverManager
Sybase Driver
MySQL Driver
Oracle Driver
Sybase DB
MySQL DB
Oracle DB
4
JDBC Drivers

• Vendor-specific implementation of the JDBC
  interfaces
• Transparent to the database application
  developers

5
JDBC specifications
JDBC 2.0 Specification
JDBC 2.0 Core API (java.sql package) Scrollable
ResultSets Streams
JDBC 1.0 Specification Basic database connectivity
JDBC 2.0 Standard Extension API (javax.sql
package) DataSource Connection pooling
Distributed transactions Rowsets
6
Loading the Driver

• Class.forName(driverClassName).newInstance()
• Class.forName(com.mysql.jdbc.Driver).newIns
  tance()
• dynamically load a java class at runtime
• executes newInstance() to create an object of
  class Driver calling the default constructor for
  the class.
• The constructor executes code to register the
  class with the DriverManager

7
Connecting to the Database

• Connection DriverManager.getConnection(String
  url)
• DriverManager.getConnection(
• jdbcmysql//localhost/accounts?userrootpasswor
  dcis384)

8
URL

• URL
• ltprotocolgtltsubprotocolgtltsubnamegt
• jdbc mysql //localhost/accounts
• ltsubnamegt
• //lthostgtltportgt/ltdatabaseNamegt
• //localhost/accounts
• //192.156.44.3/prod
• //db.mycompany.com/prod
• //db.mycompany.com4544/prod
• when not specified, connector/J will default to
  port 3306.

9
DriverManager -getConnection() method
static Connection getConnection(String url) Attempts to establish a connection to the given database URL.
static Connection getConnection(String url, Properties info) Attempts to establish a connection to the given database URL using the specified properties.
static Connection getConnection(String url, String user, String password) Attempts to establish a connection to the given database URL as used identified by password.
10
getConnection() Example

• Properties info new Properties()
• info.setProperty(user, username)
• info setProperty(password, pwd)
• Connection con DriverManager.getConnection(url,
  prop)
• OR
• String usernameusername
• String passwordpassword
• Connection con DriverManager.getConnection(url,
  username, password)

11
Queries Statement Object

• used to send a SQL statement to the database
• executes the SQL statement
• returns back the results of the SQL statement

12
createStatement()
Statement createStatement () Creates a Statement object for sending SQL statements to the database.
13
executeQuery()
ResultSet executeQuery(String sql) Executes the given SQL statement, which returns a single ResultSet object

• no assumption is made on the validity of the
  query
• if the SQL execute successfully it returns a
  ResultSet object containing rows from the
  database
• if the SQL fails it will raise a SQLException

14
Executing a Statement - Example

• ResultSet rs
• stmt.executeStatement(select name from pets)
• ResultSet

Initial cursor position
next()
Fluffy
Claws
Buffy
Fang
Chirpy
Whistler
Slim
Puffball
next()
15
ResultSet Object

• A table of data representing a database result
  set
• maintains a cursor pointing to its current row of
  data
• Initially the cursor is positioned before the
  first row
• The next() method moves the cursor to the next
  row
• next() returns false when there are no more rows
  in the ResultSet object
• A default ResultSet object is not updatable and
  has a cursor that moves forward only

16
Moving Through the ResultSet -Example

• while (rs.next())
• System.out.println(rs.getString((1)))
• Fluffy
• Claws
• Buffy
• Fang
• Chirpy
• Whistler
• Slim
• Puffball

17
Basic Getter Methods

• int getInt(int columnIndex)
• int getInt(String columnName)
• String getString(int columnIndex)
• String getString(String columnName)
• Date getDate(int columnIndex)
• Date getDate(String columnName)

18
Handling Errors

• Connector/J Driver throws a SQLException
• errors connecting with the database
• errors executing SQL statements
• To know more about a single Exception use the
  SQLException methods
• getMessage()
• getSQLState()
• getErrorCode()

19
executeUpdate() method
int executeUpdate (String sql) Creates a Statement object for sending SQL statements to the database.
Returns either the row count for INSERT, UPDATE
or DELETE statements, or 0 for SQL statements
that return nothing
20
Inserting a Row - Example

• Statement stmt connection.createStatement()
• int i stmt.executeUpdate(INSERT INTO pet
  VALUES(12, minou, Gwen, cat))

21
Updating a Row - Example

• Statement stmt connection.createStatement()
• int i stmt.executeUpdate(UPDATE pet SET
  ownerLucy where owner Gwen )

22
Deleting a Row - Example

• Statement stmt connection.createStatement()
• int i stmt.executeUpdate(DELETE FROM pet WHERE
  owner Gwen )

23
Prepared Statements - SQL

• ability to set up a statement once, and then
  execute it many times with different parameters.
• replace building ad hoc query strings, and do so
  in a more efficient manner.
• First implemented in the C API
• Available in Connector/J server-side starting
  from version 3.1

24
How databases execute queries

• parse the query
• invoke the optimizer to determine best query
  execution plan
• caches the plan query is the key to fetch plan
  from cache

25
Prepared Statement - Example

• PREPARE sel_stmt FROM SELECT name FROM pet WHERE
  id?
• SET _at_pet_id1
• EXECUTE sel_stmt USING _at_pet_id

name
Fluffy
26
JDBC Dynamic Query Example

• Statement stmt con.createStatement()
• for int(i1 ilt10 i)
• String stmtString select name from pet
  where id
• I
• ResultSet rs stmt.executeQuery(stmtString)
• while (rs.next())
• System.out.println(rs.getString(1))
• rs.close()
• statement is parsed by the database each time
• new query plan is created for each select
  statement and cached (entire stmt being the key)

27
JDBC PreparedStatement

• PreparedStatement ps con.prepareStatement(
• select name from pet
  where id ? )
• for int(i1 ilt10 i)
• ps.setInt(1, i) -- variable binding
• ResultSet rs ps.executeQuery()
• while (rs.next())
• System.out.println(rs.getString(1))
• rs.close()
• ? is called placeholder
• query is parsed only once and only 1 execution
  plan is created and caches for it
• executed many times after binding variables
• MUCH MORE EFFICIENT!

28
Placeholders- Setter methods
void setInt(int parameterIndex, int value)
void setString(int parameterIndex, String value)
void setDate(int parameterIndex, java.sql.Date value)
void setTimestamp(int parameterIndex, java.sql.Timestamp value)
void setLong(int parameterIndex, long value)
29
Disconnecting from the database

• close a connection to the database to release
  resources
• Make sure you first close all component that use
  that connection
• Close components in the reverse order you opened
  them.
• rs.close()
• stmt.close()
• conn.close()