mybatis详解

1、查询数据库里的数据

1、建立实体类Person

package com.pojo;

public class Person {
	private int id;
	private String name;
	public Person() {
	}
	public Person(int id, String name) {
		this.id = id;
		this.name = name;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Person [id=" + id + ", name=" + name + "]";
	}
}

2、建立Person.xml mapper==dao —将java类写成了配置文件

<?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">
<!-- dao的配置  mapper就是dao   data access object -->
<!-- dao对pojo包全包扫描  工作空间 -->
<mapper namespace="com.pojo">
	<!-- 配置一个查询语句 -->
	<!-- 给这个查询功能取一个唯一的id叫做listPerson -->
	<!-- 返回值的类型 resultType com.pojo.Person
		从表里面取出来的数据，通过mybatis框架注入到Person的实例中（new Person()）
	 -->
	<select id="listPerson" resultType="Person">
		select * from person
	</select>
	
</mapper>

3、mybatis-config.xml中进行 Person.xml别名配置，连接池配置，dao层的配置

<?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>
	<!-- 别名，自动扫描指定包下类型，这个配置，可以让Person.xml的resultType无需去写完整的类路径 -->
    <typeAliases>
      <package name="com.pojo"/>
    </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/demo?characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <!-- dao层配置 -->
    <mappers>
        <mapper resource="com/pojo/Person.xml"/>
    </mappers>
</configuration>

4、MybatisTest

package com.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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 com.pojo.Person;

public class MybatisTest {

	/**
	    1. 应用程序找Mybatis要数据
		2. mybatis从数据库中找来数据
		2.1 通过mybatis-config.xml 定位哪个数据库
		2.2 通过Person.xml执行对应的select语句
		2.3 基于Person.xml把返回的数据库记录封装在Person对象中
		2.4 把多个Person对象装在一个Person集合中
		3. 返回一个Person集合
	 */
    public static void main(String[] args) throws IOException {
    	//拿到框架环境配置文件
        String resource = "mybatis-config.xml";
        //org.apache.ibatis.io.Resources
        //读取配置信息到输入流
        InputStream inputStream = Resources.getResourceAsStream(resource);
        //建造者模式，创建工厂，工厂用于创建sql会话
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //使用工厂创建产品   java建立和db的联系，然后sql语句操作，关闭联系
        SqlSession session=sqlSessionFactory.openSession();
        //通过sqlsession将mapper<xml>中（select id="listPerson"）进行daoimpl的实现
        //框架帮我们实现--动态代理   Proxy.newInstance(dom4j--><mapper><select id="listPerson">sql</mapper>)
        List<Person> cs=session.selectList("listPerson");//k-v new DaoImpl().listPerson()
        
        for (Person c : cs) {
            System.out.println(c.getName());
        }
    }
	
}

2、测试增删查改

package com.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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 com.pojo.Person;

public class CurdTest {
	//拿到框架环境配置文件
	static String resource = "mybatis-config.xml";
	//org.apache.ibatis.io.Resources
	//读取配置信息到输入流
	static InputStream inputStream = null;
	static {
		try {
			inputStream= Resources.getResourceAsStream(resource);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	//建造者模式，创建工厂，工厂用于创建sql会话
	static SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	
	//列表测试
	@Test
	public void listTest()throws Exception{
		SqlSession session=sqlSessionFactory.openSession();
		
		List<Person> ps=session.selectList("listPerson");
		for(Person p:ps) {
			System.out.println(p);
		}
		
        session.commit();
        session.close();
	}
	
	//修改测试
	@Test
	public void updateTest()throws Exception{
		SqlSession session=sqlSessionFactory.openSession();
		
		Person p=session.selectOne("getPerson",2);
		p.setName("banana");
		session.update("updatePerson",p);
		
        session.commit();
        session.close();
	}
	
	//查询测试
	@Test
	public void selectOneTest()throws Exception{
		SqlSession session=sqlSessionFactory.openSession();
		
		Person p=session.selectOne("getPerson",2);
		System.out.println(p);
		
        session.commit();
        session.close();
	}
	
	//删除测试
	@Test
	public void deleteTest()throws Exception{
		SqlSession session=sqlSessionFactory.openSession();
		
		Person p=new Person();
		p.setId(4);
		session.delete("deletePerson",p);
		
        session.commit();
        session.close();
	}
	//新增测试
	@Test
	public void addTest()throws Exception{
		//使用工厂创建产品   java建立和db的联系，然后sql语句操作，关闭联系
		SqlSession session=sqlSessionFactory.openSession();
		
	    Person p = new Person();
	    p.setName("新增加的Person");
	    session.insert("addPerson",p);
		
        session.commit();
        session.close();
	}
	
}

3、模糊查询

1、Person.xml

<!-- 模糊查询 
	concat-->
	<select id="listPersonByName"  parameterType="string" resultType="Person">
         select * from   person  where name like concat('%',#{0},'%')
    </select>

2、测试类

//模糊查询测试
	@Test
	public void queryTest() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();

		List<Person> ps = session.selectList("listPersonByName", "app");
		for (Person p : ps) {
			System.out.println(p.getName());
		}

		session.commit();
		session.close();
	}

4、多条件查询

 <!-- 多条件查询 -->
    <select id="listPersonByIdAndName"  parameterType="map" resultType="Person">
         select * from   person  where id> #{id}  and name like concat('%',#{name},'%')
    </select>

//多条件测试
	@Test
	public void queryByConditions()throws Exception{
		SqlSession session = sqlSessionFactory.openSession();

        Map<String,Object> params = new HashMap<>();
        params.put("id", 1);
        params.put("name", "ban");
         
        List<Person> ps = session.selectList("listPersonByIdAndName",params);
        for (Person p:ps) {
            System.out.println(p.getName());
        }

		session.commit();
		session.close();
	}

5、一对多查询

1、需在mybatis.xml

 <!-- dao层配置 -->
    <mappers>
        <mapper resource="com/pojo/Person.xml"/>
        <mapper resource="com/pojo/Area.xml"/>
    </mappers>

2、Area.java

package com.pojo;

import java.util.List;

public class Area {
	private int id;
	private String name;
	private List<Team> teams;
	
	public Area() {
	}
	public Area(int id, String name) {
		this.id = id;
		this.name = name;
	}
	
	public List<Team> getTeams() {
		return teams;
	}
	public void setTeams(List<Team> teams) {
		this.teams = teams;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Area [id=" + id + ", name=" + name + ", teams=" + teams + "]";
	}
}

3、配置Area.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">
<!-- dao的配置 mapper就是dao data access object -->
<!-- dao对pojo包全包扫描 工作空间 -->
<mapper namespace="com.pojo">
	<!-- 自定义表与实体的结构对应类 -->
	<!-- column代表表的字段     property代表类的变量  -->
	<resultMap type="Area" id="baseResultMap">
		<!-- 所有表的主键标签都是id -->
		<id column="id" property="id" />
		<result column="name" property="name" />
		<!-- 一对多的关系  list集合 -->
		<!-- property: 指的是集合属性的值, ofType：指的是集合中元素的类型 -->
		<collection property="teams" ofType="Team">
			<id column="tid" property="id" />
			<result column="tname" property="name" />
		</collection>
	</resultMap>
	
	<select id="getAreas" resultMap="baseResultMap">
		select area.id,area.`name`,team.id tid,team.`name` tname
		from area
		inner join team on area.id=team.aid
	</select>
	
	

</mapper>

4、测试类

//一对多查询
	@Test
	public void queryOneToManyTest() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();

		List<Area> areas=session.selectList("getAreas");
		for(Area area:areas) {
			System.out.println(area);
			List<Team> lists=area.getTeams();
			for(Team team:lists) {
				System.out.println(team);
			}
		}
		
		session.commit();
		session.close();
	}

6、多对一

1、Team.java

package com.pojo;

public class Team {
	private int id;
	private String name;
	private Area area;
	public Team() {
	}
	public Team(int id, String name) {
		this.id = id;
		this.name = name;
	}
	public Area getArea() {
		return area;
	}
	public void setArea(Area area) {
		this.area = area;
	}
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	@Override
	public String toString() {
		return "Team [id=" + id + ", name=" + name + "]";
	}
	
}

2、Team.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">
<!-- dao的配置 mapper就是dao data access object -->
<!-- dao对pojo包全包扫描 工作空间 -->
<mapper namespace="com.pojo">
	<!-- 自定义表与实体的结构对应类 -->
	<!-- column代表表的字段     property代表类的变量  -->
	<resultMap type="Team" id="teamBaseResultMap">
		<!-- 所有表的主键标签都是id -->
		<id column="id" property="id" />
		<result column="name" property="name" />
		<!-- 多对一  -->
		<association property="area" javaType="Area">
			<id column="aid" property="id"/>
			<result column="aname" property="name"/>
		</association>
	</resultMap>
	
