Mybaits的连接池技术 我们在前面的 WEB 课程中也学习过类似的连接池技术,而在 Mybatis 中也有连接池技术,但是它采用的是自 己的连接池技术。在 Mybatis 的 SqlMapConfig.xml 配置文件中,通过来实 现 Mybatis 中连接池的配置。
Mybatis连接池的分类
UNPOOLED :不使用连接池的数据源
POOLED :使用连接池的数据源
JNDI 使用:JNDI 实现的数据源
相应地,MyBatis 内部分别定义了实现了 java.sql.DataSource 接口的 UnpooledDataSource, PooledDataSource 类来表示 UNPOOLED、POOLED 类型的数据源
Mybatis中连接获取过程 当我们需要创建 SqlSession 对象并需要执行 SQL 语句时,这时候 MyBatis 才会去调用 dataSource 对象 来创建java.sql.Connection对象。也就是说,java.sql.Connection对象的创建一直延迟到执行SQL语句 的时候。
1 2 3 4 5 6 7 8 9 10 11 @Test public void testSql () throws Exception { InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml" ); SqlSessionFactory factory = new SqlSessionFactoryBuilder ().build(in); SqlSession sqlSession = factory.openSession(); List<User> list = sqlSession.selectList("findUserById" ,41 ); System.out.println(list.size()); }
最后我们可以发,真正连接打开的时间点,只是在我们执行SQL语句时,才会进行。其实这样做我们也可以进一步发现,数据库连接是我们最为宝贵的资源,只有在要用到的时候,才去获取并打开连接,当我们用完了就再立即将数据库连接归还到连接池中。
Mybatis的事务控制 JDBC中的事务回顾 在 JDBC 中我们可以通过手动方式将事务的提交改为手动方式,通过 setAutoCommit()方法就可以调整。
那么我们的 Mybatis 框架因为是对 JDBC 的封装,所以 Mybatis 框架的事务控制方式,本身也是用 JDBC 的 setAutoCommit()方法来设置事务提交方式的。
Mybatis中的事务提交 Mybatis中在连接池中取出的连接,都会将调用 connection.setAutoCommit(false)方法,这样我们就必须使用 sqlSession.commit()方法,相当于使用了 JDBC 中的 connection.commit()方法实现事务提交。
当然如果需要Mybatis自动提交则可以在**openSession(true)**中传入True
但就编程而言,设置为自动提交方式为 false 再根据情况决定是否进行提交,这种方式更常用。因为我们可以根据业务 情况来决定提交是否进行提交。
Mybatis的动态SQL语句 ****标签 1 2 3 4 5 6 7 8 9 10 11 12 13 <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 >
注意:标签的 test 属性中写的是对象的属性名,如果是包装类的对象要使用 OGNL 表达式的写法。
****标签 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 <sql id ="defaultSql" > select * from user </sql > <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 >
标签 当我们需要用到子查询时就需要这个标签,例如:
传入多个 id 查询用户信息,用下边两个 sql 实现:SELECT * FROM USERS WHERE username LIKE '%张%' AND (id =10 OR id =89 OR id=16)
SELECT * FROM USERS WHERE username LIKE '%张%' AND id IN (10,89,16)
这样我们在进行范围查询时,就要将一个集合中的值,作为参数动态添加进来。
配置文件 1 2 3 4 5 6 7 8 9 10 11 12 <select id ="findInIds" resultType ="user" parameterType ="queryvo" > <include refid ="defaultSql" > </include > <where > <if test ="ids != null and ids.size() > 0" > <foreach collection ="ids" open ="id in ( " close =")" item ="uid" separator ="," > # {uid} </foreach > </if > </where > </select >
实现的sql语句:select 字段 from user where id in (?)
标签用于遍历集合,它的属性:
collection :代表要遍历的集合元素,注意编写时不要写#{}
open :代表语句的开始部分
close :代表结束部分
item :代表遍历集合的每个元素,生成的变量名
sperator :代表分隔符
多表查询 一对一关系 实体类对象:Account 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 public class Account implements Serializable { private Integer id; private Integer uid; private Double money; private User user; public User getUser ( ) { return user; } public void setUser (User user ) { this .user = user; } public Integer getId ( ) { return id; } public void setId (Integer id ) { this .id = id; } public Integer getUid ( ) { return uid; } public void setUid (Integer uid ) { this .uid = uid; } public Double getMoney ( ) { return money; } public void setMoney (Double money ) { this .money = money; } @Override public String toString ( ) { return "Account [id=" + id + ", uid=" + uid + ", money=" + money + "]" ; } }
AccountDao.xml文件 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 <?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.IAccountDao" > <resultMap type ="account" id ="accountMap" > <id column ="aid" property ="id" /> <result column ="uid" property ="uid" /> <result column ="money" property ="money" /> <association property ="user" javaType ="user" > <id column ="id" property ="id" /> <result column ="username" property ="username" /> <result column ="sex" property ="sex" /> <result column ="birthday" property ="birthday" /> <result column ="address" property ="address" /> </association > </resultMap > <select id ="findAll" resultMap ="accountMap" > select u.*,a.id as aid,a.uid,a.money from account a,user u where a.uid =u.id; </select > </mapper >
association标签 :指定sql查询结果与Account实体类中user属性对应的User类的关联
column :数据库表名
property :实体类属性名
javaType :Account实体类中user属性值的对象类型
一对多关系 实体类对象:User 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 public class User implements Serializable { private Integer id; private String username; private Date birthday; private String sex; private String address; private List <Account > accounts; public List <Account > getAccounts ( ) { return accounts; } public void setAccounts (List<Account> accounts ) { this .accounts = accounts; } 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.xml文件 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 <?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" > <resultMap type ="user" id ="userMap" > <id column ="id" property ="id" > </id > <result column ="username" property ="username" /> <result column ="address" property ="address" /> <result column ="sex" property ="sex" /> <result column ="birthday" property ="birthday" /> <collection property ="accounts" ofType ="account" > <id column ="aid" property ="id" /> <result column ="uid" property ="uid" /> <result column ="money" property ="money" /> </collection > </resultMap > <select id ="findAll" resultMap ="userMap" > select u.*,a.id as aid ,a.uid,a.money from user u left outer join account a on u.id =a.uid </select > </mapper >
collection标签 :定义了用户关联的账户信息。表示关联查询结果集
**property=”accList”**:关联查询的结果集存储在 User 对象的上哪个属性。
**ofType=”account”**:指定关联查询的结果集中的对象类型即List中的对象类型。此处可以使用别名,也可以使用全限定名。
多对多关系 角色实体类 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 public class Role implements Serializable { private Integer roleId; private String roleName; private String roleDesc; private List <User > users; public List <User > getUsers ( ) { return users; } public void setUsers (List<User> users ) { this .users = users; } public Integer getRoleId ( ) { return roleId; } public void setRoleId (Integer roleId ) { this .roleId = roleId; } public String getRoleName ( ) { return roleName; } public void setRoleName (String roleName ) { this .roleName = roleName; } public String getRoleDesc ( ) { return roleDesc; } public void setRoleDesc (String roleDesc ) { this .roleDesc = roleDesc; } @Override public String toString ( ) { return "Role{" + "roleId=" + roleId + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}' ; } }
映射配置文件 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 <?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.IRoleDao" > <resultMap id ="roleMap" type ="role" > <id property ="roleId" column ="rid" > </id > <result property ="roleName" column ="role_name" > </result > <result property ="roleDesc" column ="role_desc" > </result > <collection property ="users" ofType ="user" > <id column ="id" property ="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 > </collection > </resultMap > <select id ="findAll" resultMap ="roleMap" > select u.*,r.id as rid,r.role_name,r.role_desc from role r left outer join user_role ur on r.id = ur.rid left outer join user u on u.id = ur.uid </select > </mapper >
补充:JNDI数据源(数据库连接池) 什么时JNDI数据源
JNDI:Java Naming and Directory Interface。是SUN公司推出的一套规范,属于JavaEE技术之一。目的是模仿windows系统中的注册表
使用步骤 1、创建maven工程 3、在META-INF目录中建立一个名为context.xml 的配置文件 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 <?xml version="1.0" encoding="UTF-8" ?> <Context> <!-- <Resource name="jdbc/eesy_mybatis" 数据源的名称 type="javax.sql.DataSource" 数据源类型 auth="Container" 数据源提供者 maxActive="20" 最大活动数 maxWait="10000" 最大等待时间 maxIdle="5" 最大空闲数 username="root" 用户名 password="1234" 密码 driverClassName="com.mysql.jdbc.Driver" 驱动类 url="jdbc:mysql://localhost:3306/eesy_mybatis" 连接url字符串 /> --> <Resource name="jdbc/eesy_mybatis" type="javax.sql.DataSource" auth="Container" maxActive="20" maxWait="10000" maxIdle="5" username="root" password="1234" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/eesy_mybatis" /> </Context>
4、修改SqlMapConfig.xml中的配置(修改如下配置即可) 1 2 3 4 <dataSource type ="JNDI" > <property name ="data_source" value ="java:comp/env/jdbc/eesy_mybatis" /> </dataSource >