How to Execute SQL Query in Java?

How to Execute SQL Query in Java?

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

          }

}

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

        }

        }

    }

}

That's how you can use SQL in Java. Now this is a simple example demonstrating the use of SQL. Further more there will be more lessons uploaded here. Stay Tuned.