JAVA EE: Java JDBC

Showing posts with label Java JDBC. Show all posts

Saturday, 6 September 2014

JDBC : JDBCRowSet Update- Insert-Delete Demo

Enjoying this Java & JDBC tutorial?

Introduction

In Java JDBC, JdbcRowSet is a connected RowSet that acts as a wrapper around a standard ResultSet. Unlike a basic ResultSet, a JdbcRowSet is scrollable and updatable by default, and it adheres to the JavaBeans component model.

In this guide, you will learn how to use JdbcRowSet to perform complete CRUD operations—inserting new rows, updating existing records, and deleting rows directly without writing explicit SQL INSERT, UPDATE, or DELETE statements.

1. Initialize and Configure JdbcRowSet

Create a JdbcRowSet instance using RowSetProvider and configure database connection parameters:

import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;

public class JdbcRowSetDemo {
    public static void main(String[] args) throws Exception {
        JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();
        rowSet.setUrl("jdbc:mysql://localhost:3306/testdb");
        rowSet.setUsername("root");
        rowSet.setPassword("password");
        rowSet.setCommand("SELECT id, name, salary FROM employee");
        rowSet.execute();
        
        // CRUD operations performed below...
    }
}

2. Inserting Records with JdbcRowSet

Move the cursor to the insert row buffer, set the column values, and call insertRow():

// Move cursor to insert row buffer
rowSet.moveToInsertRow();

rowSet.updateInt("id", 105);
rowSet.updateString("name", "David");
rowSet.updateDouble("salary", 75000.0);

// Insert into the database
rowSet.insertRow();
rowSet.moveToCurrentRow();
System.out.println("Record inserted successfully!");

3. Updating and Deleting Records

Navigate to the targeted row using scrolling methods (such as absolute(), next(), or last()), update or delete the row:

// Updating a record
rowSet.absolute(1); // Navigate to first record
rowSet.updateDouble("salary", 85000.0);
rowSet.updateRow();
System.out.println("Record updated successfully!");

// Deleting a record
rowSet.last(); // Navigate to the last record
rowSet.deleteRow();
System.out.println("Record deleted successfully!");

// Close resource
rowSet.close();

Watch More Java Tutorials

Click here to watch in Youtube : https://www.youtube.com/watch?v=2VM0K-p_sUI

Click the below Image to Enlarge

JDBCRowSet Update- Insert-Delete Demo

JDBCRowSetDemo.java

import java.sql.SQLException;

import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

public class JDBCRowSetDemo
{
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL      = "jdbc:mysql://localhost:3306/world";

    // Database credentials
    static final String USERNAME    = "root";
    static final String PASSWORD    = "root";

    public static void main( String[] args )
    {
        JDBCRowSetDemo jdbcRowSetDemo = new JDBCRowSetDemo();
        jdbcRowSetDemo.addOrUpdateOrDeleteCityInformation();

    }

    private void addOrUpdateOrDeleteCityInformation()
    {
        JdbcRowSet jdbcRowSet = null;
        try
        {
            /*
             * Using RowSetFactory create the JdbcRowSet object.
             */
            RowSetFactory rowSetFactory = RowSetProvider.newFactory();
            jdbcRowSet = rowSetFactory.createJdbcRowSet();

            /*
             * Set the JdbcRowSet properties [URL,username,password,command]
             */
            jdbcRowSet.setUrl(DB_URL);
            jdbcRowSet.setUsername(USERNAME);
            jdbcRowSet.setPassword(PASSWORD);

            /*
             * Sets the command property with a query that produces a ResultSet
             * object containing all the data in the table
             */

            jdbcRowSet.setCommand("select * from city where countrycode='AFG'");
            
            /*
             * The execute method does many things for you in the background:
             * 
             * 1.It makes a connection to the database using the values you
             * assigned to the url, username, and password properties.
             * 
             * 2.It executes the query you set in the command property.
             * 
             * 3.It reads the data from the resulting ResultSet object into the
             * jdbcRs object.
             */
            jdbcRowSet.execute();

            /*
             * Moves the cursor to the fifth row of rs and update the column
             * information
             */

            jdbcRowSet.absolute(3);
            jdbcRowSet.updateInt("Population", 100);
            jdbcRowSet.updateRow();
            System.out.println("3rd row population column is updated");

            /*
             * Moves cursor to the insert row
             */

            jdbcRowSet.moveToInsertRow();
            jdbcRowSet.updateInt(1, 5080);
            jdbcRowSet.updateString(2, "India");
            jdbcRowSet.updateString(3, "IND");
            jdbcRowSet.updateString(4, "TamilNadu");
            jdbcRowSet.updateInt(5, 200000);
            jdbcRowSet.insertRow();

            System.out.println("5080th row has been inserted into the city table");
            
            jdbcRowSet.absolute(1);
            jdbcRowSet.deleteRow();
            
            System.out.println("1st row has been deleted from the city table");     
            

        }
        catch( SQLException se )
        {
            se.printStackTrace();
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }

        finally
        {
            if( jdbcRowSet != null )
            {
                try
                {
                    jdbcRowSet.close();
                }
                catch( SQLException e )
                {
                    e.printStackTrace();
                }
            }
        }

    }
}

