Saturday, December 7, 2013

    Mentabean - Simple ORM and Query Buider:

    http://mentabean.soliveirajr.com/mtw/Page/Intro/en/mentabean-simple-orm-and-query-builder

    MentaBean is a straightforward ORM (Object-Relational Mapping) framework for those who want to work with SQL without the JDBC boilerplate. Differently than other ORMs it keeps the magic to a minimum so you always know what is going on under the hood.
    With MentaBean you are in control not the framework!
    MentaBean automates CRUD operations without any SQL, but it will not make you learn a new language when you need to query. Instead it helps you build your queries using SQL without the boring parts.
    With MentaBean you do not have to learn another query language on top of SQL!
    It also does the object-to-database mappings programmatically through a fluent API. With MentaBean you don't have to worry about XML programming or Annotation hell because the mappings are done with Java code.
    With MentaBean you do not have to do the mappings using XML or Annotations "logic"!
    So if you like and understand SQL and are tired of the huge learning curve and unpredictable behavior offered by other complex ORM frameworks you should try MentaBean for a simplicity break.

Quick Start

    Notice how clean your bean is. There is no framework dependency and no annotations polluting your entity:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    public class User {
     
        public static enum Status {
            BASIC, PREMIUM, GOLD
        }
     
        private long id;
        private String username;
        private Date birthdate;
        private Status status;
        private boolean deleted;
        private Date insertTime;
     
        public User() {
            // mandatory
        }
     
        public User(long id) {
            this.id = id;
        }
     
        public User(String username, Date birthdate, Status status) {
            this.username = username;
            this.birthdate = birthdate;
            this.status = status;
        }
     
        // getters and setters
    }

    The database table:
    create table Users(
            id integer primary key auto_increment,
            username varchar(25),
            bd datetime,
            status varchar(20),
            deleted tinyint,
            insert_time timestamp
    );

    Now the mapping done with Java code through a fluent API. Again no annotation black magic or XML hell:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    BeanManager mgr = new BeanManager();
     
    BeanConfig userCfg = new BeanConfig(User.class, "Users") // "Users" is the table name
        .pk("id", DBTypes.AUTOINCREMENT) // the primary key!
        .field("username", DBTypes.STRING)
        .field("birthdate", "bd", DBTypes.DATE) // note that the database column name is different
        .field("status", DBTypes.ENUMVALUE.from(Status.class)) // status will be saved as a varchar in the database
        .field("deleted", DBTypes.BOOLEANINT) // 0 for false and 1 for true in the database
        .field("insertTime", "insert_time", DBTypes.NOW_ON_INSERT_TIMESTAMP); // again database column name is different than property name
     
    mgr.addBeanConfig(userCfg);
    And you can also map using a properties proxy to support refactoring:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    BeanManager mgr = new BeanManager();
     
    User userProps = PropertiesProxy.create(User.class);
     
    BeanConfig userCfg = new BeanConfig(User.class, "Users")
        .pk(userProps.getId(), DBTypes.AUTOINCREMENT)
        .field(userProps.getUsername(), DBTypes.STRING)
        .field(userProps.getBirthdate(), "bd", DBTypes.DATE) // note that the database column name is different
        .field(userProps.getStatus(), DBTypes.ENUMVALUE.from(Status.class))
        .field(userProps.isDeleted(), DBTypes.BOOLEANINT)
        .field(userProps.getInsertTime(), "insert_time", DBTypes.NOW_ON_INSERT_TIMESTAMP);
     
    mgr.addBeanConfig(userCfg);

    Simple CRUD operations without SQL:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    Connection conn = getConnection();
    BeanSession session = new MySQLBeanSession(mgr, conn);
     
    // INSERT:
     
    User u = new User("saoj", toDate(1983, 1, 20), Status.BASIC);
    session.insert(u);
     
    System.out.println("The new ID created for the user: " + u.getId());
     
    // SELECT:
     
    User loadedUser = new User(u.getId());
    if (session.load(loadedUser)) {
        System.out.println("User was loaded: " + loadedUser.getUsername());
    } else {
        System.err.println("User was not found in the database: " + loadedUser.getId());
    }
     
    // UPDATE:
     
    loadedUser.setUsername("soliveira");
    session.update(loadedUser); // only the username field is updated with the new value...
     
    // DELETE:
     
    session.delete(loadedUser); // bye

    Building any query with SQL without the boring JDBC boilerplate:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    Connection conn = getConnection();
    BeanSession session = new MySQLBeanSession(mgr, conn);
     
    PreparedStatement stmt = null;
    ResultSet rset = null;
     
    try {
         
        StringBuilder sql = new StringBuilder(1024);
        sql.append("select ");
        sql.append(session.buildSelect(User.class));
        sql.append(" from Users");
        sql.append(" where status = ? and deleted = ?"); // the interesting part...
         
        stmt = SQLUtils.prepare(conn, sql.toString(), Status.GOLD.toString(), 1); // varargs for params
     
        rset = stmt.executeQuery();
         
        List users = new LinkedList();
         
        while(rset.next()) {
            User u = new User();
            session.populateBean(rset, u);
            users.add(u);
        }
     
        System.out.println("Number of users loaded: " + users.size());
         
    } finally {
        SQLUtils.close(rset, stmt, conn); // you can also statically import SQLUtils.*
    }
    NOTE: Simple queries like the one above can also be done without SQL using the session.loadList(Object bean) method. Refer toLoading Lists for more details.

    You can also use a proxy to build a fully refactorable query:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    Connection conn = getConnection();
    BeanSession session = new MySQLBeanSession(mgr, conn);
      
    PreparedStatement stmt = null;
    ResultSet rset = null;
      
    try {
         
        TableAlias userAlias = session.createTableAlias(User.class);
        User user = userAlias.pxy();
          
        SQLBuilder sql = new SQLBuilder(1024, userAlias);
        sql.append("select ");
        sql.append(userAlias.columns());
        sql.append(" from ").append(userAlias.tableName());
        sql.append(" where ").column(user.getStatus()).append(" = ? and ").column(user.isDeleted()).append(" = ?"); // the interesting part...
          
        stmt = SQLUtils.prepare(conn, sql.toString(), Status.GOLD.toString(), 1); // varargs for params
      
        rset = stmt.executeQuery();
          
        List users = new LinkedList();
          
        while(rset.next()) {
            User u = new User();
            session.populateBean(rset, u);
            users.add(u);
        }
      
        System.out.println("Number of users loaded: " + users.size());
          
    } finally {
        SQLUtils.close(rset, stmt, conn); // you can also statically import SQLUtils.*
    }

No comments: