JDBC API:1. Consists of some concrete classes ( e.g.

Date, Time & SQLExce

ption )
2. consists a set of interfaces that are implemented in a driver
class.
Once the database vendor's Driver classes are loaded, we can access them
by using following sequence:1. Use the DriverManager class to obtain a reference to a Connec
tion object using the getConnection() method.
Signture of getConnection() method is getConnection(url,
name, password)
2. Use the Connection object to obtain a reference to Statement
object through the createStatement() method
Signature of createStatement() is createStatement().
3. Use the Statement object to obtain ResultSet object through t
he executeQuery() method
executeQuery(query) accepts a string, where query is sta
tic string of SQL.

-> the DriverManager class is used to get an instance of a Connection ob

ject, using the JDBC driver named in the JDBC URL:
String url = "jdbc:derby://localhost:1527/EmployeeDB";
Connection con =DriverManager.getConnection(url);\
the URL syntx for a JDBC driver is
jdbc:<driver>:[subsubprotocol:][databasename][;attribute
= value]
DriverManager
-> any JDBC 4.0 drivers found in class path are automatically lo
aded.
-> DriverManager.getConnection method will attempt to load the d
river class by looking at the file META_INF/services/java.sql.Driver.
It contains the JDBC driver's implementation of java.sql.Driver.
-> Driver's prior to JDBC 4.0 must be loaded manually by using
try {
java.lang.Class.forName("<fully qualified path o
f the driver>");
} catch (ClassNotFoundException c)
{}
-> Driver classes can also be passed to the interpreter on the c
ommand line:
java -djdbc.drivers=< fully qualified path to the driver
> <class to run>

java.sql.connection:-

-> is connection interface that represents the session between t

he Java application and the database. ( provides session with the database)
example:Connection con = DriverManager.getConnection(url, userna
me, password);
java.sql.Statement:-> an interface that is used to execute a static SQL statement a
nd return the reult
example:Statement stmt = con.createStatement();
java.sql.ResultSet:-> an interface that represents a database result set.
example:String query = "SELECT * from Employee";
RsultSet rs = stmt.executeQuery(query);

package com.example.text;
import
import
import
import

java.sql.DriverManager;
java.sql.ResultSet;
java.sql.SQLException;
java.util.Date;

public class SimpleJDBCTest

{
public static void main ( String[] args)
{
String url = "jdbc:derby://localhost:1527/EmployeeDB";
String username = "public";
String password = "tiger";
String query = "SELECT * FROM Employee";
try ( Connection con = DriverManager.getConnection ( url
, username, password);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery( query ))
{
while (rs.next())
{
int empID = rs.getInt("ID");
String first = rs.getString("Fir
stName");
String last = rs.getString("Last
Name");
Date birthdate = rs.getDate("Bir
thDate");
float salary = rs.getFloat("Sala
ry");
System.out.println("Employee ID
:" + empID + "\n" + "Employee Name:" + first + " " + last + "\n" +
"BirthDate:" + birthdate + "\n" + "Salary : " + salary );
}
}
catch ( SQLException e )

{
System.out.println ( "SQL Exception : " + e );
}
}
}
OUTPUT :
Employee ID : 110
Employee Name : Troy Hammer
Birth Date : 1965-03-31
Salary : 102310.15
We can use java.io.Console to read url, username & password from the user.

STATEMENT
-> to execute the SQL queries with JDBC, we must create an insta
nce of the Statement object.
-> Statement object is a wrapper object for a query.
various execute methods of Statement interface.
executeQuery (sqlString)

- returns ResultSet - used

for SELECT statement

executeUpdate (sqlString ) - returns Int ( rows affect
ed ) - used for INSERT,UPDATE,DELETE, or DDL
execute(sqlString) - returns boolean ( true if there
was a ResultSet ) - used for Any SQL command or commands.

PORTABLE CODE
The JDBC driver provides a programmatic "insulating" layer between your
java application and the database.
We also need to consider SQL syntax and semantics while writing database
applcations.
Most databases support a standard set of SQL syntax and semantics descri
bed by the ANSI SQL-92 Entry-level specification
// checking for support for specification
Connection con = DriverManager.getConnection(url, username, pass
word);
DatebaseMetaData dbm = con.getMetaData();
if (dbm.supportsANSI92EntrySQL() )
{
//support for entry-level SQL -92 standard
}
DatabaseMetaData interface has a set of methods that the driver develope
r uses to indicate what the driver supports, including support for entry, interm
ediate or full support for SQL - 92
also includes what type of support the database provides for queries, ty
pes, transations and more.

SQLEXCEPTION

->is thrown from errors that occur in one of the following types
: driver methods, methods that access database or make an attempt to get connect
ion to the database.
-> implements Iterable. Exceptions can be chained an returned as
a single object.
-> SQLState and SQLErrorCode values are database dependant.

catch(SQLException e)
{
while(e!=null)
{
System.out.println("SQLState: " + e.getSQLState(
));
System.out.println("SQLErrorCode"+e.getSQLErrorC
ode());
System.out.println("meassage:" + e.getMessage())
;
Throwable t = e.getCause();
while(t!=null)
{
System.out.println("Cause:"+t);
t=t.getCause();
}
e=e.getNextException();
}
}

ResultSet
-> maintains a cursor to the returned rows. initially cursor poi
nts before the first row.
-> next() is called to position the cursor in the next row.
-> def cursor is NOT upadatable and points forward only.
-> possible to create scrollable/updatable cursor.
Statement stmt = con.createStatement(ResultSet.TYPE_SCRO
LL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT a,b FROM TABLE2
");
-> ResultSet has accessor methods to read the contents of each c
olumn returned in a row.
-> ResultSet has a getter method of each type.

try-with-resources construct
-> compiler checks to see that the objects inside the parenthese
s implements java.lang.AutoCloseable
- includes one method void close()

Getting a rowCount
-> to work with databases, applications require the information

related to the number of rows fetched by a ResultSet object.

public int rowCount(ResultSet rs) throws SQLException
int rowCount = 0;
int currRow = rs.getRow();
// valid ResultSet ?
if (!rs.last())
return -1;
rowCount = rs.getRow();
// return the cursor to the current postition
if ( currRow == 0 )
rs.beforeFirst();
else
rs.absolute(currRow);
return rowCount;

}
Controlling ResultSet Fetch Size
-> JDBC driver determines the number of rows fetched by a query.
-> setFetchSize() method:
- Controls the fetch size for large data sets.
- example
rs.setFetchSize(25);
calls to rs.next() return the data in the cache until the 26th r
ow, at which time the driver will fetch another 35 rows.
PreparedStatement
-> subclass of the Statement interface
-> used to pass arguments to a precompiled SQL statement.
-> is useful when the same SQL statements are executed multipl
e times.

";

double value = 100_000;

String query = 'SELECT * FROM Employee WHERE Salary > ?
// ? is the parameter for substitution
PreparedStatement pstmt = con.preparedStatement ( query

);
pstmt.setDouble(1,value);
// substitute value for the
first parameter in the prepared statement
ResultSet rs = pstmt.executeQuery();
CallableStatement
-> allows non SQL statements ( like stored procudures ) to be ex
ecuted against the database.
CallableStatement cstmt = con.prepareCall ( "{CALL EmplA
geCount (?,?)}" );
int age=50;
cstmt.setInt(1,age);

// IN parameter is passed i

n the stored procedure.

ResultSet rs = cstmt.executeQuery();
cstmt.registerOutparameter(2, Types.INTEGER);
boolean result = cstmt.getInt(2); // OUT parameter is
returned form the stored procedure.
Syste.out.println("there are " +count + "Employees over
the age of "+age);

TRANSACTION
-> it is a mechanism to handle the groups of operations
-> either all operations in a transaction occur or none.
-> operations involved might rely on 1 or more databases.
-> transactions are formally defined by the set of ACID properti
es
JDBC Transactions
-> bY def, when a Connection is created , it is in the auto-com
mit mode.
-> each individual SQL statement is treated like a trans
action and automatically committed after it is executed.
-> to group 2 or more statements together, we must disab
le the auto-commit mode, as shown in the following code:con.setAutoCommit(false);
-> we must explicitly call the commit() method to comple
te the transaction with a database,
con.commit();
-> we can programmatically rollback transactions inthe e
vent of a failure
con.rollback();
RowSet
RowSetFactory
-> is used to create instances of the RowSet implementat
ions.
Data Access Objects
-> By combining the code that accesses the database with the "bu
siness" logic, the data access methods and the Employee table are tightly couple
d.
-> any changes to the table (such as adding a field) will requir
e a complete change to the application.
-> Employee data is not encapsulated within the example applicat
ion.
-> the purpose of DAO is to separate database - related activiti
es from the business model.