[스프링/전자정부] 전자정부 프레임워크 iBATIS tutorial

[출처, 현재 접근 불가]

http://toolsqa.com/ibatis/ibatis-tutorial/ 

Tools QA - Page Not Found

 

iBatis는 꽤 이전 프레임워크로, 내가 개발하던 2018년 대에도 MyBatis를 쓰는 추세였다.

오랜만에 문서를 정리하는데 이 문서의 출처 페이지도 사라졌다. 

그래도 일부 내용을 확인할 수 있기에 남겨둔다.

---

 

iBATIS Introduction

---

iBATIS is a persistence framework which automates the mapping between SQL database objects (e.g. tables) and objects in Java (e.g. user defined class or collection object). This mapping is created and maintained using xml configuration files. These configuration files contain various SQL statements and other framework related options. Simplicity is the biggest advantage of the iBATIS Data Mapper over object relational mapping tools. To use iBATIS Data Mapper you rely on your own objects, XML, and SQL. With iBATIS Data Mapper you have the full power of both SQL and stored procedures at your fingertips.

iBATIS is a simple but complete framework that makes it easy for you to map your objects to your SQL statements or stored procedures. The goal of the iBATIS framework is to obtain 80% of data access functionality using only 20% of the code.

 

What is iBATIS?

---

A JDBC Framework

• Developers write SQL, iBATIS executes it using JDBC.
• No more try/catch/finally/try/catch.

 

A SQL Mapper

• Automatically maps object properties to prepared statement parameters.
• Automatically maps result sets to objects.
• Support for getting rid of N+1 queries.

 

Why iBATIS Data Mapper is used?

---

Developers often create maps between objects within an application. One definition of a Mapper is an “object that sets up communication between two independent objects.”

A Data Mapper is a “layer of mappers that moves data between objects and a database while keeping them independent of each other and the mapper itself“.

You provide the database and the objects; iBATIS provides the mapping layer that goes between the two.

 

Advantages of iBATIS

---

• Supports stored procedures − iBATIS encapsulates SQL in the form of stored procedures so that business logic is kept out of the database, and the application is easier to deploy and test, and is more portable.
• Supports inline SQL − No precompiler is needed, and you have full access to all of the features of SQL.
• Supports dynamic SQL − iBATIS provides features for dynamically building SQL queries based on parameters.
• Supports O/RM − iBATIS supports many of the same features as an O/RM tool, such as lazy loading, join fetching, caching, runtime code generation, and inheritance.

 

iBatis Tutorial

---

In this post we will learn how to use iBatis framework in automation project. The major advantage of this framework is we can execute complex SQL statements easily without writing much of code. The execution is very reliable and is error free unless we do not make unnecessary logical mistakes.

Project Download (현재 접근 불가)

 

Step 1 Create a Maven Project or a Java Project using Eclipse.

For creating a Maven Project, please click here. (현재 접근 불가)

For creating a Java Project, please click here. (현재 접근 불가)

 

Step 2. Add external dependencies in to Maven project

In order to use iBatis we need to use ‘ibatis-sqlmap.jar’ or below maven dependency

<dependency>
	<groupid>org.apache.ibatis</groupid>
	<artifactid>ibatis-sqlmap</artifactid>
	<version>2.3.4.726</version>
</dependency>

 

For interacting with database we need to add ‘mysql-connector-java.jar’ or below maven dependency

<dependency>
	<groupid>mysql</groupid>
	<artifactid>mysql-connector-java</artifactid>
	<version>5.1.9</version>
</dependency>

 

To read configuration files we need ‘commons-configuration.jar’ file or below maven dependency

<dependency>
	<groupId>commons-configuration</groupId>
	<artifactId>commons-configuration</artifactId>
	<version>1.10</version>
</dependency>

 

Step 3. Set up a Local DataBase

We need a local/remote database server in case we don’t have one we can install mysql as mentioned in below steps and create a database with a table to query

1. Install mysql server using below url http://dev.mysql.com/downloads/installer/
2. Set environment variables for installed mysql so that it can be executed from any directory
   1. PATH=C:\Program Files\MySQL\MySQL Server 5.5\bin;
3. Open cmd and type mysql and it will start mysql prompt to execute sql queries
4. We will change the default password for mysql root user by using below query: SET PASSWORD for ‘root’@’localhost’ = PASSWORD(‘password’);
5. We will create a new database, create a table and insert data into table for our testing

 

