Simple SQL

EzQu primarily deals with mapping, which means that you can simply write your query and it takes care of the mapping for you. To illustrate, here's a simple example:

List<Person> people = sessionFactory.getFromSession(select -> {
    String query = "select p.* from person p left outer join address a where a.personId = p.id and a.city=? and a.street=?";
    return select.executeQuery(query, Person.class, "New York", "2nd Ave.");
});

In this example, Person.class is a plain old Java object (POJO), where the fields are named the same as the columns in the database. However, you can also use different names by annotating the fields accordingly.

Here is an example with columns defined using annotations from finer grain control (Still a pojo though):

public class Person {

   @Column(name="FIRST_NAME")
   public String firstName;

   @Column(name="LAST_NAME")
   public String lastName;
}

List<Person> people = sessionFactory.getFromSession(select -> {
    String query = "select p.* from person p left outer join address a where a.personId = p.id and a.city=? and a.street=?";
    return select.executeQuery(query, Person.class, "New York", "2nd Ave.");
});

Here is a join result

public class PersonAndAddress {
    public String firstName;
    public String lastName;
    public String city;
    public String street;
}

List<PersonAndAddress> people = sessionFactory.getFromSession(select -> {
    String query = "select p.firstName as firstName, p.lastName as lastName, a.city as city, a.street as street "
                        + " from Person p left outer join Address a where a.personId = p.id";
    return select.executeQuery(query, PersonAndAddress.class);
});

These are just three examples. You can do updates, you can use callable statements, batches, you can select by example by giving an object as an example and more.

Fluent API

EzQu also uses a type safe fluent api for doing queries. By using the type safe api EzQu provides full protection against SQL injection, ensuring the security of your database operations. You can create almsot any query using this api, all you need to know is SQL... Here are some examples.

List<String> lastNames = sessionFactory.getFromSession(select -> {
    Person p = new Person();
    List<Person> people = select.from(p).where(p.firstName).is("david").select();
    // or like this
    List<Person> people = select.from(p).where(p.getFirstName()).is("david").select();
    // or do stuff likt the following
    Person david = select.from(p).where(p.getIdentification()).is("1234567").selectFirst();
    String lastName = select.from(p).where(p.getIdentification()).is("1234567").selectFirst(p.getLastName());
    return select.from(p).select(p.getLastName());
});

With more complicated entities that also map the relationships you can do:

List<Person> people = sessionFactory.getFromSession(select -> {
    Person p = new Person();
    Address a = new Address();
    return select.from(p).innerJoin(a).on(a.getPerson()).is(p).where(a.city).is("New York").select();
});

List<Address> addresses = sessionFactory.getFromSession(select -> {
    Person p = new Person();
    Address a = new Address();
    return select.from(p).innerJoin(a).on(a.getPerson()).is(p).where(p.getLastName()).is("Pitt").selectRightHandJoin();
});

You can also leverage a powerful feature of EzQu that allows you to perform combination joins and return a result object. This can be useful for more complex queries that involve multiple tables or conditions.

List<PersonWithAddress> people = sessionFactory.getFromSession(select -> {
    Person p = new Person();
    Address a = new Address();
    return select.from(p).where(p.firstName).innerJoin(a).on(a.getPerson())
                             .is(p).where(a.city).is("New York").select(new PersonWithAddress() {
                               {
                                  firstName = p.getFirstName();
                                  lastName = p.getLastName();
                                  street = a.getStreet();
                                  city = a.getCity();
                                }
   }); 
});

You can also use this api for updates and deletes. Here is a taste:

sessionFactory.runInSession(select -> {
    Person p = new Person();
    int updated = select.from(p).set(p.lastName, "Pitt").where(p.firstName).is("Brad").update();
    int deleted = select.from(p).where(p.firstName).is("Brad").and(p.getLastName()).is("Pitt").delete();
    // do more stuff
})'

'CRUD' Operations

EzQu provides an API for performing CRUD (Create, Read, Update, Delete) operations using object notation. These operations can be performed using either the Fluent API or by using direct API commands for insert, update, and delete. When using Annotations, these operations also understand object relationships. To perform these operations, post-compilation is required on Entity Objects. There are three ways to accomplish this: using Ant, using the Eclipse plugin provided, or using the Gradle plugin provided. At runtime, only the EzQu.jar file is needed.

Here are some examples:

@Entity
@Table(name = "people")
public class Person {
    @PrimaryKey
    private Long id;

    @Column(name= "first_name")
    private String firstName;

    @Column(name="last_name")
    private String lastName;

    @One2Many(relationFieldName="person_id")
    private List<Address> adddresses; // can also be a Set

    public Person() {} // must have an empty constructor

    public Person(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }
    .....
    getters & setters
}

When working with CRUD operations in EzQu, the primary key must be a single field and can be of any type supported by the database. Every field of the object will be treated as a column, unless it is marked as 'static', 'transient', or annotated with @Transient. The @Column annotation is optional unless you need to use a different column name than the field name, or supply other parameters such as size.

In order to use CRUD operations with EzQu, the @Entity annotation is required. The @Table annotation is only necessary when the table name differs from the class name.

Depending on how you set up your Session Factory, EzQu can also create the tables for you upon first access to the table class.

@Entity
public class Address {
    @PrimaryKey
    private Long id;

    private String street;

    private String city;

    public Address() {} // must have an empty constructor

    public Address(String street, String city) {
        this.street = street;
        this.city = city;
    }
    .....
    getters & setters
}

// work with crud
sessionFactory.runInSession(db -> {
    Person p = new Person("Jhon", "Smith");
    Address a = new Address("2nd Ave.", "New York");

    List<Address> addresses = new ArrayList<>();
    addresses.add(a);

    p.setAddresses(addresses);

    // you don't need to insert the address ezqu does this for you
    // since the primary key is defined as auto increament the id will be generated by the db.
    db.insert(p);

    // after insert both p and a will have a value in their id field corresponding to the value of the id in the db.
    p.setLastName("Doe");
    a.setStreet("Park Avenue");
    // the following update will update both Person and Address because the Address a is part of Person p
    db.update(p);

    // dependent on how you setup the relationship the next part will delete person and if marked as "cascade" 
    // will delete address to.
    db.delete(p);
});

// the next part is based on the example before (without the delete)
Person p = sessionFactory.getFromSession(db -> {
    Person pDesc = new Person();
    // the next line will retrieve the Person with id of 1 and all of the addresses related to it
    return db.from(pDesc).primaryKey().is(1L).selectFirst();
});

More...

You can look at more code examples in the EzQU test project:

https://github.com/sbentin/EzquTest