Spring JdbcTemplate、JPA 和许多其他 ORM 框架的非常简单和高性能的替代品

今天同事问有没有简单轻量的数据库操作库，我向他推荐了sql2o，可以在小项目中轻松使用。

sql2o参考文档

Sql2o 是一个小型 Java 库，它可以轻松地针对您的 JDBC 兼容数据库执行 sql 语句。

Sql2o 是 Spring JdbcTemplate、JPA 和许多其他 ORM 框架的非常简单和高性能的替代品。如上所示，它还与最流行的依赖注入框架之一完美融合。

配置

唯一需要的配置是数据库的连接 url、用户名和密码。此信息在 Sql2o 类的构造函数中指定。在下面的示例中，将 sql2o 设置为连接到本地 mysql 数据库。

Sql2o sql2o = new Sql2o("jdbc:mysql://localhost:3306/myDB", "myUsername", "topSecretPassword");

或者可以指定一个数据源。

Sql2o sql2o = new Sql2o(myDataSource);

通常只需要初始化一次。

public class myDao {

    private Sql2o sql2o;

    public myDao() {

        this.sql2o = new Sql2o("jdbc:mysql://localhost:3306/myDB", "myUsername", "topSecretPassword");

    }

}

程序启动时创建一次。

public class MyDao {

    private static Sql2o sql2o;

    static{

        sql2o = new Sql2o("jdbc:mysql://localhost:3306/myDB", "myUsername", "topSecretPassword");

    }

}

与 Spring 框架集成

<dependency>

<groupId>org.sql2o</groupId>

<artifactId>sql2o</artifactId>

<version>1.6.0</version>

</dependency>

Sql2o 实例是线程安全的。这意味着 Sql2o 可以在 Spring 运行时配置为单例组件。无论您使用的是基于 XML 还是基于 Java 的应用程序上下文，都可以轻松地将 Sql2o 添加到 Spring 上下文中。

基于 Javaconfig 的配置

@Configuration@EnableTransactionManagementpublic 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());

   }

}

springboot 配置

@Configuration

public class myConfiguration {

@Autowired

private DataSource dataSource;

@Bean

    public Sql2o sql2o(){

        return new Sql2o(dataSource);

    }

//使用方式伪代码

public void test(){

String sql = "SELECT count(id) FROM t_user";

        try (Connection con = sql2o().open()) {

            int count =  con.createQuery(sql).executeScalar(Integer.class);

            System.out.println(count);

        }}

}

从数据库中获取数据

当我们的 sql2o 实例在我们的 DAO 类中设置好后，我们就可以运行一些查询了。我们要做的第一件事是从数据库中获取一些数据。Sql2o 将数据解析为 POJO 对象。

public class Task {

    private Long id;

    private String description;

    private Date dueDate;

    // getters and setters here

}

public List<Task> getAllTasks(){

    String sql =

        "SELECT id, description, duedate " +

        "FROM tasks";

    try(Connection con = sql2o.open()) {

        return con.createQuery(sql).executeAndFetch(Task.class);

    }

}

public List<Task> getTasksBetweenDates(Date fromDate, Date toDate){

    String sql =

        "SELECT id, description, duedate " +

        "FROM tasks " +

        "WHERE duedate >= :fromDate AND duedate < :toDate";

    try(Connection con = sql2o.open()) {

        return con.createQuery(sql)

            .addParameter("fromDate", fromDate)

            .addParameter("toDate", toDate)

            .executeAndFetch(Task.class);

    }

}

简单类型映射

public Integer getStudentCount(){

    String sql = "SELECT count(id) FROM students";

    try (Connection con = sql2o.open()) {

        return con.createQuery(sql).executeScalar(Integer.class);

    }

}

public List<Integer> getStudentIdList(){

    String sql = "SELECT id FROM students";

    try (Connection con = sql2o.open()) {

        return con.createQuery(sql).executeScalarList(Integer.class);

    }   

}

将数据库结果作为 Map 对象列表返回。在这种情况下，List 的每个元素都是一个表示虚拟记录。

public List<Map<String,Object>> getReportData(){

    String complexSql = "...";

    try (Connection con = sql2o.open()) {

        return con.createQuery(complexSql).executeAndFetchTable().asList();

    }

}

懒加载

如果需要读取大量数据，使用 executeAndFetch() 可能会遇到内存问题。可以使用 executeAndFetchLazy() 遍历 ResultSet 并确保不会耗尽内存。

例如，假设数据库中有数百万个任务，并且希望分批读取 1,000 个任务，并在执行过程中将每个批次刷新到一个文件中：

public void readAndFlushAllTasks() {

    String sql = "SELECT id, description, duedate  FROM tasks";

    final int BATCH_SIZE = 1000;

    List<Task> batch = new ArrayList<Task>(BATCH_SIZE);

    try (Connection con = sql2o.open()) {

        try (ResultSetIterable<Task> tasks = con.createQuery(sql).executeAndFetchLazy(Task.class)) {

            for (Task task : tasks) {

                if (batch.size() == BATCH_SIZE) {

                    // here is where you flush your batch to file

                    batch.clear();

                }

                batch.add(task);

            }

            if (!batch.isEmpty()) {

                // also flush to your file the last items

                batch.clear();

            }

        }

    }

}

注意：ResultSetIterable 类是可自动关闭的，因此您可以将其包装在 try-with-resource 语句中。这可确保在您完成后关闭基础 ResultSet。

列映射

在 sql 查询中使用别名，如下例所示。

