数据库访问框架 - Mybatis

<dependencies>
    <!-- mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.4.5</version>
    </dependency>
    <!-- mybatis依赖mysql驱动，必选 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.6</version> <scope>runtime</scope>
    </dependency>
    <!-- 单元测试依赖，可选 -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.10</version>
        <scope>test</scope>
    </dependency>
    <!-- 日志依赖，可选 -->
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.12</version>
    </dependency>
</dependencies>

public class User implements Serializable {
	private Integer id;
	private String username;
	private Date birthday;
	private String sex;
	private String address;
    
	public Integer getId() {
		return id; 
    }
	public void setId(Integer id) {
		this.id = id; 
    }
	public String getUsername() {
		return username; }
	public void setUsername(String username) {
		this.username = username; 
    }
	public Date getBirthday() {
		return birthday;
    }
	public void setBirthday(Date birthday) {
		this.birthday = birthday; 
    }
	public String getSex() {
		return sex; 
    }
	public void setSex(String sex) {
		this.sex = sex; 
    }
	public String getAddress() {
		return address; 
    }
	public void setAddress(String address) {
  this.address = address;
    }
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", birthday=" + birthday
		+ ", sex=" + sex + ", address="
		+ address + "]"; 
    } 
}

//持久层接口（也可以写成 UserDao 或者 UserMapper）
public interface IUserDao {
	/**
	* 查询所有用户
	* @return
	*/
	List<User> findAll();
}

<?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.itheima.dao.IUserDao">
	<!-- 配置查询所有操作 --> 
	<select id="findAll" resultType="com.itheima.domain.User">
		select * from user
	</select>
</mapper>

<?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>
	<!-- 配置 mybatis 的环境 -->
    <environments default="mysql">
		<!-- 配置 mysql 的环境 -->
        <environment id="mysql">
			<!-- 配置事务的类型 -->
            <transactionManager type="JDBC"></transactionManager>
			<!-- 配置连接数据库的信息：用的是数据源(连接池) -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/ee50"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
    </environments>
    
	<!-- 告知 mybatis 映射配置的位置 -->
    <mappers>
        <mapper resource="com/itheima/dao/IUserDao.xml"/>
    </mappers>
</configuration>

public class MybatisTest {
	public static void main(String[] args)throws Exception {
		//1.读取配置文件
		InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
		//2.创建 SqlSessionFactory 的构建者对象
		SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
		//3.使用构建者创建工厂对象 SqlSessionFactory
		SqlSessionFactory factory = builder.build(in);
		//4.使用 SqlSessionFactory 生产 SqlSession 对象
		SqlSession session = factory.openSession();
		//5.使用 SqlSession 创建 dao 接口的代理对象
		IUserDao userDao = session.getMapper(IUserDao.class);
		//6.使用代理对象执行查询所有方法
		List<User> users = userDao.findAll();
		for(User user : users) {
			System.out.println(user);
		}
		//7.释放资源
		session.close();
		in.close();
	} 
}

public interface IUserDao {
	/**
	* 查询所有用户
	* @return
	*/
	@Select("select * from user")
	List<User> findAll();
}

<!-- 告知 mybatis 映射配置的位置 --> 
<mappers>
    <mapper class="com.itheima.dao.IUserDao"/>
</mappers>

public class UserDaoImpl implements IUserDao{
    private SqlSessionFactory factory;
    
    public UserDaoImpl(SqlSessionFactory factory){
        this.factory = factory;
    }
    
    public List<User> findAll(){
        //1.使用工厂创建SqlSession对象
        SqlSession session = factory.openSession();
        //2.使用session执行查询所有方法，这里一定要是包含完整包名的接口全名方法，才能找到最终要执行的sql语句
        List<User> users = session.selectList("com.itheima.dao.IUserDao.findAll");
        session.close();
        //3.返回查询结果
        return users;
    }
    
}

public class MybatisTest {
	public static void main(String[] args)throws Exception {
		//1.读取配置文件
		InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
		//2.创建 SqlSessionFactory 的构建者对象
		SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
		//3.使用构建者创建工厂对象 SqlSessionFactory
		SqlSessionFactory factory = builder.build(in);
		//4.使用工厂创建dao对象
		IUserDao userDao = new UserDaoImpl(factory);
		//5.使用dao对象执行查询所有方法
		List<User> users = userDao.findAll();
		for(User user : users) {
			System.out.println(user);
		}
		//6.释放资源
		in.close();
	} 
}

