java - 如何从字符串中分别获取表名、列名和列值

Question

我有如下所示的字符串

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

如何从字符串中分别获取表名、列名和值

Answer 1

假设您的 stat 字符串遵循相同的格式，则可以执行以下操作:

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(")")));
}

输出:

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

准备 Update 语句可能有点棘手，因为您必须注意是设置 varchar 还是 int 并根据数据类型进行更改。例如，您始终将 varchar 数据括在单引号中。

以下是有关如何准备更新声明的说明:

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);
}

输出:

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

可以进一步优化代码。这仅用于说明目的。