The IdleWorx Blog: mysql

Showing posts with label mysql. Show all posts

February 3, 2011

The IdleWorx Monster MyBatis Tutorial

Building an entire Java web application with MyBatis 3, Tomcat, MySQL and Eclipse (on Windows 7)

This is a multi part tutorial which will show you how to build a Servlet/JSP based web application from scratch, using MySQL, Tomcat and MyBatis (the new version of the iBatis framework) on a windows platform using Eclipse.

You will make the most of this tutorial if

You have built at least a few small webapplications in the past.
You know how to setup Tomcat 5.x or higher version
You are somewhat familiar with the Eclipse IDE
You are familiar with SQL.
You know how to set-up MySQL on your development box and integrate it with your java web application.

Let's get started

To see how Tomcat, MyBatis and MySQL work together we're going to create a tiny java web application in Eclipse, called ModelingAgency.

If you don't want to follow the whole tutorial, you can skip through to the different sections below and pick out the information you need, otherwise continue to Part 1 - The Setup

At the end of this tutorial you will hopefully be able to create a basic Java JSP/Servlet based webapp with MyBatis, MySQL and Tomcat in an Eclipse environment.

You can download a full version of finished ModelingAgency webapp here.

This tutorial took me a long time to write, so if you found it useful in any way, if you found errors with it or you think something is missing, please let me know and I will do my best to improve it.

Part 11: Finalizing the Interface

If you have followed this tutorial so far, you will probably remember that in Part 2 I had shown you how the GUI will look when we're done. Here it is again:

We have put together everything except our interface, so it's time to modify our index.jsp to finalize the interface. Here's how your index.jsp file should look right now:

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
 <title>Modeling Agency Index</title>
</head>
<body>

<h1>The IdleWorx Modeling Agency</h1>
<hr>

<div style="width:300px;float:left;">
 
 <h2>Available Clients</h2>
 <c:forEach items="${all_clients}" var="client">
  <a href="client.do?id=${client.id}">${client.name}</a><br>
 </c:forEach> 
</div>

<div style="width:300px;float:left;">
 <h2>Available Models</h2>
 <c:forEach items="${all_models}" var="model">
  <a href="model.do?id=${model.id}">${model.name}</a><br>
 </c:forEach> 
</div>

<div style="clear:both;">
 <h2>Bookings</h2>
 <table border="1">
 <tr>
  <th>Client</th>
  <th>Model</th>
  <th>Time</th>
  <th>Place</th>
 </tr>
 <c:forEach items="${all_bookings}" var="booking">
 <tr>
  <td>${booking.client.name}</td>
  <td>${booking.model.name}</td>
  <td>${booking.time}</td>
  <td>${booking.location}</td>
  <td></td>
 </tr>
 </c:forEach> 
 </table>
</div>

</body>
</html>

Open up: http://localhost:8080/ModelingAgency/test.do. You should now have an interface very similar to the image above.

You'll notice I used the JSTL and EL libraries to nicely write our output (please don't use scriptlets in your JSP classes).

Also if you haven't noticed so far, in your ModelingAgency web application folder, you should have a file called sql.log wich has logged all of the SQL statements that MyBatis generated. You may find it useful when debugging your MyBatis apps

Congrats

If you got this far, I hope you now feel more comfortable about MyBatis,Tomcat and MySQL. You should now have all the basics to get a very simple app started in Eclipse using these technologies.

You can download a full version of the app here.

This tutorial took me a long time to write, so if you found it useful in any way, if you found errors with it or you think something is missing, please let me know and I will do my best to improve it.

Go back to the IdleWorx Monster MyBatis Tutorial

Part 10: Initializing MyBatis on Application Startup

If you've been following Part 9 of this tutorial, we now need to make sure our application has an sqlSessionVariable available ready to go when the TestServlet is invoked.

I had recently written a more detailed article about how to initialize MyBatis when a web application starts up. You can read that here.

For this part of the tutorial, I'm just going to summarize what you need to do to get MyBatis to automatically initialize when the ModelingAgency webapp starts.

Setting up a ServletContextListener

A ServletContextListener is useful when you want to do something when a java application starts up, more specifically, when a ServletContext is initalized.

In our case, we're going to use a ServletContextListener to set a MyBatis SqlSessionan application scoped varible. This will allow us to instantiate our ModelingAgencyDAO object from our TestServlet. Here's how to do it:

1) Register the listener in web.xml

Add the following code to web.xml

<listener>
   <listener-class>
  com.modelingagency.listeners.CustomServletContextListener
   </listener-class>
 </listener>

2) Create the Listener class

Create the class CustomServletContextListener.java under the com.modelingagency.listeners package:


package com.modelingagency.listeners;
 
import java.io.Reader;
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
 
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 
public class CustomServletContextListener implements ServletContextListener
{
 public void contextInitialized(ServletContextEvent event)
 {  
  ServletContext ctx = event.getServletContext(); 
        
     String resource = "mybatis.config.xml";
     try{      
      Reader reader = Resources.getResourceAsReader(resource);     
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); //this will load the default environment configured in mybatis.config.xml
      ctx.setAttribute("sqlSessionFactory", sqlSessionFactory); //set the sqlSessionFactory as an application scoped variable
     }
     catch(Exception e){
      System.out.println("FATAL ERROR: myBatis could not be initialized");
      System.exit(1);
     }    
 }
 
 @Override
 public void contextDestroyed(ServletContextEvent event){
   //nothing to do here right now
 }
}

