<commit_before>sqlapi4j
===============
<NAME> <<EMAIL>>
v0.7, December 2011


Introduction
------------

Well known persistence frameworks like Hibernate generally approach the
persistence layer from the Java point of view and try to hide the SQL
nature of the database. This has repeatedly been a source of programmers'
headache, and we think that it is wrong to ignore the technical nature of
the database when working with the persistence layer.

With sqlapi4j we approach the problem from the database/SQL point
of view. We create a DSL which we want to look and behave like plain SQL
and thus follows the principle of least surprise. Our approach is type
safe (in contrast to, e. g., Hibernate criteria API) and even null safe,
if you use JSR305 annotations and FindBugs.

sqlapi4j-core
--------------------

Let's have a look at the following SQL table:

-------------------------------------
create table person (
    id bigint not null,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    age int not null,
    gender varchar(6) not null,
    constraint person_pk primary key(id)
);

create sequence person_seq;
-------------------------------------

and the following Java model:

-----------------------------------------------------------------------------------------------------------------------------------------------------------
public class Person {

    public static enum Gender {
        MALE,
        FEMALE,
        ;
    }

    @Nonnull private final Long id;
    @Nonnull private final String firstName;
    @Nonnull private final String lastName;
    private final int age;
    @Nonnull private final Gender gender;

    public Person(@Nonnull final Long id, @Nonnull final String firstName, @Nonnull final String lastName, final int age, @Nonnull final Gender gender) {
        super();
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
        this.gender = gender;
    }

    @Nonnull
    public Long getId() {
        return id;
    }

    @Nonnull
    public String getFirstName() {
        return firstName;
    }

    @Nonnull
    public String getLastName() {
        return lastName;
    }

    public int getAge() {
        return age;
    }

    @Nonnull
    public Gender getGender() {
        return gender;
    }

}
-----------------------------------------------------------------------------------------------------------------------------------------------------------

The mapping for this table and model is very simple:

-----------------------------------------------------------------------------------------------------------------
public class PersonTable {

    static final ColumnDef<Long>   ID         = ColumnDefs.long("id");
    static final ColumnDef<String> FIRST_NAME = ColumnDefs.varchar("first_name");
    static final ColumnDef<String> LAST_NAME  = ColumnDefs.varchar("last_name");
    static final ColumnDef<Integer> AGE       = ColumnDefs.integer("age");
    static final ColumnDef<Gender> GENDER     = ColumnDefs.enum("gender", Gender.class);

    static final TableDef TABLE = new TableDef("person", FIRST_NAME, LAST_NAME, AGE, GENDER);

}
-----------------------------------------------------------------------------------------------------------------

As you can see, both the column type and the column nullness are fixed
in the column mapping.

Building SQL select statements is now very easy, especially if you know
SQL:

---------------------------------------------------------
package com.example;
import static com.example.PersonTable.TABLE;
import static com.example.PersonTable.GENDER;
import static com.example.PersonTable.AGE;
import static com.example.PersonTable.LAST_NAME;
import static com.example.PersonTable.FIRST_NAME;

// ...

// find all female entries
final SqlCommand<SelectResult> command1 = SQL
    .select(TABLE.getColumns())
    .where(GENDER.eq(Gender.FEMALE));

// find all entries with age greater than 18
final SqlCommand<SelectResult> command2 = SQL
    .select(TABLE.getColumns())
    .where(AGE.gt(18))
    .orderBy(LAST_NAME, FIRST_NAME);

// find the average age grouped by gender
final SqlCommand<SelectResult> command3 = SQL
    .select(GENDER, Sql.avg(AGE))
    .groupBy(GENDER);
---------------------------------------------------------

Well, while this looks astonishingly easy, there are some more details
involved when executing the commands.

-----------------------------------------------------------------
try {
    final Connection connection = dataSource.getConnection();
    try {
        final SelectResult result = command1.execute(connection);
        return new Person(
            row.get(PersonTable.ID),
            row.get(PersonTable.FIRST_NAME),
            row.get(PersonTable.LAST_NAME),
            row.get(PersonTable.AGE),
            row.get(PersonTable.GENDER)
        );
    } catch (final SQLException e) {
        // ...
    } finally {
        if (connection != null) {
            connection.close();
        }
    }
} catch (final SQLException e) {
    // ...
}
-----------------------------------------------------------------

