JDBC (Java Database Connectivity)

DEF: JDBC is one of the generic API developed by SUN Micro System and supplied as a part of a java software to deal with any type of database product to perform various database operations.


 

JDBC Architecture

The JDBC API supports both two-tier and three-tier processing models for database access but in general, JDBC Architecture consists of two layers −

  • JDBC API:This provides the application-to-JDBC Manager connection.
  • JDBC Driver API:This supports the JDBC Manager-to-Driver Connection.

The JDBC API uses a driver manager and database-specific drivers to provide transparent connectivity to heterogeneous databases.

The JDBC driver manager ensures that the correct driver is used to access each data source. The driver manager is capable of supporting multiple concurrent drivers connected to multiple heterogeneous databases.

Following is the architectural diagram, which shows the location of the driver manager with respect to the JDBC drivers and the Java application −

jdbc-architecture

Steps for developing a JDBC Application:

  1. JDBC Driver is one of the predefined class developed by Database vendors for establishing the communication between java program and database software or JDBC Driver is one of middleware layer between java application and database software.
  2. Establishing the communication between java program and database software is nothing but creating an Object of appropriate JDBC Driver for example if we write an Object of OracleDriver class in our java program then our java program can establish the communication with oracle database software
  3. Similarly when we create an Object of DB2 Driver class then our java program establishes the communication between DB2 database software.
  4. Here Oracle Driver (Oracle Corporation, DB2 Driver(IBM)) are the examples of JDBC Drivers JDBC drivers are developed by database vendors in the language called java and they are coming or supplied along with database softwares. A java programmer is not responsible for development of JDBC drivers but java programmer always use database Drivers for establishing the communication between java program and database software.
  5. SUN Micro system has classified into all available JDBC drivers four categories those are
  • Type1 drivers or (JDBC-ODBC bridge drivers)
  • Type2 drivers or native drivers
  • Type 3 drivers or net protocol drivers
  • Type 4 drivers or all java/pure/thin drivers

ODBC Drivers (Open Database Connectivity):

ODBC Drivers:

  1. ODBC Drives specifications (rules) developed by Xopen Company. The ODBC Specification says develop all ODBC Drivers in a language called C. Which is one of the platform independent language.
  2. All Database vendors come forward and developed drives in a language called C. ODBC drivers are meant for establishing the communication channel or bridge between non java applications and database softwares all the database vendors has given the ODBC Drivers to Xopen Company and Xopen company supplied these ODBC drivers along Microsoft Operating Systems.
  3. The following diagram gives the view about the communication system between java/non java application and database software.

What are the differences between JDBC drivers and ODBC drivers?

JDBC Drivers ODBC Drivers
1.      JDBC Drivers specification developed by SUN Micro system and JDBC drivers are implemented by Database vendors.

2.      All  database vendors implemented JDBC Drivers in java language.

3.      All the JDBC drivers are Platform independent.

4.      All the JDBC drivers are supplied through database softwares.

5.      JDBC drivers are purely meant for establishing the communication between java applications and universal softwares.

 

6.      Industry is highly recommended to use JDBC Drivers to communicate with the database because they gives/posses platform independency.

1.      ODBC Drivers specification developed  by Xopen  and JDBC drivers are implemented by Database vendors.

2.      All  database vendors implemented ODBC Drivers in C language.

3.      All the ODBC drivers are Platform dependent.

4.      All the ODBC Drivers are supplied for Microsoft Operating Systems.

5.      ODBC Drivers are meant for both java and non java programmers for establishing communication between database softwares.

6.      ODBC drivers are not recommended by the industry to communication with the database because they posses/gives platform dependency.


5 Steps to connect to the database in java

  • Register the driver class
  • Create the connection object
  • Create the Statement object
  • Execute the query
  • Close the connection object

1)Register the driver class

The forName() method of Class class is used to register the driver class. This method is used to dynamically load the driver class.

Syntax of forName() method

public static void forName(String className)throws ClassNotFoundException

Example to register the OracleDriver class

Class.forName(“oracle.jdbc.driver.OracleDriver”);


2) Create the connection object

The getConnection() method of DriverManager class is used to establish connection with the database.

Syntax of getConnection() method

1) public static Connection getConnection(String url)throws SQLException

2) public static Connection getConnection(String url,String name,String password)

throws SQLException

Example to establish connection with the Oracle database

Connection connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/databaseName”,”root”, “root”);

3) Create the Statement object

The createStatement() method of Connection interface is used to create statement. The object of statement is responsible to execute queries with the database.

Syntax of createStatement() method

public Statement createStatement()throws SQLException

Example to create the statement object

Statement stmt=con.createStatement();

4) Execute the query

The executeQuery() method of Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the records of a table.

Syntax of executeQuery() method

public ResultSet executeQuery(String sql)throws SQLException

Example to execute query

ResultSet rs=stmt.executeQuery(“select * from emp”);

while(rs.next()){

System.out.println(rs.getInt(1)+” “+rs.getString(2));

}

5) Close the connection object

