1 / 32

JDBC CONFIGURATION

JDBC CONFIGURATION. CONTROLPANEL. Java.sql package.

alicia
Download Presentation

JDBC CONFIGURATION

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. JDBC CONFIGURATION

  2. CONTROLPANEL

  3. Java.sql package • This 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.

  4. This java.sql package contains API for the following : • 1 Making a connection with a database with the help of DriverManager classa) 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.

  5. 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.

  6. 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.

  7. 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.

  8. 5). Metadata • a). 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

  9. 6). Exceptions • a). 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.

  10. 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.

  11. JDBC Classes • DriverManager • Manages JDBC Drivers • Used to Obtain a connection to a Database • Types • Defines constants which identify SQL types • Date • Used to Map between java.util.Date and the SQL DATE type • Time • Used to Map between java.util.Date and the SQL TIME type • TimeStamp • Used to Map between java.util.Date and the SQL TIMESTAMP type

  12. JDBC Interfaces • Driver • All JDBC Drivers must implement the Driver interface. Used to obtain a connection to a specific database type • Connection • Represents a connection to a specific database • Used for creating statements • Used for managing database transactions • Used for accessing stored procedures • Used for creating callable statements • Statement • Used for executing SQL statements against the database

  13. JDBC Interfaces • ResultSet • Represents the result of an SQL statement • Provides methods for navigating through the resulting data • PreparedStatement • Similar to a stored procedure • An SQL statement (which can contain parameters) is compiled and stored in the database • CallableStatement • Used for executing • stored procedures • DatabaseMetaData • Provides access to a database's system catalogue • ResultSetMetaData • Provides information about the data contained within a ResultSet

  14. Using JDBC • To execute a statement against a database, the following flow is observed • Load the driver (Only performed once) • Obtain a Connection to the database (Save for later use) • Obtain a Statement object from the Connection • Use the Statement object to execute SQL. Updates, inserts and deletes return Boolean. Selects return a ResultSet • Navigate ResultSet, using data as required • Close ResultSet • Close Statement • Do NOT close the connection • The same connection object can be used to create further statements • A 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

  15. Connecting to a Database • Once a Driver is loaded, a connection can be made to the database • The connection is defined by URL • The URL has the following form: • jdbc:driver:databasename • Examples: • jdbc:odbc:MyOdbcDatabase • jdbc:postgres:WebsiteDatabase • jdbc: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.

  16. Using a Connection • The Connection interface defines many methods for managing and using a connection to the database • public Statement createStatement() • public PreparedStatementprepareStatement(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

  17. Using a Statement • The Statement interface defines two methods for executing SQL against the database • public ResultSetexecuteQuery(String sql) • public intexecuteUpdate(String sql) • executeQuery returns a ResultSet • All rows and columns which match the query are contained within the ResultSet • The developer navigates through the ResultSet and uses the data as required. • executeUpdate returns the number of rows changed by the update statement • This is used for insert statements, update statements and delete statements

  18. Using a ResultSet • The 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 intgetInt(intcolumnNumber) -- Note: Columns are numbered • public intgetInt(String columnName) -- from 1 (not 0) • public long getLong(intcolumnNumber) • public long getLong(String columnName) • public String getString(intcolumnNumber) • public String getString(String columnName) • There are MANY more methods. Check the API documentation for a complete list

  19. SQL Types/Java Types Mapping SQL Type Java Type CHAR String VARCHAR String LONGVARCHAR String NUMERIC java.Math.BigDecimal DECIMAL java.Math.BigDecimal BIT boolean TINYINT int SMALLINT int INTEGER int BIGINT long REAL float FLOAT double DOUBLE double BINARY byte[] VARBINARY byte[] DATE java.sql.Date TIME java.sql.Time TIMESTAMP java.sql.Timestamp

  20. Difference 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.*

  21. •Comparing with the execution control of Statement – executeXXX() method is invoked on the SQL statement executeUpdate(), 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 buffer–Results are sent to the Statement object 6) Finally, response is sent to java application executeUpdate() methods

  22. Normal statement execution –Compilation includes syntax check, name validation etc.–After validation, query optimizer prepares execution plan •Returns best execution plan •SQL statement goes thru above every time it is executed •If same query is executed multiple times, – Then execution plan can be saved and reuse it– This 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

  23. when using PreparedStatement –Must be associated with one connection –It represents the execution plan, need to pass parameters –On connection close, it implicitly gets closed –Execution of query is processed as follows  The connection object submits the SQL statement todatabase Database compiles the given statement •Execution plan is prepared by the database •Database returns the execution plan with unique id–Connection object– The setXXX() methods are used to set the parameters of SQL– executeXXX () method is invoked to execute the SQL –Database executes the execution plan with supplied parameters–Finallthe result is sent to the java application

  24. Advantages –Improves the application performance compared to Statement() –Inserts or updates SQL99 data types •CLOB, BLOB –Provides a programming approach to set the values •Disadvantages –Can represent only one SQL statement at a time –Can not execute more than one SQL statement in a single PreparedStatement •Situations when it is useful to use –A single query is executed multiple times –When a query consists of numerous parameters and complex types

More Related