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)
great!! very clearly… (Y) && +1
just begin study ~ very helpful for me 😀
Hi hankspan,
glad it can help 🙂
Hi edwin:
I modify this line when I running on WebSphere 6.1
value=”java:/comp/env/jdbc/test” -> Exception
value=”java:comp/env/jdbc/test” -> OK
for reference only 😉
hi hank,
yes it is different, each application server has its own way to call JNDI. Try calling JNDI on Glassfish or WebLogic, perhaps it will be different 🙂
Thank you Thank you Thank you Thank you
?????????
Hi Edwin
i have wrote code same as above (used eclipse IDE running on tomcat 9 and have JDK 8 installed) but i m getting error as “java.lang.IllegalArgumentException: Parameter ‘dataSource’ must not be null”. while building sqlSessionFactory in config.xml its throwing error. any help?
Hi Asha
looks like your datasource is null, perhaps due to unable to initializing it properly.