If you never worked with Java JDBC I am highly recommending to review Oracle JDBC Official tutorial

If you haven't seen Midao JDBC Presentation - please look at it as it is complimentary to this tutorial.

It is expected that you have SQL Connection received or from DataSource or from DriverManager.

Midao JDBC can be received as jar from: Midao web site, GitHub or just by adding Maven dependency:

pom.xml:

..
<dependency>
  <groupId>org.midao</groupId>
  <artifactId>midao-jdbc-core</artifactId>
  <version>0.9.4</version>
</dependency>
..

List of components important to get to know at the beginning:

• Query Runner
  Is core class of the library. Provides ability to execute query.
• Input Handlers
  Input handlers are responsible for handling input: such as (named) SQL query and input parameter values.
• Output Handlers
  Output handlers are responsible for handling/conversion query output (ResultSets).
• Type Handlers
  Type handlers are responsible for handling types (automatically converts String into CLOB and back etc.)
• Profiling
  Profiling allows... to perform Query profiling...

List of components useful during advanced usage of the library:

• Pooled data sources
  Pooled data sources provides ability to create Connection pools.
• Lazy output handlers
  Unlike standard cached queries (default) - lazy output handlers reads only data you need and when you need it.
• Midao config
  Allows configuring default behaviour of Midao JDBC library.

List of components useful to know when you plan to extend library:

• Statement handlers
  Statement handlers are responsible for handling JDBC Statements
• Exception handlers
  Exception handlers are responsible for "enriching" exceptions and/or decoding vendor specific exceptions.
• Metadata handlers
  Metadata handlers are responsible to read Database metadata while handling all vendor specific nuances.

Query runner can be received via MjdbcFactory:

QueryRunnerService runner = MjdbcFactory.getQueryRunner(conn);

Query runner is core part of the system and is responsible for query execution:

// batch example
int[] rowsUpdated = runner.batch("INSERT INTO students (name) VALUES (?)", batchParameters);
// query example
Map>String, Object< result = runner.query(input, new MapOutputHandler());
// update example
runner.update(input, new RowCountOutputHandler<Integer>());
// call example
result = runner.call(input);

• batch
  batch allows executing few queries in one batch.
• update
  update allows updating database tables/values (SQL INSERT, UPDATE, or DELETE queries).
• query
  query allows querying database tables/values (SQL SELECT queries).
• call
  call allows executing Stored Procedures/Functions (SQL CALL queries).

Also Query runner allows to perform Transaction management:

By default QueryRunnerService is running in auto commit mode(every query execution is committed immediately). In order to perform Transaction management you need to set it into Manual mode:

runner.setTransactionManualMode(true);
runner.setTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED);
...
runner.commit()/runner.rollback();

• runner.commit/rollback/rollback(savepoint)
  allows commit or rollback your current transaction
• runner.setSavepoint()/setSavepoint(name)/releaseSavepoint(savepoint)
  allows to perform savepoint creation/releasing
• runner.setTransactionIsolationLevel
  allows to set Transaction isolations level. For more details please visit: JDBC Transactions

Input handlers are responsible for handling input: such as (named) SQL query and input parameter values.

Input handlers handles useful to get to know at the beginning:

Map<String, Object> queryParameters = new HashMap<String, Object>();
queryParameters.put("id", 1);

MapInputHandler input = new MapInputHandler("SELECT name FROM students WHERE id = :id", queryParameters);
...
input = new BeanInputHandler<Student>("{call TEST_NAMED(:id, :name, :address)}", student);
// result would be filled student object searched by ID = 2. All values would come from OUT parameters
Student result = runner.call(input);

• MapInputHandler
  allows using Java Map as source for parameter values
• BeanInputHandler
  allows using Java Class as source for parameter values (only fields with getter/setter would be read)

Input handlers handles useful to get to know for more advanced queries:

QueryParameters parameters = new QueryParameters();

// for Oracle
parameters.set("cursor", null, oracle.jdbc.OracleTypes.CURSOR, QueryParameters.Direction.OUT);
parameters.set("id", 2, Types.INTEGER, QueryParameters.Direction.IN);

input = new QueryInputHandler("{CALL :cursor := TEST_PROC_RETURN(:id)}", parameters);
runner.call(input, new MapOutputHandler());

• QueryInputHandler
  allows setting type/order/direction/value of parameters

Output handlers are responsible for handling/conversion query output (ResultSets).

Output handlers handles useful to get to know at the beginning:

Map<String, Object> studentMap = runner.query(input, new MapOutputHandler());
Stunent student = runner.query(input, new BeanOutputHandler<Student>());
List<Map<String, Object>> studentMapList = runner.query(input, new MapListOutputHandler());
List<Stunent> studentList  = runner.query(input, new BeanListOutputHandler<Student>());

• MapOutputHandler
  allows receiving first row of query output as Map.
• BeanOutputHandler
  allows receiving first row of query output as Java Class (only fields with getter/setter would be set)
• MapListOutputHandler
  allows receiving query output as List of Maps.
• BeanListOutputHandler
  allows receiving query output as List of Java Classes (only fields with getter/setter would be set)

Output handlers handles useful to get to know for more advanced queries:

MapLazyOutputHandler lazyStudentMapCache = runner.query(input, new MapLazyOutputHandler());
BeanLazyOutputHandler lazyStudentCache = runner.query(input, new BeanLazyOutputHandler<Student>());
MapLazyScrollOutputHandler lazyStudentScrollMapCache = runner.query(input, new MapLazyScrollOutputHandler());
BeanLazyScrollOutputHandler lazyStudentScrollCache = runner.query(input, new BeanLazyScrollOutputHandler<Student>());

• MapLazyOutputHandler
  allows iterating over(forward only) query output as Map. Data is read only when requested.
