Micronaut-core: How to access postgresql database by using mybatis and hikari?

[ggservice007]

I want to use mybatis to write sql. How to?

[ggservice007]

I try to solve it as following.
DataSourceService.java

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.annotation.PostConstruct;
import javax.annotation.PreDestroy;
import javax.inject.Singleton;

@Singleton
public class DataSourceService {

  private static final Logger log = LoggerFactory.getLogger(DataSourceService.class);

  private HikariDataSource dataSource;

  @PostConstruct
  public void initialize() {
    log.info("Initialize data source....");
    HikariConfig config = new HikariConfig("/hikari.properties");
    dataSource = new HikariDataSource(config);
  }

  @PreDestroy
  public void releaseResource() {
    log.info("close data source");
    if(dataSource != null) {
      dataSource.close();
    }
  }

  /**
   *
   * @return
   */
  public HikariDataSource getDataSource() {
    return dataSource;
  }



}
````

MyBatisSqlSessionFactoryService.java
```java
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.annotation.PostConstruct;
import javax.inject.Inject;
import javax.inject.Singleton;
import javax.sql.DataSource;

@Singleton
public class MyBatisSqlSessionFactoryService {

  @Inject
  private DataSourceService dataSourceService;

  private SqlSessionFactory sqlSessionFactory;

  private static final Logger log = LoggerFactory.getLogger(MyBatisSqlSessionFactoryService.class);

  @PostConstruct
  public void initialize() {
    DataSource dataSource = dataSourceService.getDataSource();

    TransactionFactory transactionFactory = new JdbcTransactionFactory();
    Environment environment  = new Environment("developpment", transactionFactory, dataSource);

    Configuration configuration = new Configuration(environment);
    configuration.addMappers("happyjava.module.pgdemo.dao");

    sqlSessionFactory =
        new SqlSessionFactoryBuilder().build(configuration);

  }


  public SqlSessionFactory getSqlSessionFactory() {
    return sqlSessionFactory;
  }

  public SqlSession getSqlSession() {
    return sqlSessionFactory.openSession();
  }
}

BaseService.java

import javax.inject.Inject;

public class BaseService {

   @Inject
   protected MyBatisSqlSessionFactoryService sqlSessionFactoryService;
}

CatalogBasicService.java

import org.apache.ibatis.session.SqlSession;


import javax.inject.Singleton;
import java.util.List;

@Singleton
public class CatalogBasicService extends BaseService {




  public List<CatalogBasic> getAll() {

    List<CatalogBasic> lst = null;

    try(SqlSession sqlSession = sqlSessionFactoryService.getSqlSession()) {

      CatalogBasicDao dao = sqlSession.getMapper(CatalogBasicDao.class);

      lst = dao.findAll();
    }

    return lst;
  }

}

Is it ok?

[brandonlamb]

I struggled through trying to pair HikariCP with MyBatis myself and never did get it working. It seems like there is no simple, clean way to pass a datasource into the mybatis stuff other than JNDI, which was what I last was going to attempt.

Using Java EE (TomEE) we were able to tell mybatis.xml to use a JNDI resource, and we migrated from that to using the built-in connection pooling of mybatis by passing a map of properties (jdbc url, user, pass, driver).

When exploring micronaut and Jooby, I did more googling and was going to try using the low-level APIs for adding a JNDI resource

[ilopmar]

Before running the sample application you need a Mysql server. You can start one using Docker:

docker run -it --rm -p 3306:3306 -e MYSQL_ROOT_PASSWORD=secret -e MYSQL_DATABASE=test mysql:5.5

Then you can connect to mysql console (from inside the docker container):

$ mysql -u root -p test

# Create the table and insert rows
mysql> 
CREATE TABLE `category` (`category_id` int(11) NOT NULL AUTO_INCREMENT, `category_name` varchar(45) NOT NULL, PRIMARY KEY (`category_id`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

INSERT INTO `category`(category_name) VALUES ('Foo');
INSERT INTO `category`(category_name) VALUES ('Bar');
INSERT INTO `category`(category_name) VALUES ('Baz');

I've never used Mybatis before so I've found a simple tutorial to create a basic Mybatis application. I've created a new Micronaut application with the CLI and then in this commit https://github.com/ilopmar/micronaut-mybatis-hikari-example/commit/e4594ed5ae6610816764c747be0145b3bc4eeedf I've added basic Mybatis support. Start the application and open http://localhost:8080/categories/1 on your browser.

Then I've added Micronaut's jdbc-hikari and configured the @Factory class with your own example. Take a look at this commit https://github.com/ilopmar/micronaut-mybatis-hikari-example/commit/0aacc0106cd44b2231b926fa9f5da4b5ae60a394.
Start the application again and you can see that Hikari is used with the credentials configured in application.yml

16:33:06.406 [main] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
16:33:06.552 [main] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
16:33:06.832 [main] INFO  io.micronaut.runtime.Micronaut - Startup completed in 831ms. Server Running: http://localhost:8080

Also please keep in mind that I'm using @Context (https://github.com/ilopmar/micronaut-mybatis-hikari-example/blob/master/src/main/java/issue271/mybatis/MybatisFactory.java#L23) when creating the SqlSessionFactory bean because I want it to be created when the application starts up. The normal approach would be to use @Bean to only create the bean once it's needed for the first time.

[ilopmar]

@ggservice007, @brandonlamb please let me know if the example is good enough or if you need anything else to close the issue.

[ggservice007]

Thanks a lot.

Nice, you can close this issue.