Overview of query types with examples
For detailed information and description of below handlers - please look at JavaDoc.
QueryRunnerService
is core part of the system and is responsible for actual SQL query execution
QueryRunnerService
provides 4 execution types:
1. batch
allows executing few queries in one batch
String INSERT_STUDENT_TABLE_W_PARAMS = "INSERT INTO students (name) VALUES (?)"; QueryRunnerService runner = MjdbcFactory.getQueryRunner(dataSource); // executes 2 queries in one batch. runner.batch(INSERT_STUDENT_TABLE_W_PARAMS, new Object[][] {new Object[] {"not me"}, new Object[] {"not me either"}})
2. update
allows updating database tables/values (SQL INSERT, UPDATE, or DELETE queries)
String INSERT_STUDENT_TABLE = "INSERT INTO students (name, address) VALUES ('Not me', 'unknown')"; QueryRunnerService runner = MjdbcFactory.getQueryRunner(dataSource); // inserts new row into table Students runner.update(DBConstants.INSERT_STUDENT_TABLE, new RowCountOutputHandler<Integer>(), new Object[0])
3. query
allows querying database tables/values (SQL SELECT queries)
String SELECT_STUDENT_TABLE_W_PARAMS = "SELECT name FROM students WHERE id = ?"; QueryRunnerService runner = MjdbcFactory.getQueryRunner(dataSource); // searches for Student with id = 1. Result row is returned as Map Map<String, Object> result = runner.query(DBConstants.SELECT_STUDENT_TABLE_W_PARAMS, new MapOutputHandler(), 1)
4. call
allows executing Stored Procedures/Functions (SQL CALL queries)
// :name - IN, :surname - INOUT, :fullname - OUT. String CALL_PROCEDURE_INOUT = "{call TEST_INOUT(:name, :surname, :fullname)}"; QueryRunnerService runner = MjdbcFactory.getQueryRunner(dataSource); QueryParameters parameters = new QueryParameters(); parameters.set("name", "John", QueryParameters.Direction.IN); parameters.set("surname", "doe", Types.VARCHAR, QueryParameters.Direction.INOUT); parameters.set("fullname", null, Types.VARCHAR, QueryParameters.Direction.OUT); QueryInputHandler input = new QueryInputHandler(DBConstants.CALL_PROCEDURE_INOUT, parameters); QueryParameters result = (QueryParameters) runner.call(input); // :surname - should return input as uppercase, :fullname - combines :name and :surname (as uppercase) // assertEquals("John", result.getValue("name")); // assertEquals("DOE", result.getValue("surname")); // assertEquals("John DOE", result.getValue("fullname"));
Overrider
allows to override standard(default) functionality
Overrider
is working with QueryRunnerService
, StatementHandler
and TypeHandler
runner.overrideOnce(MjdbcConstants.OVERRIDE_GENERATED_COLUMN_NAMES, new String [] {"ID"}).update(INSERT_STUDENT_TABLE, handler, new Object[0])
// by using this we require QueryRunner to throw exception if amount of parameters specified is different than amount of values given. // by default control is not strict, as InputHandler already performs such control. // below might be useful in case you are not using InputHandler to execute queries runner.override(MjdbcConstants.OVERRIDE_CONTROL_PARAM_COUNT, true);
// set limit of 20 cached elements per one Lazy cache runner.override(MjdbcConstants.OVERRIDE_LAZY_CACHE_MAX_SIZE, 20); // specify to keep every element in cache runner.override(MjdbcConstants.OVERRIDE_LAZY_CACHE_MAX_SIZE, -1);
runner.override(MjdbcConstants.OVERRIDE_LAZY_SCROLL_CHANGE_SENSITIVE, true);
For detailed information and description of below handlers - please look at JavaDoc.
AsyncQueryRunnerService
provides possibility of Asynchronous query invocation while actual code execution is performed by QueryRunnerService
Usage of AsyncQueryRunnerService
is pretty much the same as QueryRunnerService
1. batch
allows executing few queries in one batch
String INSERT_STUDENT_TABLE_W_PARAMS = "INSERT INTO students (name) VALUES (?)"; AsyncQueryRunnerService runner = MjdbcFactory.getAsyncQueryRunner(MjdbcFactory.getQueryRunner(dataSource), Executors.newCachedThreadPool()); // executes 2 queries in one batch. runner.batch(INSERT_STUDENT_TABLE_W_PARAMS, new Object[][] {new Object[] {"not me"}, new Object[] {"not me either"}})
2. update
allows updating database tables/values (SQL INSERT, UPDATE, or DELETE queries)
String INSERT_STUDENT_TABLE = "INSERT INTO students (name, address) VALUES ('Not me', 'unknown')"; AsyncQueryRunnerService runner = MjdbcFactory.getAsyncQueryRunner(MjdbcFactory.getQueryRunner(dataSource), Executors.newCachedThreadPool()); // inserts new row into table Students runner.update(DBConstants.INSERT_STUDENT_TABLE, new RowCountOutputHandler<Integer>(), new Object[0])
3. query
allows querying database tables/values (SQL SELECT queries)
String SELECT_STUDENT_TABLE_W_PARAMS = "SELECT name FROM students WHERE id = ?"; AsyncQueryRunnerService runner = MjdbcFactory.getAsyncQueryRunner(MjdbcFactory.getQueryRunner(dataSource), Executors.newCachedThreadPool()); // searches for Student with id = 1. Result row is returned as Map Future<Map<String, Object>> result = runner.query(DBConstants.SELECT_STUDENT_TABLE_W_PARAMS, new MapOutputHandler(), 1)
4. call
allows executing Stored Procedures/Functions (SQL CALL queries)
// :name - IN, :surname - INOUT, :fullname - OUT. String CALL_PROCEDURE_INOUT = "{call TEST_INOUT(:name, :surname, :fullname)}"; AsyncQueryRunnerService runner = MjdbcFactory.getAsyncQueryRunner(MjdbcFactory.getQueryRunner(dataSource), Executors.newCachedThreadPool()); QueryParameters parameters = new QueryParameters(); parameters.set("name", "John", QueryParameters.Direction.IN); parameters.set("surname", "doe", Types.VARCHAR, QueryParameters.Direction.INOUT); parameters.set("fullname", null, Types.VARCHAR, QueryParameters.Direction.OUT); QueryInputHandler input = new QueryInputHandler(DBConstants.CALL_PROCEDURE_INOUT, parameters); Future<QueryParameters> result = runner.call(input); // :surname - should return input as uppercase, :fullname - combines :name and :surname (as uppercase) // assertEquals("John", result.getValue("name")); // assertEquals("DOE", result.getValue("surname")); // assertEquals("John DOE", result.getValue("fullname"));
Below you can provide feedback, suggestions, questions regarding information posted on current page