Slide 1

JDBC CONFIGURATIONCONTROLPANEL

Java.sql packageThis package provides the APIs for accessing and processing data which is stored in the database especially relational database by using the java programming language. It includes a framework where we different drivers can be installed dynamically to access different databases especially relational databases.

This java.sql package contains API for the following : 1 Making a connection with a database with the help of DriverManager class

a) DriverManager class: It helps to make a connection with the driver.

b) SQLPermission class: It provides a permission when the code is running within a Security Manager, such as an applet. It attempts to set up a logging stream through the DriverManager class.

c) Driver interface : This interface is mainly used by the DriverManager class for registering and connecting drivers based on JDBC technology.

d). DriverPropertyInfo class : This class is generally not used by the general user.2). Sending SQL Parameters to a database :

a). Statement interface: It is used to send basic SQL statements.

b). PreparedStatement interface: It is used to send prepared statements or derived SQL statements from the Statement object.

c). CallableStatement interface : This interface is used to call database stored procedures.

d). Connection interface : It provides methods for creating statements and managing their connections and properties.

e). Savepoint : It helps to make the savepoints in a transaction.

3). Updating and retrieving the results of a query:

a). ResultSet interface: This object maintains a cursor pointing to its current row of data. The cursor is initially positioned before the first row. The next method of the resultset interface moves the cursor to the next row and it will return false if there are no more rows in the ResultSet object. By default ResultSet object is not updatable and has a cursor that moves forward only.4.) Providing Standard mappings for SQL types to classes and interfaces in Java Programming language.

a). Array interface: It provides the mapping for SQL Array.b). Blob interface : It provides the mapping for SQL Blob.c). Clob interface: It provides the mapping for SQL Clob.d). Date class: It provides the mapping for SQL Date. e). Ref interface: It provides the mapping for SQL Ref.f). Struct interface: It provides the mapping for SQL Struct.g). Time class: It provides the mapping for SQL Time.h). Timestamp: It provides the mapping for SQL Timestamp.i). Types: It provides the mapping for SQL types.5). Metadataa). DatabaseMetaData interface: It keeps the data about the data. It provides information about the database.b). ResultSetMetaData: It gives the information about the columns of a ResultSet object. c). ParameterMetaData: It gives the information about the parameters to the PreparedStatement commands

6). Exceptionsa). SQLException: It is thrown by the mehods whenever there is a problem while accessing the data or any other things.b). SQLWarning: This exception is thrown to indicate the warning. c). BatchUpdateException: This exception is thrown to indicate that all commands in a batch update are not executed successfully.d). DataTruncation: It is thrown to indicate that the data may have been truncated.

Custom mapping an SQL user- defined type (UDT) to a class in the java programming language.a). SQLData interface: It gives the mapping of a UDT to an intance of this class.b). SQLInput interface: It gives the methods for reading UDT attributes from a stream. c). SQLOutput: It gives the methods for writing UDT attributes back to a stream.

JDBC ClassesDriverManagerManages JDBC DriversUsed to Obtain a connection to a Database

TypesDefines constants which identify SQL types

DateUsed to Map between java.util.Date and the SQL DATE type

TimeUsed to Map between java.util.Date and the SQL TIME type

TimeStampUsed to Map between java.util.Date and the SQL TIMESTAMP type

JDBC InterfacesDriverAll JDBC Drivers must implement the Driver interface. Used to obtain a connection to a specific database type

ConnectionRepresents a connection to a specific databaseUsed for creating statementsUsed for managing database transactionsUsed for accessing stored proceduresUsed for creating callable statements

StatementUsed for executing SQL statements against the database

JDBC InterfacesResultSetRepresents the result of an SQL statementProvides methods for navigating through the resulting data

PreparedStatementSimilar to a stored procedureAn SQL statement (which can contain parameters) is compiled and stored in the database

CallableStatementUsed for executingstored proceduresDatabaseMetaDataProvides access to a database's system catalogue

ResultSetMetaDataProvides information about the data contained within a ResultSet

Using JDBCTo execute a statement against a database, the following flow is observedLoad the driver (Only performed once)Obtain a Connection to the database (Save for later use)Obtain a Statement object from the ConnectionUse the Statement object to execute SQL. Updates, inserts and deletes return Boolean. Selects return a ResultSetNavigate ResultSet, using data as requiredClose ResultSetClose Statement

