Table Joins

Instead of trying to automate every possible query, an approach that can only lead to complexity and too much magic, MentaBean helps you build your SQL queries so you can stay in control. It is JDBC + SQL without the boring parts. Check the example below:

Connection conn = getConnection();
PreparedStatement stmt = null;
ResultSet rset = null;

try {

	Post post = new Post(1);
	
	StringBuilder query = new StringBuilder(256);
	query.append("select ");
	query.append(session.buildSelect(Post.class, "p"));
	query.append(", ");
	query.append(session.buildSelect(User.class, "u"));
	query.append(" from Posts p join Users u on p.user_id = u.id");
	query.append(" where p.id = ?");
	
	stmt = conn.prepareStatement(query.toString());
	stmt.setInt(1, post.getId());

	// the two lines above could have been:
	// stmt = SQLUtils.prepare(conn, query.toString(), post.getId());
	
	rset = stmt.executeQuery();
	
	if (rset.next()) {
	
		session.populateBean(rset, post, "p");

		User user = new User();
	
		session.populateBean(rset, user, "u");
	
		post.setUser(user);
	}

} catch(SQLException e) {
	throw new BeanException(e);
} finally {
	SQLUtils.close(rset, stmt, conn);
}




Note that because we are using two different beans (User and Post) in the same query, we must define an alias for each table. For the Posts table we choose "p" and for the Users table we choose "u". If we were using just one bean in the query an alias would not be necessary. That's just plain, old and good SQL.

NOTE: You can also use proxies to build your query strings so that it becomes fully refactorable. Refer to the section Queries with Proxies for an example.