Output

3rd row population column is updated
5080th row has been inserted into the city table
1st row has been deleted from the city table

Environment Used

JDK version : 1.7.0_51
Mysql Server version : 5.6.19

To Download JDBCRowSetUpdateDemoApp Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCRowSetUpdateDemoApp.zip?attredirects=0&d=1

See also:

All JavaEE Viedos Playlist

All JavaEE Viedos

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial

Monday, 1 September 2014

JDBC : JDBCRowSet Event Handling Demo

Enjoying this Java & JDBC tutorial?

Subscribe to Ram N Java on YouTube

Introduction

One of the most powerful features of the RowSet technology in Java JDBC is its built-in event handling model. By adhering to the JavaBeans event architecture, a JdbcRowSet can notify registered listeners whenever the cursor moves, a row is modified, or the entire RowSet contents change.

In this guide, you will learn how to implement the RowSetListener interface and attach it to a JdbcRowSet to track and manage database events in real-time.

1. Understand the RowSetListener Interface

The RowSetListener interface provides three callback methods to handle different events:

cursorMoved(RowSetEvent event): Triggered whenever the cursor moves to a new position (e.g., calling next(), previous(), or absolute()).
rowChanged(RowSetEvent event): Triggered when an individual row is inserted, updated, or deleted.
rowSetChanged(RowSetEvent event): Triggered when a change affects the entire RowSet, such as executing a new query via execute().

2. Implement the Custom Listener

Create a class implementing RowSetListener to define custom actions when events fire:

import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;

public class MyRowSetListener implements RowSetListener {
    @Override
    public void cursorMoved(RowSetEvent event) {
        System.out.println("[EVENT] Cursor moved to another position.");
    }

    @Override
    public void rowChanged(RowSetEvent event) {
        System.out.println("[EVENT] A row has been modified, inserted, or deleted.");
    }

    @Override
    public void rowSetChanged(RowSetEvent event) {
        System.out.println("[EVENT] The entire RowSet content has changed.");
    }
}

3. Register Listener and Trigger Events

Attach your listener to the JdbcRowSet using addRowSetListener() and perform database actions to trigger the event callbacks:

import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;

public class JdbcRowSetEventDemo {
    public static void main(String[] args) throws Exception {
        JdbcRowSet rowSet = RowSetProvider.newFactory().createJdbcRowSet();
        rowSet.setUrl("jdbc:mysql://localhost:3306/testdb");
        rowSet.setUsername("root");
        rowSet.setPassword("password");
        rowSet.setCommand("SELECT id, name, salary FROM employee");

        // Register the event listener
        rowSet.addRowSetListener(new MyRowSetListener());

        // Triggers rowSetChanged event
        rowSet.execute();

        // Triggers cursorMoved events
        while (rowSet.next()) {
            System.out.println(rowSet.getInt("id") + " : " + rowSet.getString("name"));
        }

        // Clean up
        rowSet.close();
    }
}

Watch More Java Tutorials

Click here to watch in Youtube : https://www.youtube.com/watch?v=p8VzKEpG_KE

Click the below Image to Enlarge

JDBCRowSet Event Handling Demo

MyRowSetListener.java

import javax.sql.RowSetEvent;
import javax.sql.RowSetListener;

public class MyRowSetListener implements RowSetListener
{
    
    /*
     * A cursor movement.
     */
    @Override
    public void cursorMoved( RowSetEvent event )
    {
        System.out.println("Cursor Moved method is called in "+this.getClass().getName());
        System.out.println(event.toString());
    }


    /*
     * The update, insertion, or deletion of a row.
     */
    
    @Override
    public void rowChanged( RowSetEvent event )
    {
        System.out.println("Cursor Changed method is called in "+this.getClass().getName());
        System.out.println(event.toString());
    }


    /*
     * A change to the entire RowSet contents.
     */
    @Override
    public void rowSetChanged( RowSetEvent event )
    {
        System.out.println("RowSet changed method is called in "+this.getClass().getName());
        System.out.println(event.toString());
    }
}

JDBCRowSetEventHandlingDemo.java

import java.sql.SQLException;
import java.util.Scanner;