	<select id="getTeamByName" parameterType="string" resultMap="teamBaseResultMap">
		select team.id,team.name,area.id aid,area.name aname
		from team inner join area on team.aid=area.id and team.name=#{0}
	</select>

</mapper>

3、测试

//多对一查询
	@Test
	public void queryManyToOneTest() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();

		Team team=session.selectOne("getTeamByName","老虎队");
		System.out.println(team+""+team.getArea());
		
		session.commit();
		session.close();
	}

7、多对多查询----查看订单下挂的不同产品

Order.java

package com.pojo;

import java.util.List;

public class Order {
    private int id;
    private String code;
     
    List<OrderItem> orderItems;
     
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public List<OrderItem> getOrderItems() {
        return orderItems;
    }
    public void setOrderItems(List<OrderItem> orderItems) {
        this.orderItems = orderItems;
    }
 
}

Product.java

package com.pojo;

public class Product {
    private int id;
    private String name;
    private float price;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    @Override
    public String toString() {
        return "Product [id=" + id + ", name=" + name + ", price=" + price + "]";
    }
 
}

OrderItem.java

package com.pojo;

public class OrderItem {
	private int id;
	private int number;
	private Order order;
	private Product product;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public int getNumber() {
		return number;
	}
	public void setNumber(int number) {
		this.number = number;
	}
	public Order getOrder() {
		return order;
	}
	public void setOrder(Order order) {
		this.order = order;
	}
	public Product getProduct() {
		return product;
	}
	public void setProduct(Product product) {
		this.product = product;
	}
	
}

Order.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="com.pojo">
	<resultMap type="Order" id="orderBean">
		<id column="oid" property="id" />
		<result column="code" property="code" />

		<collection property="orderItems" ofType="OrderItem">
			<id column="oiid" property="id" />
			<result column="number" property="number" />
			<association property="product" javaType="Product">
				<id column="pid" property="id" />
				<result column="pname" property="name" />
				<result column="price" property="price" />
			</association>
		</collection>
	</resultMap>

	<select id="listOrder" resultMap="orderBean">
		select o.*,p.*,oi.*, o.id 'oid', p.id 'pid', oi.id 'oiid', p.name 'pname'
		from `order` o
		left join order_item oi on o.id =oi.oid
		left join product p on p.id = oi.pid
	</select>

</mapper>

8、多对多查询----查看产品对应的的订单信息

1、测试类

// 多对多查询----查看产品对应的的订单信息
	@Test
	public void queryManyToMach() {
		SqlSession session = sqlSessionFactory.openSession();
		
		List<Product> ps = session.selectList("listProducts");
		for (Product p : ps) {
			System.out.println(p.getName());
			List<OrderItem> ois = p.getOrderItems();
			for (OrderItem oi : ois) {
				System.out.format("\t%s\t%d%n", oi.getOrder().getCode(),oi.getNumber());
			}
			
		}
		
		session.commit();
		session.close();
		
	}

