ORM and SQL Builder

Mentawai is also an ORM and query builder framework that can help you work with the database. If you know SQL you can easily write Data Access Objects (DAOs) for you web application with Mentawai.

NOTE: The ORM and SQL builder capabilities of Mentawai were extracted in a separate project called MentaBean, which remains seamlessly integrated with the framework.

The bean or POJO (Plain Old Java Object):

	public class User {

		public static enum Status {
			BASIC, PREMIUM, GOLD
		}

		private int id;
		private String username;
		private Date birthdate;
		private Status status = Status.BASIC;
		private boolean deleted;
		private Date insertTime;

		public User() {
		}

		public User(int id) {
			this.id = id;
		}

		// a bunch of getters and setters
	}

The mapping in the application manager (as always done programmatically with no XML or annotations):

// inside the application manager:

	@Override
	public void loadBeans() {

		bean(User.class, "Users") // table name is "Users"
			.pk("id", DBTypes.AUTOINCREMENT)
			.field("username", DBTypes.STRING)
			.field("birthdate", "bd", DBTypes.DATE) // note that the database column name is different
			.field("status", DBTypes.ENUMVALUE.from(User.Status.class))
			.field("deleted", DBTypes.BOOLEANINT)
			.field("insertTime", "insert_time", DBTypes.NOW_ON_INSERT_TIMESTAMP);

	}

The DAO (Data Access Object):

public class JdbcUserDAO implements UserDAO {

    private final BeanSession beanSession;

    public JdbcUserDAO(BeanSession beanSession) {
        this.beanSession = beanSession;
    }

    // data access will be implemented here
}

Choosing the SQL dialect and the UserDAO implementation through IoC:

// inside the application manager:

	@Override
	public void setupIoC() {
		
		ioc("beanSession", MySQLBeanSession.class);
		
		ioc("userDAO", JdbcUserDAO.class);
	}

NOTE: A BeanSession needs a database Connection, so don't forget to configure you connection handler as described in the DB Connection section.

Simple CRUD access with no SQL:

			// INSERT:

			User u = new User();
			u.setUsername("saoj");
			u.setBirthdate("1983-01-20");
			u.setStatus(User.Status.GOLD);

			session.insert(u);

			int id = u.getId(); // should return the new ID in the database

			// SELECT:

			u = new User(1); // user with id = 1
			boolean loaded = session.load(u); // should return true

			// UPDATE:

			u.setUsername("soliveira");
			int modified = session.update(u); // should return 1

			// DELETE:

			boolean deleted = session.delete(u); // should return true


			// make sure the bean is deleted from the database...
			u = new User(1);
			loaded = session.load(u); // should return false

Writing joins to load more than one bean at the same time:

Another bean:

	public class Post {

		private int id;
		private int userId;
		private User user; // has an User (one-to-one relationship)
		private String title;
		private String body;
		private Date insertTime;

		public Post() {
		}

		public Post(int id) {
			this.id = id;
		}

		public Post(int userId, String title, String text) {
			this.userId = userId;
			this.title = title;
			this.body = text;
		}
		
		// setters and getters here...
	}

Map the new bean in the application manager:

	@Override
	public void loadBeans() {

		bean(Post.class, "Posts") // table name is "Posts"
			.pk("id", DBTypes.AUTOINCREMENT)
			.field("userId", "user_id", DBTypes.INTEGER)
			.field("title", DBTypes.STRING)
			.field("body", DBTypes.STRING)
			.field("insertTime", "insert_time", DBTypes.NOW_ON_INSERT_TIMESTAMP);
	}

Now the JOIN query:

			User u = new User(); // Note: You can also write other constructors for the User object
			u.setUsername("saoj");
			u.setBirthdate("1983-01-20");
			session.insert(u);

			int userId = u.getId(); // the unique ID created in the database for this user

			// Now insert a post for this user...
			Post p = new Post(u.getId(), "Test", "This is a test!");
			session.insert(p);

			int postId = p.getId();

			// Use JOIN to load all dependencies with a single query... (you know how to make a join, right?)

			p = new Post();

			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, postId);

			rset = stmt.executeQuery();

			if (rset.next()) {

				session.populateBean(rset, p, "p");

				u = new User();

				session.populateBean(rset, u, "u");

				p.setUser(u); // manual lazy loading (because you want control and no magic!)
			}

			rset.close();
			stmt.close();

Much more:

MentaBean offers many neat features that you can check in the recipes section of its web site. For example, you can load a list of beans, use reflection to map a bean automatically, take advantage of the three different kinds of updates, and much more.