Midao JDBC is created to simplify development with Java JDBC.

It is flexible, customizable, simple/intuitive to use and provides a lot of functionality: transactions, work with metadata, type handling, profiling, input/output/xml processing/converting, pooled datasource libraries support, cached/lazy(scrollable/updateable) query execution, named parameters, multiple vendor support out of the box, custom exception handling and overrides.

With single jar it supports both JDBC 3.0(Java 5) and JDBC 4.0(Java 6).

Midao JDBC is well tested, not only it has over 900 unit and functional tests, but also it is tested with latest drivers of: Derby, MySQL (MariaDB), PostgreSQL, Microsoft SQL and Oracle.

Unlike standard JDBC libraries - it allows usage of classes/maps, custom error handling, transaction handling, type conversion, cached and lazy queries, stored procedure handling and pooled connections.

Unlike ORM frameworks - it doesn't hide SQL from you, while allowing you to use classes/maps to set and read values from Databases. Class/map mapping also is allowed for stored procedures.

Customization and Simplicity - library tries to provide simplicity of DbUtils while providing functionality/customization of Spring JDBC and often going beyond that.

Pluggable architecture - allows to plug-in any suitable pooled datasource. Provides custom handlers tailored to specific DB Vendor. Provides some Spring JDBC handlers to make usage/migration from Spring JDBC easier/comfortable.

Versatile - with single jar supports both JDBC 3.0(Java 5) and JDBC 4.0(Java 6).

Well tested - not only it has around 800 unit and functional tests, but also it is tested with latest drivers of: Derby, MySQL (MariaDB), PostgreSQL, Microsoft SQL and Oracle.

Get to know project and library, browse getting started guide.

---

You can start using it via adding as a dependency in Maven pom.xml or by downloading jar and adding it to project classpath

Maven

pom.xml:

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

3.1 Project structure

  QueryRunner
  |
  +-- Overrider
  |
  +-- StatementHandler
  |
  +-- MetadataHandler
  |
  +-- TypeHandler
  |
  +-- TransactionHandler
  |
  +-- ExceptionHandler
  
  Processor
  |
  +-- QueryInputProcessor
  |
  +-- QueryOutputProcessor
  
  Profiler
  
  PooledDataSource

  InputHandler

  OutputHandler

  XmlHandler

4.2 Dependencies

Midao does not have any mandatory dependencies

By default Midao uses Java Logging to perform logging.

If SLF4j would be found in Classpath - it would be automatically picked by Midao instead of Java Logging.

SLF4j is "abstract" logging library which allows to plug-in different Logging libraries.

In short that means if your project uses log4j(or any other) - it will automatically(if proper slf4j dependency added) pick it up and will use it along with existing configuration.

pom.xml (Maven)
..
<-- this dependency is mandatory  -->
<dependency>
	<groupId>org.slf4j</groupId>
	<artifactId>slf4j-api</artifactId>
	<version>1.6.6</version>
</dependency>

<-- add next dependency if you want to use Java Logging via slf4j  -->
<dependency>
	<groupId>org.slf4j</groupId>
	<artifactId>slf4j-jdk14</artifactId>
	<version>1.6.6</version>
</dependency>

<-- or this if you want to use log4j via slf4j  -->
<dependency>
	<groupId>org.slf4j</groupId>
	<artifactId>slf4j-log4j12</artifactId>
	<version>1.6.6</version>
</dependency>
..

For detailed information and description of supplied components - please look at JavaDoc.

List of components

Below you will find "logical" components present in Midao

• QueryRunnerService
• AsyncQueryRunnerService
• Overrider
• StatementHandler
  • BaseStatementHandler
  • CallableStatementHandler
• MetadataHandler
  • BaseMetadataHandler
• TypeHandler
  • EmptyTypeHandler
  • BaseTypeHandler
  • UniversalTypeHandler
  • OracleTypeHandler
• TransactionHandler
  • BaseTransactionHandler
• ExceptionHandler
  BaseExceptionHandler
• Processor
  • QueryInputProcessor
    • BaseQueryInputProcessor
    • ExtendedQueryInputProcessor
    • SpringQueryInputProcessor
  • QueryOutputProcessor
    • BaseQueryOutputProcessor
• Profiler
• PooledDataSource
• InputHandler
  • NamedInputHandler
    • BeanInputHandler
      • BeanInputHandlerEx
      • SpringBeanInputHandler
    • BeanListInputHandler
      • BeanListInputHandlerEx
      • SpringBeanListInputHandler
    • MapInputHandler
      • MapInputHandlerEx
      • SpringMapInputHandler
    • MapListInputHandler
      • MapListInputHandlerEx
      • SpringMapListInputHandler
  • QueryInputHandler
    • QueryInputHandler
