How to determine type of the sql query for jdbcTemplate in JAVA?

Question

My requirement is I want to determine type of sql query so that I can choose jdbcTemplate method accordingly.

Suppose my sql query is of type insert or update then I will choose

String sql ="insert/update sql query";
jdbcTemplate.update(sql); 

jdbcTemplate method and if type of my sql query is select then I will choose

String sql ="select sql query";
jdbcTemplate.query(sql);

jdbcTemplate method.

How to determine type of sql query in java effectively?

Which jdbcTemplate method to choose if my query contains both update and select sql statements? e.g.

update table set column_name="abc" where column_name in (select column_name from table where column_name ="xyz");

Update: In reality I am accepting sql queries from the users of my application from the web form so that is the reason where actual problem arises because user can post any type of sql query through form and I want to choose specific jdbcTemplate method depending upon type of query.

Answer 1

in Oracle you can parse a query before executing it

declare
    l_theCursor     integer default dbms_sql.open_cursor;
begin
    dbms_sql.parse(  l_theCursor,  'SELECT 1 FROM DUAL', dbms_sql.native );
end;

which is a good practice anyway since you will be receiving your SQL from user input. if the statement is not valid you will get the appropriate error message. Of course the statement is not executed by the above.

After parsing the statement you can query v$sql to find out the command_type:

select command_type, sql_text
from v$sql t
where sql_text = 'SELECT 1 FROM DUAL';

The various commands_types are like so:

2 -- INSERT

3 -- SELECT

6 -- UPDATE

7 -- DELETE

189 -- MERGE

you can get the full list by select * from audit_actions order by action

Hope that helps :)

Answer 2

SELECT subqueries are irrelevant for the final result. So the command, the first verb is indicative of the result (int updateCount vs. ResultSet).

boolean isSqlSelect = sql.toUpperCase().startsWith("SELECT")
                   || sql.toUpperCase().startsWith("WITH");

Answer 3

You can run an SQL UPDATE query via the .select() method, and you can run an SQL SELECT query via the .update() method.

So why are there 2 different methods?

The update method returns a single number; this number represents the amount of changed/created rows.

The select method returns a resultset, which is like a little table: It has a number of (typed and named) columns, and you can walk through the resultset, getting a row's worth of data every time.

In practice, running a SELECT SQL statement via .update() will run the select (and if that select somehow writes to the DB, for example because you run SELECT NEXTVAL('someSequence'), those effects do occur, but you get 0 back because it didn't change/add any rows to any tables. If you run an UPDATE SQL via .select(), the updates go through, and you get an empty resultset back, or possibly a resultset with 1 column of some numberic type, and one row, with the one value that row has being equal to the update count. It depends on the JDBC driver.

There's another method (execute), which returns nothing. The same applies here: If you run an UPDATE via this method, it still works, you just don't get the updatecount back.

My advice: Use .select for all of it, and write a nice renderer that reports the resultset back to the user.

If you'd like to investigate some code that's already done all this, check out the h2 console.