[FIXED] How to add String variable to SQL query - Java, SpringBoot

Issue

I have an SQL query that looks like this:

public void getUser(String userId, String dbName) {

String q = "CREATE TEMPORARY TABLE \"TEST_DATABASE\".\"PUBLIC\".\"USER_TABLE_TEMP\" AS SELECT \"USERID\", \"FIRSTNAME\", \"LASTNAME\" from \"TEST_DATABASE\".\"PUBLIC\".\"USER\"";

jdbcTemplatePerBrand.values().forEach(tab -> tab.query(q, s -> {}));
}

I am trying to pass custom dbName to query and I have tried something like this:

 public void getUser(String userId, String dbName) {

String q = "CREATE TEMPORARY TABLE \"?\".\"PUBLIC\".\"USER_TABLE_TEMP\" AS SELECT \"USERID\", \"FIRSTNAME\", \"LASTNAME\" from \"?\".\"PUBLIC\".\"USER\"";

jdbcTemplatePerBrand.values().forEach(tab -> tab.query(q, s -> {}, dbName));

}

I am getting following error:

Database '"?"' does not exist or not authorized.

Solution

The solution to my problem was to add a variable as +dbName+ to my query.

What was bordering me was the " " that I didn't know how to handle.

I am using Snowflake and my database name is written in " ".

So the solution was that I do this:

public void getUser(String userId, String dbName) {

 String q = "CREATE TEMPORARY TABLE \""+dbName+"\".\"PUBLIC\".\"USER_TABLE_TEMP\" AS SELECT \"USERID\", 
 \"FIRSTNAME\", \"LASTNAME\" from \"TEST_DATABASE\".\"PUBLIC\".\"USER\"";

 jdbcTemplatePerBrand.values().forEach(tab -> tab.query(q, s -> {}));

}

So what I have learned from this is that if you want to add variable to your query use +variableName+ but always be aware of how your database name is written in the database you use.

Also, another NOTE

For example, I am writing queries for Snowflake in Java code.

And for variables that I want to place in statement like:

CREATE TABLE +tableName+ I do use +tableName+ approach

But when I want to add it in my WHERE statement, then I am using ? sign.

String qu = "CREATE TEMPORARY TABLE \""+dbName+"\".\"PUBLIC\".\"USER_TABLE_TEMP\" AS SELECT \"ID\", \"FIRST_NAME\", \"LAST_NAME\" from \""+dbName+"\".\"PUBLIC\".\"USER_TABLE\"\n" +
                "                where \"ID\" = ?\n" +
                "                union all\n" +
                "                select \"ID\",\"ACCOUNT_NAME\", \"ACCOUNT_NUMBER\" from \""+dbName+"\".\"PUBLIC\".\"ACCOUNT_DATA\"\n" +
                "                where \"ID\" = ?";*/

So find out what works for your database and use it :)

This is Snowflake/Java example.

Answered By - user9347049
Answer Checked By - Mildred Charles (JavaFixing Admin)