If everything went well so far, you should be able to run the application again. Going to http://localhost:8080/ModelingAgency/test.do however will show the same things as before. So we need to finalize our interface.

Part 11: Finalizing the Interface

Part 9: Creating the DAO Layer

Now that both our Java Data Model and our Database Model are connected through a MyBatis mapper file, we will add a Data Access Object class so we can abstract the calling of SQL statements, and keep our servlet clean of database related code.

I know it's not a 'pure' implementation of the DAO pattern however it serves the main purpose of decoupling servlet code from database related code.

If you want to see a better way to integrate MyBatis and basic DAO classes, check out my other blog post MyBatis DAO Example Code Tutorial

ModelingAgencyDAO.java

ModelingAgencyDAO is going to be a Java class which encapsulates all the 'logical' database functionality we need, such as getting a list of all clients, models and bookings in our database. It will accomplish this by using the MyBatis framework.

For now, create the file ModelingAgencyDAO.java under the com.modelingagency.db package, with the following content:


package com.modelingagency.db;

import java.util.ArrayList;

import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;

import com.modelingagency.objects.Booking;
import com.modelingagency.objects.Client;
import com.modelingagency.objects.Model;

public class ModelingAgencyDAO {

 private SqlSessionFactory sf;
 
 //constructor will receive a myBatis sessionFactory object
 public ModelingAgencyDAO(SqlSessionFactory containerSessionFactory) {
  if(containerSessionFactory==null)
   System.err.println("Error: could not load myBatis sessionFactory");  
  sf = containerSessionFactory;
 }
   
 public ArrayList<Client> getClients() throws PersistenceException{
    
  SqlSession session = sf.openSession();
  try
  {   
   ArrayList<Client> clients = (ArrayList<Client>)session.selectList("com.modelingagency.objects.SimpleMappers.allClients");    
   return clients;  
  }
  finally
  {
   session.close();
  }
  
 }
  
 public ArrayList<Model> getModels() throws PersistenceException{
    
  SqlSession session = sf.openSession();
  try
  {   
   ArrayList<Model> models = (ArrayList<Model>)session.selectList("com.modelingagency.objects.SimpleMappers.allModels");    
   return models;
  }
  finally
  {
   session.close();
  }
  
 }
 
 @Test
 public ArrayList<Booking> getBookings() throws PersistenceException{
    
  SqlSession session = sf.openSession();
  try
  {   
   ArrayList<Booking> bookings = (ArrayList<Booking>)session.selectList("com.modelingagency.objects.SimpleMappers.allBookings");    
   return bookings;   
  }
  finally
  {
   session.close();
  }
  
 }
 
}

You will notice the methods in this DAO class are very similar to the ones we defined in the MyBatisTest class in Part 8. It's a good idea to have a test case setup for your DAO classes.

Now in order to use the DAO from a servlet, all you have to do is create a new object and pass in a MyBatis SqlSessionFactory through the constructor.

Let's modify our TestServlet so we can make use of our DAO class to retreive a list of all clients, a list of all models, and a list of all bookings. Your TestServlet.java class should now look like this:


package com.modelingagency.servlets;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.ibatis.exceptions.PersistenceException;
import org.apache.ibatis.session.SqlSessionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.modelingagency.db.ModelingAgencyDAO;
import com.modelingagency.objects.*;

public class TestServlet  extends HttpServlet{
 
 private static Logger log = LoggerFactory.getLogger(TestServlet.class);
 
 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
 {
  HttpSession session = request.getSession();   
  ServletContext sc = getServletContext();
  
  SqlSessionFactory sf = (SqlSessionFactory)getServletContext().getAttribute("sqlSessionFactory");
  ModelingAgencyDAO dao = new ModelingAgencyDAO(sf);
  
  try
  {
   ArrayList<Client> clients = dao.getClients();    
   ArrayList<Model> models = dao.getModels();    
   ArrayList<Booking> bookings = dao.getBookings();    
   
   request.setAttribute("all_clients", clients); 
   request.setAttribute("all_models", models);
   request.setAttribute("all_bookings", bookings);
       
  }
  catch(PersistenceException p)
  {
   p.printStackTrace();  
  }
  finally
  {
   request.setAttribute("message","Welcome to the modeling agency MyBatis tutorial");
   log.info("The message attribute has been set");
  } 
                
  RequestDispatcher view = request.getRequestDispatcher("index.jsp");
  view.forward(request, response);
 }
 
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  doPost(request,response);
 }
 
}

But before you can run the web application, you'll need to make sure that MyBatis has been initalized and available to the ModelingAgency app.

Part 10: Initializing MyBatis on application startup

Part 8: Testing MyBatis with jUnit

To be certain that the MyBatis configuration and Mapper files you setup in Part 7 are working properly, let's create a jUnit test to make sure.

First, you will need to add the jUnit library to your Eclipse project:

