Java Project using JDBC and MYSQL

Java Project using JDBC and MYSQL

Here is the list of the available project in Java Using JDBC and MYSQL

New Projects in Java Using JSP, Servlet, JDBC and MYSQL 

What Is JDBC?

JDBC is a Java Database Connectivity that provides an API to connect the client with the database. In java, when we work with an application that needs access to the database, we need some interface to connect our Java programming with the database. So, for this, we use JDBC. (Here, API stands for Application Programming Interface which lets us access data and functions of another application, services, or OS).

What was before JDBC?

Before JDBC we had ODBC that stands for Oracle Database Connectivity which was platform-specific and was written in C language. So, Sun Microsoft System on February 19, 1997, released JDBC and that has its JDBC API.

So, Java provides us some classes for JDBC which are present in the java.sql or javax.sql package. Here, JDBC API uses JDBC Drivers.

Let’s see the JDBC Drivers one by one

  • JDBC-ODBC Bridge driver.
  • Native API driver.
  • Network Protocol driver.
  • Thin driver.

JDBC-ODBC Bridge Driver: It uses ODBC Driver that is in the class sun.jdbc.odbc.JdbcOdbcDriver. It is removed in JDK 8.

Native API driver: It converts the JDBC methods into Native database API. The disadvantage here is we need to install the native drivers.

Network Protocol driver: It converts the JDBC method to the vendor-specific database using a Middleware application.

Thin Driver: It converts the JDBC method to the vendor-specific database using thin drivers.

There are certain steps to be followed to connect using JDBC (In this we will connect our Program with MYSQL database).

  1. Download the drivers.
  2. Register the Drivers.
  3. Create a connection.
  4. Create a statement Object.
  5. Execute the queries.
  6. At last close the connection.

If you are creating Maven Project you just need to add the following dependencies inside pom.xml file.

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.21</version>
</dependency>

If you are adding jar files just follow these steps.

  • Create a project in eclipse (as we normally create it)
  • Add the jar file for mysql-connector.jar(open directory of MySQL where you have installed >open Connector >copy mysql-connector-java8.0.21 into lib folder)

Register the Driver

We have a method forName() of Class class which is a static method and is used to register the drivers. It loads the drivers. As it is a static method so, we are calling with the Class Name.

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

This will load the MYSQL drivers.

Create a Connection

We can create a connection by calling the getConnection () method of DriverManager class. There are the following 3 ways we can call. A connection is an interface to keep the session between java application & database. It provides some methods:

  • getConnection(String url)
  • getConnection(String url, Properties info)
  • getConnection(String url, String username, String password)
Connection con=DriverManager.getConnection(  
"jdbc:mysql://localhost:3306/DbName","system","password");

Create a Statement Object

Now, create the Object of Statement. Here Statement is an interface that contains all the methods to execute queries with the database.

Statement stmt=con.createStatement();

Suppose, if we have parameterized queries we use PreparedStatement Interface.

PreparedStatement pstmt=con.prepareStatement("insert into TABLE_DEMO values(?,?)"); 
pstmt.setString(1,"Nicolas");
pstmt.setInt(2,21);

Execute the Query

In this step, we execute the query using executeQuery() method that returns the object of ResultSet.

ResultSet rs=stmt.executeQuery("select * from TABLE_DEMO");   
while(rs.next()){  
System.out.println(rs.getString(1)+" "+rs.getInt(2));  
}

Here, we fetch the data from the database.

Close the Connection

This is the last step to close our Connection object and ResultSet Object. It is a good practice to close the connections. We used the method close().

con.close();

Example for connecting Database

Here, we will take a look at a simple example by following the above steps.

import java.sql.*;  
class MyJDBCConnect{  
public static void main(String args[]){  
try{  
Class.forName("com.mysql.jdbc.Driver");  
Connection con=DriverManager.getConnection(  
"jdbc:mysql://localhost:3306/mydb","root","root");  
Statement stmt=con.createStatement();  
ResultSet rs=stmt.executeQuery("select * from mytable");  
while(rs.next())  
System.out.println(rs.getInt(1)+"  "+rs.getString(2));  
con.close();  
}catch(Exception e){ System.out.println(e);}  
}  
}