MyBatis构造SQL

构造SQL

• 1.创建ReturnSql类，实现得到增删改查的SQL语句的四种方法

  package com.cmy.sql;
  
  import com.cmy.bean.Book;
  import org.apache.ibatis.jdbc.SQL;
  
  /**
   * @author chenmingyong
   */
  public class ReturnSql {
  	/**
  	 * 查询全部数据
  	 * @return 查询语句
  	 */
  	public String getSelectAll(){
  		return new SQL(){
  			{
  				SELECT("*");
  				FROM("book");
  			}
  		}.toString();
  	}
  	
  	/**
  	 * 增加一条新数据
  	 * @param book 对象
  	 * @return 增加语句
  	 */
  	public String getInsert(Book book){
  		return new SQL(){
  			{
  				INSERT_INTO("book");
  				INTO_VALUES("#{book_id},#{book_name},#{book_author},#{book_publisher}");
  			}
  		}.toString();
  	}
  	
  	/**
  	 * 更新数据
  	 * @param book
  	 * @return 更新语句
  	 */
  	public String getUpdate(Book book){
  		return new SQL(){
  			{
  				UPDATE("book");
  				SET("book_name=#{book_name},book_author=#{book_author},book_publisher=#{book_publisher}");
  				WHERE("book_id=#{book_id}");
  			}
  		}.toString();
  	}
  	
  	/**
  	 * 删除一条语句
  	 * @param book_id 图书编号
  	 * @return 删除语句
  	 */
  	public String getDelete(Integer book_id){
  		return new SQL(){
  			{
  				DELETE_FROM("book");
  				WHERE("book_id=#{book_id}");
  			}
  		}.toString();
  	}
  }
  
  

• 2.创建接口

  package com.cmy.mapper;
  
  import com.cmy.bean.Book;
  import com.cmy.sql.ReturnSql;
  import org.apache.ibatis.annotations.*;
  
  import java.util.List;
  
  /**
   * @author chenmingyong
   */
  public interface BookMapper {
  	/**
  	 * 查询全部图书信息
  	 * @return 结果集
  	 */
  	@SelectProvider(type = ReturnSql.class, method = "getSelectAll")
  	public abstract List<Book> selectAll();
  	
  	/**
  	 * 插入一条数据
  	 * @param book 图书类实例对象
  	 * @return 返回影响行数
  	 */
  	@InsertProvider(type = ReturnSql.class, method = "getInsert")
  	public abstract Integer insert(Book book);
  	
  	/**
  	 * 更新一条数据
  	 * @param book 图书类实例对象
  	 * @return 返回影响行数
  	 */
  	@UpdateProvider(type = ReturnSql.class, method = "getUpdate")
  	public abstract Integer update(Book book);
  	
  	/**
  	 * 删除一条数据
  	 * @param book_id 图书id
  	 * @return 返回影响行数
  	 */
  	@DeleteProvider(type = ReturnSql.class, method = "getDelete")
  	public abstract Integer delete(Integer book_id);
  }
  
  

• 3.在核心配置文件中配置映射关系

  <?xml version="1.0" encoding="UTF-8" ?>
  
  <!--MyBitis的DTD约束，定义xml标签约束,使开发者按照定义书写-->
  <!DOCTYPE configuration
          PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
          "http://mybatis.org/dtd/mybatis-3-config.dtd">
  <!--configuration 根标签-->
  <configuration>
      <!--引入数据库配置文件-->
      <properties resource="jdbc.properties" />
      <!--配置LOG4J-->
      <settings>
          <setting name="logImpl" value="log4j"/>
      </settings>
      <!--起别名-->
      <typeAliases>
          <package name="com.cmy.bean" />
      </typeAliases>
      <!--environments配置数据库环境，环境可以有很多个，而default属性则是指定某个环境-->
      <environments default="mysql1">
          <!--    environment数据库环境，id属性：唯一标识    -->
          <environment id="mysql1">
              <!--transactionManager事务管理，type 采用JDBC默认的事务管理-->
              <transactionManager type="JDBC" />
              <!--dataSource数据库源信息 type属性 连接池-->
              <dataSource type="POOLED">
                  <!--property 获取数据库连接的配置信息-->
                  <property name="driver" value="${driver}" />
                  <property name="url" value="${url}" />
                  <property name="username" value="${username}" />
                  <property name="password" value="${password}" />
              </dataSource>
          </environment>
      </environments>
      <!--mappers配置映射关系-->
      <mappers>
          <package name="com.cmy.mapper"/>
      </mappers>
  </configuration>
  
  

  使用<package>标签配置映射关系，指定对应接口所在包的映射关系