/**
 * 根据id查询用户信息
 * @param userId
 * @return
 */
User findById(Integer userId);

/**
 * 保存用户
 * @param user
 */
void saveUser(User user);

/**
 * 更新用户
 * @param user
 */
void updateUser(User user);

/**
 * 根据Id删除用户
 * @param userId
 */
void deleteUser(Integer userId);

/**
 * 根据名称模糊查询用户信息
 * @param username
 * @return
 */
List<User> findByName(String username);

<!-- 根据id查询用户 -->
<!-- 内置类型可以用简称int，也可以用全限定名java.lang.Integer，但自定义类型必须用全限定名 -->
<select id="findById" parameterType="int" resultType="com.itheima.domain.User">
    select * from user where id = #{uid}
</select>

<!-- 保存用户 -->
<insert id="saveUser" parameterType="com.itheima.domain.User">
    <!-- 配置插入操作后，获取插入数据的id -->
    <!-- keyProperty实体类属性名 -->
    <!-- keyColumn数据库列名 -->
    <!-- order="AFTER"语句执行完成后再执行 -->
    <selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
        select last_insert_id();
    </selectKey>
    insert into user(username,address,sex,birthday)values(#{username},#{address},#{sex},#{birthday});
</insert>

<!-- 更新用户 -->
<update id="updateUser" parameterType="com.itheima.domain.User">
     update user set username=#{username},address=#{address},sex=#{sex},birthday=#{birthday} where id=#{id}
</update>

<!-- 删除用户-->
<delete id="deleteUser" parameterType="java.lang.Integer">
    delete from user where id = #{uid}
</delete>

<!-- 根据名称模糊查询 -->
<select id="findByName" parameterType="string" resultType="com.itheima.domain.User">
       select * from user where username like #{name}
     <!-- select * from user where username like '%${value}%'-->
</select>

public class MybatisTest {
    private InputStream in;
    private SqlSession sqlSession;
    private IUserDao userDao;

    @Before//用于在测试方法执行之前执行
    public void init()throws Exception{
        //1.读取配置文件，生成字节输入流
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactory
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        //3.获取SqlSession对象
        sqlSession = factory.openSession();
        //4.获取dao的代理对象
        userDao = sqlSession.getMapper(IUserDao.class);
    }

    @After//用于在测试方法执行之后执行
    public void destroy()throws Exception{
        //提交事务
        sqlSession.commit();
        //6.释放资源
        sqlSession.close();
        in.close();
    }

    @Test
    public void testFindOne(){
        //5.执行查询一个方法
        User user = userDao.findById(50);
        System.out.println(user);
    }
    
    @Test
    public void testFindByName(){
        //5.执行查询一个方法
        List<User> users = userDao.findByName("%王%");
        //List<User> users = userDao.findByName("王");
        for(User user : users){
            System.out.println(user);
        }
    }
}

public class QueryVo {
    private User user;
    public User getUser() {
        return user;
    }
    public void setUser(User user) {
        this.user = user;
    }
}

<!-- 根据用户名称模糊查询，参数变成一个 QueryVo 对象了 -->
<select id="findByVo" resultType="com.itheima.domain.User" parameterType="com.itheima.domain.QueryVo">
    select * from user where username like #{user.username};
</select>

<!-- 配置 查询结果的列名和实体类的属性名的对应关系 -->
<resultMap id="userMap" type="com.itheima.domain.User">
    <!-- 主键字段的对应 -->
    <id property="userId" column="id"></id>
    <!--非主键字段的对应-->
    <result property="userName" column="username"></result>
    <result property="userAddress" column="address"></result>
    <result property="userSex" column="sex"></result>
    <result property="userBirthday" column="birthday"></result>
</resultMap>

<!-- 根据id查询用户 -->
<select id="findById" parameterType="int" resultMap="userMap">
    select * from user where id = #{uid}
</select>

    <properties>
        <property name="driver" value="com.mysql.jdbc.Driver"></property>
        <property name="url" value="jdbc:mysql://localhost:3306/eesy_mybatis"></property>
        <property name="username" value="root"></property>
        <property name="password" value="1234"></property>
    </properties>
...
    <!--配置连接池-->
    <dataSource type="POOLED">
        <property name="driver" value="${jdbc.driver}"></property>
        <property name="url" value="${jdbc.url}"></property>
        <property name="username" value="${jdbc.username}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </dataSource>

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/eesy
jdbc.username=root
jdbc.password=1234

<properties resource="jdbcConfig.properties"></properties>

<properties url="file:///D:/xxx/xxx/jdbcConfig.properties"></properties>

<!--使用typeAliases配置别名，它只能配置domain中类的别名 -->
<typeAliases>
    <!--typeAlias用于配置别名。type属性指定的是实体类全限定类名。alias属性指定别名，当指定了别名就再区分大小写 -->
    <!-- 单个别名定义 -->
    <-- <typeAlias type="com.itheima.domain.User" alias="user"></typeAlias> -->
 
    <!-- 用于指定要配置别名的包，当指定之后，该包下的实体类都会注册别名，并且类名就是别名，不再区分大小写-->
    <!-- 批量别名定义，扫描整个包下的类，别名为类名（首字母大写或小写都可以） -->
    <package name="com.itheima.domain"></package>
</typeAliases>

<!-- 配置映射文件的位置 -->
<mappers>
    <!-- 使用相对于类路径的资源 -->
    <!--<mapper resource="com/itheima/dao/IUserDao.xml"></mapper>-->
    
    <!-- 使用mapper接口类路径 -->
    <!--<mapper class="com.itheima.dao.UserDao"/>-->
    
    <!-- package标签是用于指定dao接口所在的包,当指定了之后就不需要在写mapper以及resource或者class了 -->
    <package name="com.itheima.dao"></package>
</mappers>

<!-- 抽取重复的语句代码片段 --> 
<sql id="defaultSql">
    select * from user
</sql>

<!-- 配置查询所有操作 --> 
<select id="findAll" resultType="user"> 
    <include refid="defaultSql"></include>
</select>

<!-- 根据 id 查询 --> 
<select id="findById" resultType="user" parameterType="int">
    <include refid="defaultSql"></include>
    where id = #{uid}
</select>

<select id="findByUser" resultType="user" parameterType="user">
    select * from user where 1=1
    <if test="username!=null and username != '' ">
        and username like #{username}
    </if> 
    <if test="address != null">
        and address like #{address}
    </if>
</select>

<!-- 根据用户信息查询 --> 
<select id="findByUser" resultType="user" parameterType="user"> 
    <include refid="defaultSql"></include> 
    <where> 
        <if test="username!=null and username != '' ">
            and username like #{username}
        </if> 
        <if test="address != null">
            and address like #{address}
        </if>
    </where>
</select>

<!-- 根据queryvo中的Id集合实现查询用户列表 -->
<select id="findUserInIds" resultMap="userMap" parameterType="queryvo">
    <include refid="defaultUser"></include>
    <where>
        <if test="ids != null and ids.size()>0">
            <foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
                #{uid}
            </foreach>
        </if>
    </where>
</select>

/**
 * 测试foreach标签的使用
 */
@Test
public void testFindInIds(){
    QueryVo vo = new QueryVo();
    List<Integer> list = new ArrayList<Integer>();
    list.add(41);
    list.add(42);
    list.add(46);
    vo.setIds(list);

    List<User> users = userDao.findUserInIds(vo);
    for(User user : users){
        System.out.println(user);
    }
}

<!-- 定义封装account和user的resultMap -->
<resultMap id="accountUserMap" type="account">
    <id property="id" column="aid"></id>
    <result property="uid" column="uid"></result>
    <result property="money" column="money"></result>
    <!-- 一对一的关系映射：配置封装user的内容-->
    <association property="user" column="uid" javaType="user">
        <id property="id" column="id"></id>
        <result column="username" property="username"></result>
        <result column="address" property="address"></result>
        <result column="sex" property="sex"></result>
        <result column="birthday" property="birthday"></result>
    </association>
</resultMap>

<!-- 查询所有 -->
<select id="findAll" resultMap="accountUserMap">
    select u.*,a.id as aid,a.uid,a.money from account a , user u where u.id = a.uid;
</select>

public class Account implements Serializable {
	...

    //从表实体应该包含一个主表实体的对象引用
    private User user;

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

<!-- 定义User的resultMap-->
<resultMap id="userAccountMap" type="user">
    <id property="id" column="id"></id>
    <result property="username" column="username"></result>
    <result property="address" column="address"></result>
    <result property="sex" column="sex"></result>
    <result property="birthday" column="birthday"></result>
    <!-- 配置user对象中accounts集合的映射 -->
    <!-- collection 是用于建立一对多中集合属性的对应关系
         ofType 用于指定集合元素的数据类型 -->
    <collection property="accounts" ofType="account">
        <id column="aid" property="id"></id>
        <result column="uid" property="uid"></result>
        <result column="money" property="money"></result>
    </collection>
</resultMap>

<!-- 查询所有 -->
<select id="findAll" resultMap="userAccountMap">
    select u.*,a.id as aid ,a.uid,a.money from user u left outer join account a on u.id =a.uid
</select>

public class User implements Serializable {
	...

    //一对多关系映射：主表实体应该包含从表实体的集合引用
    private List<Account> accounts;
    
    public List<Account> getAccounts() {
        return accounts;
    }

    public void setAccounts(List<Account> accounts) {
        this.accounts = accounts;
    }
}

<!-- 定义封装account和user的resultMap -->
<resultMap id="accountUserMap" type="account">
    <id property="id" column="id"></id>
    <result property="uid" column="uid"></result>
    <result property="money" column="money"></result>
    <!-- 一对一的关系映射：配置封装user的内容
    select属性指定的内容：查询用户的唯一标识，方法名，必填
    column属性指定的内容：用户根据id查询时，所需要的参数名(sql语句返回的列名)，必填
    -->
    <association property="user" column="uid" javaType="user" select="com.itheima.dao.IUserDao.findById">
    </association>
</resultMap>

<!-- 查询所有 -->
<select id="findAll" resultMap="accountUserMap">
    select * from account
</select>

<!-- 根据id查询用户 -->
<select id="findById" parameterType="int" resultType="user">
    select * from user where id = #{uid}
</select>

<!--配置参数-->
<settings>
    <!--延迟加载的全局开关，开启Mybatis支持延迟加载，默认值false-->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!--当开启时，任何方法的调用都会加载该对象的所有属性。否则，每个属性会按需加载，默认值false(true in <=3.4.1)-->
    <setting name="aggressiveLazyLoading" value="false"></setting>
</settings>

<!-- 定义User的resultMap-->
<resultMap id="userAccountMap" type="user">
    <id property="id" column="id"></id>
    <result property="username" column="username"></result>
    <result property="address" column="address"></result>
    <result property="sex" column="sex"></result>
    <result property="birthday" column="birthday"></result>
    <!-- 配置user对象中accounts集合的映射 -->
    <!-- 用于指定查询account列表的sql语句，所以填写的是该sql映射的id，必填
    column属性指定的内容：用于指定select属性的sql语句的参数来源，上面的参数来自于user的id列，所以就写成id这一个字段名了，必填-->
    <collection property="accounts" ofType="account" select="com.itheima.dao.IAccountDao.findAccountByUid" column="id">
    </collection>
</resultMap>

<!-- 查询所有 -->
<select id="findAll" resultMap="userAccountMap">
    select * from user
</select>

<!-- 根据用户id查询账户列表 -->
<select id="findAccountByUid" resultType="account">
    select * from account where uid = #{uid}
</select>

<settings>
    <!-- 开启二级缓存的支持 -->
    <!-- cacheEnabled 的默认取值就是true，所以这一步其实可以省略不配置 -->
    <setting name="cacheEnabled" value="true"/>
</settings>

<?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.itheima.dao.IUserDao">
    <!--开启user支持二级缓存-->
    <!--标签表示当前这个 mapper 映射将使用二级缓存，区分的标准就看mapper的namespace值-->
    <cache></cache>
    ...
</mapper>

<!-- 根据id查询用户 -->
<select id="findById" parameterType="INT" resultType="user" useCache="true">
    select * from user where id = #{uid}
</select>

<?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文件的位置 -->
    <properties resource="jdbcConfig.properties"></properties>
    <!-- 配置别名的注册 -->
    <typeAliases>
        <package name="com.itheima.domain"></package>
    </typeAliases>
    <!-- 配置环境-->
    <environments default="mysql">
        <environment id="mysql">
            <!-- 配置事务的类型是 JDBC -->
            <transactionManager type="JDBC"></transactionManager>
            <!-- 配置数据源 -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"></property>
                <property name="url" value="${jdbc.url}"></property>
                <property name="username" value="${jdbc.username}"></property>
                <property name="password" value="${jdbc.password}"></property>
            </dataSource>
        </environment>
    </environments>
    <!-- 配置映射信息 -->
    <!-- 指定带有注解的dao接口所在位置 -->
    <mappers>
        <!-- 配置 dao 接口的位置，它有两种方式
			第一种：使用 mapper 标签配置 class 属性
			第二种：使用 package 标签，直接指定 dao 接口所在的包
        -->
        <mapper class="com.itheima.dao.IUserDao"></mapper>
    </mappers>
</configuration>

public interface IUserDao {

    /**
     * 查询所有用户
     * @return
     */
    @Select("select * from user")
    @Results(id="userMap",
		value= {
			@Result(id=true,column="id",property="userId"),
			@Result(column="username",property="userName"),
			@Result(column="sex",property="userSex"),
			@Result(column="address",property="userAddress"),
			@Result(column="birthday",property="userBirthday")
		})
    List<User> findAll();
    
    /**
     * 根据 id 查询一个用户
     * @param userId
     * @return
     */
    @Select("select * from user  where id=#{id} ")
    @ResultMap("userMap")
    User findById(Integer userId);

    /**
     * 保存操作
     * @param user
     */
    @Insert("insert into user(username,address,sex,birthday)values(#{username},#{address},#{sex},#{birthday})")
    @SelectKey(keyColumn="id",keyProperty="id",resultType=Integer.class,before = 
false, statement = { "select last_insert_id()" })
    int saveUser(User user);

    /**
     * 更新操作
     * @param user
     */
    @Update("update user set username=#{username},sex=#{sex},birthday=#{birthday},address=#{address} where id=#{id}")
    int updateUser(User user);

    /**
     * 删除用户
     * @param userId
     */
    @Delete("delete from user where id=#{uid} ")
    int deleteUser(Integer userId);

    /**
     * 查询使用聚合函数
     * @return
     */
    @Select("select count(*) from user ")
    int findTotalUser();

    /**
     * 根据用户名称模糊查询
     * @param username
     * @return
     */
    //@Select("select * from user where username like #{username} ")
    @Select("select * from user where username like '%${value}%' ")
    List<User> findUserByName(String username);
}

/**
* 查询所有账户，采用延迟加载的方式查询账户的所属用户
* @return
*/
@Select("select * from account")
@Results(id="accountMap",
    value= {
    @Result(id=true,column="id",property="id"),
    @Result(column="uid",property="uid"),
    @Result(column="money",property="money"),
    @Result(column="uid",property="user",one=@One(select="com.itheima.dao.IUserDao.findById",fetchType=FetchType.LAZY))
})
List<Account> findAll();

/**
* 查询所有用户
* @return
*/
@Select("select * from user")
@Results(id="userMap",
    value= {
        @Result(id=true,column="id",property="userId"),
        @Result(column="username",property="userName"),
        @Result(column="sex",property="userSex"),
        @Result(column="address",property="userAddress"),
        @Result(column="birthday",property="userBirthday"),
        @Result(column="id",property="accounts",many=@Many(select="com.itheima.dao.IAccountDao.findByUid",fetchType=FetchType.LAZY))
})
List<User> findAll();

<!-- 配置二级缓存 --> 
<settings>
    <!-- 开启二级缓存的支持 --> 
    <setting name="cacheEnabled" value="true"/>
</settings>

@CacheNamespace(blocking=true)  //mybatis 基于注解方式实现配置二级缓存
public interface IUserDao {
    ...
}