JDBC MySQL Schema and Query builder

This library uses JDBC to help you create MySQL databases, migrations and seeders programmatically.

Installation

You can use this library via Maven and Jitpack. Add the following repository to your POM file.

<repositories>
    <repository>
        <id>jitpack.io</id>
        <url>https://jitpack.io</url>
    </repository>
</repositories>

Now add the following dependency.

<dependencies>
    <dependency>
        <groupId>com.github.MontealegreLuis</groupId>
        <artifactId>dbal-jdbc</artifactId>
        <version>master-SNAPSHOT</version>
    </dependency>
</dependencies>

That's it! Enjoy!

Usage

Database management

Database database = new Database(connection);
database.drop(name);
database.create(name);
database.use(name);

Migrations

Table movies = schema.table("movies").ifNotExists();
movies.increments("id");
movies.string("title", 300).makeRequired();
movies.integer("rating").defaultTo("0");
movies.string("thumbnail");

Table categories = schema.table("categories").ifNotExists();
categories.increments("id");
categories.string("name").makeRequired();

Table moviesCategories = schema.table("movies_categories").ifNotExists();
IntColumn movieId = (IntColumn) moviesCategories
    .integer("movie_id")
    .unsigned()
    .makeRequired()
;
IntColumn categoryId = (IntColumn) moviesCategories
    .integer("category_id")
    .unsigned()
    .makeRequired()
;
moviesCategories.foreign(movieId).references("id").on("movies");
moviesCategories.foreign(categoryId).references("id").on("categories");
moviesCategories.primary(movieId, categoryId);

Query builder

class MoviesTable extends Table<Movie> {
    class MoviesMapper implements RowMapper<Movie> {
        @Override
        public Movie mapRow(List<Object> values) {
            return new Movie(
                (long) values.get(0),
                values.get(1).toString(),
                (int) values.get(2),
                values.get(3)
            );
        }
    }
    
    MoviesTable(Connection connection) {
        super(connection);
    }

    Movie findBy(int movieId) {
        return this.select("*").where("id = ?").execute(movieId).fetch();
    }

    void update(String title, int rating, long id) {
        this
            .createUpdate("title", "rating")
            .where("id = ?")
            .execute(title, rating, id)
        ;
    }

    Movie insert(
        String title,
        int rating,
        String thumbnail,
        List<Category> categories
    ) {
        return this
            .createInsert("title", "rating", "thumbnail")
            .execute(title, rating, thumbnail)
            .fetch()
        ;
    }

    @Override
    protected String table() {
        return "movies";
    }

    @Override
    protected RowMapper<Movie> mapper() {
        return new MoviesMapper();
    }
}

Seeders

MoviesTable table = new MoviesTable(connection); 
table.insert(
    movie.title(),
    movie.rating(),
    movie.thumbnail(),
    movie.categories()
);

Tests

You can run the test suite using Maven. Create your configuration file for the integration tests using test.dist.prperties

$ cp src/test/java/resources/test.dist.properties src/test/java/resources/test.properties

Run the tests with Maven

$ mvn test