mybatis的配置文件详解(二)
一.properties
这些属性都是可外部配置且可动态替换的，既可以在典型的 Java 属性文件中配置，亦可通过 properties 元素的子元素来传递。例如
1)
<?xml version="1.0" encoding="utf-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="db.properties"/> <typeAliases> <package name="com.shsxt.po"></package> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> <mappers> <package name="com.shsxt.mapper"/> <!--<mapper resource="com.shsxt.mapper.UserMapper"></mapper>--> </mappers> </configuration>
这种方法是将数据源的属性放在db.properties中,然后在mybatis中读取property的value值,需要在<configuration>中配置一个properties的属性,<properties resource="db.properties"/>
db.properties的内容如下:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis02?useSSL=false
username=root
password=123456
2)直接将属性的value值写死在mybaties.xml
<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?useSSL=false"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment>
二,typeAliases
有三种方式配置typeAliases,第一种方式是手动配置别名,第二种方式是通过注解,第三种方式是通过扫描包(推荐使用该方式)
在configuration标签中添加如下标签,
<typeAliases>
<typeAlias type="com.shsxt.po.User alise="user"/>
</typeAliases>
通过包扫描的方式配置别名
<typeAliases>
<package name="com.shsxt.po" >
<typeAliases>
3,注解的方式配置别名
暂时不用
三,typeHandles类型处理器
无论是 MyBatis 在预处理语句（PreparedStatement）中设置一个参数时，还是从结果集中取出一个值时， 都会用类型处理器将获取的值以合适的方式转换成 Java类型。
下表描述了一些默认的类型处理器。
四,环境配置
MyBatis 可以配置成适应多种环境，这种机制有助于将 SQL 映射应用于多种数据库之中， 现实情况下有多种理由需要这么做。例如，开发、测试和生产环境需要有不同的配置；或者共享相同 Schema 的多个生产数据库， 想使用相同的 SQL 映射。许多类似的用例。不过要记住：尽管可以配置多个环境，每个 不过要记住：尽管可以配置多个环境，每个SqlSessionFactory 实例只能选 实例只能选择其一。所以，如果你想连接两个数据库，就需要创建两个 SqlSessionFactory 实例，每个数据库对应一个。而如果是三个数据库，就需要三个实例，依此类推，记起来很简单：
<environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> <environment id="test"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver2}"/> <property name="url" value="${url2}"/> <property name="username" value="${username2}"/> <property name="password" value="${password2}"/> </dataSource> </environment> </environments>
db.properties文件
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis02?useSSL=false
username=root
password=123456
driver2=com.mysql.jdbc.Driver
url2=jdbc:mysql://localhost:3306/mybatis?useSSL=false
username2=root
password2=123456
五,DataSource数据源
dataSource 元素使用标准的 JDBC 数据源接口来配置 JDBC 连接对象的资源。数据源类型有三种：UNPOOLED，POOLED，JNDI。
UNPOOLED是没有数据库连接池的,没执行一次操作,打开一次数据库,关闭一次数据库.效率较为低下
POOLED是存在数据库连接池的,没有操作数据库从数据库连接池中拿取连接
JNDI这个数据源的实现是为了能在如 EJB 或应用服务器这类容器中使用，容器可以集中或在外部配置数据源，然后放置一个 JNDI 上下文的引用。
六 mapper映射器(4种配置)
第一种 配置文件路径(相对项目的相对路径)
<mappers> <mapper resource="com/shsxt/mapper/UserMapper.xml"></mapper> </mappers>
第二种 配置文件的绝对路径(不推荐使用,随着项目的变更,路径也要改变,比较麻烦)
<mappers> <mapper url="file:///F:/IDEA/mybatis02/src/main/resources/mybatis.xml"/> </mappers>
第三种 注解形式配置(用的相对较少)
<mappers> <mapper class="com.shsxt.mapper.UserMapper"/> </mappers>
配置注解的时候,在接口的方法要加上注解,映射文件中可以不用配置sql语句
第四种 映射包下所有接口(较为常用)
<mappers> <package name="com.shsxt.mapper"/> </mapper>
七,封装Dao
将UserMapper的接口实现,将接口里方法重写并且实现封装
import com.shsxt.mapper.UserMapper; import com.shsxt.po.User; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import java.util.List; import java.util.Map; public class UserMapperImpl implements UserMapper { private SqlSessionFactory sqlSessionFactory; public UserMapperImpl(SqlSessionFactory sqlSessionFactory) { this.sqlSessionFactory = sqlSessionFactory; } @Override public User queryById(Integer id) { SqlSession session=null; User user=null; try { session=sqlSessionFactory.openSession(); user=session.selectOne("com.shsxt.mapper.UserMapper.queryById" ,id); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.close(); } } return user; }
八,增删改查,在UserMapper.xml的文件中配置
UserMapper.xm的文件配置内容如下
<?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.shsxt.mapper.UserMapper"> <sql id="sql">username,realname,password</sql> <resultMap id="usermap" type="user"> <id column="id" property="id"/> <result column="username" property="user_name"/> <result column="realname" property="realname"/> <result column="password" property="password"/> </resultMap> <select id="queryById" parameterType="int" resultMap="usermap"> select * from user where id=#{id} </select> <select id="queryAll" resultType="int"> select count(*) from user </select> <select id="queryUname" resultType="String" > select username from user where id =#{id} </select> <select id="queryByName" parameterType="user" resultType="user"> select username from user where username like concat('%',#{username},'%') </select> <select id="queryIdByMap" parameterType="int" resultType="map"> select * from user where id=#{id} </select> <select id="queryUserByMap" parameterType="map" resultType="user"> select * from user where id=#{id} and username=#{username} </select> <insert id="insertByUser" parameterType="user"> insert into user (username,realname,password) values (#{username},#{realname},#{password}) </insert> <insert id="insertByUserHasKey" parameterType="user"> <selectKey keyProperty="id" order="AFTER" resultType="int"> select LAST_INSERT_ID() as id </selectKey> insert into user (username,realname,password) values (#{username},#{realname},#{password}) </insert> <insert id="insertByUserHasKey02" parameterType="user" useGeneratedKeys="true" keyProperty="id"> insert into user (username,realname,password) values (#{username},#{realname},#{password}) </insert> <insert id="insertByUserBatch" parameterType="list" > insert into user (username,realname,password) values <foreach collection="list" item="item" separator=","> (#{item.username},#{item.realname},#{item.password}) </foreach> </insert> <update id="updateUser" parameterType="user"> update user set username=#{username}, realname=#{realname},password=#{password} where id=#{id} </update> <update id="updataUserBatch" > update user set password='88888888' where id in ( <foreach collection="array" item="item" separator="," index="index"> #{item} </foreach> ) </update> <delete id="deleteUser" parameterType="int"> delete from user where id=#{id} </delete> <delete id="deleteUserBatch" parameterType="list"> delete from user where id in ( <foreach collection="list" item="item" separator="," index="index"> #{item} </foreach> ) </delete> </mapper>
实现类UserMapperImpl的配置如下:
package com.shsxt.mapper.Impl; import com.shsxt.mapper.UserMapper; import com.shsxt.po.User; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import java.util.List; import java.util.Map; public class UserMapperImpl implements UserMapper { private SqlSessionFactory sqlSessionFactory; public UserMapperImpl(SqlSessionFactory sqlSessionFactory) { this.sqlSessionFactory = sqlSessionFactory; } @Override public User queryById(Integer id) { SqlSession session=null; User user=null; try { session=sqlSessionFactory.openSession(); user=session.selectOne("com.shsxt.mapper.UserMapper.queryById" ,id); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.close(); } } return user; } @Override public Integer queryAll() { SqlSession session=null; Integer a=null; try { session=sqlSessionFactory.openSession(); a=session.selectOne("com.shsxt.mapper.UserMapper.queryAll" ); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.close(); } } return a; } @Override public String queryUname(Integer id) { SqlSession session=null; String abc=null; try { session=sqlSessionFactory.openSession(); abc=session.selectOne("com.shsxt.mapper.UserMapper.queryUname", id); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.close(); } } return abc; } @Override public List<User> queryByName(User user) { SqlSession session=null; List<User> list=null; try { session=sqlSessionFactory.openSession(); list=session.selectList("com.shsxt.mapper.UserMapper.queryByName", user); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.close(); } } return list; } @Override public Map queryIdByMap(Integer id) { SqlSession sqlsession=null; Map map=null; try{ sqlsession=sqlSessionFactory.openSession(); map=sqlsession.selectOne("com.shsxt.mapper.UserMapper.queryIdByMap",id); }catch (Exception e){ e.printStackTrace(); }finally { if(sqlsession!=null){ sqlsession.close(); } } return map; } @Override public Integer insertByUser(User user) { SqlSession session=null; Integer a=null; try { session=sqlSessionFactory.openSession(); a=session.insert("com.shsxt.mapper.UserMapper.insertByUser",user); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.close(); } } return a; } @Override public Integer insertByUserHasKey(User user) { SqlSession session=null; Integer a=null; try { session=sqlSessionFactory.openSession(); a=session.insert("com.shsxt.mapper.UserMapper.insertByUserHasKey",user); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.commit(); session.close(); } } return a; } @Override public Integer insertByUserHasKey02(User user) { SqlSession session=null; Integer a=null; try { session=sqlSessionFactory.openSession(); a=session.insert("com.shsxt.mapper.UserMapper.insertByUserHasKey02",user); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.commit(); session.close(); } } return a; } @Override public Integer insertByUserBatch(List<User> list) { SqlSession session=null; Integer a=null; try { session=sqlSessionFactory.openSession(); a=session.insert("com.shsxt.mapper.UserMapper.insertByUserBatch",list); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.commit(); session.close(); } } return a; } @Override public User queryUserByMap(Map map) { SqlSession session=null; User user=null; try { session=sqlSessionFactory.openSession(); user=session.selectOne("com.shsxt.mapper.UserMapper.queryUserByMap",map); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.commit(); session.close(); } } return user; } @Override public Integer updateUser(User user) { SqlSession session=null; Integer a =null; try { session=sqlSessionFactory.openSession(); a=session.update("com.shsxt.mapper.UserMapper.updateUser",user); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.commit(); session.close(); } } return a; } @Override public Integer updataUserBatch(Integer[] ids) { SqlSession session=null; Integer aa=null; try { session=sqlSessionFactory.openSession(); aa=session.update("com.shsxt.mapper.UserMapper.updataUserBatch",ids); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.commit(); session.close(); } } return aa; } @Override public Integer deleteUser(Integer id) { SqlSession session=null; Integer aa=null; try { session=sqlSessionFactory.openSession(); aa=session.delete("com.shsxt.mapper.UserMapper.deleteUser",id); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.commit(); session.close(); } } return aa; } @Override public Integer deleteUserBatch(List<Integer> ids) { SqlSession session=null; Integer aa=null; try { session=sqlSessionFactory.openSession(); aa=session.update("com.shsxt.mapper.UserMapper.deleteUserBatch",ids); }catch (Exception e){ e.printStackTrace(); }finally { if(session!=null){ session.commit(); session.close(); } } return aa; } }
测试单元的代码如下:
package com.shsxt; import com.shsxt.mapper.Impl.UserMapperImpl; import com.shsxt.mapper.UserMapper; import com.shsxt.po.User; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Before; import org.junit.Test; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class Test_mybatis02 { SqlSessionFactory sqlSessionFactory; @Before public void ready() throws IOException { InputStream inputStream= Resources.getResourceAsStream("mybatis.xml"); sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream); } /* * queryById*/ @Test public void test01(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); User user= userMapper.queryById(1); System.out.println(user); } @Test public void test02(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); Integer a = userMapper.queryAll(); System.out.println("总数量是："+a); } @Test public void test03(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); String aa = userMapper.queryUname(1); System.out.println("id为1的uname是："+aa); } @Test public void test04(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); User user=new User(); user.setUser_name("hello"); List<User> lists = userMapper.queryByName(user); for (User per_user :lists) { System.out.println(per_user); } } @Test public void test05(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); Map map= userMapper.queryIdByMap(1); System.out.println(((Map) map).toString()); } @Test public void test06(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); User user=new User(); user.setUser_name("ahah"); user.setRealname("papapa"); user.setPassword("89533"); Integer aavv=userMapper.insertByUser(user); System.out.println(aavv); } @Test public void test07(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); User user=new User(); user.setUser_name("ahah"); user.setRealname("papapa"); user.setPassword("89533"); Integer aavv=userMapper.insertByUserHasKey(user); System.out.println("受影响的行数: "+aavv); System.out.println("返回的id是： "+user.getId()); } @Test public void Test08(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); User user=new User(); user.setUser_name("ahah"); user.setRealname("papapa"); user.setPassword("89533"); Integer aavv=userMapper.insertByUserHasKey02(user); System.out.println("受影响的行数: "+aavv); System.out.println("返回的id是： "+user.getId()); } @Test public void Test09(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); List<User> list=new ArrayList<>(); for (int i = 0; i <10 ; i++) { User user=new User(); user.setUser_name("haha"+i); user.setPassword("123456"+i); user.setRealname("hehe"+i); list.add(user); } Integer aavv=userMapper.insertByUserBatch(list); System.out.println("受影响的行数: "+aavv); } @Test public void Test10(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); Map map=new HashMap(); map.put("id",1); map.put("username","hello"); User user=userMapper.queryUserByMap(map); System.out.println(user); } @Test public void Test11(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); User user=new User(); user.setRealname("laodu"); user.setPassword("888888"); user.setUser_name("liudaye"); user.setId(1); Integer a= userMapper.updateUser(user); System.out.println(a); } @Test public void Test12(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); Integer[] aa=new Integer[10]; for (int i = 0; i < 10; i++) { aa[i]=i+10; } Integer a= userMapper.updataUserBatch(aa); System.out.println(a); } @Test public void Test13(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); Integer a= userMapper.deleteUser(22); System.out.println(a); } @Test public void Test14(){ UserMapper userMapper=new UserMapperImpl(sqlSessionFactory); List<Integer> list=new ArrayList<>(); for (int i = 10; i < 20; i++) { list.add(i); } Integer a= userMapper.deleteUserBatch(list); System.out.println(a); } }
九,sql片段和动态sql
ql 元素用来定义一个可以复用的 SQL 语句段，供其它语句调用,例如
<sql id="User_columns">userId, userName, password</sql>
<!--用 include 引用-->
<select id="findUserById" resultMap="RM_User" >
<include refid="User_columns"/> from user where userId =#{userId}
</select>
Batis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。
利用动态 SQL 这一特性,可以彻底摆脱这种痛苦。它借助 ognl（类似于 jsp 里面的 el 表达式）表达式来完成动态 sql 的拼接使得非常简便。
1,条件判断 使用 if 标签就是加一个 test 属性作为判断， 如果有多个条件组合判断的话用 and, or连接
<select id="queryByName" parameterType="string" resultType="user" resultMap="usermap"> select * from user where 1=1 <if test="null!=username and ''!=username"> and username like concat('%',#{username},'%') </if> </select>
测试方法
import com.shsxt.mapper.UserMapper; import com.shsxt.po.User; 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.Before; import org.junit.Test; import java.io.InputStream; import java.util.List; public class mybaties03_Test { private UserMapper userMapper; @Before public void before() throws Exception{ InputStream inputStream = Resources.getResourceAsStream("mybatis.xml"); SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); userMapper =sqlSession.getMapper(UserMapper.class); } @Test public void test02(){ List<User> list=userMapper.queryByName("li"); for (User user:list) { System.out.println(user); } } }
2,choose,when,otherwise
我们不想用到所有的条件语句，而只想从中择其一二。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句
<select id="queryByParams" parameterType="string" resultMap="usermap" resultType="user"> select id, <choose> <when test="realname!=null and realname!='' "> username </when> <otherwise> password </otherwise> </choose> from user </select>
测试方法
import com.shsxt.mapper.UserMapper; import com.shsxt.po.User; 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.Before; import org.junit.Test; import java.io.InputStream; import java.util.List; public class mybaties03_Test { private UserMapper userMapper; @Before public void before() throws Exception{ InputStream inputStream = Resources.getResourceAsStream("mybatis.xml"); SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream); SqlSession sqlSession = sqlSessionFactory.openSession(); userMapper =sqlSession.getMapper(UserMapper.class); } @Test public void test03(){ List<User> list=userMapper.queryByParams("hello"); for (User user:list) { System.out.println(user); } } }
10,mapper 接口代理方式的 crud 开发
对于 mapper 接口方式开发，需要遵循 mybatis 开发规范，mybatis 框架可以自
动生成 mapper 接口对象。
完成账户表 crud 操作
开发规则：
1. mapper.xml 中 namespace 等于接口类全限定名
2. mapper.java 接口中的方法名必须与 mapper.xml 中 statement id 一致
3. mapper.java 输入参数类型必须与 mapper.xml 中 statement 的
parameterType 参数类型一致
4.mapper.java 中方法的返回值类型必须与 mapper.xml 中对应 statement 返回
值类型一致。
接口名 与映射文件名称 一致（非集成环境）
映射文件与接口处于同一个包中(非集成环境
InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
userMapper =sqlSession.getMapper(UserMapper.class);
说白了就是我们不需要手动创建实现类了,mybatis的框架会帮我们通过动态代理来创建实现类,我们只需要用接口去接收创建好的实现的对象就可以了.