Do NOT close the connectionThe same connection object can be used to create further statementsA Connection may only have one active Statement at a time. Do not forget to close the statement when it is no longer needed.Close the connection when you no longer need to access the database

Connecting to a DatabaseOnce a Driver is loaded, a connection can be made to the database

The connection is defined by URLThe URL has the following form:jdbc:driver:databasename

Examples:jdbc:odbc:MyOdbcDatabasejdbc:postgres:WebsiteDatabasejdbc:oracle:CustomerInfo

A connection is obtained in the following manner:

Connection aConnection = DriverManager.getConnection("jdbc:odbc:myDatabase");

Overloaded versions of the getConnection method allow the specification of a username and password for authentication with the database.Using a ConnectionThe Connection interface defines many methods for managing and using a connection to the database

public Statement createStatement()public PreparedStatement prepareStatement(String sql)public void setAutoCommit(boolean)public void commit()public void rollback()public void close()

The most commonly used method is createStatement()When an SQL statement is to be issued against the database, a Statement object must be created through the Connection

Using a StatementThe Statement interface defines two methods for executing SQL against the database

public ResultSet executeQuery(String sql)public int executeUpdate(String sql)

executeQuery returns a ResultSetAll rows and columns which match the query are contained within the ResultSetThe developer navigates through the ResultSet and uses the data as required.

executeUpdate returns the number of rows changed by the update statementThis is used for insert statements, update statements and delete statements

Using a ResultSetThe ResultSet interface defines many navigation methods

public boolean first()public boolean last()public boolean next()public boolean previous()

The ResultSet interface also defines data access methods

public int getInt(int columnNumber)-- Note: Columns are numberedpublic int getInt(String columnName)-- from 1 (not 0)public long getLong(int columnNumber)public long getLong(String columnName)public String getString(int columnNumber)public String getString(String columnName)

There are MANY more methods. Check the API documentation for a complete listSQL Types/Java Types MappingSQL TypeJava Type

CHARStringVARCHARStringLONGVARCHARStringNUMERICjava.Math.BigDecimalDECIMALjava.Math.BigDecimalBITbooleanTINYINTintSMALLINTintINTEGERintBIGINTlongREALfloatFLOATdoubleDOUBLEdoubleBINARYbyte[]VARBINARYbyte[]DATEjava.sql.DateTIMEjava.sql.TimeTIMESTAMPjava.sql.TimestampDifference between Statement & PreparedStatement

There are four steps for the execution of query : Query is parsed,Query is compile,Query is optimized and Query is executed. In case of statement interface these four steps are performed ,each time when query is submitted for execution.But in case of prepared statement first three steps are performed only once, when the query in initially submitted. Only the last step is performed each time query is submitted(in subsequence submissions),i.e if same query is submitted to be execute with different values multiple times then prepared statement interface provides better perfromance as compared to statement interface.*

Comparing with the execution control of StatementexecuteXXX() method is invoked on the SQL statementexecuteUpdate(), executeQuery()

2) The Statement object submits SQL Statement to DB

3) The DB compiles the given SQL statement

4) An execution plan is prepared by DB to execute the statement

5) Execution plan is then executed.If SQL contains SELECT, the results are cached in bufferResults are sent to the Statement object

6) Finally, response is sent to java application executeUpdate() methods

Normal statement execution

Compilation includes syntax check, name validation etc.After validation, query optimizer prepares execution planReturns best execution plan

SQL statement goes thru above every time it is executedIf same query is executed multiple times,

Then execution plan can be saved and reuse itThis stored execution plan is known as pre-compiled statement

The PreparedStatement interface is designed for it Hence, their execution is much faster compared to statement

when using PreparedStatementMust be associated with one connectionIt represents the execution plan, need to pass parametersOn connection close, it implicitly gets closedExecution of query is processed as follows

The connection object submits the SQL statement todatabaseDatabase compiles the given statementExecution plan is prepared by the databaseDatabase returns the execution plan with unique idConnection objectThe setXXX() methods are used to set the parameters of SQLexecuteXXX () method is invoked to execute the SQLDatabase executes the execution plan with supplied parametersFinall the result is sent to the java applicationAdvantagesImproves the application performance compared toStatement()Inserts or updates SQL99 data typesCLOB, BLOBProvides a programming approach to set the values

DisadvantagesCan represent only one SQL statement at a timeCan not execute more than one SQL statement in a singlePreparedStatement

Situations when it is useful to useA single query is executed multiple timesWhen a query consists of numerous parameters and complex types