public List<Task> getTasksBetweenDates(Date fromDate, Date toDate){

    String sql =

        "SELECT id, description, due_date duedate " +

        "FROM tasks " +

        "WHERE duedate > :fromDate AND duedate < :toDate";

    try (Connection con = sql2o.open()) {

        return con.createQuery(sql)

            .addParameter("fromDate", fromDate)

            .addParameter("toDate", toDate)

            .executeAndFetch(Task.class);

    }

}

另一种方法是将列映射添加到 sql2o 查询。这可以通过调用 addColumnMapping 方法来完成，如下所示。

public List<Task> getTasksBetweenDates(Date fromDate, Date toDate){

    String sql =

        "SELECT id, description, due_date " +

        "FROM tasks " +

        "WHERE due_date > :fromDate AND due_date < :toDate";

    try (Connection con = sql2o.open()) {

        return con.createQuery(sql)

            .addParameter("fromDate", fromDate).addParameter("toDate", toDate)

            .addColumnMapping("DUE_DATE", "dueDate")

            .executeAndFetch(Task.class);

    }

}

映射公共的列

Map<String, String> colMaps = new HashMap<String,String>();

colMaps.put("DUE_DATE", "dueDate");colMaps.put("DESC", "description");colMaps.put("E_MAIL", "email");colMaps.put("SHORT_DESC", "shortDescription");

sql2o.setDefaultColumnMappings(colMaps);

更新和插入

使用 sql2o 更新和插入是使用 executeUpdate() 方法执行的。

更新示例

String updateSql = "update myTable set value = :valParam where id = :idParam";

try (Connection con = sql2o.open()) {

    con.createQuery(updateSql)

    .addParameter("valParam", foo)

    .addParameter("idParam", bar)

    .executeUpdate();

}

插入示例：

String insertSql =

"insert into myTable(id, value) values (:idParam, :valParam)";

try (Connection con = sql2o.open()) {

    con.createQuery(insertSql)

    .addParameter("idParam", bar)

    .addParameter("valParam", foo)

    .executeUpdate();

}

获取插入id

通过 createQuery 方法的重载来完成的：createQuery(String sql, boolean returnGeneratedKeys)要获取实际插入的值，请在执行语句后调用 getKey() 方法。

String sql = "insert into MYTABLE ( value ) values ( :val )";

try (Connection con = sql2o.open()) {

    int insertedId = con.createQuery(sql, true)

    .addParameter("val", someValue)

    .executeUpdate()

    .getKey();

}

bind()方法。

如果需要从 POJO 类中添加许多参数，可以使用 Query.bind(Object) 方法。

public class MyModel {

private int prop1;

private String prop2;

private String prop3;

private Date prop4;

// and so on..

// Getters and settes

}

为参数指定与模型类中相应属性相同的名称

String sql =

"insert into MYTABLE(col1, col2, col3, col4 ...) " +

"values (:prop1, :prop2, :prop3, :prop4 ...)";

try (Connection con = sql2o.open()) {

    con.createQuery(sql).bind(model).executeUpdate();

}

事务

可以通过调用Sql2o实例上的beginTransaction()方法打开一个事务；这将返回一个 Connection 实例。使用返回的 Connection 对象的 createQuery() 方法创建的所有查询都在事务中运行。调用 commit() 或 rollback() 来提交或回滚事务并关闭连接。

String sql1 = "INSERT INTO SomeTable(id, value) VALUES (:id, :value)";String sql2 = "UPDATE SomeOtherTable SET value = :val WHERE id = :id";

try (Connection con = sql2o.beginTransaction()) {

    con.createQuery(sql1).addParameter("id", idVariable1).addParameter("val", valueVariable1).executeUpdate();

    con.createQuery(sql2).addParameter("id", idVariable2).addParameter("val", valueVariable2).executeUpdate();

    con.commit();

}

注意：如果你没有在 Connection 对象上显式调用 commit() 或 rollback()，事务将在退出 try-with-resources 块时自动回滚。

批量运行查询

如果您需要使用不同的参数多次运行 UPDATE、INSERT 或 DELETE 查询，则通过批量运行它们将获得巨大的性能提升。

public void insertABunchOfRows(){

    final String sql = "INSERT INTO SomeTable(id, value) VALUES (:id, :value)";

    try (Connection con = sql2o.beginTransaction()) {

        Query query = con.createQuery(sql);

        for (int i = 0; i < 100; i++){

            query.addParameter("id", i).addParameter("value", "foo" + i).addToBatch();

        }

        query.executeBatch(); // executes entire batch

        con.commit();

    }

}

注意：应该在事务中批量处理。

注册自定义转换器

实现和使用自定义转换器。

示例：Java 8 LocalDate 到 java.sql.Date 转换。

首先，实现 org.sql2o.converters.Converter 接口

public class LocalDateConverter implements Converter<LocalDate> {

        @Override

        public LocalDate convert(final Object val) throws ConverterException {

            if (val instanceof java.sql.Date) {

                return ((java.sql.Date) val).toLocalDate();

            } else {

                return null;

            }

        }

        @Override

        public Object toDatabaseParam(final LocalDate val) {

            if (val == null) {

                return null;

            } else {

                return new java.sql.Date(val.atStartOfDay().toInstant(ZoneOffset.UTC).toEpochMilli());

            }

        }

    }

在实例化时注册

final Quirks quirks = new NoQuirks(){

    {

        converters.put(java.sql.Date.class, new LocalDateConverter ());

    }

};

final DataSource dataSource = //final

Sql2o sql2o = new Sql2o(dataSource, quirks);