最详细Ibats连接db2教程

写这篇博文的目的是为了加深自己对Ibatis框架的理解，如果诸多不足之处，请各位大佬多多包含

所用到的jar包：

db2jcc.jar
ibatis-2.3.4.726.jar

一、项目结构

a)    本实例只是实现使用ibatis框架从数据库操作数据，所以项目采用普通java项目

b)    项目结构：

二、数据库准备

c)    在对应数据库创建表

CREATE TABLE     employee     (         id INTEGER NOT NULL,--员工编号         name VARCHAR(10) NOT NULL,--员工姓名         post VARCHAR(50) NOT NULL,--职位         salary INTEGER,--薪资         qq VARCHAR(11),--qq         PRIMARY KEY (id)--主键     )

d)    添加部分测试数据

INSERT INTO EMPLOYEE VALUES
   (1,'ZhangShan','Java开发工程师',5200,'6521421'),
   (2,'李四','测试员',3600,'66558427'),
   (3,'王五','项目经理',8000,'3344220'),
   (4,'叶韵','UI工程师',4500,'35274256'),
   (5,'叶悠悠','Java开发工程师',5200,'336622014')

C）创建完成数据库表结构如下

 

三、各层的文件配置信息

1、根据表创建完善实体类Employee.java

     

package com.shangbo.domain; /**  * 员工表实体类  * @作者 XieGang  * @日期 2018-5-19  */ publicclass Employee {      private Integer id;      private String name;      private String post;      private Integer salary;      private String qq;      public Employee() {          // TODO Auto-generated constructor stub      }      public Employee(Integer id, String name, String post, Integer salary,               String qq) {          super();          this.id = id;          this. name = name;          this.post = post;          this.salary = salary;          this.qq = qq;      }      /**       * 获取： id       */      public Integer getId() {          returnid;      }      /**       * 设置： id       */      publicvoid setId(Integer id) {          this.id = id;      }      /**       * 获取：name       */      public String getNane() {          returnname;      }      /**       * 设置：name       */      publicvoid setNane(String name) {          this. name = name;      }      /**       * 获取： post       */      public String getPost() {          returnpost;      }      /**       * 设置： post       */      publicvoid setPost(String post) {          this.post = post;      }      /**       * 获取： salary       */      public Integer getSalary() {          returnsalary;      }      /**       * 设置： salary       */      publicvoid setSalary(Integer salary) {          this.salary = salary;      }      /**       * 获取： qq       */      public String getQq() {          returnqq;      }      /**       * 设置： qq       */      publicvoid setQq(String qq) {          this.qq = qq;      }      /* (non-Javadoc)       * @see java.lang.Object#toString()       */      @Override      public String toString() {          return"Employee [id=" + id + ", name=" + name + ", post=" + post                    + ", salary=" + salary + ", qq=" + qq + "]";      } }

 

2、编写dao层接口EmployeeDao.java

package com.shangbo.dao;   import java.util.List;   import com.shangbo.domain.Employee;   /**  * 员工dao层接口  * @作者 XieGang  * @日期 2018-5-19  */ publicinterface EmployeeDao {     /**      * <font size="4" color="#ff8000">〓      * 添加员工信息      * 〓</font><br/><font size="3" color="#8000ff">● <b>addEmployee():boolean--EmployeeDao</b></font><br/><font size="3" color="blue">      * ● TODO()</font><font color="#009966">      * @param employee      * @return      */     boolean addEmployee(Employee employee);     /**      * <font size="4" color="#ff8000">〓      * 删除员工信息      * 〓</font><br/><font size="3" color="#8000ff">● <b>delEmployeeById():boolean--EmployeeDao</b></font><br/><font size="3" color="blue">      * ● TODO()</font><font color="#009966">      * @param id      * @return      */     boolean delEmployeeById(Integer id);     /**      * <font size="4" color="#ff8000">〓      * 修改员工信息      * 〓</font><br/><font size="3" color="#8000ff">● <b>updateEmployee():boolean--EmployeeDao</b></font><br/><font size="3" color="blue">      * ● TODO()</font><font color="#009966">      * @param employee      * @return      */     boolean updateEmployee(Employee employee);     /**      * <font size="4" color="#ff8000">〓      * 查询员工信息      * 〓</font><br/><font size="3" color="#8000ff">● <b>queyrEmployeeById():Employee--EmployeeDao</b></font><br/><font size="3" color="blue">      * ● TODO()</font><font color="#009966">      * @param id      * @return      */     Employee queyrEmployeeById(Integer id);     /**      * <font size="4" color="#ff8000">〓      * 查询所有员工信息      * 〓</font><br/><font size="3" color="#8000ff">● <b>queryAllEmployee():List<Employee>--EmployeeDao</b></font><br/><font size="3" color="blue">      * ● TODO()</font><font color="#009966">      * @return      */     List<Employee> queryAllEmployee();     /**      * <font size="4" color="#ff8000">〓      * 统计员工总数      * 〓</font><br/><font size="3" color="#8000ff">● <b>countEmployee():Integer--EmployeeDao</b></font><br/><font size="3" color="blue">      * ● TODO()</font><font color="#009966">      * @return      */     Integer countEmployee(); }

3、编写实现类

package com.shangbo.dao.impl;   import java.sql.SQLException; import java.util.List;   import com.ibatis.sqlmap.client.SqlMapClient; import com.shangbo.dao.EmployeeDao; import com.shangbo.domain.Employee; /**  * 员工dao层实现类  * @作者 XieGang  * @日期 2018-5-19  */ publicclass EmployeeDaoImpl implements EmployeeDao {         private SqlMapClient sqlMapClient = null;     /**      * 初始化sqlMapClient      */     {         try {             // 读取配置文件             Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");             // 从工厂得到sqlMapClient             sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);             reader.close();         } catch (IOException e) {             e.printStackTrace();         }     }         @Override     publicboolean addEmployee(Employee employee) {         boolean flag =false;         try {             flag = (Boolean) sqlMapClient.insert("employeeDao.addEmployee", employee);         } catch (SQLException e) {             e.printStackTrace();         }           return flag;     }       @Override     publicboolean delEmployeeById(Integer id) {         boolean flag =false;         try {             flag = sqlMapClient.delete("employeeDao.delEmployeeById", id)>0;         } catch (SQLException e) {             e.printStackTrace();         }           return flag;     }       @Override     publicboolean updateEmployee(Employee employee) {         boolean flag =false;         try {             flag = sqlMapClient.update("employeeDao.updateEmployee", employee)>0;         } catch (SQLException e) {             e.printStackTrace();         }           return flag;     }       @Override     public Employee queyrEmployeeById(Integer id) {         Employee employee=null;         try {             employee = (Employee) sqlMapClient.queryForObject("employeeDao.queyrEmployeeById", id);         } catch (SQLException e) {             e.printStackTrace();         }         return employee;     }       @SuppressWarnings("unchecked")     @Override     public List<Employee> queryAllEmployee() {         List<Employee> list=null;         try {             list = sqlMapClient.queryForList("employeeDao.queryAllEmployee");         } catch (SQLException e) {             e.printStackTrace();         }         return list;     }       @Override     public Integer countEmployee() {         Integer count =null;         try {             count =  (Integer)sqlMapClient.queryForObject("employeeDao.countEmployee");         } catch (SQLException e) {             e.printStackTrace();         }         return count;     }   }

 

4、配置SqlMapConfig

<?xml version="1.0" encoding="UTF-8" ?>  <!DOCTYPE sqlMapConfig PUBLIC "-//iBATIS.com//DTD SQL Map Config 2.0//EN"  "http://www.ibatis.com/dtd/sql-map-config-2.dtd">  <sqlMapConfig>      <!-- 加载配置文件 -->     <properties resource="db2.properties" />          <!-- 使用事务管理 -->     <transactionManager type="JDBC">          <!-- 配置数据源 -->         <dataSource type="SIMPLE">              <property name="JDBC.Driver" value="${db2.driver}" />              <property name="JDBC.ConnectionURL" value="${db2.url}" />              <property name="JDBC.Username" value="${db2.username}" />              <property name="JDBC.Password" value="${db2.password}" />          </dataSource>      </transactionManager>          <!-- 加载对应的编写sql语句的xml文件 -->     <sqlMap resource="com/shangbo/dao/sql/EmployeeDao.xml" />    </sqlMapConfig>

5、配置db2.properties

db2.driver=com.ibm.db2.jcc.DB2Driver db2.url=jdbc:db2://localhost:50000/rbas db2.username=mydb2date db2.password=123456

说明：

Ø  驱动名是从连接工具包中找的，连接db2数据库不用修改，如果是其他数据库，根据对应的驱动包进行修改

Ø  url是访问路径：根据自己数据库的位置进行修改

Ø  用户名是使用dbvisual链接数据库的用户名，根据自己的进行修改

Ø  密码是根据自己设置的进行修改

6、配置EmployeeDao.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="employeeDao">     <!-- 给对应的实体类起别名，方便使用 -->     <typeAlias alias="employee" type="com.shangbo.domain.Employee" />       <!-- 添加员工信息 -->     <insert id="addEmployee" parameterClass="employee">        INSERT        INTO        EMPLOYEE VALUES (            #id#,            #name#,            #post#,            #salary#,            #qq#,        )     </insert>       <!-- 删除员工信息 -->     <delete id="delEmployeeById" parameterClass="integer">        DELETE FROM EMPLOYEE WHERE ID = id     </delete>       <!-- 动态更新员工信息 -->     <update id="updateEmployee" parameterClass="employee">        UPDATE        EMPLOYEE        <dynamic prepend="SET">            <isNotEmpty property="name" prepend=",">               NAME=#name#            </isNotEmpty>            <isNotEmpty property="post" prepend=",">               POST=#post#            </isNotEmpty>            <isNotEmpty property="salary" prepend=",">               SALARY=#salary#            </isNotEmpty>            <isNotEmpty property="qq" prepend=",">               QQ=#qq#            </isNotEmpty>        </dynamic>        WHERE        ID=#id#     </update>       <!-- 根据id查询员工信息 -->     <select id="queyrEmployeeById" parameterClass="integer"        resultClass="employee">        SELECT        id,        name,        post,        salary,        qq        FROM        EMPLOYEE        WHERE        id = #id#     </select>       <!-- 查询所有员工信息 -->     <select id="queryAllEmployee" resultClass="employee">        SELECT        id,        name,        post,        salary,        qq        FROM        EMPLOYEE     </select>         <!-- 统计员工信息 -->     <select id="countEmployee" resultClass="integer">        SELECT COUNT(ID) FROM EMPLOYEE     </select> </sqlMap>

四、测试

package com.shangbo.test; import java.util.List; import com.shangbo.dao.EmployeeDao; import com.shangbo.dao.impl.EmployeeDaoImpl; import com.shangbo.domain.Employee; /**  * Ibatis练习测试类  * @作者 XieGang  * @日期 2018-5-19  */ public class TestEmployee { private EmployeeDao employeeDao = new EmployeeDaoImpl(); public static void main(String[] args) { TestEmployee test = new TestEmployee(); //测试查询所有员工 // test.testQueryAll(); //测试按照id查询员工 // test.testQueryEmpById(5); //测试修改员工信息1 修改成功 // test.testUpdateEmp(new Employee(5, "李白","诗人", 12000, "520131455")); //测试修改员工信息2 报错：[jcc][10271][10295][3.57.82] 无法识别 JDBC 类型：0。 ERRORCODE=-4228, SQLSTATE=null // test.testUpdateEmp(new Employee(null, "李白","诗人", 12000, "520131455")); //测试修改员工信息3 动态修改成功 /* Employee e = new Employee(); e.setId(5); e.setPost("王者荣耀英雄"); test.testUpdateEmp(e);*/ //测试添加员工完整信息 如果主键重复，会报SQLERRMC=1;错误 // test.testaddEmp(new Employee(8, "貂蝉","法师", 12500, "4521012")); //测试删除员工信息 // test.testDelEmp(11); //测试统计员工总数 test.testCountEmp(); } /** * <font size="4" color="#ff8000">〓  * 测试查询所有员工信息 * 〓</font><br/><font size="3" color="#8000ff">● <b>testQueryAll():　void--TestEmployee</b></font><br/><font size="3" color="blue"> * ● TODO()</font><font color="#009966"> */ public void testQueryAll(){ List<Employee> employees = employeeDao.queryAllEmployee(); for (Employee employee : employees) { System.out.println(employee); } } /** * <font size="4" color="#ff8000">〓  * 测试根据id查询员工信息 * 〓</font><br/><font size="3" color="#8000ff">● <b>testQueryEmpById():　void--TestEmployee</b></font><br/><font size="3" color="blue"> * ● TODO()</font><font color="#009966"> * @param id 员工id */ public void testQueryEmpById(Integer id){ Employee employee = employeeDao.queyrEmployeeById(id); System.out.println(employee); } /** * <font size="4" color="#ff8000">〓  * 测试修改修改员工 * 〓</font><br/><font size="3" color="#8000ff">● <b>testUpdateEmp():　void--TestEmployee</b></font><br/><font size="3" color="blue"> * ● TODO()</font><font color="#009966"> * @param employee */ public void testUpdateEmp(Employee employee){ boolean flag = employeeDao.updateEmployee(employee); System.out.println("修改结果："+flag); } /** * <font size="4" color="#ff8000">〓  * 测试添加员工 * 〓</font><br/><font size="3" color="#8000ff">● <b>testaddEmp():　void--TestEmployee</b></font><br/><font size="3" color="blue"> * ● TODO()</font><font color="#009966"> * @param employee */ public void testAddEmp(Employee employee){ Object object = employeeDao.addEmployee(employee); System.out.println("添加结果："+object); } /** * <font size="4" color="#ff8000">〓  * 删除员工信息 * 〓</font><br/><font size="3" color="#8000ff">● <b>testDelEmp():　void--TestEmployee</b></font><br/><font size="3" color="blue"> * ● TODO()</font><font color="#009966"> * @param id */ public void testDelEmp(Integer id){ Object object = employeeDao.delEmployeeById(id); System.out.println("删除结果："+object); } /** * <font size="4" color="#ff8000">〓  * 统计员工总数 * 〓</font><br/><font size="3" color="#8000ff">● <b>testCountEmp():　void--TestEmployee</b></font><br/><font size="3" color="blue"> * ● TODO()</font><font color="#009966"> */ public void testCountEmp(){ Integer countEmployee = employeeDao.countEmployee(); System.out.println("员工总数为："+countEmployee); } }

 至此，一个完整的Ibatis连接连接db2数据库创建完成