CODEWITHSUNDEEP
javaarraysstringstringbuilderstringbuffer
Madhava
Madhava

Reputation: 87

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

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

Upvotes: 0

Views: 660

Answers (2)

ninja.coder
ninja.coder

Reputation: 9648

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.

Upvotes: 1

Ken Bekov
Ken Bekov

Reputation: 14015

First approach, split your string with regexp. Needed strings will be in array at idex 2(table name), index 3(col names) and index 5(values):

public static void main(String[] args){        

    String stat="INSERT INTO DEPARTMENT(DNO, NAME, TEST_ID) VALUES (2, 'ADM', 1)";
    String[] strings = stat.split("[\\(\\)]|(\\b\\s\\b)");

    System.out.println(strings[2]);
    System.out.println(strings[3]);
    System.out.println(strings[5]);
}

Output will be:

DEPARTMENT
DNO, NAME, TEST_ID
2, 'ADM', 1

To get separate column names, you can split corresponding string again:

public static void main(String[] args){        

    String stat="INSERT INTO DEPARTMENT(DNO, NAME, TEST_ID) VALUES (2, 'ADM', 1)";
    String[] strings = stat.split("[\\(\\)]|(\\b\\s\\b)");

    String[] columnNames = (strings[3].split(",\\s"));
    for(String s:columnNames){
        System.out.println(s);
    }
}

Output will be:

DNO
NAME
TEST_ID

You can process string with values same way. This approach has less code.

Second approach (I would prefer) is simple regular expression:

publi static main(String[]args) {

    String stat="INSERT INTO DEPARTMENT(DNO, NAME, TEST_ID) VALUES (2, 'ADM', 1)";
    Pattern pattern = Pattern.compile("INSERT INTO (\\w+)\\((.*)\\) VALUES \\((.*)\\)");
    Matcher matcher = pattern.matcher(stat);
    if(matcher.find()){
        System.out.println(matcher.group(1));
        System.out.println(matcher.group(2));
        System.out.println(matcher.group(3));
    }
}

Output will be:

DEPARTMENT
DNO, NAME, TEST_ID
2, 'ADM', 1

Upvotes: 1

Related Questions