mybatis实现Dao层开发
传统开发模式(以上篇博客为例)
编写UserMapper接口
public interface UserMapper {
// 查询所有
public List<User> findAll();
}
编写UserMapper实现类
public class UserMapperImpl implements UserMapper {
@Override
public List<User> findAll() {
try {
// 1.加载核心配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 2.构建工厂
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
// 3.创建会话
SqlSession sqlSession = sqlSessionFactory.openSession();
// 4.执行sql
List<User> list = sqlSession.selectList("UserMapper.findAll");
// 5.释放资源
sqlSession.close();
return list;
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
编写映射文件
<?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="UserMapper">
<!--查询所有-->
<select id="findAll" resultType="User">
select * from user
</select>
</mapper>
junit测试
public class UserMapperTest {
// 模拟service
public static void main(String[] args){
// 调用dao层代码
UserMapper userMapper = new UserMapperImpl();
List<User> list = userMapper.findAll();
System.out.println(list);
}
}
mybatis接口代理模式
UserMapper
public interface UserMapper {
List<User> findAllUser();
}
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.itfxp.mapper.UserMapper">
<!--查询所有用户信息-->
<select id="findAllUser" resultType="User">
select * from user
</select>
</mapper>
SqlMapConfig.xml
<configuration>
<!--加载jdbc.properties配置文件-->
<properties resource="jdbc.properties"></properties>
<!--别名设置-->
<typeAliases>
<package name="com.lifly.entity"></package>
</typeAliases>
<environments default="mysql">
<!--environment 元素体中包含了事务管理和连接池的配置。-->
<environment id="mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!--mappers 元素则包含了一组映射器(mapper),这些映射器的 XML 映射文件包含了 SQL 代码和映射定义信息。-->
<mappers>
<!--<mapper resource="com/lifly/mapper/UserMapper.xml"/>-->
<package name="com.lifly.mapper"></package>
</mappers>
</configuration>
测试
public class MyBatisDaoTest {
public static void main(String[] args) throws IOException {
// 加载核心配置文件
InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
// 构建SqlSessionFactory工厂对象
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(is);
// 通过工厂创建SqlSession会话对象
SqlSession sqlSession = sessionFactory.openSession();
// 获取接口的实现类的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> allUser = mapper.findAllUser();
for (User user : allUser) {
System.out.println(user);
}
}
}
mybatis接口开发规范
- Mapper映射文件的namespace与Mapper接口全限定名一致
- Mapper接口的方法名与id的属性名一致
- 方法的参数类型与parameterType属性类型一致
- 方法的返回值类型与resultType属性类型一致
- 映射文件需要与接口在统一包下,文件名和接口名相同:扫描包,加载所有的映射文件
mybatis实现增删改查
UserMapper接口
public interface UserMapper {
/**
* 查询所有用户信息
* @return
*/
public List<User>findAllUser();
/**
* 添加用户信息
* @param user
*/
public void addUser(User user);
/**
* 修改用户信息
* @param user
*/
public void updateUserById(User user);
/**
* 删除用户系信息
* @param id
*/
public void deleteUserById(int id);
/**
* 根据id查询用户
* @param id
*/
public User queryUserById(int id);
}
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.lifly.mapper.UserMapper">
<!--查询所有用户信息-->
<select id="findAllUser" resultType="User">
select * from user
</select>
<!--添加用户信息-->
<insert id="addUser" parameterType="User">
insert into user (username,birthday,sex,address)
values(#{username},#{birthday},#{sex},#{address})
</insert>
<!--修改用户信息-->
<update id="updateUserById" parameterType="User">
update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>
<!--删除用户信息-->
<delete id="deleteUserById" parameterType="java.lang.Integer">
delete from user where id=#{id}
</delete>
<!--根据用户id查询-->
<select id="queryUserById" parameterType="java.lang.Integer" resultType="User">
select * from user where id=#{id}
</select>
</mapper>
测试代码
public class TestUserMapper {
/**
* 查询用户所有信息
*/
@Test
public void findAllUser(){
//获取sqlSession
SqlSession sqlSession = MybatisUtil.openSession();
//获取接口的实现类的代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> user = mapper.findAllUser();
for (User user1 : user) {
System.out.println(user1);
}
MybatisUtil.close(sqlSession);
}
/**
* 添加用户信息测试
*/
@Test
public void addUser(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUsername("jack");
user.setBirthday(new Date());
user.setSex("男");
user.setAddress("东京");
mapper.addUser(user);
MybatisUtil.commit(sqlSession);
MybatisUtil.close(sqlSession);
}
/**
* 修改用户信息测试
*/
@Test
public void updateUserById(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setId(50);
user.setUsername("rose");
user.setBirthday(new Date());
user.setSex("女");
user.setAddress("东京");
mapper.updateUserById(user);
//提交事务
MybatisUtil.commit(sqlSession);
//释放资源
MybatisUtil.close(sqlSession);
}
/**
* 根据id删除用户信息
*/
@Test
public void deleteUserById(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.deleteUserById(51);
MybatisUtil.commit(sqlSession);
MybatisUtil.close(sqlSession);
}
@Test
public void queryUserById(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.queryUserById(50);
System.out.println(user);
MybatisUtil.close(sqlSession);
}
}
mybatis单表查询
resultMap属性使用
如果数据库返回结果的列名和要封装的属性名完全一致的话用resultType属性
如果不一致的话使用resultMap属性。
例如我们在编写sql给查询字段的时候给字段属性取别名
<!--根据别名查询用户信息-->
<select id="findAllUser" resultMap="userResultMap">
select id as uid ,username as name,birthday as bir,sex as gender,address as addr from user
</select>
如果还像平常那样获取数据会出现以下情况,不会到错,但取出的都输null
为了解决上述问题,就需要来引入resultMap属性,来手动建立字段关系映射。如以下配置文件
<mapper namespace="com.lifly.mapper.UserMapper1">
<resultMap id="userResultMap" type="com.lifly.entity.User">
<!--
如果数据库返回结果的列名和要封装的实体的属性名==完全一致==的话用 ==resultType== 属性
如果数据库返回结果的列名和要封装的实体的属性名有==不一致==的情况用==resultMap== 属性
-->
<!--
resultMap手动建立映射
id="userResultMap"
type="com.lifly.entity.User"建立映射的java的类路径
-->
<id column="id" property="id"></id>
<!--
id:主键 property:实体属性名
column:列名 property:实体属性名
-->
<result column="name" property="username"></result>
<result column="bir" property="birthday"></result>
<result column="gender" property="sex"></result>
<result column="addr" property="address"></result>
<!--
result普通字段
column:列名 property 实体属性名
-->
</resultMap>
<!--根据别名查询用户信息-->
<select id="findAllUser" resultMap="userResultMap">
select id as uid ,username as name,birthday as bir,sex as gender,address as addr from user
</select>
多条件查询
根据id和username查询user表(user表上篇文章已给出)
1.注解方式@Param
UserMapper接口
/**
* 多条件查询方法一
* @param id
* @param username
* @return
*/
public List<User>findByParam1(@Param("id") Integer id,@Param("username") String username);
UserMapper.xml配置文件
<!--多条件查询方法一-->
<select id="findByParam1" resultType="User">
select * from user where id = #{id} and username = #{username}
</select>
测试
/**
* 多条件查询方法一
*/
@Test
public void findByParam1(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper1 mapper = sqlSession.getMapper(UserMapper1.class);
List<User> userList = mapper.findByParam1(50, "rose");
for (User user : userList) {
System.out.println(user);
}
MybatisUtil.close(sqlSession);
}
2.封装成都pojo形式
UserMapper接口
/**
* 多条件查询方法二
* @param user
* @return
*/
public List<User>findByParam2(User user);
UserMapper.xml配置文件
<!--多条件查询方法二-->
<select id="findByParam2" parameterType="User" resultType="User">
select * from user where id = #{id} and username = #{username}
</select>
测试
/**
* 多条件查询方法二
*/
@Test
public void findByParam2(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper1 mapper = sqlSession.getMapper(UserMapper1.class);
User user = new User();
user.setId(49);
user.setUsername("jack");
List<User> userList = mapper.findByParam2(user);
for (User user1 : userList) {
System.out.println(user1);
}
MybatisUtil.close(sqlSession);
}
模糊查询
方式一
因为sql与%%耦合,不建议使用,了解即可
UserMapper接口
/**
* 根据姓名模糊查询用户信息方法一
* @param username
* @return
*/
List<User> findUserByLike1(String username);
UserMapper.xml
<!--根据姓名模糊查询用户信息方法一-->
<select id="findUserByLike1" parameterType="string" resultType="User">
select * from user where username like #{username}
</select>
测试
/**
* 根据用户名模糊查询用户信息方法一
*/
@Test
public void findUserByLike1(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper2 mapper = sqlSession.getMapper(UserMapper2.class);
List<User> userList = mapper.findUserByLike1("%j%");
//Preparing: select * from user where username like ?
for (User user : userList) {
System.out.println(user);
}
MybatisUtil.close(sqlSession);
}
方法二
UserMapper接口
/**
* 根据姓名模糊查询用户信息方法二
* @param username
* @return
*/
List<User>findUserByLike2(String username);
UserMapper.xml
<!--根据姓名模糊查询用户信息方法二-->
<select id="findUserByLike2" parameterType="string" resultType="User">
select * from user where username like "%" #{username} "%"
</select>
测试
/**
* 根据用户名模糊查询用户信息方法二
*/
@Test
public void findUserByLike2(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper2 mapper = sqlSession.getMapper(UserMapper2.class);
List<User> userList = mapper.findUserByLike2("j");
// Preparing: select * from user where username like "%" ? "%"
for (User user : userList) {
System.out.println(user);
}
MybatisUtil.close(sqlSession);
}
方法三
UserMapper接口
/**
* 根据姓名模糊查询用户信息方法三
* @param username
* @return
*/
List<User>findUserByLike3(String username);
UserMapper.xml
<!--根据姓名模糊查询用户信息方法三-->
<select id="findUserByLike3" parameterType="string" resultType="User">
select * from user where username like "%${value }%"
</select>
测试
/**
* 根据用户名模糊查询用户信息方法三
*/
@Test
public void findUserByLike3(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper2 mapper = sqlSession.getMapper(UserMapper2.class);
List<User> userList = mapper.findUserByLike3("j");
// select * from user where username like "%j%"
for (User user : userList) {
System.out.println(user);
}
MybatisUtil.close(sqlSession);
}
方法四
UserMapper接口
/**
* 根据姓名模糊查询用户信息方法四
* @param username
* @return
*/
List<User>findUserByLike4(String username);
UserMapper.xml
<!--根据姓名模糊查询用户信息方法四-->
<select id="findUserByLike4" parameterType="string" resultType="User">
select * from user where username like concat('%',#{username},'%')
</select>
测试
/**
* 根据用户名模糊查询用户信息方法四
*/
@Test
public void findUserByLike4(){
SqlSession sqlSession = MybatisUtil.openSession();
UserMapper2 mapper = sqlSession.getMapper(UserMapper2.class);
List<User> userList = mapper.findUserByLike4("k");
//Preparing: select * from user where username like concat('%',?,'%')
for (User user : userList) {
System.out.println(user);
}
MybatisUtil.close(sqlSession);
}
#{}与${}区别[面试题]
${}:底层是statement
- sql与参数拼接在一起,会出现sql注入的问题
- 每次执行sql都会编译一次
- 接受简单的数据类型命名:${value}
- 接受引用数据类型命名:${属性名}
- 字符串类型需要加 ‘${value}’
#{}:底层是PreparedStatement
- sql与参数分离,不会出现sql注入问题
- sql只需要编译一次
- 接受简单数据类型命名:#{随意写}
- 接收引用数据类型命名:#{属性名}