程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> 基於注解實現增刪查改和多參數列表查詢

基於注解實現增刪查改和多參數列表查詢

編輯：DB2教程

基於注解實現增刪查改和多參數列表查詢

在【Mybatis】Mybatis入門概述及第一個Mybatis實例實現增刪改查和【Mybatis】Mybatis接口編程方式實現增刪改查演示了如何使用XML來操作Mybatis實現CRUD，但是大量的XML配置文件的編寫是非常煩人的。因此Mybatis也提供了基於注解的配置方式，下面我們來演示一下使用接口加注解來實現CRUD的的例子。

一、創建數據庫、數據表

use test;  
create table t_employeer(  
employeer_id int not null  primary key AUTO_INCREMENT ,  
employeer_name varchar(50) default null,  
employeer_age int default null,  
employeer_department varchar(100) default null,  
employeer_worktype varchar(100) default null  
)

二、創建工程

整個工程目錄如下：

記得要導入相應的包！

1、雇員對應的類Employeer.java

package com.mucfc.model;
/**
 * 雇員信息類
 *@author linbingwen
 *@time 2015.5.11
 */
public class Employeer {
	private Integer employeer_id;
	private String employeer_name;
	private Integer employeer_age ;
	private String employeer_department;
	private String employeer_worktype;
	
	public Employeer() {
		super();
	}
	public Integer getEmployeer_id() {
		return employeer_id;
	}
	public void setEmployeer_id(Integer employeer_id) {
		this.employeer_id = employeer_id;
	}
	public String getEmployeer_name() {
		return employeer_name;
	}
	public void setEmployeer_name(String employeer_name) {
		this.employeer_name = employeer_name;
	}
	public Integer getEmployeer_age() {
		return employeer_age;
	}
	public void setEmployeer_age(Integer employeer_age) {
		this.employeer_age = employeer_age;
	}
	public String getEmployeer_department() {
		return employeer_department;
	}
	public void setEmployeer_department(String employeer_department) {
		this.employeer_department = employeer_department;
	}
	public String getEmployeer_worktype() {
		return employeer_worktype;
	}
	public void setEmployeer_worktype(String employeer_worktype) {
		this.employeer_worktype = employeer_worktype;
	}
	@Override
	public String toString() {
		return "Employeer [employeer_id=" + employeer_id + ", employeer_name="
				+ employeer_name + ", employeer_age=" + employeer_age
				+ ", employeer_department=" + employeer_department
				+ ", employeer_worktype=" + employeer_worktype + "]";
	}
	


}

Employeer.xml文件，用來放置一些映射，刪除了Sql語句了

<?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.mucfc.dao.EmployeerMapper">  
   
 <!-- 定義數據庫字段與實體對象的映射關系 -->  
 <resultMap type="Employeer" id="employeerResultMap">  
    <id property="employeer_id" column="employeer_id"/>   
    <result property="employeer_name" column="employeer_name"/>  
    <result property="employeer_age" column="employeer_age"/>  
    <result property="employeer_department" column="employeer_department"/>  
    <result property="employeer_worktype" column="employeer_worktype"/>  
 </resultMap>  
      
 </mapper>

2、配置mybatis-config.xml

<?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>  
    <!-- 設置別名  -->  
    <typeAliases>  
        <typeAlias alias="Employeer" type="com.mucfc.model.Employeer"/>  
    </typeAliases>  
      
    <!-- 配置數據源相關的信息  -->  
    <environments default="development">  
        <environment id="development">  
            <transactionManager type="JDBC" />  
            <dataSource type="POOLED">  
            <property name="driver" value="com.mysql.jdbc.Driver"/>  
             <property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8"/>   
            <property name="username" value="root"/>   
            <property name="password" value="christmas258@"/>    
            </dataSource>  
        </environment>  
    </environments>  
      
    <!-- 列出映射文件 -->  
    <mappers>  
        <mapper resource="com/mucfc/model/Employeer.xml" />  
    </mappers>  
</configuration>

3、EmployeerMapper.java在添加Sql映射語句，使用注解的方式來實現

package com.mucfc.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.Update;

import com.mucfc.model.Employeer;

/**
 * SQL語句映射類
 * @author linbingwen
 * @time 2015.5.11
 */
public interface EmployeerMapper {
    @Select("select * from `t_employeer` where employeer_name like #{employeer_name}")
	@ResultMap("employeerResultMap")
	public List findEmployeerByName(String employeer_name);    
    
    @Select("select* from `t_employeer` where employeer_name=#{0} and employeer_department=#{1}")
	@ResultMap("employeerResultMap")
    
	public List findEmployeerByNameandDep(String employeer_name,String employeer_department); 
	@ResultMap("employeerResultMap")
	@Select("select* from `t_employeer` where employeer_name=#{key1} and employeer_department=#{key2}")
	public List findEmployeerByNameandDep1(Map map);
	
	

	@Select("select* from `t_employeer` where employeer_id =#{id}")
	@ResultMap("employeerResultMap")
	public Employeer findEmployeerByID(int id);

     @Insert(" insert into `t_employeer`(employeer_name,employeer_age,employeer_department,employeer_worktype) values(#{employeer_name},#{employeer_age},#{employeer_department},#{employeer_worktype})")
	public void addEmployeer(Employeer employeer);

    @Delete("delete from `t_employeer` where employeer_id = #{employeer_id}")
	public void deleteEmployeer(int id);