• 4.编写测试类

  package com.cmy.test;
  
  import com.cmy.bean.Book;
  import com.cmy.mapper.BookMapper;
  import org.apache.ibatis.io.Resources;
  import org.apache.ibatis.session.SqlSession;
  import org.apache.ibatis.session.SqlSessionFactory;
  import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  import org.junit.Test;
  
  import java.io.InputStream;
  import java.util.List;
  
  /**
   * @author chenmingyong
   */
  public class Test01 {
  	/**
  	 * 查询全部数据
  	 */
  	@Test
  	public void selectAll(){
  		InputStream is = null;
  		SqlSession sqlSession = null;
  		try {
  			is = Resources.getResourceAsStream("MyBatisConfig.xml");
  			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  			sqlSession = sqlSessionFactory.openSession(true);
  			BookMapper mapper = sqlSession.getMapper(BookMapper.class);
  			List<Book> list = mapper.selectAll();
  			for (Book book : list) {
  				System.out.println(book);
  			}
  		}
  		catch (Exception e){
  			e.printStackTrace();
  		}
  		finally {
  			if(sqlSession != null){
  				sqlSession.close();
  			}
  			if(is != null){
  				try {
  					is.close();
  				}
  				catch (Exception e){
  					e.printStackTrace();
  				}
  			}
  		}
  	}
  	
  	/**
  	 * 增加一条新数据
  	 */
  	@Test
  	public void insert(){
  		InputStream is = null;
  		SqlSession sqlSession = null;
  		try {
  			is = Resources.getResourceAsStream("MyBatisConfig.xml");
  			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  			sqlSession = sqlSessionFactory.openSession(true);
  			BookMapper mapper = sqlSession.getMapper(BookMapper.class);
  			Book book = new Book(4, "测试", "测试", "测试");
  			Integer result = mapper.insert(book);
  			System.out.println(result);
  		}
  		catch (Exception e){
  			e.printStackTrace();
  		}
  		finally {
  			if(sqlSession != null){
  				sqlSession.close();
  			}
  			if(is != null){
  				try {
  					is.close();
  				}
  				catch (Exception e){
  					e.printStackTrace();
  				}
  			}
  		}
  	}
  	
  	/**
  	 * 更新数据
  	 */
  	@Test
  	public void update(){
  		InputStream is = null;
  		SqlSession sqlSession = null;
  		try {
  			is = Resources.getResourceAsStream("MyBatisConfig.xml");
  			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  			sqlSession = sqlSessionFactory.openSession(true);
  			BookMapper mapper = sqlSession.getMapper(BookMapper.class);
  			Book book = new Book(4, "测试11", "测试11", "测试11");
  			Integer result = mapper.update(book);
  			System.out.println(result);
  		}
  		catch (Exception e){
  			e.printStackTrace();
  		}
  		finally {
  			if(sqlSession != null){
  				sqlSession.close();
  			}
  			if(is != null){
  				try {
  					is.close();
  				}
  				catch (Exception e){
  					e.printStackTrace();
  				}
  			}
  		}
  	}
  	
  	/**
  	 * 删除数据
  	 */
  	@Test
  	public void delete(){
  		InputStream is = null;
  		SqlSession sqlSession = null;
  		try {
  			is = Resources.getResourceAsStream("MyBatisConfig.xml");
  			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
  			sqlSession = sqlSessionFactory.openSession(true);
  			BookMapper mapper = sqlSession.getMapper(BookMapper.class);
  			Integer result = mapper.delete(4);
  			System.out.println(result);
  		}
  		catch (Exception e){
  			e.printStackTrace();
  		}
  		finally {
  			if(sqlSession != null){
  				sqlSession.close();
  			}
  			if(is != null){
  				try {
  					is.close();
  				}
  				catch (Exception e){
  					e.printStackTrace();
  				}
  			}
  		}
  	}
  }