Mybatis:
MyBatis 是一种持久层框架,它支持自定义 SQL、存储过程以及高级映射。MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java pojo为数据库中的记录。
Mybatis配置:
首先需要导入mybatis有关的jar包
下载地址:https://github.com/mybatis/mybatis-3/releases
创建SqlMapConfig.xml文件:(全局配置文件)
【注意:文件放在config文件夹下(已设置为 Resources Root文件)】
<?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>
<!-- 是用resource属性加载外部配置文件 -->
<properties resource="db.properties">
<!-- 在properties内部用property定义属性 -->
<!-- 如果外部配置文件有该属性,则内部定义属性被外部属性覆盖 -->
<property name="jdbc.username" value="root123" />
<property name="jdbc.password" value="root123" />
</properties>
<!-- 配置mybatis的环境信息,与spring整合,该信息由spring来管理-->
<!-- 和spring整合后 environments配置将废除 -->
<environments default="development">
<environment id="development">
<!-- 配置JDBC事务控制,由mybatis进行管理-->
<transactionManager type="JDBC"></transactionManager>
<!-- 配置数据源,采用mybatis连接池-->
<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>
<!-- 加载映射文件 -->
<mappers>
<mapper resource="sqlmap/User.xml"/>
<mapper resource="mapper/UserMapper.xml" />
<mapper resource="mapper/OrderMapper.xml" />
</mappers>
</configuration>
log4j.properties:
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
映射文件:(在全局配置文件中要加载所需的映射文件)
User.xml:【这是对应UserDao的】
<?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="com.mybatis.dao.UserDao">
<!-- 根据用户ID查询用户信息-->
<!-- select:表示一个MappingStatement对象-->
<!-- id:statement的唯一标识-->
<!-- #{}:表示一个占位符?-->
<!-- #{id}:里面的id表示输入参数的参数名称,如果该参数是简单类型,那么#{}里面的参数名称可以任意-->
<!-- parameterType:输入参数的java类型-->
<!-- resultType:输出结果的所映射的java类型(单条结果所对应的的java类型)-->
<select id="findUserById" parameterType="int" resultType="com.mybatis.po.User">
SELECT * FROM USER WHERE id =#{id}
</select>
<!--查询全部-->
<select id="findUserAll" resultType="com.mybatis.po.User">
SELECT * FROM USER
</select>
<!--通过username查找用户-->
<select id="findUserByUsername" parameterType="String" resultType="com.mybatis.po.User">
select * from user where username like '%${value}%'
</select>
<!-- 新增用户 -->
<insert id="insertUser" parameterType="com.mybatis.po.User">
<!-- selectKey 标签实现主键返回 -->
<!-- keyColumn:主键对应的表中的哪一列 -->
<!-- keyProperty:主键对应的pojo中的哪一个属性 -->
<!-- order:设置在执行insert语句前执行查询id的sql,孩纸在执行insert语句之后执行查询id的sql -->
<!-- resultType:设置返回的id的类型 -->
<selectKey keyColumn="id" keyProperty="id" order="AFTER" resultType="int">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO `user`
(username,birthday,sex,address) VALUES (#{username},#{birthday},#{sex},#{address})
</insert>
<!-- 更新用户 -->
<update id="updateUserById" parameterType="com.mybatis.po.User">
UPDATE `user` SET
username = #{username} WHERE id = #{id}
</update>
<!-- 删除用户 -->
<delete id="deleteUserById" parameterType="int">
delete from user where id=#{id}
</delete>
</mapper>
UserMapper.xml:【这是对应UserMapper的】
<?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:命名空间,用于隔离sql -->
<!-- 还有一个很重要的作用,使用动态代理开发DAO,1. namespace必须和Mapper接口类路径一致 -->
<mapper namespace="com.mybatis.mapper.UserMapper">
<!-- 根据用户ID查询用户信息-->
<!-- select:表示一个MappingStatement对象-->
<!-- id:statement的唯一标识-->
<!-- #{}:表示一个占位符?-->
<!-- #{id}:里面的id表示输入参数的参数名称,如果该参数是简单类型,那么#{}里面的参数名称可以任意-->
<!-- parameterType:输入参数的java类型-->
<!-- resultType:输出结果的所映射的java类型(单条结果所对应的的java类型)-->
<select id="findUserById" parameterType="int" resultType="com.mybatis.po.User">
SELECT * FROM USER WHERE id =#{id}
</select>
<!--查询全部-->
<select id="findUserAll" resultType="com.mybatis.po.User">
SELECT * FROM USER
</select>
<!--通过username查找用户-->
<select id="findUserByUsername" parameterType="String" resultType="com.mybatis.po.User">
select * from user where username like '%${value}%'
</select>
<!-- 新增用户 -->
<insert id="insertUser" parameterType="com.mybatis.po.User">
<!-- selectKey 标签实现主键返回 -->
<!-- keyColumn:主键对应的表中的哪一列 -->
<!-- keyProperty:主键对应的pojo中的哪一个属性 -->
<!-- order:设置在执行insert语句前执行查询id的sql,孩纸在执行insert语句之后执行查询id的sql -->
<!-- resultType:设置返回的id的类型 -->
<selectKey keyColumn="id" keyProperty="id" order="AFTER" resultType="int">
SELECT LAST_INSERT_ID()
</selectKey>
INSERT INTO `user`
(username,birthday,sex,address) VALUES (#{username},#{birthday},#{sex},#{address})
</insert>
<!-- 更新用户 -->
<update id="updateUserById" parameterType="com.mybatis.po.User">
UPDATE `user` SET
username = #{username} WHERE id = #{id}
</update>
<!-- 删除用户 -->
<delete id="deleteUserById" parameterType="int">
delete from user where id=#{id}
</delete>
<!-- 根据条件查询用户 -->
<select id="queryUserByWhere" parameterType="com.mybatis.po.User" resultType="com.mybatis.po.User">
SELECT id, username, birthday, sex, address FROM `user`
<!-- where标签可以自动添加where,同时处理sql语句中第一个and关键字 -->
<where>
<if test="sex != null">
AND sex = #{sex}
</if>
<if test="username != null and username != ''">
AND username LIKE
'%${username}%'
</if>
</where>
</select>
</mapper>
User类:
package com.mybatis.po;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
public class User implements Serializable {
private int id;
private String username;
private String sex;
private Date birthday;
private String address;
public User(){}
public User(String username,Date birthday, String sex, String address) {
this.username = username;
this.sex = sex;
this.birthday = birthday;
this.address = address;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", sex='" + sex + '\'' +
", birthday=" + birthday +
", address='" + address +
'}';
}
}
第一种实现方式:
接口加实现类:
接口:
package com.mybatis.dao;
import com.mybatis.po.User;
import java.util.List;
public interface UserDao {
//通过id查找用户信息
User findUserById(Integer id);
//查找所有用户信息
List<User> findUserAll();
//通过username查找用户信息
List<User> findUserByUsername(String username);
//新增用户
void insertUser(User user);
//通过id更新username
void updateUserById(User user);
//通过id删除指定用户
void deleteUserById(Integer id);
}
实现类:
package com.mybatis.dao.impl;
import com.mybatis.dao.UserDao;
import com.mybatis.po.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import java.util.List;
public class UserDaoImpl implements UserDao {
//创建SqlSessionFactory
public SqlSessionFactory sqlSessionFactory;
public UserDaoImpl(SqlSessionFactory sqlSessionFactory) {
super();
this.sqlSessionFactory = sqlSessionFactory;
}
@Override
public User findUserById(Integer id) {
// 创建SqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 执行查询逻辑
User user = sqlSession.selectOne("findUserById", id);
// 释放资源
sqlSession.close();
return user;
}
@Override
public List<User> findUserAll() {
// 创建SqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 执行查询逻辑
List<User> user = sqlSession.selectList("findUserAll");
// 释放资源
sqlSession.close();
return user;
}
@Override
public List<User> findUserByUsername(String username) {
// 创建SqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 执行查询逻辑
List<User> user = sqlSession.selectList("findUserByUsername",username);
// 释放资源
sqlSession.close();
return user;
}
@Override
public void insertUser(User user) {
// 创建SqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 执行保存逻辑
sqlSession.insert("insertUser", user);
// 提交事务
sqlSession.commit();
// 释放资源
sqlSession.close();
}
@Override
public void updateUserById(User user) {
// 创建SqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 执行保存逻辑
sqlSession.insert("updateUserById", user);
// 提交事务
sqlSession.commit();
// 释放资源
sqlSession.close();
}
@Override
public void deleteUserById(Integer id) {
// 创建SqlSession
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 执行保存逻辑
sqlSession.insert("deleteUserById", id);
// 提交事务
sqlSession.commit();
// 释放资源
sqlSession.close();
}
}
测试类:
package com.mybatis.test;
import com.mybatis.dao.UserDao;
import com.mybatis.dao.impl.UserDaoImpl;
import com.mybatis.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.InputStream;
import java.util.Date;
import java.util.List;
public class UserTest2 {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
// 创建SqlSessionFactoryBuilder
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
// 加载SqlMapConfig.xml配置文件
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
// 创建SqlsessionFactory
this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
}
@Test
public void testFindUserById() {
// 创建DAO
UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
// 执行查询
User user = userDao.findUserById(1);
System.out.println(user);
}
@Test
public void testFindUserAll() {
// 创建DAO
UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
// 执行查询
List<User> list = userDao.findUserAll();
for (User user:list) {
System.out.println(user);
}
}
@Test
public void testFindUserByUsername() {
// 创建DAO
UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
// 执行查询
List<User> list = userDao.findUserByUsername("王");
for (User user:list) {
System.out.println(user);
}
}
@Test
public void testInsertUser() {
// 创建DAO
UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
//封装数据
User user = new User();
user.setUsername("陈小姐");
user.setSex("女");
user.setBirthday(new Date());
user.setAddress("马来山市");
// 执行操作
userDao.insertUser(user);
}
@Test
public void testUpdateUsernameById() {
// 创建DAO
UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
//封装数据
User user = new User();
user.setId(12);
user.setUsername("陈美");
// 执行操作
userDao.updateUserById(user);
}
@Test
public void testDeleteUserById() {
// 创建DAO
UserDao userDao = new UserDaoImpl(this.sqlSessionFactory);
// 执行操作
userDao.deleteUserById(12);
}
}
第二种mapper代理:
创建与映射文件同名的接口,对应相对应操作的方法。
package com.mybatis.mapper;
import com.mybatis.po.OrderUser;
import com.mybatis.po.User;
import java.util.List;
public interface UserMapper {
//通过id查找用户信息
User findUserById(Integer id);
//查找所有用户信息
List<User> findUserAll();
//通过username查找用户信息
List<User> findUserByUsername(String username);
//新增用户
void insertUser(User user);
//通过id更新username
void updateUserById(User user);
//通过id删除指定用户
void deleteUserById(Integer id);
//根据条件查找用户
List<User> queryUserByWhere(User user);
}
测试类:
package com.mybatis.test;
import com.mybatis.po.OrderUser;
import com.mybatis.po.User;
import com.mybatis.mapper.UserMapper;
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.Date;
import java.util.List;
public class UserMapperTest {
private SqlSessionFactory sqlSessionFactory;
@Before
public void init() throws Exception {
// 创建SqlSessionFactoryBuilder
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
// 加载SqlMapConfig.xml配置文件
InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
// 创建SqlsessionFactory
this.sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
}
@Test
public void testFindUserById() {
// 获取sqlSession,和spring整合后由spring管理
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 从sqlSession中获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 执行查询方法
User user = userMapper.findUserById(1);
System.out.println(user);
// 和spring整合后由spring管理
sqlSession.close();
}
@Test
public void testFindUserAll() {
// 获取sqlSession,和spring整合后由spring管理
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 从sqlSession中获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 执行查询方法
List<User> list = userMapper.findUserAll();
for (User user:list) {
System.out.println(user);
}
// 和spring整合后由spring管理
sqlSession.close();
}
@Test
public void testFindUserByUsername() {
// 获取sqlSession,和spring整合后由spring管理
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 从sqlSession中获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 执行查询方法
List<User> list = userMapper.findUserByUsername("王");
for (User user:list) {
System.out.println(user);
}
// 和spring整合后由spring管理
sqlSession.close();
}
@Test
public void testInsertUser() {
// 获取sqlSession,和spring整合后由spring管理
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 从sqlSession中获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//封装数据
User user = new User();
user.setUsername("陈珂");
user.setSex("女");
user.setBirthday(new Date());
user.setAddress("马来山市");
// 执行操作
userMapper.insertUser(user);
// 和spring整合后由spring管理
sqlSession.commit();
sqlSession.close();
}
@Test
public void testUpdateUsernameById() {
// 获取sqlSession,和spring整合后由spring管理
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 从sqlSession中获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
//封装数据
User user = new User();
user.setId(14);
user.setUsername("陈琳");
// 执行操作
userMapper.updateUserById(user);
// 和spring整合后由spring管理
sqlSession.commit();
sqlSession.close();
}
@Test
public void testDeleteUserById() {
// 获取sqlSession,和spring整合后由spring管理
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 从sqlSession中获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 执行操作
userMapper.deleteUserById(13);
// 和spring整合后由spring管理
sqlSession.commit();
sqlSession.close();
}
@Test
public void testQueryUserByWhere() {
// 获取sqlSession,和spring整合后由spring管理
SqlSession sqlSession = this.sqlSessionFactory.openSession();
// 从sqlSession中获取Mapper接口的代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
// 执行查询方法
User u = new User();
u.setSex("女");
List<User> list = userMapper.queryUserByWhere(u);
for (User user:list){
System.out.println(user);
}
// 和spring整合后由spring管理
sqlSession.close();
}
}