How to get tablename,column names and column values seperatly from string

Question

I have String like below

 String stat="INSERT INTO DEPARTMENT(DNO, NAME, TEST_ID) VALUES (2, 'ADM', 1)";

How to get table name, column names and values separately from the string

Answer 1

Assuming you are following the same format for your stat String, here is how you can do it:

public static void main (String[] args) 
{
    String stat = "INSERT INTO DEPARTMENT(DNO, NAME, TEST_ID) VALUES (2, 'ADM', 1)";
    System.out.println("Table Name: " + stat.substring(stat.indexOf("INSERT INTO ") + 12,stat.indexOf("(")));
    System.out.println("Column Names: " + stat.substring(stat.indexOf("(") + 1,stat.indexOf(")")));
    System.out.println("Column Values: " + stat.substring(stat.indexOf("VALUES (") + 8,stat.lastIndexOf(")")));
}

Output:

Table Name: DEPARTMENT
Column Names: DNO, NAME, TEST_ID
Column Values: 2, 'ADM', 1

Preparing the Update statement can be bit tricky as you have to take care of whether you are setting varchar or int and do changes based on the datatype. For Example, you will always enclose varchar data in single quotes.

Here is an illustration on how you can prepare the Update Statement:

public static void main (String[] args) 
{
    String stat = "INSERT INTO DEPARTMENT(DNO, NAME, TEST_ID) VALUES (2, 'ADM', 1)";
    String tableName = stat.substring(stat.indexOf("INSERT INTO ") + 12,stat.indexOf("("));
    String columnNames = stat.substring(stat.indexOf("(") + 1,stat.indexOf(")"));
    String columnValues = stat.substring(stat.indexOf("VALUES (") + 8,stat.lastIndexOf(")"));

    /* Prepare New Values Object */
    Object[] newValues = new Object[3];
    newValues[0] = new Integer(1);
    newValues[1] = new String("foo");
    newValues[2] = new Integer(8);

    /* Extract Column Names & Appen New Values */
    StringBuilder sb = new StringBuilder();
    sb.append("UPDATE " + tableName + " SET ");
    String[] splits = columnNames.split(", ");
    for(int i = 0; i < splits.length; i++) {
        sb.append(newValues[i] instanceof String ? splits[i] + " = '" + newValues[i] + "', " : splits[i] + " = " + newValues[i] + ", ");
    }
    String newString = sb.substring(0, sb.length() - 2);

    /* Extract Old Values & Prepare Where Caluse */
    sb = new StringBuilder();
    sb.append(" WHERE ");
    String[] splitz = columnValues.split(", ");
    for(int i = 0; i < splits.length; i++) {
        sb.append(splits[i] + " = " + splitz[i] + " AND ");
    }
    String where = sb.substring(0, sb.length() - 5);

    /* Print Result */
    System.out.println(newString + where);
}

Output:

UPDATE DEPARTMENT SET DNO = 1, NAME = 'foo', TEST_ID = 8 WHERE DNO = 2 AND NAME = 'ADM' AND TEST_ID = 1

You can optimize the code more. This is just for illustration purposes.