基于注解实现增删查改和多参数列表查询

和通数据库htsjk.Com2019-03-13 21:01 来源:未知阅读:9706 评论 314 热度2

标签：参数注解查改

基于注解实现增删查改和多参数列表查询

在【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);
         }

结果：

本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。

返回首页

评论暂时关闭