Good Transfer Object Hierarhy

One approach worked well for me. Allow me to show by example.

class UserTO {
 private Long id;
 ...

 public Long getId() {
  return id;
 }

 public void setId(Long id) {
  this.id = id;
 }
 ...
}

class UserHistoryTO {
 private Long id;
 ...

 public Long getId() {
  return id;
 }

 public void setId(Long id) {
  this.id = id;
 }
 ...
}

class UserDetailsTO extends UserTO {
 private List<UserHistoryTO> userHistory;
 ...

 public List<UserHistoryTO> getUserHistory() {
  return userHistory;
 }

 public void setUserHistory(List<UserHistoryTO> userHistory) {
  this.userHistory = userHistory;
 }
 ...
}

Do you see the point? Ok, whatever.

I'll go with MyBatis

You've worked hard and developed a bunch of persistence capable entities, data objects to hold projections, a good amount of JPQL/HQL NamedQueries, a few native SQL queries, optimistic locking is still perfect, you've put OpenEntityManagerInViewFilter because of some lazy things do not adhere to transaction demarcation and boundaries, and it works, it works just fine. Then you push it to production wait a month or so open The Slow Query Log, !#@! you think.

I'd advise against JPA/Hibernate especially when you do something highly scalable and highly available (see Sean Hull's 20 Biggest Bottlenecks That Reduce And Slow Down Scalability, Rule Number 9), especially when CQRS and Event Sourcing are a big deal. Ok, one may believe that doing programming with relational database without knowing how to write SQL and what execute plan will be used to run it is a good idea in case you protect yourself with Hibernate. But despite Hibernate abstracts RDB it doesn't remove complexity, take a look at Hibernate ORM documentation. And after all abstractions are leaky and you'll end up debugging SQL and writing native queries.

But you probably do not want to program in JDBC anymore. To make it convenient to work with DB and let DB do its stuff I'd use MyBatis that just removes boilerplate code and doesn't introduce some piece of magic. And yes, it is simple, see MyBatis3 Introduction.

To make you feel what it is like a really minimalistic example below. Mapper xml file src/main/resources/test/persistence/TestMapper.xml

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="test.persistence.TestMapper">

 <select id="selectOne" parameterType="long" resultType="test">
  SELECT
  t.id, t.name
  FROM test t
  WHERE t.id = #{id}
 </select>
</mapper>

Mapper interface src/main/java/test/persistence/TestMapper.java

package test.persistence;

import test.model.Test;

public interface TestMapper {
 Test selectOne(Long id);
}

Transfer object src/main/java/test/model/Test.java

package test.model;

import java.io.Serializable;

public class Test implements Serializable {

 private Long id;

 private String name;

 public Test() {
  super();
 }

 public Long getId() {
  return id;
 }

 public void setId(Long id) {
  this.id = id;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

}

And a piece of code that make use of it

try (SqlSession session = sqlSessionFactory.openSession(TransactionIsolationLevel.REPEATABLE_READ)) {
 TestMapper mapper = session.getMapper(TestMapper.class);
 Test res = mapper.selectOne(1L);
 session.commit();
}

So as you may guess the mapper interface is implemented by MyBatis with the help of provided mapper xml file. See MyBatis3 Getting started.

Simplistic ORM like thing in C for MySQL

Take a look at extern_mysql.h and extern_mysql.c. It is a simple request to struct mapping done in C for MySQL 5.0 and above for one of the projects. libmysql_r is not the simplest thing to work with, code provided was tested in production, so it works, take a look if you had a bad day and were forced to get data from MySQL in C.

A few words about an interface. To get data from mysql:
1) add request to mb_mysql_stmt:

mb_mysql_stmt_t mb_mysql_stmt[] = {
    ...
    {
        "select * from SCHEDULE",
        NULL, NULL, sizeof(mb_db_schedule_t), bind_r_schedule
    },
    ...
};

where bind_r_schedule is a binding definition:

mb_bind_t   bind_r_schedule[] = {
    {"schedule_id",     offsetof(mb_db_schedule_t, schedule_id),        sizeof(((mb_db_schedule_t*)0)->schedule_id),    0},
    {"name",            offsetof(mb_db_schedule_t, name),               sizeof(((mb_db_schedule_t*)0)->name),           0},
    {"schedule_type",   offsetof(mb_db_schedule_t, schedule_type),      sizeof(((mb_db_schedule_t*)0)->schedule_type),  0},
    {"enabled",         offsetof(mb_db_schedule_t, enabled),            sizeof(((mb_db_schedule_t*)0)->enabled),        0},
    {"last_backup",     offsetof(mb_db_schedule_t, last_backup),        sizeof(((mb_db_schedule_t*)0)->last_backup),    0},
    {NULL, 0, 0, 0}
};

This means that mb_db_exec() will return an array of mb_db_schedule_t, each element of this array will correspond to one row in the result set. To map fields in result set to struct members bindings are used, consult C API Prepared Statement Type Codes to map mysql types to C ones (for MYSQL_TIME mb_time_t is defined). The length of the array will be stored in variable pointed by the count argument of the mb_db_exec() function.
If you want to get single value from db you can use, for instance

mb_bind_t   bind_r_config_value[] = {
    {"value",   0,  256, 0},
    {NULL, 0, 0, 0}
};
mb_mysql_stmt_t mb_mysql_stmt[] = {
    ...
    {
        "select value from CONFIG where `key`='address'",
        NULL, NULL, 256, bind_r_config_value
    },
    ...
};

We do not use struct and map the whole row to buffer of 256 bytes (256 is the max length of the value field in config table), to obtain results call

char *data;
data = mb_db_exec(mb_db_ip_address_stmt, NULL);
if (!data)
    mb_log_err("cannot find ip address");

Note that we do not need count here. Also note that bindings can be reused (bind_r_config_value can be used to obtain various values from config table).
2) add request number to mb_db_stmt_e enum:

typedef enum {
    ...
    mb_db_ip_address_stmt
} mb_db_stmt_e;