mybatis是一个封装了jdbc的不完全的orm持久层框架,它使程序员只关注sql本身,而不需要关注创建连接、statement创建等重复操作,只要具有sql基础就可以上手。相对于hibernate,mybatis不能自动建表,但是可以直接编写sql语句灵活性好易于sql优化和维护。可见mybatis更擅长需求多变的场景,如互联网场景。
创建mybatis项目
1.mybaits的代码由github.com管理,下载地址:https://github.com/mybatis/mybatis-3/releases
创建mybatis生产环境。
2.创建数据库和表
3.编写pojo类
4.编写默认全局配置文件,一般叫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>
<properties resource="db.properties">
<property name="db.username" value="666" />
</properties>-->
<settings></settings>
<!--别名-->
<typeAliases>
<!--给User类定义别名user-->
<typeAlias type="com.aloha.mybatis.po.User" alias="user"/>
<!-- package:指定包名称来为该包下的po类声明别名,默认的别名就是类名(首字母大小写都可) -->
<package name="com.aloha.mybatis.po" />
</typeAliases>
<!-- 配置mybatis的环境信息,与spring整合,该信息由spring来管理 -->
<environments default="development">
<environment id="development">
<!-- 配置JDBC事务控制,由mybatis进行管理 -->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置数据源,采用mybatis连接池 -->
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/mybatis1?useUnicode=true&characterEncoding=utf-8" />
<property name="username" value="root" />
<property name="password" value="root" />
</dataSource>
</environment>
</environments>
<!-- 加载映射文件 -->
<mappers>
<mapper resource="User.xml" />
<mapper resource="mapper/UserMapper.xml" />
<!-- 批量加载映射文件:此方法要求mapper接口和mapper映射文件相同且位于同一目录中 -->
<!--<package name="com.aloha.mybatis.po" />-->
</mappers>
</configuration>
db.properties的配置同dataSource中的配置,配置后可替代value的值。
5.创建mapper映射的User.xml文件:
文件中引入mapper约束:
<?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标签操作数据库:
<mapper namespace="test">
<!--根据用户id查询用户信息
mappedstatement对象
id:statement的唯一标识
resultType:输出结果映射的java类型:单条结果对应的java类型
-->
<select id="findUserById" parameterType="string" resultType="com.aloha.mybatis.po.User" >
SELECT * FROM USER WHERE id = #{id}
</select>
<!--模糊查询-->
<select id="findUserByName" parameterType="java.lang.String" resultType="com.aloha.mybatis.po.User">
SELECT * FROM USER WHERE username LIKE '%${value}%'
</select>
<!--添加
<insert id="insertUser" parameterType="com.aloha.mybatis.po.User">
INSERT INTO USER(username,birthday,sex,address) VALUES (#{username},#{birthday},#{sex},#{address})
</insert>-->
<!--自增主键: LAST_INSERT_ID()是mysql函数-->
<insert id="insertUser" parameterType="com.aloha.mybatis.po.User">
<selectKey keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address})
</insert>
<!--主键返回uuid
如果使用uuid那么id必须为string类型,得改一些定义
-->
<insert id="insertUserUUID" keyProperty="com.aloha.mybatis.po.User">
<selectKey keyProperty="id" resultType="string" order="BEFORE">
SELECT UUID()
</selectKey>
INSERT INTO USER(id,username,birthday,sex,address) VALUES(#{id},#{username},#{birthday},#{sex},#{address})
</insert>
</mapper>
创建测试类写测试方法:
public class MybatisTest {
@Test
public void findUserByIdTest() throws Exception {
//读取全局配置文件路径
String resource = "SqlMapConfig.xml" ;
InputStream inputStream = Resources.getResourceAsStream(resource);
//创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//创建SqlSession
SqlSession sqlSession = sqlSessionFactory.openSession();
User o = sqlSession.selectOne("test.findUserById", 2);
System.out.println(o);
sqlSession.close();
}
}
测试结果:
如果是模糊查询:
返回的结果数量大于2 的话应该使用selectList查询,用List接收。
#{}和${}
#{}表示占位符?,#{}接收简单类型的参数时,里面的名称可以任意 ${}表示拼接符,${}接收简单类型的参数时,里面的名称必须是value ${}里面的值会原样输出,不加解析(如果该参数值是字符串,有不会添加引号) ${}存在sql注入的风险,但是有些场景下必须使用,比如排序后面会动态传入排序的列名 |
Mapper接口开发需要遵循以下规范:
1、 Mapper.xml文件中的namespace与mapper接口的类路径相同。
2、 Mapper接口方法名和Mapper.xml中定义的每个statement的id相同
3、 Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql 的parameterType的类型相同
4、 Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同
接口开发简单实例:
创建接口
public interface UserMapper {
public User findUserById(int id) throws Exception;
public void insertUser(User user) throws Exception;
}
编写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 namespace="com.aloha.mybatis.po.usermapper.UserMapper">
<!-- 根据用户ID查询用户信息 -->
<select id="findUserById" parameterType="int" resultType="User">
SELECT * FROM USER WHERE id =#{id}
</select>
<!-- 添加用户 -->
<insert id="insertUser" parameterType="com.aloha.mybatis.po.User">
<selectKey keyProperty="id" resultType="int" order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO USER(username,birthday,sex,address) VALUES(#{username},#{birthday},#{sex},#{address})
</insert>
</mapper>
编写测试类:
package com.aloha.mybatis.po.usermapper;
import com.aloha.mybatis.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;
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void setUp() throws Exception{
String resource = "SqlMapConfig.xml" ;
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
@Test
public void userMapperTest() throws Exception{
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User userById = mapper.findUserById(1);
System.out.println(userById);
sqlSession.close();
}
@Test
public void insertUserTest() throws Exception{
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("王尼玛");
user.setAddress("西安");
mapper.insertUser(user);
sqlSession.commit();
sqlSession.close();
}
}
测试结果:
注意:
SqlSession对象创建不能提取公共部分是因为SqlSession是存储数据的是线程不安全的。
执行插入操作时需要提交事务否则执行不完成。
使用mapper接口方式开发mapper标签的namespace属性必须时Mapper类的全路径。
Mapper的xml文件必须和
请遵守mapper接口编程规范。。。
MyBatis整合Spring
mybatis将加载properties、创建数据资源、sqlSessionFactory管理和bean管理(包括mapper管理)权交给Spring。
创建SqlMapConfig.xml:(可以看到,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>
<!--自定义别名-->
<typeAliases>
<package name="com.aloha.mybatis.po"/>
</typeAliases>
<!--加载映射文件-->
<mappers>
<mapper resource="mybatis/sqlMap/User.xml"/>
</mappers>
</configuration>
创建pojo的User.java和User.xml;
创建UserMapperSpring.java:
package com.aloha.mybatis.mapper;
import com.aloha.mybatis.po.User;
public interface UserMapperSpring {
public User findUserById(int id) throws Exception;
public void insertUser(User user) throws Exception;
}
在UserMapperSpring.java同目录下创建UserMapperSpring.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 namespace="com.aloha.mybatis.mapper.UserMapperSpring">
<select id="findUserById" parameterType="int" resultType="com.aloha.mybatis.po.User">
SELECT * FROM USER WHERE id = #{id}
</select>
</mapper>
创建applicationContext.xml:
<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"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd ">
<!-- 加载jdbc的配置文件 -->
<context:property-placeholder location="db.properties"/>
<!-- 创建数据源-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${db.driver}"/>
<property name="url" value="${db.url}"/>
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
<property name="maxActive" value="10"/>
<property name="maxIdle" value="5"/>
</bean>
<!--管理sqlSessionFactory-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="configLocation" value="mybatis/SqlMapConfig.xml"/>
<property name="dataSource" ref="dataSource"/>
</bean>
<!--管理UserDao-->
<bean id="userDao" class="com.aloha.mybatis.dao.UserDaoImplSpring">
<!--依赖注入sqlSessionFactory-->
<property name="sqlSessionFactory" ref="sqlSessionFactory"></property>
</bean>
<!--通过mapper方式查询:Spring创建mapper代理,可以单个,也可以批量-->
<bean id="userMapperSpring" class="org.mybatis.spring.mapper.MapperFactoryBean">
<property name="mapperInterface" value="com.aloha.mybatis.mapper.UserMapperSpring"/>
<property name="sqlSessionFactory" ref="sqlSessionFactory"/>
</bean>
</beans>
编写测试类:
package com.aloha.mybatis.mapper;
import com.aloha.mybatis.po.User;
import org.junit.Before;
import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class UserMapperTest {
private ApplicationContext context ;
@Before
public void SetUp() throws Exception{
context = new ClassPathXmlApplicationContext("spring/applicationContext.xml");
}
@Test
public void findUserByIdTest() throws Exception{
UserMapperSpring userMapperSpring =(UserMapperSpring) context.getBean("userMapperSpring");
User userById = userMapperSpring.findUserById(2);
System.out.println(userById);
}
}
测试结果: