💥 SQL Query Builder

💡 Usage

• 👉 Running Database Queries
• 👉 Select Statements
• 👉 Raw Expressions
• 👉 Joins
• 👉 Basic Where Clauses
• 👉 Ordering, Grouping, Limit & Offset
• 👉 Conditional Clauses
• 👉 Insert Statements
• 👉 Update Statements
• 👉 Delete Statements

✨ Running Database Queries

Retrieving All Rows From A Table

importio.github.dldash.persistence.contracts.Query;
classUserController {
publicvoidindex() {
Queryquery = Query.builder()
.table("users")
.where("name", "John")
.build();
System.out.println(query.sql());
System.out.println(query.bindings());
}
}

Aggregates

Queryquery = Query.builder()
.table("users")
.count()
.build();

✨ Select Statements

Specifying A Select Clause

Queryquery = Query.builder()
.table("users")
.select("name", "email as user_email")
.build();

The distinct method allows you to force the query to return distinct results:

Queryquery = Query.builder()
.table("users")
.distinct()
.build();

✨ Raw Expressions

Query.raw("NOW()")

✨ Joins

Inner Join Clause

Queryquery = Query.builder()
.table("users")
.join("contacts", "users.id", "=", "contacts.user_id")
.join("orders", "users.id", "=", "orders.user_id'")
.select("users.*", "contacts.phone", "orders.price")
.build();

Left Join Clause

Queryquery = Query.builder()
.table("users")
.leftJoin("posts", "users.id", "=", "posts.user_id")
.build();

✨ Basic Where Clauses

Where Clauses

Queryquery = Query.builder()
.table("users")
.where("votes", 100)
.where("age", ">", 35)
.build();

Or Where Clauses

Queryquery = Query.builder()
.table("users")
.where("votes", ">", 100)
.orWhere("name", "John")
.build();

Queryquery = Query.builder()
.table("users")
.where("votes", ">", 100)
.orWhere(x -> x.where("name", "Abigail").where("votes", ">", 50))
.build();

The example above will produce the following SQL:

select*from users where votes >100or (name ='Abigail'and votes >50)

Additional Where Clauses

whereBetween / orWhereBetween

Queryquery = Query.builder()
.table("users")
.whereBetween("votes", 1, 100)
.build();

whereIn / whereNotIn / orWhereIn / orWhereNotIn

Queryquery = Query.builder()
.table("users")
.whereIn("id", Arrays.asList(1, 2, 3))
.build();

Queryquery = Query.builder()
.table("users")
.whereNotIn("id", Arrays.asList(1, 2, 3))
.build();

whereNull / whereNotNull / orWhereNull / orWhereNotNull

Queryquery = Query.builder()
.table("users")
.whereNull("updated_at")
.whereNotNull("created_at")
.build();

whereDate / whereMonth / whereDay / whereYear / whereTime

Queryquery = Query.builder()
.table("users")
.whereDate("created_at", "2016-12-31")
.whereMonth("created_at", "12")
.whereDay("created_at", "31")
.whereYear("created_at", "2016")
.whereTime("created_at", "11:20:45")
.build();

Logical Grouping

Queryquery = Query.builder()
.table("users")
.where("name", "=", "John")
.where(x -> x.where("votes", ">", 100).orWhere("title", "=", "Admin"))
.build();

The example above will produce the following SQL:

select*from users where name ='John'and (votes >100or title ='Admin')

✨ Ordering, Grouping, Limit & Offset

Ordering

Queryquery = Query.builder()
.table("users")
.orderBy("name", "desc")
.build();

Grouping

Queryquery = Query.builder()
.table("users")
.groupBy("account_id", "status")
.build();

Limit & Offset

Queryquery = Query.builder()
.table("users")
.skip(10)
.take(5)
.build();

Alternatively, you may use the limit and offset methods. These methods are functionally equivalent to the take and skip methods, respectively:

Queryquery = Query.builder()
.table("users")
.offset(10)
.limit(5)
.build();

Paginate

Queryquery = Query.builder()
.table("users")
.paginate(10, 1)
.build();

✨ Conditional Clauses

Sometimes you may want certain query clauses to apply to a query based on another condition. For instance, you may only want to apply a where statement if a given input value is present on the incoming HTTP request. You may accomplish this using the when method:

Stringrole = request.get("role");
Queryquery = Query.builder()
.table("users")
.when(role, (q, value) -> q.where("role_id", value))
.build();

Where if not null

Queryquery = Query.builder()
.table("users")
.whereIfPresent("A", 1)
.whereIfPresent("B", null)
.build();

✨ Insert Statements

importio.github.dldash.persistence.builders.InsertQuery;
Queryquery = InsertQuery.builder()
.table("users")
.insert("email", "kayla@example.com")
.insert("votes", 0)
.build();

The ignore() method will ignore duplicate record errors while inserting records into the database:

Queryquery = InsertQuery.builder()
.table("users")
.ignore()
.insert("id", 1)
.insert("email", "archer@example.com")
.build();

Upserts (on duplicate key update)

Queryquery = InsertQuery.builder()
.table("users")
.insert("email", "kayla@example.com")
.insertOrUpdate("votes", 10)
.insertOrUpdate("updated_at", Query.raw("NOW()"))
.build();

✨ Update Statements

importio.github.dldash.persistence.builders.UpdateQuery;
Queryquery = UpdateQuery.builder()
.table("users")
.update("votes", 1)
.where("id", 1)
.build();

Update if not null

Queryquery = UpdateQuery.builder()
.table("users")
.update("votes", 1)
.updateIfPresent("name", null)
.where("id", 1)
.build();

✨ Delete Statements

importio.github.dldash.persistence.builders.DeleteQuery;
Queryquery = DeleteQuery.builder()
.table("users")
.where("votes", ">", 100)
.build();