As you might expect, this can lead to a lot of boilerplate code. Of
course, you can tweak your connection and exception handling by
directly accessing the core functionality of sqlapi4j, but
in most cases it is more desirable to use the convenience
introduced by the sqlapi4j-dao package.

sqlapi4j-dao
-------------------

sqlapi4j-dao offers a bunch of convenience wrappers making the
everyday life much easier.

AbstractDao
~~~~~~~~~~~

The AbstractDao class takes care of connection handling, exception
handling, and of transforming the result rows into Java objects.

First we make minor adjustments to the Person model:

---------------------------------------------------------------------------------------------------------------------------------------------------------------
package com.example;
import static com.example.PersonTable.TABLE;
import static com.example.PersonTable.ID;
import static com.example.PersonTable.GENDER;
import static com.example.PersonTable.AGE;
import static com.example.PersonTable.LAST_NAME;
import static com.example.PersonTable.FIRST_NAME;

// ...

public class Person {

    public static enum Gender {
        MALE,
        FEMALE,
        ;
    }

    public static class PersonId implements Id {

        private PersonId(final long value) {
            super(value);
        }

        @Nonnull
        public static PersonId valueOf(final long value) {
            return new PersonId(value);
        }

    }

    @Nonnull private final PersonId id;
    @Nonnull private final String firstName;
    @Nonnull private final String lastName;
    private final int age;
    @Nonnull private final Gender gender;

    public Person(@Nonnull final PersonId id, @Nonnull final String firstName, @Nonnull final String lastName, final int age, @Nonnull final Gender gender) {
        super();
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
        this.gender = gender;
    }

    @Nonnull
    public PersonId getId() {
        return id;
    }

    @Nonnull
    public String getFirstName() {
        return firstName;
    }

    @Nonnull
    public String getLastName() {
        return lastName;
    }

    public int getAge() {
        return age;
    }

    @Nonnull
    public Gender getGender() {
        return gender;
    }

}
---------------------------------------------------------------------------------------------------------------------------------------------------------------

and to the table mapping:

-----------------------------------------------------------------------------------------------------------------
public class PersonTable {

    static final PKColumnDef<PersonId>    ID         = new PKColumnDef<PKColumnDef>("id", PersonId.class, "person_seq");
    static final ColumnDef<String> FIRST_NAME = ColumnDefs.varchar("first_name");
    static final ColumnDef<String> LAST_NAME  = ColumnDefs.varchar("last_name");
    static final ColumnDef<Integer> AGE       = ColumnDefs.integer("age");
    static final ColumnDef<Gender> GENDER     = ColumnDefs.enum("gender", Gender.class);

    static final TableDef TABLE = new TableDef("person", FIRST_NAME, LAST_NAME, AGE, GENDER);

}
-----------------------------------------------------------------------------------------------------------------

The above SQL select statements now look like this:

--------------------------------------------------------------------------------------------------
public static final ResultTransformer<Person> TRANSFORMER = new ResultTransformer<Person>() {

        @Override
        public Person apply(final SqlResultRow row) {
            return new Person(
                row.get(ID),
                row.get(FIRST_NAME),
                row.get(LAST_NAME),
                row.get(AGE),
                row.get(GENDER)
            );
        }
};

// ... create dao instance ..

final List<Person> females = dao.findAll(TRANSFORMER,
    TABLE,
    GENDER.eq(Gender.FEMALE)
);

final List<Person> over18 = dao.findAll(TRANSFORMER, dao.sql()
    .select(TABLE.getColumns())
    .where(AGE.gt(18))
    .orderBy(LAST_NAME, FIRST_NAME)
);
--------------------------------------------------------------------------------------------------

SingleTableDao
~~~~~~~~~~~~~~

The SingleTableDao class takes the ease of use even further by
defining a default table for SQL statements and a default result
transformer. Of course, you still have the power of the AbstractDao
at hand when you want to deviate from the defaults.

The above SQL select statements now look like this:

