Execute SQL Query [INSERT, UPDATE, DELETE AND SELECT ] in Java

byAnkeet Chamlagain -
0

How to Execute SQL Query in Java?

explore java logo

Introduction:

SQL Query is the database query in which the Java API and database communicate. It is only possible by using SQL Query. SQL Query can be executed using Statement or PreparedStatement and the methods are;

a) executeUpdate()
b) executeQuery()
c) execute()

executeUpdate():
It is used to execute the action queries like INSERT, UPDATE and DELETE. It returns the number of rows affected.
For example,

// using statement
String sql= “INSERT INTO tbl_person(name,address,phone) values (‘abc’, ‘xyz’, 123456)”;
Statement st=conn.createStatement();
int count=st.executeUpdate(sql);
System.out.println(“No. of rows inserted: “+count);

// using prepared statement
String sql= “INSERT INTO tbl_person(name,address,phone) values (?,?,?)”;
PreparedStatement pst=conn.prepareStatement(sql);
pst.setString(1,’abc’);
pst.setString(2, ‘xyz’);
pst.setInt(3,12345);
int count=pst.executeUpdate();
System.out.println(“No. of rows inserted: “+count);

executeQuery():
It is used to execute sql select query. It returns the ResultSet of the database table.
For example,

      // using statement
String sql=”SELECT *FROM tbl_person”;
Statement st=conn.createStatement();
ResultSet res=st.executeQuery(sql);

	//using prepared statement
String sql=”SELECT *FROM tbl_person”;
PreparedStatement pst=conn.prepareStatement(sql);
ResultSet res=pst.executeQuery();

execute():
It can be used to execute any sql queries. It returns ‘true’ after it executes sql SELECT query and ‘false’ after it executes other queries. It can be useful when we do  not know which query we are going to use.
For example,
      public void test(String sql){
          Statement st=conn.createStatement()a;
          if(st.execute(sql)==true){
                   ResultSet res=st.executeQuery();
          }
          else{
                   System.out,println(“Operation Succesfull”);
          }
}

A perfect example to demonstrate the INSERT AND SELECT query.
import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
import java.sql.*;
public class UserInputFromFrame implements ActionListener {
    JFrame f,f1;
    JLabel l1,l2,l3;
    JTextField t1,t2,t3;
    JButton b,b2;
    JTable table;
    JScrollPane jp;
    UserInputFromFrame(){
        f=new JFrame();
        f.setSize(250,300);
        f.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        f.setLayout(new FlowLayout());
       
        l1=new JLabel("Name");
        l2=new JLabel("Address");
        l3=new JLabel("Cell No");
       
        t1=new JTextField(20);
        t2=new JTextField(20);
        t3=new JTextField(20);
       
        b=new JButton("Save");
        b2=new JButton("Show Data");
        b.addActionListener(this);
        b2.addActionListener(this);
       
        f.add(l1);
        f.add(t1);
        f.add(l2);
        f.add(t2);
        f.add(l3);
        f.add(t3);
        f.add(b);
        f.add(b2);
       
        f.setVisible(true);
  
    }
   
    public static void main(String[] args) {
        new UserInputFromFrame();
       
    }
    public void actionPerformed(ActionEvent e){
       
       
        String name=t1.getText();
        String address=t2.getText();
        String cellno=t3.getText();
       
        String url="jdbc:mysql://localhost:3306/java_db";
        String uname="root";
        String pass="";
       
     if(e.getSource()==b){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn=DriverManager.getConnection(url, uname, pass);
           String sql="INSERT INTO tb1_person(name,address,cellno)VALUES(?,?,?)";
           
            PreparedStatement pst=conn.prepareStatement(sql);
            pst.setString(1,name);
           pst.setString(2,address);
           pst.setString(3,cellno);
          
          // pst.execute();
           if(pst.execute()==false){
               t1.setText("");
               t2.setText("");
               t3.setText(""); 
           }
 
             conn.close();
           
        }
        catch(Exception ex){
            System.out.println(ex);
        }
     }

     else if(e.getSource()==b2){
         f1=new JFrame("Show records");
         f1.setSize(400,300);
         f1.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
   
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection conn=DriverManager.getConnection(url,uname,pass);
            String sql="SELECT *FROM tb1_person";
            Statement st=conn.createStatement();
            ResultSet rs=st.executeQuery(sql);
            String cols[]={"ID","NAME","ADDRESS","CELL NO"};
           
            int c=0;
            while(rs.next()){
                c++;
            }
            Object [][]data=new Object[c][4];
            rs.beforeFirst();
            int row=0;
            while(rs.next()){
                data[row][0]=rs.getString("id");
                data[row][1]=rs.getString("name");
                data[row][2]=rs.getString("address");
                data[row][3]=rs.getString("cellno");
                row++;
              
              table=new JTable(data,cols);
              jp=new JScrollPane(table);
              f1.add(jp);
              f1.setVisible(true);
               
            }
             conn.close();
           
        }
        catch(Exception ee){
            System.out.println(ee);
        }
        }
    }
}


***************************************

Using INSERT, SELECT, UPDATE & DELETE Query

Introduction:

In the previous lesson we learned about how we can execute the SQL Query in the Java Program. And also the different types of  JDBC Drivers. Now let us use the sql query and write the program.

The requirement for implementing the SQL Query are;

a) First of all Create the database named “java_db”.

    Command in XAMPP to create database is;

    CREATE DATABASE java_db;

b) Now access the database.

    To access the database in XAMPP write the below command;

    Use java_db;

c) Now create the table inside that database.

    To create table use the below database command in XAMPP.

    create table tbl_person(

        id int auto_increment primary key,

        name varchar(40) not null,

        address varchar(100) not null,

        cellno varchar(15) not null

        );