By closing connection object statement and ResultSet will be closed automatically. The close() method of Connection interface is used to close the connection.

Syntax of close() method

public void close()throws SQLException

JDBC Driver

  1. JDBC Drivers
  1. JDBC-ODBC bridge driver
  2. Native-API driver
  3. Network Protocol driver
  4. Thin driver

JDBC Driver is a software component that enables java application to interact with the database.There are 4 types of JDBC drivers:

1.      JDBC-ODBC bridge driver

2.      Native-API driver (partially java driver)

3.      Network Protocol driver (fully java driver)

4.      Thin driver (fully java driver)

1) JDBC-ODBC bridge driver

The JDBC-ODBC bridge driver uses ODBC driver to connect to the database. The JDBC-ODBC bridge driver converts JDBC method calls into the ODBC function calls. This is now discouraged because of thin driver.

type-1

easy to use.Advantages:

  • can be easily connected to any database.

Disadvantages:

  • Performance degraded because JDBC method call is converted into the ODBC function calls.
  • The ODBC driver needs to be installed on the client machine.

2) Native-API driver

The Native API driver uses the client-side libraries of the database. The driver converts JDBC method calls into native calls of the database API. It is not written entirely in java.

type-2

Advantage:

  • performance upgraded than JDBC-ODBC bridge driver.

Disadvantage:

  • The Native driver needs to be installed on the each client machine.
  • The Vendor client library needs to be installed on client machine.

3) Network Protocol driver

The Network Protocol driver uses middleware (application server) that converts JDBC calls directly or indirectly into the vendor-specific database protocol. It is fully written in java.

type-3

Advantage:

  • No client side library is required because of application server that can perform many tasks like auditing, load balancing, logging etc.

Disadvantages:

  • Network support is required on client machine.
  • Requires database-specific coding to be done in the middle tier.
  • Maintenance of Network Protocol driver becomes costly because it requires database-specific coding to be done in the middle tier.

 

4) Thin driver

The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is why it is known as thin driver. It is fully written in Java language.

type-4

Advantage:

  • Better performance than all other drivers.
  • No software is required at client side or server side.

Disadvantage:

  • Drivers depend on the Database.

DriverManager class:

The DriverManager class acts as an interface between user and drivers. It keeps track of the drivers that are available and handles establishing a connection between a database and the appropriate driver. The DriverManager class maintains a list of Driver classes that have registered themselves by calling the method DriverManager.registerDriver().

Commonly used methods of DriverManager class:

1) public static void registerDriver(Driver driver): is used to register the given driver with DriverManager.
2) public static void deregisterDriver(Driver driver): is used to deregister the given driver (drop the driver from the list) with DriverManager.
3) public static Connection getConnection(String url): is used to establish the connection with the specified url.
4) public static Connection getConnection(String url,String userName,String password): is used to establish the connection with the specified url, username and password.

What are the differences betweenDriverManager.registrationDriver and Class.forName(-)

DriverManager.registerDriver   Class.forName(-)
1.      This statement is used for registering or loading the Drivers explicitly.

 

2.      This Statement verifies the JdbcDriver availability at compile time and registered at runtime time.

3.      This approach is not recommended by industry because explicit driver registration done by Java programmer.

1.      This statement is used for registering or loading the Drivers implicitly with the help of inbuilt static blocks.

2.      This Statement verifies the JdbcDriver availability and registration at runtime.

3.      This approach is  recommended by industry because implicit driver registration done by Drivers Developers(Database developers)


Connection interface:

A Connection is the session between java application and database. The Connection interface is a factory of Statement, PreparedStatement, and DatabaseMetaData i.e. object of Connection can be used to get the object of Statement and DatabaseMetaData. The Connection interface provide many methods for transaction management like commit(),rollback() etc.

By default, connection commits the changes after executing queries.

Commonly used methods of Connection interface:

1) public Statement createStatement(): creates a statement object that can be used to execute SQL queries.
2) public Statement createStatement(int resultSetType,int resultSetConcurrency): Creates a Statement object that will generate ResultSet objects with the given type and concurrency.
3) public void setAutoCommit(boolean status): is used to set the commit status.By default it is true.
4) public void commit(): saves the changes made since the previous commit/rollback permanent.
5) public void rollback(): Drops all changes made since the previous commit/rollback.
6) public void close(): closes the connection and Releases a JDBC resources immediately.

Statement interface

The Statement interface provides methods to execute queries with the database. The statement interface is a factory of ResultSet i.e. it provides factory method to get the object of ResultSet.

Commonly used methods of Statement interface:

The important methods of Statement interface are as follows:

1) public ResultSet executeQuery(String sql): is used to execute SELECT query. It returns the object of ResultSet.
2) public int executeUpdate(String sql): is used to execute specified query, it may be create, drop, insert, update, delete etc.
3) public boolean execute(String sql): is used to execute queries that may return multiple results.
4) public int[] executeBatch(): is used to execute batch of commands.

ResultSet interface

The object of ResultSet maintains a cursor pointing to a particular row of data. Initially, cursor points to before the first row.

