Mybatis 的使用 (2)

1. 关于mybatis框架的说明

为了更加直观的看到mybatis执行的sql语句，可以在项目的src目录下加入log4j.properties文件，

将程序执行的sql输出到控制台,需要引入依赖包以及log4j.properties文件：

                <dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.14</version>

</dependency>

------------------------------------

### direct log messages to stdout ###

log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.err
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c\:mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
### set log levels - for more verbose logging change 'info' to 'debug' ###
log4j.rootLogger=debug, stdout

l  Sql映射文件中的parameterType和resultType都可以指定为hashmap类型

<select id="queryBook4Map" resultType="hashmap">
select
<include refid="book_columns" />
from t_book where
id=#{id}
</select>
<select id="queryBook4Like" resultMap="bookProperty4Columns"
parameterType="Book">
select
<include refid="book_columns" />
from t_book where
book_name like '%${name}%'
</select>

l  可以在sqlMapConfig.xml中定义别名，简化sql映射文件的配置

在sqlMapConfig.xml中：

<typeAliases>
<typeAlias type="mybatis.entity.User" alias="User"/>
<typeAlias type="mybatis.entity.Book" alias="Book"/>
</typeAliases>

在sql映射文件中使用别名：

<resultMap type="Book" id="bookProperty4Columns">
<id property="id" column="id" />
<result property="name" column="book_name" />
<result property="price" column="book_price" />
</resultMap>

使用<sql>标签将所有字段进行提取，来消除sql语句中的重复字段：

<sql id="book_columns">
id,book_name,book_price
</sql>

l  模糊查询

在sql映射文件中：

<select id="queryBook4Like" resultMap="bookProperty4Columns"
parameterType="Book">
select
<include refid="book_columns" />
from t_book where
book_name like '%${name}%'
</select>

在java代码中：

@Test
public void testQueryByNameLike() {
Book book = new Book();
book.setName("g");
System.out.println(book);
List<Book> booklist = sqlSession.selectList(NAMESPACE + ".queryBook4Like", book);
for (Book book2 : booklist) {
System.out.println(book2);
}
sqlSession.close();
}

---------------------BookMapper.xml------------------------------------

<?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="mybatis.entity.Book">
<sql id="book_columns">
id,book_name,book_price
</sql>
<resultMap type="Book" id="bookProperty4Columns">
<id property="id" column="id" />
<result property="name" column="book_name" />
<result property="price" column="book_price" />
</resultMap>
<select id="queryBookById" resultType="Book">
select
<include refid="book_columns" />
from t_book where id=#{id}
</select>
<select id="queryBookById2" resultType="Book">
SELECT id,book_name AS
NAME,book_price AS price FROM t_book where id=#{id}
</select>
<select id="queryBookById3" resultMap="bookProperty4Columns">
select
<include refid="book_columns" />
from t_book where id=#{id}
</select>
<select id="queryBook4Map" resultType="hashmap">
select
<include refid="book_columns" />
from t_book where
id=#{id}
</select>
<select id="queryBook4Like" resultMap="bookProperty4Columns"
parameterType="Book">
select
<include refid="book_columns" />
from t_book where
book_name like '%${name}%'
</select>
<insert id="insertBook" parameterType="Book">
insert into
t_book(book_name,book_price) values(#{name},#{price})
</insert>
<insert id="insertBookFromMap" parameterType="hashmap">
insert into
t_book(book_name,book_price) values(#{name},#{price})
</insert>

</mapper>

--------------------------BookMethodTest.java--------------------------

package mybatis;
import java.io.IOException;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.Before;
import org.junit.Test;
import mybatis.entity.Book;
public class BookMethosTest {
SqlSession sqlSession = null;
static final String NAMESPACE = "mybatis.entity.Book";
@Before
public void createSession() throws IOException {
Reader resourceAsReader = Resources.getResourceAsReader("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
sqlSession = sessionFactory.openSession();
}
@Test
public void testQueryById() {
Book book = sqlSession.selectOne(NAMESPACE + ".queryBookById", 1);
System.out.println(book);
sqlSession.close();
}
@Test
public void testQueryById2() {
Book book = sqlSession.selectOne(NAMESPACE + ".queryBookById2", 1);
System.out.println(book);
sqlSession.close();
}
@Test
public void testQueryById3() {
Book book = sqlSession.selectOne(NAMESPACE + ".queryBookById3", 2);
System.out.println(book);
sqlSession.close();
}
        @Test
public void testQueryById4Map() {
Map<String, Object> book = sqlSession.selectOne(NAMESPACE + ".queryBook4Map", 2);
System.out.println(book);
sqlSession.close();
}
@Test
public void testQueryByNameLike() {
Book book = new Book();
book.setName("g");
System.out.println(book);
List<Book> booklist = sqlSession.selectList(NAMESPACE + ".queryBook4Like", book);
for (Book book2 : booklist) {
System.out.println(book2);
}
sqlSession.close();
}
@Test
public void insertBook() {
Book book = new Book("English", 33);
int num = sqlSession.insert(NAMESPACE + ".insertBook", book);
System.out.println(num);
sqlSession.commit();
sqlSession.close();
}

@Test
public void insertBook2() {
Map<String, Object> bookmap = new HashMap<String, Object>();
bookmap.put("name", "政治");
bookmap.put("price", 300);
int num = sqlSession.insert(NAMESPACE + ".insertBookFromMap", bookmap);
System.out.println(num);
sqlSession.commit();
sqlSession.close();
}
}