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">
	<property name="dataSource" ref="mysqlDS"></property>
</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.