CREATE TABLE employee ( emp_id int(11) DEFAULT NULL, emp_name varchar(100) DEFAULT NULL, emp_salary int(11) DEFAULT NULL )
insert into employee (emp_id,emp_name,emp_salary) values(1,’olx’,100);
insert into employee (emp_id,emp_name,emp_salary) values(2,’uber’,300);
insert into employee (emp_id,emp_name,emp_salary) values(3,’safari’,300);

 

Step 4: Set Up Project

Since the Maven project is already created as mentioned in step1, with structure

• src/main/java – Any base class we need to add
• src/test/java – Any test case class we need to add

so we will add one more directory structure to handle resources file.

• src/main/resources/META-INF/

 

1) We will add an XML configuration file with name ‘SqlMapConfig.xml’ on above mentioned path with the following content. This is the file where we need to provide all configurations required for iBatis like

• jdbc mysql connector class : com.mysql.jdbc.Driver
• mysql data base ip/hostname and portnumber with name of default database to connect with: jdbc:mysql://127.0.0.1:3306/jmeter.
• Note : We used localhost ip here as we are querying locally installed mysql database and by default it runs on port number 3306
• mysql database server username and password
• Path of resource xml file where anyone can write independent queries for execution

 

The official documentation of file ‘SQLMapConfig.xml’ can be found at below url

https://ibatis.apache.org/docs/java/dev/com/ibatis/sqlmap/client/sql-map-config.txt

 

2) We will add a properties file “ibatis.properties” on above mentioned path which will contain data in key value pairs. This file will supply data (as mentioned in points under 4.1) to SqlMapConfig.xml

 

3) We will add ‘Resource XML‘(Common.xml). This is a file where we can write SQL queries to execute. For testing purpose I added 4 different statements which we come across while performing backend verification e.g. ‘select’, ‘insert’, ‘update’ and ‘delete’ statements

The official documentation of this file can be found at below URL

https://ibatis.apache.org/docs/java/dev/com/ibatis/sqlmap/client/sql-map.txt

 

Let’s quickly see the code we create a test case class in src/test/java folder

1. Create static variable to hold SQL Map config and Properties file

2. Create static object of SqlMapClient class

3. Before test begins we configuration ibatis by preparing sqlMapClient

• Read db params from ibatis.properties file
• Create a properties list to pass dynamic db params read from ibatis.properties file
• Creating a reader instance to read sqlMapConfig.xml file
• Building sqlMapClient to execute queries by passing data prepared in step 3.2 and 3.3

 

Step 5: Set Up & Executing Test

 

1) Select Statement Test : Create first test to execute select query by calling ‘queryForList’ method.

This method accepts 2 arguments i.e.

• Name of mapped SQL statement in Common.xml i.e. common.getEmpDetails<FileName.select statement id value in Common.xml>
• Map Object containing dynamic arguments to be passed in where clause of select query. If we don’t want to pass any argument we can pass null
• Store select SQL output in List<Map<String,Object>>
• Traverse the Map using for loop and get the data present for key

Execution results of ‘Select’ statement

 

2) Insert Statement Test : Create second test to execute insert query by calling ‘insert’ method.

This method accepts 2 arguments i.e.

• Name of mapped SQL statement in Common.xml i.e. common.addEmpDetails<FileName.insert statement id value in Common.xml>
• Map Object containing values to be inserted into table
• This Function returns the primary key of the newly inserted row, but we are not storing it.

 

3) Update Statement Test : Create third test to execute update query by calling ‘update method.

This method accepts 2 arguments i.e.

• Name of mapped SQL statement in Common.xml i.e. common.updateEmpDetails<FileName.update statement id value in Common.xml>
• Map Object containing dynamic arguments to be passed in where clause of update query. If we don’t want to pass any argument we can pass null.
• This Function returns the number of rows affected after update query is executed, but we are not storing it.

Execution results of ‘Update’ statement

 

4) Delete Statement Test : Create fouth test to execute delete query by calling ‘delete method.

This method accepts 2 arguments i.e.

• Name of mapped SQL statement in Common.xml i.e. common.deleteEmpDetails<FileName.delete statement id value in Common.xml>
• Map Object containing dynamic arguments to be passed in where clause of delete query. If we don’t want to pass any argument we can pass null.
• This Function returns the number of rows affected after delete query is executed, but we are not storing it.

Execution results of ‘Delete’ statement

 

