Using INSERT, SELECT, UPDATE & DELETE Query in Java

Using INSERT, SELECT, UPDATE & DELETE Query

logo
Logo

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);

           

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

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

           

            // 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:

 

Post a Comment

0 Comments