import static com.feedzai.commons.sql.abstraction.engine.configuration.PdbProperties.*;

(...)

Properties properties = new Properties() {
	{
		setProperty(JDBC, "<JDBC-CONNECTION-STRING>");
		setProperty(USERNAME, "username");
		setProperty(PASSWORD, "password");
		setProperty(ENGINE, "<PDB-ENGINE>");
		setProperty(SCHEMA_POLICY, "create");
	}
};

DatabaseEngine engine = DatabaseFactory.getConnection(properties);

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

DbEntity data_type_table =
	dbEntity()
		.name("data_type")
		.addColumn("id", INT, UNIQUE, NOT_NULL)
		.addColumn("code", STRING, UNIQUE, NOT_NULL)
		.addColumn("description", CLOB)
		.pkFields("id")
		.build();

engine.addEntity(data_type_table);

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

DbEntity provider_table =
	dbEntity()
		.name("provider")
			.addColumn("id", INT, true, UNIQUE, NOT_NULL)
			.addColumn("uri", STRING, UNIQUE, NOT_NULL)
			.addColumn("certified", BOOLEAN, NOT_NULL)
			.addColumn("description", CLOB)
			.pkFields("id")
			.build();

engine.addEntity(provider_table);

DbEntity stream_table =
	dbEntity()
	.name("stream")
		.addColumn("id", INT, true, UNIQUE, NOT_NULL)
		.addColumn("provider_id", INT, NOT_NULL)
		.addColumn("data_type_id", INT, NOT_NULL)
		.addColumn("description", CLOB)
		.pkFields("id")
		.addFk(
			dbFk()
				.addColumn("provider_id")
				.foreignTable("provider")
				.addForeignColumn("id"),
			dbFk()
				.addColumn("data_type_id")
				.foreignTable("data_type")
				.addForeignColumn("id"))
		.addIndex(false, "provider_id", "data_type_id")
		.build();

engine.addEntity(stream_table);

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

DbEntity module_table =
	dbEntity()
		.name("module")
        .addColumn("name", STRING, UNIQUE, NOT_NULL)
        .build();

engine.addEntity(module_table);

DbEntity stream_to_module_table =
	dbEntity()
		.name("stream_to_module")
			.addColumn("name", STRING, NOT_NULL)
			.addColumn("stream_id", INT, NOT_NULL)
			.addColumn("active", INT)
			.pkFields("name", "stream_id")
			.addFk(
				dbFk()
					.addColumn("name")
					.foreignTable("module")
					.addForeignColumn("name"),
				dbFk()
					.addColumn("stream_id")
					.foreignTable("stream")
					.addForeignColumn("id"))
            .build();

engine.addEntity(stream_to_module_table);

engine.dropEntity("stream_to_module");

data_type_table = data_type_table
                    .newBuilder()
                    .removeColumn("description")
                    .build();

engine.updateEntity(data_type_table);

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

Expression alterColumn = alterColumn(
                            table("stream_to_module"),
					        dbColumn("active", BOOLEAN).addConstraint(NOT_NULL).build());

engine.executeUpdate(alterColumn);

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

Expression dropPrimaryKey = dropPK(table("TEST"));

engine.executeUpdate(dropPrimaryKey);

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

EntityEntry data_type_entry =
	entry()
		.set("id", 1)
		.set("code", "INT16")
		.set("description", "The type of INT you always want!")
		.build();

engine.persist("data_type", data_type_entry, false);

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

EntityEntry provider_entry =
	entry()
		.set("uri", "from.some.where")
		.set("certified", true)
		.build();

long generatedKey = engine.persist("provider", provider_entry, true);

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

engine.beginTransaction();

try {
    EntityEntry entry = entry()
    	.set("code", "SINT")
    	.set("description", "A special kind of INT")
    	.build();

    engine.addBatch("data_type", entry);

    entry = entry()
    	.set("code", "VARBOOLEAN")
    	.set("description", "A boolean with a variable number of truth values")
    	.build();

    engine.addBatch("data_type", entry);

    // Perform more additions...

    engine.flush();
    engine.commit();
} finally {
    if (engine.isTransactionActive()) {
        engine.rollback();
    }
}

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