    @Update(" update t_employeer set employeer_name = #{employeer_name},employeer_age= #{employeer_age},employeer_department = #{employeer_department} ,employeer_worktype=#{employeer_worktype}  where employeer_id = #{employeer_id}  ")
	public void updateEmployeer(Employeer employeer);

}

4、測試使用

（1）單參數查找

	/**
	 * 查找
	 */
	public static Employeer findEmployeerById(int id) {
		SqlSession session = null;
		Employeer employeer=null;
		try {
			session = sqlSessionFactory.openSession();
			EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class);
			employeer=employeerMapper.findEmployeerByID(id);
		} finally {
			session.close();
		}
		return employeer;
	}

對應SQL語句：

	@Select("select* from `t_employeer` where employeer_id =#{id}")
	@ResultMap("employeerResultMap")
	public Employeer findEmployeerByID(int id);

調用：

	  System.out.println(findEmployeerById(10));
	  System.out.println(findEmployeerById(11));

結果：

（2）添加

	/**
	 * 增加
	 */
	public static void addEmployeer(Employeer employeer){
		SqlSession session = null;
		try {
			session = sqlSessionFactory.openSession();
			EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class);
			employeerMapper.addEmployeer(employeer);
            session.commit() ;  		
		} finally {
			session.close();
		}
		
	}

對應SQL語句：

     @Insert(" insert into `t_employeer`(employeer_name,employeer_age,employeer_department,employeer_worktype) values(#{employeer_name},#{employeer_age},#{employeer_department},#{employeer_worktype})")
	public void addEmployeer(Employeer employeer);

（3）更改

	/**
	 * 更改
	 */
	public static void updateEmployeer(Employeer employeer){
		SqlSession session = null;
		try {
			session = sqlSessionFactory.openSession(); 
			EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class);
			employeerMapper.updateEmployeer(employeer);
            session.commit() ;  		
		} finally {
			session.close();
		}
		
	}

對應SQL語句：

    @Update(" update t_employeer set employeer_name = #{employeer_name},employeer_age= #{employeer_age},employeer_department = #{employeer_department} ,employeer_worktype=#{employeer_worktype}  where employeer_id = #{employeer_id}  ")
	public void updateEmployeer(Employeer employeer);

（4）刪除

	/**
	 * 刪除
	 * 
	 */
	public static void deleteEmployeer(int id){
		SqlSession session = null;
		try {
			session = sqlSessionFactory.openSession();
			EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class);
			employeerMapper.deleteEmployeer(id);
            session.commit() ;  		
		} finally {
			session.close();
		}
	}

對應SQL語句：

    @Delete("delete from `t_employeer` where employeer_id = #{employeer_id}")
	public void deleteEmployeer(int id);

（5）列表查詢

/**
	 * 單參數查詢列表
	 */
	public static List getEmployeerList(String employeer_name){
		SqlSession session = null;
		 List employeers=null;
		try {
			session = sqlSessionFactory.openSession(); 
			EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class);
			 employeers = employeerMapper.findEmployeerByName(employeer_name);	
            session.commit() ;  		
		} finally {
			session.close();
		}
		return employeers;
   
    }
	/**
	 * 多參數查詢列表
	 */
	public static List getEmployeerList(String employeer_name,String employeer_department){
		SqlSession session = null;
		 List employeers=null;
		try {
			session = sqlSessionFactory.openSession(); 
			EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class);
			 employeers = employeerMapper.findEmployeerByNameandDep(employeer_name, employeer_department);
            session.commit() ;  		
		} finally {
			session.close();
		}
		return employeers;
   
    }
	/**
	 * 多參數查詢列表，使用map
	 */
	public static List getEmployeerList(Map map){
		SqlSession session = null;
		 List employeers=null;
		try {
			session = sqlSessionFactory.openSession(); 
			EmployeerMapper employeerMapper=session.getMapper(EmployeerMapper.class);
			 employeers = employeerMapper.findEmployeerByNameandDep1(map);
            session.commit() ;  		
		} finally {
			session.close();
		}
		return employeers;
   
    }

對應的Sql語句

    @Select("select * from `t_employeer` where employeer_name like #{employeer_name}")
	@ResultMap("employeerResultMap")
	public List findEmployeerByName(String employeer_name);    
    
    @Select("select* from `t_employeer` where employeer_name=#{0} and employeer_department=#{1}")
	@ResultMap("employeerResultMap") 
	public List findEmployeerByNameandDep(String employeer_name,String employeer_department); 
	@ResultMap("employeerResultMap")
	@Select("select* from `t_employeer` where employeer_name=#{key1} and employeer_department=#{key2}")
	public List findEmployeerByNameandDep1(Map map);
	
	
	@Select("select* from `t_employeer` where employeer_id =#{id}")
	@ResultMap("employeerResultMap")
	public Employeer findEmployeerByID(int id);

測試：

	System.out.println("=========================使用單參數查詢===========================");
		List employeers=getEmployeerList("張三");
		 for(Employeer employeer:employeers){
             System.out.println(employeer);
         }
	 System.out.println("=========================使用多單參數查詢===========================");
		 List employeers1=getEmployeerList("張三","產品二部");
		 for(Employeer employeer1:employeers1){
             System.out.println(employeer1);
         }
		 System.out.println("=========================使用多單參數map方式查詢===========================");
		 Map map = new HashMap(); 
		 map.put("key1", "明明"); 
		 map.put("key2", "財會部");
		 List employeers2=getEmployeerList(map);
		 for(Employeer employeer2:employeers2){
             System.out.println(employeer2);
         }

結果：