By default, ResultSet object can be moved forward only and it is not updatable.

But we can make this object to move forward and backward direction by passing either TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int,int) method as well as we can make this object as updatable by:

  1. Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
  2. CONCUR_UPDATABLE);

Commonly used methods of ResultSet interface

1) public boolean next(): is used to move the cursor to the one row next from the current position.
2) public boolean previous(): is used to move the cursor to the one row previous from the current position.
3) public boolean first(): is used to move the cursor to the first row in result set object.
4) public boolean last(): is used to move the cursor to the last row in result set object.
5) public boolean absolute(int row): is used to move the cursor to the specified row number in the ResultSet object.
6) public boolean relative(int row): is used to move the cursor to the relative row number in the ResultSet object, it may be positive or negative.
7) public int getInt(int columnIndex): is used to return the data of specified column index of the current row as int.
8) public int getInt(String columnName): is used to return the data of specified column name of the current row as int.
9) public String getString(int columnIndex): is used to return the data of specified column index of the current row as String.
10) public String getString(String columnName): is used to return the data of specified column name of the current row as String.

PreparedStatement interface

The PreparedStatement interface is a subinterface of Statement. It is used to execute parameterized query.

Let’s see the example of parameterized query:

  1. String sql=”insert into emp values(?,?,?)”;

As you can see, we are passing parameter (?) for the values. Its value will be set by calling the setter methods of PreparedStatement.

Why use PreparedStatement?

Improves performance: The performance of the application will be faster if you use PreparedStatement interface because query is compiled only once.

How to get the instance of PreparedStatement?

The prepareStatement() method of Connection interface is used to return the object of PreparedStatement. Syntax:

  1. publicPreparedStatement prepareStatement(String query)throws SQLException{}

Methods of PreparedStatement interface

The important methods of PreparedStatement interface are given below:

Method Description
public void setInt(int paramIndex, int value) sets the integer value to the given parameter index.
public void setString(int paramIndex, String value) sets the String value to the given parameter index.
public void setFloat(int paramIndex, float value) sets the float value to the given parameter index.
public void setDouble(int paramIndex, double value) sets the double value to the given parameter index.
public int executeUpdate() executes the query. It is used for create, drop, insert, update, delete etc.
public ResultSet executeQuery() executes the select query. It returns an instance of ResultSet.

ResultSetMetaData Interface

The metadata means data about data i.e. we can get further information from the data.

If you have to get metadata of a table like total number of column, column name, column type etc. , ResultSetMetaData interface is useful because it provides methods to get metadata from the ResultSet object.

Commonly used methods of ResultSetMetaData interface

Method Description
public int getColumnCount()throws SQLException it returns the total number of columns in the ResultSet object.
public String getColumnName(int index)throws SQLException it returns the column name of the specified column index.
public String getColumnTypeName(int index)throws SQLException it returns the column type name for the specified index.
public String getTableName(int index)throws SQLException it returns the table name for the specified column index.

DatabaseMetaData interface:

DatabaseMetaData interface provides methods to get meta data of a database such as database product name, database product version, driver name, name of total number of tables, name of total number of views etc.

Commonly used methods of DatabaseMetaData interface

·         public String getDriverName()throws SQLException: it returns the name of the JDBC driver.

·         public String getDriverVersion()throws SQLException: it returns the version number of the JDBC driver.

·         public String getUserName()throws SQLException: it returns the username of the database.

·         public String getDatabaseProductName()throws SQLException: it returns the product name of the database.

·         public String getDatabaseProductVersion()throws SQLException: it returns the product version of the database.

·         public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types)throws SQLException: it returns the description of the tables of the specified catalog. The table type can be TABLE, VIEW, ALIAS, SYSTEM TABLE, SYNONYM etc.


CallableStatement Interface

To call the stored procedures and functions, CallableStatement interface is used.

We can have business logic on the database by the use of stored procedures and functions that will make the performance better because these are precompiled.

Suppose you need the get the age of the employee based on the date of birth, you may create a function that receives date as the input and returns age of the employee as the output.

What is the difference between stored procedures and functions.

The differences between stored procedures and functions are given below:

Stored Procedure Function
is used to perform business logic. is used to perform calculation.
must not have the return type. must have the return type.
may return 0 or more values. may return only one values.
We can call functions from the procedure. Procedure cannot be called from function.
Procedure supports input and output parameters. Function supports only input parameter.
Exception handling using try/catch block can be used in stored procedures. Exception handling using try/catch can’t be used in user defined functions.

 

Differences between Statement and PreparedStatement

Statement PreparedStatement
1.      Statement is one of the super interface for the PreparedStatement interface.

2.      What are all queries submitted through the Statement interface such queries are known as static queries.

 

3.      Queries of Statement interface participates repeatedly in parsing and compilation phase.

4.      Statement interface object is highly recommended to execute different types of queries at different times.

5.      The data of static queries of Statement interface participates in both parsing and compilation phase.

1.      PreparedStatement is one of the sub interface for the Statement interface.

2.      What are all queries submitted through the PreparedStatement interface such queries are known as dynamic or precompiled queries.

