iBatis (currently known as MyBatis) is another popular persistence framework (like hibernate) which automates the mapping between SQL database objects (e.g. tables) and objects in Java, .NET, and Ruby on Rails. This mapping is created and maintained using xml configuration files. These configuration files contain various SQL statements and other framework related options.
The main benefits of iBatis over other persistence frameworks is that it provides a greater flexibility and control over SQL statements writing. It means that any database expert can write these optimized statements and java developer just need to merge it is xml files.
In iBatis tutorial, We will learn to develop our first hello world application using iBatis. I will suggest you to follow below steps yourself but if you are in hurry, directly download the source code.
1. Create maven application in eclipse
To do this, open command prompt and navigate to workspace. Run below command:
mvn archetype:generate -DgroupId=com.howtodoinjava.ibatis.demo -DartifactId=ibatisHelloWorld -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false
This command will create a default maven project in your workspace. Now convert it to eclipse project.
mvn eclipse:eclipse
Import the project in eclipse.
2. iBatis maven dependencies
Below dependencies are required for adding iBatis to project.
<dependency> <groupid>org.apache.ibatis</groupid> <artifactid>ibatis-sqlmap</artifactid> <version>2.3.4.726</version> </dependency>
I am using MySQL as database. So adding database connecting driver support.
<dependency> <groupid>mysql</groupid> <artifactid>mysql-connector-java</artifactid> <version>5.1.9</version> </dependency>
For logging purpose, add log4j support.
<dependency> <groupid>log4j</groupid> <artifactid>log4j</artifactid> <version>1.2.17</version> </dependency>
3. Create Database
Create new database demoDB in mysql and create a new table USERINFO
. We will use this table for data persistence.
CREATE TABLE USERINFO
(
ID INT,
NAME VARCHAR(100),
EMAIL VARCHAR(50),
PASSWORD VARCHAR(16),
STATUS INT
);
4. Create model class
Model class which will map to created table in database. This class is essentially a POJO and it will have exactly one field for corresponding column in database table.
package com.howtodoinjava.ibatis.demo.dto; public class UserTEO { private Integer id; private String name; private String email; private String password; private int status; public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } 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; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public int getStatus() { return status; } public void setStatus(int status) { this.status = status; } }
5. DAO – SqlMapClient
This will be implemented using an interface UserDao
and implementation class UserDaoIbatis
. It can be different names based on your choice.
package com.howtodoinjava.ibatis.demo.dao; import com.howtodoinjava.ibatis.demo.dto.UserTEO; import com.ibatis.sqlmap.client.SqlMapClient; public class UserDaoIbatis implements UserDao { @Override public UserTEO addUser(UserTEO user, SqlMapClient sqlmapClient) { try { Integer id = (Integer)sqlmapClient.queryForObject("user.getMaxId"); id = id == null ? 1 : id + 1; user.setId(id); user.setStatus(1); sqlmapClient.insert("user.addUser", user); user = getUserById(id, sqlmapClient); return user; } catch(Exception e) { e.printStackTrace(); } return null; } @Override public UserTEO getUserById(Integer id, SqlMapClient sqlmapClient) { try { UserTEO user = (UserTEO)sqlmapClient.queryForObject("user.getUserById", id); return user; } catch(Exception e) { e.printStackTrace(); } return null; } @Override public void deleteUserById(Integer id, SqlMapClient sqlmapClient) { try { sqlmapClient.delete("user.deleteUserById", id); } catch(Exception e) { e.printStackTrace(); } } }
6. Write sqlmaps and sqlmap config
This is the main part. In sqlmap config, we need to give config settings like databse connection properties and path to sqlmaps where we write real sql queries.
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMapConfig PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-config-2.dtd"> <sqlMapConfig> <settings useStatementNamespaces="true"/> <transactionManager type="JDBC"> <dataSource type="SIMPLE"> <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/> <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost:3306/demoDB"/> <property name="JDBC.Username" value="root"/> <property name="JDBC.Password" value="lg225295"/> </dataSource> </transactionManager> <sqlMap resource="user.xml"/> </sqlMapConfig>
Lets write sqlmap with queries in it and map the result to UserTEO.
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap namespace="user"> <typeAlias alias="USER" type="com.howtodoinjava.ibatis.demo.dto.UserTEO" /> <resultMap id="userResultMap" class="USER"> <result property="id" column="ID" /> <result property="name" column="NAME" /> <result property="email" column="EMAIL" /> <result property="password" column="PASSWORD" /> <result property="status" column="STATUS" /> </resultMap> <select id="getUserById" parameterClass="java.lang.Integer" resultMap="userResultMap"> SELECT * FROM USERINFO WHERE ID = #value# </select> <select id="getMaxId" resultClass="java.lang.Integer"> SELECT MAX(ID) FROM USERINFO </select> <insert id="addUser" parameterClass="USER"> INSERT INTO USERINFO (ID,NAME,EMAIL,PASSWORD,STATUS) VALUES(#id#,#name#,#email#,#password#,#status#); </insert> <delete id="deleteUserById" parameterClass="java.lang.Integer"> DELETE FROM USERINFO WHERE ID = #value# </delete> </sqlMap>
7. iBatis Demo
To test iBatis hello world example, we need to create sqlmapClient which will act as EntityManager
in JPA. It will connect to database and execute the required queries and update the result back to data mappers.
SQLMapClient
can be constructed in different ways in different frameworks, but as this is ibatis example application so I am creating it using code.
package com.howtodoinjava.ibatis.demo; import java.io.Reader; import com.howtodoinjava.ibatis.demo.dao.UserDao; import com.howtodoinjava.ibatis.demo.dao.UserDaoIbatis; import com.howtodoinjava.ibatis.demo.dto.UserTEO; import com.ibatis.common.resources.Resources; import com.ibatis.sqlmap.client.SqlMapClient; import com.ibatis.sqlmap.client.SqlMapClientBuilder; public class TestMain { public static void main(String[] args) throws Exception { //Initialize dao UserDao manager = new UserDaoIbatis(); //Create the SQLMapClient Reader reader = Resources.getResourceAsReader("sql-maps-config.xml"); SqlMapClient sqlmapClient = SqlMapClientBuilder.buildSqlMapClient (reader); //Create a new user to persist UserTEO user = new UserTEO(); user.setId(1); user.setName("Demo User"); user.setPassword("password"); user.setEmail("demo-user@howtodoinjava.com"); user.setStatus(1); //Add the user manager.addUser(user,sqlmapClient); //Fetch the user detail UserTEO createdUser = manager.getUserById(1, sqlmapClient); System.out.println(createdUser.getEmail()); //Lets delete the user manager.deleteUserById(1, sqlmapClient); } }
Running above main method will execute the sql command and this can be verified using logs generated.
DEBUG [main] - Created connection 12589126. DEBUG [main] - {conn-100000} Connection DEBUG [main] - {conn-100000} Preparing Statement: SELECT MAX(ID) FROM USERINFO DEBUG [main] - {pstm-100001} Executing Statement: SELECT MAX(ID) FROM USERINFO DEBUG [main] - {pstm-100001} Parameters: [] DEBUG [main] - {pstm-100001} Types: [] DEBUG [main] - {rset-100002} ResultSet DEBUG [main] - {rset-100002} Header: [MAX(ID)] DEBUG [main] - {rset-100002} Result: [null] DEBUG [main] - Returned connection 12589126 to pool. DEBUG [main] - Checked out connection 12589126 from pool. DEBUG [main] - {conn-100003} Connection DEBUG [main] - {conn-100003} Preparing Statement: INSERT INTO USERINFO (ID,NAME,EMAIL,PASSWORD,STATUS) VALUES(?,?,?,?,?); DEBUG [main] - {pstm-100004} Executing Statement: INSERT INTO USERINFO (ID,NAME,EMAIL,PASSWORD,STATUS) VALUES(?,?,?,?,?); DEBUG [main] - {pstm-100004} Parameters: [1, Demo User, demo-user@howtodoinjava.com, password, 1] DEBUG [main] - {pstm-100004} Types: DEBUG [main] - Returned connection 12589126 to pool. DEBUG [main] - Checked out connection 12589126 from pool. DEBUG [main] - {conn-100005} Connection DEBUG [main] - {conn-100005} Preparing Statement: SELECT * FROM USERINFO WHERE ID = ? DEBUG [main] - {pstm-100006} Executing Statement: SELECT * FROM USERINFO WHERE ID = ? DEBUG [main] - {pstm-100006} Parameters: [1] DEBUG [main] - {pstm-100006} Types: DEBUG [main] - {rset-100007} ResultSet DEBUG [main] - {rset-100007} Header: [ID, NAME, EMAIL, PASSWORD, STATUS] DEBUG [main] - {rset-100007} Result: [1, Demo User, demo-user@howtodoinjava.com, password, 1] DEBUG [main] - Returned connection 12589126 to pool. DEBUG [main] - Checked out connection 12589126 from pool. DEBUG [main] - {conn-100008} Connection DEBUG [main] - {conn-100008} Preparing Statement: SELECT * FROM USERINFO WHERE ID = ? DEBUG [main] - {pstm-100009} Executing Statement: SELECT * FROM USERINFO WHERE ID = ? DEBUG [main] - {pstm-100009} Parameters: [1] DEBUG [main] - {pstm-100009} Types: DEBUG [main] - {rset-100010} ResultSet DEBUG [main] - {rset-100010} Header: [ID, NAME, EMAIL, PASSWORD, STATUS] DEBUG [main] - {rset-100010} Result: [1, Demo User, demo-user@howtodoinjava.com, password, 1] DEBUG [main] - Returned connection 12589126 to pool. demo-user@howtodoinjava.com DEBUG [main] - Checked out connection 12589126 from pool. DEBUG [main] - {conn-100011} Connection DEBUG [main] - {conn-100011} Preparing Statement: DELETE FROM USERINFO WHERE ID = ? DEBUG [main] - {pstm-100012} Executing Statement: DELETE FROM USERINFO WHERE ID = ? DEBUG [main] - {pstm-100012} Parameters: [1] DEBUG [main] - {pstm-100012} Types: DEBUG [main] - Returned connection 12589126 to pool.
Happy Learning !!
INSERT INTO USERINFO (ID,NAME,EMAIL,PASSWORD,STATUS)
VALUES(#id#,#name#,#email#,#password#,#status#);
we dont need ‘;’ at the end of this query.
hi , can you tell if that necessary to creat database or mybatis can do it self if i import project
You need to create the db.
Quick starter. Very helpfull
Hi Lokesh
I have a problem where I can see in the logs that its preparing the insert statement but not executing the insert which is throwing an error .
Can you please provide insight as to why this could be happening?
Thanks in advance for your help
Can you give me the error logs here. That will be helpful.
[APP] [2014-08-01 15:40:31.486] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] {conn-100328} Connection
[APP] [2014-08-01 15:40:31.487] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] {conn-100328} Preparing Statement: insert into table1 ( userName, userId, userAddress, userPhone ) values ( ?,?,?,? )
[APP] [2014-08-01 15:40:31.487] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1c8b69b] for key [org.apache.commons.dbcp.BasicDataSource@737611] bound to thread [http-8080-Processor22]
[APP] [2014-08-01 15:40:31.590] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1c8b69b] for key [org.apache.commons.dbcp.BasicDataSource@737611] bound to thread [http-8080-Processor22]
[APP] [2014-08-01 15:40:31.620] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [DEBUG] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@1c8b69b] for key [org.apache.commons.dbcp.BasicDataSource@737611] bound to thread [http-8080-Processor22]
[APP] [2014-08-01 15:40:31.621] DSC-ID[NULL_ID] SID[F14BACB9028391BC21969A209903E960] RID[096860] [INFO ] Dao Ended: [userDaoImpl#createuser] Execution Time=[179ms]
I am not seeing any exception/error here.
Hi Lokesh, Could you please provide some idea on how to do transaction management using multiple database (SQL Server & DB2) in iBatis with Spring. I have implemented tx in multiple databases using iBatis with Struts 1 (or plain Java) in Hewitt. No idea how to do when integrating with Spring.
If you use annotation @Transactional at service layer, does it not working?
I did a quick search fr this topic and found two threads which can help you:
https://stackoverflow.com/questions/7085271/how-to-set-up-transaction-with-mybatis-and-spring
https://coderanch.com/t/573774/oa/create-multiple-database-connection-Spring
Tks, very usefull, but one question, why when i see the table in the database can see the changes? it always told me that is empty. I can see the same logs that you show …
because
"manager.deleteUserById(1, sqlmapClient);"
is deleting the record. Comment this line.Thanks for posting this wonder full blog. This very use full and many thanks again !!