Right Click on the ModelingAgency project in Project Explorer
Go to Properties -> Java Build Path -> Libraries Tab -> Add Library (button)
Select jUnit4 and click Finish

Second, create a new package under your webapp called com.modelingagency.tests.
We will keep our jUnit test classes in here.

Third, add the file MyBatisTest.java to the com.modelingagency.tests package:

package com.modelingagency.tests;

import static org.junit.Assert.*;

import java.io.Reader;
import java.util.ArrayList;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.modelingagency.objects.Booking;
import com.modelingagency.objects.Client;
import com.modelingagency.objects.Model;

public class MyBatisTest {
 
 private static Logger log = LoggerFactory.getLogger(MyBatisTest.class);
 private static SqlSessionFactory sf;
 
  
 @BeforeClass
 public static void setUp() throws Exception {
  log.info("starting up myBatis tests");
  String resource = "mybatis.config.xml";
  Reader reader = Resources.getResourceAsReader(resource);  
  sf = new SqlSessionFactoryBuilder().build(reader,"testing"); //we're using the 

'testing' 
 }
 
 @AfterClass
 public static void tearDown() throws Exception {  
  log.info("closing down myBatis tests");
 }
 
 @Test
 public void getClients(){
    
  SqlSession session = sf.openSession();
  try
  {   
   ArrayList client = (ArrayList)session.selectList

("com.modelingagency.objects.SimpleMappers.allClients");    
   assertNotNull("Client list is null",client);   
  }
  finally
  {
   session.close();
  }
  
 }
  
 @Test
 public void getModels(){
    
  SqlSession session = sf.openSession();
  try
  {   
   ArrayList model = (ArrayList)session.selectList

("com.modelingagency.objects.SimpleMappers.allModels");    
   assertNotNull("Model list is null",model);
  }
  finally
  {
   session.close();
  }
  
 }
 
 @Test
 public void getBookings(){
    
  SqlSession session = sf.openSession();
  try
  {   
   ArrayList bookings = (ArrayList)session.selectList

("com.modelingagency.objects.SimpleMappers.allBookings");    
   assertNotNull("Booking is null",bookings);   
  }
  finally
  {
   session.close();
  }
  
 }
 
 
}

I'm not going into detail into what the MyBatisTest class does, but it should be self explanatory. It loads the MyBatis configuration we have defined, using the 'test' environment, and then runs some test methods against the queries we have defined in the SimpleMappers.xml mapper file.

To run the test, right click on the MyBatisTest.java, and select
Run As -> jUnit Test

If everything went well, you should see the following output in the jUnit window:

Now you're ready to move on.

Part 9: Creating the DAO Layer

Part 7: Configuring MyBatis

If you have followed the first parts of this tutorial Part 1 to Part 6 you should now have a very simple MySQL database setup and the java layer ready for the ModelingAgency web application.

Now I'm going to show you how to integrate your database and java model layer with MyBatis 3

Setting up the MyBatis configuration file
Configuring the mapper file

The MyBatis configuration file

First it's time to add the main MyBatis configuration file to your web application. You can call this file anything you want, but let's use mybatis.config.xml for now.

This file will hold all the MyBatis configuration info and references to all the other sql mapping files we will define.

Create the following file /src/mybatis.config.xml:


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" 
 "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- Created by 2011: http://blog.idleworx.com http://www.idleworx.com -->
<configuration>
 
 <properties resource="dbcp.properties"/>
  
 <settings>
  <setting name="useGeneratedKeys" value="true"/>
 </settings>
    
 <typeAliases>  
  <typeAlias alias="Client" type="com.modelingagency.objects.Client"/>
  <typeAlias alias="Model" type="com.modelingagency.objects.Model"/>
  <typeAlias alias="Booking" type="com.modelingagency.objects.Booking"/>  
 </typeAliases>
  
 <environments default="development">
  
  <environment id="development">
   <transactionManager type="JDBC"/>
   <dataSource type="JNDI">
    <property name="initial_context" value="java:comp/env"/>
    <property name="data_source" value="/jdbc/mydb"/> 
   </dataSource>
  </environment>
  
  <environment id="testing">
   <transactionManager type="JDBC"/>
   <dataSource type="POOLED">
    <property name="driver" value="${db.driver}"/>
    <property name="url" value="${db.url}"/>
    <property name="username" value="${db.user}"/>
    <property name="password" value="${db.pass}"/>
   </dataSource>
  </environment>
    
 </environments>

 <mappers>
  <mapper resource="com/modelingagency/objects/SimpleMappers.xml"/>
 </mappers>

</configuration>

Also, create the following file /src/dbcp.properties:

db.url=jdbc:mysql://localhost/modeling_agency
db.driver=com.mysql.jdbc.Driver
db.user=test
db.pass=test

Notes

