The following are the objectives of this module:

1. Why do we need JDBC
2. What is a driver?
3. SQL basics – creating a database – select, insert, update and delete queries in MySQL
4. What is a Connection object
5. What is a JDBC url
6. What is a PreparedStatement
7. How to execute a PreparedStatement – Why should we use prepared statement

JDBC stands for Java Database connectivity.

From the early times of programming, data has always been persisted to a database. The early databases are stored as binary or text data in the file systems. Nowadays RDBMS (Relational Database Management System) is the proven and widely accepted technology in the world today to persist data. Why is that so?

RDBMS guarantees the integrity of the data at any point of time. RDBMS is a proven technology built on rock-solid principles of programming.

RDBMS establish relationship between tables and the data, and the integrity and constraints can never be violated. This data storage format and integrity guarantee has made RDBMS the preferred choice the world over, for data storage.

Also it would serve to note at this point, that in an RDBMS, data is stored is the form of rows inside a table.

The natural language of the RDBMS is SQL ( called Structured Query Language). RDBMS understands only SQL and not any other language ( perhaps with the exception of C language, that is understood by some RDBMS )

So the typical usage is this:

Java Program <—> JDBC <— > RDMBS

JDBC forms the bridge between your Java code and the SQL code, executing the SQL code in the program at the RDBMS and collecting the results in the Java code.

But we have different RDBMS vendors is the market today, the leading RDBMS vendors being :

1.Oracle Corporation
2.Microsoft – with SQL server
3.MYSQL (now part of Oracle Corporation)
4.IBM – with DB2

Remember Java’s promise to the programming world is “Write once, Run any where”

How do we gaurantee that our Java code written against and tested on one RDBMS say MySQL will run the same against another database, say MSSQL?

To overcome this problem of multiple vendor RDBMS implementations, Sun Micro system, the creators of the Java programming language, came out with a specification, called the JDBC specification.

The different RDBMS vendors were supposed to implement the interfaces defined by the JDBC specification, and come out with as appropriate driver to connect to their data base. And almost all the major RDBMS vendors support JDBC.

If we need to connect to an Oracle database, you would need an Oracle JDBC driver.

If we need to connect to a MySQL database, you would need a MySQL driver. Almost all the drivers for the different RDBMS are available as a free download.

In order to interact with an appropriate RDBMS, that driver needs to be in the classpath. No other configuration changes need to be done! That is the beautiful abstraction that JDBC provides us over the proprietary RDBMS vendors.

Setting up a MySQL database server:

Please refer to the appendix on how to setup a MySQL database server. You will also find information about basic SQL commands that will help you do the following:

it is important that we understand the following SQL commands and queries.

1. Creating a database
2. Creating a table
3. Inserting a record into a table
4. Fetching rows from a table
5. Updating values is a table
6. Removing data from a table

To install the MySQL database server – You can download it for free from http://dev.mysql.com. If this URL is changed, you could google the words “Download MySQL database server”, and choose your version depending on your platform (Windows or Linux or Mac OS X), and install it on your computer.

To create a database in MySQL, use the following SQL syntax

CREATE DATABASE <<database name>>;

For example:

CREATE DATABASE ONLINESHOP_DB;
	

Using the Database

Now, we need to be aware that our database server will house many such databases like the one we created just now. To select the right database we want to work with, we have to use the command

	USE <<database name>>

For example:

		USE ONLINESHOP_DB;

Creating a table

We create a table using the CREATE table syntax. A table consists of fields that can belong to different data types.

	CREATE TABLE <<table name>>
	(
		<<column name>> <<data type>> <<constraint>>

	);

	CREATE TABLE ADDRESS (
		ID BIGINT NOT NULL auto_increment PRIMARY KEY,
		ADDRESS1 VARCHAR(256),
		CITY VARCHAR(256),
		STATE VARCHAR(256),
		COUNTRY VARCHAR(256),
		PINCODE INT
	);

Here we specify the ID column to be of type Big Integer with the NOT NULL constraint. Additionally the ID column is also an auto_increment column meaning that the MySQL database server will auto-generate values for this column and need not be specified by the person storing value into this table.

We have also mentioned that the ID column is the Primary Key.

The following are the data-types that we will use while creating columns

Specifying relationships between tables:

In a normal business scenario, a Customer generally has an address.

