2

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.

Pawan Patil
  • 1,067
  • 5
  • 20
  • 46

3 Answers3

3

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 :)

Ted at ORCL.Pro
  • 1,602
  • 1
  • 7
  • 10
  • 1
    Excellent idea. Using Oracle 12 [DBMS_SQL_TRANSLATOR.SQL_ID](https://docs.oracle.com/database/121/ARPLS/d_sql_trans.htm#ARPLS73952) is it possible to calculate the `SQL_ID` in the parsing PL/SQL block, which makes the access to `V$SQL` much more confortable. – Marmite Bomber Feb 13 '19 at 17:22
  • 1
    @MarmiteBomber thanks for the comment. A low-fi old-skool way would also be to add a comment on each query with a unique identifier and then easily query for that – Ted at ORCL.Pro Feb 13 '19 at 17:25
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");
Joop Eggen
  • 107,315
  • 7
  • 83
  • 138
  • What is mean by first verb and why SELECT subqueries are irrelevant? – Pawan Patil Feb 13 '19 at 13:39
  • 2
    select can start with *with* also – Ori Marko Feb 13 '19 at 13:49
  • An INSERT ... (SELECT ...) just returns an update count, the sub-select is an intermediate product. – Joop Eggen Feb 13 '19 at 13:49
  • So should I choose jdbcTemplate.update(sql) method for INSERT ... (SELECT ...) type of queries ? – Pawan Patil Feb 13 '19 at 13:52
  • @PawanPatil yes INSERT and UPDATE both return an update count. See JDBC `PreparedStatement` that provides either `ResultSet executeQuery` (SELECT) or `int executeUpdate` (SQL changes). The JdbcTemplate builds upon this fundament. – Joop Eggen Feb 13 '19 at 14:02
  • `/* My select */ SELECT ...` will be considered an update? – Marmite Bomber Feb 13 '19 at 16:22
  • @MarmiteBomber Okay the check requires throwing an error when not SELECT/INSERT/UPDATE/DELETE/... . On the other hand .update/.select must be called based on the knowledge of the query, so this is a storm in a glass of water, as the Dutch say. – Joop Eggen Feb 13 '19 at 16:38
1

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.

rzwitserloot
  • 85,357
  • 5
  • 51
  • 72