JDBC includes the java.sql and javax.sql packages, which has the necessary interfaces and classes you'll need for developing JDBC applications. In JDK 6 onwards, the jdbc classes are bundled with rt.jar itself. JDBC Driver is a mechanism to connect to a database using JDBC protocol. Every database comes with a jdbc driver. Quiz : JDBC Driver is delivered from the database vendor. Types Of JDBC Drivers: * Type 1 - drivers that implement the JDBC API as a mapping to another data access API, such as ODBC. Drivers of this type are generally dependent on a native library, which limits their portability. The JDBC-ODBC Bridge driver is an example of a Type 1 driver. Note: Incomplete without odbc driver. * Type 2 - drivers that are written partly in the Java programming language and partly in native code. These drivers use a native client library specific to the data source to which they connect. Again, because of the native code, their portability is limited. Note: Uses JNI interface * Type 3 - drivers that use a pure Java client and communicate with a middleware server using a database-independent protocol. The middleware server then communicates the client?s requests to the data source. Note: Pure java. Incomplete without middleware. * Type 4 - drivers that are pure Java and implement the network protocol for a specific data source. The client connects directly to the data source. Note: Pure java driver. This is great for a java program to use. Quiz: Type 4 database driver uses pure java. JavaDB comes with 2 Type4 drivers: Embedded Driver and Network Client Driver. The derby.jar contains one class which implements java.sql.Driver -- that is driver class. In the latest OS, you will find these files here in /opt/SUNWjavadb/lib : derby.jar - Derby engine (jdbc embedded client driver included) derbyclient.jar - jdbc network client driver derbynet.jar - Derby network server derbyrun.jar - Executable used to start derby tools. derbytools.jar - includes ij, dblook (schema dump), import/export tools export JAVA_HOME=/usr/jdk/latest export DERBY_HOME=/opt/SUNWjavadb Setting DERBY_HOME is a must! Set class path for embedded mode: (or do . EmbeddedCP to source that script) export CLASSPATH=$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbytools.jar:. Set class path for client server mode: export CLASSPATH=$DERBY_HOME/lib/derbyclient.jar:$DERBY_HOME/lib/derbytools.jar:. Execute this or ./sysinfo java org.apache.derby.tools.sysinfo The output says that the derby is jdbc 4.0 compliant! set path to include /opt/SUNWjavadb/bin; because it will create derby.log in curdir. java org.apache.derby.tools.ij (or ./ij) connect 'jdbc:derby:MyDbTest;create=true'; create table movie(name varchar(30), actor varchar(30)); insert into movie values ('matrix', 'keanu reeves'); The database is created as dir in curdir! And derby.log created in curdir! To avoid this do: java org.apache.derby.tools.ij -Dderby.system.home=/home/thava/data Note: ij myfile.sql; run 'myfile.sql'; are supported. Program: import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; /* Embedded Driver demo */ class Derbydemo { public static String driver = "org.apache.derby.jdbc.EmbeddedDriver"; public static String protocol = "jdbc:derby:myDB;create=true;user=myuser;password=mypasswd"; public static void main(String[] args) { try { Class.forName(driver).newInstance(); Connection conn = DriverManager.getConnection(protocol); System.out.println("Created Connection ... "); conn.setAutoCommit(false); Statement s = conn.createStatement(); s.execute("create table movie (name varchar(30), actor varchar(30))"); System.out.println("Created Table ... "); s.execute("insert into movie values ('vanilla sky', 'tom cruise')"); System.out.println("Inserted tuple..."); ResultSet rs = s.executeQuery("select * from movie"); while (rs.next()) System.out.println("Movie Name: " + rs.getString(1) + " ; Actor: " + rs.getString(2)); } catch (Throwable e) { System.out.println("exception thrown:"); e.printStackTrace(); } } Setting up server : Set env: export CLASSPATH=$DERBY_HOME/lib/derby.jar:$DERBY_HOME/lib/derbytools.jar:$DERBY_HOM/lib/derbynet.jar:. OR . /opt/SUNWhadb/bin/setNetworkServerCP run server /opt/SUNWhadb/bin/startNetworkServer OR java org.apache.derby.drda.NetworkServerControl start [-h ] [-p ] There are two ways to start the Network Server from a Java application. You can include the following line in the derby.properties file: [ derby.properties is looked up in curdir or $(derby.system.home)/ dir. ] derby.drda.startNetworkServer=true This starts the server on the default port, 1527, listening on localhost (all interfaces). To specify a different port: derby.drda.portNumber=1110 derby.drda.host=myhost You can also specify the startNetworkServer and portNumber properties by using a Java command: java -Dderby.drda.startNetworkServer=true -Dderby.drda.portNumber=1110 -Dderby.drda.host=myhost yourApp Or you can directly use the NetworkServerControl API to start the Network Server from a separate thread within a Java application: NetworkServerControl server = new NetworkServerControl(); server.start (null); Stop network server: stopNetworkServer.ksh [-h ] [-p ] java org.apache.derby.drda.NetworkServerControl shutdown [-h ][-p ] Remember: Class.forName(driver).newInstance(); Connection conn = DriverManager.getConnection(protocol); Note: DriverManager() also loads all drivers specified in $(jdbc.drivers) property. Scrollable updatable result set: Connection con = DriverManager.getConnection("jdbc:mySubprotocol:mySubName"); Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet uprs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES"); COF_NAME PRICE ------------------ ----- Colombian 7.99 French_Roast 8.99 Espresso 9.99 Colombian_Decaf 8.99 French_Roast_Decaf 9.99 uprs.last(); uprs.updateFloat("PRICE", 10.99f); uprs.updateRow(); PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? "); updateSales.setInt(1, 75); updateSales.setString(2, "Colombian"); updateSales.executeUpdate(): If you set autocommit=false, commit explicitly: con.setAutoCommit(false); ..... con.commit(); Supporting Save points : Statement stmt = conn.createStatement(); int rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) VALUES " + "(?FIRST?)"); // set savepoint Savepoint svpt1 = conn.setSavepoint("SAVEPOINT_1"); rows = stmt.executeUpdate("INSERT INTO TAB1 (COL1) " + "VALUES (?SECOND?)"); ... conn.rollback(svpt1); ... conn.commit(); Note: Connection.releaseSavepoint() is supported. Catching an SQLException tells you that something is wrong, but it does not tell you what was or was not committed. Since you cannot count on the fact that nothing was committed, calling the method rollback is the only way to be sure. Creating stored procedure is like any other statement: Statement stmt = con.createStatement(); stmt.executeUpdate("create procedure SHOW_SUPPLIERS ... "); CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}"); ResultSet rs = cs.executeQuery(); try { // Code that could generate an exception goes here. // If an exception is generated, the catch block below // will print out information about it. } catch(SQLException ex) { System.out.println("\n--- SQLException caught ---\n"); while (ex != null) { // Note: while loop!!! System.out.println("Message: " + ex.getMessage ()); System.out.println("SQLState: " + ex.getSQLState ()); System.out.println("ErrorCode: " // Vendor specific + ex.getErrorCode ()); ex = ex.getNextException(); System.out.println(""); } } SQLWarning warn = rs.getWarnings(); if (warn != null) { System.out.println("\n---Warning---\n"); while (warn != null) { System.out.println("Message: " + warn.getMessage()); System.out.println("SQLState: " + warn.getSQLState()); System.out.print("Vendor error code: "); System.out.println(warn.getErrorCode()); System.out.println(""); warn = warn.getNextWarning(); } } ================================================================ The Ref interface does not provide methods for dereferencing. Instead, a Ref can be passed as an input parameter to an appropriate SQL statement that fetches the object that it references. See the separate JDBC API documentation for details. Storing refs The PreparedStatement.setRef() method may be called to pass a Ref as an input parameter to a prepared statement. Wow! you can get the pointer to struct type and then later compare with that ref only. The REF is there in server! CREATE TYPE RESIDENCE ( DOOR NUMERIC(6), STREET VARCHAR(100), CITY VARCHAR(50), OCCUPANT REF(PERSON) ); .... INSERT INTO HOMES (DOOR, STREET, CITY, OCCUPANT) VALUES ( 1234, 'CARTOON LANE', 'LOS ANGELES', (SELECT OID FROM PEOPLE P WHERE P.NAME.FIRST = 'DAFFY') ); Note: The REF is directly inserted into the table! When you want to maeterialize the REF, you can always do something like "select * from table t where col = ?" and pass your ref to that query! Struct struct = (Struct)rs.getObject(1); retrieves a Struct value from the first column of the current row of result set rs. The Struct interface contains methods for retrieving the attributes of a structured type as an array of java.lang.Object values. JDO : Java Data Objects; Api for Java model abstraction of persistence, The original JDO 1.0 is JSR 12; current JDO 2 is JSR 243; Beginning with JDO 2.0, the development of the API and the Technology Compatibility Kit (TCK) takes place within the Apache JDO open-source project. Object persistence is defined in the external XML metafiles, which may have vendor-specific extensions. Now annotations are also supported (similar to Java Persistence API). JDO vendors provide developers with enhancers, which modify compiled Java class files so they can be transparently persisted. Vendor implementations are not as popular as JavaPersistenceAPI-- Many JDO implementations now offer JPI interface also for the JDO objects. JDO supports different datastores like RDBMS, XML, ObjectDB, webservices, etc. [ however the java persistence API is biased towards RDBMS datastore. ] Alternatives to JDO : direct file I/O, serialization, JDBC, Enterprise JavaBeans (EJB), Bean-Managed Persistence (BMP) or Container-Managed Persistence (CMP) entity beans, and the Java Persistence API. Java Persistence API : The Java Persistence API provides a POJO persistence model for object-relational mapping. JSR 220. It uses annotation to relate 2 classes. You need to create entity manager: em.persist(obj) will automatically persist the object for you. You can specify "foreign key" kind of relations and deletes can be cascaded. etc. Object/relational metadata can be specified directly in the entity class file by using annotations, or in a separate XML descriptor file distributed with the application. The Java Persistence Query Language (JPQL) is used to make queries against entities stored in a relational database. Queries resemble SQL queries in syntax, but operate against entity objects rather than directly with database tables . projects like Hibernate and Open-Source Version TopLink Essentials are now implementations of the Java Persistence API. ================================================================= J2EE connector : A resource adapter provides connectivity to an external system from the application server. The resource adapter?s functionality is similar to that provided by the JDBC interfaces used in the J2EE platform to establish a connection with a data source. These interfaces, which the Connector specification refers to as the service provider interface (SPI), are the following: * DataSource * ConnectionPoolDataSource * XADataSource Additionally, the Connector Architecture defines a packaging format to allow a resource adapter to be deployed into a J2EE compliant application server. --- Basically now, the database is a resource, jdbc driver is a "resource adapter". Appendix A : rt.jar contents for jdbc in java 1.4 : java/sql/Array.class java/sql/BatchUpdateException.class java/sql/Blob.class java/sql/CallableStatement.class java/sql/Clob.class java/sql/Connection.class java/sql/DataTruncation.class java/sql/DatabaseMetaData.class java/sql/Date.class java/sql/Driver.class java/sql/DriverInfo.class java/sql/DriverManager.class java/sql/DriverPropertyInfo.class java/sql/ParameterMetaData.class java/sql/PreparedStatement.class java/sql/Ref.class java/sql/ResultSet.class java/sql/ResultSetMetaData.class java/sql/SQLData.class java/sql/SQLException.class java/sql/SQLInput.class java/sql/SQLOutput.class java/sql/SQLPermission.class java/sql/SQLWarning.class java/sql/Savepoint.class java/sql/Statement.class java/sql/Struct.class java/sql/Time.class java/sql/Timestamp.class java/sql/Types.class javax/sql/ConnectionEvent.class javax/sql/ConnectionEventListener.class javax/sql/ConnectionPoolDataSource.class javax/sql/DataSource.class javax/sql/PooledConnection.class javax/sql/RowSet.class javax/sql/RowSetEvent.class javax/sql/RowSetInternal.class javax/sql/RowSetListener.class javax/sql/RowSetMetaData.class javax/sql/RowSetReader.class javax/sql/RowSetWriter.class javax/sql/XAConnection.class javax/sql/XADataSource.class rt.jar contents for jdbc in java 1.5 adds javax.sql.rowset definitions : javax/sql/rowset/BaseRowSet.class javax/sql/rowset/CachedRowSet.class javax/sql/rowset/FilteredRowSet.class javax/sql/rowset/JdbcRowSet.class javax/sql/rowset/JoinRowSet.class javax/sql/rowset/Joinable.class javax/sql/rowset/Predicate.class javax/sql/rowset/RowSetMetaDataImpl$1.class javax/sql/rowset/RowSetMetaDataImpl$ColInfo.class javax/sql/rowset/RowSetMetaDataImpl.class javax/sql/rowset/RowSetWarning.class javax/sql/rowset/WebRowSet.class javax/sql/rowset/rowset.properties javax/sql/rowset/serial/SQLInputImpl.class javax/sql/rowset/serial/SQLOutputImpl.class javax/sql/rowset/serial/SerialArray$InternalResultSetImpl.class javax/sql/rowset/serial/SerialArray.class javax/sql/rowset/serial/SerialBlob.class javax/sql/rowset/serial/SerialClob.class javax/sql/rowset/serial/SerialDatalink.class javax/sql/rowset/serial/SerialException.class javax/sql/rowset/serial/SerialJavaObject.class javax/sql/rowset/serial/SerialRef.class javax/sql/rowset/serial/SerialStruct.class javax/sql/rowset/spi/ProviderImpl.class javax/sql/rowset/spi/SyncFactory.class javax/sql/rowset/spi/SyncFactoryException.class javax/sql/rowset/spi/SyncProvider.class javax/sql/rowset/spi/SyncProviderException.class javax/sql/rowset/spi/SyncResolver.class javax/sql/rowset/spi/TransactionalWriter.class javax/sql/rowset/spi/XmlReader.class javax/sql/rowset/spi/XmlWriter.class rt.jar of jdk6 adds following definitions: java/sql/ClientInfoStatus.class java/sql/DriverService.class java/sql/NClob.class java/sql/RowId.class java/sql/RowIdLifetime.class java/sql/SQLClientInfoException.class java/sql/SQLDataException.class java/sql/SQLException$1.class java/sql/SQLFeatureNotSupportedException.class java/sql/SQLIntegrityConstraintViolationException.class java/sql/SQLInvalidAuthorizationSpecException.class java/sql/SQLNonTransientConnectionException.class java/sql/SQLNonTransientException.class java/sql/SQLRecoverableException.class java/sql/SQLSyntaxErrorException.class java/sql/SQLTimeoutException.class java/sql/SQLTransactionRollbackException.class java/sql/SQLTransientConnectionException.class java/sql/SQLTransientException.class java/sql/SQLXML.class java/sql/Wrapper.class javax/sql/CommonDataSource.class javax/sql/StatementEvent.class javax/sql/StatementEventListener.class