Consider the scenario when we want to data-model a Customer table having a relationship to the address table we just created above.

CREATE TABLE CUSTOMER  (
	ID BIGINT NOT NULL auto_increment PRIMARY KEY, 
	NAME VARCHAR(256),
	ADDRESS_ID BIGINT NOT NULL REFERENCES ADDRESS(ID)
);

Here we specify that the ADDRESS_ID column in the CUSTOMER table references the ID column of the ADDRESS table.

Thus ADDRESS_ID is a foreign key that points to the ADDRESS table.

Inserting Records in a table

The INSERT command is used to insert data into a table The general format of the insert statement is as given below:

	INSERT INTO table_name (column1, column2, column3,...) 
		VALUES (value1, value2, value3,...)

For Example:

	
	INSERT INTO ADDRESS( ADDRESS1, CITY, STATE, PINCODE)
	values ('25 Town Hall Drive','Hamden', 'Connecticut', '05643');

Updating Records in a table

The UPDATE command is used to update data in a given table. The general format of the update statement is as given below:

	
	UPDATE table_name SET column1=value, column2=value2,...
	WHERE some_column=some_value 

Note: The WHERE clause specifies which record or records should be updated. If not specified without the WHERE clause, all the rows in the table will be updated for the given column values

Deleting Records from a table

The DELETE command is used to delete data from a given table. The general format of the delete statement is as follows:

DELETE FROM table_name WHERE column1 = value and column2 = value

Fetching data from a table

We use the SELECT command to fetch data from a table

For Example:

	
	SELECT * FROM ADDRESS;
	SELECT ADDRESS1, CITY, STATE, PINCODE FROM ADDRESS WHERE ID = 1;

The WHERE clause in the SELECT statement is used to restrict the data.

JOINING tables

Let us say we want to join data from two or more tables. This is accomplished by joining the foreign keys to the respective tables and restricting the data based on the criteria in the WHERE clause

Let us say, we want to fetch the Customer’s name and the Address data. Remember they are in two different tables. So let us write an SQL Query that will fetch the data from the two tables

	SELECT cust.NAME, addr.ADDRESS1, addr.CITY, addr.STATE, addr.PINCODE
	FROM	
		CUSTOMER cust,
		ADDRESS addr
	WHERE
		addr.ID = cust.ADDRESS_ID
	AND cust.ID = 1;

This query joins the CUSTOMER table with the ADDRESS table and fetches that ADDRESS record for the customer whose ID = 1

MySQL database server supports the following types of JOIN:

	SELECT column_name(s)
	FROM table_name1
		INNER JOIN table_name2
	ON table_name1.column_name=table_name2.column_name 
			

	SELECT column_name(s)
	FROM table_name1
	LEFT JOIN table_name2
	ON table_name1.column_name=table_name2.column_name  
			

	SELECT column_name(s)
	FROM table_name1
	RIGHT JOIN table_name2
	ON table_name1.column_name=table_name2.column_name 
			

Types of relationships

The following are the types of relationships in a Data model

1. One to One
2. One to Many
3. Many to Many

A One-to-One mapping is the type of relationship that exists between the Customer and her Address. Each Customer (generally) has one and only one address, so we model the relationship by declaring a FOREIGN KEY to the ADDRESS table from the CUSTOMER table

A One to Many relationship exists when one Entity is related to multiple other types of a Second entity.

Take the example of a Product belonging to a Category. It is possible for one product to belong to more than one Category

For example, a product “iPhone” can belong to the Categories like “Electronic Items”, “Gadgets”, “Mobiles”, “Personal Entertainment” etc.

To model this kind of One-to-Many relationship we use a mapping table as follows:

CREATE TABLE CATEGORY (
	ID BIGINT NOT NULL auto_increment PRIMARY KEY,
	…

);

CREATE TABLE PRODUCT (
	ID BIGINT NOT NULL auto_increment PRIMARY KEY,
	…

);

CREATE TABLE CATEGORY2PRODUCT (
	ID BIGINT NOT NULL IDENTITY PRIMARY KEY,
	CATEGORY_ID BIGINT NOT NULL REFERENCES CATEGORY(ID),
	PRODUCT_ID BIGINT NOT NULL REFERENCES PRODUCT(ID)
);
	

