JDBC TALKING TO DATABASE UnitVI K Phani Sirisha

JDBC TALKING TO DATABASE (Unit-VI) K. Phani Sirisha

What Is JDBC? • Java database connectivity or JDBC is an interface used to connect to relational databases from the Java program. • JDBC is a Java API which consists of a set of classes and interfaces written in the Java programming language. • JDBC provides a standard API tool for database developers and makes it possible to write database applications using a pure Java API. • With the help of JDBC API developers can query database, add data, update data and even delete the data from database very easily.

What does JDBC Do ? With the help of JDBC, it is possible to do the following three things • to establish a connection with a database • to send SQL statements • to process the resullts The following code fragment gives a basic example of these three steps: Connection con = Driver. Manager. get. Connection ( "jdbc: odbc: wombat", "login", "password"); Statement stmt = con. create. Statement(); Result. Set rs = stmt. execute. Query("SELECT a, b, c FROM Table 1"); while (rs. next()) { int x = get. Int("a"); String s = get. String("b"); float f = get. Float("c"); }

JDBC Vs. ODBC and other APIs Microsoft's ODBC (Open Data. Base Connectivity) API is probably the most widely used programming interface for accessing relational databases. It offers the ability to connect to almost all databases on almost all platforms. So why not just use ODBC from Java? "Why do you need JDBC? " • ODBC is not appropriate for direct use from Java because it uses a C interface. Calls from Java to native C code have a number of drawbacks in the security, implementation, robustness, and automatic portability of applications. • A literal translation of the ODBC C API into a Java API would not be desirable. For example, Java has no pointers, and ODBC makes copious use of them. • ODBC is hard to learn. It mixes simple and advanced features together, and it has complex options even for simple queries. JDBC, on the other hand, was designed to keep simple things simple while allowing more advanced capabilities where required. • When ODBC is used, the ODBC driver manager and drivers must be manually installed on every client machine. When the JDBC driver is written completely in Java, however, JDBC code is automatically installable, portable, and secure on all Java platforms from network computers to mainframes.

Two-tier and Three-tier Models • The JDBC API supports both two-tier and three-tier models for database access. • In a two-tier model, a Java application/applet communicates directly with the database, via the JDBC driver. The Java application/applet and the database can be on the same machine, or the database can be on a server and the Java application/applet can be on a client machine using any network protocol.

• In the three-tier model, commands are sent to a "middle tier" of services, which then send SQL statements to the database. The database processes the SQL statements and sends the results back to the middle tier, which then sends them to the user. Another advantage is that when there is a middle tier, the user can employ an easy-to-use higher-level API which is translated by the middle tier into the appropriate low-level calls. • In a three-tier model, a Java application/applet communicates with a middle tier component that functions as an application server. The application server talks to a given database using JDBC.

The JDBC Package • The java. sql and javax. sql are the primary packages for JDBC 4. 0 JDBC API is contained in 2 packages. java. sql - which contains java data objects that provide the basics for connecting to the DBMS and interacting with data stored in the DBMS. This package is a part of J 2 SE. javax. sql – which extends java. sql and is in J 2 EE. This includes the data objects that manage connection pooling, JNDI , and other advanced JDBC features.

Components of JDBC Component Description Driver. Manager It loads database drivers and manages the connection between the application and the driver Driver It translates API calls into operation for a specific data source Connection It is a session between an application and a database Statement It is an SQL statement to perform a query or update operation It gives information about returned data, the database, and the driver It is the logical set of columns and rows of data returned by executing a statement Meta. Data Result. Set

Types of JDBC Drivers The JDBC drivers that we are aware of at this time fit into one of four categories: • JDBC-ODBC bridge driver: Which provides JDBC access via ODBC drivers. Note that ODBC binary code, and in many cases database client code, must be loaded on each client machine that uses this driver. As a result, this kind of driver is most appropriate on a corporate network where client installations are not a major problem, or appropriate for application server code written in Java in a three-tier architecture. • Native-API partly-Java driver: This kind of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB 2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.

• JDBC-Net pure Java driver: This driver translates JDBC calls into a DBMS- independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect its pure Java clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC alternative. • Native-protocol pure Java driver: This kind of driver converts JDBC calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Note: To access database using the JDBC API, driver categories 3 and 4 are the most preferable.