Product.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="com.pajo">
	<!-- 自定义表与实体类 -->
	<resultMap type="Product" id="productBean">
		<!-- column代表表的字段，property代表类的变量 -->
		<id column="id" property="id"/>
		<result column="name" property="name"/>
		<result column="price" property="price"/>
		<!-- property表示集合的值，ofType表示集合中元素的类型  -->
		<collection property="orderItems" ofType="OrderItem">
			<id column="orderItem_id" property="id"/>
			<result column="number" property="number"/>
			<!-- property表示变量， javaType表示变量的类型 -->
			<association property="order" javaType="Order">
				<id column="order_id" property="id"/>
				<result column="code" property="code"/>
			</association>
		</collection>
	</resultMap>

	<select id="listProducts" resultMap="productBean">
		select p.*,o.*,oi.*, oi.id 'orderItem_id', o.id 'order_id'
		from product p
		left join order_item oi on p.id = oi.pid
		left join `order` o on o.id = oi.oid 
		order by oi.pid 
	</select>


</mapper>

Product.java

package com.pojo;

import java.util.List;

public class Product {
    private int id;
    private String name;
    private float price;
    
    List<OrderItem> orderItems;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public float getPrice() {
        return price;
    }
    public void setPrice(float price) {
        this.price = price;
    }
    public List<OrderItem> getOrderItems() {
		return orderItems;
	}
	public void setOrderItems(List<OrderItem> orderItems) {
		this.orderItems = orderItems;
	}
	@Override
	public String toString() {
		return "Product [id=" + id + ", name=" + name + ", price=" + price + ", orderItems=" + orderItems + "]";
	}
	
 
}

9、总结

1、一对多

<collection property="" ofType=""></collection>

2、多对一

<association property="" javaType=""></association>

10、if语句拼接

