In this video tutorial, you will learn about the basic features of Java JDBC.
Time – 00:00
In this video, I will provide an overview of Java JDBC. We will cover the following topics. What is JDBC? We’ll also discuss the features of JDBC in terms of SQL statements. Then we’ll see the databases that are supported. Next, we will cover the architecture and API of JDBC. Finally, we will walk through the development process for executing a simple query.
Time – 00:32
JDBC allows a Java application to connect to a relational database. The major databases are supported such as Oracle, Microsoft SQL Server, DB2 and many others. The database can be located on the same local machine as in the application. It can also be located in a network in a remote location. The database can also be deplored in the Cloud. No worries, JDBC can still give you to a connection to the database regardless of the location.
Time – 01:08
The main feature of JDBC is that it is a standard API. You develop your application code to the JDBC API and you can connected to various databases. There’s no need to develop code for different databases. That’s the main advantage of JDBC. JDBC is a call level interface that supports ANSI SQL 2003. When you provide SQL statements to the JDBC driver, it simply passes the SQL to the database. You can make use of SQL to perform select, insert, update and delete.
Time – 01:43
You can also build complex SQL series queries with inner and outer joins. You can also make calls to store procedures on the database. As you can see, JDBC does not place any limitations on your SQL development. You are free to leverage ANSI SQL 2003 to meet your application requirements.
Time – 02:04
Here’s a nice graphic of the databases that are supported by JDBC. As you can see, there’s support for Oracle, My SQL, Informix, DB2, SQL Server, Sybase, PostgreS and many others. Note, this is not an exhaustive list. You can simply visit the URL here to see a complete list of databases.
Time – 02:26
The JDBC architecture includes a key component which is the JDBC driver. The JDBC driver provides the critical link between your Java application and the database. The driver converts the standard JDBC API calls to low level calls for your specific database. There are multiple JDBC driver implementations. Your database vendor normally provides the JDBC driver. Simply visit the database vendor’s website and search for the words JDBC driver. The vendors will provide the link to download the driver along with supporting documentation on how to connect to the database.
Time – 03:06
Another key player is the driver manager. The driver manager helps connect and app to the database based on a database connection stream. The current version of JDBC is version 4.0. with this latest version, the driver manager will automatically load JDBC drivers that are in the application’s classpath. No additional work is required by the developer.
Time – 03:36
Most JDBC drivers are now JDBC 4.0 compliant. However, there are Legacy JDBC 3.0 drivers that have to be specifically loaded with Java code. That Java code is class.forname and the actual driver name. The JDBC API is defined in two packages. java.sql and javax.sql. The key classes in the API are driver manager, connection, statement, results set and data source.
Time – 04:16
Great. We’ve covered a lot of the theory, a lot of the key concepts and classes. Now, let’s walk through an actual development process for a simple query. The process is, step one, we’ll get a connection to a database. Then in step two, we’ll actually create a statement object. Then step three, we’ll execute a SQL query. Then finally in step four, we’ll process the results set.
Time – 04:53
Step one, getting a connection to a database. In order to connect to a database, we need to find the database. Remember, it can be local, on the network or in the Cloud. Using a similar approach for finding web resources in the internet, we can make use of a URL. In this case, a JDBC URL. The basic syntax is JDBC:driver protocol:driver connection details. I’ll show you some examples here. Note that I added a color coding so that you can map the different sections of the JDBC URL.
Time – 05:27
If we had a Microsoft SQL Server database and we already had an ODBC DSN set up for it, or data source name, then I can access that Microsoft SQL Server database using JDBC:ODBC:demo DSN. Another case, if we had an Oracle database, then I could connect to the Oracle database using JDBC:oracle:@myserver, that’s our actual database server name:1521 which is the Oracle listener report:demodb which is the actual Oracle siting for that database.
Time – 06:16
Moving forward to MySQL, we can make a connection using the URL of JDBC:mysql://localhost, that’s where the database resides, :3306 which is the MySQL port number which is the … /demodb. That’s the name of the of the database schema that we’re going to connect to.
Time – 06:42
Now we have enough background information to get a connection to the database. In this example, we wanted to find variables on the database URL, the user ID and the password. Then we’ll make a call to the drivermanager.getconnection. Then we’ll pass in the three variables for database URL, user ID and password. If everything is successful, then we will get a valid connection to the database. If there’s a failure then it could possibly throw an exception, SQLException if there’s a bad URL credentials. It can also throw the ClassNotFoundException if the JDBC driver is not in the classpath.
Time – 07:24
Now that we have a valid connection then we can move on to step two creating a statement object. In order to create a statement, you use the myConn.createstatement and that will return a create statement object. We’ll use this later on when we’re ready to execute our SQL query.
Time – 07:43
Step three, we’ll use a statement object to execute a SQL query. In this example, I’ll have mystmt.executequery, select * from employees. This will return a result set object. I’ll assign it to myRS. Remember, we can pass in any SQL query that supports ANSI SQL 2003. In this example, we just kept it really simple with a select * but you can also do your inner joins, outer joins or any complex SQL query that you want. In this example, the SQL string was hard coded which can also pass in a string object that you build dynamically.
Time – 08:27
In step four, we’ll actually process the result set. When you have a result set, the cursor is initially positioned before the first row. We can make use of the method, result.next to move the cursor forward one row. This method also returns true if there are more rows to process. We can use this in a loop to process each row in a results set. Here in the code example, I have while myRS.next and then inside the loop are read data from each row. That’s the basic structure of processing the result set.
Time – 09:00
When we’re inside of the loop, we need to retrieve the data. We can make use of various getter methods for retrieving the data. We can retrieve it by adding the column name or the column index. In this example, we’re going to print out the last name and first name of each row in the result set. I’ll have while, myRs.next, system out print line, myRs.getstring, last name. Then to print out the first name, I’ll have system out print line, myRs.getstring, first name. Note here that last name and first name are columns in the table.
Time – 09:39
We’ve actually covered a lot of information here. We covered some good ground. Let’s just kind of recap on the actual development process. The first step is that you need to get a connection to a database. Then we create a statement object. From there, we can execute our sql query. Then finally, process the result set.
Time – 10:04
In summary, we covered the following topics. We defined what JDBC is about. We discussed the features. We also viewed the databases that are supported. We also examined the JDBC architecture. Then finally, we went to the development process of executing a simple, SQL statement.
Time – 10:28
I’d recommend that you take a look at our follow on tutorials. We go into more details such as how to install a mySQL database. We also show you how to create tables and add sample data. We also discuss the technique of configuring a JDBC driver and a Java IDE. We also have on my suite of examples on selecting, insert, update and delete. Then also, we cover store procedures, transactions and much more.
Time – 11:01
Great. In this session, you received an overview of JDBC. Please subscribe to this channel for more videos. Also, visit luv2code.com to download JDBC source code examples.