zero dependency database access for java
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
Sebastian Hoß 2b4e11be15 fix #96 3 years ago
.settings checkin eclipse .settings files 3 years ago
AUTHORS fix #23 3 years ago
build/jenkins read rc file from bin 3 years ago
yosql-acceptance-tests fix #90 3 years ago
yosql-cli squid:S1181 3 years ago
yosql-core fix #96 3 years ago
yosql-example don't depend on yosql-core 3 years ago
yosql-gradle-plugin fix #60 3 years ago
yosql-maven-plugin fix #59 3 years ago
yosql-micro-benchmarks fix #96 3 years ago
yosql-testutils squid:S00112 3 years ago
.bazelrc show errors during test execution by default 3 years ago
.classpath add real acceptance test 3 years ago
.factorypath fix #29 3 years ago
.gitignore checkin eclipse .settings files 3 years ago
.project checkin eclipse project setup files 3 years ago
CODE_OF_CONDUCT.asciidoc fix #33 3 years ago
CONTRIBUTING.asciidoc fix #32 3 years ago
LICENSE add LICENSE 3 years ago
Makefile fix #52 3 years ago
README.asciidoc add example help output 3 years ago
WORKSPACE fix #88 3 years ago

README.asciidoc

= yosql image:https://img.shields.io/badge/email-%40metio-brightgreen.svg?style=social&label=mail["Discuss on Google Groups", link="https://groups.google.com/forum/#!forum/metio"] image:https://img.shields.io/badge/irc-%23metio.wtf-brightgreen.svg?style=social&label=IRC["Chat on IRC", link="http://webchat.freenode.net/?channels=metio.wtf"]
Sebastian Hoß <https://seb.xn--ho-hia.de/[@sebhoss]>
:github-org: sebhoss
:project-name: yosql
:toc:
:toc-placement: preamble

image:https://img.shields.io/badge/license-cc%20zero-000000.svg?style=flat-square["CC Zero", link="http://creativecommons.org/publicdomain/zero/1.0/"]
image:https://www.openhub.net/p/{project-name}/widgets/project_thin_badge.gif["Open Hub statistics", link="https://www.openhub.net/p/{project-name}"]

'''

link:https://github.com/krisajenkins/yesql[yesql] inspired build tool that bridges the world of Java & SQL with zero runtime dependencies. It expects a set of .sql files as inputs and generates the required Java code to call the SQL statements define in those .sql files. Besides the generic CLI, support for Maven and Gradle is link:https://github.com/sebhoss/yosql/milestones?direction=asc&sort=due_date&state=open[planned].

'''

== Usage

=== Maven

1) Add the plugin to your *pom.xml*:

[source, xml]
----
<build>
<plugins>
...
<plugin>
<groupId>com.github.sebhoss.yosql</groupId>
<artifactId>yosql-maven-plugin</artifactId>
<version>0.0.1</version>
</plugin>
...
</plugins>
</build>
----

2) Add .sql files in *src/main/yosql* and write SQL statements into them.

[source]
----
project/
├── pom.xml
└── src/
└── main/
└── yosql/
└── user/
├── findUser.sql
└── addUser.sql
└── item/
├── queryAllItems.sql
└── createItemTable.sql
----

3) Execute the *yosql:generate* goal (or just run `mvn generate-sources`) to generate the Java code.

=== Bazel

1) Add git repository to your `WORKSPACE`:

[source]
----
git_repository(
name = "yosql",
remote = "https://github.com/sebhoss/yosql.git",
tag = "0.0.1-bazel",
)
----


2) Write .sql files in a directory of your choice (e.g. `persistence`)

[source]
----
project/
├── WORKSPACE
└── persistence/
├── BUILD
└── user/
├── findUser.sql
└── addUser.sql
└── item/
├── queryAllItems.sql
└── createItemTable.sql
----

3) Declare a `genrule` in one of your BUILD files:

[source]
----
filegroup(
name = "your-sql-files",
srcs = glob(["persistence/**/*.sql"]),
)

genrule(
name = "yosql-example",
srcs = [":your-sql-files"],
outs = [
"com/example/persistence/UserRepository.java",
"com/example/persistence/ItemRepository.java",
"com/example/persistence/util/ResultRow.java",
"com/example/persistence/util/ResultState.java",
"com/example/persistence/util/FlowState.java",
"com/example/persistence/converter/ToResultRowConverter.java",
],
cmd = """
$(location @yosql//yosql-cli) \
--inputBaseDirectory persistence \
--outputBaseDirectory $(@D) \
--logLevel off
""",
tools = ["@yosql//yosql-cli"],
)
----