Let’s Begin:

First of all let us see the INSERT and SELECT Query at once;

Source Code:

package DatabaseDemo;

import java.awt.*;

import javax.swing.*;

import java.awt.event.*;

import java.sql.*;

public class UserInputFromFrame implements ActionListener {

    JFrame f,f1;

    JLabel l1,l2,l3;

    JTextField t1,t2,t3;

    JButton b,b2;

    JTable table;

    JScrollPane jp;

    UserInputFromFrame(){

        f=new JFrame();

        f.setSize(250,300);

        f.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);

        f.setLayout(new FlowLayout());

        l1=new JLabel("Name");

        l2=new JLabel("Address");

        l3=new JLabel("Cell No");


        t1=new JTextField(20);

        t2=new JTextField(20);

        t3=new JTextField(20);

       

        b=new JButton("Save");

        b2=new JButton("Show Data");

        b.addActionListener(this);

        b2.addActionListener(this);

        f.add(l1);

        f.add(t1);

        f.add(l2);

        f.add(t2);

        f.add(l3);

        f.add(t3);

        f.add(b);

        f.add(b2);

        f.setVisible(true); 
    }

    public static void main(String[] args) {

        new UserInputFromFrame();   

    }

    public void actionPerformed(ActionEvent e){

        String name=t1.getText();

        String address=t2.getText();

        String cellno=t3.getText();

        String url="jdbc:mysql://localhost:3306/java_db";

        String uname="root";

        String pass="";

     if(e.getSource()==b){

        try{

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

            Connection conn=DriverManager.getConnection(url, uname, pass);

            String sql="INSERT INTO tbl_person(name,address,cellno)VALUES(?,?,?)";

            PreparedStatement pst=conn.prepareStatement(sql);

            pst.setString(1,name);

           pst.setString(2,address);

           pst.setString(3,cellno);

          // pst.execute();

           if(pst.execute()==false){

               t1.setText("");

               t2.setText("");

               t3.setText(""); 

           }

             conn.close();   

        }

        catch(Exception ex){

            System.out.println(ex);

        }

     }

     else if(e.getSource()==b2){

         f1=new JFrame("Show records");

         f1.setSize(400,300);

         f1.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);

        try{

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

            Connection conn=DriverManager.getConnection(url,uname,pass);

            String sql="SELECT *FROM tbl_person";

            Statement st=conn.createStatement();

            ResultSet rs=st.executeQuery(sql);

            String cols[]={"ID","NAME","ADDRESS","CELL NO"};

           

            int c=0;

            while(rs.next()){

                c++;

            }

            Object [][]data=new Object[c][4];

            rs.beforeFirst();

            int row=0;

            while(rs.next()){

                data[row][0]=rs.getString("id");

                data[row][1]=rs.getString("name");

                data[row][2]=rs.getString("address");

                data[row][3]=rs.getString("cellno");

                row++;

              

              table=new JTable(data,cols);

              jp=new JScrollPane(table);

              f1.add(jp);

              f1.setVisible(true);  

            }

             conn.close();   

        }

        catch(Exception ee){

            System.out.println(ee);

        }

        }

    }

}

Output:

Inserting the data:

insert data
Showing the data:

select data

Now let us UPDATE the first data which kinda looks non-sense.

Source Code:

 package DatabaseDemo;

import java.util.*;

import java.sql.*;

public class UpdateRecordsFromUserInput {

    public static void main(String[] args) {

        String name,address,cellno;

        int id;

        Scanner input=new Scanner(System.in);

        System.out.println("Enter ID");

        id=input.nextInt();

        System.out.println("Enter your name: ");

        name=input.next();

        System.out.println("Enter address: ");

        address=input.next();

        System.out.println("Cell Number: ");

        cellno=input.next();


        String url="jdbc:mysql://localhost:3306/java_db";

        String username="root";

        String pass="";

         try{

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

            Connection conn=DriverManager.getConnection(url,username,pass);

            // using prepared statement

            String sql="UPDATE tbl_person set name=?,address=?,cellno=? WHERE id=?";

           // Statement st=conn.createStatement();

           // st.execute(sql);

           PreparedStatement pst=conn.prepareStatement(sql);

           pst.setString(1,name);

           pst.setString(2,address);

           pst.setString(3,cellno);

           pst.setInt(4,id);

           pst.execute();

            conn.close();

            }

        catch(Exception e){

            System.out.println(e);

        }    

    }  

}

Output:

update data

Now DELETE the record:

After updating the record, the records seems like this. So let us delete the record which id is 2.

updated data

Source Code:

  /*

To delete the record of given id.

 */

package DatabaseDemo;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.util.Scanner;

public class DeleteRecordsOfGivenId {

     public static void main(String[] args) {

        Scanner input=new Scanner(System.in);

        System.out.println("Enter ID: ");

        int id=input.nextInt();

        String url="jdbc:mysql://localhost:3306/java_db";

        String username="root";

        String pass="";

         try{

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

            Connection conn=DriverManager.getConnection(url,username,pass);

           

     //       String sql="INSERT INTO tb1_person(name,address,cellno)"

       //             + "VALUES('"+name+"','"+address+"','"+cellno+"')";

           

            // using prepared statement

            String sql="DELETE FROM tbl_person WHERE id=?";

           // Statement st=conn.createStatement();

           // st.execute(sql);

           PreparedStatement pst=conn.prepareStatement(sql);

           pst.setInt(1,id);

           pst.execute();

             conn.close();

            }

        catch(Exception e){

            System.out.println(e);

        }  

    }

}

Output:

 

delete data
after deleted data
***********

Tags:

Post a Comment

Previous Post Next Post