Overview Input/Output Handlers and how to use them
For detailed information and description of below handlers - please look at JavaDoc.
InputHandler is responsible for handling input Query parameters: query string, input parameters, parameter values, types, direction and order.
BeanInputHandler allows accepting Java Class as source of parameter values
Cat cat = new Cat();
// initialize cat with data
InputHandler input = new BeanInputHandler<Cat>("SELECT ID FROM CATS WHERE AGE = :cat.age AND NAME = :cat.name"
, cat, "cat");
BeanListInputHandler allows accepting List of Java Class as source of parameter values
String encodedMultipleParameterQuery = "SELECT * FROM cats FULL JOIN dogs ON cats.owner=dogs.owner WHERE (cats.AGE=:cat.age AND cats.NAME = :cat.name) or (dogs.weight = :dog.weight AND dogs.breed = :dog.breed AND dogs.age = :dog.age)";
Map<String, Pet> parameterValues = new HashMap<String, Pet>();
parameterValues.put("cat", this.cat);
parameterValues.put("dog", this.dog);
BeanListInputHandler<Pet> inputHandler = new BeanListInputHandler<Pet>(encodedMultipleParameterQuery, parameterValues);
MapInputHandler allows accepting Java Map as source of parameter values
String encodedSingleParameterQuery = "SELECT ID FROM CATS WHERE AGE=:cat.age AND NAME = :cat.name";
Map<String, Object> catMap = new HashMap<String, Object>() {
{put("age", 5);put("name", "whiskers");}
};
MapInputHandler inputHandler = new MapInputHandler(this.encodedSingleParameterQuery, this.catMap, "cat");
MapListInputHandler allows accepting List of Java Map as source of parameter values
Map<String, Object> catMap = new HashMap<String, Object>() {
{put("age", 5);put("name", "whiskers");}
};
Map<String, Object> dogMap = new HashMap<String, Object>() {
{put("weight", 17);put("breed", "Blue Lacy");put("age", 3);}
};
Map<String, Map<String, Object>> parameterValues = new HashMap<String, Map<String, Object>>();
parameterValues.put("cat", this.catMap);
parameterValues.put("dog", this.dogMap);
MapListInputHandler inputHandler = new MapListInputHandler(this.encodedMultipleParameterQuery, parameterValues);
Extended Input Handlers have the same core as standard named handlers, but unlike the latter - allow to set parameter configuration inline. Example below.
For additional information please browse Named Input Handlers and JavaDoc.
Please be careful as currently only "jdbcType" (varchar/integer etc.; types which are listed in MjdbcTypes) and "mode" (in, out, inout; as listed in QueryParameters.Direction) are supported.
Map<String, Object> queryParameters = new HashMap<String, Object>();
queryParameters.put("id", 1);
MapInputHandlerEx input =
new MapInputHandlerEx(
"SELECT name FROM students WHERE id = #{id,jdbcType=INTEGER,mode=in}",
queryParameters);
Map<String, Object> result = runner.query(input, new MapOutputHandler());
BeanInputHandlerEx
BeanListInputHandlerEx
MapInputHandlerEx
MapListInputHandlerEx
QueryInputHandler allows accepting QueryParameters as source of parameter values
QueryParameters parameters = new QueryParameters();
// for MySQL, MariaDB, Oracle
parameters.set("clobIn", "John", Types.CLOB, QueryParameters.Direction.IN);
parameters.set("clobOut", null, Types.CLOB, QueryParameters.Direction.OUT);
parameters.set("blobIn", "Doe", Types.BLOB, QueryParameters.Direction.IN);
parameters.set("blobOut", null, Types.BLOB, QueryParameters.Direction.OUT);
input = new QueryInputHandler("{call TEST_PROC_LARGE(:clobIn, :clobOut, :blobIn, :blobOut)}", parameters);
SpringBeanInputHandler
SpringBeanListInputHandler
SpringMapInputHandler
SpringMapListInputHandler
The difference between InputHandlers and Spring Input Handlers is that Spring Handlers are using SpringInputProcessor instead of BaseInputProcessor
For detailed information and description of below handlers - please look at JavaDoc.
OutputHandler is responsible for handling Query output and converting it into Java Classes
Table which would be used for showcasing possibilities of OutputHandlers
| # | Name | Occupation | Age |
|---|---|---|---|
| 1 | Jack | Sheriff | 36 |
| 2 | Henry | Mechanic | 36 |
| 3 | Alison | Agent | 30 |
BeanMapOutputHandler converts Query output into Map of Java Class. Column which would be treated as key is specified via Constructor
// paramsList contains table found in the beginning of OutputHandler section
BeanMapOutputHandler<String, Character> handler = new BeanMapOutputHandler<String, Character>(Character.class, 0);
Map<String, Character> result = handler.handle(paramsList);
// result would contain map of rows (as Character class). result would contain:
// org.junit.Assert.assertArrayEquals(new Object[]{"jack", "sheriff", 36}, new Object[]{result.get("jack").getName(), result.get("jack").getOccupation(), result.get("jack").getAge()});
// org.junit.Assert.assertArrayEquals(new Object[]{"henry", "mechanic", 36}, new Object[]{result.get("henry").getName(), result.get("henry").getOccupation(), result.get("henry").getAge()});
// org.junit.Assert.assertArrayEquals(new Object[]{"alison", "agent", 30}, new Object[]{result.get("alison").getName(), result.get("alison").getOccupation(), result.get("alison").getAge()});
KeyedOutputHandler converts Query output into Map of Maps. Column which would be treated as key is specified via Constructor
// paramsList contains table found in the beginning of OutputHandler section
KeyedOutputHandler<String> handler = new KeyedOutputHandler<String>(0);
Map<String, Map<String, Object>> result = handler.handle(paramsList);
// result would contain map of rows (as map). result would contain:
//org.junit.Assert.assertArrayEquals(new Object[]{"jack", "sheriff", 36},
// new Object[]{result.get("jack").get("name"), result.get("jack").get("occupation"), result.get("jack").get("age")});
//org.junit.Assert.assertArrayEquals(new Object[]{"henry", "mechanic", 36},
// new Object[]{result.get("henry").get("name"), result.get("henry").get("occupation"), result.get("henry").get("age")});
//org.junit.Assert.assertArrayEquals(new Object[]{"alison", "agent", 30},
// new Object[]{result.get("alison").get("name"), result.get("alison").get("occupation"), result.get("alison").get("age")});
ArrayOutputHandler converts Query output (first row) into Array of Object
// paramsList contains table found in the beginning of OutputHandler section
Object[] result = new ArrayOutputHandler().handle(paramsList)
// result would contain first row(default): "jack", "sheriff", 36
// Assert.assertArrayEquals(new Object[]{"jack", "sheriff", 36}, result);
ArrayListOutputHandler converts Query output into List of Array of Object(each line is represented as List element)
// paramsList contains table found in the beginning of OutputHandler section
List<Object[]> result = new ArrayListOutputHandler().handle(paramsList);
// result would contain list of rows (as arrays). result would contain:
// Assert.assertArrayEquals(new Object[]{"jack", "sheriff", 36}, result.get(0));
// Assert.assertArrayEquals(new Object[]{"henry", "mechanic", 36}, result.get(1));
// Assert.assertArrayEquals(new Object[]{"alison", "agent", 30}, result.get(2));
BeanOutputHandler converts Query output (first row) into Java Class
// paramsList contains table found in the beginning of OutputHandler section
BeanOutputHandler<Character> handler = new BeanOutputHandler<Character>(Character.class);
Character result = handler.handle(paramsList);
// result would contain first row converted into Character class
// org.junit.Assert.assertArrayEquals(new Object[]{"jack", "sheriff", 36}, new Object[]{result.getName(), result.getOccupation(), result.getAge()});
BeanListOutputHandler converts Query output into List of Java Class(each line is represented as List element)
// paramsList contains table found in the beginning of OutputHandler section
List<Character> result = new BeanListOutputHandler<Character>(Character.class).handle(paramsList);
// result would contain list of rows (as Character class). result would contain:
// Assert.assertArrayEquals(new Object[]{"jack", "sheriff", 36}, new Object[]{result.get(0).getName(), result.get(0).getOccupation(), result.get(0).getAge()});
// Assert.assertArrayEquals(new Object[]{"henry", "mechanic", 36}, new Object[]{result.get(1).getName(), result.get(1).getOccupation(), result.get(1).getAge()});
// Assert.assertArrayEquals(new Object[]{"alison", "agent", 30}, new Object[]{result.get(2).getName(), result.get(2).getOccupation(), result.get(2).getAge()});
ColumnListOutputHandler converts Query output Column (specified via Constructor) into List of Object(each line is represented as List element)
// paramsList contains table found in the beginning of OutputHandler section
List<String> result = new ColumnListOutputHandler<String>(0).handle(paramsList);
// result would contain first column(0): "jack", "henry", "alison"
// Assert.assertArrayEquals(new Object[] {"jack", "henry", "alison"}, result.toArray());
MapListOutputHandler converts Query output into List of Maps(each line is represented as List element)
// paramsList contains table found in the beginning of OutputHandler section
MapListOutputHandler handler = new MapListOutputHandler();
List<Map<String, Object>> result = handler.handle(paramsList);
// result would contain list of rows (as Map). result would contain:
//org.junit.Assert.assertArrayEquals(new Object[]{"jack", "sheriff", 36},
// new Object[]{result.get(0).get("name"), result.get(0).get("occupation"), result.get(0).get("age")});
//org.junit.Assert.assertArrayEquals(new Object[]{"henry", "mechanic", 36},
// new Object[]{result.get(1).get("name"), result.get(1).get("occupation"), result.get(1).get("age")});
//org.junit.Assert.assertArrayEquals(new Object[]{"alison", "agent", 30},
// new Object[]{result.get(2).get("name"), result.get(2).get("occupation"), result.get(2).get("age")});
MapOutputHandler converts Query output (first row) into Map
// paramsList contains table found in the beginning of OutputHandler section
MapOutputHandler handler = new MapOutputHandler();
Map<String, Object> result = handler.handle(paramsList);
// result would contain first row as Map: "jack", "sheriff", 36
//org.junit.Assert.assertArrayEquals(new Object[]{"jack", "sheriff", 36},
// new Object[]{result.get("name"), result.get("occupation"), result.get("age")});
RowCountOutputHandler returns number of rows updated. Useful during update invocation.
RowCountOutputHandler<Integer> handler = new RowCountOutputHandler<Integer>();
ScalarOutputHandler converts Query output Column (specified via Constructor) (first row) into Object
// paramsList contains table found in the beginning of OutputHandler section
ScalarOutputHandler<String> handler = new ScalarOutputHandler<String>(0);
String result = handler.handle(paramsList);
// return would contain first row and first column(0).
// Assert.assertEquals("jack", result);
BeanLazyOutputHandler allows lazy reading of query output as sequence of Java classes
BeanLazyOutputHandler useful in cases when you have to fetch a lot of data, but not all of it will be actually needed.
For example if user searched for 100 pages but will be able to browse few pages only.
// It is generally advised to use standard cached queries as all resource management is done by the system.
// During lazy query execution developer have to manually manage transactions (QueryRunnerService.commit/rollback) and manually close resources (LazyOutputHandler.close).
QueryRunnerService runner = MjdbcFactory.getQueryRunner(this.dataSource);
// lazy query execution can be done only in manual transaction mode.
runner.setTransactionManualMode(true);
...
// return as Bean List
BeanLazyOutputHandler<Student> lazyBeanList = runner.query(input, new BeanLazyOutputHandler<Student>(Student.class));
// iterate over Bean List
while (lazyBeanList.hasNext() == true) {
// retrieve next element
lazyBeanList.getNext() // returns Student class
}
// freeing all used resources. If not closed - might lead to resource leak and/or errors.
lazyBeanList.close();
// telling to finish current transaction(doesn't matter if it is update or just query) and free all the resources.
// if current transaction would be committed before LazyOutputHandler was closed - it might be no longer accessible and exception might be thrown.
runner.commit();
BeanLazyScrollUpdateOutputHandler allows lazy scrolling/updating of query output as sequence of Java classes
For description of scrollable/updateable lazy output handlers please look at Lazy scroll/update support
// Please do not forget that all standard rules of LazyOutputHandlers still apply - using manual mode, closing resources and committing values (review BeanLazyOutputHandler example above).
...
// return as Bean List
BeanLazyScrollUpdateOutputHandler<Student> lazyBeanList = runner.query(input, new BeanLazyScrollUpdateOutputHandler<Student>(Student.class));
// iterate backwards over Bean List
while (lazyBeanList.hasPrev() == true) {
// retrieve prev element
lazyBeanList.hasPrev() // returns Student class
}
// move cursor to specified position
lazyBeanList.moveTo(newCursorPosition);
// move cursor relatively by specified amount of rows
lazyBeanList.moveRelative(moveCursorRelative);
// read value
lazyBeanList.getCurrent();
// updates current row
lazyBeanList.updateRow(student);
// inserts new row
lazyBeanList.insertRow(student);
...
BeanLazyScrollOutputHandler allows lazy scrolling (read-only) of query output as sequence of Java classes
For description of scrollable/updateable lazy output handlers please look at Lazy scroll/update support
BeanLazyUpdateOutputHandler allows lazy updating (forward only) of query output as sequence of Java classes
For description of scrollable/updateable lazy output handlers please look at Lazy scroll/update support
MapLazyOutputHandler allows lazy reading of query output as sequence of Maps
MapLazyOutputHandler useful in cases when you have to fetch a lot of data, but not all of it will be actually needed.
For example if user searched for 100 pages but will be able to browse few pages only.
// It is generally advised to use standard cached queries as all resource management is done by the system.
// During lazy query execution developer have to manually manage transactions (QueryRunnerService.commit/rollback) and manually close resources (LazyOutputHandler.close).
QueryRunnerService runner = MjdbcFactory.getQueryRunner(this.dataSource);
// lazy query execution can be done only in manual transaction mode.
runner.setTransactionManualMode(true);
...
// return as Map List
MapLazyOutputHandler lazyMapList = runner.query(input, new MapLazyOutputHandler());
// iterate over Map List
while (lazyMapList.hasNext() == true) {
// retrieve next element
lazyMapList.getNext() // returns Map
}
// freeing all used resources. If not closed - might lead to resource leak and/or errors.
lazyMapList.close();
// telling to finish current transaction(doesn't matter if it is update or just query) and free all the resources.
// if current transaction would be committed before LazyOutputHandler was closed - it might be no longer accessible and exception might be thrown.
runner.commit();
MapLazyScrollUpdateOutputHandler allows lazy scrolling/updating of query output as sequence of Java maps
For description of scrollable/updateable lazy output handlers please look at Lazy scroll/update support
// Please do not forget that all standard rules of LazyOutputHandlers still apply - using manual mode, closing resources and committing values (review MapLazyOutputHandler example above).
...
// return as Bean List
MapLazyScrollUpdateOutputHandler lazyMapList = runner.query(input, new MapLazyScrollUpdateOutputHandler());
// iterate backwards over Bean List
while (lazyMapList.hasPrev() == true) {
// retrieve prev element
lazyMapList.hasPrev() // returns map
}
// move cursor to specified position
lazyMapList.moveTo(newCursorPosition);
// move cursor relatively by specified amount of rows
lazyMapList.moveRelative(moveCursorRelative);
// read value
lazyMapList.getCurrent();
// updates current row
lazyMapList.updateRow(studentMap);
// inserts new row
lazyMapList.insertRow(studentMap);
...
MapLazyScrollOutputHandler allows lazy scrolling (read-only) of query output as sequence of Java classes
For description of scrollable/updateable lazy output handlers please look at Lazy scroll/update support
MapLazyUpdateOutputHandler allows lazy updating (forward only) of query output as sequence of Java classes
For description of scrollable/updateable lazy output handlers please look at Lazy scroll/update support
XML Handlers were created to allow execution of queries from external files. Main benefit comes from the fact that query and it's configuration (generate keys, output handler etc.) can be stored in external file and query change won't require rebuild/redeploy of the whole package
Version 0.9.5 comes with support of XML handler with next features: query/update execution, any available output handler (inc. lazy), generate keys, control parameter count etc. Next features are planned to be implemented: call/batch execution, setting of statement/type handler per query, refresh mechanism in case of file change etc.
Xml handlers utilize existing Midao JDBC guidelines and XML syntax is inspired by iBatis/MyBatis project, but please be informed that differences between myBatis and Midao JDBC Xml Handlers are substantial as Midao JDBC is not an ORM library nor tries to be one. The goal behind Xml Handlers is to simplify query execution and allow ORM execution when needed.
Example usage can be found below:
String xmlContent = "<?xml version=\"1.0\"?>" +
"<root>" +
"<query id='org.midao.jdbc.examples.Student.findOne' outputHandler='BeanOutputHandler'>" +
"SELECT name FROM students WHERE id = #{id,jdbcType=INTEGER,mode=in}" +
"</query>" +
"</root>";
// xml should be added to Repository before it can be executed
XmlRepositoryFactory.addAll(
XmlRepositoryFactory.getDocument(new ByteArrayInputStream(
xmlContent.getBytes()
)
));
XmlInputOutputHandler<Student> handler = new XmlInputOutputHandler<Student>(Student.class, "findOne", 1);
// XML query execution
Student student = (Student) runner.execute(handler);
String xmlContent = "<?xml version=\"1.0\"?>" +
"<root>" +
"<query id='findStudent' outputHandler='MapOutputHandler'>" +
"SELECT name FROM students WHERE id = #{id,jdbcType=INTEGER,mode=in}" +
"</query>" +
"</root>";
// xml should be added to Repository before it can be executed
XmlRepositoryFactory.addAll(
XmlRepositoryFactory.getDocument(new ByteArrayInputStream(
xmlContent.getBytes()
)
));
XmlInputOutputHandler handler = new XmlInputOutputHandler("findStudent", 1);
// XML query execution
Map<String, Object> student = (Map<String, Object>) runner.execute(handler);
List of allowed commands:
query - performs query invocation via QueryRunnerService.query()update - performs update invocation via QueryRunnerService.update()List of allowed parameters:
id - Mandatory. In there you should specify query name. If query handles Bean - full bean name (inc. package) should be set as prefix to the name, example: id='org.midao.jdbc.examples.Student.findOne'.outputHandler - Optional. In there you should specify one of the OutputHandlers. If nothing was set - MapListOutputHandler would be used by default.generateKeys - Optional. Accepts true value. If set - instructs JDBC to return generated keys.generateKeysColumns - Optional. If generateKeys is set "true" and you are using Oracle database - that field should be used to specify fields which are generated, example: generateKeysColumns="ID,TRIGGER_GENERATED_COLUMN"controlParamCount - Optional. If you need to turn off parameter amount control (might be needed in rare cases) - you can set it via that parameter. Accepts values: true/false.List of allowed parameter (for example: #{id}) inline configuration:
jdbcType - Optional. Allows setting types which are listed in MjdbcTypes. Example: #{id,jdbcType=INTEGER}mode - Optional. Allows setting direction which are listed in QueryParameters.Direction. Example: #{id,mode=in}Below you can provide feedback, suggestions, questions regarding information posted on current page