-->

What is JDBC:

JDBC is part of Java Standard Edition(Java SE),it is abbreviated as Java Data Base Connectivity. What it does is , it will enable connection between Java Applications and Database(i.e Simply it act’s interface between Java Applications and Database). JDBC API(Application Programming Interface) contains lot of classes and interfaces which are written in Java programming language.Some of key points about JDBC are as follows:

  1. JDBC is used to write  Persistency logics at application level
  2. Persistency logic  means  logics which interact with database
  3. For persistency logic you can use either JDBC or Hibernate framework
  4. JDBC is good performer than Hibernate framework because ,If you write logic in Hibernate, it needs to convert into JDBC and then it will return appropriate results

JDBC

Some of the classes and interfaces in JDBC are

  1. DriverManager(java.sql.DriverManager) → Class
  2. Connection → Interface
  3. PreparedStatement → Interface
  4. Statement → Interface
  5. CallableStatement → Interface
  6. ResultSet →Class
  7. ResultSetMetaData → Class
JDBC Advantages:
  1. If use combination of JAVA API and JDBC API , This will make us Application development easy , safe and Cost Effective.
  2. Since JDBC driver is written in java, there was no configuration set up required on client side
  3. JDBC can read any kind of Database.
  4. JDBC is platform independent.

Prerequisites for JDBC Java Projects:

To start implement JDBC projects you should be having min of knowledge about SQL Queries to perform DML(Data Manipulation Language) and DDL(Data Definition Language) operations

DDL operations are like CREATE, ALTER and DROP.Sample Queries are as follows:

  1. CREATE table TABLE_NAME (
    variable_name data_type,
    variable_name1 data_type);
    Ex: CREATE table INCOME (
    month_income int NOT NULL,
    month varchar(25) NOT NULL); → This will create INCOME table with month_income , month as a columns.
  2. ALTER TABLE ADD CONSTRAINT constraint_name PRIMARY KEY ();
    Ex:ALTER TABLE INCOME ADD CONSTRAINT month_income_pk PRIMARY KEY (month_income); →This will make month_income_pk as PK(Primery Key) for INSERT Table.
  3. DROP TABLE;
    Ex: DROP INCOME; → This will drop/delete INCOME table.

Note: SQL Queries are not case sensitive(DELETE OR delete both are same).

DML operations are like SELECT, INSERT, UPDATE and DELETE.Sample Queries are as follows:

  1. SELECT COLUMN from TABLE where CONDITION;
    Ex: SELECT month_income from INCOME where month=’Jan’; → This will return Jan month income.
  2. INSERT into TABLE(COLUMN1, COLUMN2,…) values(‘Test’,’Test2′,….);
    Ex: INSERT into INCOME(month_income, month) values(‘$1000′,’ABC’); → This will insert $1000 , ABC values into month_income ,month columns.
  3. UPDATE TABLE SET COLUMN where CONDITION;
    Ex:UPDATE INCOME SET month_income=’mon_inc’ where month_income=’month_income’; →This will update month_income column as mon_inc.
  4. DELETE TABLE;
    Ex: DELETE INCOME; → This will delete INCOME table.

Note: SQL Queries are not case sensitive(SELECT or select both are same).


Installing Procedure

In order to communicate with Database we should have active Database installed in our machine.Please follow below steps.

  1. First step is to make sure you have set JAVA_HOME environment variable path in your machine.
  2. Install your preferred Database in your machine. You can install PostgreSQL(Click Here to Download) or you can download MySQL(Click Here to Download) or anyother Databses.
  3. Download Corresponding Database Drivers: for PostgreSQL and for MySQL
  4. Final step is to credentials for Database.please remember password that you give while installing any DB because you have to use that password if you want to connect to Database.

I am using PostgreSQL database for my examples but you can use whatever you like most

Connection steps for JDBC

If you are going to write JDBC code,There are mainly 5 fundamental steps involved.

  1. Import all required JDBC packages.simple you can use import java.sql.* to import all packages.
  2. Register JDBC Driver with database using class.forName().
  3. Establish connection with database using connection interface.like
    1. connection = DriverManager.getConnection(“jdbc:mysql://localhost:3306/username”,”username”,”password”
    2. connection = DriverManager.getConnection(“jdbc:postgresql://localhost:5432/username”, “username”,
      “password”)
  4. Declare JDBC Statements interface to execute SQL operations.
  5. Close all the resources(i.e connection resources and statement resources).

Testing DB Connection

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
public class FirstJDBCDemo {
      public static void main(String[] argv) {
           System.out.println("-------- PostgreSQL "+ "JDBC Connection Testing ------------");
           try {
// Here we need to provide database Driver path, you can google with your database name , it will give you the driver path.
                  Class.forName("org.postgresql.Driver"); 
               } catch (ClassNotFoundException e) {

			System.out.println("Where is your PostgreSQL JDBC Driver? "
					+ "Include in your library path!");
			e.printStackTrace();
			return;

		}

		System.out.println("PostgreSQL JDBC Driver Registered!");

		Connection connection = null;

		try {
// Here in place of username & password give your database username and password.
			connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/username", "username",
					"password");

		} catch (SQLException e) {

			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return;

		}

		if (connection != null) {
			System.out.println("You can control your database now!");
		} else {
			System.out.println("Error occured while performing connection operation , please check your credentials!");
		}
	}

}

