Mybatis的CRUD操作
1、先给出实体类 User.java (和上篇博客一样,此处不予展示)
2、给出接口 IUserDao
package dao;
import java.util.List;
import domain.User;
public interface IUserDao {
/**
* 查询所有
* @return
*/
List<User> findAll();
/**
* 保存用户信息
*/
void save(User user);
/**
* 更新用户信息
*/
void update(User user);
/**
* 根据id删除用户
*/
void delete(Integer id);
/**
* 根据id查询用户
*/
User FindById(Integer id);
/**
* 根据输入的name进行模糊查询
*/
List<User> FindByName(String name);
/**
* 查询总用户数
*/
int FindTotal();
}
3、给出配置文件 IUserMapper.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="dao.IUserDao">
<!--配置查询所有-->
<select id="findAll" resultType="domain.User">
select * from user
</select>
<!-- 保存用户信息 -->
<insert id="save" parameterType="domain.User">
insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address});
</insert>
<!-- 更新用户信息 -->
<update id="update" parameterType="domain.User">
update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id};
</update>
<!-- 根据id删除用户 -->
<delete id="delete" parameterType="Integer">
delete from user where id=#{id};
</delete>
<!-- 根据id查询用户 -->
<select id="FindById" parameterType="Integer" resultType="domain.User">
select * from user where id=#{id};
</select>
<!-- 根据输入的name进行模糊查询 -->
<select id="FindByName" parameterType="String" resultType="domain.User">
select * from user where username like #{name};
</select>
<!-- 查询总用户数 -->
<select id="FindTotal" resultType="Integer">
select count(*) from user;
</select>
</mapper>
4、给出 conf.xml 配置文件(和上篇博客一样,此处不予展示)
5、接下来会逐一展示各种操作:
-
保存用户信息的Test代码:
import java.io.IOException; import java.io.InputStream; import java.util.Date; 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 dao.IUserDao; import domain.User; public class SaveUser{ public static void main(String[] args) throws IOException { User user= new User(); user.setUsername("lisi"); user.setBirthday(new Date()); user.setSex("女"); user.setAddress("beijing"); //1.读取配置文件 InputStream in = Resources.getResourceAsStream("conf.xml"); //2.创建SqlSessionFactory工厂 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(in); //3.使用工厂生产SqlSession对象 SqlSession session = factory.openSession(); //4.使用SqlSession创建Dao接口的代理对象 IUserDao userDao = session.getMapper(IUserDao.class); //5.执行保存方法 userDao.save(user); //提交事务 session.commit(); System.out.println("已保存"); session.close(); in.close(); } }
如果 factory.openSession() 参数设为true,则为自动提交事务,就不需要手动提交事务,不需要session.commit();
为了简便起见,可以改写成:
-
更新用户信息
import java.io.IOException; import java.io.InputStream; import java.util.Date; 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 dao.IUserDao; import domain.User; public class update { public static void main(String[] args) throws IOException { User user2= new User(); user2.setId(49); user2.setUsername("lalalin"); user2.setBirthday(new Date()); user2.setSex("女"); user2.setAddress("beijing"); //1.读取配置文件 InputStream in = Resources.getResourceAsStream("conf.xml"); //2.创建SqlSessionFactory工厂 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(in); //3.使用工厂生产SqlSession对象 SqlSession session = factory.openSession(); //4.使用SqlSession创建Dao接口的代理对象 IUserDao userDao = session.getMapper(IUserDao.class); //5、执行更新保存方法 userDao.update(user2); //提交事务 session.commit(); System.out.println("已更新保存"); session.close(); in.close(); } }
- 根据 id 删除用户
import java.io.IOException; import java.io.InputStream; import java.util.Date; 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 dao.IUserDao; import domain.User; public class deleteUser { public static void main(String[] args) throws IOException { //1.读取配置文件 InputStream in = Resources.getResourceAsStream("conf.xml"); //2.创建SqlSessionFactory工厂 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(in); //3.使用工厂生产SqlSession对象 SqlSession session = factory.openSession(); //4.使用SqlSession创建Dao接口的代理对象 IUserDao userDao = session.getMapper(IUserDao.class); //5.执行删除方法 userDao.delete(49); //提交事务 session.commit(); System.out.println("已删除该条记录"); session.close(); in.close(); } }
- 根据id查询用户
import java.io.IOException; import java.io.InputStream; 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 dao.IUserDao; import domain.User; public class FindById { public static void main(String[] args) throws IOException { //1.读取配置文件 InputStream in = Resources.getResourceAsStream("conf.xml"); //2.创建SqlSessionFactory工厂 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(in); //3.使用工厂生产SqlSession对象 SqlSession session = factory.openSession(); //4.使用SqlSession创建Dao接口的代理对象 IUserDao userDao = session.getMapper(IUserDao.class); //5.执行根据id查询用户方法 User user = userDao.FindById(41); System.out.println(user); //提交事务 session.commit(); session.close(); in.close(); } }
- 根据输入的name进行模糊查询
import java.io.IOException; import java.io.InputStream; import java.util.List; 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 dao.IUserDao; import domain.User; public class FindByName { public static void main(String[] args) throws IOException { //1.读取配置文件 InputStream in = Resources.getResourceAsStream("conf.xml"); //2.创建SqlSessionFactory工厂 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(in); //3.使用工厂生产SqlSession对象 SqlSession session = factory.openSession(); //4.使用SqlSession创建Dao接口的代理对象 IUserDao userDao = session.getMapper(IUserDao.class); //5.执行模糊查询方法 List<User> users = userDao.FindByName("%l%"); for(User user: users) { System.out.println(user); } //提交事务 session.commit(); session.close(); in.close(); } }
- 查询总用户数
import java.io.IOException; import java.io.InputStream; 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 dao.IUserDao; import domain.User; public class FindTotal { public static void main(String[] args) throws IOException { //1.读取配置文件 InputStream in = Resources.getResourceAsStream("conf.xml"); //2.创建SqlSessionFactory工厂 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); SqlSessionFactory factory = builder.build(in); //3.使用工厂生产SqlSession对象 SqlSession session = factory.openSession(); //4.使用SqlSession创建Dao接口的代理对象 IUserDao userDao = session.getMapper(IUserDao.class); //5.执行查询总记录条数方法 int count = userDao.FindTotal(); System.out.println(count); //提交事务 session.commit(); session.close(); in.close(); } }
优化代码
问题扩展:新增用户 id 的返回值
新增用户后,同时还要返回当前新增用户的 id 值,因为 id 是由数据库的自动增长来实现的,所以就相当于我们要在新增后将自动增长 auto_increment 的值返回。
-
改写 IUserMapper.xml
<!-- 保存用户信息 --> <insert id="save" parameterType="domain.User"> <!-- 配置插入操作后,获取插入数据的id --> <selectKey keyProperty="userId" keyColumn="id" resultType="int" order="AFTER"> select last_insert_id(); </selectKey> insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address}); </insert>
keyProperty代表它对应的属性名称
keyColumn数据库的id值
resultType返回值类型
order表示什么时候进行操作- 更改 Save.java,红线部分为改变的代码,其实就是加了两句System输出
运行结果:
- 更改 Save.java,红线部分为改变的代码,其实就是加了两句System输出