1.构建maven工程
2.添加依赖pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.hill</groupId>
<artifactId>mybatisDemo</artifactId>
<packaging>war</packaging>
<version>0.0.1</version>
<name>mybatisDemo</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<org.slf4j.version>1.7.21</org.slf4j.version>
<!-- spring版本号 -->
<spring.version>3.2.8.RELEASE</spring.version>
<!-- mybatis版本号 -->
<mybatis.version>3.4.5</mybatis.version>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>
<!-- mybatis-spring -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.1</version>
</dependency>
<!-- spring -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>4.3.0.RELEASE</version>
</dependency>
<!-- mysql -->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.1.1</version>
</dependency>
<!-- 加密/解密依赖包 -->
<!-- https://mvnrepository.com/artifact/org.bouncycastle/bcprov-jdk14 -->
<dependency>
<groupId>org.bouncycastle</groupId>
<artifactId>bcprov-jdk14</artifactId>
<version>1.50</version>
</dependency>
<!-- alibaba json包 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.46</version>
</dependency>
<!-- ehcache 缓存包 -->
<dependency>
<groupId>net.sf.ehcache</groupId>
<artifactId>ehcache</artifactId>
<version>2.10.2</version>
</dependency>
<!-- mybatis整合ehcache所需jar包 -->
<dependency>
<groupId>org.mybatis.caches</groupId>
<artifactId>mybatis-ehcache</artifactId>
<version>1.1.0</version>
</dependency>
<!-- log start -->
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${org.slf4j.version}</version>
</dependency>
<!-- log end -->
<!-- webapp start -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>servlet-api</artifactId>
<version>2.5</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.1</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
<scope>runtime</scope>
</dependency>
<!-- webapp end -->
<!-- junit start -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<!-- junit end -->
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-install-plugin</artifactId>
<version>2.5.2</version>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-clean-plugin</artifactId>
<version>3.0.0</version>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.5.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>${project.build.sourceEncoding}</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<version>2.7</version>
<configuration>
<encoding>${project.build.sourceEncoding}</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>2.19.1</version>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-war-plugin</artifactId>
<version>2.6</version>
</plugin>
<plugin>
<groupId>org.eclipse.jetty</groupId>
<artifactId>jetty-maven-plugin</artifactId>
<version>9.3.7.v20160115</version>
<configuration>
<webApp>
<contextPath>/${project.artifactId}</contextPath>
</webApp>
</configuration>
</plugin>
</plugins>
<finalName>${project.artifactId}</finalName>
</build>
</project>
3.配置mybatis核心配置文件mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 引入配置文件 -->
<properties resource="db.properties"/>
<settings>
<!-- 开启mybatis二级缓存 这个地方为全局开关;如果设置为false,其他mapple添加<cache/> 也无用 -->
<setting name="cacheEnabled" value="true"/>
</settings>
<!-- 配置别名 -->
<typeAliases>
<typeAlias type="cn.hill.domain.User" alias="user"/>
<typeAlias type="cn.hill.domain.Items" alias="items"/>
<typeAlias type="cn.hill.domain.Orders" alias="orders"/>
<typeAlias type="cn.hill.domain.OrderDetail" alias="orderDetail"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!-- 配置数据库连接 -->
<dataSource type="UNPOOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 配置映射文件 -->
<mapper resource="cn/hill/mapper/userMapper.xml" />
<mapper resource="cn/hill/mapper/itemsMapper.xml" />
<mapper resource="cn/hill/mapper/ordersMapper.xml" />
<mapper resource="cn/hill/mapper/cacheTestMapper.xml"/>
<mapper resource="cn/hill/mapper/ordersDetailMapper.xml"/>
<mapper class="cn.hill.dao.UserDao"/>
</mappers>
</configuration>
4.编写mapper映射
<?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="cn.hill.dao.OrdersDao">
<!-- 配置 one to more 方式 -->
<select id="findOrderDetailByUserId" parameterType="Integer" useCache="false" resultMap="orderDetailResultMap">
<![CDATA[
SELECT
t3.username as username,
t2.number as number,
t2.createtime ,
t2.note,
t4.name,
t4.detail,
t4.price,
t1.items_num as itemsNum
FROM
orderdetail t1
LEFT JOIN orders t2 ON t1.orders_id = t2.id
LEFT JOIN USER t3 ON t2.user_id = t3.id
LEFT JOIN items t4 ON t1.items_id = t4.id
]]>
<where>
t3.id = #{userId}
</where>
</select>
<!-- 不配置resultMap,直接将结果封装到map -->
<select id="findOrderDetailByUserIdToMap" parameterType="Integer" useCache="false" resultType="hashMap">
<![CDATA[
SELECT
t3.username as username,
t2.number as number,
t2.createtime ,
t2.note,
t4.name,
t4.detail,
t4.price,
t1.items_num as itemsNum
FROM
orderdetail t1
LEFT JOIN orders t2 ON t1.orders_id = t2.id
LEFT JOIN USER t3 ON t2.user_id = t3.id
LEFT JOIN items t4 ON t1.items_id = t4.id
]]>
<where>
t3.id = #{userId}
</where>
</select>
<resultMap type="orders" id="orderDetailResultMap" >
<id property="id" column="id"/>
<result property="userId" column="user_id"/>
<result property="number" column="number"/>
<result property="createtime" column="createtime"/>
<result property="note" column="note"/>
<association property="user" javaType="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="birthday" column="birthday"/>
<result property="sex" column="sex"/>
<result property="address" column="address"/>
</association>
<collection property="orderDetailList" ofType="orderDetail">
<id property="id" column="id"/>
<result property="orderId" column="orders_id"/>
<result property="itemsId" column="items_id"/>
<result property="itemsNum" column="items_num"/>
<association property="items" javaType="items">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="price" column="price"/>
<result property="detail" column="detail"/>
<result property="pic" column="pic"/>
<result property="createTime" column="createtime"/>
</association>
</collection>
</resultMap>
</mapper>
5.测试
a.获取SqlSessionFactory
package cn.hill.util;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class SqlSessionFactoryHelper {
private static SqlSessionFactory SQL_SESSION_FACTORY = null;
private SqlSessionFactoryHelper() {
}
public static synchronized SqlSessionFactory getSqlSessionFactory() {
if(null == SQL_SESSION_FACTORY) {
InputStream is = null;
try {
is = Resources.getResourceAsStream("mybatis-config.xml");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
SQL_SESSION_FACTORY = new SqlSessionFactoryBuilder().build(is);
}
return SQL_SESSION_FACTORY;
}
}
b.测试
package cn.hill.dao;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.junit.AfterClass;
import org.junit.BeforeClass;
import org.junit.Test;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import cn.hill.domain.Orders;
import cn.hill.util.SqlSessionFactoryHelper;
public class OrdersDaoTest {
private static SqlSession session = null;
@BeforeClass
public static void setUpBeforeClass() throws Exception {
session = SqlSessionFactoryHelper.getSqlSessionFactory().openSession(true);
}
@AfterClass
public static void tearDownAfterClass() throws Exception {
session.close();
}
@Test
public void testSelectOrders() {
//fail("Not yet implemented");
OrdersDao dao = session.getMapper(OrdersDao.class);
Orders orders = dao.selectOrders(4);
System.out.println("订单号:" + orders.getId());
System.out.println("下单客户:" + orders.getUser().getUsername());
System.out.println("下单时间:" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(orders.getCreatetime()));
}
@Test
public void testFindOrderDetailByUserId() {
//fail("Not yet implemented");
OrdersDao dao = session.getMapper(OrdersDao.class);
List<Orders> list = dao.findOrderDetailByUserId(1);
list.forEach(x -> {
x.getOrderDetailList().forEach(y -> {
System.out.println(y.getItems().getName());
});
});
}
@Test
public void testfindOrderDetailByUserIdToMap() {
//fail("Not yet implemented");
OrdersDao dao = session.getMapper(OrdersDao.class);
List<Map<String, Object>> list = dao.findOrderDetailByUserIdToMap(1);
JSONArray jsonArray = new JSONArray();
System.out.println();
list.forEach(x -> {
JSONObject jsonobj = new JSONObject();
jsonobj.putAll(x);
jsonArray.add(jsonobj);
x.forEach((k,v) -> {
// System.out.print(k + ": " + v + "\t");
});
// System.out.println("");
});
// 输出结果一样
String jsonString = JSON.toJSONString(list);
System.out.println(jsonString);
System.out.println(jsonArray);
System.out.println(jsonArray.toString());
}
}
6 别忘记了log4j 哦,可查看具体sql语句
#
# Licensed to the Apache Software Foundation (ASF) under one
# or more contributor license agreements. See the NOTICE file
# distributed with this work for additional information
# regarding copyright ownership. The ASF licenses this file
# to you under the Apache License, Version 2.0 (the
# "License"); you may not use this file except in compliance
# with the License. You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing,
# software distributed under the License is distributed on an
# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
# KIND, either express or implied. See the License for the
# specific language governing permissions and limitations
# under the License.
#
log4j.rootLogger=debug, stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d %p [%c] - %m %n
# General Apache libraries
log4j.logger.org.apache=trace
# Default MyBatis logging
log4j.logger.org.apache=TRACE
log4j.logger.com.mysql=TRACE
log4j.logger.java.sql=TRACE