engine.executeUpdate(
	update(table("stream"))
	.set(
		eq(column("cd"), k("Double")),
		eq(column("description", k("Double precision floating point number")))
	.where(eq(column(id), k(1))));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

engine.executeUpdate(
	delete(table("stream")));

engine.executeUpdate(
	delete(table("stream"
		.where(eq(column(id), k(1))));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

engine.executeUpdate(truncate(table("stream")));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

Expression query =
	select(all())
	.from(table("streams"));

// Fetches everything! Use with care when you know the result set is small.
List<Map<String, ResultColumn>> results = engine.query(query);

for(Map<String, ResultColumn> result : results) {
    Int id = result.get("id").toInt();
    String description = result.get("description").toString();
	System.out.println(id + ": "+ description);
}

// If your result set is large consider using the iterator.
ResultIterator it = engine.iterator(select(all()).from(table("streams")));
Map<String, ResultColumn> next;
while ((next = it.next()) != null) {
    Int id = next.get("id").toInt();
    String description = next.get("description").toString();
	System.out.println(id + ": "+ description);
}

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

results = engine.query(
	select(all())
	.from(table("streams")));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

results = engine.query(
	select(all())
	.from(table("streams"))
	.where(eq(column("data_type_id"), k(4)))
	.andWhere(like(column("description"), k("match t%xt"))));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

results = engine.query(
	select(all())
	.from(table("streams"))
	.where(
		and(between(column("data_type_id"), k(2), k(5)),
			notIn(column("provider_id"), L(k(1), k(7), k(42))))));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

results = engine.query(
	select(max(column("id")).alias("the_best"))
	.from(table("streams"))
	.where(
		and(eq(column("data_type_id"), k(4)),
			eq(column("provider_id"), k(1)))));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

results = engine.query(
	select(all())
	from(table("stream")
		.innerJoin((table("data_type"),
					join(
					    column("stream", "data_type_id"),
					    column("data_type", "id")))));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

results = engine.query(
	select(column("data_type_id"), count(column("id")).alias("count"))
	.from(table("streams"))
	.where(eq(column("provider_id"), k(4)))
	.groupby(column("data_type_id"))
	.orderby(column("data_type_id").asc());

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

results = engine.query(
	select(
		plus(
			column("data_type_id"),
			column("provider_id"),
			k(1)
		).alias("mess_ids"),
		coalesce(
			column("description"),
			k("Romeo must die")))
	.from(table("streams")));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

results = engine.query(
	select(all())
	.from(table("streams"))
	.having(
		gt(plus(
				column("data_type_id"),
				column("provider_id")),
			k(5)))
	.limit(10)
	.offset(2));

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

Query query = select(all())
		.from(table("streams"))
		.where(eq(column("data_type_id"), lit("?")))
		.andWhere(like(column("description"), k("match t%xt")));

engine.createPreparedStatement("MyPS", query);

// It is always a good policy to clear the parameters
engine.clearParameters("MyPS");
engine.setParameter("MyPS", 1, 10);

engine.executePS("MyPS");
List<Map<String, ResultColumn>> result = engine.getPSResultSet("MyPS");

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

Update update = update(table("stream"))
			.set(
				eq(column("cd"), lit("?")),
				eq(column("description", lit("?")))
			.where(eq(column(id), k(1))));

engine.createPreparedStatement("MyPS", query);

engine.clearParameters("MyPS");
engine.setParameter("MyPS", 1, "INT");
engine.setParameter("MyPS", 2, "Your regular integer implementation.");

int affectedEntries = engine.executePSUpdate("MyPS");

import static com.feedzai.commons.sql.abstraction.dml.dialect.SqlBuilder.*;

(...)

Expression view = createView("simple_stream")
					.as(select(column("id"), column("data_type_id"))
						.from(table("stream")))
					.replace();

engine.executeUpdate(view);