Today is Saturday, 17th June 2017

Archive for March, 2014


Java JDBC Tutorial – Part 0: Overview

In this video tutorial, you will learn about the basic features of Java JDBC.

Please subscribe 🙂

Video Transcript

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.

Share


Java JDBC Tutorial – Part 4: Deleting Data from a Database

In this video tutorial, you will learn how to delete data from a database with Java JDBC.

Please subscribe 🙂

Video Transcript

Time – 00:00
Hello. In this video, you will learn how to use Java JDBC to delete data from a database. In regards to Prep Work, you must have a database installed. This demo will use MySQL.

Time: 00:13
You also need to have the database driver configured. Again, we’ll use MySQL. If you need help on how to do this, then please look at my other video “Connecting to a MySQL Database with Java.”

Time – 00:31
In this example, we will use the employees’ table. This table has columns for employee ID, last name, first name, and e-mail address. Let’s switch over to the MySQL tool so we can review the data.

Time – 00:45
As you can see, we have the employees’ table in place. Make note of the entry for David Brown. We will delete this employee from the database. The SQL command that we’ll use to delete an employee is delete from employees where last_name=’Brown’;

Time – 01:09
Let’s move and try development environment and get started. We’ll start with this main program, that’s going to have three variables for the user, password, and the connection information. The first thing we’ll do is we’ll get a connection to the database by saying DriverManager.getConnection, passing in the params. Next, we’ll create the statement using myConn.createStatement. Then we’ll define the SQL query string. In this case,
delete from employees where last_name=’Brown’

Time – 01:43
Now, actually execute that SQL statement by saying executeUpdate and the database will return the number of rows affected. Also, I will print those out. Then finally, I’ll just print out “Delete complete.” so that we know that I’m done.

Time – 02:01
Now, let’s run our application. I’ll right click; I’ll select “Run As”, “Java Application.” Now, let it execute. Now, it will print out the number of Rows affected and Delete complete. Now, let’s move over to our SQL Query tool and very the results.

Time – 02:24
If you note here with the previous entry, we had a listing for a David Brown. I’ll refresh this Query, and now note that David Brown is gone. A Java Application was actually successful in deleting that entry.

Time – 02:37
Great, so in summary, you learned how to use Java JDBC to delete data from a database. If you would like to download the source code, please visit luv2code.com.

Source Code: Driver.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Driver {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://localhost:3306/demo";
String user = "student";
String password = "student";
Connection myConn = null;
Statement myStmt = null;
try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
myStmt = myConn.createStatement();
// 3. Execute SQL query
String sql = "delete from employees where last_name='Brown'";
int rowsAffected = myStmt.executeUpdate(sql);
System.out.println("Rows affected: " + rowsAffected);
System.out.println("Delete complete.");
}
catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
}

MySQL Script for Creating the Database table

File: sql/table-setup.sql

create database if not exists demo;
use demo;
drop table if exists employees;
CREATE TABLE <code>employees</code> (
<code>id</code> int(11) NOT NULL AUTO_INCREMENT,
<code>last_name</code> varchar(64) DEFAULT NULL,
<code>first_name</code> varchar(64) DEFAULT NULL,
<code>email</code> varchar(64) DEFAULT NULL,
PRIMARY KEY (<code>id</code>)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
INSERT INTO <code>employees</code> (<code>id</code>,<code>last_name</code>,<code>first_name</code>,<code>email</code>) VALUES (1,'Doe','John','john.doe@foo.com');
INSERT INTO <code>employees</code> (<code>id</code>,<code>last_name</code>,<code>first_name</code>,<code>email</code>) VALUES (2,'Public','Mary','mary.public@foo.com');
INSERT INTO <code>employees</code> (<code>id</code>,<code>last_name</code>,<code>first_name</code>,<code>email</code>) VALUES (3,'Queue','Susan','susan.queue@foo.com');

Download Source Code: jdbcdemo-delete.zip

Share


Java JDBC Tutorial – Part 3: Updating Data in a Database

In this video tutorial, you will learn how to update data in a database with Java JDBC.

Please subscribe 🙂

Video Transcript
Time – 00:00
Hello. In this video, you will learn how to use Java JDBC to update data in a database. In regards to prep work, you must have a database installed. This demo will use MYSQL. You also need to have the database driver configured. Again, we’ll use MYSQL. If you need help on how to do this, please look at my other video, Connecting To MYSQL Databases with Java.

Time – 00:30
In this example, we will use the Employees table. This table has columns for employee ID, last name, first name and email address. Let’s switch over to the MYSQL tool, so we can take a look at some of the data.

Time – 00:50
Here’s our sample table that we have with sample data already in place. What I’d like for you to do is make note of David Brown’s email address. He is currently listed as davidbrown@foo.com. We’re going to change this email address to make use of luv2code.com.

Time – 01:13
His new email address is demo@luv2code.com. The following sql can perform this update. We will use this sql update statement in a Java application.

Time – 01:26
This slide outlines our Java development process. First, we will get a connection to the database. Then we will create a statement. Finally, we will execute the sql update statement. Let’s move onto our development environment and get started.

Time – 01:46
We’re going to start with a simple Java program. The program is going to start out with the url, the connection to the database, the user ID and password.

Time – 01:56
The first thing that we’ll do is we will get a connection to the database. Then we will create a statement. Then we’ll actually execute the sql query. This is the same query that we had from the previous slide.

Time – 02:12
Then we’ll actually do an execute update. This will actually send the query to the database and perform the update. Finally, just to give us some debugging information, we’ll have a print-line statement to say update complete.

Time – 02:27
Let’s go ahead and run this application. Run as Java application. It says update complete. Great. Let’s go to our SQL tool to verify the results.

Time – 02:42
In the SQL tool, I just simply do a refresh and note the new email address for David Brown. It’s now demo@luv2code.com. That works out, and we’re actually successful with updating data using Java JDBC.

Time – 03:00
This wraps up the presentation. If you’d like to download the source code, please visit the website luv2code.com.

Source Code: Driver.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Driver {
public static void main(String[] args) throws SQLException {
String url = "jdbc:mysql://localhost:3306/demo";
String user = "student";
String password = "student";
Connection myConn = null;
Statement myStmt = null;
try {
// 1. Get a connection to database
myConn = DriverManager.getConnection(url, user, password);
// 2. Create a statement
myStmt = myConn.createStatement();
// 3. Execute SQL query
String sql = "update employees set email='demo@luv2code.com' where id=9";
int rowsAffected = myStmt.executeUpdate(sql);
System.out.println("Rows affected: " + rowsAffected);
System.out.println("Update complete.");
}
catch (Exception exc) {
exc.printStackTrace();
}
finally {
if (myStmt != null) {
myStmt.close();
}
if (myConn != null) {
myConn.close();
}
}
}
}

Download Source Code: jdbcdemo-update.zip

Share