Spring之Mybatis的参数处理、分页实现、数据库字段不匹配以及与Spring的整合

Mybatis多个参数的处理

• 通过Map或者对象类型包装多个参数

<select id="findAccountByAccNoAndAccPassword"
    parameterType="com.xdl.bean.XdlBankAccount"
    resultType="com.xdl.bean.XdlBankAccount">
    select * from xdl_bank_account_30 where acc_no = #{param1,jdbcType=VARCHAR}
        and  acc_password = #{param2,jdbcType=VARCHAR}
</select>

<select id="findAccountByAccNoAndAccPassword2"
	parameterType="map"
    resultType="com.xdl.bean.XdlBankAccount">
    select * from xdl_bank_account_30 where acc_no = #{acc_no}
        and  acc_password = #{acc_password}
</select>

• 通过在sql语句中使用index对参数进行编号编号从0开始

<!--  根据账号 和 密码查询银行账户  -->
<select id="findAccountByAccNoAndAccPassword3"
    resultType="com.xdl.bean.XdlBankAccount">
    select * from xdl_bank_account_30 where acc_no = #{0}
        and  acc_password = #{1}
</select>

• 也可以使用param1等对参数进行编号编号从param1开始,@Param(“参数名”)设置在接口方法的参数上

<select id="findAccountByAccNoAndAccPassword4"
    resultType="com.xdl.bean.XdlBankAccount">
    select * from xdl_bank_account_30 where acc_no = #{param1,jdbcType=VARCHAR}
        and  acc_password = #{param2,jdbcType=VARCHAR}
</select>

• Dao接口的方法如下

XdlBankAccount findAccountByAccNoAndAccPassword3(String acc_no,String acc_password);

XdlBankAccount findAccountByAccNoAndAccPassword4(@Param("acc_no")String acc_no,
    @Param("acc_password")String acc_password);

分页的实现

• 按照acc_money排序，一页显示X条，显示第n页数据

