Okay, welcome back. In this lecture, we'll explore JDBC in detail and how it can and should be used to access data sources. In particular, we'll review the following topics. Connecting to a database using JDBC, executing a statement against a database that returns a ResultSet, setting up and working with PreparedStatements, extracting multiple rows of data from a ResultSet where each column value is represented as a string, and inserting and updating and deleting rows in a table.

At the conclusion of this lecture, you should be able to perform each of the items listed here. Take a moment to rate yourself on each of these on a scale of one through to five. At the conclusion of this lecture, these objectives will be reviewed. You should rate yourself again to see how much benefit you've received.

So what is JDBC? Well even though JDBC is often thought of as an acronym for Java Database Connectivity, in reality, JDBC is a trademarked name all by itself. The APIs that are defined by JDBC can be used to access a wide variety of data sources, even though most applications are only using JDBC to communicate with a DBMS. The API can be used to communicate with any compliant data source, from flat files to relational database management systems. The API defines full support for creating database connections, sending SQL statements, invoking store procedures, handling transactions and naturally, dealing with the data that might be returned as a result of a query against a data source. Database vendors implement these interfaces in JDBC drivers specific to a particular database management system, DBMS in order to become JDBC compliant.

One of the key technologies or languages used when working with JDBC is the Structured Query Language, or SQL. A relational database consists of a series of tables. There are no fixed relationships between tables. All relationships can be defined dynamically at run time. Thus the database is ideal for situations where requirements are continually changing or where ad hoc queries need to be supported.

The usual method for accessing data is via the industry-standard language called SQL. The overall process of connecting to a database and executing a SQL query consists of the following summarized steps. You input the JDBC packages. You then load in register a JDBC driver. You open a connection to the database. Then create a statement object to perform a query. Execute the statement object and return a query result. Process the ResultSet. Close the ResultSet and statement objects. And then finally, close the connection.

Now, in order for a Java application to make a connection to a database system, the following configuration requirements should be provided. A JDBC URL, a JDBC driver, and a list of database-specific properties and or database-specific authentication credentials. The JDBC URL and driver are both mandatory, as they provide the location of the database and the mechanism or implementation to actually connect.

Starting with Java 6 and JDBC 4.0 onwards, the JDBC driver no longer needs to be explicitly declared, as now the DriverManager will attempt to discover and load it by searching for a suitable driver from the classpath. For example, if you have placed a java file containing the JDBC driver for MySQL such as the MySQL connector for Java in the classpath, the DriverManager will automatically load the com.MySQL.jdbc.driver class which it will find in the Java file.

The java.SQL.DriverManager provides a method to configure in the right JDBC driver at runtime. This can be done by using the system property JDBC.drivers, for example, by specifying the D flag on the Java command when launching a program, or by explicitly loading using a Class.forName method. As mentioned in the previous slide, explicitly adding the driver via either of these methods explained here is no longer necessary if you are using the JDBC 4.0 driver onwards.

When connecting to a database, the application must provide information about both the type and the location of the database. This is often referred to as the database URL. In most cases, databases require a username and password in order to establish a connection. When a connection is requested from the DriverManager, the DriverManager will search through all the available drivers in order to find a driver that supports the URL provided.

Each driver defines a specific format and syntax of URL that is to be used. A java.SQL.statement object is used for executing a non-parametrized SQL statement and obtaining results produced by it in the form of a ResultSet. The statement object provides several important methods, some of which are: executeQuery, used for slick statements and returns a ResultSet, executeUpdate used for update operations and returns a number of rows affected, getWarnings returns warnings sent by the server due to the last statement, and close() closes all resources.

Java.SQL.PreparedStatement. If the same SQL statement is executed multiple times, it is more efficient to use a PreparedStatement. A SQL statement with or without input parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute the statement multiple times whether or not the database or its driver supports free compilation is largely transparent to programmers. Rather than taking a SQL string as an execution argument, the PreparedStatement is constructed with the execution statement.

Generally, dynamic SQL is used, meaning that question marks are embedded in the statement string where their values can be dynamically changed at run time. This allows a database statement to be compiled once and then be used repeatedly, changing only the parameters that control the result of its execution. Note that, unlike Java indexes, SQL column indices start at one, not zero, so be careful.

Java.SQL.ResultSet. Only one ResultSet per statement can be opened at any point in time. Therefore, if the reading of one ResultSet is interleaved with the reading of another, each must have been generated by different statements. All statement execute methods implicitly close a statement's current ResultSet if an open one exists.

There are two get methods for each data type. One that takes a column index and one that takes a column name. For example, to get a string object from the first column of our employee table, we can either use getString one or getString employee name. Using a column number is slightly more efficient but makes the code less readable. A connection must be available and still valid to use a ResultSet.

The Statement execute query method executes a SQL statement and returns a single ResultSet. Alternatively, executeUpdate can be used to execute a SQL insert update or delete statement. In addition, SQL statements that return nothing, such as SQL DDL statements, can be executed this way.

Transaction control. By default, the transaction mode for a connection is generally with AutoCommit enabled. This means that each statement runs in a separate transaction, in that the transaction is implicitly committed prior to the call returning. In many cases, this behavior is not acceptable. This option can be turned off on the connection. With AutoCommit disabled, a new transaction is created when a statement is executed, but only if there is not a transaction already in place on the connection.

Additional statements can be executed and all will be performed in the same existing transaction. Once the application decides that it is time to commit the work that has been performed across the multiple statements, it calls commit on the connection. JDBC provides reasonable type mappings from Java for the common SQL data types. There is also a corresponding type mapping from Java types to SQL types.

Now, not all databases may support all the SQL types. For example, BIGINT is not in the SQL 92 standard. java.SQL.Time and java.SQL.Date are thin wrappers for the java.util.Date class. The constructor of the java.util.Date class will not accept either a java.SQL.Time or java.SQL.Date object. For more information on mapping SQL data types in Java, please refer to the JDBC specification, section eight.

Okay, before we complete this lecture, pause this video and consider the following questions to test yourself on the content that we have just reviewed. Write down your answers and then resume the video to compare.

Okay, the answers to the above questions are. One, a connector class. Two, a JDBC driver is used to connect to a database by providing the JDBC URL a username and password. Three, from the connection, a statement is created which can be used to issue commands. Four, metadata can be retrieved on both the database and our returned ResultSet which will provide detailed information regarding structure and content.