DATABASE CONNECTIVITY WITH JDBC

Java JDBC ( JDBC drivers) is a java API to connect and execute query with the database. JDBC API uses jdbc drivers to connect with the database.

Why use JDBC

Before JDBC, ODBC API was the database API to  connect and execute query with the database. But ODBC API uses ODBC driver which is written in C language (i.e. platform dependent and unsecured). That is why java has defined its own API (JDBC API) that uses JDBC drivers (written in java language).

DID YOU KNOW  

• How to connect Java application with oracle and MYSQL database using JDBC ?
• What is the different between Statement and PreparedStatement interface ?
• How to print total number ao tables and view of a database using JDBC ?
• How to store and retrieve images from Oracle database using JDBC ?
• How to store and retrieve files from Oracle database using JDBC ?

What is API 

Java_APIs

(Application programming interface) is a document that contains description of all the features of a product or software.It represents classes and interface that software programs can follow to communicate with each other. An API can be created for application, libraries, operating system,etc.
In this JDB tutorial, we will learn 

1. JDBC Drivers
2. 5 Steps to connect to the database
3. Connectivity with Oracle using JDBC
4. Connectivity with MySQL using JDBC
5. Connectivity with Access without DNS
6. Driver Manager Class
7. Connection Interface 
8. Statement Interface 
9. Result Set Interface
10. Prepared Statement Interface
11. Result Set Meta Data Interface
12. Database Meta Data Interface
13. Storing Image In Oracle
14. Retrieving Image In Oracle
15. Storing File In Oracle
16. Retrieving File From Oracle
17. Callable Statement
18. Transaction management Using JDBC
19. Batch Statement Using JDBC

JDBC RowSet

Let's see the working of new JDBC RowSet interface.
This chapter provide an example of how to create a simple JDBC application. This will show you how to open a database connection, execute a SQL query , and display the result.
All the steps mentioned in this template example, would be explained in subsequent chapter of this tutorial. 

Creating JDBC Application  

Import the packages:

Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql. will suffice.

Resister the JDBC driver:

Require that you initialize a driver so you can open communication channel with the database.

Open a connection:

Requires using the DriverManager .getConnection() method to create a Connection object, which represent a physical connection with the database.

Execute a query:

Require using an object of type Statement for building and submitting an SQL statement to the database.

Extract data from result set:

Require that you use the appropriate ResultSet.getXXX() method to retrieve the data from the resulr set.

Clean up the environment:

Requires explicitly closing all database resource versus relying on the JVM's garbage collection.

Sample Code:

This sample example can serve a template when you need to create your own JDBC application in the future.

This sample code has been written based on the environment and database setup done in the previous chapter.

Copy and paste the following example in FirstExample.java,compile and run as follow-

//STEP 1. Import required packages

import java.sql.;

public class FirstExample {

  // JDBC driver name and database URL

  static final String JDBC_DRIVER="com.mysql.jdbc.Driver";

  static final String DB_URL="jdbc:mysql://localhost/EMP";

  

  //  Database credentials 

  static final String USER ="username";

  static final String PASS ="password";

  

  public static void main(String[] args){

  Connection conn = null;

  Statement stmt = null;

  try{

    //STEP 2. Register JDBC driver 

  class.forName("com.mysql.jdbc.Driver");

   //STEP 3. Open a connection

  System.out.println("Connecting to database...");

  conn = DriverManager.getConnection(DB_URL,USER,PASS);

   //STEP 4. Execute a query

   System.out.println("Creating to database...");

   stmt=conn.createStatement();

   String sql;

   sql = "SELECT id, first,last,age FROM Employees";

   ResultSet rs = stmt.executeQuery(sql);

    //STEP 5. Extract data form result set 

     while(rs.next()){

//Retrieve by column name 

      int id = rs.getInt("id");

      int age = rs.getInt("age");

      String first = rs.getString("frist");

      String last = rs.getString("last");

    //Display values

      System.out.print("ID: " + id);

  System.out.print(", Age: " + age);

  System.out.print(", First: " + first);

  System.out.print(", Last: " + last);

}

 

//STEP 6: Clean-up environment 

   rs.close();

   stmt.close();

}

catch(SQLExcetion se)

{

//Handle errors for JDBC

se.printStackTrace();

}

finally

{

//Finally block used to close resources

try{

    if(conn!=null)

   conn.close();

}

catch(SQLExpection se2){

}//nothing we can do

try{

if(conn!=null)

conn.close();

}

catch (SQLException se) {

se.printStackTrace();

}//End finallay try

}//end try

System.out.println("Goodbye!");

  }//End Main

}//end FirstFxample

5 Step to connect to the database in java

1. Register the driver class 
2. Create the connection object
3. Create the Statement object
4. Execute the query
5. Close the connection object

Register the drive class  

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

Syntax of forName() method

public static void forName(String className)throw ClassNotFoundException

Example to register the OracleDriver class....

Class.forName("oracle.jdbc.driver.OracleDriver"); 

Create the Connection object

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

Syntax of getConnection() method

public static connection getConnection(String url)throw SQLException

public static connection getConnection(String url,String name,String password)throw SQLException

Example to establish connection with the Oracle database.

Connection......

con=DriveManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","password");

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 to createStatement() method

public Statement createStatement()throws SQLException

Example to create the statement() method

public Statement stm=con.createStatement();

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 executeQuery() method

ResultSet rs=stm.executeQuery("select*from emp");

while(rs.next()){

 System.out.println(rs.getInt(1)+"rs.getString");

} 

Close the connection object

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

Syntax of close() method

public void close()throw SqlException

example to close connection

con.close();  

Thanks for reading, If you want to become a programmer then you can also read Python,C++,C language ..

I think you got your doubt in this article if you like then you give me comment and if any problem you can ask me.