• BeanLazyOutputHandler
  allows iterating over(forward only) query output as Java Class. Data is read only when requested.
• MapLazyScrollOutputHandler
  allows iterating over(in any direction) query output as Map. Data is read only when requested.
• BeanLazyScrollOutputHandler
  allows iterating over(in any direction) query output as Map. Data is read only when requested.

Type handlers are responsible for handling types (for example: automatically converts byte[] into BLOB and back).

By default type handling is not performed. If one required - you need to use it explicitly:

// for whole system
MjdbcConfig.setDefaultTypeHandler(OracleTypeHandler.class/UniversalTypeHandler.class);
// for this particular QueryRunner
QueryRunnerService runner = MjdbcFactory.getQueryRunner(conn, OracleTypeHandler.class/UniversalTypeHandler.class);

QueryRunnerService runner = MjdbcFactory.getQueryRunner(conn, UniversalTypeHandler.class);
...
QueryParameters parameters = new QueryParameters();

// in this example we set String value into both SQL Clob and SQL Blob. Type handlers would perform required conversion.
parameters.set("clobIn", "John", MjdbcTypes.CLOB, QueryParameters.Direction.IN);
parameters.set("clobOut", null, MjdbcTypes.CLOB, QueryParameters.Direction.OUT);

parameters.set("blobIn", "Doe", MjdbcTypes.BLOB, QueryParameters.Direction.IN);
parameters.set("blobOut", null, MjdbcTypes.BLOB, QueryParameters.Direction.OUT);
                        
input = new QueryInputHandler(DBConstants.CALL_PROCEDURE_LARGE, parameters);
QueryParameters updatedParameters = runner.call(input, new MapOutputHandler()).getCallInput();

// we would receive String value
updatedParameters.getValue("clobOut");
// we would receive byte[] value
updatedParameters.getValue("blobOut");

Type handlers useful to get to know at the beginning:

• EmptyTypeHandler
  should be used when no type handling is required
• OracleTypeHandler
  should be used to perform type handling for Oracle Database
• UniversalTypeHandler
  should be used to perform type handling for MySQL/MariaDB/PostgreSQL/MsSQL etc.

Type handlers useful to get to know for extention of the library:

• BaseTypeHandler
  this class is base type handler implementation with support of JDBC4 only, serves as base for other type handlers

Features useful to get to know at the beginning:

// switch it on. that's it. no more actions is required.
MjdbcConfig.setProfilerEnabled(true);

// if you need to modify profiling string - can be done via:
MjdbcConfig.setProfilerOutputFormat("Invoked class [%s].\n - Method [%s{}]\n - Args [%s]\n - Time [%5.3f] sec ");

• Profiling
  allows to perform query profiling

Features which might be useful during advanced library usage:

// creation of pooled data source. All required dependencies should be added. Click for details
DataSource ds = MjdbcFactory.createDataSource(driverClassName, url, userName, password, initialSize, maxActive);
// more advanced example
DataSource ds = MjdbcFactory.createDataSource(properties);

// Midao config allows configuration of Midao JDBC library:
MjdbcConfig.setProfilerOutputFormat("Invoked class [%s].\n - Method [%s{}]\n - Args [%s]\n - Time [%5.3f] sec ");
MjdbcConfig.setDefaultLazyCacheMaxSize(newCacheMaxSize);
// etc.

• Pooled data sources
  provides connection pool using Apache DBCP or C3P0
• MjdbcConfig
  allows tweaking of Midao JDBC Library

Below you will find few interesting examples to see above in action:

A lot more examples can be found in GitHub Examples

QueryRunnerService runner = MjdbcFactory.getQueryRunner(this.dataSource);

Map<String, Object> queryParameters = new HashMap<String, Object>();
queryParameters.put("id", 1);

MapInputHandler input = new MapInputHandler("SELECT name FROM students WHERE id = :id", queryParameters);

Map<String, Object> studentMap = runner.query(input, new MapOutputHandler());
// or
Student student = runner.query(input, new BeanOutputHandler<Student>());

QueryRunnerService runner = MjdbcFactory.getQueryRunner(this.dataSource);

Map<String, Object> queryParameters = new HashMap<String, Object>();
queryParameters.put("id", 1);

MapInputHandler input = new MapInputHandler("SELECT name FROM students WHERE id > :id", queryParameters);

List<Map<String, Object>> studentMapList = runner.query(input, new MapListOutputHandler());
// or
List<Student> studentList = runner.query(input, new BeanListOutputHandler<Student>());

QueryRunnerService runner = MjdbcFactory.getQueryRunner(conn, UniversalTypeHandler.class/OracleTypeHandler.class/BaseTypeHandler.class);
...
QueryParameters parameters = new QueryParameters();

// in this example we set String value into both SQL Clob and SQL Blob. Type handlers would perform required conversion.
parameters.set("clobIn", "John", MjdbcTypes.CLOB, QueryParameters.Direction.IN);
parameters.set("clobOut", null, MjdbcTypes.CLOB, QueryParameters.Direction.OUT);

parameters.set("blobIn", "Doe", MjdbcTypes.BLOB, QueryParameters.Direction.IN);
parameters.set("blobOut", null, MjdbcTypes.BLOB, QueryParameters.Direction.OUT);
                        
input = new QueryInputHandler("{call TEST_PROC_LARGE(:clobIn, :clobOut, :blobIn, :blobOut)}", parameters);

QueryParameters updatedParameters = runner.call(input, new MapOutputHandler()).getCallInput();

// we would receive String value
updatedParameters.getValue("clobOut");
// we would receive byte[] value
updatedParameters.getValue("blobOut");

Below you can provide feedback, suggestions, questions regarding information posted on current page