Source Code: FirstJDBCDemo

JDBC Statements

Statement interface(public statement interface)object is used to execute static SQL queries with no parameters, once if you run this statement it will produce Result Set with appropriate info from database. Statement objects are created using Connection interface object(con.createStatement()).Statement object consists some of methods to execute SQL queries.they are

  1. boolean excecute(String sqlQuery)
  2. ResultSet executeQuery(String sqlQuery)
  3. int executeUpdate(String sqlQuery)

Creating Table

package jdbc_demos;

// Step: 1 importing all JDBC packages
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCStatementExample {

	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;

		try {
 // Step: 2 Register JDBC Driver with database using class.forName().
			Class.forName("org.postgresql.Driver");
			
 // Step: 3 Establish connection with database using connection interface.
            connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/username", "username",
					"password");
            
 // Step: 4 Declare JDBC Statements interface to execute SQL operations.
            statement = connection.createStatement();
            String sqlQuery = "CREATE TABLE INCOME("
    				+ "month_income  NUMBER(5)  NOT NULL, "
    				+ "month  VARCHAR(20)  NOT NULL, "
    				+ "created_date DATE NOT NULL, "
    				+ ")";
            statement.execute(sqlQuery);
            System.out.println("INCOME Table is created");

		} catch(Exception e){
			e.printStackTrace();
		}finally {
 // Step: 5 Close all the resources.
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		}
       }
}

Source Code:JDBCStatementExample

JDBC Insert Statement Example

package jdbc_demos;

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

public class JDBCInsertStatementExample {

	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;

		try {
			// Step: 2 Register JDBC Driver with database using class.forName().
			Class.forName("org.postgresql.Driver");

			// Step: 3 Establish connection with database using connection
			// interface.
			connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/username", "username",
					"password");

			// Step: 4 Declare JDBC Statements interface to execute SQL
			// operations.
			statement = connection.createStatement();
			String insertSQLQuery = "INSERT into INCOME"+"(month_income, month)"+"values('$1000','ABC')"; 
		
			statement.executeUpdate(insertSQLQuery);
			System.out.println("Values are successfully inserted into INCOME Table.");

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// Step: 5 Close all the resources.
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}

		}
	}

}

Source Code:JDBCInsertStatementExample

JDBC Select Statement Example

package jdbc_demos;

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

public class JDBCSelectStatementExample {
	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;

		try {
			// Step: 2 Register JDBC Driver with database using class.forName().
			Class.forName("org.postgresql.Driver");

			// Step: 3 Establish connection with database using connection
			// interface.
			connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/username", "username",
					"password");

			// Step: 4 Declare JDBC Statements interface to execute SQL
			// operations.
			statement = connection.createStatement();
			String selectSQLQuery = "SELECT month_income from INCOME where month='Jan'";
			// Here we are using ResultSet for select query.
			ResultSet resultSet = statement.executeQuery(selectSQLQuery);
			int monthIncome = resultSet.getInt("month_income");
			System.out.println("month_income is : "+ resultSet.getInt(monthIncome));

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// Step: 5 Close all the resources.
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}

		}

	}

}

Source Code:JDBCSelectStatementExample

JDBC PreparedStatements

JDBC Prepared Statement is an object which represents pre-compiled SQL statement.This is extended to prepared statement and This object is used to execute SQL queries with specific parameters.

You can use same examples which are added above for PreparedStatements also,but there are few changes required,They are

  1. Import PreparedStatement instead of Statement.
  2. Set values for attributes to those we are passing in SQL Query.

I am adding few examples below to understand about PreparedStatements.

Note:Usage of PreparedStatements is strongly recommended than using Statements.

JDBC Select Prepared Statement Example

package jdbc_demos;

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

public class JDBCSelectPreparedStatementExample {
	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		PreparedStatement statement = null;

		try {
			// Step: 2 Register JDBC Driver with database using class.forName().
			Class.forName("org.postgresql.Driver");

			// Step: 3 Establish connection with database using connection
			// interface.
			connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/username", "username",
					"password");

			// Step: 4 Declare JDBC Statements interface to execute SQL
			// operations.
			String selectSQLQuery = "SELECT month_income from INCOME where month='Jan'";
			statement = connection.prepareStatement(selectSQLQuery);
			statement.setInt(1, 1000);
			ResultSet resultSet = statement.executeQuery();
			int monthIncome = resultSet.getInt("month_income");
			System.out.println("Month Income is : " + monthIncome);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// Step: 5 Close all the resources.
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}

		}

	}

}

