mybatis查询入门(log4j控制台sql语句日记输出的)

先看demo的结构,可以先讲数据库建立起来,下面有代码里面有数据库sql语句数据库建模好之后,只要运行junit下面的test目录下面的相应方法,注意去掉ignore

数据库的模型图

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

/**
 * MyBatis Connection Factory, which reads the configuration data from a XML file.
 * static只读取一次xml配置文件
 * @author Loiane Groner
 * http://loianegroner.com (English)
 * http://loiane.com (Portuguese)
 */
public class MyBatisConnectionFactory {

	private static SqlSessionFactory sqlSessionFactory;

	static {

		try {

			String resource = "SqlMapConfig.xml";
			Reader reader = Resources.getResourceAsReader(resource);

			if (sqlSessionFactory == null) {
				sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
				
			}
		}

		catch (FileNotFoundException fileNotFoundException) {
			fileNotFoundException.printStackTrace();
		}
		catch (IOException iOException) {
			iOException.printStackTrace();
		}
	}

	public static SqlSessionFactory getSqlSessionFactory() {

		return sqlSessionFactory;
	}

}

<!-- 命名别名 -->
	<typeAliases>
		<typeAlias alias="Article" type="com.loiane.model.Article"/>
	</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/blog_ibatis"/>
				<property name="username" value="root"/>
				<property name="password" value="wangxuehui"/>
			</dataSource>
	   </environment>
	</environments>
	
	<!-- 相应的对应配置文件  -->
    <mappers>
  	   <mapper resource="com/loiane/data/Article.xml"/>
    </mappers>

<?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="Article">

	<!-- 根据title是否存在,可以查看控制台的sql语句,一看就明白了 -->
    <select id="selectArticleByTitle" parameterType="Article"
     resultType="Article">
    	SELECT id, title, author
		FROM article
		WHERE id_status = 1
		<if test="title != null">
			AND title LIKE #{title}
		</if> 
    </select>
    
    <!-- 根据title,author是否存在,可以查看控制台的sql语句,一看就明白了 -->
    <select id="selectArticleByTitleAndAuthor" parameterType="Article"
     resultType="Article">
    	SELECT id, title, author
		FROM article
		WHERE id_status = 1
		<if test="title != null">
			AND title LIKE #{title}
		</if> 
		<if test="author != null">
			AND author LIKE #{author}
		</if>
    </select>
    
     <!-- 我感觉这个相当于java if elseif else语句,可以查看控制台的sql语句,一看就明白了 -->
    <select id="selectArticleByTitleOrAuthorOrCategory" parameterType="Article"
     resultType="Article">
    	SELECT id, title, author
		FROM article
		WHERE id_status = 1
		<choose>
			<when test="title != null">
				AND title LIKE #{title}
			</when> 
			<when test="author != null">
				AND author LIKE #{author}
			</when>
			<otherwise>
				AND id_category = 3
			</otherwise>
		</choose>
    </select>
	
	  <!-- 我感觉这个要自己尝试输入title,author的不同的值,再查看控制台的sql语句,一看就明白了 -->
	 <select id="selectArticleByTitleAndAuthorDynamic" parameterType="Article"
     resultType="Article">
    	SELECT id, title, author
		FROM article
		<where>
			<if test="title != null">
				title LIKE #{title}
			</if> 
			<if test="author != null">
				OR author LIKE #{author}
			</if>
		</where>
    </select>
    
     <!-- 我感觉这个selectArticleByTitleAndAuthorDynamic配置功能差不多,可以查看控制台的sql语句,一看就明白了 -->
    <select id="selectArticleByTitleAndAuthorDynamic2" parameterType="Article"
     resultType="Article">
    	SELECT id, title, author
		FROM article
		<trim prefix="WHERE" prefixOverrides="AND |OR ">
			<if test="title != null">
				title LIKE #{title}
			</if> 
			<if test="author != null">
				AND author LIKE #{author}
			</if>
		</trim>
    </select>
    
    <!-- sql语句在in方面的应用,可以查看控制台的sql语句,一看就明白了 -->
    <select id="selectArticleByListCategories" resultType="Article">
    	SELECT id, title, author
		FROM article
		WHERE id_category IN
		<foreach item="category" index="index" collection="list"
			open="(" separator="," close=")">
			#{category}
		</foreach> 
    </select>
    
</mapper>