Java. Soft Framework Java. Soft provides three JDBC product components as part of the Java Development Kit (JDK): • The JDBC driver manager, • The JDBC driver test suite, and • The JDBC-ODBC bridge. The JDBC driver manager is the backbone of the JDBC architecture. Its primary function is to connect Java applications to the correct JDBC driver The JDBC driver test suite provides some confidence that JDBC drivers will run your program. Only drivers that pass the JDBC driver test suite can be designated JDBC COMPLIANT The JDBC-ODBC bridge allows ODBC drivers to be used as JDBC drivers. It will provide a way to access some of the less popular DBMSs if JDBC drivers are not implemented for them.

Driver Interface and Driver. Manager Class • To create Connection objects is the primary use of the Driver interface. • All drivers supply a class that implements the Driver interface. • Driver. Manager keeps track of the drivers that are available and establishing a connection between a database and the appropriate driver. • With the help of Driver. Manager class drivers are loaded for any given connection request • When a Driver class is loaded, it creates an instance of itself and registers it with the Driver. Manager class. • A user can load and register a driver by calling Class. for. Name(“URL”) • The Driver. Manager class will attempt to load the driver classes referenced in the Url by calling get. Connection and get. Drivers methods.

Connection Interface • A Connection object is instantiated to make a connection to a specific database using a specific driver. • Connection interface can be used for the maintenance and monitoring the status of database sessions • A connection session includes the SQL statements that are executed and the results that are returned over that connection. • A single application can have one or more connections with a single database, or it can have connections with many different databases.

Statement Interface • A Statement object is used to send SQL statements to a database. • There actually three kinds of Statement objects, all of which act as containers for executing SQL statements on a given connection: Statement, Prepared. Statement, which inherits from Statement, and Callable. Statement, which inherits from Prepared. Statement. • They are specialized for sending particular types of SQL statements • A Statement object is used to execute a simple SQL statement • A Prepared. Statement object is used to execute a precompiled SQL statement • A Callable. Statement object is used to execute a call to a database stored procedure.

Prepared. Statement Interface • The Prepared. Statement interface is a subclass of Statement interface. It is used to execute precompiled SQL statements. • For faster and efficient statement execution, pre-compilation is allowed.

Result. Set Interface • A Result. Set is actually a container for the result of a query. i. e which contains all of the rows which satisfied the conditions in an SQL statement • Result. Set provides the methods for the retrieval of data which is returned by the database. • It also contains methods for conversion of data types between SQL and Java.

Result. Set. Meta. Data Interface • Through Result. Set. Meta. Data interface, the user can get information about the types and properties of the columns in a Result. Set object • This is useful to collect data that has the same semantics, but stored in different database sources in different formats.

Database. Meta. Data Interface • The Database. Meta. Data interface provides information regarding the database itself. • For example, it gives information about its version , table names, columns of these tables and their types and supported functions. • It has also methods for discovering database information specific to the current connection, such as available tables, schemas, and catalogs.

The Essential JDBC Program Basic steps to writing an application that uses JDBC API 1. Import the java. sql package Any application that uses JDBC API must import the java. sql package import java. sql. *; 2. Load a JDBC Driver • JDBC driver must be loaded before the J 2 EE component can connect the DBMS. • When a driver class is first loaded, it registers itself with the Driver. Manager. • Class. for. Name() method can be used to load the JDBC driver. • We should pass the name of the driver to this method and this method throws Class. Not. Found. Exception if an error occurs when loading the driver String driver = “sun. jdbc. odbc. Jdbc. Odbc. Driver”; Class. for. Name(driver); OR Class. for. Name(“sun. jdbc. odbc. Jdbc. Odbc. Driver”);

3. Establishing a Connection • Once the driver is loaded, the J 2 EE component must connect the DBMS using the Driver. Manager class and Connection interface. • get. Connection() method of Driver. Manager class is used to establishing a connection to the database • Driver. Manager Connects to given JDBC URL with given user name and password. • A Connection object is returned by the get. Connection() if access is granted; else get. Connection() throws an SQLException. • If username & password is required then those information need to be supplied to access the database. Connection con = Driver. Manager. get. Connection(url, username, password) ;

4. Create a Statement • Once the Connection is established, a Statement object is needed to execute the SQL statements • So, to create a Statement object, we can use create. Statement() method of Connection interface by using Connection object. Statement st = con. create. Statement(); 5. Execute the Statement • The execute. Query() method of Statement interface is used to execute queries to the database and it throws an SQLException if it found anything wrong in the query. • This method returns the object of Result. Set that can be used to get all the records of a table. Result. Set rs = st. execute. Query(“Select * from Customers”);

