How to add String variable to SQL query – Java, SpringBoot

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.

Answer

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 alln" +
                "                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.