Integration with Spring Framework

Sql2o instance is thread-safe. This means that Sql2o can be configured as a singleton component in Spring runtime. Adding Sql2o to your Spring context is easy either you are using XML-based or Java-based application contexts.

Configure Sql2o in Spring Context

There are multiple ways to create DataSource object. The method shown below is chosen for its simplicity.

XML based configuration

<bean id="mysqlDS" class="org.apache.commons.dbcp.BasicDataSource">
	<property name="driverClassName">com.mysql.jdbc.Driver</property>
	<property name="url">jdbc:mysql://localhost:3306/testDB</property>
	<property name="username">user</property>
	<property name="password">pass</property>
</bean>
<bean id="sql2o" class="org.sql2o.Sql2o">
	<constructor-arg ref="mysqlDS"/>
</bean>

In addition, if you will want to use Spring's declarative transactional management, add the following

   <tx:annotation-driven transaction-manager="txManager"/>
   <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
       <property name="dataSource" ref="mysqlDS"/>
   </bean>

Java-based configuration

@Configuration
@EnableTransactionManagement
public class DatabaseContext implements TransactionManagementConfigurer {

   @Bean
   public DataSource dataSource() {
      final BasicDataSource dataSource = new BasicDataSource();
      dataSource.setDriverClassName("com.mysql.jdbc.Driver");
      dataSource.setUrl("jdbc:mysql://localhost:3306/testDB");
      dataSource.setUsername("user");
      dataSource.setPassword("pass");
   }

   @Bean
   @Override
   public PlatformTransactionManager annotationDrivenTransactionManager() {
      return new DataSourceTransactionManager(dataSource());
   }

   @Bean
   public Sql2o sql2o() {
      return new Sql2o(dataSource());
   }

}

Define Model Class

package org.sql2o.domain;

public class Student{
	private int studentId;
	private String firstName;
	private String lastName;
	...
}

Define Spring DAO contract

package org.sql2o.test;

import org.sql2o.domain.Student;

public interface TestRepository {
	
	public int getStudentCount();
	public Student getStudent(int studentId);

}

Autowire Sql2o in Spring DAO Layer

package org.sql2o.test.impl;

import org.sql2o.Sql2o;
import org.sql2o.domain.Student;
import org.sql2o.test.TestRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class TestRepositoryImpl implements TestRepository {
	@Autowired
	private Sql2o sql2o;
	
	@Override
	public int getStudentCount(){
		String sql = "SELECT count(id) FROM students";
                
		try (Connection con = sql2o.open()) {
			return con.createQuery(sql).executeScalar(Integer.class);
		}
	}
	
	@Override
	public Student getStudent(int studentId){
		String sql = "SELECT * FROM students where id=:id";

		try (Connection con = sql2o.open()) {
			return con.createQuery(sql)
				.addParameter("id", studentId)
				.executeAndFetchFirst(Student.class);;
		}
	}
}

And That's it!!

Sql2o is very simple and high-performance alternative to Spring JdbcTemplate, JPA and many other ORM frameworks. As shown above, it also gels perfectly with one of the most popular dependency injection frameworks.

You can also try a Java code generator for Spring Boot projects that uses Sql2o. It autogenerates Model, DAO contract, DAO Layer and Service. You can find it here: https://marco-sulla.github.io/ewa/