You'll notice we have a 'testing' environment setup. We'll use that for testing our app with jUnit.
Using the useGeneratedKeys property will allow MyBatis to make use of the autogenerated row id values from the database, as we defined in the SQL in Part 2: client_id INTEGER NOT NULL AUTO_INCREMENT
typeAliases will simplify things for you, by helping MyBatis identify fully qualified java class names. This way you won't have to type the fully qualified names of your objects in MyBatis mapper files
In a typical application there are usually many SQL mapper files, however for our simple application we only really need one, the SimpleMappers.xml file
The mybatis.config.xml file should go under /src/mybatis.config.xml (this is because the file should be on the application's classpath)

Tip:If you're wondering how to setup multiple environments in MyBatis see my other blog posting: Initializing MyBatis Using a ServletContextListener

Configuring the Mapper file

MyBatis SQL mappers allow us to map our java data model to our database model.

The mapper file holds all the sql statments you need to interact with your database, so it's pretty important, and it also happens to be a critical element of the MyBatis framework, which will simplify your life a lot, once you pass the learning curve :)

The mapper file also stores definitions of objects that will be used to retrieve data from the database. By doing this MyBatis abstracts most of the hard work of writing tons of sql statements, and most importantly jdbc code.

Since we've configured this file's location (com/modelingagency/objects/SimpleMappers.xml) in the mybatis.config.xml file above, MyBatis now knows to load this resource whenever it is initialized.

The main functionality we're going to need now in the SimpleMapper.xml file is this:

1) Getting a list of clients
2) Getting a list of models
3) Getting a list of all bookings

Create the following file: /src/com/modelingagency/objects/SimpleMappers.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">
<!-- Created by: http://blog.idleworx.com, http://www.idleworx.com -->
<mapper namespace="com.modelingagency.objects.SimpleMappers">
 
 <resultMap id="clientResultMap" type="Client">
  <id property="id" column="client_id" />  
  <result property="name" column="client_name" />  
 </resultMap>
 
 <resultMap id="modelResultMap" type="Model">
  <id property="id" column="model_id" />  
  <result property="name" column="model_name" /> 
 </resultMap>
  
 <resultMap id="bookingResultMap" type="Booking">
  <id property="id" column="booking_id" />  
  <result property="time" column="booking_time" />    
  <result property="location" column="booking_location" />
  <association property="client" column="booking_client_id" javaType="Client" resultMap="clientResultMap"/>
  <association property="model" column="booking_model_id" javaType="Model" resultMap="modelResultMap"/>
 </resultMap> 
  
 <select id="allClients" parameterType="string" resultMap="clientResultMap">
  select  * from client order by 1  
 </select>
 
 <select id="allModels" parameterType="string" resultMap="modelResultMap">
  select  * from model order by 1
 </select>
 
 <select id="allBookings" parameterType="string" resultMap="bookingResultMap">
  select * from client, model, booking
  where booking_client_id = client_id and booking_model_id = model_id
  order by 1
 </select>
 
</mapper>

Notes

The name of each query is identified by the 'id' parameter
parameterType indicates what type of parameter you will pass into the sql statement (for simple queries there is only one parameter or none), for queries which require multiple parameters you can use the 'hashmap' parameterType to pass in name value pairs. In our case the parameter is not actually used.
By defining result maps for each of our objects, MyBatis knows how to automatically map a column from the database table directly to the javabean property name (in our case the Client,Model and Booking classes)
If you don't use Result Maps in your mapper files, you can use the resultType attribute instead which specifies the Java Bean class name we want to map, and which is defined in mybatis.config.xml. Normally you have to put a fully qualified class name here like com.modelingagency.objects.Model, but because if you defined the typeAliases element in the mybatis.config.xml you can just use the short name
The allBookings query makes use of MyBatis associations. Associations are what MyBatis uses to retrieve one-to-many relationships from your database into java objects. To learn more about this read: Understanding MyBatis' Multiple Table Joins

To make sure the MyBatis configuration and mapper files are all setup correctly, before we create our DAO layer and use it from our Servlet, we need to do a quick test.

Part 8: Testing MyBatis with jUnit

Part 6: The Java POJO model

If you've been following IdleWorx's Tutorial on Building a Web Application with MyBatis,MySQL,Tomcat and Eclipse , you should be now ready to setup the database layer of the ModelingAgency webapp.

In order to be able to interact with the database we setup in Part 2 we will create a simple java model made of POJOs (simple JavaBeans that is).

These Plain Old Java Objects (POJOs) will serve as the model, or in other words, a way to transfer data between the database and the ModelingAgency webapp.

At this point we're not concerned about how we'll actually retrieve the data from the database into our model classes, that is where MyBatis will come in later on and take care of that for us.

If you remember our database layout from Part 2, we have 3 business objects in our application that we need to model:

Client
Model
Booking

So let's add the following classes to the com.modelingagency.objects package:

Client.java


package com.modelingagency.objects;

/**
 * Copyright 2011: http://blog.idleworx.com, http://www.idleworx.com   
 */
public class Client {
 private Integer id;
 private String name;
 
 public Client(){}
 
 public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 
 @Override
 public String toString() {
  return "[client] " + "(" + id + ")" + " " + name;
 }
}

Model.java

package com.modelingagency.objects;

/**
 * Copyright 2011: http://blog.idleworx.com, http://www.idleworx.com   
 */
public class Model {

 private Integer id;
 private String name;
 
 public Model() {}
 