6. Process the Result. Set • After executing the query, the results received from DBMS are assigned to Result. Set object. • Only one Result. Set per Statement can be open at once. • The Result. Set consists of tuples and returns one tuple at a time when the next() function is paplied • We can say that the Result. Set acts like an iterator. • A Result. Set maintains a cursor pointing to its current row of data. • The next() method moves the cursor to the next row. • get. String() method of Result. Set object is used to get the value of a specified column in the current row of the Result. Set. while(rs. next()) { First. Name = rs. get. String(1); Last. Name = rs. get. String(2); Salary = rs. get. Int(3); System. out. println(Firstname+” “+Last. Name+” “+salary); }

7. Close the Satement • It is appropriate to close a statement when it is no longer in need st. close(); 8. Close the Connection • Once the J 2 EE component is finished accessing the DBMS, it is necessary to disconnect from the database • The connection to the DBMS is terminated by using the close() method of the Connection interface. • commit() function is executed to commit all transactions that have been made through the connection • By closing connection object statement and Result. Set will be closed automatically. con. commit(); con. close();

JDBC URL Syntax: jdbc : <subprotocol> : <subname> • URL consists of 3 parts üJdbc – This indicates that the jdbc protocol is to be used to read the URL. ü<subprotocol> - which is the JDBC driver name ü<subname> - which is the name of the database • Each driver has its own subprotocol • Each subprotocol has its own syntax for the source Ex: jdbc : odbc : Customer. Info

Statement Object contains 3 methods: execute() (used for DDL commands like, Create, Alter, Drop) execute. Update() (Used for DML commands like, Insert, Update, Delete) exceute. Query() (Used for Select command) • The execute() method is used during execution of DDL commands and also used when there may be multiple results returned. • The execute. Update() executes INSERT, UPDATE, DELETE, and returns an integer value specifying the number of rows affected or 0 if zero rows selected. • The execute. Query() method, which passes the query as an argument. The query is then transmitted to the DBMS for processing. • The execute. Query()method executes a simple select query and returns a Result. Set object. • All the 3 methods throws an SQLException when error occurs.

