Beginning MyBatis : DataSource, JNDI and Apache DBCP
In this tutorial, im trying to connect a servlet to mysql database using Apache DBCP. Im using mybatis and accessing Apache DBCP’s datasource via JNDI. Btw, im using Netbeans 6.9 and Apache Tomcat 6.
First as always, a table named “contoh” on a database “test”
CREATE TABLE contoh ( nama VARCHAR(30) NOT NULL, alamat VARCHAR(100), PRIMARY KEY (nama) ) insert into contoh (nama, alamat) values ('edwin', 'singapore'); insert into contoh (nama, alamat) values ('kamplenk', 'ciledug'); insert into contoh (nama, alamat) values ('nugie', 'pamulang'); insert into contoh (nama, alamat) values ('samsu', 'dago'); insert into contoh (nama, alamat) values ('tebek', 'karawaci');
and a simple java bean
package com.edw.mybatis.bean; import java.io.Serializable; public class Contoh implements Serializable { private String nama; private String alamat; // other setter and getter @Override public String toString(){ return nama+" : "+alamat; } }
next is creating a simple xml query, ContohMapper.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="com.edw.mybatis.mapper.ContohMapper" > <resultMap id="ContohMap" type="com.edw.mybatis.bean.Contoh" > <id column="nama" property="nama" jdbcType="VARCHAR" /> <result column="alamat" property="alamat" jdbcType="VARCHAR" /> </resultMap> <select id="selectAll" resultMap="ContohMap"> SELECT * FROM contoh </select> </mapper>
and an interface class to map my queries
package com.edw.mybatis.mapper; import com.edw.mybatis.bean.Contoh; import java.util.List; public interface ContohMapper { List<Contoh> selectAll(); }
Dont forget to create a connection pooling configuration on context.xml, which located under META-INF folder.
<?xml version="1.0" encoding="UTF-8"?> <Context antiJARLocking="true" path="/MyBatisDBCP"> <Resource name="jdbc/test" auth="Container" type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/test" username="root" password="xxxx" maxActive="100" maxIdle="30" maxWait="10000" removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" /> </Context>
an xml configuration, Configuration.xml. This is where i put my connection properties.
<?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"> <configuration> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="JNDI"> <property name="data_source" value="java:/comp/env/jdbc/test"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/edw/mybatis/xml/ContohMapper.xml" /> </mappers> </configuration>
and a singleton class to load my configuration class,
package com.edw.mybatis.config; import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; /** * * @author edw */ public class MyBatisSqlSessionFactory { protected static final SqlSessionFactory FACTORY; static { try { Reader reader = Resources.getResourceAsReader("com/edw/mybatis/xml/Configuration.xml"); FACTORY = new SqlSessionFactoryBuilder().build(reader); } catch (Exception e){ throw new RuntimeException("Fatal Error. Cause: " + e, e); } } public static SqlSessionFactory getSqlSessionFactory() { return FACTORY; } }
and a simple servlet to call mybatis query
package com.edw.servlet; import com.edw.mybatis.bean.Contoh; import com.edw.mybatis.config.MyBatisSqlSessionFactory; import com.edw.mybatis.mapper.ContohMapper; import java.io.IOException; import java.io.PrintWriter; import java.util.List; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.ibatis.session.SqlSession; import org.apache.log4j.Logger; /** * * @author edw */ public class MainServlet extends HttpServlet { private Logger logger = Logger.getLogger(MainServlet.class); protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); PrintWriter out = response.getWriter(); try { SqlSession session = MyBatisSqlSessionFactory.getSqlSessionFactory().openSession(); ContohMapper contohMapper = session.getMapper(ContohMapper.class); List<Contoh> contohs = contohMapper.selectAll(); out.println("<html>"); out.println("<head>"); out.println("<title>DBCP Example MainServlet</title>"); out.println("</head>"); out.println("<body>"); for (Contoh contoh : contohs) { out.println(contoh.getNama()+" "+contoh.getAlamat()+"<br />"); logger.debug(contoh.getNama()+" "+contoh.getAlamat()); } out.println("</body>"); out.println("</html>"); }catch(Exception ex){ logger.error(ex.getMessage(), ex); out.println(ex.getMessage()); } finally { out.close(); } } @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } }
and dont forget to register your jndi and servlet to web.xml
<?xml version="1.0" encoding="UTF-8"?> <web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"> <!-- Session --> <session-config> <session-timeout> 30 </session-timeout> </session-config> <!-- Welcome file --> <welcome-file-list> <welcome-file>mainServlet</welcome-file> </welcome-file-list> <!-- Servlet --> <servlet> <servlet-name>mainServlet</servlet-name> <servlet-class>com.edw.servlet.MainServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>mainServlet</servlet-name> <url-pattern>/mainServlet</url-pattern> </servlet-mapping> <!-- JNDI --> <resource-ref> <description>DB Connection</description> <res-ref-name>jdbc/test</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> </web-app>
And also, dont forget to put your mysql-connector.jar to tomcat lib. And if you see this error, Cannot create JDBC driver of class '' for connect URL 'null'
, it means there’s something wrong with your JNDI configuration on context.xml.
This is my netbeans project screenshot
and this is what the result will look like
Have fun, cheers. (B)