SqlMapConfig.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>
<!-- 和spring整合后 environments配置将废除 -->
<environments default="development">
<environment id="development">
<!-- 使用jdbc事务管理 -->
<transactionManager type="JDBC" />
<!-- 数据库连接池 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url"
value="jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8" />
<property name="username" value="root" />
<property name="password" value="mysql" />
</dataSource>
</environment>
</environments>
<!-- 加载mapper.xml -->
<!-- 扫描加载mapper.xml -->
<mappers>
<package name="com.kaisen.jdbc.mapper" />
</mappers>
</configuration>
mapper.xml映射文件
早期ibatis的映射文件以表名命名,后期mybatis 映射文件命名方式为:表名Mapper.xml。。
命名方式是不固定的。
映射文件使用mybatis-3-mapper.dtd
UserMapper.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映射文件是以sql语句为单位进行配置,最终将sql语句封装到MappedStatement对象中 namespace命名空间作用是更好对sql语句进行隔离,方便管理sql
注意:后期讲mybatis的mapper代理开发方式时namespace有特殊的作用??? -->
<mapper namespace="com.kaisen.jdbc.mapper.UserMapper">
<!-- 打开二级缓存 -->
<cache type="org.mybatis.caches.ehcache.EhcacheCache" >
<property name="timeToIdleSeconds" value="3600"/>
<property name="timeToLiveSeconds" value="3600"/>
<!-- 同ehcache参数maxElementsInMemory -->
<property name="maxEntriesLocalHeap" value="1000"/>
<!-- 同ehcache参数maxElementsOnDisk -->
<property name="maxEntriesLocalDisk" value="10000000"/>
<property name="memoryStoreEvictionPolicy" value="LRU"/>
</cache>
<!-- 根据用户id查询一个用户信息 select:用于查询,主要配置sql语句、输入参数类型、
输出结果类型 最终该 select 标签 中所配置的内容会封装到MappedStatement对象,可以将该
select称为是一个Statement id:唯 一标识 namespace下的一个sql语句,
将id称为Statement的id parameterType:指定输入参数的类型(简单类型、自定义pojo)
#{}:表示一个占位符号,占位符号可以防止sql注入 #{value}:value表示接收输入参数的值,
如果接收的输入参数是简单类型,#{}里边可以写value或其它的名称
resultType:将sql查询结果集映射成java对象 将多个列的值映射到一个对象中,需要定义的pojo,
resultType映射规则是sql查询列名和pojo的属性名必须一致方可完成映射
resultType 指定单条记录所映射的java对象 -->
<!-- 在statement中设置useCache=false可以禁用当前select语句的二级缓存,
即每次查询都会发出sql去查询,默认情况是true,即该sql使用二级缓存 -->
<!--增删改操作 flushCache="true" 属性,默认情况下为true即刷新缓存,
如果改成false则不会刷新。使用缓存时如果手动修改数据库表中的查询数据会出现脏读。 -->
<!-- 根据id查询用户 -->
<select id="findUserById" parameterType="int" resultType="user" useCache="false">
SELECT
id,username,birthday,sex,address FROM USER WHERE id = #{id}
</select>
<update id="updateUser" parameterType="user">
update user set username=#{username},address=#{address}
</update>
<!-- 根据用户名模糊查询用户 -->
<select id="findUserByName" parameterType="java.lang.String"
resultType="user">
SELECT id,username,birthday,sex,address FROM USER WHERE username like
#{name}
<!-- SELECT id,username,birthday,sex,address FROM USER WHERE username like
'%${value}%' -->
</select>
<!-- 输入参数类 -->
<!-- 根据包装类中的pojo属性查询数据 -->
<select id="findUserByQuerUserVoUser" parameterType="querUserVo"
resultType="user">
SELECT id,username,birthday,sex,address FROM USER WHERE id
= #{user.id}
</select>
<!-- 输入参数为hashmap 根据map的key取值 -->
<select id="findUserByHashMap" parameterType="hashmap"
resultType="user">
SELECT id,username,birthday,sex,address FROM USER WHERE sex
= '${sex}' and username like '%${username}%'
</select>
<!-- 输出参数映射 -->
<!-- 输出参数为简单类型 -->
<select id="findUserCount" parameterType="querUserVo"
resultType="int">
SELECT count(id) FROM USER WHERE username like
'%${user.username}%'
</select>
<!-- 输出参数为map 字段名与属性名不一致 -->
<!-- 定义resultMap type映射的pojo类 id 唯一标示 -->
<resultMap type="user" id="querUserResultMap">
<!--结果集中的唯一标示 column结果集中的列名 property 映射的pojo类的属性名 -->
<id column="id_" property="id" />
<!-- 普通列 -->
<result column="username_" property="username" />
<result column="birthday_" property="birthday" />
<result column="sex_" property="sex" />
<result column="address_" property="address" />
</resultMap>
<select id="findUserResultMap" parameterType="string" resultMap="querUserResultMap">
SELECT id id_,username username_,birthday birthday_,sex sex_,address
address_ FROM USER WHERE sex = #{sex}
</select>
<!-- 动态sql 语法 -->
<!-- 定义sql片段 -->
<sql id="findByUsernameAndSexSqlWhere">
<!-- 判断用户是否有值 -->
<if test="user!=null">
<if test="user.username!=null and user.username!=''">
and username like '%${user.username}%'
</if>
<if test="user.sex!=null and user.sex!=''">
and sex = #{user.sex}
</if>
</if>
</sql>
<!-- 综合条件查询 引用动态sql片段 -->
<select id="findUserList" parameterType="querUserVo" resultType="user">
SELECT id,username,birthday,sex,address FROM USER
<!-- 使用动态sql片段 where包裹 -->
<where>
<!-- 引入sql片段 -->
<include refid="findByUsernameAndSexSqlWhere"/>
</where>
</select>
<!-- foreach 循环遍历输入参数 -->
<!-- sql片段 -->
<sql id="foreachUserIds">
<!-- collection为要遍历的集合 item为遍历出来的每一个元素 open为开始循环时拼接的sql close为结束时拼接的sql separator每次循环中间拼接的sql -->
<foreach collection="ids" item="id" open="and id in(" close=")" separator=",">
#{id}
</foreach>
</sql>
<select id="findUsersByIds" parameterType="querUserVo" resultType="user">
SELECT id,username,birthday,sex,address FROM USER
<where>
<include refid="foreachUserIds"/>
</where>
</select>
<!-- 添加用户 -->
<!-- <insert id=""></insert> -->
<!-- 删除用户 -->
<!-- <delete id=""></delete> -->
</mapper>
OrderCustomerMapper.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映射文件是以sql语句为单位进行配置,最终将sql语句封装到MappedStatement对象中 namespace命名空间作用是更好对sql语句进行隔离,方便管理sql
注意:后期讲mybatis的mapper代理开发方式时namespace有特殊的作用??? -->
<mapper namespace="com.kaisen.jdbc.mapper.OrderCustomerMapper">
<!-- 使用resultType 完成 -->
<select id="findOrdersCustomer" resultType="com.kaisen.po.OrdersCustomer">
SELECT
orders.*,USER.username,USER.address FROM orders,USER WHERE
orders.user_id=USER.id
</select>
<!-- 使用resultMap完成 -->
<!-- 定义resultMap -->
<resultMap type="orders" id="ordersMap">
<!-- 唯一标是 -->
<id column="id" property="id" />
<!-- 属性 -->
<result column="user_id" property="userId" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
<result column="note" property="note" />
<!-- 关联映射单个对象 标签注意使用javaType指定关联映射的对象类型 -->
<association property="user" javaType="com.kaisen.po.User" >
<id column="user_id" property="id" />
<result column="username" property="username" />
<result column="address" property="address" />
</association>
</resultMap>
<!-- 查询订单 延迟加载用户信息 -->
<resultMap type="orders" id="ordersLazyingUserMap">
<!-- 唯一标是 -->
<id column="id" property="id" />
<!-- 属性 -->
<result column="user_id" property="userId" />
<result column="number" property="number" />
<result column="createtime" property="createtime" />
<result column="note" property="note" />
<!-- 关联映射单个对象 标签注意使用javaType指定关联映射的对象类型 -->
<association property="user" javaType="com.kaisen.po.User" select="com.kaisen.jdbc.mapper.UserMapper.findUserById" column="user_id">
</association>
</resultMap>
<select id="findOrdersLazingUser" resultMap="ordersLazyingUserMap">
select * from orders
</select>
<select id="findOrdersCustomerMap" resultMap="ordersMap">
SELECT
orders.*,user.username,user.address FROM orders,USER WHERE
orders.user_id=user.id
</select>
<!-- resultMap 多表关联多对多查询 -->
<!-- 继承resultMap -->
<resultMap type="orders" id="orderDetialMap" extends="ordersMap">
<!-- 关联映射集合对象 标签注意使用ofType指定关联映射的List中pojo的类型。-->
<collection property="orderdetails" ofType="com.kaisen.po.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="id" property="ordersId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
</collection>
</resultMap>
<!-- 查询订单关联的用户与明细 -->
<select id="findOrdersCustomerOrderDetilsMap" resultMap="orderDetialMap">
SELECT
orders.*,
user.username,
user.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num
FROM orders,USER,orderdetail WHERE
orders.user_id=user.id and orderdetail.orders_id = orders.id
</select>
<!-- 关联查询用户对应的订单 订单对应的明细 明细对应的商品信息 -->
<!-- 定义resultMap -->
<resultMap type="user" id="userOrdersDetailsItemsMap">
<id column="user_id" property="id"/>
<result column="username" property="username"/>
<result column="address" property="address"/>
<!-- 用户关联的订单表 一对多 -->
<collection property="orderslist" ofType="com.kaisen.po.Orders">
<id column="id" property="id"/>
<result column="user_id" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<result column="note" property="note"/>
<!-- 订单关联的订单明细表 一对多-->
<collection property="orderdetails" ofType="com.kaisen.po.Orderdetail">
<id column="orderdetail_id" property="id"/>
<result column="id" property="ordersId"/>
<result column="items_id" property="itemsId"/>
<result column="items_num" property="itemsNum"/>
<!-- 订单明细表关联的商品信息表 一对一-->
<association property="items" javaType="com.kaisen.po.Items">
<id column="items_id" property="id"/>
<result column="name" property="name"/>
<result column="price" property="price"/>
<result column="detail" property="detail"/>
<result column="pic" property="pic"/>
<result column="items_createtime" property="createtime"/>
</association>
</collection>
</collection>
</resultMap>
<select id="findUserOrdersDetailsItemsMap" resultMap="userOrdersDetailsItemsMap">
SELECT
orders.*,
user.username,
user.address,
orderdetail.id orderdetail_id,
orderdetail.items_id,
orderdetail.items_num,
items.name,
items.price,
items.detail,
items.pic,
items.createtime items_createtime
FROM orders,USER,orderdetail,items WHERE
orders.user_id=user.id
and orderdetail.orders_id = orders.id
and orderdetail.items_id = items.id
</select>
</mapper>
pojo类
User类
public class User implements Serializable{
private int id;
private String username;// 用户姓名
private String sex;// 性别
private Date birthday;// 生日
private String address;// 地址
private List<Orders> orderslist;
实现getter和setter方法
{
Orders类与OrdersCustomer类
public class Orders implements Serializable{
private Integer id;
private Integer userId;
private String number;
private Date createtime;
private String note;
private User user;
private List<Orderdetail> orderdetails;
}
public class OrdersCustomer extends Orders {
//继承了orders类
private String username;
private String address;
{
定义接口管理方法(接口名与配置mapper名一致且同路径)
public interface OrderCustomerMapper {
List<OrdersCustomer> findOrdersCustomer() throws Exception;
List<Orders> findOrdersCustomerMap() throws Exception;
List<Orders> findOrdersCustomerOrderDetilsMap() throws Exception;
List<User> findUserOrdersDetailsItemsMap() throws Exception;
List<Orders> findOrdersLazingUser() throws Exception;
}
public interface UserMapper {
public User findUserById(Integer id) throws Exception;
public List<User> findUserByName(String name) throws Exception;
public User findUserByQuerUserVoUser(QuerUserVo quervo) throws Exception;
public List<User> findUserByHashMap(Map< String, Object> map) throws Exception;
public int findUserCount(QuerUserVo quervo) throws Exception;
public List<User> findUserResultMap(String sex) throws Exception;
public List<User> findUserList(QuerUserVo quervo) throws Exception;
public List<User> findUsersByIds(QuerUserVo quer) throws Exception;
public void updateUser(User user) throws Exception;
}
测试
public class TestForDao {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws IOException {
// mybatis全局配置文件
String resource = "SqlMapConfig.xml";
// 根据mybatis的全局配置文件构造 一个流
InputStream inputStream = Resources.getResourceAsStream(resource);
// 创建SqlSessionFactory
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
// 使用dao查询
@Test
public void testForFind() {
UserDao userDao = new UserDaoImpl(sqlSessionFactory);
try {
User user = userDao.findByUserId(1);
System.out.println(user);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 使用代理先查询
@Test
public void testForFind1() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
try {
User user = mapper.findUserById(1);
System.out.println(user);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 使用代理查询
@Test
public void testForFind2() {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// assert mapper==null;
try {
// 条件查询
// List<User> list = mapper.findUserByName("张三");
// 模糊查询
List<User> list = mapper.findUserByName("%张%");
System.out.println(list);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 根据包装类中的pojo属性查询
@Test
public void testForFindUserByVo() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// assert mapper==null;
QuerUserVo quer = new QuerUserVo();
//设置user的id
User use = new User();
use.setId(1);
quer.setUser(use);
//查询
User user = mapper.findUserByQuerUserVoUser(quer);
System.out.println(user);
}
//输入参数为map 是查询用户
@Test
public void testForFindUserByHashMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
// assert mapper==null;
//设置map
Map<String, Object> map = new HashMap<String, Object>();
map.put("sex", "男");
map.put("username", "张");
List<User> list = mapper.findUserByHashMap(map);
System.out.println(list);
}
//输出参数为简单类型 是查询用户findUserCount
@Test
public void testForFindUserCount() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//设置queruservo
QuerUserVo quer = new QuerUserVo();
User user = new User();
user.setUsername("张");
quer.setUser(user);
int count = mapper.findUserCount(quer);
System.out.println(count);
}
//输出参数为resultmap映射的pojo类 是查询用户findUserResultMap
@Test
public void testForFindUserResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//设置queruservo
List<User> list = mapper.findUserResultMap("男");
System.out.println(list);
}
//综合条件查询findUserList
@Test
public void testForFindUserList() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//设置queruservo
QuerUserVo quer = new QuerUserVo();
User user = new User();
user.setUsername("张");
user.setSex("男");
quer.setUser(user);
List<User> list = mapper.findUserList(quer);
System.out.println(list);
}
//综合条件查询findUsersByIds 遍历id集合查询
@Test
public void testForFindUsersByIds() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//设置queruservo
QuerUserVo quer = new QuerUserVo();
quer.getIds().add("1");
quer.getIds().add("16");
quer.getIds().add("22");
List<User> list = mapper.findUsersByIds(quer);
System.out.println(list);
}
//多表联合查询 一对一
@Test
public void testForFindOrdersCustomer() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
//设置queruservo
List<OrdersCustomer> list = mapper.findOrdersCustomer();
System.out.println(list);
}
//多表联合查询 数据通过resultMap封装到ordersCustomer中
@Test
public void testForFindOrdersCustomerResultMap() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
//设置queruservo
List<Orders> list = mapper.findOrdersCustomerMap();
System.out.println(list);
}
//多表联合查询 数据通过resultMap封装到orders中
@Test
public void testForFindOrdersCustomerOrderdetails() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
//设置queruservo
List<Orders> list = mapper.findOrdersCustomerOrderDetilsMap();
System.out.println(list);
}
//多表联合查询 数据通过resultMap封装到user中
@Test
public void testForFindUserOrdersDetailsItems() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
//设置queruservo
List<User> list = mapper.findUserOrdersDetailsItemsMap();
System.out.println(list);
}
//延迟加载
@Test
public void testForFindOrdersLazingUser() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderCustomerMapper mapper = sqlSession.getMapper(OrderCustomerMapper.class);
//设置queruservo
List<Orders> list = mapper.findOrdersLazingUser();
User user = list.get(0).getUser();
System.out.println(user);
System.out.println(list);
}
//测试一级缓存
@Test
public void testForFindUserSessionsqlCache() throws Exception {
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.findUserById(1);
user.setUsername("王十九");
mapper.updateUser(user);
sqlSession.commit();
user = mapper.findUserById(1);
System.out.println(user);
}
//测试二级缓存
@Test
public void testForFindUserMapperCache() throws Exception {
SqlSession sqlSession1 = sqlSessionFactory.openSession();
SqlSession sqlSession2 = sqlSessionFactory.openSession();
SqlSession sqlSession3= sqlSessionFactory.openSession();
SqlSession sqlSession4 = sqlSessionFactory.openSession();
UserMapper mapper1 = sqlSession1.getMapper(UserMapper.class);
UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
UserMapper mapper3 = sqlSession3.getMapper(UserMapper.class);
UserMapper mapper4 = sqlSession4.getMapper(UserMapper.class);
//使用sqlsession1查询
User user = mapper1.findUserById(1);
System.out.println(user);
//关闭sqlsession1 数据会写入二级缓存
sqlSession1.close();
/*user.setUsername("王十九");
mapper.updateUser(user);
sqlSession.commit();*/
//使用sqlsession2查询
User user2 = mapper2.findUserById(1);
System.out.println(user2);
}
/**
*
* @Title: testForfOR @Description: TODO(跳出双层for循环) @param 参数 @return void
* 返回类型 @throws
*/
@Test
public void testForfOR() {
for循环的名字: for (int i = 0; i < 10; i++) {
for (int j = 0; j < 10; j++) {
System.out.println(i + "---" + j);
if (i == 4) {
System.out.println("=================================");
break for循环的名字;
}
}
}
}
}
个人笔记整理