 public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 
 @Override
 public String toString() {
  return "[model] " + "(" + id + ")" + " " + name;
 }
}

Booking.java

package com.modelingagency.objects;

import java.sql.Timestamp;
import java.util.Date;

/**
 * Copyright 2011: http://blog.idleworx.com, http://www.idleworx.com   
 */
public class Booking {

 private Integer id;
 private Client client;
 private Model model;
 private Timestamp time;
 private String location;
 
 public Booking(){}
 
 public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 public Client getClient() {
  return client;
 }
 public void setClient(Client client) {
  this.client = client;
 }
 public Model getModel() {
  return model;
 }
 public void setModel(Model model) {
  this.model = model;
 }
 public Timestamp getTime() {
  return time;
 }
 public void setTime(Timestamp time) {
  this.time = time;
 }
 public String getLocation() {
  return location;
 }
 public void setLocation(String location) {
  this.location = location;
 }
 
 @Override
 public String toString() {
  return "[booking] " + "(" + id + ")" + " where: " + location + " when: " + new Date(time.getTime());
 }
 
}

Note that the Booking model class doesn't have getters for the booking_client_id and booking_model_id fields which are part of the booking table.

We're not going to model each field directly into our java beans, instead we're going to let MyBatis retrieve the actual Client and Model objects whenever a Booking object is returned from the database. And we're going to accomplish this through the use of MyBatis Result Maps, as you will shortly see.

Before we do that however, we need to setup MyBatis.

Part 7: Configuring MyBatis

Part 5: Setting up Logging

If you've followed Part 4 of this tutorial, we're about to setup logging for the ModelingAgency webapp in order to learn how to use MySQL, MyBatis and Tomcat togather.

If you're familiar with Log4J you can use that library. However for the ModelingAgency webapp we're going to setup the Logback library, which is the new Log4J.

You have probably already downloaded the Logback jars in Part 1. If not, you can download all of the jars required for the ModelingAgency app here.

Configuring Logback

Configuring Logback with a Java web application is relatively simple. Once you have the required jars in /WEB-INF/lib, create a file called logback.xml and put it under your project's /src folder.

Here is the logback.xml file we we will use for this project:

<?xml version="1.0" encoding="UTF-8"?>
<configuration scan="true" debug="false" > <!-- set debug to true to dump logback status info when library is loaded -->
 
  <contextName>ModelingAgency</contextName>
  
  <!-- APPENDERS -->
  
  <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">    
    <!-- encoders are assigned the type ch.qos.logback.classic.encoder.PatternLayoutEncoder by default -->
    <encoder> 
      <pattern>[%contextName] %d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
    </encoder>
  </appender>
  
  <appender name="SQLDUMP" class="ch.qos.logback.core.FileAppender">
    <file>sql.log</file>
    <append>false</append>
    <encoder>
      <!-- <pattern>%date %level [%thread] %logger{10} [%file:%line] %msg%n</pattern> -->
      <pattern>[%contextName] [%c] [%d{dd MMM yyyy - hh:mm:ss}] %5p - %m %n</pattern>      
    </encoder>
  </appender>
  
  <appender name="FILE" class="ch.qos.logback.core.FileAppender">
    <file>application.log</file>
    <encoder>
      <!-- <pattern>%date %level [%thread] %logger{10} [%file:%line] %msg%n</pattern> -->
      <pattern>[%contextName] [%c] [%d{dd MMM yyyy - hh:mm:ss}] %5p - %m %n</pattern>      
    </encoder>
  </appender>
  
  <appender name="OUT" class="ch.qos.logback.core.ConsoleAppender">    
    <encoder>
    <pattern>[%contextName] [%c] [%d{dd MMM yyyy - hh:mm:ss}] %5p - %m %n</pattern>        
    </encoder>
  </appender>
  
   <appender name="OUT_SIMPLE" class="ch.qos.logback.core.ConsoleAppender">    
    <encoder>
    <pattern>[%c] %5p - %m %n</pattern>        
    </encoder>
  </appender>
        
  <!-- LOGGERS -->  
    
  <!--  loggers inherit the appender from their parent ( the root logger ) -->
  <logger name="java.sql" level="DEBUG" additivity="false"> 
   <appender-ref ref="SQLDUMP" /> 
  </logger>
   
  <logger name="org.apache.ibatis.datasource" level="WARN"/> <!-- removes datasoure debug info from logs --> 
       
  <root level="debug"> <!-- can set level to OFF-->
    <appender-ref ref="OUT" />
  </root>
  
</configuration>

Adding logging to TestServlet

To make sure logback works properly, let's add some simple logging code to the TestServlet we created in Part 4

Add the following logging code to the TestServlet.java file:


package com.modelingagency.servlets;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class TestServlet  extends HttpServlet{
 
 private static Logger log = LoggerFactory.getLogger(TestServlet.class);
 
 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
 {
  HttpSession session = request.getSession();   
  ServletContext sc = getServletContext();
  
  request.setAttribute("message","This is a message from the TestServlet. It works!");
  log.info("The message attribute has been set");
             
  RequestDispatcher view = request.getRequestDispatcher("index.jsp");
  view.forward(request, response); 
 }
 
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  doPost(request,response);
 }
 
}

