Development patterns in EoD SQL 2.0

EoD SQL has always been focused on flexability, and EoD SQL 2.0 is taking that concept even further.

To introduce this topic, I have some important points (some of which may already be known):

  • EoD SQL understands Connection pooling
    • Creating a Query instance with a DataSource is completely different to creating one with a Connection
  • EoD SQL allows you to declare the type you want to return
  • EoD SQL does not mangle your SQL, ever!
    • You tell your database exactly what you want to

Here is a little example of some code that uses some of EoD SQL’s more advanced tricks:

public class User {
  private static final UserQuery QUERY = QueryTool.getQuery(UserQuery.class);

  @AutoGeneratedKeys
  private Long id = null;

  private String email;

  private String password;

  @ResultColumn( "display_name" )
  private String displayName;

  private User() {}

  public User(String email, String password, String displayName) {
    this.email = email;
    this.password = password;
    this.displayName = displayName;
  }

  public Long getId() {
    return id;
  }

  public String getEmailAddress() {
    return email;
  }

  public void setEmailAddress(String emailAddress) {
    this.email = emailAddress;
  }

  public String getPassword() {
    return password;
  }

  public void setPassword(String password) {
    this.password = password;
  }

  public String getDisplayName() {
    return displayName;
  }

  public void setDisplayName(String displayName) {
    this.displayName = displayName;
  }

  public synchronized void save() throws SQLException {
    if(id == null) {
      id = QUERY.insert(this);
    } else {
      QUERY.update(this);
    }
  }

  public static User getUser(long id) throws SQLException {
    return QUERY.selectById(id);
  }

  public static User getUser(String email) throws SQLException {
    return QUERY.selectByEmail(email);
  }
}

The things to note in the above code:

And then the Query declaration:

public interface UserQuery extends BaseQuery {
  @Select( "SELECT * FROM users WHERE id = ?1 LIMIT 1" )
  public User selectById(long id) throws SQLException;

  @Select( "SELECT * FROM users WHERE email = ?1 LIMIT 1" )
  public User selectByEmail(String email) throws SQLException;

  @Update( sql="INSERT INTO users (email, password, display_name) " +
    "VALUES (?{1.email}, ?{1.password}, ?{1.displayName})",
    keys=GeneratedKeys.RETURNED_KEYS_FIRST_COLUMN )
  public long insert(User user) throws SQLException;

  @Update( "UPDATE users SET email = ?{1.email}, password = ?{1.password}," +
    "display_name = ?{1.displayName} WHERE id = ?{1.id}" )
  public void update(User user) throws SQLException;
}

Things to note in the above code:

  1. The UserQuery instance QUERY in the User class is declared “static final”
    1. Because it is created with a DataSource, EoD SQL will automatically pull a Connection from the pool each time you run a query
    2. You need never close one of these Query objects, since the Connection is returned to the pool when you are no longer using it automatically
  2. Returning Database-Generated-Keys has become much easier in EoD SQL 2.0 as you can see from the “insert” method of the Query interface
  3. You are not forced to return a DataSet<User> in EoD SQL, you can return any type that EoD SQL understands (and it knows about quite a few)
    1. All the Java primitives and their wrapper classes
    2. Arrays
    3. UUID
    4. Any class you write that could be returned in a DataSet
    5. Many of the Collections types
      1. Collection
      2. List
      3. Set
      4. SortedSet
Advertisements

4 Responses to “Development patterns in EoD SQL 2.0”

  1. Stef Says:

    Hi, working with your API and it’s pretty cool. Too bad the EOD concept made it into the java 6 release.
    However it doesn’t seem to work when you run a query over 2 or more tables and do a “SELECT AS” statement. The representing getter will return null.eg.
    SELECT e.name AS employeeName,c.name AS companyName FROM employees AS e, companies AS c WHERE (e.company_id = c.id)

    Will you include stuff like that in the 2.0 release?

  2. Stef Says:

    Sorry, typo. The EOD concept DIDN’T make it into the 6 release

  3. Jason Says:

    Hi Steff,

    You’re problem is very interesting to me, since many of my queries include joins (and have done since alpha releases).

    Remember that EOD doesn’t call setter methods unless you annotate them with the @ResultColumn() you want (or have a public field with the same name or annotation). EOD SQL 2.0 is able to set the values of non-public fields.

    So just make sure that the setters are annotated, or the fields are public (and that all the names match), EOD SQL will not complain if you’re missing bindings, since it’s a loose-binding framework.

    Hope that helps.

  4. Performance improvements to EoD SQL 2.0 « Techno Lemming Says:

    […] 2.0, I thought I should take another look at the performance of the new code. Although the API does run a lot faster once you have a Query object in memory, constructing the Query in the first place is not as fast as […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: