组团学

MyBatis基于DAO实现CRUD

阅读 (324430)

1、根据id查询

1.1、修改IUserDao.java文件

package com.tianyi.dao; import com.tianyi.javabean.User; import java.util.List; public interface IUserDao { List<User> findAll(); User findById(Integer uid); }

1.2、修改IUserDao.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.tianyi.dao.IUserDao"> <!-- 配置查询所有操作 --> <select id="findAll" resultType="com.tianyi.javabean.User"> select * from user </select> <select id="findById" resultType="com.tianyi.javabean.User" parameterType="int"> select * from user where id=#{uid} </select> </mapper>

注:

resultType 属性: 用于指定结果集的类型。
**parameterType 属性:**用于指定传入参数的类型。

sql 语句中使用#{}字符:
它代表占位符,相当于原来jdbc部分所学的?,都是用于执行语句时替换实际的数据。
具体的数据是由#{}里面的内容决定的。

#{}中内容的写法:

由于数据类型是基本类型,所以此处可以随意写。

1.3、编写测试类MbTest1.java

package com.tianyi.test; import com.tianyi.dao.IUserDao; import com.tianyi.javabean.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.After; import org.junit.Before; import org.junit.Test; import java.io.InputStream; public class MbTest1 { private InputStream in ; private SqlSessionFactory factory; private SqlSession session; private IUserDao userDao; @Test public void testFindOne() { //6.执行操作 User user = userDao.findById(1); System.out.println(user); } @Before //在测试方法执行之前执行 public void init()throws Exception { //1.读取配置文件 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.创建构建者对象 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //3.创建 SqlSession 工厂对象 factory = builder.build(in); // 4.创建 SqlSession 对象 session = factory.openSession(); //5.创建 Dao 的代理对象 userDao = session.getMapper(IUserDao.class); } @After//在测试方法执行完成之后执行 public void destroy() throws Exception{ session.commit(); //7.释放资源 session.close(); in.close(); } }

单元测试导包

<dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency>

注意:

执行测试时出现:不再支持源选项 1.5。请使用 1.6 或更高版本。

在pom.xml加入

<properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.source>1.9</maven.compiler.source> <maven.compiler.target>1.9</maven.compiler.target> </properties>

2、保存操作

2.1、修改IUserDao.java文件

package com.tianyi.dao; import com.tianyi.javabean.User; import java.util.List; public interface IUserDao { List<User> findAll(); User findById(Integer uid); int addUser(User user); }

2.2、修改IUserDao.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.tianyi.dao.IUserDao"> <!-- 配置查询所有操作 --> <select id="findAll" resultType="com.tianyi.javabean.User"> select * from user </select> <select id="findById" resultType="com.tianyi.javabean.User" parameterType="int"> select * from user where id=#{uid} </select> <insert id="addUser" parameterType="com.tianyi.javabean.User" > insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address}) </insert> </mapper>

注:

{}中内容的写法:

由于我们保存方法的参数是 一个 User 对象,此处要写 User 对象中的属性名称。它用的是 ognl 表达式。

ognl 表达式:
它是 apache 提供的一种表达式语言,全称是:Object Graphic Navigation Language 对象图导航语言,它是按照一定的语法格式来获取数据的。

语法格式就是使用 #{对象.对象}的方式

{user.username}它会先去找 user 对象,然后在 user 对象中找到 username 属性,并调用getUsername()方法把值取出来。但是我们在 parameterType 属性上指定了实体类名称,所以可以省略 user. 而直接写 username。

2.3、修改MbTest1.java文件

package com.tianyi.test; import com.tianyi.dao.IUserDao; import com.tianyi.javabean.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.After; import org.junit.Before; import org.junit.Test; import java.io.InputStream; import java.util.Date; public class MbTest1 { private InputStream in ; private SqlSessionFactory factory; private SqlSession session; private IUserDao userDao; @Test public void testFindOne() { //6.执行操作 User user = userDao.findById(1); System.out.println(user); } @Test public void testAddUser(){ //增加操作 User user=new User(); user.setUsername("王小红"); user.setBirthday(new Date()); user.setAddress("北京昌平"); user.setSex("女"); System.out.println("保存前:"+user); userDao.addUser(user); System.out.println("保存后:"+user); } @Before //在测试方法执行之前执行 public void init()throws Exception { //1.读取配置文件 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.创建构建者对象 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //3.创建 SqlSession 工厂对象 factory = builder.build(in); // 4.创建 SqlSession 对象 session = factory.openSession(); //5.创建 Dao 的代理对象 userDao = session.getMapper(IUserDao.class); } @After//在测试方法执行完成之后执行 public void destroy() throws Exception{ session.commit(); //7.释放资源 session.close(); in.close(); } }

2.4、设置新增加记录id返回值

<?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.tianyi.dao.IUserDao"> <!-- 配置查询所有操作 --> <select id="findAll" resultType="com.tianyi.javabean.User"> select * from user </select> <select id="findById" resultType="com.tianyi.javabean.User" parameterType="int"> select * from user where id=#{uid} </select> <insert id="addUser" parameterType="com.tianyi.javabean.User" > <selectKey keyColumn="id" keyProperty="id" resultType="java.lang.Integer" > select last_insert_id() </selectKey> insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address}); </insert> </mapper>

注:

selectKey 标签表示子查询中主键的提取问题
keyColumn表示查询语句返回结果的列名
SELECT LAST_INSERT_ID():得到刚 insert 进去记录的主键值,只适用与自增主键
keyProperty:将查询到主键值设置到 parameterType 指定的对象的那个属性
order:SELECT LAST_INSERT_ID() 执行顺序,相对于 insert 语句来说它的执行顺序(after,before)
resultType:指定 SELECT LAST_INSERT_ID() 的结果类型

3、用户更新

3.1、修改IUserDao.java文件

package com.tianyi.dao; import com.tianyi.javabean.User; import java.util.List; public interface IUserDao { List<User> findAll(); User findById(Integer uid); int addUser(User user); int updateUser(User user); }

3.2、修改IUserDao.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.tianyi.dao.IUserDao"> <!-- 配置查询所有操作 --> <select id="findAll" resultType="com.tianyi.javabean.User"> select * from user </select> <select id="findById" resultType="com.tianyi.javabean.User" parameterType="int"> select * from user where id=#{uid} </select> <insert id="addUser" parameterType="com.tianyi.javabean.User" > <selectKey keyColumn="id" keyProperty="id" resultType="java.lang.Integer" > select last_insert_id() </selectKey> insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address}); </insert> <update id="updateUser" parameterType="com.tianyi.javabean.User"> update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id} </update> </mapper>

3.3、修改MbTest1.java文件

package com.tianyi.test; import com.tianyi.dao.IUserDao; import com.tianyi.javabean.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.After; import org.junit.Before; import org.junit.Test; import java.io.InputStream; import java.util.Date; public class MbTest1 { private InputStream in ; private SqlSessionFactory factory; private SqlSession session; private IUserDao userDao; @Test public void testFindOne() { //6.执行操作 User user = userDao.findById(1); System.out.println(user); } @Test public void testAddUser(){ User user=new User(); user.setUsername("王小红"); user.setBirthday(new Date()); user.setAddress("北京昌平"); user.setSex("女"); System.out.println("保存前:"+user); int id=userDao.addUser(user); System.out.println("保存后:"+user); System.out.println(user.getId()); } @Test public void updateUser(){ User user=userDao.findById(6); user.setSex("男"); userDao.updateUser(user); } @Before //在测试方法执行之前执行 public void init()throws Exception { //1.读取配置文件 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.创建构建者对象 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //3.创建 SqlSession 工厂对象 factory = builder.build(in); // 4.创建 SqlSession 对象 session = factory.openSession(); //5.创建 Dao 的代理对象 userDao = session.getMapper(IUserDao.class); } @After//在测试方法执行完成之后执行 public void destroy() throws Exception{ session.commit(); //7.释放资源 session.close(); in.close(); } }

4、用户删除

4.1、修改IUserDao.java文件

package com.tianyi.dao; import com.tianyi.javabean.User; import java.util.List; public interface IUserDao { List<User> findAll(); User findById(Integer uid); int addUser(User user); int updateUser(User user); int deleteUser(Integer uid); }

4.2、修改IUserDao.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.tianyi.dao.IUserDao"> <!-- 配置查询所有操作 --> <select id="findAll" resultType="com.tianyi.javabean.User"> select * from user </select> <select id="findById" resultType="com.tianyi.javabean.User" parameterType="int"> select * from user where id=#{uid} </select> <insert id="addUser" parameterType="com.tianyi.javabean.User" > <selectKey keyColumn="id" keyProperty="id" resultType="java.lang.Integer" > select last_insert_id() </selectKey> insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address}); </insert> <update id="updateUser" parameterType="com.tianyi.javabean.User"> update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id} </update> <delete id="deleteUser" parameterType="int"> delete from user where id=#{id} </delete> </mapper>

4.3、修改MbTest1.java文件

package com.tianyi.test; import com.tianyi.dao.IUserDao; import com.tianyi.javabean.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.After; import org.junit.Before; import org.junit.Test; import java.io.InputStream; import java.util.Date; public class MbTest1 { private InputStream in ; private SqlSessionFactory factory; private SqlSession session; private IUserDao userDao; @Test public void testFindOne() { //6.执行操作 User user = userDao.findById(1); System.out.println(user); } @Test public void testAddUser(){ User user=new User(); user.setUsername("王小红"); user.setBirthday(new Date()); user.setAddress("北京昌平"); user.setSex("女"); System.out.println("保存前:"+user); int id=userDao.addUser(user); System.out.println("保存后:"+user); System.out.println(user.getId()); } @Test public void updateUser(){ User user=userDao.findById(6); user.setSex("男"); int n=userDao.updateUser(user); System.out.println("n="+n); } @Test public void deleteUser(){ int n=userDao.deleteUser(12); System.out.println("n="+n); } @Before //在测试方法执行之前执行 public void init()throws Exception { //1.读取配置文件 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.创建构建者对象 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //3.创建 SqlSession 工厂对象 factory = builder.build(in); // 4.创建 SqlSession 对象 session = factory.openSession(); //5.创建 Dao 的代理对象 userDao = session.getMapper(IUserDao.class); } @After//在测试方法执行完成之后执行 public void destroy() throws Exception{ session.commit(); //7.释放资源 session.close(); in.close(); } }

5、用户模糊查询

5.1、修改IUserDao.java文件

package com.tianyi.dao; import com.tianyi.javabean.User; import java.util.List; public interface IUserDao { List<User> findAll(); User findById(Integer uid); int addUser(User user); int updateUser(User user); int deleteUser(Integer uid); List<User> findByName(String username); }

5.2、修改IUserDao.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.tianyi.dao.IUserDao"> <!-- 配置查询所有操作 --> <select id="findAll" resultType="com.tianyi.javabean.User"> select * from user </select> <select id="findById" resultType="com.tianyi.javabean.User" parameterType="int"> select * from user where id=#{uid} </select> <insert id="addUser" parameterType="com.tianyi.javabean.User" > <selectKey keyColumn="id" keyProperty="id" resultType="java.lang.Integer" > select last_insert_id() </selectKey> insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address}); </insert> <update id="updateUser" parameterType="com.tianyi.javabean.User"> update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id} </update> <delete id="deleteUser" parameterType="int"> delete from user where id=#{id} </delete> <select id="findByName" resultType="com.tianyi.javabean.User" parameterType="java.lang.String"> select * from user where username like #{username} </select> </mapper>

5.3、修改MbTest1.java文件

package com.tianyi.test; import com.tianyi.dao.IUserDao; import com.tianyi.javabean.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.After; import org.junit.Before; import org.junit.Test; import java.io.InputStream; import java.util.Date; import java.util.List; public class MbTest1 { private InputStream in ; private SqlSessionFactory factory; private SqlSession session; private IUserDao userDao; @Test public void testFindOne() { //6.执行操作 User user = userDao.findById(1); System.out.println(user); } @Test public void testAddUser(){ User user=new User(); user.setUsername("王小红"); user.setBirthday(new Date()); user.setAddress("北京昌平"); user.setSex("女"); System.out.println("保存前:"+user); int id=userDao.addUser(user); System.out.println("保存后:"+user); System.out.println(user.getId()); } @Test public void updateUser(){ User user=userDao.findById(6); user.setSex("男"); int n=userDao.updateUser(user); System.out.println("n="+n); } @Test public void deleteUser(){ int n=userDao.deleteUser(12); System.out.println("n="+n); } @Test public void findByName(){ List<User> users=userDao.findByName("%王%"); for(User u:users){ System.out.println(u.getId()+":"+u.getUsername()); } } @Before //在测试方法执行之前执行 public void init()throws Exception { //1.读取配置文件 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.创建构建者对象 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //3.创建 SqlSession 工厂对象 factory = builder.build(in); // 4.创建 SqlSession 对象 session = factory.openSession(); //5.创建 Dao 的代理对象 userDao = session.getMapper(IUserDao.class); } @After//在测试方法执行完成之后执行 public void destroy() throws Exception{ session.commit(); //7.释放资源 session.close(); in.close(); } }

5.4、模糊查询的另一种配置方式

修改IUserDao.xml文件

<select id="findByName" resultType="com.tianyi.javabean.User" parameterType="java.lang.String"> select * from user where username like '%${value}%' </select>

注:

将原来的#{}占位符,改成了${value}。注意如果用模糊查询的这种写法,那么​{value}的写法就是固定的,不能写成其它名字。

5.5、#{}与${}区别

#{}表示一个占位符号

通过#{}可以实现 preparedStatement 向占位符中设置值,自动进行java类型和jdbc类型转换,#{}可以有效防止sql注入。 #{}可以接收简单类型值或pojo属性值。如果parameterType传输单个简单类型值,#{}括号中可以是value或其它名称。
**{}表示拼接sql串** 通过{}可以将parameterType传入的内容拼接在sql中且不进行jdbc类型转换,{}可以接收简单类型值或pojo属性值,如果 parameterType传输单个简单类型值,{}括号中只能是value。

6、查询中的函数使用

6.1、修改IUserDao.java文件

package com.tianyi.dao; import com.tianyi.javabean.User; import java.util.List; public interface IUserDao { List<User> findAll(); User findById(Integer uid); int addUser(User user); int updateUser(User user); int deleteUser(Integer uid); List<User> findByName(String username); int allCount(); }

6.2、修改IUserDao.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.tianyi.dao.IUserDao"> <!-- 配置查询所有操作 --> <select id="findAll" resultType="com.tianyi.javabean.User"> select * from user </select> <select id="findById" resultType="com.tianyi.javabean.User" parameterType="int"> select * from user where id=#{uid} </select> <insert id="addUser" parameterType="com.tianyi.javabean.User" > <selectKey keyColumn="id" keyProperty="id" resultType="java.lang.Integer" > select last_insert_id() </selectKey> insert into user(username,birthday,sex,address)values(#{username},#{birthday},#{sex},#{address}); </insert> <update id="updateUser" parameterType="com.tianyi.javabean.User"> update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id} </update> <delete id="deleteUser" parameterType="int"> delete from user where id=#{id} </delete> <select id="findByName" resultType="com.tianyi.javabean.User" parameterType="java.lang.String"> select * from user where username like '%${value}%' </select> <select id="allCount" resultType="int"> select count(*) from user </select> </mapper>

6.3、修改MbTest1.java文件

package com.tianyi.test; import com.tianyi.dao.IUserDao; import com.tianyi.javabean.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.After; import org.junit.Before; import org.junit.Test; import java.io.InputStream; import java.util.Date; import java.util.List; public class MbTest1 { private InputStream in ; private SqlSessionFactory factory; private SqlSession session; private IUserDao userDao; @Test public void testFindOne() { //6.执行操作 User user = userDao.findById(1); System.out.println(user); } @Test public void testAddUser(){ User user=new User(); user.setUsername("王小红"); user.setBirthday(new Date()); user.setAddress("北京昌平"); user.setSex("女"); System.out.println("保存前:"+user); int id=userDao.addUser(user); System.out.println("保存后:"+user); System.out.println(user.getId()); } @Test public void updateUser(){ User user=userDao.findById(6); user.setSex("男"); int n=userDao.updateUser(user); System.out.println("n="+n); } @Test public void deleteUser(){ int n=userDao.deleteUser(12); System.out.println("n="+n); } @Test public void findByName(){ List<User> users=userDao.findByName("王"); for(User u:users){ System.out.println(u.getId()+":"+u.getUsername()); } } @Test public void allCount(){ int n=userDao.allCount(); System.out.println(n); } @Before //在测试方法执行之前执行 public void init()throws Exception { //1.读取配置文件 in = Resources.getResourceAsStream("SqlMapConfig.xml"); //2.创建构建者对象 SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder(); //3.创建 SqlSession 工厂对象 factory = builder.build(in); // 4.创建 SqlSession 对象 session = factory.openSession(); //5.创建 Dao 的代理对象 userDao = session.getMapper(IUserDao.class); } @After//在测试方法执行完成之后执行 public void destroy() throws Exception{ session.commit(); //7.释放资源 session.close(); in.close(); } }
需要 登录 才可以提问哦