mybatis+spring
http://73677ebe.wiz03.com/share/s/1PpTW-2nTkUu2dDZix2UMXBy0outv123rAcl2CDlKN2mR73p
<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/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.ilucky.mybatis</groupId>
<artifactId>mybatis-util</artifactId>
<version>1.0-SNAPSHOT</version>
<packaging>war</packaging>
<name>mybatis-util</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<org.springframework>4.1.1.RELEASE</org.springframework>
</properties>
<dependencies>
<!-- spring -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aspects</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-expression</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-instrument</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-instrument-tomcat</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jms</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-messaging</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-orm</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-oxm</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc-portlet</artifactId>
<version>${org.springframework}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-websocket</artifactId>
<version>${org.springframework}</version>
</dependency>
<!-- mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<!-- commons -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.2</version>
</dependency>
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.4</version>
<scope>provided</scope>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.18</version>
</dependency>
<!-- log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- bonecp -->
<dependency>
<groupId>com.jolbox</groupId>
<artifactId>bonecp</artifactId>
<version>0.8.0.RELEASE</version>
</dependency>
</dependencies>
</project>
package com.ilucky.mybatis;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;
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.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.ilucky.mybatis.model.User;
import com.ilucky.mybatis.model.UserType;
import com.ilucky.mybatis.service.UserService;
import com.ilucky.mybatis.util.IdUtil;
/**
* v1.0:20161117
* @author IluckySi
* @since 20151013
* mybatis基础使用,mybatis+spring集成.
*/
public class MainTest {
private static Logger logger = Logger.getLogger(MainTest.class);
public static ClassPathXmlApplicationContext context;
public static UserService userService;
public static void main(String[] args) {
// 简单测试.
// test();
// 初始化环境.
if(!initEnv()) {
return;
}
//获取service bean.
userService = (UserService) context.getBean("userService");
// 保存单个对象.
// 注意: 如果我们传入到Mapper层的参数是一个对象,mybatis会自动将其转换成Map集合,key=对象字段名称,value=对象字段值.
// createUser();
// 保存多个对象.
// 注意: 如果我们传入到Mapper层的参数一个是List集合,mybatis会自动将其封装成Map集合,且key="list",value=List集合.
// 同时注意: 如果List集合中是对象,则遍历集合时mybatis会自动将对象转换为Map集合,key=对象字段名称,value=对象字段值.
// createUserList();
// 修改对象.
// 注意: 如果我们传入到Mapper层的参数是一个对象,mybatis会自动将其转换成Map集合,key=对象字段名称,value=对象字段值.
// 同时注意: 拼写sql时哪些是条件字段,哪些是需要修改的字段.
// modifyUser();
// 删除单个对象.
// 注意: 如果我们传入到Mapper层的参数是一个元素,mybatis会自动将其转换为Map集合吗?
// 如果将sql改为<delete id="deleteUser">delete from mybatis_user where <if test="user!=null">id=#{user}}</if></delete>
// 会报如下错误: There is no getter for property named 'user' in 'class java.lang.String'
// 怎么回事呢? 原因是: <if test="user!=null">这种写法是针对某个对象的, 即传入对象的user字段是否为空,
// 如上传入deleteUser方法的是个字符串, 并且字符串没有user这个属性,所以会报如上错误.
// deleteUser();
// 删除多个对象.
// 注意: 如果我们传入到Mapper层的参数只有一个,并且是List集合,mybatis会自动将其封装成Map集合,且key="list",value=List集合.
// deleteUserList();
// 测试事务.
// 事务回滚了: Exception in thread "main" java.lang.ArithmeticException: / by zero
// testTransaction();
// 测试事务.
// 注意: 在service层处理业务的时候,如果service方法被try catch包裹, 则事务是不能回滚的,因为异常被捕获了,
// 解决方法是捕获Exception后手动抛出异常,throw new RuntimeException("事务回滚");
// Exception in thread "main" java.lang.RuntimeException: 事务回滚
// testTransaction2();
// 查询单个对象.
// 1. 如果报错:A query was run and no Result Maps were found for the Mapped Statement 'com.ilucky.mybatis.mapper.UserMapper.getUser'.
// It's likely that neither a Result Type nor a Result Map was specified.
// 针对查询语句, 必须指定返回类型, 通过resultType或resultMap指定, resultType和resultMap的区别是什么呢?
// a. 当提供的返回类型属性是resultType时,MyBatis会将Map里面的键值对取出赋给resultType所指定的对象对应的属性。
// 所以其实MyBatis的每一个查询映射的返回类型都是ResultMap,只是当提供的返回类型属性是resultType的时候,MyBatis对自动的给把对应的值赋给resultType所指定对象的属性。
// b. 当提供的返回类型是resultMap时,因为Map不能很好表示领域模型,就需要自己再进一步的把它转化为对应的对象,常常在复杂查询中很有作用.
// 解决方法:添加resultType="User", 即<select id="getUser" resultType="User">
//
// 2. 报错:Error instantiating class com.ilucky.mybatis.model.User with invalid types () or values (). Cause: java.lang.NoSuchMethodException:
// mybatis会通过反射机制将mybatis_user数据表中的数据按照表字段名称自动映射到User类中.
// 具体过程是: 首先通过反射机制创建User, 然后通过反射机制将数据按照表字段名称自动映射到User类中.
// 所以User必须提供一个空的构造方法, 否则mybatis无法通过反射创建User. 为User添加完空的构造方法, 继续执行,输出结果如下
// id=49455d9faf79443c801e40ae736f0b85,name=name-8,password=password-8,sex=true,birthday=Tue Nov 03 10:07:51 CST 2015,createTime=null,userType=null
//
// 3. 了解了mybatis自动映射的原理后, 导致上面结果的原因就很清楚了, mybatis通过映射创建了User后,
// 然后通过反射机制调用各个字段的set方法完成数据映射, 而set方法是按照数据库字段拼接的,所以setCreate_time和setUser_type会出现问题, 从而导致数据丢失.
// 注意:如果User类中的字段名称和mybaits_user数据表中的名称严格一致,则不会导致上面的问题.
// 解决方法:声明一个resultMap,将User类中的字段名称和mybatis_usr数据表中的名称进行手动映射.
// 结合上面分析的resultType和resultMap的区别, 如果返回的数据可以支持自动映射到某个model类上,则可以使用resultType,否则需要使用resultMap进行手动映射.
// 完成手动映射,输出结果如下:
// id=49455d9faf79443c801e40ae736f0b85,name=name-8,password=password-8,sex=true,birthday=Tue Nov 03 10:07:51 CST 2015,createTime=Thu Oct 01 12:38:31 CST 2015,userType=COMMON
// getUser();
// 查询所有对象.
// 注意: mybatis的Mapper实现是通过id来区分的,所以id不能重复.
// getUserList();
// 根据name查询对象.
// 注意: 查询单个对象和查询所有对象,写法基本上是一样的(<select id="getUser" resultMap="userMap">), mybatis是如何查询结果并进行封装的?
// 如果数据表中name是唯一的, 则查询是没有问题的, 但是如果不是唯一的, 有两条数据name是一样的,
// 则User getUserByName1(String name);会报错: expected one result (or null) to be returned by selectOne(), but found: 2
// 而List<User> getUserByName2(String name);不会报错.
// 结论: 当查询对象时,mybatis会分析mapper层方法的返回结果,如果是一个对象,则数据库查询必须返回一个,否则会报错,
// 如果mybatis分析mapper层方法的返回结果是一个对象集合, 则没有任何限制.
// 最后,如果mapper层返回的是一个对象,则封装成resultMap,如果mapper层返回的是一个对象集合,则封装成一个元素为resultMap的List集合.
// 所以, 针对这种查询,建议返回一个List集合。另外,这里只做一个简单分析,后面会深入分析mybatis源码.
// getUserByName();
// 查询所有对象个数.
// getUserCount();
// 分页查询对象(包括条件查询和模糊查询).
// 注意: 针对时间的排序和比较,要用order by或者between and,不能像比较字符串那样比较,否则会有问题.
getUserListByPage();
}
/**
* 简单测试.
*/
public static void test() {
String resource = "mybatis-test.xml";
InputStream is = null;
SqlSessionFactory ssf = null;
SqlSession ss = null;
try {
is = Resources.getResourceAsStream(resource);
ssf = new SqlSessionFactoryBuilder().build(is, "development");
ss = ssf.openSession();
ss.insert("createUser", new User(IdUtil.getId(), "test", "123456", true, new Date(), new Date(), UserType.COMMON));
ss.commit();
logger.info("创建用户成功");
} catch (IOException e) {
logger.error("异常:"+e);
} finally {
try {
if(ss != null) {
ss.close();
ss = null;
}
if(is != null) {
is.close();
is = null;
}
} catch (IOException e) {
logger.error("异常:"+e);
}
}
}
/**
* 加载spring配置文件和log4j日志文件
* @return boolean
*/
public static boolean initEnv() {
try {
context = new ClassPathXmlApplicationContext("spring.xml");
PropertyConfigurator.configure("src/main/resources/log4j.properties");
logger.info("初始化环境成功");
return true;
} catch (Exception e) {
logger.error("初始化环境异常:"+e);
return false;
}
}
/**
* 保存单个对象
*/
public static void createUser() {
User user = new User(IdUtil.getId(), "name-0", "123456", true, new Date(), new Date(), UserType.COMMON);
userService.createUser(user);
}
/**
* 保存多个对象
*/
public static void createUserList() {
List<User> userList = new ArrayList<User>();
for(int i = 0; i < 10; i++) {
int number = new Random().nextInt(24 * 3);// 生日跨度是72个小时~=3天.
int number2 = new Random().nextInt(24 * 3); // 创建时间跨度是72个小时~=3天.
Date birthday = new Date(new Date().getTime() - 1000 * 60 * 60 * number);
Date createTime = new Date(new Date().getTime() - 1000 * 60 * 60 * number2);
User user = new User(IdUtil.getId(), "name-"+i, "password-"+i, i%2 == 0 ? true : false, birthday, createTime, i%2 == 0 ? UserType.COMMON : UserType.SUPER);
userList.add(user);
}
userService.createUserList(userList);
}
/**
* 修改对象
*/
public static void modifyUser() {
User user = new User("072462ba0236401fbb411ba95702a058", "name-modify", "654321", false, new Date(), new Date(), UserType.SUPER);
userService.modifyUser(user);
}
/**
* 删除单个对象
*/
public static void deleteUser() {
userService.deleteUser("3e028c910ef1483b85d8bb7228be5fff");
}
/**
* 删除多个对象
*/
public static void deleteUserList() {
List<String> userList = new ArrayList<String>();
userList.add("a93e28b75d144df198388be82a634147");
userList.add("b3d59acfea404244890b74732ac726ae");
userService.deleteUserList(userList);
}
/**
* 测试事务
*/
private static void testTransaction() {
userService.testTransaction();
}
/**
* 测试事务
*/
private static void testTransaction2() {
userService.testTransaction2();
}
/**
* 查询单个对象
*/
private static void getUser() {
userService.getUser("c71bad53655c4844aec9d43e81c3e61f");
}
/**
* 查询所有对象
*/
private static void getUserList() {
userService.getUserList();
}
/**
* 查询所有对象个数
*/
private static void getUserCount() {
userService.getUserCount();
}
/**
* 根据用户名查询对象
*/
private static void getUserByName() {
userService.getUserByName("name-transaction1");
}
/**
* 分页查询对象
*/
private static void getUserListByPage() {
userService.getUserListByPage();
}
}
package com.ilucky.mybatis.service;
import java.util.List;
import com.ilucky.mybatis.model.User;
/**
* v1.0:20161107
* @author IluckySi
* @since 20151013
*/
public interface UserService {
/**
* 创建User
* @param user
*/
public void createUser(User user);
/**
* 创建User集合
* @param userList
*/
public void createUserList(List<User> userList);
/**
* 修改User
* @param user
*/
public void modifyUser(User user);
/**
* 删除User
* @param user
*/
public void deleteUser(String user);
/**
* 删除User集合
* @param userList
*/
public void deleteUserList(List<String> userList);
/**
* 测试事务
*/
public void testTransaction();
/**
* 测试事务
*/
public void testTransaction2();
/**
* 查询User
*@param user
*/
public void getUser(String user);
/**
* 查询User集合
*/
public void getUserList();
/**
* 根据name查询User
*@param user
*/
public void getUserByName(String name);
/**
* 查询User数量
*/
public void getUserCount();
/**
* 分页查询User集合
*/
public void getUserListByPage();
}
package com.ilucky.mybatis.service;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.ilucky.mybatis.mapper.UserMapper;
import com.ilucky.mybatis.model.User;
import com.ilucky.mybatis.model.UserType;
import com.ilucky.mybatis.util.IdUtil;
/**
* v1.0:20161117
* @author IluckySi
* @since 20151013
*/
@Service("userService")
@Transactional(rollbackFor = Exception.class)
public class UserServiceImpl implements UserService {
private static Logger logger = Logger.getLogger(UserServiceImpl.class);
@Autowired
private UserMapper userMapper;
@Override
public void createUser(User user) {
userMapper.createUser(user);
}
@Override
public void createUserList(List<User> userList) {
userMapper.createUserList(userList);
}
@Override
public void modifyUser(User user) {
userMapper.modifyUser(user);
}
@Override
public void deleteUser(String user) {
userMapper.deleteUser(user);
}
@Override
public void deleteUserList(List<String> userList) {
userMapper.deleteUserList(userList);
}
@Override
public void testTransaction() {
User user1 = new User(IdUtil.getId(), "name-transaction1", "123456", true, new Date(), new Date(), UserType.COMMON);
User user2 = new User(IdUtil.getId(), "name-transaction2", "123456", true, new Date(), new Date(), UserType.COMMON);
userMapper.createUser(user1);
int i = 1;
logger.info("测试事务"+100/(i-1));
userMapper.createUser(user2);
}
@Override
public void testTransaction2() {
try {
User user1 = new User(IdUtil.getId(), "name-transaction1", "123456", true, new Date(), new Date(), UserType.COMMON);
User user2 = new User(IdUtil.getId(), "name-transaction2", "123456", true, new Date(), new Date(), UserType.COMMON);
userMapper.createUser(user1);
int i = 1;
logger.info("测试事务"+100/(i-1));
userMapper.createUser(user2);
} catch (Exception e) {
logger.error("测试事务发生异常"+e);
// throw new RuntimeException("事务回滚");
}
}
@Override
public void getUser(String user) {
User userResult = userMapper.getUser(user);
logger.info(userResult);
}
@Override
public void getUserList() {
List<User> userList= userMapper.getUserList(null);
if(userList != null) {
logger.info("共"+userList.size()+"条数据");
for(int i = 0; i < userList.size(); i++) {
logger.info(userList.get(i));
}
}
}
@Override
public void getUserByName(String name) {
//User userResult = userMapper.getUserByName1(name);
//logger.info(userResult);
List<User> userList= userMapper.getUserByName2(name);
if(userList != null) {
logger.info("共"+userList.size()+"条数据");
for(int i = 0; i < userList.size(); i++) {
logger.info(userList.get(i));
}
}
}
@Override
public void getUserCount() {
int count = userMapper.getUserCount();
logger.info(count);
}
@Override
public void getUserListByPage() {
Map<String, Object> condition = new HashMap<String, Object>();
condition.put("first", 0);
condition.put("count", 5);
condition.put("userType", UserType.COMMON.name());
condition.put("likeName", "%" + "-2" +"%");
int number = new Random().nextInt(24 * 3);//生日跨度是72个小时~=3天.
Date smallBirthday = new Date(new Date().getTime() - 1000 * 60 * 60 * number);
logger.info("smallBirthday="+smallBirthday);
condition.put("smallBirthday", smallBirthday);
condition.put("bigBirthday", new Date());
List<User> userList= userMapper.getUserList(condition);
if(userList != null) {
logger.info("共"+userList.size()+"条数据");
for(int i = 0; i < userList.size(); i++) {
logger.info(userList.get(i));
}
}
}
}
package com.ilucky.mybatis.mapper;
import java.util.List;
import java.util.Map;
import com.ilucky.mybatis.model.User;
/**
* v1.0:20161107
*
* @author IluckySi
* @since 20151013
*/
public interface UserMapper {
/**
* 创建User
*
* @param user
*/
public void createUser(User user);
/**
* 创建User集合
*
* @param userList
*/
public void createUserList(List<User> userList);
/**
* 修改User
*
* @param user
*/
public void modifyUser(User user);
/**
* 删除User
*
* @param user
*/
public void deleteUser(String user);
/**
* 删除User集合
*
* @param user
*/
public void deleteUserList(List<String> userList);
/**
* 查询User
*
* @param user
* @reuturn User
*/
public User getUser(String user);
/**
* 根据name查询User
*
* @param user
* @reuturn User
*/
public User getUserByName1(String name);
/**
* 根据name查询User
*
* @param user
* @return List<User>
*/
public List<User> getUserByName2(String name);
/**
* 查询User集合
*
* @return List<User>
*/
public List<User> getUserList(Map<String, Object> condition);
/**
* 查询User数量
*
* @return int
*/
public int getUserCount();
}
<?xml version="1.0" encoding="UTF-8"?>
<!-- v1.0:20161107 -->
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ilucky.mybatis.mapper.UserMapper">
<resultMap type="User" id="userMap">
<id property="id" column="id"></id>
<id property="name" column="name"></id>
<id property="password" column="password"></id>
<id property="sex" column="sex"></id>
<id property="birthday" column="birthday" ></id>
<id property="createTime" column="create_time" ></id>
<id property="userType" column="user_type"></id>
</resultMap>
<insert id="createUser">
insert into mybatis_user(id, name, password, sex, birthday, create_time, user_type)
values(#{id}, #{name}, #{password}, #{sex}, #{birthday}, #{createTime}, #{userType})
</insert>
<insert id="createUserList" >
insert into mybatis_user(id, name, password, sex, birthday, create_time, user_type) values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id}, #{item.name}, #{item.password}, #{item.sex}, #{item.birthday}, #{item.createTime}, #{item.userType})
</foreach>
</insert>
<update id="modifyUser">
update mybatis_user set
<if test="name!=null">name=#{name},</if>
<if test="password!=null">password=#{password},</if>
<if test="sex!=null">sex=#{sex},</if>
<if test="birthday!=null">birthday=#{birthday},</if>
<if test="userType!=null">user_type=#{userType},</if>
id=#{id} where
<if test="id!=null">id=#{id}</if>
</update>
<!-- 如下方式会异常: Caused by: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'user' in 'class java.lang.String' -->
<!-- <delete id="deleteUser">
delete from mybatis_user where
<if test="user!=null">id=#{user}}</if>
</delete> -->
<delete id="deleteUser">
delete from mybatis_user where id=#{user}
</delete>
<delete id="deleteUserList">
delete from mybatis_user where id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
<select id="getUser" resultMap="userMap">
select * from mybatis_user where id=#{user}
</select>
<select id="getUserByName1" resultMap="userMap">
select * from mybatis_user where name=#{name}
</select>
<select id="getUserByName2" resultMap="userMap">
select * from mybatis_user where name=#{name}
</select>
<!-- <select id="getUserList" resultMap="userMap">
select * from mybatis_user
</select> -->
<!-- 时间比较有问题 -->
<!-- <select id="getUserList" resultMap="userMap">
select * from mybatis_user where 1=1
<if test="smallBirthday!=null"> and birthday >#{smallBirthday} </if>
<if test="bigBirthday!=null"> and birthday <#{bigBirthday} </if>
<if test="userType!=null"> and user_type=#{userType} </if>
<if test="likeName!=null"> and name like#{likeName} </if>
<if test="first!=null"> limit ${first},${count}</if>
</select> -->
<select id="getUserList" resultMap="userMap">
select * from mybatis_user where 1=1
<if test="smallBirthday!=null and bigBirthday!=null"> and birthday between #{smallBirthday} and #{bigBirthday}</if>
<if test="userType!=null"> and user_type=#{userType} </if>
<if test="likeName!=null"> and name like#{likeName} </if>
<if test="first!=null"> limit ${first},${count}</if>
</select>
<select id="getUserCount" resultType="int">
select count(*) from mybatis_user
</select>
</mapper>
package com.ilucky.mybatis.model;
import java.util.Date;
/**
* v1.0:20161107
* @author IluckySi
* @since 20151013
*/
public class User {
private String id;
private String name;
private String password;
private boolean sex;
private Date birthday;
private Date createTime;
private UserType userType;
public User() {
}
public User(String id, String name, String password, boolean sex, Date birthday, Date createTime, UserType userType) {
super();
this.id = id;
this.name = name;
this.password = password;
this.sex = sex;
this.birthday = birthday;
this.createTime = createTime;
this.userType = userType;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public boolean isSex() {
return sex;
}
public void setSex(boolean sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public UserType getUserType() {
return userType;
}
public void setUserType(UserType userType) {
this.userType = userType;
}
public String toString() {
return "id="+id+",name="+name+",password="+password+",sex="+sex+",birthday="+birthday+",createTime="+createTime+",userType="+userType;
}
}
package com.ilucky.mybatis.model;
/**
* v1.0:20161107
* @author IluckySi
* @since 20151013
*/
public enum UserType {
COMMON("common", "普通用户"), SUPER("super", "超级用户");
public String name;
public String displayName;
UserType(String name, String displayName) {
this.name = name;
this.displayName = displayName;
}
}
<!-- v1.0:20161117 -->
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jms="http://www.springframework.org/schema/jms"
xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/jms http://www.springframework.org/schema/jms/spring-jms-4.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd">
<!-- 配置数据库参数 -->
<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
<property name="driverClass" value="com.mysql.jdbc.Driver" />
<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=utf8" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>
<!-- 配置mybaits注入 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="classpath:mybatis.xml"></property>
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 配置mapper扫描-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.ilucky.mybatis.mapper" />
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
</bean>
<!-- 配置service扫描 -->
<context:component-scan base-package="com.ilucky.mybatis.service" />
<!-- 配置事务管理器 -->
<bean id="transactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!-- 通过aop配置让所有service包下所有bean的方法拥有事务 -->
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="*"></tx:method>
</tx:attributes>
</tx:advice>
<!-- pointcut中expression的意思是:
第一个*表示方法的返回值可以是任意的.
第一个..表示service包下任意的class.
第二个*表示service包下任意class中的任意方法.
第一个..表示service包下任意class中的任意方法中的参数可以是任意的.
(注意第一个星号后面必须要有一个空格,否则会报错误)-->
<aop:config proxy-target-class="true">
<aop:pointcut id="serviceMethod"
expression="execution(* com.ilucky.mybatis.service..*(..))"></aop:pointcut>
<aop:advisor pointcut-ref="serviceMethod" advice-ref="txAdvice"></aop:advisor>
</aop:config>
</beans>
<?xml version="1.0" encoding="UTF-8" ?>
<!-- v1.0:20161117 -->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 配置缓存设置 -->
<settings>
<setting name="cacheEnabled" value="false"/>
</settings>
<!--配置model别名-->
<typeAliases>
<typeAlias type="com.ilucky.mybatis.model.User" alias="User" />
</typeAliases>
<!--加载mapper文件 -->
<mappers>
<mapper resource="com/ilucky/mybatis/mapper/UserMapper.xml" />
</mappers>
</configuration>
<?xml version="1.0" encoding="UTF-8" ?>
<!-- v1.0:20161116 -->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--给实体类起一个别名 User-->
<typeAliases>
<typeAlias type="com.ilucky.mybatis.model.User" alias="User" />
</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/mybatis?useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!--装载xml,相当于把dao的实现装载进来 -->
<mappers>
<mapper resource="com/ilucky/mybatis/mapper/UserMapper.xml" />
</mappers>
</configuration>
#Loggers
log4j.rootLogger=debug,console,file
#console
log4j.logger.console=debug
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%d]-%p-%l-%m%n
#file
log4j.logger.file=debug
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=D://mybatis-util.log
log4j.appender.file.MaxFileSize=100KB
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%d]-%p-%l-%m%n
/*创建数据库语句*/
CREATE Database If Not Exists mybatis Character Set UTF8;
use mybatis;
/*创建数据表语句*/
DROP TABLE IF EXISTS `mybatis_user`;
CREATE TABLE `mybatis_user` (
`id` VARCHAR(128) NOT NULL UNIQUE,
`name` VARCHAR(128) NOT NULL,
`password` VARCHAR(1280) NOT NULL,
`sex` TINYINT(1) NOT NULL,
`birthday` DATETIME NOT NULL,
`create_time` DATETIME NOT NULL,
`user_type` VARCHAR(128) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;