Please note: We employ the same strategy as in the One-to-many model to the Many-to-Many relationships also using the mapping table.

The Connection object

A Connection object represents a connection to the database.

Connection conn = DriverManager.getConnection(
                  "jdbc:mysql://localhost:3306/studentdb");

The Connection object takes a JDBC url as its input argument. The following are the constituents of the JDBC url

1. The protocol (which is jdbc

)

2. The vendor name (which is mysql)
3. The server name (DNS name or IP address – we have chosen localhost here but it could be any IP or DNS address)
4. The port on the server (3306 is the default MySQL port)
5. The database name (the database name on the server – remember the RDBMS server can have multiple databases)

Now the Java code has all the information that is need to connect to the database. It is now possible for Java code to use the appropriate driver and connect to the database with this information in the JDBC url

It would serve to remind that the JDBC connection is an expensive resource. It needs to be released as soon as the work is done. It is always a good practice to close the connection, in the finally method.

	Connection conn = null;
	try {
		conn = DriverManager.getConnection(
			"jdbc:mysql://localhost:3306/studentdb");
			
	} catch (Exception e) {
		e.printStackTrace();
	} finally {
		if (conn != null) conn.close(); 
	}

The Prepared statement

We know that as for as the Java code is concerned, the SQL statement is just a String value.

This String has to be compiled into native SQL at the RDBMS layer. The PreparedStatement does the work of translating the String into native SQL and executing it at the database layer.

The process of compiling the String into SQL code is an expensive operation.

So Java provides us with an opportunity to optimize on this process, by reusing the compiled nature SQL multiple times by just replacing the values.

That is why the prepared statement has place holders in the form of question marks (?) to substitute data, thereby reusing the compiled SQL code

String varName = “Tom Cruise”;

try {
	 conn = DriverManager.getConnection(
	 	"jdbc:mysql://localhost:3306/studentdb");
		
	 String sqlQuery = "select course from users where name=?";

	 ps = conn.prepareStatement(sqlQuery);
	 ps.setString(1, varName);

	 rs = ps.executeQuery();

	 if (rs.next()) {
	 	String courseName = rs.getString("course");
	 	System.out.println("Course is " + courseName);
	 }

} catch (Exception e) {

	//-- exception handling code comes here

} finally {

	//-- close resultset, preparedStatement and connection
}

The takeaway point from this discussion, is that we should use PreparedStatements wherever we can in our code. This will give the JDBC drivers to conveniently improve on the performance of the Java applications.

The ResultSet

The executeQuery() method on the PreparedStatement returns a ResultSet.

A ResultSet is used to fetch the results of execution of a query. The data can be fetched row by row, using a column name or the index.

	
	String courseName = rs.getString("course");

A while loop is used to iterate thru the entire list of data fetched into the resultset.

	
	. . .
		 
	conn = DriverManager.getConnection(
		"jdbc:mysql://localhost:3306/studentdb");
	
	String sqlQuery = "select name, course from users";

	ps = conn.prepareStatement(sqlQuery);
	
	rs = ps.executeQuery();

	while (rs.next()) {
		
		String name = rs.getString("name");
		String courseName = rs.getString("course");

		System.out.println("Name " + name + " Course is " + courseName);
	}

	. . .

Modifying data in a table

We use the executeUpdate() on the PreparedStatement when we want to manipulate the data in a table.

	Connection conn = DriverManager.getConnection(
		"jdbc:mysql://localhost:3306/studentdb");
		
	String sqlQuery = "update users set name = ? where name = ?";
	ps = conn.prepareStatement(sqlQuery);

	ps.setString(1, "Mel Gibson");
	ps.setString(2, “Tom Cruise”);

	int updateCount = ps.executeUpdate();

This code changes the name from Tom Cruise to Mel Gibson.

Note that for the insert, update and delete SQL queries, we would use the executeUpdate() method.

Summary:

1. JDBC forms the bridge between Java code and the Relational databases
2. A Connection object represents a connection to the database – and has a URL that specifies the details of the connection
3. JDBC connection objects need to be released in the finally method block
4. The executeQuery() method of the PreparedStatement returns a ResultSet – which can be iterated over to fetch the query results
5. We would need to use the executeUpdate() method on the PreparedStatement object for any queries that insert, update or delete the rows in a table in an RDBMS.