Product.xml配置

	<!-- if语句拼接 -->
	<select id="listProduct_if" parameterType="map" resultType="Product">
		select * from product
		<if test="name!=null">
			where name like concat('%',#{name},'%')
		</if>
	</select>

测试类

package com.test;

import java.io.IOException;
import java.io.InputStream;
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.Test;

import com.pojo.Product;

public class SqlTest {

	// 拿到框架环境配置文件
		static String resource = "mybatis-config.xml";
		// org.apache.ibatis.io.Resources
		// 读取配置信息到输入流
		static InputStream inputStream = null;
		static {
			try {
				inputStream = Resources.getResourceAsStream(resource);
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		// 建造者模式，创建工厂，工厂用于创建sql会话
		static SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

		// if语句拼接
		@Test
		public void ifTest() {
			SqlSession session = sqlSessionFactory.openSession();
			
			System.out.println("全部查询");
			List<Product> ps = session.selectList("listProduct_if");
			for (Product p: ps) {
				System.out.println(p);
			}
			
			System.out.println("模糊查询");
			Map<String, Object> params = new HashMap<>();
			params.put("name", "a");
			List<Product> ps2 = session.selectList("listProduct_if",params);
			for (Product p : ps2) {
				System.out.println(p);
			}
			
			session.commit();
			session.close();
		}
		
		
		
		
		
}

11、where语句拼接

1、xml的配置：where语句拼接 去掉的第一个and，增加where关键字

	<!-- where语句拼接	去掉的第一个and，增加where关键字 -->
	<select id="listProduct_where" parameterType="map" resultType="Product">
		select * from product
		<where>
			<if test="name!=null">
				and name like concat('%',#{name},'%')
			</if>
			<if test="price!=null and price!=0">
				and price > #{price}
			</if>
		</where>
	</select>


// where语句拼接
	@Test
	public void whereTest() {
		SqlSession session = sqlSessionFactory.openSession();

		System.out.println("全部查询");
		List<Product> ps = session.selectList("listProduct_where");
		for (Product p : ps) {
			System.out.println(p);
		}

		System.out.println("模糊查询");
		Map<String, Object> params = new HashMap<>();
		params.put("name", "a");
		params.put("price", 10);
		List<Product> ps2 = session.selectList("listProduct_where", params);
		for (Product p : ps2) {
			System.out.println(p);
		}

		session.commit();
		session.close();
	}

12、set标签

<!-- set语法，表字段自定义 -->
	<update id="updateProduct" parameterType="Product">
		update product
		<set>
			<if test="name != null">name=#{name},</if>
			<if test="price != null">price=#{price}</if>
		</set>
		where id=#{id}
	</update>

13、trim标签

	<!-- trim制定标签 -->
	<select id="listProduct_trim" resultType="Product">
		select * from product
		<!-- prerfix:现在是where语法，在所有的条件前加上关键字where
			prefixOverrides：对第一个and 或者是Or 进行删除操作
			 -->
		<trim prefix="where" prefixOverrides="and | or ">
			<if test="name!=null">
				and name like concat('%',#{name},'%')
			</if>
			<if test="price!=null and price!=0">
				and price > #{price}
			</if>
		</trim>
	</select>
	
	<update id="updateProduct_trim" parameterType="Product">
		update product
		<trim prefix="set" suffixOverrides=",">
			<if test="name!=null">name=#{name},</if>
			<if test="price!=null">price=#{price}</if>
		</trim>
		where id=#{id}
	</update>

// trim标签
		@Test
		public void trimTest() {
			SqlSession session = sqlSessionFactory.openSession();

			System.out.println("多条件查询");
			Map<String, Object> params = new HashMap<>();
//			params.put("name", "a");
			params.put("price", 10);
			List<Product> ps2 = session.selectList("listProduct_trim", params);
			for (Product p : ps2) {
				System.out.println(p);
			}
			
			Product p = new Product();
			p.setId(6);
			p.setName("product zzz");
			p.setPrice(99.99f);
			session.update("updateProduct_trim",p);

			session.commit();
			session.close();
		}

14、choose when标签

<!-- choose when 标签 -->
<select id="listProduct_cw" resultType="Product">
	SELECT * FROM product
	<where>
		<choose>
			<when test="name != null">
				and name like concat('%',#{name},'%')
			</when>
			<when test="price !=null and price != 0">
				and price >= #{price}
			</when>
			<otherwise>
				and id >1
			</otherwise>
		</choose>
	</where>
</select>

// choose-when语句 if-else效果，执行一个
@Test
public void chooseWhenTest() {
	SqlSession session = sqlSessionFactory.openSession();

	Map<String, Object> params = new HashMap<>();
	params.put("name", "a");
	params.put("price", 99);
	List<Product> ps = session.selectList("listProduct_cw", params);
	for (Product p : ps) {
		System.out.println(p);
	}

	session.commit();
	session.close();
}

15、foreach 拼接标签

<!-- foreach item-list中的元素，index代表计数，list代表传入集合 -->
	<select id="listProduct_for" resultType="Product">
		SELECT * FROM product
		WHERE ID in
		<!-- (1,2,3) list-item-【index】 (item【0】,item[1]..item[length-1]) -->
		<foreach item="item" index="index" collection="list" open="("
			separator="," close=")">
			#{item}
		</foreach>
	</select>

// foreach 拼接标签
	@Test
	public void foreachTest() {
		SqlSession session = sqlSessionFactory.openSession();

		List<Integer> ids = new ArrayList();
		ids.add(1);
		ids.add(3);
		ids.add(5);

		List<Product> ps = session.selectList("listProduct_for", ids);
		for (Product p : ps) {
			System.out.println(p);
		}

		session.commit();
		session.close();
	}

16、bind标签

// bind标签
	@Test
	public void bindTest() {
		SqlSession session = sqlSessionFactory.openSession();

		Map<String, String> params = new HashMap();
		params.put("name", "x");

		List<Product> ps = session.selectList("listProduct_bind", params);
		for (Product p : ps) {
			System.out.println(p);
		}
		session.commit();
		session.close();
	}


	<!-- bind语法 等同于再做一次字符串拼接 -->
	<!-- 本来的模糊查询方式 -->
	<!-- <select id="listProduct" resultType="Product"> -->
	<!-- select * from product_ where name like concat('%',#{0},'%') -->
	<!-- </select> -->
	<select id="listProduct_bind" resultType="Product">
		<bind name="likename" value="'%' + name + '%'" />
		select * from product where name like #{likename}
	</select>

17、注解—OrderMapper中的方法名和xml中的id一样

1、OrderMapper.java

@Param("oname") 取别名 
package com.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.pojo.Order;

public interface OrderMapper {

	//传入名称模糊查询
	public List<Order> listByName(@Param("oname")String name);
	
}

2、OrderMapper.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">
<!-- dao的配置 mapper就是dao data access object -->
<!-- dao对pojo包全包扫描 工作空间 -->
<mapper namespace="com.dao.OrderMapper">
	<select id="listByName" parameterType="string"
		resultType="com.pojo.Order">
		select * from `order`
		<if test="oname!=null">
			where code like concat('%',#{oname},'%')
		</if>
	</select>
</mapper>

	@Test
	public void daoTest() {
		SqlSession session = sqlSessionFactory.openSession();

		OrderDao orderDao=session.getMapper(OrderDao.class);//mapper==dao
		List<Order> orders=orderDao.listAll();
		for(Order order:orders) {
			System.out.println(order);
		}
		
		OrderMapper orderMapper=session.getMapper(OrderMapper.class);
		List<Order> orders2=orderMapper.listByName("A");
		for(Order order:orders2) {
			System.out.println(order);
		}
		
		session.commit();
		session.close();
	}

18、日志

1、配置内容

log4j.rootLogger=debug, stdout, R
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
# Pattern to output the caller's file name and line number.
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n
log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.File=example.log
log4j.appender.R.MaxFileSize=100KB
log4j.appender.R.MaxBackupIndex=5
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n

2、log4j的配置解释：

设置日志输出的等级为debug,低于debug就不会输出了
设置日志输出到两种地方，分别叫做 stdout和 R
log4j.rootLogger=debug, stdout, R
 
第一个地方stdout, 输出到控制台
log4j.appender.stdout=org.apache.log4j.ConsoleAppender

输出格式是 %5p [%t] (%F:%L) - %m%n, 格式解释在下个步骤讲解
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] (%F:%L) - %m%n

第二个地方R, 以滚动的方式输出到文件，文件名是example.log,文件最大100k, 最多滚动5个文件
log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.File=example.log
log4j.appender.R.MaxFileSize=100KB
log4j.appender.R.MaxBackupIndex=5
 
输出格式是 %p %t %c - %m%n, 格式解释在下个步骤讲解
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%p %t %c - %m%n

3、log4j日志输出格式总览：

%c 输出日志信息所属的类的全名
%d 输出日志时间点的日期或时间，默认格式为ISO8601，也可以在其后指定格式，比如：%d{yyy-MM-dd HH:mm:ss }，输出类似：2020-10-18- 22：10：28
%f 输出日志信息所属的类的类名
%l 输出日志事件的发生位置，即输出日志信息的语句处于它所在的类的第几行
%m 输出代码中指定的信息，如log(message)中的message
%n 输出一个回车换行符，Windows平台为“rn”，Unix平台为“n”
%p 输出优先级，即DEBUG，INFO，WARN，ERROR，FATAL。如果是调用debug()输出的，则为DEBUG，依此类推
%r 输出自应用启动到输出该日志信息所耗费的毫秒数
%t 输出产生该日志事件的线程名

所以：
%5p [%t] (%F:%L) - %m%n 就表示
宽度是5的优先等级 线程名称 (文件名:行号) - 信息 回车换行
# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com=trace
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
# Pattern to output the caller's file name and line number.
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

19、mybatis整合c3p0

1、jar包

2、定义一个c3p0的连接类

C3P0DataSourceFactory.java

package com.utils;

import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0DataSourceFactory extends UnpooledDataSourceFactory {
	public C3P0DataSourceFactory() {
		this.dataSource = new ComboPooledDataSource();
	}
}

3、替换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>
	<settings>
		<!-- 打开延迟加载的开关 -->
		<setting name="lazyLoadingEnabled" value="true" />
		<!-- 将积极加载改为消息加载即按需加载 -->
		<setting name="aggressiveLazyLoading" value="false" />
	</settings>
	<!-- 别名，自动扫描指定包下类型，这个配置，可以让Person.xml的resultType无需去写完整的类路径 -->
	<typeAliases>
		<package name="com.pojo" />
	</typeAliases>
	<!-- 连接池的基础配置 -->
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="com.utils.C3P0DataSourceFactory">
				<property name="driverClass" value="com.mysql.jdbc.Driver" />
				<property name="jdbcUrl"
					value="jdbc:mysql://localhost:3306/demo?useUnicode=true&amp;characterEncoding=UTF-8&amp;autoReconnect=true&amp;failOverReadOnly=false" />
				<property name="user" value="root" />
				<property name="password" value="root" />
				<!-- 连接池初始化大小为3 -->
				<property name="initialPoolSize" value="3" />
				<!-- 连接池最大为10 -->
				<property name="maxPoolSize" value="10" />
				<!-- 连接池最小为3 -->
				<property name="minPoolSize" value="3" />
				<!-- 连接池在无空闲连接可用时一次性最多创建的新数据库连接数 -->
				<property name="acquireIncrement" value="5" />
				<!-- 连接的最大空闲时间，如果超过这个时间（秒），某个数据库连接还没有被使用，则会断开掉这个连接。如果为0，则永远不会断开连接,即回收此连接 -->
				<property name="maxIdleTime" value="30" />

				<!-- 最大的Statement数量 -->
				<property name="maxStatements" value="500" />
				<!-- 每个连接启动的最大Statement数量 -->
				<property name="maxStatementsPerConnection" value="50" />
				<!-- 同时运行的线程数 -->
				<property name="numHelperThreads" value="5" />
			</dataSource>
		</environment>
	</environments>
	<!-- dao层配置 -->
	<mappers>
		<mapper class="com.dao.OrderDao" />
		<mapper resource="com/pojo/Product.xml" />
		<mapper resource="com/dao/ProductMapper.xml" />
		<mapper class="com.dao.ProductDao" />
		<mapper resource="com/pojo/Area.xml" />
		<mapper resource="com/dao/OrderMapper.xml" />
		<mapper resource="com/dao/ClassRoomMapper.xml" />
		<mapper resource="com/dao/ClassRoomCustomMapper.xml" />
		<!-- <mapper resource="com/pojo/Person.xml"/> -->
		<!-- <mapper resource="com/pojo/Team.xml"/> --><!-- <mapper resource="com/pojo/Order.xml"/> <mapper class="com.dao.TeamMapper"/> -->
	</mappers>
</configuration>

20、逆向工程

自动生成pojo的实体类中加序列化，和Mapper，简单的sql

1.jar包mybatis-generator-core-1.3.5.jar

2.在src下配置generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
  PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
  "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <!--数据库驱动-->
    <context id="DB2Tables"    targetRuntime="MyBatis3">
        <commentGenerator>
            <property name="suppressDate" value="true"/>
            <property name="suppressAllComments" value="false"/>
        </commentGenerator>
        <!--数据库链接地址账号密码-->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost/demo" userId="root" password="root">
        </jdbcConnection>
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!--生成Model类存放位置-->
        <javaModelGenerator targetPackage="com.pojo" targetProject="src">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!--生成映射文件存放位置-->
        <sqlMapGenerator targetPackage="com.dao" targetProject="src">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!--生成Dao类存放位置-->
         
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.dao" targetProject="src">
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!--生成对应表及类名
        example是sql生成语法，设置为false之后，只能手工sql
        -->
        <table tableName="classroom" domainObjectName="ClassRoom" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="false" selectByExampleQueryId="false">
            <!-- 使用自增长键 -->
            <property name="my.isgen.usekeys" value="true"/>
            <generatedKey column="id" sqlStatement="JDBC"/>       
        </table>
    </context>
</generatorConfiguration>

3、配置TestMybatisGenerator.java

package com.generator;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.mybatis.generator.api.MyBatisGenerator;
import org.mybatis.generator.config.Configuration;
import org.mybatis.generator.config.xml.ConfigurationParser;
import org.mybatis.generator.internal.DefaultShellCallback;
 
public class TestMybatisGenerator {
 
    public static void main(String[] args) throws Exception {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        InputStream is= TestMybatisGenerator.class.getClassLoader().getResource("generatorConfig.xml").openStream();
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(is);
        is.close();
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
        System.out.println("生成代码成功，刷新项目，查看文件");
         
    }
}

4、测试类

//curdplus
	@Test
	public void plusTest()throws Exception{
		//使用工厂创建产品   java建立和db的联系，然后sql语句操作，关闭联系
		SqlSession session=sqlSessionFactory.openSession();
		ClassRoomMapper mapper=session.getMapper(ClassRoomMapper.class);

		//一级缓存，查询同一个内容只查一次，第二次从session中取
	    ClassRoom room=mapper.selectByPrimaryKey(1);
		System.out.println(room);
	    ClassRoom room2=mapper.selectByPrimaryKey(1);
		System.out.println(room2);
		
//		ClassRoomCustomMapper classRoomCustomMapper=session.getMapper(ClassRoomCustomMapper.class);
//		
//		List<ClassRoom> rooms=classRoomCustomMapper.listAll();
//		for(ClassRoom tmp:rooms) {
//			System.out.println(tmp);
//		}
//		ClassRoom room2=new ClassRoom();
//		room2.setCname("135教室");
//		room2.setCid("707");
//		mapper.insertSelective(room2);

//		room.setCname("java班");
//		mapper.updateByPrimaryKeySelective(room);
	    
//		mapper.deleteByPrimaryKey(3);
		
        session.commit();
        session.close();
        
        SqlSession session2=sqlSessionFactory.openSession();
        ClassRoom room3=session2.selectOne("selectByPrimaryKey",1);
        System.out.println(room3);
//		ClassRoomMapper mapper2=session2.getMapper(ClassRoomMapper.class);
//	    ClassRoom room3=mapper2.selectByPrimaryKey(1);
//		System.out.println(room3);
	}

4、自定义---ClassRoomCustomMapper.java
package com.dao;

import java.util.List;

import com.pojo.ClassRoom;

//自定义的dao
public interface ClassRoomCustomMapper {
	
	List<ClassRoom> listAll();
	
}

5、配置----ClassRoomCustomMapper.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="com.dao.ClassRoomCustomMapper">
  <!-- 二级缓存开启 -->
  
  <resultMap id="BaseResultMap" type="com.pojo.ClassRoom">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="cid" jdbcType="VARCHAR" property="cid" />
    <result column="cname" jdbcType="VARCHAR" property="cname" />
  </resultMap>
  <sql id="Base_Column_List">
    id, cid, cname
  </sql>
  <select id="listAll" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from classroom
  </select>
</mapper>

21、缓存

1、一级缓存：缓存在session中

	@Test
	public void cacheTest() {
		SqlSession session=sqlSessionFactory.openSession();
		ClassRoomMapper mapper=session.getMapper(ClassRoomMapper.class);
		//一级缓存  sql执行结果会存储在session中
		ClassRoom classRoom1=mapper.selectByPrimaryKey(1);
		System.out.println(classRoom1);
		//session中直接取值出来
		ClassRoom classRoom2=mapper.selectByPrimaryKey(1);
		System.out.println(classRoom2);
		
        session.commit();
        session.close();

2、二级缓存：缓存到factory中

 //二级缓存，是sessionfactory起作用，sql执行结果存储在factory中，重新搞了一次session后，
        //直接冲factory中取出sql查询值
		SqlSession session2=sqlSessionFactory.openSession();
		ClassRoomMapper mapper2=session2.getMapper(ClassRoomMapper.class);
		ClassRoom classRoom3=mapper2.selectByPrimaryKey(1);
		System.out.println(classRoom3);

	    session2.commit();
	    session2.close();
	}

3、需要在作用的Mapper.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="com.dao.ClassRoomCustomMapper">
  <!-- 二级缓存开启 -->
  <cache/>
  <resultMap id="BaseResultMap" type="com.pojo.ClassRoom">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="cid" jdbcType="VARCHAR" property="cid" />
    <result column="cname" jdbcType="VARCHAR" property="cname" />
  </resultMap>
  <sql id="Base_Column_List">
    id, cid, cname
  </sql>
  <select id="listAll" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from classroom
  </select>
</mapper>

4、mybatis-config.xml

<settings>
		<!-- 打开二级缓存 -->
 		<setting name="cacheEnabled" value="true"/>
		<!-- 打开延迟加载的开关 -->
		<setting name="lazyLoadingEnabled" value="true" />
		<!-- 将积极加载改为消息加载即按需加载 -->
		<setting name="aggressiveLazyLoading" value="false" />
	</settings>