Restart your project now, by right clicking on the index.jsp file and selecting Run on server.

If you access the TestServlet's url on your local server now, http://localhost:8080/ModelingAgency/test.do you should get an output like this:

[ModelingAgency] [com.modelingagency.servlets.TestServlet] [03 Feb 2011 - 10:38:26] INFO - The message attribute has been set

If you got this far, it means you have setup Logback logging for the ModelingAgency webapp.

Now the fun part,

Part 6: Creating the java pojo model

Part 4: Creating a Test Servlet

If you've been following Part 3 of this tutorial, you are on your way to creating a Java Web Application using MyBatis,MySQL and Tomcat in Eclipse.

Creating a test Servlet

So far we have created the MySQL database for the ModelingAgency application in Part 2 and we have setup the ModelingAgency project in Eclipse in Part 4, but we haven't used any Servlets yet.

Even if you're not familiar with the MVC model (which you should be if you're reading this tutorial), if you're using plain old Servlets these days it's typical to use them as a Controller.

In other words as a java classes responsible for reading request parameters, performing business logic, and passing on the necessary data to a view (eg. a jsp file) in the form of request parameters.

If all this sounds confusing to you, and you're considering learning Java Web Application Development, I would strongly recommend grabbing a copy of Head 1st Servlets.

To take our application to the next level, and help you understand servlets better, we're going to create a test servlet.

The Servlet

Create a new java class called TestServlet.java under the com.modelingagency.servlets package:


package com.modelingagency.servlets;

import java.io.IOException;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

public class TestServlet  extends HttpServlet{

 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
 {
  HttpSession session = request.getSession();   
  ServletContext sc = getServletContext();
  
  request.setAttribute("message","This is a message from the TestServlet. It works!"); 
             
  RequestDispatcher view = request.getRequestDispatcher("index.jsp");
  view.forward(request, response); 
 }
 
 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  doPost(request,response);
 }
 
}

We're going to use this servlet to display a simple text string to make sure things are working, before moving forward.

Don't forget to configure your servlet in the deployment descriptor, so the webapp knows how to 'serve' it.

Your web.xml file should now look like this:


<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
 
 <display-name>ModelingAgency</display-name>
 
 <welcome-file-list>
  <welcome-file>index.html</welcome-file>
  <welcome-file>index.htm</welcome-file>
  <welcome-file>index.jsp</welcome-file>
  <welcome-file>default.html</welcome-file>
  <welcome-file>default.htm</welcome-file>
  <welcome-file>default.jsp</welcome-file>
 </welcome-file-list>
 
 <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/mydb</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
  
  <servlet>
  <servlet-name>test</servlet-name>
  <servlet-class>com.modelingagency.servlets.TestServlet</servlet-class>  
 </servlet>
 
 <servlet-mapping>
  <servlet-name>test</servlet-name>
  <url-pattern>/test.do</url-pattern>
 </servlet-mapping> 
  
</web-app>

index.jsp

Before we re-start our server and run our application again to see if all this works, don't forget to modify index.jsp so it will display the attribute you set in the servlet

The index.jsp file should now look like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
 <title>Modeling Agency Index</title>
</head>
<body>

Things are looking good.
<br>
${requestScope.message}

</body>
</html>

If you run the application again, and go the the servlet url that we setup in the deployment descriptor, http://localhost:8080/ModelingAgency/test.do you should see the output:

Things are looking good.
This is a message from the TestServlet. It works!

What next

If you got so far, you are well on your way to setting up a web application using MySQL,MyBatis and Tomcat using Eclipse. Before we get to the MyBatis part, lets see how to setup logging for the ModelingAgency web application (or any java web application for that matter).

Part 5: Setting up Logging

Part 3: Creating the ModelingAgency project in Eclipse

If you've followed the previous tutorials Part 1 and Part 2 you should now be ready to create a simple java web application called ModelingAgency in Eclipse, in order to learn how MySQL, MyBatis and Tomcat work together.

Creating a new project

Open up Eclipse. Under the Project Explorer tab right click and select New -> Project
Select the Dynamic Web Project and click Next
Enter the project properties. For this project we will use the 2.4 Dynamic Web Module version, and the Tomcat 6.0 Runtime (which you may have to configure manually)
Click Finish. You should now have a project that looks like this:

Setup the java packages

To organize our code a little, let's create the following packages under our /src directory

com.modelingagency.db
com.modelingagency.listeners
com.modelingagency.objects
com.modelingagency.servlets

Click on Java Resources: src -> New -> Package and create the packages above

External jar files

Remember all those jar files you downloaded in Part 1 (the mybatis jar, standard.jar,jstl.jar,etc). Now it's time to make them available to the webapp by placing them under the /WEB-INF/lib folder.

You can download all the jar files you need to place in this folder here.

Configuring the database connection

In order to connect to the database we have setup in Part 2, we have to add a context.xml file under the /META-INF folder.

There are other was to setup a database connection from Tomcat, but placing it in /META-INF/context.xml allows each webapp to be bundled with its database configuration, and is easier to manage in my experience.

context.xml

