Getting started

Overview of the project and how to start using it

Goal of this data centric project - is to shield Java developer from nuances of vendor implementation and standard boilerplate code.

Midao Project have first library released: Midao JDBC. The rest is under development and will be released when ready.


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.

There are a lot of Open-Source Java Projects out there. This section created to help you decide if this project is something which might be useful for you.

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.

  • Smaller and simpler.
  • No dependency on Spring Container.
  • Provides functionality which is absent in Spring JDBC.
  • Provides some support for Spring JDBC (templates, exception handlers, metadata handlers).
  • Exposes a lot of configuration/extension opportunities without the need to actually modify library code.
  • Handles much more of JDBC boilerplate code.
  • Provides significantly more functionality.
  • Configurable/Extensible.
  • Midao JDBC follows principles set by DbUtils. Which results in easy migration and short learning curve for Apache DbUtils projects.
  • Smaller and have better performance(because of direct JDBC usage).
  • Midao JDBC is not an ORM Library/Framework nor tries to be one. It's main goal to work with SQL directly while providing functionality to make it more comfortable to use object relational mapping when needed.

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

Download presentation

Get to know project in general and library in particular

Download presentation

Getting started

Get more help on how to start using this library

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

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

Example of simple query
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
Example of lazy query
// 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();
Example retrieval of generated keys
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
Example handling of Large types
// 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
Example ORM capabilities
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);
Example handling of Return/Cursor
// 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();
Example of XML input/output handler (handling custom class)
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);
Example of XML input/output handler (handling map)
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);

Please review this website, browse JavaDocs, look at example Functional tests, browse forums and just take a peek at source code

If you would like to ask question, submit an issue or propose an enhancement please go to GitHub


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