Source Code:JDBCSelectPreparedStatementExample

JDBC Insert Prepared Statement Example

package jdbc_demos;

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

public class JDBCInsertPreparedStatementExample {
	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		PreparedStatement statement = null;

		try {
			// Step: 2 Register JDBC Driver with database using class.forName().
			Class.forName("org.postgresql.Driver");

			// Step: 3 Establish connection with database using connection
			// interface.
			connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/username", "username",
					"password");

			// Step: 4 Declare JDBC Statements interface to execute SQL
			// operations.
			String insertSQLQuery = "INSERT into INCOME"+"(month_income, month)"+"values(?,?)"; 
			statement = connection.prepareStatement(insertSQLQuery);
			statement.setInt(1, 1000);
			statement.setString(2, "ABC");
			int rows = statement.executeUpdate();
			System.out.println("No of rows impacted : " + rows);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// Step: 5 Close all the resources.
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}

		}

	}

}

Source Code:JDBCInsertPreparedStatementExample

JDBC Transaction Management

Transaction means a logical unit of DML operations(i.e set of statements into single unit is called Transaction).That means all operations will either Succeed or Fail.

For Example if you take Money transfer operation, first bank needs to deduct money from one account and transfer it to another account.If any error occurs during whole process all transactions should be Roll Back.Transaction management helps to achieve this process.

Consolidated Example

Here i am adding a consolidated code with performing operations like select , insert and delete.

BankAccount.java

package jdbc_exercises;

import java.sql.*;

public class BankAccount {
	String accName;
	long accNumber;
	String SSN;
	double balance;

	public BankAccount(String accName, long accNumber) {
		this.accName = accName;
		this.accNumber = accNumber;
	}

	public void createBankAccountInfo() {
		Connection con;
		PreparedStatement stmt;
		
		try {
			Class.forName("org.postgresql.Driver");
			con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/username", "username", "password");
			con.setAutoCommit(false);
			String sql = "insert into bank_account_information" + "(accName,accNum,SSN,balance) values" + "(?,?,?,?)";
			stmt = con.prepareStatement(sql);
			stmt.setString(1, accName);
			stmt.setLong(2, accNumber);
			stmt.setString(3, SSN);
			stmt.setDouble(4, balance);
			int rows = stmt.executeUpdate();
			con.commit();
			
			System.out.println("rows effected: " + rows);
            con.close();
            stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public void depositMoney(double amountTodeposit) {
		Connection con;
		PreparedStatement stmt;
		if (amountTodeposit > 0) {
			balance += amountTodeposit;
			System.out.println("Total Available balance is :" + balance);
			try {
				Class.forName("org.postgresql.Driver");
				con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/username", "username",
						"password");
				con.setAutoCommit(false);
				String sql = "update bank_account_information" + " set balance = ? where accnum=?";
				stmt = con.prepareStatement(sql);
				stmt.setDouble(1, balance);
				stmt.setLong(2, accNumber);
				int rows = stmt.executeUpdate();
				con.commit();
				System.out.println("rows effected: " + rows);
				con.close();
	            stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			System.out.println("please enter amount greater than zero");
		}

	}

	public void withdrawMoney(double amountToWithdraw) {
		Connection con;
		PreparedStatement stmt;
		try {
			Class.forName("org.postgresql.Driver");
			con = DriverManager.getConnection("jdbc:postgresql://localhost:5432/username", "username", "password");
			con.setAutoCommit(false);
			if (amountToWithdraw <= balance) {
				System.out.println("Withdrawing amunt is :" + amountToWithdraw);
				double newBalance = balance - amountToWithdraw;
				System.out.println("Total Available balance is :" + newBalance);
				String sql = "update bank_account_information" + " set balance = ? where accnum=?";
				stmt = con.prepareStatement(sql);
				stmt.setDouble(1, newBalance);
				stmt.setLong(2, accNumber);
				int rows = stmt.executeUpdate();
				con.commit();
				System.out.println("rows effected: " + rows);
				con.close();
	            stmt.close();
			} else {
				System.out.println("insufficient funds ..... enter diffeent amount to withdraw");
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void printStatement() {
      System.out.println("your bank account informationis :");
      System.out.println(toString());
	}

	@Override
	public String toString() {
		return "BankAccount [accName=" + accName + ", accNumber=" + accNumber + ", SSN=" + SSN + ", balance=" + balance
				+ "]";
	}

}

BankAccountTest.java

package jdbc_exercises;

public class BankAccountTest {
	public static void main(String[] args) {
		BankAccount bankAccount = new BankAccount("venkat", 2146957220);
		bankAccount.balance = 500;
		bankAccount.SSN = "21469572";
	    System.out.println(bankAccount.balance);
		bankAccount.depositMoney(200.0);
	    bankAccount.withdrawMoney(500.00);
		bankAccount.printStatement();
	}

}

Source Code: JDBC Transaction