1.

What is JDBC Connection? Explain steps to get JDBC database connection in a simple Java program.

Answer»

Loading the driver: At first, you need to load or register the driver before using it in the program. Registration must be done once in your program. You can register a driver by using any one of the two methods mentioned below:

  • Class.forName(): Using this, we load the driver’s class file into memory during runtime. It’s not required to use a new or creation of an object.

The below given example uses Class.forName() to load the Oracle driver:

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

The MySQL Connector/J version 8.0 library comes with a JDBC driver class: com.mysql.jdbc.Driver. Before Java 6, we had to load the driver explicitly using the statement given below:

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

However, this statement is no longer needed, because of a new update in JDBC 4.0 that comes from Java 6. As long as you place the MySQL JDBC driver JAR file into the classpath of your program, the driver manager can find and load the driver.

  • DriverManager.registerDriver(): DriverManager is a built-in Java class with a static member register. Here we will be calling the constructor of the driver class during compile time.

The below given example uses DriverManager.registerDriver() to register the Oracle driver:

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

For registering the MySQL driver, use the below-given CODE:

DriverManager.registerDriver(new com.mysql.jdbc.Driver(); );

Create the connections: 

  • After loading the driver into the program, establish connections using the code given below:
Connection con = DriverManager.getConnection(URL,user,password);

Here,

con: Reference to a Connection interface.

url: Uniform Resource Locator.

user: Username from which SQL command prompt is accessed.

password: Password from which SQL command prompt is accessed.

  • Url in Oracle can be created as follows:
String url = "jdbc:oracle:thin:@localhost:1521:xe";

Where oracle represents the database used, thin is the driver used, @localhost is the IP(Internet Protocol) address where the database is stored, 1521 is the PORT number and xe represents the service provider.

All 3 parameters given above are of string type and are expected to be declared by the programmer before the function call. Use of this can be REFERRED from the final code of an application.

  • Url in MySQL can be created as follows:
String url = "jdbc:mysql://localhost:3306/test1";

Where localhost represents hostname or IP address of the MySQL server, 3306 port number of the server and by default, it is 3306, test1 is the name of the database on the server.

Create a statement:

  • Once a connection establishment is done, you can interact with the database. The Statement, PreparedStatement, and CallableStatement JDBC interfaces will define the methods that permit you to send SQL COMMANDS and receive data from the database.
  • We can use JDBC Statement as follows:
Statement st = con.createStatement();

Here, con is a reference to the Connection interface used in the earlier step.

Execute the query:

  • Here, query means an SQL query. We can have various types of queries. A few of them are as follows:
    • Query for updating or inserting a table in a database.
    • Query for data retrieval.
  • The executeQuery() method that belongs to the Statement interface is used for executing queries related to values retrieval from the database. This method returns the ResultSet object which can be used to get all the table records.
  • The executeUpdate(sql_query) method of the Statement interface is used for executing queries related to the update/insert operation.

Example:

int m = st.executeUpdate(sql); if (m==1) System.out.println("Data inserted successfully : "+sql); else System.out.println("Data insertion failed");

Here SQL is the SQL query of string type.

Close the connection:

  • So finally we have sent the data to the location specified and now we are at the end of our task completion.
  • On connection closing, objects of Statement and ResultSet will be automatically closed. The close() method of the Connection interface is used for closing the connection.

Example:

con.close();

Implementation of JDBC Oracle database connection using a Java program:

import java.sql.*;import java.util.*;class OracleCon{ public static void main(String a[]) { //Creating the connection String url = "jdbc:oracle:thin:@localhost:1521:xe"; String user = "system"; String password = "123"; //Entering the data Scanner k = new Scanner(System.in); System.out.println("Enter employee Id"); int empid = k.nextInt(); System.out.println("Enter employee name"); String empname = k.next(); System.out.println("Enter employee address"); String address = k.next(); //Inserting data using SQL query String sql = "insert into employee values("+empid+",'"+empname+"','"+address+"')"; Connection con=null; try { DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); //Reference to connection interface con = DriverManager.getConnection(url,user,password); Statement st = con.createStatement(); int m = st.executeUpdate(sql); if (m == 1) System.out.println("Data inserted successfully : "+sql); else System.out.println("Data insertion failed"); con.close(); } catch(Exception ex) { System.err.println(ex); } }}

Implementation of JDBC MySQL database connection using Java program:

import java.sql.*; class MysqlCon{ public static void main(String args[]) { //Creating the connection String url = "jdbc:mysql://localhost:3306/test1"; String user = "system"; String password = "123"; try { Class.forName("com.mysql.jdbc.Driver"); //Reference to connection interface Connection con=DriverManager.getConnection(url,user,password); Statement st = con.createStatement(); //Displaying all the records of employee table ResultSet rs = st.executeQuery("select * from employee"); while(rs.next()) System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getString(3)); con.close(); } catch(Exception e) { System.out.println(e); } } }


Discussion

No Comment Found

Related InterviewSolutions