JAVA EE: Database CRUD

Showing posts with label Database CRUD. 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