Java Database Connectivity (JDBC)

 

What is SQL? 

  • SQL stands for Structure Query Language. It is a kind of language (Most like to English).
  • SQL is most popular and widely used open source Database management system (DBMS) language.
  • SQL lets you access and manipulate databases.
  • It is simple to setup and use.
  • SQL is 4th generation language.
  • SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.

What can SQL do?

  • SQL can execute queries against a database
  • SQL can retrieve data from a database
  • SQL can insert records in a database
  • SQL can update records in a database
  • SQL can delete records from a database
  • SQL can create new databases
  • SQL can create new tables in a database
  • SQL can create stored procedures in a database
  • SQL can create views in a database
  • SQL can set permissions on tables, procedures, and views

MYSQL Server: 

  • MYSQL is Database Management System (DBMS).
  • It is a relational database management system.

Features of MYSQL: 

  1. MYSQL is a database system used on the web.
  2. MYSQL is very fast, reliable, and easy to use.
  3. It is a database system runs on a server.
  4. It works on number of platforms.
  5. It is developed, distributed, and supported by Oracle Corporation.

JDBC:

JDBC stands for Java Database Connectivity. JDBC basically connect Java Application to database server. JDBC is a standard Java API for handling database related activities. In java, there is a package java.sql having number of classes for database related programming. Java.sql includes java.sql.DriverManager class and two interfaces java.sql.Driver and java.sql.Connection.

Characteristics of JDBC: 

  • JDBC is a Structured Query Language (SQL) – level API, which is very popular for Relational Database Management System.
  • JDBC is compactible with the most of the popular database management systems, namely Oracle DB, MYSQL, Sybase, Microsoft SQL, etc.
  • For executing SQL statement from java program, it will execute to database server through JDBC Driver. Now for different database server there are different JDBC drivers available.
  • For e.g. -    To connect with MYSQL there should be a MYSQL compactible JDBC Driver installed in our system.

Advantages of JDBC: -

  1. Write once, run anywhere (Platform independent)
  2. Object – relational mapping
  3. Network independent 
  4. Database independent 

Types of JDBC drivers: -

JDBC driver is a software component which enables java application to interact with the database management systems.

I.  JDBC-ODBC bridge driver

  • For connecting to a database, the JDBC-ODBC bridge driver uses ODBC driver.
  • JDBC method calls ae converted into ODBC function cell by using JDBC-ODBC bridge driver.
  • JDBC-ODBC driver uses third-party API.
  • It doesn’t require internet connection means that we can’t access through server. 

II.  Native-API driver

  • It uses the client sides libraries of the database.
  • It uses native C or C++ API.
  • The Native-API driver is used to convert JDBC class to native calls of the database API.
  • It doesn’t require internet connection means we can’t access through server.
  • It is partially in java language.

III. Network Protocol driver

  • It uses application server which converts JDBC method calls into the vender- specific database protocol.
  • It needs internet connection and having access to remote server.
  • It is fully in java language.

IV. Thin driver

  • The thin driver job is to convert JDBC method calls into the vender- specific database protocol.
  • It doesn’t need any API.
  • It doesn’t require internet connection.

JDBC Steps: 

To invoke JDBC into program there are five steps.
  1. Load a JDBC driver.
  2. Create connections.
  3. Connect to the data source.
  4. Execute SQL statements.
  5. Map the result to data structures. 

Some JDBC components:

Connection to database: 

1. Load JDBC driver

  • For MySQL Database:

         Class.forName("com.mysql.cj.jdbc.Driver").newInstance();

  • For Oracle Database:

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

2. Establishing Connection: 

Create an object of Connection class. Grab a connection with the help of method getConnection() by using DriverManager.



3. Types of statements: 

There are three types of statements.

  • Commonly used methods in statement class: 



  • Execution of SQL statements:



  • Execution of Prepared Statements: 




4. ResultSet: 

Result set gives access to a data generated by executing a statement. It maintains a cursor pointing to its current row of data. next() method is used to takes cursor to the next row.

  • Commonly used methods in ResultSet class: 

  • Execution of ResultSet: 



5. JDBC Exceptions: 

SQLException is a class which provides information about errors while accessing database.

  • Execution of SQLException: 

Sample codes:

Code for connecting to SQL Database


Sample code for Statement


Sample code for ResultSet

(sample for SQLException is given in above all sample codes)

Comments

Post a Comment