Example-1: Statement st = con. create. Statement(); Result. Set rs = st. execute. Query(“select * from employee”); Example-2: Statement st = con. create. Statement(); int r=st. execute. Update(“insert into employee values(10, "skr”, 98453421)”); int s= st. execute. Update(“update employee set Mobile=89706454 where Mobile=98453421” ); int n=st. execute. Update(“delete from employee where eno=10”); Example-3: Statement st = con. create. Statement(); st. execute(“Drop table Employee”); st. execute(“Create table Employee (eno number, name varhcar(10), age Number(3))”); st. execute(“alter table employee add primary key(eno)”);

Prepared. Statement • The Prepared. Statement object allows you to execute parameterized queries. • Prepared. Statement interface is used to represent a precompiled query, which can be executed multiple times without compiling it again and again. • It improves the performance of an application and performs faster execution by avoiding compilation of multiple queries. • A SQL query can be precompiled and executed by using the Prepared. Statement object. Ex: Select * from employee where eno=? • Here a query is created as usual, but a question mark is used as a placeholder for a value that is inserted into the query after the query is compiled. • The prepare. Statement() method of Connection object is called to return the Prepared. Statement object.

• The setxxx() method of the Prepared. Statement object is used to replace the question mark with the value passed to the setxxx() method. • The setxxx() requires 2 parameters. The first parameter is an integer that identifies the position of the question mark placeholder and the second parameter is the value that replaces the question mark placeholder. • Prepared. Statement's execute methods have no parameters Code snippet: try{ Prepared. Statement pst; String query=“select * from customer where cust. Number=? ”; pst = con. prepare. Statement(query); pst. set. Int(1, 1234); Result. Set rs=pst. execute. Query(); pst. close(); } catch (Exception e) { }

execute. Update • execute. Update method can be used to execute both the Statement and the Prepared. Statement objects. • No argument is supplied to the execue. Update when it is used with Prepared. Statement because the Prepared. Statement’s object already contains the query • Once a parameter has been set with a value, it will retain that value until it is reset to another value or the method clear. Parameters is called String query = “update coffees set sales=? where cof_name like ? “; Prepared. Statement update. Sales = con. prepare. Statement(query); update. Sales. set. Int(1, 75); update. Sales. set. String(2, ”colombian”); updatesales. execute. Update(); • execute. Update() method can also return an integer value that indicates howmany rows of a table were updated int n = updatesales. execute. Update();

Using a Loop to Set Values The following code fragment demonstrates using a for loop to set values for parameters in the prepared. Statement object update. Sales. String query = “update coffees set sales=? where cof_name like ? “; Prepared. Statement update. Sales = con. prepare. Statement(query); int [] sales = {175, 150, 60}; String [] coffees = {“colombian”, ”French_Roast”, “Espresso”}; for(int i=0; i<coffees. length; i++) { update. Sales. set. Int(1, sales[i]); update. Sales. set. String(2, coffees[i]); updatesales. execute. Update(); }

Result. Set • The Result. Set interface is used to represent the data in tabular form, which is generated by executing an SQL query. • The Result. Set object contains methods that are used to copy data from the Result. Set into a Java collection object or variable for further processing. • Data in a Result. Set object is logically organized into a virtual table consisting of rows and columns. • The Result. Set uses a virtual cursor to point to a row of the virtual table. • The virtual cursor is positioned above the first row of data when the Result. Set is returned by the execute. Query(). This means the virtual cursor must be moved to the first row using the next() method. • The next() method returns a boolean true if the row contains data, otherwise a boolean false is returned. • Once the virtual cursor points to a row, the getxxx() method is used to copy data from the row to a collection, object or variable. • The data type of the getxxx() method must be same data type as the column in the Result. Set.

• getxxx() method requires one parameter, which is an integer that represents the number of the column that contains the data. • For example, get. String(1) copies the data from the first column of the Result. Set. • We can also retrieve the data from Result. Set object by passing column name as argument to the getxxx() method. i. e rs. get. String(“First. Name”); Reading the Result. Set try { String query = "SELECT First. Name, Last. Name FROM Customers"; Statement st; Result. Set rs; st = con. create. Statement(); rs = st. execute. Query (query); while(rs. next()) { System. out. println(rs. get. String(1) +”t”+ rs. get. String (2)); } st. close(); } catch ( Exception e ){ }

Scrollable Result. Sets • The Scrollable Resultset is the first area of new functionality. • It is possible with the method next to move through the rows of a Result. Set, which is started at the first row and going to the last row. (i. e only forward) • But by using this scrollable Result. Set that is scrollable, you can move a Result. Set’s cursor wherever you want and also check on where it is currently positioned. • you can move back one row at any time or start at the last row and iterate through a Resultset’s rows backwards with the method previous. • There also methods for moving to the nth row or moving a specified number of rows from the current row. • It can also move the cursor to the first row, the last row, the position before the first row, and the position after the last row.

Code Snippet: Result. Set rs = st. execute. Query(“select * from committees”); While(rs. next()) { } rs. absolute(5); rs. relative(-2); rs. relative(4); rs. previous(); int row. Number = rs. get. Row(); rs. move. After. Last(); while(previous()) { }

Batch Updates • Batch Processing allows you to group related SQL statements into a batch and submit them with one call to the database. • When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance. • You should use the Database. Meta. Data. supports. Batch. Updates() method to determine if the target database supports batch update processing. The method returns true if your JDBC driver supports this feature. • The add. Batch() method of Statement, Prepared. Statement, and Callable. Statement is used to add individual statements to the batch. • The execute. Batch() is used to start the execution of all the statements grouped together.

• The execute. Batch() returns an array of integers, and each element of the array represents the update count for the respective update statement. • Just as you can add statements to a batch for processing, you can remove them with the clear. Batch() method. This method removes all the statements you added with the add. Batch() method. However, you cannot selectively choose which statement to remove. Code Snippet: con. set. Auto. Commit(false); Statement st = con. creat. Satement(); st. add. Batch(“Insert into emploee values(1000, ‘Joe Jones’)”); st. add. Batch(“Insert into departments values(260, ‘Shoe’)”); st. add. Batch(“Insert into emp_dept values(1000, ‘ 260’)”); int [] update. Counts = st. execute. Batch(); con. commit();

• • • Programmatic Updates ( Updatable Resultset) Using the JDBC API, you can modify column values and insert or delete a row instead of SQL statements. To change the address and amount columns in the fourth row, then use the following code fragment rs. absolute(4); rs. update. String(“address”, ” 321 kasten”); rs. update. Float(“amount”, 10101. 0 f); rs. update. Row(); To insert a new row, you have to follow certain steps. Firstly, you move the cursor to a special row, called the insert row to build the new row. Secondly, you send the new row to the database, and finally move the cursor back to the Result. Set. rs. move. To. Insert. Row(); rs. update. Object(1, my. Array); rs. update. Int(2, 3856); rs. udate. String(3, ”Mysteries”); rs. insert. Row(); rs. first(); You move the cursor to the row you want to delete ad call the method delete. Row to delete a row rs. first(); rs. delete. Row();

Data Types and JDBC • Since SQL data types and Java data types are not identical, there needs to be some mechanism for reading and writing data between an application using Java types and a database using SQL types. • JDBC data types are the bridge data types between java data type and underlying database software. • The JDBC driver converts the Java data type to the appropriate JDBC type before sending it to the database. It uses a default mapping for most data types. • These JDBC data types are JDBC driver independent

• The OUT parameter contains the value returned by the stored procedures. The OUT parameter must be registered using the register. Out. Parameter() method, and then later retrieved by using the getxxx() method. • The INOUT parameter is a single parameter that is used to pass information to the stored procedure and retrieve information from the stored procedure. • Each parameter is denoted by “? ” (question mark placeholder for each parameter) , and we will set the value for the parameters by using the setxxx() methods Code snippet: Callable. Statement cstmt = con. prepare. Call( “{call get. Month. Date(? , ? )}); cstmt. set. Int(1, 6); cstmt. set. Int(2, 1999); Result. Set rs = cstmt. execute. Query();

Callable Statement (Calling Stored Procedures) • A Callable. Statement object provides a way to call stored procedures in a standard way for all DBMSs. • A stored procedure is stored in a database and it is a block of code which is identified by a unique name. • The stored procedure is executed by invoking the name of the stored procedure. • The syntax for a stored procedure with no parameters would look like this: { call procedure_name } • Callable. Statement objects are created with the Connection method prepare. Callable. Statement cstmt = con. prepare. Call( "{call procedure. Name}”); • The Callable. Statement object uses 3 types of parameters IN, OUT and INOUT when calling a stored procedure. • The IN parameter contains any data that needs to be passed to the stored procedure and whose value is assigned using the setxxx() method.

• Procedures can also have parameters for returning a resultreferred to as OUT parameters • The syntax for a procedure that returns a result parameter is: {? = call procedure_name[(? , . . . )]} • The placeholder for an OUT parameter is also ? • For each OUT parameter, you must identify the type of the output value as one of the types defined in the java. sql. Types class by calling the register. Out. Parameter() method for the Callable. Statement obejct. cstmt. register. Out. Parameter(2, Types. INTEGER); • Once the OUT parameter has been registered, you can execute the procedure and get the value from the Callable. Statement int value = cstmt. get. Int(2);

Mapping Relational Data onto Java Objects • Because data types in SQL and data types in the Java programming language are not identical, there needs to be some mechanism for transferring data between an application using Java types and a database using SQL types • In order to transfer data between a database and an application written in the Java programming language, the JDBC API provides three sets of methods: Ø Methods on the Result. Set class for retrieving SQL SELECT results as Java types Ø Methods on the Prepared. Statement class for sending Java types as SQL statement parameters Ø Methods on the Callable. Statement class for retrieving SQL OUT parameters as Java types

Mapping SQL Data Types to Java Types • When the programmers use SQL type names to create a table using CREATE TABLE statement then they must take care to use SQL type names that are supported by their target database. • If you want to write portable JDBC programs that can create tables on a variety of different databases, you have two main choices. First, you can use SQL type names such as INTEGER, NUMERIC, or VARCHAR, which are likely to work for all databases. Or second, you can use the Database. Meta. Data. get. Type. Info method to discover which SQL types are actually supported by a given database and select a database-specific SQL type name that matches a given JDBC type. • JDBC defines a standard mapping from the JDBC database types to Java types. For example, a JDBC INTEGER is normally mapped to a Java int.

Basic JDBC Data Types • The following list defines different JDBC data types which are supported by JDBC 4. 0 API and how they are related to standard SQL types and to Java types CHAR, VARCHAR, and LONGVARCHAR • CHAR represents a small, fixed-length character string, VARCHAR represents a small, variable- length character string, and LONGVARCHAR represents a large, variable-length character string. • The SQL CHAR type and SQL VARCHAR type are mapped to JDBC CHAR and JDBC VARCHAR types and supported by all the major databases • There is no consistent SQL mapping for the JDBC LONGVARCHAR type. All the major databases support some kind of very large variable-length string supporting up to at least a gigabyte of data, but the SQL type names vary. • The recommended Java mapping is String and the recommended method is Resultset. get. String

BINARY, VARBINARY, and LONGVARBINARY • BINARY represents a small, fixed-length binary value, VARBINARY represents a small, variable-length binary value, and LONGVARBINARY represents a large, variable-length binary value. • The use of these various BINARY types has not been standardized and support varies considerably among the major databases • BINARY, VARBINARY, and LONGVARBINARY can all be expressed identically as byte arrays in Java. • The method recommended for retrieving BINARY and VARBINARY values is Result. Set. get. Bytes • The method get. Binary. Stream is recommended if a column of type JDBC LONGVARBINARY stores a byte array that is many megabytes long BIT • The JDBC type BIT represents a single bit value that can be zero or one. • The recommended Java mapping for the JDBC BIT type is as a Java boolean.

TINYINT • The JDBC type TINYINT represents an 8 -bit unsigned integer value between 0 and 255. • The corresponding SQL type, TINYINT, is currently supported by only a subset of the major databases. • The recommended Java mapping for the JDBC TINYINT type is as either a Java byte or a Java short. SMALLINT • The JDBC type SMALLINT represents a 16 -bit signed integer value between -32768 and 32767. • The corresponding SQL type, SMALLINT, is defined in SQL-92 and is supported by all the major databases • The recommended Java mapping for the JDBC SMALLINT type is as a Java short. INTEGER • The JDBC type INTEGER represents a a 32 -bit signed integer value between 2147483648 and 2147483647. • The corresponding SQL type, INTEGER, is defined in SQL-92 and is widely supported by all the major databases. • The recommended Java mapping for the INTEGER type is as a Java int.

BIGINT • The JDBC type BIGINT represents a 64 -bit signed integer value between 9223372036854775808 and 9223372036854775807. • The corresponding SQL type BIGINT is a non-standard extension to SQL and not implemented by any of the major databases, and we recommend that its use should be avoided in portable code. • The recommended Java mapping for the BIGINT type is as a Java long. REAL • The JDBC type REAL represents a "single precision" floating point number which supports 7 digits of mantissa. • The corresponding SQL type REAL is defined in SQL-92 and is widely, though not universally, supported by the major databases. • The recommended Java mapping for the REAL type is as a Java float.

DOUBLE • The JDBC type DOUBLE represents a "double precision" floating point number which supports 15 digits of mantissa. • The corresponding SQL type is DOUBLE PRECISION, which is defined in SQL- 92 and is widely supported by the major databases. • The recommended Java mapping for the DOUBLE type is as a Java double. FLOAT • The JDBC type FLOAT is basically equivalent to the JDBC type DOUBLE. • FLOAT represents a "double precision" floating point number that supports 15 digits of mantissa. • The corresponding SQL type FLOAT is defined in SQL-92. • The recommended Java mapping for the FLOAT type is as a Java double. DECIMAL and NUMERIC • The JDBC types DECIMAL and NUMERIC are very similar. • The method recommended for retrieving DECIMAL and NUMERIC values is Result. Set. get. Big. Decimal

DATE, TIME, and TIMESTAMP • There are three JDBC types relating to time: • The JDBC DATE type represents a date consisting of day, month, and year. • The JDBC TIME type represents a time consisting of hours, minutes, and seconds. • The JDBC TIMESTAMP type represents DATE plus TIME plus a nanosecond field. • Because the standard Java class java. util. Date does not match any of these three JDBC date-time types exactly (it includes both DATE and TIME information but has no nanoseconds), JDBC defines three subclasses of java. util. Date to correspond to the SQL types. They are: • java. sql. Date for SQL DATE information. • java. sql. Time for SQL TIME information. java. sql. Timestamp for SQL TIMESTAMP information

Advanced JDBC Data Types • The ISO (International Organization for Standardization) and IEC (the International Electrotechnical Commission) have defined new data types that are commonly referred to as SQL 3 types. Of these new SQL 3 data types, BLOB, CLOB, ARRAY, and REF are predefined types, whereas the SQL structured type and the DISTINCT type are userdefined types (UDTs). BLOB • The JDBC type BLOB represents an SQL 3 BLOB (Binary Large Object). • A JDBC BLOB value is mapped to an instance of the Blob interface in the Java programming language. CLOB • The JDBC type CLOB represents the SQL 3 type CLOB (Character Large Object). • A JDBC CLOB value is mapped to an instance of the Clob interface in the Java programming language. • The BLOB and CLOB data can be retrieved by using the method get. Binary. Stream() or get. Ascii. Stream()

ARRAY • The JDBC type ARRAY represents the SQL 3 type ARRAY. • An ARRAY value is mapped to an instance of the Array interface in the Java programming language. STRUCT • The JDBC type STRUCT represents the SQL 3 structured type. An SQL structured type, which is defined by a user with a CREATE TYPE statement, consists of one or more attributes. These attributes may be any SQL data type, built-in or user-defined. • The standard mapping for the SQL type STRUCT is to a Struct object in the Java programming language. A Struct object contains a value for each attribute of the STRUCT value it represents. DISTINCT • The JDBC type DISTINCT represents the SQL 3 type DISTINCT. • The standard mapping for a DISTINCT type is to the Java type to which the base type of a DISTINCT object would be mapped. For example, a DISTINCT type based on a CHAR would be mapped to a String object, and a DISTINCT type based on an SQL INTEGER would be mapped to an int.

REF • The JDBC type REF represents an SQL 3 type REF<structured type>. An SQL REF references (logically points to) an instance of an SQL structured type and is a unique identifier In the Java programming language, the interface Ref represents an SQL REF. • A REF is reference to SQL structured type and It is persistently stored with the instance it references in a special table on the server. An application can select the REF value from its special table and use it in place of the structured type instance it identifies. JAVA_OBJECT • The JDBC type JAVA_OBJECT, added in the JDBC 2. 0 core API, makes it easier to use objects in the Java programming language as values in a database. • JAVA_OBJECT is simply a type code for an instance of a class defined in the Java programming language that is stored as a database object. • For DBMSs that support them, values of type JAVA_OBJECT are stored in a database table using the method Prepared. Statement. set. Object. They are retrieved with the methods Result. Set. get. Object or Callable. Statement. get. Object and updated with the Result. Set. update. Object method.

Examples of Mapping • This section presents three different scenarios, describing the data mapping and conversion required in each. Simple SQL Statement • In the most common case, a user executes a simple SQL statement and gets back a Result. Set object with the results. • The value returned by the database and stored in a Result. Set column will have a JDBC data type. • A call to a Result. Set. get. XXX method will retrieve that value as a Java data type. • For example, if a Result. Set column contains a JDBC FLOAT value, the method get. Double will retrieve that value as a Java double. (A user who does not know the type of a Result. Set column can get that information by calling the method Result. Set. get. Meta. Data and then invoking the Result. Set. Meta. Data methods get. Column. Type or get. Colu mn. Type. Name. )

• The following code fragment demonstrates getting the column type names for the columns in a result set: String query = "select * from Table 1"; Result. Set rs = stmt. execute. Query(query); Result. Set. Meta. Data rsmd = rs. get. Meta. Data(); int column. Count = rsmd. get. Column. Count(); for (int i = 1; i <= column. Count; i++) { String s = rsmd. get. Column. Type. Name(i); System. out. println ("Column " + i + " is type " + s); }

SQL Statement with IN Parameters • In another possible scenario, the user sends an SQL statement which takes input parameters. • In this case, the user calls the Prepared. Statement. set. XXX methods to assign a value to each input parameter. • For example, Prepared. Statement. set. Long(1, 2345678) will assign the value 2345678 to the first parameter as a Java long. The driver will convert 2345678 to a JDBC BIGINT in order to send it to the database. SQL Statement with INOUT Parameters • In another scenario, a user wants to call a stored procedure, assign values to its INOUT parameters, retrieve values from the results, and retrieve values from the parameters. • In this scenario, the first thing to do is to assign values to the INOUT parameters using Callable. Statement. set. XXX methods. • Each out parameter must be registered with driver by using the method Callable. Statement. register. Out. Parameter, which takes one of the JDBC types defined in the class Types. • A programmer retrieves the results returned to a Result. Set object with Result. Set. get. XXX methods and retrieves the values stored in the output parameters with Callable. Statement. get. XXX methods.

Custom Mapping • The driver will use the custom mapping instead of the standard mapping when it converts a UDT(user defined Types) from a JDBC type to a Java type or vice versa. • All custom mapping is done using the connection’s type map • A type map is an instance of the java. util. Map interface, is associated with every new connection when it is created • a custom mapping is to make an entry of UDT in a type map. • With the methods Result. Set. get. Object and Callable. Statement. get. Object, UDTs are retrieved from the database, and with Prepared. Satement. set. Object UDTs are sent back to the database. • When an application calls a get. Object method to retrieve a UDT, the driver will check to see if the type map associated with the connection has an entry for the UDT • If there is a matching entry, the driver will use that type map to custom map the UDT , other wise the driver will use the standard mapping.

Handling Errors • Any JDBC object encounters an error , it halt the execution and throws an SQLException. Ex: database connection errors , malformed SQL statements and insufficient database privileges etc. • The SQLException class extends the normal java. lang. Exception class and defines some additional methods like get. SQLState()and get. Error. Code() to provide additional information about an error. try { } catch(SQLException e) { System. out. println(“SQL Exception occurred”); System. out. println(e. get. Message()); System. out. println(e. get. SQLState()); System. out. println(e. get. Error. Code()); }

SQL Warning • JDBC classes have the option of generating a SQLWarnings when something is not right • A warning is generated if, for example, you try to connect to a database with the create attribute set to true if the database already exists. Aggregates like sum() also raise a warning if NULL values are encountered during the evaluation. • SQLWarnings are rare, but provide information about the database access warnings • The following objects can receive a warning: – Connection – Statement (also, Prepared. Statement, Callable. Statement) – Result. Set • Warnings are retrieved by using the get. Warnings() method • Call get. Warning to obtain the warning object, and get. Next. Warning (on the warning object) for any additional warnings SQLWarning connection. Warning = connection. get. Warnings(); SQLWarning statement. Warning = statement. get. Warnings(); SQLWarning resultset. Warning = result. Set. get. Warnings();

The Interactive SQL Tool • The interactive SQL tool is a means of entering and executing SQL statements. • It will be a simple front end to the JDBC API. • It will provide a means of entering and executing SQL statements and at the same time display areas for viewing results. • The requirement for the Interactive SQL tool class is: § to enable the user to enter and execute an SQL command § to display the Result. Set from an SQL query § to display the error information where appropriate. • This can be implement as an application with a window based by using swing class JFrame and a swing component called JTable. • The JTable calss is defined in the javax. swing. table package • The Result. Set is generated as table of data values, so JTable

Table. Model Interface • To display a rectangular array of data on the screen, we can use JTable component. • The Table. Model interface declares methods that are used by a JTable object to access the data item to be displayed at each position in the table. • This interface is defined in the javax. swing. table package, along with the JTable class. • The class which encapsulating a Result. Set need to implement this interface. • The Table. Model interface enables a Java Swing application to manage data in a JTable object.

class Results. Model extends Abstract. Table. Model { public void set. Result. Set(Result. Set rs){ } public int get. Column. Count() {} pubic int get. Row. Count() { } public String get. Column. Name(int column){ } public String get. Value. At(int row, int column) { } } public int get. Column. Count() { return columns. length; } pubic int get. Rowcount() { return Row. Data == null ? 0 : Row. Data. size(); }

public String get. Value. At(int row, int column) { return Row. Data. element. At(row)[column]; } public String get. Column. Name(int column) { return columns[column] = = null ? “No Name” : columns[column]; } Vector<String[]> Row. Data = new Vector<String[]>(); // To store the contents of a row as an array of string objects public void set. Resultset(Result. Set rs) { try { Result. Set. Meta. Data rsmt = rs. get. Meta. Data(); int cols = rsmt. get. Column. Count(); columns = new String[cols];

for(int i=0; i<cols; i++) { columns[i]=rsmt. get. Column. Label(i+1); } Row. Data. clear(); while(rs. next()) { rowdata = new String[cols]; for(int i=0; i<cols; i++) { rowdata[i] = rs. get. String(i+1); } Row. Data. add. Element(rowdata); } fire. Table. Changed(null); /* It notifies all listeners for JTable object that the mode has changed, so that JTable object should redraw from the scratch */ } } catch(SQLException e){ System. out. println(e); }