naveenkumar j
naveenkumar j's Blog

naveenkumar j's Blog

How to connect with SQL database using java?(JDBC connectivity)

How to connect with SQL database using java?(JDBC connectivity)

JDBC Connectivity tutorial using java programming language.

naveenkumar j's photo
naveenkumar j
·Jun 23, 2022·

3 min read

Subscribe to my newsletter and never miss my upcoming articles

prerequisites:

1) WAMPSERVER

2) Eclipse/Apache NetBeans IDE

The Java Database Connectivity (JDBC) API:

Java Database Connectivity (JDBC) is an application programming interface (API) which defines how a client may access a database. JDBC is like a bridge between a Java application and a database. Let's see a step-by-step implementation of JDBC connectivity in java.

step 1: open WAMPSERVER and click phpmyadmin option. By default the username is root and password is empty ("") and click go option.

image.png

image.png

step 2: open your favourite java IDE to type the following code.

CREATE DATABASE:(DatabaseCreation.java)


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

public class DatabaseCreation {
   static final String DB_URL = "jdbc:mysql://localhost/";
   static final String USER = "root";
   static final String PASS = "";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {              
         String sql = "CREATE DATABASE JAVADATABASE";
         stmt.executeUpdate(sql);
         System.out.println("Database created successfully...");         
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

image.png

CREATE TABLE:(CreateTable.java)

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

public class CreateTable {
   static final String DB_URL = "jdbc:mysql://localhost/JAVADATABASE";
   static final String USER = "root";
   static final String PASS = "";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {              
          String sql = "CREATE TABLE REGISTRATION " +
                   "(id INTEGER not NULL, " +
                   " first VARCHAR(255), " + 
                   " last VARCHAR(255), " + 
                   " age INTEGER, " + 
                   " PRIMARY KEY ( id ))"; 

         stmt.executeUpdate(sql);
         System.out.println("Created table in given database...");         
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

image.png

INSERTION:(Insertion.java)


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

public class Insertion {
   static final String DB_URL = "jdbc:mysql://localhost/JAVADATABASE";
   static final String USER = "root";
   static final String PASS = "";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {              
          String sql = "INSERT INTO REGISTRATION VALUES (1,'Naveen','kumar',20)"; 
          stmt.executeUpdate(sql);
          System.out.println("Value inserted in table successfully....");        
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

image.png

UPDATION:(UpdateTable.java)

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

public class UpdateTable {
   static final String DB_URL = "jdbc:mysql://localhost/JAVADATABASE";
   static final String USER = "root";
   static final String PASS = "";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {              
          PreparedStatement ps=conn.prepareStatement("update registration set id=? where first=?");
          ps.setInt(1,101);
          ps.setString(2,"Naveen");
          ps.executeUpdate();  
          System.out.print("Updation done successfully.......");
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

image.png

SELECTION:(Selection.java)

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

public class Selection {
   static final String DB_URL = "jdbc:mysql://localhost/JAVADATABASE";
   static final String USER = "root";
   static final String PASS = "";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {              
          String strSelect="select * from registration";
          System.out.println("The SQL statement is: "+strSelect+"\n");
          ResultSet rs=stmt.executeQuery(strSelect);
            while(rs.next()) {
                System.out.println(rs.getInt(1)+", "+rs.getString(2)+rs.getString(3)+", "+rs.getInt(4));
            }
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

image.png

DELETION:(Deletion.java)

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

public class Deletion {
   static final String DB_URL = "jdbc:mysql://localhost/JAVADATABASE";
   static final String USER = "root";
   static final String PASS = "";

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement();
      ) {              
          String sql="delete from registration where id='101'";
          stmt.executeUpdate(sql);
          System.out.println("Deletion done successfully.....");
      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

image.png

Did you find this article valuable?

Support naveenkumar j by becoming a sponsor. Any amount is appreciated!

See recent sponsors Learn more about Hashnode Sponsors
 
Share this