--------------------------------------------------------------------------------------------------
final List<Person> females = dao.findAll(GENDER.eq(Gender.FEMALE);

final List<Person> over18 = dao.findAll(dao.sql()
    .select(TABLE.getColumns())
    .where(AGE.gt(18))
    .orderBy(LAST_NAME, FIRST_NAME)
);
--------------------------------------------------------------------------------------------------
<commit_msg>fixed asciidoc README again<commit_after>sqlapi4j
========


Introduction
------------

Well known persistence frameworks like Hibernate generally approach the
persistence layer from the Java point of view and try to hide the SQL
nature of the database. This has repeatedly been a source of programmers'
headache, and we think that it is wrong to ignore the technical nature of
the database when working with the persistence layer.

With sqlapi4j we approach the problem from the database/SQL point
of view. We create a DSL which we want to look and behave like plain SQL
and thus follows the principle of least surprise. Our approach is type
safe (in contrast to, e. g., Hibernate criteria API) and even null safe,
if you use JSR305 annotations and FindBugs.

sqlapi4j-core
-------------

Let's have a look at the following SQL table:

-------------------------------------
create table person (
    id bigint not null,
    first_name varchar(255) not null,
    last_name varchar(255) not null,
    age int not null,
    gender varchar(6) not null,
    constraint person_pk primary key(id)
);

create sequence person_seq;
-------------------------------------

and the following Java model:

-----------------------------------------------------------------------------------------------------------------------------------------------------------
public class Person {

    public static enum Gender {
        MALE,
        FEMALE,
        ;
    }

    @Nonnull private final Long id;
    @Nonnull private final String firstName;
    @Nonnull private final String lastName;
    private final int age;
    @Nonnull private final Gender gender;

    public Person(@Nonnull final Long id, @Nonnull final String firstName, @Nonnull final String lastName, final int age, @Nonnull final Gender gender) {
        super();
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
        this.gender = gender;
    }

    @Nonnull
    public Long getId() {
        return id;
    }

    @Nonnull
    public String getFirstName() {
        return firstName;
    }

    @Nonnull
    public String getLastName() {
        return lastName;
    }

    public int getAge() {
        return age;
    }

    @Nonnull
    public Gender getGender() {
        return gender;
    }

}
-----------------------------------------------------------------------------------------------------------------------------------------------------------

The mapping for this table and model is very simple:

-----------------------------------------------------------------------------------------------------------------
public class PersonTable {

    static final ColumnDef<Long>   ID         = ColumnDefs.long("id");
    static final ColumnDef<String> FIRST_NAME = ColumnDefs.varchar("first_name");
    static final ColumnDef<String> LAST_NAME  = ColumnDefs.varchar("last_name");
    static final ColumnDef<Integer> AGE       = ColumnDefs.integer("age");
    static final ColumnDef<Gender> GENDER     = ColumnDefs.enum("gender", Gender.class);

    static final TableDef TABLE = new TableDef("person", FIRST_NAME, LAST_NAME, AGE, GENDER);

}
-----------------------------------------------------------------------------------------------------------------

As you can see, both the column type and the column nullness are fixed
in the column mapping.

Building SQL select statements is now very easy, especially if you know
SQL:

---------------------------------------------------------
package com.example;
import static com.example.PersonTable.TABLE;
import static com.example.PersonTable.GENDER;
import static com.example.PersonTable.AGE;
import static com.example.PersonTable.LAST_NAME;
import static com.example.PersonTable.FIRST_NAME;

// ...

// find all female entries
final SqlCommand<SelectResult> command1 = SQL
    .select(TABLE.getColumns())
    .where(GENDER.eq(Gender.FEMALE));

// find all entries with age greater than 18
final SqlCommand<SelectResult> command2 = SQL
    .select(TABLE.getColumns())
    .where(AGE.gt(18))
    .orderBy(LAST_NAME, FIRST_NAME);

// find the average age grouped by gender
final SqlCommand<SelectResult> command3 = SQL
    .select(GENDER, Sql.avg(AGE))
    .groupBy(GENDER);
---------------------------------------------------------

Well, while this looks astonishingly easy, there are some more details
involved when executing the commands.

-----------------------------------------------------------------
try {
    final Connection connection = dataSource.getConnection();
    try {
        final SelectResult result = command1.execute(connection);
        return new Person(
            row.get(PersonTable.ID),
            row.get(PersonTable.FIRST_NAME),
            row.get(PersonTable.LAST_NAME),
            row.get(PersonTable.AGE),
            row.get(PersonTable.GENDER)
        );
    } catch (final SQLException e) {
        // ...
    } finally {
        if (connection != null) {
            connection.close();
        }
    }
} catch (final SQLException e) {
    // ...
}
-----------------------------------------------------------------

As you might expect, this can lead to a lot of boilerplate code. Of
course, you can tweak your connection and exception handling by
directly accessing the core functionality of sqlapi4j, but
in most cases it is more desirable to use the convenience
introduced by the sqlapi4j-dao package.

sqlapi4j-dao
------------

sqlapi4j-dao offers a bunch of convenience wrappers making the
everyday life much easier.

AbstractDao
~~~~~~~~~~~

The AbstractDao class takes care of connection handling, exception
handling, and of transforming the result rows into Java objects.

First we make minor adjustments to the Person model:

---------------------------------------------------------------------------------------------------------------------------------------------------------------
package com.example;
import static com.example.PersonTable.TABLE;
import static com.example.PersonTable.ID;
import static com.example.PersonTable.GENDER;
import static com.example.PersonTable.AGE;
import static com.example.PersonTable.LAST_NAME;
import static com.example.PersonTable.FIRST_NAME;

// ...

public class Person {

    public static enum Gender {
        MALE,
        FEMALE,
        ;
    }

    public static class PersonId implements Id {

        private PersonId(final long value) {
            super(value);
        }

        @Nonnull
        public static PersonId valueOf(final long value) {
            return new PersonId(value);
        }

    }

    @Nonnull private final PersonId id;
    @Nonnull private final String firstName;
    @Nonnull private final String lastName;
    private final int age;
    @Nonnull private final Gender gender;

    public Person(@Nonnull final PersonId id, @Nonnull final String firstName, @Nonnull final String lastName, final int age, @Nonnull final Gender gender) {
        super();
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;
        this.age = age;
        this.gender = gender;
    }

    @Nonnull
    public PersonId getId() {
        return id;
    }

    @Nonnull
    public String getFirstName() {
        return firstName;
    }

    @Nonnull
    public String getLastName() {
        return lastName;
    }

    public int getAge() {
        return age;
    }

    @Nonnull
    public Gender getGender() {
        return gender;
    }

}
---------------------------------------------------------------------------------------------------------------------------------------------------------------

and to the table mapping:

-----------------------------------------------------------------------------------------------------------------
public class PersonTable {

    static final PKColumnDef<PersonId>    ID         = new PKColumnDef<PKColumnDef>("id", PersonId.class, "person_seq");
    static final ColumnDef<String> FIRST_NAME = ColumnDefs.varchar("first_name");
    static final ColumnDef<String> LAST_NAME  = ColumnDefs.varchar("last_name");
    static final ColumnDef<Integer> AGE       = ColumnDefs.integer("age");
    static final ColumnDef<Gender> GENDER     = ColumnDefs.enum("gender", Gender.class);

    static final TableDef TABLE = new TableDef("person", FIRST_NAME, LAST_NAME, AGE, GENDER);

}
-----------------------------------------------------------------------------------------------------------------

The above SQL select statements now look like this:

--------------------------------------------------------------------------------------------------
public static final ResultTransformer<Person> TRANSFORMER = new ResultTransformer<Person>() {

        @Override
        public Person apply(final SqlResultRow row) {
            return new Person(
                row.get(ID),
                row.get(FIRST_NAME),
                row.get(LAST_NAME),
                row.get(AGE),
                row.get(GENDER)
            );
        }
};

// ... create dao instance ..

final List<Person> females = dao.findAll(TRANSFORMER,
    TABLE,
    GENDER.eq(Gender.FEMALE)
);

final List<Person> over18 = dao.findAll(TRANSFORMER, dao.sql()
    .select(TABLE.getColumns())
    .where(AGE.gt(18))
    .orderBy(LAST_NAME, FIRST_NAME)
);
--------------------------------------------------------------------------------------------------

SingleTableDao
~~~~~~~~~~~~~~

The SingleTableDao class takes the ease of use even further by
defining a default table for SQL statements and a default result
transformer. Of course, you still have the power of the AbstractDao
at hand when you want to deviate from the defaults.

The above SQL select statements now look like this:

--------------------------------------------------------------------------------------------------
final List<Person> females = dao.findAll(GENDER.eq(Gender.FEMALE);

final List<Person> over18 = dao.findAll(dao.sql()
    .select(TABLE.getColumns())
    .where(AGE.gt(18))
    .orderBy(LAST_NAME, FIRST_NAME)
);
--------------------------------------------------------------------------------------------------

{'fraction_non_alphanumeric': 0.2496802125356686, 'fraction_numerical': 0.0035422611433631804, 'mean_word_length': 1.566540404040404, 'pattern_counts': {'":': 0, '<': 46, '<?xml version=': 0, '>': 46, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2077047', 'n_tokens_mistral': 4977, 'n_tokens_neox': 4785, 'n_words': 1656}