package com.ibatis.demo;
 import java.io.FileNotFoundException;
 import java.io.IOException;
 import java.io.Reader;
 import java.sql.SQLException;
 import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.Properties;
 import org.apache.commons.configuration.Configuration;
 import org.apache.commons.configuration.PropertiesConfiguration;
 import org.testng.annotations.BeforeTest;
 import org.testng.annotations.Test;
 import com.ibatis.common.resources.Resources;
 import com.ibatis.sqlmap.client.SqlMapClient;
 import com.ibatis.sqlmap.client.SqlMapClientBuilder;
public class TestiBatisMain {

 // 1. Create variables to hold location of SQLMapConfig file and its properties file which will supply data to it
 public static final String SQL_PROPERTY_LOCATION = "META-INF/ibatis.properties";
 public static final String SQL_CONFIG_LOCATION =   "META-INF/SqlMapConfig.xml";

 // 2. The SqlMapClient is the central class for working with SQL Maps.
 // This class will allow us to run mapped statements (select, insert, update, delete etc.),
 // Once we have an SqlMapClient instance, everything we need to work with SQL Maps is easily available.
 public static SqlMapClient sqlMapClient;

 @BeforeTest
 public static void configureiBatis() throws FileNotFoundException, IOException {
 try {
 // 3.1 Create reference variable of Configuration interface and assign implementing class 'PropertiesConfiguration' object to it
 // Using this reference variable we can read 'ibatis.properties' file value
 Configuration sqlConfig = new PropertiesConfiguration(SQL_PROPERTY_LOCATION);

 // 3.2 Properties to be used to provide values to dynamic property tokens present in the sql-map-config.xml configuration file.
 Properties databaseProperty = new Properties();
 databaseProperty.put("driver", sqlConfig.getString("driver").trim());
 databaseProperty.put("jdbcURL", sqlConfig.getString("jdbcURL").trim());
 databaseProperty.put("username", sqlConfig.getString("username").trim());
 databaseProperty.put("password", sqlConfig.getString("password").trim());

 System.out.println( "Creating Oracle Database connection using"
 + "\n username :: " + databaseProperty.getProperty("username")
 + "\n password :: " + databaseProperty.getProperty("password")
 + "\n jdbcURL  :: " + databaseProperty.getProperty("jdbcURL")
 + "\n driver   :: " + databaseProperty.getProperty("driver"));

 // 3.3 Creating a Reader instance that reads an sql-map-config.xml file
 Reader reader = Resources.getResourceAsReader(SQL_CONFIG_LOCATION);

 // The SqlMapClientBuilder class is responsible for parsing configuration documents and building the SqlMapClient instance.
 // Its current implementation works with XML configuration files
 // Building an SqlMapClient using the specified reader and properties file.
 sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader, databaseProperty);
 } catch (Exception e) {
 e.printStackTrace();
 }
 }

 @Test(enabled=false)
 public static void testInsertQueryUsingiBatis() throws FileNotFoundException, IOException, SQLException {

 System.out.println( " testInsertQueryUsingiBatis executed");
 Map<String, Object> searchparams = new HashMap<String, Object>();
 searchparams.put("emp_id",4);
 searchparams.put("emp_name","SpiceCabs");
 searchparams.put("emp_salary",999);
 if (sqlMapClient != null) {
 try {
 sqlMapClient.insert("common.addEmpDetails",searchparams);
 System.out.println("|Record Inserted Successfully|");
 }
 catch (SQLException e) {
 e.printStackTrace();
 }
 }
 } 

 @Test(enabled=false)
 public static void testUpdateQueryUsingiBatis() throws FileNotFoundException, IOException, SQLException {

 System.out.println( " testUpdateQueryUsingiBatis executed");
 Map<String, Object> searchparams = new HashMap<String, Object>();
 searchparams.put("id",4);
 searchparams.put("salary",777);
 if (sqlMapClient != null) {
 try {
 sqlMapClient.update("common.updateEmpDetails", searchparams);
 System.out.println("|Record updated Successfully|");
 }
 catch (SQLException e) {
 e.printStackTrace();
 }
 }
 }

 @Test(enabled=true)
 public static void testDeleteQueryUsingiBatis() throws FileNotFoundException, IOException, SQLException {

 System.out.println( " testDeleteQueryUsingiBatis executed");
 Map<String, Object> searchparams = new HashMap<String, Object>();
 searchparams.put("id",1);
 if (sqlMapClient != null) {
 try {
 sqlMapClient.delete("common.deleteEmpDetails", searchparams);
 System.out.println("|Record deleted Successfully|");
 }
 catch (SQLException e) {
 e.printStackTrace();
 }
 }
 }
}

 

 

---