ibatis学习之 增删改查

 本文只是简单介绍下通过inline parameter方式进行增删改查

使用到的sqlmap-config.xml请参考前面一篇ibatis学习之 执行sql脚本

sql映射文件:

Employee.xml文件里面内容

<?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="Employee">

 

<select id="getAll" resultClass="Employee">

SELECT * FROM EMPLOYEE

</select>

 

<update id="update" parameterClass="Employee">

UPDATE EMPLOYEE

SET first_name = #first_name#

WHERE id = #id#

</update>

 

<insert id="insertProduct-Mysql" parameterClass="Employee">

insert into EMPLOYEE(first_name,last_name,salary)

values(#first_name#,#last_name#,#salary#)

<selectKey resultClass="int" keyProperty="id">

SELECT LAST_INSERT_ID() AS id

</selectKey>

</insert>

 

<select id="getById" resultClass="Employee" parameterClass="java.util.Map">

    select * from EMPLOYEE where id = #id#

</select>

 

 

<delete id="deleteById" parameterClass="java.util.Map">

  delete from EMPLOYEE where id = #id#

</delete>

 

 

</sqlMap>

 

测试代码为：

 

 

 

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 org.junit.Assert;

import org.junit.Before;

import org.junit.Test;

 

import com.ibatis.common.resources.Resources;

import com.ibatis.sqlmap.client.SqlMapClient;

import com.ibatis.sqlmap.client.SqlMapClientBuilder;

 

 

public class IbatisUpdateTest {

private SqlMapClient smc;

 

@Before

public void init() {

Reader rd;

try {

rd = Resources.getResourceAsReader("SqlMapConfig.xml");

smc = SqlMapClientBuilder.buildSqlMapClient(rd);

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

 

}

 

@Test

public void testUpdate() {

/* This would update one record in Employee table. */

System.out.println("Going to update record.....");

Employee rec = new Employee();

rec.setId(2);

rec.setFirstName("jinbingchuan");

try {

smc.update("Employee.update", rec);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

System.out.println("Record updated Successfully ");

}

 

@Test

public void testGetAll() throws IOException, SQLException {

System.out.println("Going to read records.....");

List<Employee> ems = (List<Employee>) smc.queryForList("Employee.getAll", null);

for (Employee e : ems) {

System.out.print(" getId== " + e.getId());

System.out.print(" getFirstName== " + e.getFirstName());

System.out.print(" getLastName == " + e.getLastName());

System.out.print(" getSalary== " + e.getSalary());

System.out.println("");

}

System.out.println("Records Read Successfully ");

 

}

 

@Test

public void testInsert(){

Employee employee = new Employee();

employee.setFirstName("jin");

employee.setSalary(234);

employee.setlastName("king");

try {

smc.startTransaction();

smc.insert("Employee.insertProduct-Mysql", employee);

smc.commitTransaction();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

 

}

finally{

try {

smc.endTransaction();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

 

@Test

public void testGetById(){

try {

smc.startTransaction();

Map<String,Integer> parameterMap = new HashMap<String,Integer>();

parameterMap.put("id", 7);

Employee employee = (Employee)smc.queryForObject("Employee.getById", parameterMap);

smc.commitTransaction();

Assert.assertNotNull(employee);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally{

try {

smc.endTransaction();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

 

}

 

@Test

public void testDeleteById(){

try {

smc.startTransaction();

Map<String,Integer> parameterMap = new HashMap<String,Integer>();

parameterMap.put("id", 13);

int result = smc.delete("Employee.deleteById", parameterMap);

smc.commitTransaction();

Assert.assertEquals(1, result);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

finally{

try {

smc.endTransaction();

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

 

}

}