4) Depend on the generated sources by using `:yosql-example` in the `srcs` of another rule.

=== Standalone (CLI)

1) Download the latest release

2) Write .sql files in a directory of your choice (e.g. `/path/to/your/sql/files`)

3) Call `java -jar yosql-cli.jar --inputBaseDirectory /path/to/your/sql/files --outputBaseDirectory /output/path`

'''

The above configuration is enough to generate two repositories: *UserRepository* and *ItemRepository*. The name is derived from the name of the folder that contains .sql files and can be configured as seen below. Each of them will have regular Java methods to execute the SQL statements defined in those .sql files. The `UserRepository` for example exposes these methods (based on *findUser.sql*):

[source, java]
----
// eagerly loads all results and puts them into the result list
List<ResultRow> findUser(Object userId)

// same as above, but calls 'list.stream()' on the result list
Stream<ResultRow> findUserStreamEager(Object userId)

// lazily loads all rows in a stateful stream (e.g. the Stream needs to be closed after consumption)
Stream<ResultRow> findUserStreamLazy(Object userId)
----

In case your project declares a dependency to RxJava2 (`io.reactivex.rxjava2:rxjava2`), the following additional method will be generated:

[source, java]
----
// lazily loads all rows in a stateful flow which closes automatically
Flowable<ResultRow> findUserFlow(Object userId)
----

You can enable/disable the generation of each of these methods both in the plugin configuration and for each statement individually.

The *addUser.sql* file might contain a writing statement (e.g. `INSERT INTO ...`) which generates the following Java methods in the same `UserRepository`:

[source, java]
----
// writes a single new user
int addUser(Object userId, Object name)

// batches multiple writes of new users
int[] addUserBatch(Object[] userId, Object[] name)
----

The SQL statements in your .sql files are just plain SQL, e.g. `findUser.sql` could look like this

[source, sql]
----
SELECT *
FROM users
WHERE id = :userId
----

and `addUser.sql` like this:

[source, sql]
----
INSERT INTO users (id, name)
VALUES (:userId, :name)
----

In order to customize the generated code, e.g. change the type of the parameters, a YAML frontmatter can be specified for each SQL statement like this:

[source, sql]
----
-- parameters:
-- - name: userId
-- type: int
SELECT *
FROM users
WHERE id = :userId
----

This changes the generated code to this:

[source, java]
----
// uses 'int' instead of 'Object'
List<ResultRow> findUser(int userId)
Stream<ResultRow> findUserStreamEager(int userId)
Stream<ResultRow> findUserStreamLazy(int userId)
Flowable<ResultRow> findUserFlow(int userId)
----

Changing the result type is possible with the help of a converter:

[source, sql]
----
-- parameters:
-- - name: userId
-- type: int
-- resultConverter:
-- converterType: my.own.UserConverter
-- resultType: my.own.User
SELECT *
FROM users
WHERE id = :userId
----

Which in turn changes the generated code into this:

[source, java]
----
// uses 'User' instead of 'ResultRow' & 'int' instead of 'Object'
List<User> findUser(int userId)
Stream<User> findUserStreamEager(int userId)
Stream<User> findUserStreamLazy(int userId)
Flowable<User> findUserFlow(int userId)
----


`my.own.UserConverter` could look like this:

[source, java]
----
package my.own;

import java.sql.ResultSet;
import java.sql.SQLException;

import my.own.User;
import my.own.persistence.util.ResultState;

public class UserConverter {

public final User asUserType(final ResultState result) throws SQLException {
final ResultSet resultSet = result.getResultSet();
final User pojo = new User();
pojo.setId(resultSet.getInt("id"));
pojo.setName(resultSet.getString("name"));
return pojo;
}

}
----

Vendor specific statements are support as well. Just add a *vendor* key to your frontmatter like this:

[source, sql]
----
-- name: findUsers
-- vendor: Oracle
SELECT *
FROM (
SELECT ROWNUM rn, data.*
FROM (
SELECT *
FROM users
WHERE id = :userId
) data
WHERE rn <= :offset + :limit
)
WHERE rn >= :offset
;

-- name: findUsers
-- vendor: PostgreSQL
SELECT *
FROM users
WHERE id = :userId
OFFSET :offset
FETCH NEXT :limit ROWS ONLY
;

-- name: findUsers
SELECT *
FROM users
WHERE id = :userId
OFFSET :offset
LIMIT :limit
;
----

The first two statements specify a vendor which means that those queries will only be executed when running against the specified database. In case you want to specify a fallback-query that is used whenever no other vendor matches, specify another statement with the same name but no vendor.

Take a look in the *yosql-example* module that contains multiple example .sql files and a small application that uses the generated repositories. A complete list of configuration options follows below.

=== Configuration Options

The plugin offers several ways to influence how the generated code looks like. Run `yosql-cli help` to view the help and `yosql-cli help --command generate` to view detailed information of the available configuration options of the `generate` command. Its output looks like this:

[source]
----
Usage: yosql-cli [generate] [OPTIONS]

Generates Java code based on SQL files. [generate] can be omitted for brevity.

Possible [OPTIONS] are:
┌──────────────────────────────┬──────────────────────────────────────────────────────────────────────┬───────────────────────┐
│Name │Description │Defaults │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--basePackageName │The base package name for the generated code. │com.example.persistence│
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--classGeneratedAnnotation │Should @Generated annotations be added to generated classes? │true │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--converterPackageName │The package name suffix for the generated converters. Nested package│converter │
│ │names in the form 'my.subpackage.converter' are possible. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--defaulFlowStateClassName │The simple class name of the generated flow state class. Uses both│FlowState │
│ │'basePackageName' and 'utilityPackageName' to construct the fully│ │
│ │qualified name. Only generated if 'methodRxJavaApi' is true. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--defaultResultRowClassName │The class name of the result row class. Uses both 'basePackageName'│ResultRow │
│ │and 'utilityPackageName' to construct the fully qualified name. Only│ │
│ │generated if 'methodStandardApi' is true. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--defaultResultStateClassName │The class name of the result state class. Uses both 'basePackageName'│ResultState │
│ │and 'utilityPackageName' to construct the fully qualified name. Only│ │
│ │generated if 'methodStandardApi' is true. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--defaultRowConverter │The default row converter to use for each statement that returns a│resultRowConverter │
│ │value and does not specify a converter itself. Can either be the alias│ │
│ │of a converter or the fully qualified name of a converter taken from│ │
│ │'resultRowConverters'. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--fieldGeneratedAnnotation │Should @Generated annotations be added to generated fields? │false │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--generatedAnnotationComment │The comment to place inside generated @Generated annotations. │DO NOT EDIT │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--inputBaseDirectory │The input directory for .sql files │. │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--locale │The locale to use. │en │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--logLevel │The logging level to use while executing the YoSql CLI. Possible│INFO │
│ │values are [ALL, TRACE, DEBUG, INFO, WARN, ERROR, OFF]. Upper/lower│ │
│ │case does not matter. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--loggingApi │The logging API that should be used in the generated code. Possible│JDK │
│ │values are [NONE, JDK, LOG4J, SLF4J] which are defined in│ │
│ │'de.xn__ho_hia.yosql.model.LoggingAPI'. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--matchCatchAndRethrow │Whether generated methods should catch SqlExceptions and rethrow them│true │
│ │as RuntimeExceptions. If set to false, this will cause methods to│ │
│ │declare that they throw a checked exception which in turn will force│ │
│ │all its users to handle the exception themselves. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--maxThreads │The maximum number of threads to use. │0 │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodAllowedCallPrefixes │The allow method name prefixes for calling methods. Only used if│call, execute │
│ │'methodValidateNamePrefixes' is true. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodAllowedReadPrefixes │The allow method name prefixes for reading methods. Only used if│select, read, query,│
│ │'methodValidateNamePrefixes' is true. │find │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodAllowedWritePrefixes │The allow method name prefixes for writing methods. Only used if│update, insert, delete,│
│ │'methodValidateNamePrefixes' is true. │create, write, add,│
│ │ │remove, merge, drop │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodBatchApi │Controls whether the generated repositories should contain batch│true │
│ │methods for SQL INSERT/UPDATE/DELETE statements. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodBatchPrefix │The method name prefix to apply to all batch methods. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodBatchSuffix │The method name suffix to apply to all batch methods. │Batch │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodEagerName │The method name suffix to apply to all eager stream methods. │Eager │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodGeneratedAnnotation │Should @Generated annotations be added to generated methods? │false │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodLazyName │The method name suffix to apply to all lazy stream methods. │Lazy │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodRxJavaApi │Controls whether a RxJava 'io.reactivex.Flowable' based method should│true │
│ │be generated. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodRxJavaPrefix │The method name prefix to apply to all RxJava methods. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodRxJavaSuffix │The method name suffix to apply to all RxJava methods. │Flow │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodStandardApi │Controls whether the generated repositories should contain standard│true │
│ │methods that. Standard methods execute depending on the type of the│ │
│ │query and could either be a single 'executeQuery' on a│ │
│ │PreparedStatement in case of SQL SELECT statements or a single call to│ │
│ │'executeUpdate' for SQL UPDATE statements. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodStreamEagerApi │Controls whether an eager Java8 'java.util.stream.Stream' based method│true │
│ │should be generated. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodStreamLazyApi │Controls whether a lazy Java8 'java.util.stream.Stream' based method│true │
│ │should be generated. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodStreamPrefix │The method name prefix to apply to all stream methods. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodStreamSuffix │The method name suffix to apply to all stream methods. │Stream │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--methodValidateNamePrefixes │Controls whether method names are validated according to│true │
│ │'methodAllowedCallPrefixes', 'methodAllowedReadPrefixes' and│ │
│ │'methodAllowedWritePrefixes'. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--outputBaseDirectory │The output directory for all generated files │. │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--repositoryGenerateInterface │Should interfaces be generated for generated repositories? │true │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--repositoryNameSuffix │The repository name suffix to use for all generated repositories. │Repository │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--resultRowConverters │List of globally configured row converters for result sets. The│ │
│ │'ToResultRowConverter' is always added with the alias│ │
│ │'resultRowConverter' even when the converter is not used (FIXTHAT).│ │
│ │Configure new converters along the following pattern:│ │
│ │'ALIAS:FQN_OF_CONVERTER:FQN_OF_RESULT[,...]' │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--sqlFilesCharset │The character set to use while reading .sql files. │UTF-8 │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--sqlFilesSuffix │Only consider files as input that match this file name suffix. │.sql │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--sqlStatementSeparator │The SQL statement separator to use to split multiple SQL statement│; │
│ │inside a single .sql file. │ │
├──────────────────────────────┼──────────────────────────────────────────────────────────────────────┼───────────────────────┤
│--utilityPackageName │The package name suffix for the generated utilities. Nested package│util │
│ │names in the form 'my.subpackage.util' are possible. │ │
└──────────────────────────────┴──────────────────────────────────────────────────────────────────────┴───────────────────────┘
----


== Hacking

The `Makefile` in the root of this project defines common task for maintainers:

[source]
----
usage: make [target]

benchmark:
bench-lc-small-sample Run full codegen lifecycle against small sample (runtime is ~5min)
bench-lc-medium-sample Run full codegen lifecycle against medium sample (runtime is ~15min)
bench-lc-big-sample Run full codegen lifecycle against big sample (runtime is ~30min)
bench-lc-large-sample Run full codegen lifecycle against large sample (runtime is ~1h)
bench-parsing-each Run file parsing benchmark against each individual .sql file (runtime is ~30min)
bench-parsing-all Run file parsing benchmark against each individual .sql file (runtime is ~5min)

contributing:
sign-waiver Sign the WAIVER

example:
example-h2-all Run all examples against H2 database (works w/o docker)
example-psql-all Run all examples against Postgres (use docker-compose.yml for env)
example-mysql-all Run all examples against MySQL (use docker-compose.yml for env)

hacking:
build Build everything
test Test everything
clean Test everything
coverage Run code coverage

other:
help Show this help
----

== Alternatives

* https://github.com/OpenGamma/ElSql

== License

To the extent possible under law, the author(s) have dedicated all copyright
and related and neighboring rights to this software to the public domain
worldwide. This software is distributed without any warranty.

You should have received a copy of the CC0 Public Domain Dedication along
with this software. If not, see http://creativecommons.org/publicdomain/zero/1.0/.

== Mirrors

* https://github.com/sebhoss/yosql
* https://bitbucket.org/sebhoss/yosql
* https://gitlab.com/sebastian.hoss/yosql
* https://v2.pikacode.com/sebhoss/yosql
* http://repo.or.cz/yosql.git