import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;

public class JDBCRowSetEventHandlingDemo
{
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL      = "jdbc:mysql://localhost:3306/world";

    // Database credentials
    static final String USERNAME    = "root";
    static final String PASSWORD    = "root";

    public static void main( String[] args )
    {
        JDBCRowSetEventHandlingDemo jdbcRowSetEventHandlingDemo = 
                                          new JDBCRowSetEventHandlingDemo();
        Scanner scanner = new Scanner(System.in);
        while( true )
        {
            System.out.print("Enter City Country Code :");
            String cityCountrycode = scanner.nextLine();

            if( cityCountrycode.equals("exit") )
            {
                break;
            }

            jdbcRowSetEventHandlingDemo.getCityInformation(cityCountrycode);

        }
        scanner.close();
    }

    private void getCityInformation( String cityCountrycode )
    {
        JdbcRowSet jdbcRowSet = null;
        try
        {
            /*
             * Using RowSetFactory create the JdbcRowSet object.
             */
            RowSetFactory rowSetFactory = RowSetProvider.newFactory();
            jdbcRowSet = rowSetFactory.createJdbcRowSet();

            /*
             * Set the JdbcRowSet properties [URL,username,password,command]
             */
            jdbcRowSet.setUrl(DB_URL);
            jdbcRowSet.setUsername(USERNAME);
            jdbcRowSet.setPassword(PASSWORD);

            /*
             * Sets the command property with a query that produces a ResultSet
             * object containing all the data in the table
             */

            jdbcRowSet.setCommand("select * from city where countrycode=?");
            jdbcRowSet.setString(1, cityCountrycode);

            /*
             * The execute method does many things for you in the background:
             * 
             *  1.It makes a connection to the database using the values you assigned
             *  to the url,username, and password properties.
             *    
             *  2.It executes the query you set in the command property.
             *  
             *  3.It reads the data from the resulting ResultSet object into the jdbcRs object.
             *  
             */         
            jdbcRowSet.execute();

        
            /*
             * Adding Listener to the jdbcRowSet
             */
            
            MyRowSetListener myRowSetListener = new MyRowSetListener();
            jdbcRowSet.addRowSetListener(myRowSetListener);
            
            /*
             *  Iterate the jdbcRowSet and get each row information
             *  of city table.
             */
            
            while( jdbcRowSet.next() )
            {
                int id = jdbcRowSet.getInt(1);
                String name = jdbcRowSet.getString(2);
                String countryCode = jdbcRowSet.getString(3);
                String district = jdbcRowSet.getString(4);
                int population = jdbcRowSet.getInt(5);

                /*
                 * Display values
                 */
                System.out.print("ID: " + id);
                System.out.print(", Name: " + name);
                System.out.print(", CountryCode: " + countryCode);
                System.out.print(", District: " + district);
                System.out.println(", Population: " + population);
            }
            
        }
        catch( SQLException se )
        {
            se.printStackTrace();
        }
        catch( Exception e )
        {
            e.printStackTrace();
        }   
        
        finally
        {
            if(jdbcRowSet!=null)
            {
                try
                {
                    jdbcRowSet.close();
                }
                catch( SQLException e )
                {
                    e.printStackTrace();
                }
            }
        }

    }
}

Output

Enter City Country Code :ind
Cursor Moved method is called in MyRowSetListener
javax.sql.RowSetEvent[source=com.sun.rowset.JdbcRowSetImpl@87c06e0]
ID: 1024, Name: Mumbai (Bombay), CountryCode: IND, District: Maharashtra, Population: 10500000
Cursor Moved method is called in MyRowSetListener
javax.sql.RowSetEvent[source=com.sun.rowset.JdbcRowSetImpl@87c06e0]
ID: 1025, Name: Delhi, CountryCode: IND, District: Delhi, Population: 7206704
Cursor Moved method is called in MyRowSetListener
javax.sql.RowSetEvent[source=com.sun.rowset.JdbcRowSetImpl@87c06e0]
ID: 1026, Name: Calcutta [Kolkata], CountryCode: IND, District: West Bengali, Population: 4399819
Cursor Moved method is called in MyRowSetListener
javax.sql.RowSetEvent[source=com.sun.rowset.JdbcRowSetImpl@87c06e0]

--
--

Environment Used

JDK version : 1.7.0_51
Mysql Server version : 5.6.19

To Download JDBCRowSetEventHandlingDemoApp Project Click the below link

https://sites.google.com/site/javaee4321/jdbc/JDBCRowSetEventHandlingDemoApp.zip?attredirects=0&d=1

See also:

Servlets Tutorial

All Design Patterns Links

JDBC Tutorial