3.      Queries of PreparedStatement interface only once in participating compilation phase.

4.      PreparedStatement interface Object is highly recommended to execute same type of query multiple times.

5.      The data of dynamic queries of PreparedStatement participating only at execution phase.

Programs :

JDBC Statement example – Create a table

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementCreateExample {

	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaserelationships",;
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";

	public static void main(String[] argv) {

		try {

			createDbUserTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void createDbUserTable() throws SQLException {

		Connection dbConnection = null;
		Statement statement = null;

		String createTableSQL = "CREATE TABLE DBUSER("
				+ "USER_ID NUMBER(5) NOT NULL, "
				+ "USERNAME VARCHAR(20) NOT NULL, "
				+ "CREATED_BY VARCHAR(20) NOT NULL, "
				+ "CREATED_DATE DATE NOT NULL, " + "PRIMARY KEY (USER_ID) "
				+ ")";

		try {
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();

			System.out.println(createTableSQL);
                        // execute the SQL stetement
			statement.execute(createTableSQL);

			System.out.println("Table \"dbuser\" is created!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

			if (statement != null) {
				statement.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
					DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

}

JDBC Statement example – Insert a record

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class JDBCStatementInsertExample {

	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaseName",;
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";
	private static final DateFormat dateFormat = new SimpleDateFormat(
			"yyyy/MM/dd HH:mm:ss");

	public static void main(String[] argv) {

		try {

			insertRecordIntoDbUserTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void insertRecordIntoDbUserTable() throws SQLException {

		Connection dbConnection = null;
		Statement statement = null;

		String insertTableSQL = "INSERT INTO DBUSER"
				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) " + "VALUES"
				+ "(1,'mkyong','system', " + "to_date('"
				+ getCurrentTimeStamp() + "', 'yyyy/mm/dd hh24:mi:ss'))";

		try {
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();

			System.out.println(insertTableSQL);

			// execute insert SQL stetement
			statement.executeUpdate(insertTableSQL);

			System.out.println("Record is inserted into DBUSER table!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

			if (statement != null) {
				statement.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
                               DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

	private static String getCurrentTimeStamp() {

		java.util.Date today = new java.util.Date();
		return dateFormat.format(today.getTime());

	}

}

 


JDBC Statement example – Update a record

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementUpdateExample {

	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaserelationships",;
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";

	public static void main(String[] argv) {

		try {

			updateRecordIntoDbUserTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void updateRecordIntoDbUserTable() throws SQLException {

		Connection dbConnection = null;
		Statement statement = null;

		String updateTableSQL = "UPDATE DBUSER"
				+ " SET USERNAME = 'mkyong_new' "
				+ " WHERE USER_ID = 1";

		try {
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();

			System.out.println(updateTableSQL);

			// execute update SQL stetement
			statement.execute(updateTableSQL);

			System.out.println("Record is updated to DBUSER table!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

			if (statement != null) {
				statement.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
                              DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

}

 


JDBC Statement example – Select All  record

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementSelectExample {

	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaseName",;
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";

	public static void main(String[] argv) {

		try {

			selectRecordsFromDbUserTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void selectRecordsFromDbUserTable() throws SQLException {

		Connection dbConnection = null;
		Statement statement = null;

		String selectTableSQL = "SELECT USER_ID, USERNAME from DBUSER";

		try {
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();

			System.out.println(selectTableSQL);

			// execute select SQL stetement
			ResultSet rs = statement.executeQuery(selectTableSQL);

			while (rs.next()) {

				String userid = rs.getString("USER_ID");
				String username = rs.getString("USERNAME");

				System.out.println("userid : " + userid);
				System.out.println("username : " + username);

			}

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

			if (statement != null) {
				statement.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER,
					DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

}

 


JDBC Statement example – Delete a record

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementDeleteExample {

	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaseName",;
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";

	public static void main(String[] argv) {

		try {

			deleteRecordFromDbUserTable();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void deleteRecordFromDbUserTable() throws SQLException {

		Connection dbConnection = null;
		Statement statement = null;

		String deleteTableSQL = "DELETE DBUSER WHERE USER_ID = 1";

		try {
			dbConnection = getDBConnection();
			statement = dbConnection.createStatement();

			System.out.println(deleteTableSQL);

			// execute delete SQL stetement
			statement.execute(deleteTableSQL);

			System.out.println("Record is deleted from DBUSER table!");

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

			if (statement != null) {
				statement.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
                             DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

}

 


  • JDBC PreparedStatement example – Create a table

    import java.sql.DriverManager;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCPreparedStatementCreateExample {
    
    	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaseName",;
    	private static final String DB_USER = "user";
    	private static final String DB_PASSWORD = "password";
    
    	public static void main(String[] argv) {
    
    		try {
    
    			createTable();
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    	}
    
    	private static void createTable() throws SQLException {
    
    		Connection dbConnection = null;
    		PreparedStatement preparedStatement = null;
    
    		String createTableSQL = "CREATE TABLE DBUSER1("
    				+ "USER_ID NUMBER(5) NOT NULL, "
    				+ "USERNAME VARCHAR(20) NOT NULL, "
    				+ "CREATED_BY VARCHAR(20) NOT NULL, "
    				+ "CREATED_DATE DATE NOT NULL, " + "PRIMARY KEY (USER_ID) "
    				+ ")";
    
    		try {
    			dbConnection = getDBConnection();
    			preparedStatement = dbConnection.prepareStatement(createTableSQL);
    
    			System.out.println(createTableSQL);
    
    			// execute create SQL stetement
    			preparedStatement.executeUpdate();
    
    			System.out.println("Table \"dbuser\" is created!");
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		} finally {
    
    			if (preparedStatement != null) {
    				preparedStatement.close();
    			}
    
    			if (dbConnection != null) {
    				dbConnection.close();
    			}
    
    		}
    
    	}
    
    	private static Connection getDBConnection() {
    
    		Connection dbConnection = null;
    
    		try {
    
    			Class.forName(DB_DRIVER);
    
    		} catch (ClassNotFoundException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		try {
    
    			dbConnection = DriverManager.getConnection(
                                DB_CONNECTION, DB_USER,DB_PASSWORD);
    			return dbConnection;
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		return dbConnection;
    
    	}
    
    }

     


  • JDBC PreparedStatement example – Insert a record

    import java.sql.DriverManager;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    
    public class JDBCPreparedStatementInsertExample {
    
    	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaseName",;
    	private static final String DB_USER = "user";
    	private static final String DB_PASSWORD = "password";
    
    	public static void main(String[] argv) {
    
    		try {
    
    			insertRecordIntoTable();
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    	}
    
    	private static void insertRecordIntoTable() throws SQLException {
    
    		Connection dbConnection = null;
    		PreparedStatement preparedStatement = null;
    
    		String insertTableSQL = "INSERT INTO DBUSER"
    				+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
    				+ "(?,?,?,?)";
    
    		try {
    			dbConnection = getDBConnection();
    			preparedStatement = dbConnection.prepareStatement(insertTableSQL);
    
    			preparedStatement.setInt(1, 11);
    			preparedStatement.setString(2, "mkyong");
    			preparedStatement.setString(3, "system");
    			preparedStatement.setTimestamp(4, getCurrentTimeStamp());
    
    			// execute insert SQL stetement
    			preparedStatement.executeUpdate();
    
    			System.out.println("Record is inserted into DBUSER table!");
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		} finally {
    
    			if (preparedStatement != null) {
    				preparedStatement.close();
    			}
    
    			if (dbConnection != null) {
    				dbConnection.close();
    			}
    
    		}
    
    	}
    
    	private static Connection getDBConnection() {
    
    		Connection dbConnection = null;
    
    		try {
    
    			Class.forName(DB_DRIVER);
    
    		} catch (ClassNotFoundException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		try {
    
    			dbConnection = DriverManager.getConnection(
                                DB_CONNECTION, DB_USER,DB_PASSWORD);
    			return dbConnection;
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		return dbConnection;
    
    	}
    
    	private static java.sql.Timestamp getCurrentTimeStamp() {
    
    		java.util.Date today = new java.util.Date();
    		return new java.sql.Timestamp(today.getTime());
    
    	}
    
    }

     


  • JDBC PreparedStatement example – Update a record

    import java.sql.DriverManager;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCPreparedStatementUpdateExample {
    
    	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaseName",;
    	private static final String DB_USER = "user";
    	private static final String DB_PASSWORD = "password";
    
    	public static void main(String[] argv) {
    
    		try {
    
    			updateRecordToTable();
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    	}
    
    	private static void updateRecordToTable() throws SQLException {
    
    		Connection dbConnection = null;
    		PreparedStatement preparedStatement = null;
    
    		String updateTableSQL = "UPDATE DBUSER SET USERNAME = ? "
    				                  + " WHERE USER_ID = ?";
    
    		try {
    			dbConnection = getDBConnection();
    			preparedStatement = dbConnection.prepareStatement(updateTableSQL);
    
    			preparedStatement.setString(1, "mkyong_new_value");
    			preparedStatement.setInt(2, 1001);
    
    			// execute update SQL stetement
    			preparedStatement.executeUpdate();
    
    			System.out.println("Record is updated to DBUSER table!");
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		} finally {
    
    			if (preparedStatement != null) {
    				preparedStatement.close();
    			}
    
    			if (dbConnection != null) {
    				dbConnection.close();
    			}
    
    		}
    
    	}
    
    	private static Connection getDBConnection() {
    
    		Connection dbConnection = null;
    
    		try {
    
    			Class.forName(DB_DRIVER);
    
    		} catch (ClassNotFoundException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		try {
    
    			dbConnection = DriverManager.getConnection(
                                DB_CONNECTION, DB_USER,DB_PASSWORD);
    			return dbConnection;
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		return dbConnection;
    
    	}
    
    }

     


  • JDBC PreparedStatement example – Delete a record

    import java.sql.DriverManager;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    
    public class JDBCPreparedStatementSelectExample {
    
    	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaseName",;
    	private static final String DB_USER = "user";
    	private static final String DB_PASSWORD = "password";
    
    	public static void main(String[] argv) {
    
    		try {
    
    			deleteRecordFromTable();
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    	}
    
    	private static void deleteRecordFromTable() throws SQLException {
    
    		Connection dbConnection = null;
    		PreparedStatement preparedStatement = null;
    
    		String deleteSQL = "DELETE DBUSER WHERE USER_ID = ?";
    
    		try {
    			dbConnection = getDBConnection();
    			preparedStatement = dbConnection.prepareStatement(deleteSQL);
    			preparedStatement.setInt(1, 1001);
    
    			// execute delete SQL stetement
    			preparedStatement.executeUpdate();
    
    			System.out.println("Record is deleted!");
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		} finally {
    
    			if (preparedStatement != null) {
    				preparedStatement.close();
    			}
    
    			if (dbConnection != null) {
    				dbConnection.close();
    			}
    
    		}
    
    	}
    
    	private static Connection getDBConnection() {
    
    		Connection dbConnection = null;
    
    		try {
    
    			Class.forName(DB_DRIVER);
    
    		} catch (ClassNotFoundException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		try {
    
    			dbConnection = DriverManager.getConnection(
                                DB_CONNECTION, DB_USER,DB_PASSWORD);
    			return dbConnection;
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		return dbConnection;
    
    	}
    
    }

     


  • JDBC PreparedStatement example – Select list of the records

    import java.sql.DriverManager;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class JDBCPreparedStatementSelectExample {
    
    	private static final String DB_DRIVER = "com.mysql.jdbc.Driver";
    	private static final String DB_CONNECTION = "jdbc:mysql://localhost:3306/databaseName",;
    	private static final String DB_USER = "user";
    	private static final String DB_PASSWORD = "password";
    
    	public static void main(String[] argv) {
    
    		try {
    
    			selectRecordsFromTable();
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    	}
    
    	private static void selectRecordsFromTable() throws SQLException {
    
    		Connection dbConnection = null;
    		PreparedStatement preparedStatement = null;
    
    		String selectSQL = "SELECT USER_ID, USERNAME FROM DBUSER WHERE USER_ID = ?";
    
    		try {
    			dbConnection = getDBConnection();
    			preparedStatement = dbConnection.prepareStatement(selectSQL);
    			preparedStatement.setInt(1, 1001);
    
    			// execute select SQL stetement
    			ResultSet rs = preparedStatement.executeQuery();
    
    			while (rs.next()) {
    
    				String userid = rs.getString("USER_ID");
    				String username = rs.getString("USERNAME");
    
    				System.out.println("userid : " + userid);
    				System.out.println("username : " + username);
    
    			}
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		} finally {
    
    			if (preparedStatement != null) {
    				preparedStatement.close();
    			}
    
    			if (dbConnection != null) {
    				dbConnection.close();
    			}
    
    		}
    
    	}
    
    	private static Connection getDBConnection() {
    
    		Connection dbConnection = null;
    
    		try {
    
    			Class.forName(DB_DRIVER);
    
    		} catch (ClassNotFoundException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		try {
    
    			dbConnection = DriverManager.getConnection(
                                 DB_CONNECTION, DB_USER,DB_PASSWORD);
    			return dbConnection;
    
    		} catch (SQLException e) {
    
    			System.out.println(e.getMessage());
    
    		}
    
    		return dbConnection;
    
    	}
    
    }

     


     Relationship in Database Management System

A relationship, in the context of databases, is a situation that exists between two relational database tables when one table has a foreign key that references the primary key of the other table. Relationships allow relational databases to split and store data in different tables, while linking disparate data items.

There are four relationships in database.

– One to One: One entity is associated with another entity. For Ex: Each employee is associated with one department
– One to Many: One entity is associated with many other entities. For Ex: A company is associated with all working employees in one branch/office/country.
– Many to One: Many entities are associated with only one entity. For Ex: Many employees are associated with one project.
– Many to Many: Many entities are associated with many other entities. For Ex: In a company many employees are associated with multiple projects(completed/existing), and at the same time, projects are associated with multiple employees.

What are the benefits of normalizing database?

– It helps to avoid duplicate entries.
– It allows saving storage space.
– It enhances the performance of queries.

What are the benefits of normalizing database?

The benefits of normalization are:

– The process of searching, sorting and creating indexes is faster
– More tables can be derived for clear and needed tables to be designed
– Clustering indexes can be created which provides the flexibility in fine tuning queries.
– Less redundant data and fewer null values will make the database more compact.
– The indexes of tables make data modification commands execution much faster.
– If redundant data is not maintained, the execution of triggers is quicker.
– Normalization facilitates in reducing data modification anomalies.

What is normalization?

– Normalization is the way of organizing data in a database by removing redundancy and inconsistent dependency.
– Database normalization has the rules to be followed while creating databases.Each rule is called as normal form.

First Normal form states – Remove duplicate column and identify each set of related data with a primary key.
Second normal form – Create relationship between master and master detail tables using foreign key.
Third normal form – Remove the fields that do not depend on the primary key.

– It is the process of organizing data into related table.
– To normalize database, we divide database into tables and establish relationships between the tables.
– It reduces redundancy. It is done to improve performance of query.

What is normalization?

Normalization is the technique for designing database tables to eliminate data redundancy, to safe guard the database against certain anomalies (structural problems). It is a step-by-step breakdown of data structure complexity into simple structure that facilitates no loss to information and relationship.

What is denormalization?

The process of adding redundant data to get rid of complex join, in order to optimize database performance. This is done to speed up database access by moving from higher to lower form of normalization

What is denormalization?

Redundant data addition for optimizing the performance of a database, denormalization is the process. The inefficiencies those inherit in RDBMS software are addressed with denormalization. It is a process of combining data into a single table. Denormalization structures are used in databases for providing quick access for a specific user needs.

Explain DML and DDL statements.

Data definition language is used to define and manage all attributes and properties of a database.

Most DDL statements include following commands:
– CREATE
– ALTER
– DROP

Data Manipulation Language: Data manipulation language is used to select, insert, update, and delete data in the objects defined with DDL.

Explain DML and DDL statements.

DDL-Data Definition Language: DDL is the syntax of SQL for defining the way the database is organized physically. Database meta data is created by DDL of SQL. The tables, views, indexes, sequences, triggers, database users (by DBA) are well defined using DDL statement, CREATE. Modification for the metadata or schema is done by ALTER command and removing database objects is done by DROP command.

DML-Data Manipulation Language: DML statements, INSERT, DELETE, UPDATE AND SELECT … INTO are used for data manipulation, adding new rows, deleting unwanted rows and changing data in the rows. These are known as SQL-change statements. DML does not change the schema of database.

Relationships Programs :

One to One: One entity is associated with another entity.

package onetoone;

import onetoone.User;

public class Person {

	private int person_id;
	private String person_name;
	private String addr;
	private User user;

	public int getPerson_id() {
		return person_id;
	}

	public void setPerson_id(int person_id) {
		this.person_id = person_id;
	}

	public String getPerson_name() {
		return person_name;
	}

	public void setPerson_name(String person_name) {
		this.person_name = person_name;
	}

	public String getAddr() {
		return addr;
	}

	public void setAddr(String addr) {
		this.addr = addr;
	}

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}
}
package onetoone;

public class User {

	int user_id;
	private String user_name;
	
	public int getUser_id() {
		return user_id;
	}
	public void setUser_id(int user_id) {
		this.user_id = user_id;
	}
	public String getUser_name() {
		return user_name;
	}
	public void setUser_name(String user_name) {
		this.user_name = user_name;
	}
	
	
}
package onetoone;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Test {

	public static void main(String[] args) {

		User user = new User();

		user.setUser_id(101);
		user.setUser_name("RahulMoundear");

		Person person = new Person();
		person.setPerson_id(1);
		person.setPerson_name("Rahul Moundekar");
		person.setAddr("wadgaonsheri");

		person.setUser(user);

		try {

			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/databaserelationships",
					"root", "root");

			Statement statement = connection.createStatement();

			String usersql = "insert into user values(" + user.getUser_id()
					+ ",'" + user.getUser_name() + "')";

			String personsql = "insert into person values("
					+ person.getPerson_id() + ",'" + person.getPerson_name()
					+ "','" + person.getAddr() + "','"
					+ person.getUser().getUser_id() + "')";

			statement.execute(usersql);
			statement.execute(personsql);

			System.out.println("Data Save Successfully");
			
		} catch (Exception e) {
			e.printStackTrace();// TODO: handle exception
		}

	}

}

– One to Many: One entity is associated with many other entities.
– Many to One: Many entities are associated with only one entity.

package onetomany;

public class Address {
	private int addr_id;
	private String street;
	private Person person;
	
	
	public int getAddr_id() {
		return addr_id;
	}
	public void setAddr_id(int addr_id) {
		this.addr_id = addr_id;
	}
	public String getStreet() {
		return street;
	}
	public void setStreet(String street) {
		this.street = street;
	}
	public Person getPerson() {
		return person;
	}
	public void setPerson(Person person) {
		this.person = person;
	}
	

}
package onetomany;

import java.util.List;

public class Person {

	private int person_id;
	private String person_name;
	private List<Address> addresses;
	
	
	public int getPerson_id() {
		return person_id;
	}
	public void setPerson_id(int person_id) {
		this.person_id = person_id;
	}
	public String getPerson_name() {
		return person_name;
	}
	public void setPerson_name(String person_name) {
		this.person_name = person_name;
	}
	
	public List<Address> getAddresses() {
		return addresses;
	}
	public void setAddresses(List<Address> addresses) {
		this.addresses = addresses;
	}
	
	
	
	
}
package onetomany;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class Test {

	public static Person setAllData() {
		Address address1 = new Address();
		address1.setAddr_id(101);
		address1.setStreet("pune");

		Address address2 = new Address();
		address2.setAddr_id(102);
		address2.setStreet("mumbai");

		List<Address> list = new ArrayList<Address>();

		list.add(address1);
		list.add(address2);

		Person person = new Person();
		person.setPerson_id(1);
		person.setPerson_name("Rahul Moundekar");
		person.setAddresses(list);

		return person;
	}

	public static void main(String[] args) {

		Person person = setAllData();

		try {

			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/databaserelationships",
					"root", "root");

			Statement statement = connection.createStatement();

			List<Address> addresses = person.getAddresses();
			Iterator<Address> it = addresses.iterator();
			while (it.hasNext()) {
				Address address = (Address) it.next();
				String addresssql = "insert into address values("
						+ address.getAddr_id() + ",'" + address.getStreet()
						+ "'," + person.getPerson_id() + ")";
				statement.execute(addresssql);

			}

			String personsql = "insert into person1 values("
					+ person.getPerson_id() + ",'" + person.getPerson_name()
					+ "')";

			statement.execute(personsql);

			System.out.println("Data Save Successfully");

		} catch (Exception e) {
			e.printStackTrace();// TODO: handle exception
		}

	}

}

– Many to Many: Many entities are associated with many other entities.

package manytomany;

import java.util.List;

public class Student implements java.io.Serializable {

	private Integer studentId;
	private String studentName;
	private List<Course> courses;
	
	
	public List<Course> getCourses() {
		return courses;
	}

	public void setCourses(List<Course> courses) {
		this.courses = courses;
	}

	public Student() {
	}

	public Integer getStudentId() {
		return this.studentId;
	}

	public void setStudentId(Integer studentId) {
		this.studentId = studentId;
	}

	
	public String getStudentName() {
		return this.studentName;
	}

	public void setStudentName(String studentName) {
		this.studentName = studentName;
	}

}
package manytomany;
import java.util.List;

public class Course implements java.io.Serializable {

	
	private static final long serialVersionUID = 1L;
	private Integer courseId;
	private String courseName;
	private List<Student> students;
	
	public List<Student> getStudents() {
		return students;
	}

	public void setStudents(List<Student> students) {
		this.students = students;
	}

	public Course() {
	}

	public Course(String courseName) {
		this.courseName = courseName;
	}

	public Integer getCourseId() {
		return this.courseId;
	}

	public void setCourseId(Integer courseId) {
		this.courseId = courseId;
	}

	public String getCourseName() {
		return this.courseName;
	}

	public void setCourseName(String courseName) {
		this.courseName = courseName;
	}

}
package manytomany;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class Test {

	public static void main(String[] args) {

		Student student1 = new Student();
		student1.setStudentId(101);
		student1.setStudentName("Rahul");

		Student student2 = new Student();
		student2.setStudentId(102);
		student2.setStudentName("Mahesh");

		List<Student> students1=new ArrayList<Student>();
			students1.add(student1);
			
		List<Student> students = new ArrayList<Student>();
			students.add(student2);
			students.add(student1);
			
		/*
		 * ======================================================================
		 * ===========
		 */
		Course course1 = new Course();
		course1.setCourseId(1);
		course1.setCourseName("Java");

		Course course2 = new Course();
		course2.setCourseId(2);
		course2.setCourseName("cpp");

		List<Course> courses = new ArrayList<Course>();
		courses.add(course1);
		courses.add(course2);

		List<Course> coursess = new ArrayList<Course>();
		coursess.add(course2);

		student1.setCourses(courses);
		course1.setStudents(students1);

		try {

			Class.forName("com.mysql.jdbc.Driver");
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/databaserelationships",
					"root", "root");

			Statement statement = connection.createStatement();
			/*
			 * ==================================Save Student and
			 * Course===========================================================
			 */
			Iterator<Student> iterator = students.iterator();
			while (iterator.hasNext()) {
				Student student = (Student) iterator.next();
				String studentsql = "insert into student values("
						+ student.getStudentId() + ",'"
						+ student.getStudentName() + "')";
				statement.execute(studentsql);
			}

			Iterator<Course> it = courses.iterator();
			while (it.hasNext()) {
				Course course = (Course) it.next();
				String coursesql = "insert into course values("
						+ course.getCourseId() + ",'" + course.getCourseName()
						+ "')";
				statement.execute(coursesql);
			}

			/*
			 * =========================================End======================
			 * ===================================================
			 */
			int i = 0;
			List<Course> list = student1.getCourses();
			Iterator<Course> itr = list.iterator();
			while (itr.hasNext()) {
				Course course = (Course) itr.next();
				List<Student> list1 = course.getStudents();
				Iterator<Student> itr1 = list1.iterator();
				while (itr1.hasNext()) {
					Student student = (Student) itr1.next();
					String cou_stud = "insert into course_student values("
							+ i++ + ",'" + student.getStudentId() + "','"
							+ course.getCourseId() + "')";
					statement.execute(cou_stud);
				}

			}

			System.out.println("done");

		} catch (Exception e) {
			e.printStackTrace();// TODO: handle exception
		}

	}

}