Create the following file and place it under /META-INF/context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context docBase="ModelingAgency" path="/ModelingAgency" reloadable="true" >
                        
      <!-- http://commons.apache.org/dbcp/configuration.html -->      
      <Resource name="jdbc/mydb" auth="Container" type="javax.sql.DataSource"
               maxActive="100" 
               maxIdle="5" 
               maxWait="10000"
               
               username="test" password="test" 
               driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost/modeling_agency" 
               
               removeAbandoned="true"
               removeAbandonedTimeout="10"
      logAbandoned="true"
      validationQuery="SELECT 1" /> 
             
</Context>

Note:The docBase attribute and path map to the same name as your Eclipse project name. Also, If you have a different username or password that you created in Part 2, other than 'test' make sure to use it instead.

web.xml

The deployment descriptor, web.xml, is the most important piece in any Java webapp (but I'm sure you know that already).

In order to make the database connection we defined in context.xml known to your webapp, you should also add a resource entry in web.xml.

Edit your web.xml file so it looks like this:


<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
 
 <display-name>ModelingAgency</display-name>
 
 <welcome-file-list>
  <welcome-file>index.html</welcome-file>
  <welcome-file>index.htm</welcome-file>
  <welcome-file>index.jsp</welcome-file>
  <welcome-file>default.html</welcome-file>
  <welcome-file>default.htm</welcome-file>
  <welcome-file>default.jsp</welcome-file>
 </welcome-file-list>
 
 <resource-ref>
      <description>DB Connection</description>
      <res-ref-name>jdbc/mydb</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
  </resource-ref>
  
</web-app>

Starting the app

Just to make sure we're on the right track and things are working fine, let's create an index page and start our server:

First, create the following index.jsp file under the /WebContent folder.

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
 <title>Modeling Agency Index</title>
</head>
<body>

Things are looking good.

</body>
</html>

Now to start the ModelingAgency web application on a tomcat server,
right click the index.jsp file, select Run As -> Run on server

Make sure the Server's host name is localhost and the Server runtime environment is associated with your Tomcat 6.0 installation. Click Finish.

If all is well, the server should be running in Eclipse and you will see this:

Your project layout at this point should be looking like this:

Now you are ready to move on to

Part 4: Creating a Test Servlet

Part 2: The ModelingAgency Application

Part 1: The Setup

To see how Tomcat, MyBatis and MySQL work together, we're going to create a small Java web application in Eclipse called ModelingAgency.

The Modeling Agency web app does the following:

It lets a photographer (a client object) book a model (a model object) for a photography shoot at a particular place and time (a booking object).

The Interface

Our interface is going to look something like this when we're done:

You can think of the application as an appointment organizer for photographers. I'm sure this will make more sense when you see the actual tables.

The Tables

The client table

The model table

The booking table

The SQL

Here is the sql to create the MySQL database for our ModelingAgency, and populate with the data above:


create database modeling_agency;
use modeling_agency;
grant all on modeling_agency.* to 'test'@'localhost' identified by 'test' #feel free to rename this to whatever you want

CREATE TABLE modeling_agency.client
(
  client_id INTEGER NOT NULL AUTO_INCREMENT,
  client_name VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(client_id)
);

CREATE TABLE modeling_agency.model
(
  model_id INTEGER NOT NULL AUTO_INCREMENT,
  model_name VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(model_id)
);

CREATE TABLE modeling_agency.booking
(
 booking_id INTEGER NOT NULL AUTO_INCREMENT,
 booking_client_id INTEGER,
 booking_model_id INTEGER,
 booking_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 booking_location VARCHAR(100) NOT NULL DEFAULT '##NO LOCATION SELECTED##', 
 PRIMARY KEY (booking_id),
 CONSTRAINT `FK_booking_client` FOREIGN KEY (booking_client_id)
  REFERENCES modeling_agency.client (client_id),
 CONSTRAINT `FK_booking_model` FOREIGN KEY (booking_model_id)
  REFERENCES modeling_agency.model (model_id) 
);

insert into modeling_agency.client (client_name) values ("Michelangelo");
insert into modeling_agency.client (client_name) values ("Rafaelo");
insert into modeling_agency.client (client_name) values ("Leonardo");
insert into modeling_agency.client (client_name) values ("Donatello");

insert into modeling_agency.model (model_name) values ("Monalisa");
insert into modeling_agency.model (model_name) values ("Plain Jane");
insert into modeling_agency.model (model_name) values ("Funky Susan");
insert into modeling_agency.model (model_name) values ("Mary");

insert into booking (booking_client_id,booking_model_id,booking_location) values ((select client_id from client where client_name = 'Michelangelo'),(select model_id from model where model_name = 'Monalisa'),"Somewhere in Italy");
insert into booking (booking_client_id,booking_model_id,booking_location) values ((select client_id from client where client_name = 'Leonardo'),(select model_id from model where model_name = 'Plain Jane'),"Near the river");
insert into booking (booking_client_id,booking_model_id,booking_location) values ((select client_id from client where client_name = 'Donatello'),(select model_id from model where model_name = 'Mary'),"On the beach");

Note:Keep in mind the mysql test user we setup in the SQL above, as it will be the same you will use when you configure how Tomcat will work with your database

Now we're ready to setup the ModelingAgency project as a DynamicWeb project in eclipse.

Part 3: Creating the ModelingAgency project in Eclipse

Part 1: The Setup

Installing Eclipse

Chances are you already have Eclipse installed by now. Just make sure you have the Java EE version of Eclipse or at least the Web Tools Project installed.

For this tutorial I used Java EE Eclipse (Helios) SR1

If you are installing eclipse for the first time use this link: Download Java EE Eclipse Helios

Installing Tomcat 6

Go to: http://tomcat.apache.org/index.html and download the appropriate Tomcat distribution you need.
Tomcat 5.5 or 6 should both work with this tutorial.

For this tutorial I used Tomcat 6.0.29.
You can download this version here.

Installing MySQL

It's assumed at this point that you have a working MySQL database instance setup already. Ideally MySQL version 5.1 or above. If you already have the MySQL driver setup with your Tomcat server you can skip this section, if not, read on.

To use MySQL from your Java web application you will first need to download the MySQL Java Driver, also known as the JDBC Driver for MySQL (Connector/J)

1) Go to: http://www.mysql.com/downloads/connector/j/ and download JDBC Driver for MySQL (Connector/J) zip archive.

