Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Generic Functions to Generate INSERT, UPDATE & DELETE Oracle SQL Query

, 28 May 2014
Rate this:
Please Sign up or sign in to vote.
Introduction: This example shows how you can generate INSERT, UPDATE & DELETE SQL Query for Oracle DB. Here you’ll get three functions i.e. insertSQL(), updateSQL() & deleteSQL() to generate INSERT, UPDATE & DELETE SQL Query respectively. These three functions will return the respective query

Introduction:

This example shows how you can generate INSERT, UPDATE & DELETE SQL Query for Oracle DB. Here you’ll get three functions i.e. insertSQL(), updateSQL() & deleteSQL() to generate INSERT, UPDATE & DELETE SQL Query respectively. These three functions will return the respective query as a String. It also check for NULL or BLANK values, so that these will be automatically eliminated before making the query.

Quick Start:

insertSQL():

/**
 * <h1>Get INSERT SQL Query</h1>
 * <p>It is a generic function. It can be use for any DB Table</p>
 *
 * @author Debopam Pal, Software Developer, NIC, India.
 * @param tableName Table on which the INSERT Operation will be performed.
 * @param columnValueMappingForInsert List of Column & Value pair to Insert.
 * @return Final generated INSERT SQL Statement.
 */
public static String insertSQL(String tableName, Map<String, String> columnValueMappingForInsert) {
    StringBuilder insertSQLBuilder = new StringBuilder();

    /**
     * Removing column that holds NULL value or Blank value...
     */
    if (!columnValueMappingForInsert.isEmpty()) {
        for (Map.Entry<String, String> entry : columnValueMappingForInsert.entrySet()) {
            if(entry.getValue() == null || entry.getValue().equals("")) {
                columnValueMappingForInsert.remove(entry.getKey());
            }
        }
    }

    /* Making the INSERT Query... */
    insertSQLBuilder.append("INSERT INTO");
    insertSQLBuilder.append(" ").append(tableName);
    insertSQLBuilder.append("(");

    if (!columnValueMappingForInsert.isEmpty()) {
        for (Map.Entry<String, String> entry : columnValueMappingForInsert.entrySet()) {
            insertSQLBuilder.append(entry.getKey());
            insertSQLBuilder.append(",");
        }
    }

    insertSQLBuilder = new StringBuilder(insertSQLBuilder.subSequence(0, insertSQLBuilder.length() - 1));
    insertSQLBuilder.append(")");
    insertSQLBuilder.append(" VALUES");
    insertSQLBuilder.append("(");

    if (!columnValueMappingForInsert.isEmpty()) {
        for (Map.Entry<String, String> entry : columnValueMappingForInsert.entrySet()) {
            insertSQLBuilder.append(entry.getValue());
            insertSQLBuilder.append(",");
        }
    }

    insertSQLBuilder = new StringBuilder(insertSQLBuilder.subSequence(0, insertSQLBuilder.length() - 1));
    insertSQLBuilder.append(")");

    // Returning the generated INSERT SQL Query as a String...
    return insertSQLBuilder.toString();
}

updateSQL():

/**
 * <h1>Get UPDATE SQL Query</h1>
 * <p>It is a generic function. It can be use for any DB Table</p>
 *
 * @author Debopam Pal, Software Developer, NIC, India.
 * @param tableName Table on which the UPDATE Operation will be performed.
 * @param columnValueMappingForSet List of Column & Value pair to Update.
 * @param columnValueMappingForCondition List of Column & Value pair for WHERE clause.
 * @return Final generated UPDATE SQL Statement.
 */
public static String updateSQL(String tableName, Map<String, String> columnValueMappingForSet, Map<String, String> columnValueMappingForCondition) {
    StringBuilder updateQueryBuilder = new StringBuilder();

    /**
     * Removing column that holds NULL value or Blank value...
     */
    if (!columnValueMappingForSet.isEmpty()) {
        for (Map.Entry<String, String> entry : columnValueMappingForSet.entrySet()) {
            if(entry.getValue() == null || entry.getValue().equals("")) {
                columnValueMappingForSet.remove(entry.getKey());
            }
        }
    }

    /**
     * Removing column that holds NULL value or Blank value...
     */
    if (!columnValueMappingForCondition.isEmpty()) {
        for (Map.Entry<String, String> entry : columnValueMappingForCondition.entrySet()) {
            if(entry.getValue() == null || entry.getValue().equals("")) {
                columnValueMappingForCondition.remove(entry.getKey());
            }
        }
    }

    /* Making the UPDATE Query */
    updateQueryBuilder.append("UPDATE");
    updateQueryBuilder.append(" ").append(tableName);
    updateQueryBuilder.append(" SET");
    updateQueryBuilder.append(" ");

    if (!columnValueMappingForSet.isEmpty()) {
        for (Map.Entry<String, String> entry : columnValueMappingForSet.entrySet()) {
            updateQueryBuilder.append(entry.getKey()).append("=").append(entry.getValue());
            updateQueryBuilder.append(",");
        }
    }

    updateQueryBuilder = new StringBuilder(updateQueryBuilder.subSequence(0, updateQueryBuilder.length() - 1));
    updateQueryBuilder.append(" WHERE");
    updateQueryBuilder.append(" ");

    if (!columnValueMappingForCondition.isEmpty()) {
        for (Map.Entry<String, String> entry : columnValueMappingForCondition.entrySet()) {
            updateQueryBuilder.append(entry.getKey()).append("=").append(entry.getValue());
            updateQueryBuilder.append(",");
        }
    }

    updateQueryBuilder = new StringBuilder(updateQueryBuilder.subSequence(0, updateQueryBuilder.length() - 1));

    // Returning the generated UPDATE SQL Query as a String...
    return updateQueryBuilder.toString();
}

