sql2o is a small library, which makes it easy to convert the result of your sql-statements into objects. No resultset hacking required. Kind of like an orm, but without the sql-generation capabilities. Supports named parameters.

Related tags

sql2o
Overview

sql2o Build Status Maven Central Coverage Status

Sql2o is a small java library, with the purpose of making database interaction easy. When fetching data from the database, the ResultSet will automatically be filled into your POJO objects. Kind of like an ORM, but without the SQL generation capabilities. Sql2o requires at Java 7 or 8 to run. Java versions past 8 may work, but is currently not supported.

Examples

Check out the sql2o website for examples.

Performance

A key feature of sql2o is performance. The following metrics were based off the Dapper.NET metrics. Note that typical usage can differ from optimal usage for many frameworks. Depending on the framework, typical usage may not involve writing any SQL, or it may map underscore case to camel case, etc.

Performance of SELECT

Execute 1000 SELECT statements against a DB and map the data returned to a POJO. Code is available here.

Method Duration
Hand coded ResultSet 60ms
Sql2o 75ms (25% slower)
Apache DbUtils 98ms (63% slower)
JDBI 197ms (228% slower)
MyBatis 293ms (388% slower)
jOOQ 447ms (645% slower)
Hibernate 494ms (723% slower)
Spring JdbcTemplate 636ms (960% slower)

Contributing

Want to contribute? Awesome! Here's how to set up.

Coding guidelines.

When hacking sql2o, please follow these coding guidelines.

Note on running Oracle specific tests

In order to run the Oracle database tests you will have to add the Oracle Maven repo to your settings.xml as instructed in the Oracle Fusion Middleware Maven Setup guide

