Store with Db
DEPRECATED! This page has not been updated for a long time. Still, it may contain some valid and valuable information.
After setting the presentation layer and the service layer, we are
ready to dive into the database. The first thing is connecting to one.
As expected, we will initialize database connection in AppCore. But
that is not all! We need to set up the DbOom framework in such way to
minimize all work with the framework as much as possible. And at the
end, we will present a micro database layer that emphasizes simplicity
of working with database. Let's start.
Database initialization
Method initDb() from AppCore was already introduced before and here
is how it may look like:
void initDb() { petite.registerPetiteBean( CoreConnectionPool.class, "dbpool", null, null, false); ConnectionProvider cp = (ConnectionProvider) petite.getBean("dbpool"); cp.init(); // global settings DbManager dbManager = DbManager.getInstance(); dbManager.setDebug(true); dbManager.setConnectionProvider(cp); dbManager.setSessionProvider(new ThreadDbSessionProvider()); DbOomManager dbOomManager = DbOomManager.getInstance(); // manual configuration (before entities registration) TableNamingStrategy tns = new TableNamingStrategy(); tns.setPrefix("jd_"); tns.setUppercase(false); dbOomManager.setTableNames(tns); ColumnNamingStrategy cns = new ColumnNamingStrategy(); cns.setUppercase(false); dbOomManager.setColumnNames(cns); // automatic configuration AutomagicDbOomConfigurator dbcfg = new AutomagicDbOomConfigurator(); dbcfg.setIncludedEntries(this.getClass().getPackage().getName() + ".*"); dbcfg.configure(dbOomManager); }
Let's analyze what we did here.
Connection pool
Here we use CoreConnectionPool as application connection pool. It is
working, it is simple and it is available out of Tomcat. Of course,
nothing is stopping us from using DataSource - in that case we would
use the DataSourceConnectionProvider instead. However, data source is
not available out of Tomcat; then usually you need to put jdbc driver in
Tomcats lib folder…
Notice little trick we used: connection pool is manually registered as Petite bean, and then immediately lookuped from the container. Why that? So the connection pool instance becomes aware of Petite properties (defined in previous step). Properties will be loaded during Petite initialization and then injected into the bean on its creation.
This trick allow us to externalize connection pool configuration in the
app.props file:
# database
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jodd-example
jdbc.username=root
jdbc.password=root!
# db pool
dbpool.driver=${jdbc.driverClassName}
dbpool.url=${jdbc.url}
dbpool.user=${jdbc.username}
dbpool.password=${jdbc.password}
dbpool.maxConnections=50
dbpool.minConnections=5
dbpool.waitIfBusy=true
Be sure that your IDE or build system copy *.props resources to the classpath.
SessionProvider
Since we do not want to manually open sessions, we can instead use a session provider: nice, friendly class that will provide database session when required. Since we are building a web application, we can assume that each request works in separate thread, so we can create a db session and store it in the thread storage to make it available during the request.
DbOom configuration
DbOom is set through DbOomManager. The only thing we set here is the
table prefix and if table names and columns names are in lowercase. Of
course, by using the same trick as above, we can also externalize
DbOom settings in the props file, too.
The best part is left for the end: automatic registration of all db entities found on the class path.
DB Entities
Although Db entities in Jodd are simple POJOs, you may create
one base abstract type for all entities. To make our life simpler, we
gonna put some more assumptions: lets say that all entities have Long
primary key named ID. Now, if id property of entity is set, we say
that entity is persistent (stored in database), if property is null,
we say entity is new, not saved yet.
Using abstract entities is NOT mandatory and it's purely your decision! Jodd is happy with just any POJO. Also, using abstract entities may make sub-classing and sharing entities details difficult, if not impossible.
Our abstract entity may look like:
public abstract class Entity { @DbId protected Long id; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public boolean isPersistent() { return id != null; } @Override public int hashCode() { if (id == null) { return System.identityHashCode(this); } return 31 * id.hashCode(); } @Override public boolean equals(Object o) { if (this == o) { return true; } if (o instanceof Entity == false) { return false; } Entity entity = (Entity) o; if (id == null && entity.id == null) { return this == o; } if ((id == null) || (entity.id == null)) { return false; } return id.equals(entity.id); } @Override public String toString() { return "Entity{" + this.getClass().getSimpleName() + ':' + id + '}'; } }
Now create domain/model objects that inherits the Entity:
@DbTable public class User extends Entity { @DbColumn private String name; @DbColumn private String email; ... // accessors methods }
User entity is now mapped to the following database table (MySql
syntax):
create table jd_user( id int unsigned not null primary key auto_increment, name varchar(20), email varchar(20) );
This makes mapping done! We are ready to use our mapped table, but… let's first make things even more easier :)
Generic Dao
Now we are able to create generic DAO for basic operations. We will use
DbEntitySql generators for that. Before we continue, we will assume
one last thing: the IDs are autogenerated on insert. Again, we can build
the generic DAO in other cases as well, such as using sequences for
calculating the next ID value or using specific tables etc; this all
depends on how You would like to organize the database.
Back to generic DAO source:
import static jodd.db.oom.DbOomQuery.query; import static jodd.db.oom.sqlgen.DbEntitySql.insert; import static jodd.db.oom.sqlgen.DbEntitySql.updateAll; @PetiteBean public class AppDao { public <E extends Entity> E store(E entity) { if (entity.isPersistent() == false) { DbQuery q = query(insert(entity)); q.setGeneratedColumns(); q.executeUpdate(); long key = q.getGeneratedKey(); entity.setId(Long.valueOf(key)); q.close(); } else { query(updateAll(entity)).autoClose().executeUpdate(); } return entity; } public <E extends Entity> E findById(Class<E> entityType, Long id) { return query(DbEntitySql.findById(entityType, id)) .autoClose().find(entityType); } public <E extends Entity> E findOne(E criteria) { return query(DbEntitySql.find(criteria)) .autoClose().find(criteria.getClass()); } public <E extends Entity> List<E> findAll(E criteria) { return query(DbEntitySql.find(criteria)) .autoClose().list(criteria.getClass()); } public void deleteById(Class entityType, Long id) { query(DbEntitySql.deleteById(entityType, id)) .autoClose().executeUpdate(); } .... }
Check GenericDao in DbOom. It may be a good base class to extend from.
Notice there is no single line of SQL used in the code. Common SQL
codes, like for creating and finding are generated by DbEntitySql.
Also notice that we used 'auto-close' mode. This was not necessary
as queries would be closed on session end, but since this is a utility
method it is a good practice to close created queries.
Usage example, finally
We all waited this :) From here you can continue in any direction you
like. Here is just a quick usage example. First, lets wire AppDao with
FooService:
@PetiteBean public class FooService { @PetiteInject AppDao appDao; public void storeUser(User user) { appDao.store(user); } }
And now use it in our Madvoc action:
...
@Action
public void view() {
User user = new User();
user.setName("test");
user.setEmail("test@foo.com");
fooService.storeUser(user);
}
...
Now every time when you visit a page, a new User object
is saved in the database. Except… an exception will be thrown:
jodd.db.DbSqlException: No DbSession associated with current thread.
It seems that ThreadDbSessionHolder is not used.
jodd.db.ThreadDbSessionProvider.getDbSession(ThreadDbSessionProvider.java:26)
We didn't create any database session!
Don't forget DbSession!
Every database operation must operate inside a DbSession. For this first
step, lets control database sessions manually. Start by creating simple
session manager like this:
@PetiteBean public class AppDbSession { @PetiteInject ConnectionProvider dbpool; public void start() { DbSession dbSession = new DbSession(dbpool); ThreadDbSessionHolder.set(dbSession); } public void stop() { ThreadDbSessionHolder.remove(); } }
We are creating sessions on demand and store them in thread local
variable, through ThreadDbSessionHolder. This way our session
provider, ThreadDbSessionProvider will return database session
whenever some database operation request one. This enables
sharing of database session during one request. Of course,
we must manually close the session at the end.
Now we can use above class like this:
@PetiteBean public class FooService { @PetiteInject AppDao appDao; @PetiteInject AppDbSession appDbSession; public void storeUser(User user) { appDbSession.start(); appDao.store(user); appDbSession.stop(); } }
Restart Tomcat and go to index.html page. No more exceptions,
and user is finally stored to database.
This is the most simple way of handling database sessions. Sure, you ca fire AOP and make things even easier. Keep reading to meet our transactional manager ;)
Recapitulation
We learned here to create POJO entity that is mapped to the database table. We also learned that for basic DB operations we don't even have to write any SQL. Finally, we learned about database sessions.