deleteSQL():

/**
 * <h1>Get DELETE SQL Query</h1>
 * <p>It is a generic function. It can be use for any DB Table.</p>
 *
 * @author Debopam Pal, Software Developer, NIC, India.
 * @param tableName Table on which the DELETE Operation will be performed.
 * @param columnValueMappingForCondition List of Column & Value pair for WHERE clause.
 * @return Final generated DELETE SQL Statement.
 */
public static String deleteSQL(String tableName, Map<String, String> columnValueMappingForCondition) {
    StringBuilder deleteSQLBuilder = new StringBuilder();

    /**
     * Removing column that holds NULL value or Blank value...
     */
    if (!columnValueMappingForCondition.isEmpty()) {
        for (Map.Entry<String, String> entry : columnValueMappingForCondition.entrySet()) {
            if(entry.getValue() == null || entry.getValue().equals("")) {
                columnValueMappingForCondition.remove(entry.getKey());
            }
        }
    }

    /* Making the DELETE Query */
    deleteSQLBuilder.append("DELETE FROM");
    deleteSQLBuilder.append(" ").append(tableName);
    deleteSQLBuilder.append(" WHERE");
    deleteSQLBuilder.append(" ");

    if (!columnValueMappingForCondition.isEmpty()) {
        for (Map.Entry<String, String> entry : columnValueMappingForCondition.entrySet()) {
            deleteSQLBuilder.append(entry.getKey()).append("=").append(entry.getValue());
            deleteSQLBuilder.append(" AND ");
        }
    }

    deleteSQLBuilder = new StringBuilder(deleteSQLBuilder.subSequence(0, deleteSQLBuilder.length() - 5));

    // Returning the generated DELETE SQL Query as a String...
    return deleteSQLBuilder.toString();
}

How to Call:

// Preparing to call updateSQL() function...to generate UPDATE SQL...
Map<String, String> columnValueMappingForSet = new HashMap<String, String>();
columnValueMappingForSet.put("FIRST_NAME", "'DEBOPAM'");
columnValueMappingForSet.put("LAST_NAME", "'PAL'");
columnValueMappingForSet.put("DESIGNATION", "'Software Developer'");
columnValueMappingForSet.put("ORGANIZATION", "'NIC'");

Map<String, String> columnValueMappingForCondition = new HashMap<String, String>();
columnValueMappingForCondition.put("EMPLOYEE_NO", "201400002014");

// Getting UPDATE SQL Query...
String updateSQL = updateSQL("EMPLOYEE", columnValueMappingForSet, columnValueMappingForCondition);

System.out.println(updateSQL);

OUTPUT:

UPDATE EMPLOYEE
SET
ORGANIZATION='NIC',
FIRST_NAME='DEBOPAM',
DESIGNATION='Software Developer',
LAST_NAME='PAL'
WHERE
EMPLOYEE_NO=201400002014

Limitation:

It follows only Oracle SQL Syntax, if you try, you can make it Database Independent.

If you've any doubt, please post your questions. If you really like this article, please share it.

Don’t forget to Vote or Comment about my writing.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Debopam Pal
Software Developer National Informatics Centre (NIC)
India India
Hello! Myself Debopam Pal. I've completed my MCA degree from West Bengal University of Technology at 2013. I'm from India. I’ve started to work with MS Technologies in 2013 specially in C# 4.0, ASP.NET 4.0. I've also worked in PHP 5. Now I work in JAVA/J2EE, Struts2. Currently I'm involved in a e-Governance Project since Jan, 2014. In my leisure time I write Blog, Articles as I think that every developer should contribute something otherwise resource will be finished one day. Thank you for your time.
 
Visit: Linkedin Profile | Facebook Profile | Google+ Profile | CodeProject Profile
Follow on   Google+   LinkedIn

Comments and Discussions

 
QuestionSSRS? PinmemberTonBill2-Jun-14 2:57 
SuggestionIntersting PinmemberJean-Francois Gouin29-May-14 2:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140926.1 | Last Updated 29 May 2014
Article Copyright 2014 by Debopam Pal
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid