CODEWITHSUNDEEP
javaregex
Vishal_Kotecha
Vishal_Kotecha

Reputation: 491

Java regex get table alias from SQL query

I am using the SQL Server 2017 database with a Spring boot project. I can not use JPA. Using spring JDBC. I have a requirement where I can add conditions to a query dynamically.

For example, there is a query already available in my properties file -

SELECT o.OrderID, c.CustomerName
FROM ECO.Orders o
INNER JOIN ECO.Customers c ON o.CustomerID = c.CustomerID

(ECO is my schema name and must include in the query)

Now, in java, I need to append conditions dynamically - eg. "where CustomerID = 1"

Problem with this is it will throw

jdbc.SQLServerException: Ambiguous column name CustomerID

So I have to add an alias to this column.

"where o.CustomerID = 1" will work absolutely fine.

Now, I need to find a way to get an alias from the query.

I have tried following: 

 String test = query.substring(query.indexOf("FROM ECO."));
 System.out.println(test);

Which gives following result : 

FROM ECO.Orders o

I just need to get the Table alias from this. I guess this could be done with regex.

Upvotes: 0

Views: 635

Answers (1)

Michael Kreutz
Michael Kreutz

Reputation: 1256

You could extract the alias as shown in the following example using regex:

  public static void main(String[] args) {
    final String queryString = "SELECT o.OrderID, c.CustomerName \n"
        + "FROM ECO.Orders o \n"
        + "INNER JOIN ECO.Customers c ON o.CustomerID = c.CustomerID \n";
    deriveAlias(queryString);
    System.out.println("Alias: " + deriveAlias(queryString));
  }

  private static String deriveAlias(String queryString) {
    String query = queryString.replace('\n', ' ');
    final Pattern pattern = Pattern.compile(".*FROM ECO\\.[\\w]+ ([a-zA-Z]+).*");
    final Matcher matcher = pattern.matcher(query);
    if (matcher.matches()) {
      return matcher.group(1);
    }
    return null;
  }

Upvotes: 1

Related Questions