2) Extract the driver .jar file into your Tomcat installation:

CATALINA_HOME/common/lib folder (Tomcat 5) or CATALINA_HOME/lib (Tomcat 6)

For this tutorial I used the mysql-connector-java-5.1.13-bin.jar but any version above that should also work.

Tip: Avoid putting your MySQL driver jar under WEB-INF/lib as it may cause issues down the road when you want to use mysql with other webapps. You'll probably forget about it and it will cause class path conflicts. So the best bet is to leave it in the shared libraries folder of your Tomcat server. That way all webapps will have access to it.

Jar libraries for this tutorial

To make things quicker for you, you can download all the jar files you will need for this tutorial here. Once we setup the eclipse project in Part 3: Creating the ModelingAgency project in Eclipse, you can just extract all the jars here under the /WEB-INF/lib folder

However if you want to do it manually follow the rest of this tutorial.

Installing MyBatis

Go to: http://www.mybatis.org/java.html.
Click on Download the Persistence Framework and download the zipped archive mybatis-3.0.4-bundle.zip (direct link)

Once we have setup the ModelingAgency web application in Eclipse in Part 3: Creating the ModelingAgency project in Eclipse, you will extract the mybatis-3.0.4.jar to the /WEB-INF/lib folder of your application. Don't worry, I'll remind you about that later.

Tip:Before you go any futher, I would strongly suggest you go through the very well written MyBatis documentation before you continue with this tutorial (Especially if you've never used iBatis or MyBatis). It will take about 30 minutes to skim through it, and will give you an excellent foundation of the basics.

download the documentation

Installing JSTL/EL

One last thing. In our project we are going to use the JSTL library to make things easier a bit. With a combination of the JSP Expression Language and the JSTL library you should almost never need to write 'java' code inside your jsp files anymore. To learn more about this read The Evolution of Servlets and JSP.

To make use of Java's JSTL library and the JSP Expression Language in our project, you will need to download jstl.jar and standard.jar from the Jakarta Taglibs Project extract them to the /WEB-INF/lib folder of your application. Don't worry, I'll remind you about this later as well..

These jars can be found here:

Go to: http://jakarta.apache.org/site/downloads/downloads_taglibs-standard.cgi and download the 1.1.2.zip binary.
For direct link click here.

Installing LogBack (the new Log4j)

If you want to be able to log things to the console for this project you should also install LogBack, the successor of Log4J

Download logback-0.9.28.zip. You will need to extract the following files to the /WEB-INF/lib folder of the webapp we will setup in Part 3: Creating the ModelingAgency project in Eclipse.

logback-core-0.9.28.jar
logback-classic-0.9.28.jar

There is also an additional depndency which logback needs, which you can download from here: slf4j-api-1.6.1.jar

If you feel like everything here is setup correctly, let's move on to

February 3, 2011

Building an entire Java web application with MyBatis 3, Tomcat, MySQL and Eclipse (on Windows 7)

You will make the most of this tutorial if

Let's get started

Table of Contents

This tutorial took me a long time to write, so if you found it useful in any way, if you found errors with it or you think something is missing, please let me know and I will do my best to improve it.

Congrats

This tutorial took me a long time to write, so if you found it useful in any way, if you found errors with it or you think something is missing, please let me know and I will do my best to improve it.

Setting up a ServletContextListener

1) Register the listener in web.xml

2) Create the Listener class

ModelingAgencyDAO.java

The MyBatis configuration file

Notes

Configuring the Mapper file

Notes

Configuring Logback

Adding logging to TestServlet

Creating a test Servlet

The Servlet

index.jsp

What next

Creating a new project

Setup the java packages

External jar files

Configuring the database connection

context.xml

web.xml

Starting the app

The Interface

The Tables

The SQL

Installing Eclipse

Installing Tomcat 6

Installing MySQL

Jar libraries for this tutorial

Installing MyBatis

Installing JSTL/EL

Installing LogBack (the new Log4j)