• OutputHandler
  • KeyedOutputHandler
    • BeanMapOutputHandler
    • KeyedOutputHandler
  • BaseOutputHandler
    • ArrayListOutputHandler
    • ArrayOutputHandler
    • BeanListOutputHandler
    • BeanOutputHandler
    • ColumnListOutputHandler
    • MapListOutputHandler
    • MapOutputHandler
    • RowCountOutputHandler
    • ScalarOutputHandler
  • LazyOutputHandler
    • MapLazyOutputHandler
    • MapLazyScrollOutputHandler
    • MapLazyUpdateOutputHandler
    • MapLazyScrollUpdateOutputHandler
    • BeanLazyOutputHandler
    • BeanLazyScrollOutputHandler
    • BeanLazyUpdateOutputHandler
    • BeanLazyScrollUpdateOutputHandler
• XmlHandler
  • XmlInputOutputHandler

In future guides, we may walk through these components individually in more detail. Until then, look for each of these in the documentation for information on how to utilize and customize them.

For more examples and Database specific handling please look at examples present in GitHub

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> result = runner.query(input, new MapOutputHandler());

// result keeps all returned columns as map

// It is generally advised to use standard cached quesries 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);

MapInputHandler input = new MapInputHandler("SELECT * FROM students", null);

// return as Map List
MapLazyOutputHandler lazyMapList =  runner.query(input, new MapLazyOutputHandler());

// return as Bean List
BeanLazyOutputHandler<Student> lazyBeanList = runner.query(input, new BeanLazyOutputHandler<Student>(Student.class));

// 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();

// 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.
runner.commit();

String INSERT_STUDENT_TABLE = "INSERT INTO students (name, address) VALUES ('Not me', 'unknown')";

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

MapOutputHandler handler = new MapOutputHandler();

// in this example ID is generated field

// works for MySQL, MariaDB, PostgreSQL and others
Map<String, Object> result = runner.update(INSERT_STUDENT_TABLE, handler, new Object[0])

// works for Oracle
Map<String, Object> result =  runner.overrideOnce(MjdbcConstants.OVERRIDE_GENERATED_COLUMN_NAMES, new String [] {"ID"}).update(INSERT_STUDENT_TABLE, handler, new Object[0])

// result keeps all generated keys

// for MySQL, MariaDB, PostgreSQL etc.
QueryRunnerService runner = MjdbcFactory.getQueryRunner(this.dataSource, UniversalTypeHandler.class);

// for Oracle
QueryRunnerService runner = MjdbcFactory.getQueryRunner(this.dataSource, OracleTypeHandler.class);

QueryInputHandler input = null;
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);

// for PostgreSQL
parameters.set("clobIn", "John", Types.VARCHAR, QueryParameters.Direction.IN);
parameters.set("clobOut", null, Types.VARCHAR, QueryParameters.Direction.OUT);
		        
parameters.set("blobIn", "Doe".getBytes(), Types.BINARY, QueryParameters.Direction.IN);
parameters.set("blobOut", null, Types.BINARY, QueryParameters.Direction.OUT);

input = new QueryInputHandler("{call TEST_PROC_LARGE(:clobIn, :clobOut, :blobIn, :blobOut)}", parameters);

CallResults<QueryParameters, Map<String, Object>> result = this.values.put("result", runner.call(input, new MapOutputHandler()));

// result.getCallInput() - you will get updated input from OUT/INOUT parameters
// result.getCallOutput() - you will get Procedure/Function return

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

BeanInputHandler<Student> input = null;
Student student = new Student();
student.setId(2);

// :id is IN parameter and :name and :address are OUT parameters
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);

// for MySQL, MariaDB, PostgreSQL etc.
QueryRunnerService runner = MjdbcFactory.getQueryRunner(this.dataSource, UniversalTypeHandler.class);

// for Oracle
QueryRunnerService runner = MjdbcFactory.getQueryRunner(this.dataSource, OracleTypeHandler.class);

QueryInputHandler input = null;
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);

// for MySQL, MariaDB, PostgreSQL etc.
parameters.set("id", 2, Types.INTEGER, QueryParameters.Direction.IN);

// Postgres has a little different invocation style
input = new QueryInputHandler("{:cursor = call TEST_PROC_RETURN(:id)}", parameters);

// MySQL, MariaDB etc.
input = new QueryInputHandler("{call TEST_PROC_RETURN(:id)}", parameters);

CallResults<QueryParameters, Map<String, Object>> result = runner.call(input, new MapOutputHandler());

// Oracle
List<QueryParameters> outputList = (List<QueryParameters>) result.getCallInput().getValue("cursor");

// MySQL, MariaDB, PostgreSQL etc.
List<Map<String, Object>> outputList = result.getCallOutput();

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

Browse JavaDoc Examples Look at sources