Issues
  • addParameter with a list

    addParameter with a list

    It should be possible to add a parameter with a list or an array.

    Example:

    int[] ids = new int[]{1,2,3};
    String sql = "select * from mytable where id in (:idParam)";
    try (Connectio con = sql2o.open()) {
        return con.createQuery(sql)
                .addParameter("idParam", ids)
                .executeAndFetch(Pojo.class);
    }
    
    feature request priority-normal 
    opened by aaberg 22
  • Is not no longer updated, 1.6.0-RC3 has now been a long time

    Is not no longer updated, 1.6.0-RC3 has now been a long time

    Is not no longer updated, 1.6.0-RC3 has now been a long time

    opened by objectboy2016 15
  • Constructors are not called with version 1.5.0-SNAPSHOT

    Constructors are not called with version 1.5.0-SNAPSHOT

    It seems like constructors are not called on POJOS when they are created with the UnsafeFieldSetterFactory. I guess the problem is with the Unsafe class on line 250

    This needs to be fixed before 1.5.0 release.

    bug priority-high 
    opened by aaberg 12
  • Bidirectional Converters and Quirks

    Bidirectional Converters and Quirks

    • Converters can go both directions, see https://github.com/aaberg/sql2o/issues/65
    • Quirks is now an interface rather than an enum, see https://github.com/aaberg/sql2o/issues/62

    Before merging, I want to test on a DB2 database because the Query#addParameter(String, Date) no longer exists. @aaberg do you have a DB2 db available to you?

    Note: at some point we may want to move the quirks implementations to respective extension packages

    feature request priority-normal 
    opened by aldenquimby 10
  • Please add support for Java8 java.time.LocalDate

    Please add support for Java8 java.time.LocalDate

    Thank you for your wonderful library, I'm integrating it into my framework (Vaadin on Kotlin) and it works flawlessly. One thing though: if I have a pojo with java.time.LocalDate, it seems that it cannot be mapped since reading such pojo from a database will result in

    at sun.reflect.GeneratedMethodAccessor12.invoke(Unknown Source)
    	at org.sql2o.reflection.MethodAccessorsGenerator$2.setProperty(MethodAccessorsGenerator.java:126)
    	at org.sql2o.DefaultResultSetHandlerFactory$4.handle(DefaultResultSetHandlerFactory.java:180)
    	at org.sql2o.PojoResultSetIterator.readNext(PojoResultSetIterator.java:34)
    	at org.sql2o.ResultSetIteratorBase.safeReadNext(ResultSetIteratorBase.java:87)
    	at org.sql2o.ResultSetIteratorBase.hasNext(ResultSetIteratorBase.java:52)
    	at org.sql2o.Query.executeAndFetch(Query.java:455)
    	at org.sql2o.Query.executeAndFetch(Query.java:441)
    

    It seems that the type is correctly of type LocalDate, however actual value is java.sql.Date. I'm using H2, the database column type is DATE. Thanks!

    opened by mvysny 10
  • Performance tests

    Performance tests

    This adds performance tests for a basic SELECT mapping query against many Java ORMs. Also, the readme is now markdown and includes the performance results.

    Included:

    The basic idea of each performance test is to set up a connection in the constructor, then time how long it takes to execute the run method 1000 times.

    TODO BEFORE MERGING

    • update the readme with results from a fast machine

    We could add more down the road, like:

    Other interesting ORMs that we won't test against (for now):

    • persism - not available via maven
    opened by aldenquimby 9
  • Simplify api by removing executeScalar methods

    Simplify api by removing executeScalar methods

    The basic idea here is to use the standard executeAndFetch methods everywhere and remove the executeScalar methods.

    To implement this, we simply check to see if the type the caller is asking for has a registered converter. So for example:

    executeAndFetchFirst(Time.class) means we know they actually want a scalar because there is a registered converter for Time.class.

    executeAndFetchFirst(MyComplexObject.class) means we know they actually want a object mapping because we have no way of generating the object from a single column.

    This solves the problem brought up here where an object may have a property with the same name as the first column. Everything is based off the type the user is asking for.

    Any thoughts on this?

    opened by aldenquimby 9
  • PreparedStatement.isClosed method is not supported on all DB2 drivers

    PreparedStatement.isClosed method is not supported on all DB2 drivers

    Will throw a NullPointerException with some db2 drivers. When a sql2o connection is closed, sql2o will iterate over all created statements and make sure they are closed. This logic calls the isClosed method.

    Either this should be moved to Quirks, or sql2o should just call close without first checking if statement is already closed.

    bug priority-normal 
    opened by aaberg 8
  • how to use

    how to use "inner join" with sql2o to return pojos

    hi,aaberg: sql2o is a good job!I saw the samples in official website.it's easy and so concise. but the only question is how can I use "inner join" with sql2o to return the result "POJO" for me,just like hibernate .the pojo contains other pojos.

    Thanks. tudou

    feature request 
    opened by lizhuangs 8
  • Library uses now-private reflection mechanism

    Library uses now-private reflection mechanism

    MethodAccessorsGenerator uses a series of now-hidden, potentially soon-to-be-removed APIs in sun.reflect (these were moved into jdk,.internal.reflect and are no longer accessible in JDK11+.

    Is there an alternative to these classes (I Googled for replacements but found surprisingly nothing on this). I'm assuming this code is used by the row/object mapping when retrieving results from the DB.

    opened by vertex-github 0
  • How to correctly insert with array type (ex bigint[]) columns?

    How to correctly insert with array type (ex bigint[]) columns?

    In my case, I have a class Product and table product:

    @Entity(name = "product")
    @Table(schema = "delivery", name = "product")
    @Getter
    @Setter
    public class Product extends BaseEntity<Long> {
    
        @Column(name = "cost")
        private Double cost;
    
        @Column(name = "picture")
        private String picture;
    
        @Column(name = "categories")
        private List<Long> categories;
    
    }
    
    create table product
    (
        id         bigserial                              not null
            constraint product_pk
                primary key,
        title      varchar                                not null,
        created    timestamp with time zone default now() not null,
        updated    timestamp with time zone,
        cost       double precision         default 0     not null,
        categories bigint[],
        picture    varchar
    );
    

    There is a problem with categories field.

    I want to insert Product object to database. Running

    Product product = new Product();
    
            product.setTitle("Milk");
            product.setCost(100.);
            product.setCategories(Arrays.asList(10l, 20l));
    
            String query = "insert into delivery.product (title, cost, categories) " +
                    "values (:title, :cost, :categories)";
    
            try (Connection con = sql2o.beginTransaction()) {
                con.createQuery(query)
                        .bind(product)
                        .executeUpdate();
    
                con.commit();
            }
    

    I get org.sql2o.Sql2oException: Error in executeUpdate, ERROR: INSERT has more expressions than target columns, because the final query looks like insert into delivery.product (title, cost, categories) values (?, ?, ?,?) RETURNING *. One parameter of type List<Long> transformed to two positions in query!

    The solution I found is to use

    String query = "insert into delivery.product (title, cost, categories) " +
                    "values (:title, :cost, :categories)";
    
            try (Connection con = sql2o.beginTransaction()) {
                Query q = con.createQuery(query)
                        .bind(product);
    
                Long[] longs = new Long[product.getCategories().size()];
                product.getCategories().toArray(longs);
                try {
                    q.addParameter("categories", con.getJdbcConnection().createArrayOf("bigint", longs));
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                q.executeUpdate();
    
                con.commit();
            }
    

    But it is not good idea, I think.

    The main question is: can we make Qyery.bind() work with List/Array parameters when insert?

    opened by iamhook 0
  • NPE when return query object is of type Map

    NPE when return query object is of type Map

    In the query example shown on your website, a query is made and then the result returned as a List of Customer objects (List). This necessitates the creation of the model object, Customer.

    What if someone does not want to create the model object? Is it possible to use a Map as the return model object? The map key would contain the column name ("id" / "name" / "address"), with the key's value containing the the value of that column, for that particular row.

    I tried it and I got a NPE:

    var queryText = "select * from customers"; List list = con.createQuery(queryText).executeAndFetch(Map.class);

    Exception in thread "main" java.lang.NullPointerException at org.sql2o.reflection.PojoMetadata.initializePropertyInfo(PojoMetadata.java:145) at org.sql2o.reflection.PojoMetadata.access$200(PojoMetadata.java:19) at org.sql2o.reflection.PojoMetadata$Cache.evaluate(PojoMetadata.java:244) at org.sql2o.reflection.PojoMetadata$Cache.evaluate(PojoMetadata.java:241) at org.sql2o.tools.AbstractCache.get(AbstractCache.java:49) at org.sql2o.reflection.PojoMetadata.getPropertyInfoThroughCache(PojoMetadata.java:81) at org.sql2o.reflection.PojoMetadata.(PojoMetadata.java:68) at org.sql2o.DefaultResultSetHandlerFactoryBuilder.newFactory(DefaultResultSetHandlerFactoryBuilder.java:61) at org.sql2o.Query.newResultSetHandlerFactory(Query.java:545) at org.sql2o.Query.executeAndFetch(Query.java:587)

    Thank you in advance, Daniel

    opened by danuvian 0
  • State of the Project

    State of the Project

    Is sql2o still actively being supported and maintained? Are there any plans to turn over maintenance of the project to new hands if not? I'd like to volunteer to help.

    opened by The-Funk 0
  • sql2o can't rolback ?

    sql2o can't rolback ?

    The "connection.rollback()" has execute,But data still updated. EG :

        public void write() {
            try (Connection connection = sql2o.beginTransaction()) {
                Query q = connection.createQuery(" UPDATE `users` set name = 'alice' where id = 1");
                q.setAutoDeriveColumnNames(true);
                q.executeUpdate();
                // DO SAVE
                connection.commit();
                // TEST DATA ROLLBACK, The "connection.rollback()" has execute but data still updated.
                connection.rollback();
            }
        }
    
    
    opened by mailtous 3
  • Publish version 1.6.0 of oracle extensions

    Publish version 1.6.0 of oracle extensions

    I noticed that the sql2o-oracle and sql2o-oracle-joda-time artifacts were last published in 2016 with version 1.6.0-RC3.

    https://search.maven.org/search?q=sql2o-oracle

    The main Sql2o artifact and other extensions have version 1.6.0 available in Maven Central. Would it be possible to release version 1.6.0 of the Oracle extensions?

    opened by aalmiray 0
  • Issue with ArrayParameters.updateQueryWithArrayParameters when query contains '?' character

    Issue with ArrayParameters.updateQueryWithArrayParameters when query contains '?' character

    Given the following Sql2o query:

    SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100') FROM table WHERE foo = :foo AND bar in (:bar)

    When building the prepared statement, Sql2o calls static method ArrayParameters.updateQueryWithArrayParameters after having replaced named parameters by '?' characters.

    Lets say I set 2 values in my bar named parameters, we will call the static method with parameters:

    • parsedQuery

    SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100') FROM table WHERE foo = ? AND bar in (?)

    • arrayParametersSortedAsc

    [ArrayParameter{parameterIndex: 2, parameterCount: 2}]

    As the method iterates through the parsed query and searches for '?' characters to try and add the '?' character 1 more time into the parsed query, it count the first '?' from my CONCAT('http://phatdomain.com/', path, '?width=100') statement and then fails to add '?' placeholder at the right place, giving the resulting parsed query:

    SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100') FROM table WHERE foo = ?,? AND bar in (?)

    Here is a simple test case to reproduce the issue:

    package org.sql2o;
    
    import com.google.common.collect.ImmutableList;
    import org.junit.jupiter.api.Assertions;
    import org.junit.jupiter.api.Test;
    
    /**
     * Created by romain on 04/12/2019
     */
    public class SimpleTest {
    
        @Test
        public void works() {
            String query = "SELECT name, CONCAT('http://phatdomain.com/', path) FROM table WHERE foo = ? AND bar in (?)";
            ArrayParameters.ArrayParameter barArrayParameter = new ArrayParameters.ArrayParameter(2, 2);
            query = ArrayParameters.updateQueryWithArrayParameters(query, ImmutableList.of(barArrayParameter));
            Assertions.assertEquals("SELECT name, CONCAT('http://phatdomain.com/', path) FROM table WHERE foo = ? AND bar in (?,?)", query);
        }
    
        @Test
        public void doesNotWork() {
            String query = "SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100') FROM table WHERE foo = ? AND bar in (?)";
            ArrayParameters.ArrayParameter barArrayParameter = new ArrayParameters.ArrayParameter(2, 2);
            query = ArrayParameters.updateQueryWithArrayParameters(query, ImmutableList.of(barArrayParameter));
            Assertions.assertEquals("SELECT name, CONCAT('http://phatdomain.com/', path, '?width=100') FROM table WHERE foo = ? AND bar in (?,?)", query);
        }
    
    }
    
    opened by rdigiorgio 0
  • Column mappings failed

    Column mappings failed

    Based on this docs: https://github.com/aaberg/sql2o/wiki/Column-mappings

    1. Failed mapping when SELECT

    Old query: SELECT created_date createdDate, user_id userId, phone FROM mob_user WHERE userId = :userIdParam Exception: Database error: ORA-00904: "USERID": invalid identifier

    But its success when query like this: New query: SELECT created_date createdDate, user_id userId, phone FROM mob_user WHERE user_id = :userIdParam

    1. Failed mapping when UPDATE

    Old query: UPDATE mob_user SET cuid = :cuidParam WHERE user_id = :userIdParam Exception: - (but field not updated)

    But its success when query like this: New query: UPDATE mob_user SET cuid = :cuidParam WHERE phone = :phoneParam

    Can you help me what's wrong??? I see something is wrong with the column using underscore. I'm using sql2o version 1.6.

    MAVEN

    org.sql2o sql2o 1.6.0
    opened by LinggaAskaEdo 1
  • Remove case sensitive converter

    Remove case sensitive converter

    In Boolean converter single character made case sensitive

    opened by ashwanisingh1 0
Releases(sql2o-parent-1.6.0)
R2DBC Driver for Oracle Database

About Oracle R2DBC The Oracle R2DBC Driver is a Java library that supports reactive programming with Oracle Database. Oracle R2DBC implements the R2DB

Oracle 59 Mar 24, 2021
Speedment is a Stream ORM Java Toolkit and Runtime

Java Stream ORM Speedment is an open source Java Stream ORM toolkit and runtime. The toolkit analyzes the metadata of an existing SQL database and aut

Speedment 1.9k May 5, 2021
requery - modern SQL based query & persistence for Java / Kotlin / Android

A light but powerful object mapping and SQL generator for Java/Kotlin/Android with RxJava and Java 8 support. Easily map to or create databases, perfo

requery 3k Mar 10, 2021
jOOQ is the best way to write SQL in Java

jOOQ's reason for being - compared to JPA Java and SQL have come a long way. SQL is an "ancient", yet established and well-understood technology. Java

jOOQ Object Oriented Querying 4.3k Mar 13, 2021
A zero ceremony ORM for Java

Release notes Welcome Persism is a wood simple, auto discovery, auto configuration, and convention over configuration ORM (Object Relational Mapping)

Dan Howard 10 Mar 27, 2021
光 HikariCP・A solid, high-performance, JDBC connection pool at last.

HikariCP It's Faster.Hi·ka·ri [hi·ka·'lē] (Origin: Japanese): light; ray. Fast, simple, reliable. HikariCP is a "zero-overhead" production ready JDBC

Brett Wooldridge 14.7k Mar 12, 2021
MariaDB Embedded in Java JAR

What? MariaDB4j is a Java (!) "launcher" for MariaDB (the "backward compatible, drop-in replacement of the MySQL(R) Database Server", see FAQ and Wiki

Michael Vorburger ⛑️ 578 Mar 11, 2021
Realm is a mobile database: a replacement for SQLite & ORMs

Realm is a mobile database that runs directly inside phones, tablets or wearables. This repository holds the source code for the Java version of Realm

Realm 11.1k Mar 11, 2021
Java code generator for calling PL/SQL.

OBridge OBridge provides a simple Java source code generator for calling Oracle PL/SQL package procedures. Supported input, output parameters and retu

Ferenc Karsany 19 Feb 24, 2021
JDBC driver for ClickHouse

This is a basic and restricted implementation of jdbc driver for ClickHouse. It has support of a minimal subset of features to be usable.

ClickHouse 644 May 6, 2021
Persistent priority queue over sql

queue-over-sql This projects implement a persistent priority queue (or a worker queue) (like SQS, RabbitMQ and others) over sql. Why? There are some c

Shimon Magal 12 Mar 21, 2021
A lightweight and performant Java ORM alternative.

LightORM A lightweight and performant Java ORM alternative. LightORM has annotation processors so that all the integration code with the database is g

Jailson Pereira 12 Mar 13, 2021
MixStack lets you connects Flutter smoothly with Native pages, supports things like Multiple Tab Embeded Flutter View, Dynamic tab changing, and more. You can enjoy a smooth transition from legacy native code to Flutter with it.

中文 README MixStack MixStack lets you connects Flutter smoothly with Native pages, supports things like Multiple Tab Embeded Flutter View, Dynamic tab

Yuewen Engineering 59 Apr 2, 2021