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

zz22

先看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>