JDBC

JDBC stands for Java Database Connectivity. JDBC provides the customary Java API for interacting with database. JDBC will work like a mediator in between the Java language and the database. JDBC library and API each and every task related to database usage.

JDBC Architecture:
JDBC-API supports both 2-tier and 3-tier architectures for Database access. But, in generally JDBC architecture consists 2 layers
  •  JDBC API: This provides Application to JDBC Manager connection
  • JDBC Driver API: This supports the JDBC Manager to Driver connection.
          Following is the architectural diagram , which shows the location of the DriverManager with respect to the JDBC Driver and Java application.



Common JDBC components:
JDBC API provides the following classes and interfaces.
  • DriverManager: This category manages the list of info drivers. Matches association requests from the java application with the right info driver exploitation communication sub-protocol. the primary driver that acknowledges a definite sub-protocol beneath JDBC are accustomed establish a info association.
  •  Driver:  Driver is that the Interface. This interface handles the communications with the info server. we'll act directly with Driver objects terribly seldom. Instead, we have a tendency to use DriverManager objects, which is able to manages objects of this kind. It conjointly abstracts the main points related to operating with Driver objects.
  • Connection:  Connection is that the interface. This interface with all strategies for contacting a info. The association object represents communication context. i.e. all communication with info is thru association object solely.
  •  Statement:   Statement is that the interface. we tend to use objects created from this interface to submit the SQL statement to the info. Some derived interfaces settle for parameters additionally to capital punishment hold on procedures.
  •  ResultSet:   ResultSet is that the interface. This interface holds the information retrieved from info when we tend to capital punishment Associate in Nursing SQL question exploitation Statement objects. It acts as Associate in Nursing iterator to permit you to maneuver through its information.
  •  SQLException:   This category handles any error that happens in a very info application.
JDBC important steps to create application:
There are 5 common steps to remember to create an JDBC application. 
  •   First, Load the driver by using the forName() method which is available under the class Class.
Class.forName("com.mysql.jdbc.Driver");
  • Make a connection to the database by using the getConnection("URL","UserName","Password") providing by DriverManager class.
Connection conn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/jdbc","root","root");
  • Create a statement by using the Connection instance which is created in the previous step.
Statement st = conn.createStatement();
  • Executing the query in database by using the execute() or executeUpdate() methods. These methods are available under Statement instance.
String sql = "SELECT * FROM Person";
  • View & Modify the resulting records by using the ResultSet.
ResultSet rs = st.executeQuery(sql);

          Before going to create simple JDBC programs better you should know about the JDBC Architecture and JDBC various types of Drivers.
JDBC Example for inserting records into database table:
Here, we tend to area unit attending to produce a jdbc program to insert records into information table. To insert records in information table 1st we want to form information and a table within the information. For making information follow the below directions,

          Open MySQL command prompt --> Login into MySQL command prompt by entering the correct password of MySQL --> Execute the below query for creating the database,

CREATE database jdbc;

          For clarifying the database is created or not, execute the below query to display all the databases names,

SHOW databases;
         
          That will show all existing databases list within the MySQL prompt. There you'll check recently created information is existing therein list or not. If it's exists continue the below directions different wise execute the produce information question yet again.

          Once information creation completes, can use that information by execution the below question,


USE jdbc;

          After executing the use command, we will execute the create table command for creating the table with columns.

CREATE table PERSON(id integer(5),
                    firstname varchar(50),
                    lastname varchar(50),
                    age integer(3));

          For clarification of the table is created or not, Execute the below query to view all the tables list, 


SHOW tables;
         
          If your table is present in the list, that's fine. Otherwise execute the Create Table query once again.

          Now, we will move to write JDBC program for inserting the person details into database table,
package com.javatbrains.jdbc;

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

public class InsertPersonValues {
     static String URL = "jdbc:mysql://localhost:3306/jbdc";
     static String userName = "root";
     static String password = "root";

     public static void main(String[] args) throws ClassNotFoundException,
              SQLException {
          Connection conn = null;
          Statement stmt = null;

          try {
              Class.forName("com.mysql.jdbc.Driver");
              conn = DriverManager.getConnection(url, userName, password);
              stmt = conn.createStatement();
              String sql = "INSERT into Person (id,firstname,lastname,age) values(1,'Java','TBrains',10)";
              stmt.execute(sql);
          } catch (SQLException se) {
              System.out
                        .println("Exception while inserting the values into Person Table"     + se);
          } finally {
              stmt.close();
              conn.close();
          }
     }
}

Steps:
  • Load the driver by using Class.forName() method.  
  • Make the connection by using the DriverManager.getConnection() method.  
  • Provide the URL, username and passwords inside the getConnection method.  
  • Create the statement by using the conn.CreateStatement() method.  
  • Declare a query which is related to insert the records and assign to String.  
  • Execute the query by using the stmt.execute() method.  
  • Close all the connection which we opened.   
  • Execute the program to insert records into database table.
          If program executed successfully, open the database and and run the below query to view the record is inserted or not.
Select * from Person;         

Comments

Popular posts from this blog

Prime, Fibonacci and Factorial number with example in java

JNDI configuration for Tomcat 9 with Oracle

SessionFactory creation failed.java.lang.NoSuchFieldError: namingStrategy