<!--   按照acc_money 排序 一页显示 X 条  显示 第 n 页数据   -->
<select id="findAccountListByPageInfo"  resultType="com.xdl.bean.XdlBankAccount">
    select * from
      (select rownum r,t.*  from
        (select  * from xdl_bank_account_30  order by acc_money) t
        where  rownum &lt; #{pageSize}*#{pageNumber} + 1
    ) where r > ((#{pageNumber}-1)*#{pageSize})
</select>

List<XdlBankAccount>   findAccountListByPageInfo(
	@Param("pageSize")int   pageSize,
	@Param("pageNumber")int pageNumber);

   List<XdlBankAccount> datas = dao.findAccountListByPageInfo(2, 2);
System.out.println(datas);

• 使用分页插件 ---- 根据某个字段排序,查询表中的所有数据

  <!--在XdlBankAccountMapper.xml文件中输入-->
  <select id="findAll"   resultType="com.xdl.bean.XdlBankAccount" >
    select  * from xdl_bank_account_30 order by acc_money
  </select>

  a.拷贝分页插件的jar包(pageHelper.jar jsqlparser.jar)到lib

  b.在主配置文件(sqlmap-config.xml)中配置,分页插件的拦截器

   <!--设定改为STDOUT_LOGGING,可以在输出日志里显示sql语句的-->
   <settings>
  <setting name="logImpl" value="STDOUT_LOGGING" />
   </settings>
  
   <plugins>
      <plugin interceptor="com.github.pagehelper.PageHelper"></plugin>
   </plugins>

  c.使用分页插件的api,完成分页查询

        PageHelper.startPage(2, 2);
  List<XdlBankAccount> datas2 = dao.findAll();
  //System.out.println(datas2);
  for (XdlBankAccount xdlBankAccount : datas2) {
  	System.out.println(xdlBankAccount);
  }

当数据库中的字段,表,实体类中属性不对应时如何解决

• 使用字段的别名

  在主配置文件(sqlmap-config.xml)中定义实体类的别名,必须放在拦截器settings下面

  <typeAliases>
     <typeAlias type="com.xdl.bean.XdlBankAccount" alias="account"/>
  </typeAliases>

  在XdlBankAccountMapper.xml文件写sql语句时,数据类型可以使用别名

  <select id="findAll"   resultType="account" >
    select  * from xdl_bank_account_30 order by acc_money
  </select>

  当数据库的字段和实体类的属性不对应时,在XdlBankAccountMapper.xml文件写sql语句,使用别名

  <select id="findAccountById" parameterType="int"
      resultType="com.xdl.bean.XdlBankAccount">
      select id acc_id,acc_no,acc_password,acc_money
         from xdl_bank_account_30 where  id = #{id}
  </select>

• 使用resultMap,让数据库字段和实体类中的属性对应

<select id="findAccountById" parameterType="int"
    resultMap="accountMap">
     select * from xdl_bank_account_30 where  id = #{id}
</select>
<resultMap type="com.xdl.bean.XdlBankAccount" id="accountMap">
     <!--  说明数据库中的字段和实体类中属性的对应关系 -->
     <result  column="id"  property="acc_id"/>
</resultMap>

Spring和Mybatis整合

• SqlSessionFactoryBean

  产生的是SqlSessionFactory类型的对象,最终能提供SqlSession

  这个类型依赖于dataSource和Sql定义文件

• MapperFactoryBean

  产生的是Mapper的实现类,这个类型依赖于SqlSessionFactory和Mapper接口

整合步骤

• 建立一个项目,导入jar包(mybatis.jar mybatis-spring.jar ojdbc14.jar ioc aop dao 数据库连接池)拷贝Spring配置文件到src

• 编写实体类

   package com.xdl.bean;
public class XdlBankAccount {
    public XdlBankAccount(int id, String acc_no, String acc_password, double acc_money) {
		super();
		this.id = id;
		this.acc_no = acc_no;
		this.acc_password = acc_password;
		this.acc_money = acc_money;
	}
	public XdlBankAccount() {
		super();
		// TODO Auto-generated constructor stub
	}
	@Override
	public String toString() {
		return "XdlBankAccount [id=" + id + ", acc_no=" + acc_no + ", acc_password=" + acc_password + ", acc_money="
				+ acc_money + "]";
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getAcc_no() {
		return acc_no;
	}
	public void setAcc_no(String acc_no) {
		this.acc_no = acc_no;
	}
	public String getAcc_password() {
		return acc_password;
	}
	public void setAcc_password(String acc_password) {
		this.acc_password = acc_password;
	}
	public double getAcc_money() {
		return acc_money;
	}
	public void setAcc_money(double acc_money) {
		this.acc_money = acc_money;
	}
	private  int   id;
    private  String acc_no;
    private  String acc_password;
    private  double  acc_money;
}

• 编写SQL定义文件(XdlBankAccountMapper.xml),根据id查询银行账户

   <?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD Mapper 3.0//EN"
 "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
 <!-- namespace指定和哪个Mapper映射器接口对应 -->
<mapper namespace="com.xdl.mapper.XdlBankAccountDAO">
	<!-- 定义SQL语句 -->
    <select id="findAccountById" parameterType="int"
      resultType="com.xdl.bean.XdlBankAccount">
         select * from xdl_bank_account_30 where  id = #{id}
    </select>
</mapper>

• 根据Mapper映射器规则,编写DAO接口

   package com.xdl.mapper;
import com.xdl.bean.XdlBankAccount;
public interface XdlBankAccountDAO {
     XdlBankAccount  findAccountById(int  id);
}

• 在Spring配置文件(applicationContext.xml)中配置SqlSessionFactoryBean,依赖于dataSource和SQL定义,然后配置MapperFactoryBean就可以产生DAO的实现类

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	   xmlns:context="http://www.springframework.org/schema/context"
	   xmlns:jee="http://www.springframework.org/schema/jee"
	   xmlns:aop="http://www.springframework.org/schema/aop"
	   xmlns:mvc="http://www.springframework.org/schema/mvc"
	   xsi:schemaLocation="
		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.1.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.1.xsd
		http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.1.xsd
		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.1.xsd
		http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.1.xsd
		http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.1.xsd">
	<!--  连接池对象的配置  -->
	<bean  id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" >
			<value>oracle.jdbc.OracleDriver</value>
		</property>
		<property name="url"
				  value="jdbc:oracle:thin:@localhost:1521:oracle"></property>
		<property name="username"  value="system"></property>
		<property name="password"  value="123456"></property>
	</bean>
	<!--  创建sqlSessionFactory -->
	<bean id="sqlSessionFactory"  class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource"  ref="dataSource"></property>
		<property name="mapperLocations"  value="classpath:com/xdl/mapper/*.xml"></property>
	</bean>
	<!--  生成Mapper的实现类 -->
	<bean  id="accountDao"  class="org.mybatis.spring.mapper.MapperFactoryBean" >
		<property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
		<property name="mapperInterface" value="com.xdl.mapper.XdlBankAccountDAO"></property>
	</bean>
</beans>

• 创建测试类,进行测试

   package com.xdl.test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.xdl.mapper.XdlBankAccountDAO;
public class XdlBankAccountDAOTest {
	public static void main(String[] args) {
		ApplicationContext app =
			new ClassPathXmlApplicationContext("applicationContext.xml");
        XdlBankAccountDAO  dao = app.getBean("accountDao", XdlBankAccountDAO.class);
        System.out.println(dao.findAccountById(1));
	}
}

• 编写根据账号和密码查询账户以及根据id删除银行账户

在SQL定义文件(XdlBankAccountMapper.xml)里增加语句

   <!-- 根据账号和密码查询账户 -->
<select id="findAccountByAccNoAndAccPassword" parameterType="int"
    resultType="com.xdl.bean.XdlBankAccount">
        select * from xdl_bank_account_30 where  acc_no = #{acc_no} and acc_password = #{acc_password}
</select>
<!-- 根据id删除账户 -->
<select id="deleteAccountById" parameterType="int"
    resultType="com.xdl.bean.XdlBankAccount">
        delete from xdl_bank_account_30 where  id = #{id}
</select>

在XdlBankAccountDao里增加方法

XdlBankAccount  findAccountByAccNoAndAccPassword(@Param("acc_no") String acc_no,@Param("acc_password") String acc_password);
int  deleteAccountById(int id)；