增删改查
新建java工程如下
在数据库中test创建表user
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> <!-- 和spring整合后 environments配置将废除--> <environments default="development"> <environment id="development"> <!-- 使用jdbc事务管理--> <transactionManager type="JDBC" /> <!-- 数据库连接池--> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/test?characterEncoding=utf-8" /> <property name="username" value="root" /> <property name="password" value="root" /> </dataSource> </environment> </environments> <mappers> <mapper resource="User.xml"/> </mappers> </configuration> User.java import java.util.Date; public class User { private int id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String 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; } } user.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"> <!-- namespace:命名空间,做sql隔离 --> <mapper namespace="test"> <!-- id:sql语句唯一标识 parameterType:指定传入参数类型 resultType:返回结果集类型 #{}占位符:起到占位作用,如果传入的是基本类型(string,long,double,int,boolean,float等),那么#{}中的变量名称可以随意写. --> <select id="findUserById" parameterType="java.lang.Integer" resultType="reed.pojo.User"> select * from user where id=#{id} </select> <!-- 如果返回结果为集合,可以调用selectList方法,这个方法返回的结果就是一个集合,所以映射文件中应该配置成集合泛型的类型 ${}拼接符:字符串原样拼接,如果传入的参数是基本类型(string,long,double,int,boolean,float等),那么${}中的变量名称必须是value 注意:拼接符有sql注入的风险,所以慎重使用 --> <select id="findUserByUserName" parameterType="java.lang.String" resultType="reed.pojo.User"> select * from user where userName like '%${value}%' </select> <insert id="insertUser" parameterType="reed.pojo.User"> insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address}) </insert> <update id="updateUserById" parameterType="reed.pojo.User"> update user set sex=#{sex} where id=#{id} </update> <delete id="deleteUserById" parameterType="int" > delete from user where id=#{id} </delete> </mapper>
MybatisTest.java
package reed.test; import java.io.IOException; import java.io.InputStream; import java.util.Date; 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 org.junit.Before; import org.junit.Test; import reed.pojo.User; public class Mybatis_first { //会话工厂 private SqlSessionFactory sqlSessionFactory; @Before public void createSqlSessionFactory() throws IOException { // 配置文件 String resource = "SqlMapConfig.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); // 使用SqlSessionFactoryBuilder从xml配置文件中创建SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder() .build(inputStream); } // 根据 id查询用户信息 @Test public void testFindUserById() { // 数据库会话实例 SqlSession sqlSession = null; try { // 创建数据库会话实例sqlSession sqlSession = sqlSessionFactory.openSession(); // 查询单个记录，根据用户id查询用户信息 User user = sqlSession.selectOne("test.findUserById", 1); // 输出用户信息 System.out.println(user); } catch (Exception e) { e.printStackTrace(); } finally { if (sqlSession != null) { sqlSession.close(); } } } @Test public void testFindUserByUserName(){ SqlSession sqlSession = null; try{ sqlSession=sqlSessionFactory.openSession(); List<User>list=sqlSession.selectList("test.findUserByUserName", "张"); System.out.println(list.size()); } catch (Exception e) { e.printStackTrace(); } finally { if (sqlSession != null) { sqlSession.close(); } } } @Test public void insertUser(){ SqlSession sqlSession=null; try{ sqlSession=sqlSessionFactory.openSession(); User user=new User(); user.setUsername("李四"); user.setAddress("安徽安庆"); user.setBirthday(new Date()); sqlSession.insert("test.insertUser", user); sqlSession.commit(); } catch (Exception e) { e.printStackTrace(); } finally { if (sqlSession != null) { sqlSession.close(); } } } @Test public void updateUserById() throws Exception{ SqlSession sqlSession=null; sqlSession=sqlSessionFactory.openSession(); User user=new User(); user.setId(2); user.setSex("1"); sqlSession.update("test.updateUserById", user); sqlSession.commit(); } @Test public void deleteUserById() throws Exception{ SqlSession sqlSession=null; sqlSession=sqlSessionFactory.openSession(); sqlSession.delete("test.deleteUserById", 2); sqlSession.commit(); sqlSession.close(); } } ------------------------------------------------------------------原始dao写法
新建工程如下
UserDao
import java.util.List; import reed.pojo.User; public interface UserDao { public User findUserById(Integer id); public List<User> findUserByUserName(String userName); public void Insert(User user); public void deleteUserById(Integer id); } UserDaoImpl package reed.dao; import java.util.Date; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import reed.pojo.User; public class UserDaoImpl implements UserDao{ private SqlSessionFactory sqlSessionFactory; public UserDaoImpl(SqlSessionFactory sqlSessionFactory) { this.sqlSessionFactory = sqlSessionFactory; } @Override public User findUserById(Integer id) { SqlSession openSession=sqlSessionFactory.openSession(); User user=openSession.selectOne("test.findUserById", id); openSession.close(); return user; } @Override public List<User> findUserByUserName(String userName) { SqlSession openSession=sqlSessionFactory.openSession(); List<User>list=openSession.selectList("test.findUserByUserName", userName); openSession.close(); return list; } @Override public void Insert(User user) { SqlSession openSession=sqlSessionFactory.openSession(); openSession.insert("test.insertUser", user); openSession.commit(); openSession.close(); } @Override public void deleteUserById(Integer id) { SqlSession openSession=sqlSessionFactory.openSession(); openSession.insert("test.deleteUserById", id); openSession.commit(); openSession.close(); } }UserDaoTest
package reed.test; import java.io.InputStream; import java.util.Date; import java.util.List; 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 reed.dao.UserDao; import reed.dao.UserDaoImpl; import reed.pojo.User; public class UserDaoTest { private SqlSessionFactory factory; //作用:在测试方法前执行这个方法 @Before public void setUp() throws Exception{ String resource = "SqlMapConfig.xml"; //通过流将核心配置文件读取进来 InputStream inputStream = Resources.getResourceAsStream(resource); //通过核心配置文件输入流来创建会话工厂 factory = new SqlSessionFactoryBuilder().build(inputStream); } @Test public void testFindUserById() throws Exception{ //将初始化好的工厂注入到实现类中 UserDao userDao = new UserDaoImpl(factory); User user = userDao.findUserById(1); System.out.println(user); } @Test public void testFindUserByUserName() throws Exception{ UserDao userDao = new UserDaoImpl(factory); List<User>list=userDao.findUserByUserName("张"); System.out.println(list.size()); } @Test public void testUpdate(){ UserDao userDao = new UserDaoImpl(factory); User user=new User(); user.setUsername("李四"); user.setAddress("安徽安庆"); user.setBirthday(new Date()); user.setSex("1"); userDao.Insert(user); } @Test public void testdelete(){ UserDao userDao = new UserDaoImpl(factory); userDao.deleteUserById(3); } }--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Mapper接口开发需要遵循以下规范：
1、 Mapper.xml文件中的namespace与mapper接口的类路径相同。
2、 Mapper接口方法名和Mapper.xml中定义的每个statement的id相同
3、 Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql的parameterType的类型相同
4、 Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同
修改SqlMapConfig.xml文件：
<mappers> <mapper resource="reed/mapper/UserMapper.xml"/> </mappers>
实现下查询功能
新建reed.mapper
UserMapper
public interface UserMapper { public User findUserById(int id); public List<User> findUserByUsername(String userName); } 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="reed.mapper.UserMapper"> <!-- 根据id获取用户信息 --> <select id="findUserById" parameterType="int" resultType="reed.pojo.User"> select * from user where id = #{id} </select> <!-- 自定义条件查询用户列表 --> <select id="findUserByUsername" parameterType="java.lang.String" resultType="reed.pojo.User"> select * from user where username like '%${value}%' </select> </mapper> UserMapperTest.java package reed.test; 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 org.junit.Before; import org.junit.Test; import reed.mapper.UserMapper; import reed.pojo.User; public class UserMapperTest { private SqlSessionFactory factory; //作用:在测试方法前执行这个方法 @Before public void setUp() throws Exception{ String resource = "SqlMapConfig.xml"; //通过流将核心配置文件读取进来 InputStream inputStream = Resources.getResourceAsStream(resource); //通过核心配置文件输入流来创建会话工厂 factory = new SqlSessionFactoryBuilder().build(inputStream); } @Test public void testFindUserById() throws Exception{ SqlSession openSession = factory.openSession(); //通过getMapper方法来实例化接口 UserMapper mapper = openSession.getMapper(UserMapper.class); User user = mapper.findUserById(1); System.out.println(user); } @Test public void findUserByUsername()throws Exception{ SqlSession openSession = factory.openSession(); //通过getMapper方法来实例化接口 UserMapper mapper = openSession.getMapper(UserMapper.class); List<User> userList=mapper.findUserByUsername("张"); System.out.println(userList); } }--------------------------------------------------------------------------------------------------------------------------------
开发中通过pojo传递查询条件 ，查询条件是综合的查询条件，不仅包括用户查询条件还包括其它的查询条件
QueryVo.java package reed.pojo; public class QueryVo { private User user; public User getUser() { return user; } public void setUser(User user) { this.user = user; } } UserMapper.xml <select id="findUserbyVo" parameterType="reed.pojo.QueryVo" resultType="reed.pojo.User"> SELECT * FROM user where username like '%${user.username}%' </select> UserMapper.javapublic List<User> findUserbyVo(QueryVo queryvo);
UserMapperTest.java
@Test public void testFindUserByVo() throws Exception{ SqlSession openSession = factory.openSession(); //通过getMapper方法来实例化接口 UserMapper mapper = openSession.getMapper(UserMapper.class); QueryVo vo = new QueryVo(); User user = new User(); user.setUsername("张"); user.setSex("1"); vo.setUser(user); //只有1条记录也可以用List List<User> list = mapper.findUserbyVo(vo); System.out.println(list); } ----------------------------------------------------------------------------------------------------------------------------------------------------------动态sql语句的拼接（if，where）
<!-- 封装sql条件,封装后可以重用. id:是这个sql条件的唯一标识 --> <sql id="user_Where"> <!-- where标签作用: 会自动向sql语句中添加where关键字 会去掉第一个条件的and关键字 --> <where> <if test="username != null and username != ''"> and username like '%${username}%' </if> <if test="sex != null and sex != ''"> and sex=#{sex} </if> </where> </sql> <!-- 调用sql条件 --> <include refid="user_Where"></include> </select> ---public List<User> findUserByUserNameAndSex(User user);
--
@Test public void testFindUserByUserNameAndSex() throws Exception{ SqlSession openSession = factory.openSession(); //通过getMapper方法来实例化接口 UserMapper mapper = openSession.getMapper(UserMapper.class); User user=new User(); user.setUsername("张"); user.setSex("1"); List<User> list = mapper.findUserByUserNameAndSex(user); System.out.println(list); } -----------------------------------------------------------------------------------------------------------------------------------------动态sql语句的拼接（foreach）
<select id="findUserByIds" parameterType="reed.pojo.QueryVo" resultType="reed.pojo.User"> select * from user <where> <if test="ids != null"> <!-- foreach:循环传入的集合参数 collection:传入的集合的变量名称 item:每次循环将循环出的数据放入这个变量中 open:循环开始拼接的字符串 close:循环结束拼接的字符串 separator:循环中拼接的分隔符 --> <foreach collection="ids" item="id" open="id in (" close=")" separator=","> #{id} </foreach> </if> </where> </select> public List<User>findUserByIds(QueryVo vo); @Test public void testFindUserByIds() throws Exception{ SqlSession openSession = factory.openSession(); //通过getMapper方法来实例化接口 UserMapper mapper = openSession.getMapper(UserMapper.class); QueryVo vo=new QueryVo(); List<Integer> ids = new ArrayList<Integer>(); ids.add(1); ids.add(4); vo.setIds(ids); List<User> list =mapper.findUserByIds(vo); System.out.println(list); } ------------------------------------------------------------------------------------------------------------------------------------------一对多查询
查询所有用户信息及用户关联的订单信息。
法一：一对一自动映射，新增一个CustomOrders类
package reed.pojo; import java.util.Date; public class CustomOrders extends Orders{ private int uid; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 public int getUid() { return uid; } public void setUid(int uid) { this.uid = uid; } 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; } }----
<select id="findOrdersAndUser1" resultType="reed.pojo.CustomOrders"> select a.*, b.id uid, username, birthday, sex, address from orders a, user b where a.user_id = b.id </select> --- @Test public void testFindOrdersAndUser() throws Exception{ SqlSession openSession = factory.openSession(); //通过getMapper方法来实例化接口 UserMapper mapper = openSession.getMapper(UserMapper.class); List<CustomOrders> list = mapper.findOrdersAndUser1(); System.out.println(list); }法二：手动映射 <!-- id:resultMap的唯一标识 type:将查询出的数据放入这个指定的对象中 注意:手动映射需要指定数据库中表的字段名与java中pojo类的属性名称的对应关系 --> <select id="findOrdersAndUser2" resultMap="orderAndUserResultMap"> select a.*, b.id uid, username, birthday, sex, address from orders a, user b where a.user_id = b.id </select> <resultMap type="reed.pojo.Orders" id="orderAndUserResultMap"> <!-- id标签指定主键字段对应关系 column:列,数据库中的字段名称 property:属性,java中pojo中的属性名称 --> <id column="id" property="id"/> <!-- result:标签指定非主键字段的对应关系 --> <result column="user_id" property="userId"/> <result column="number" property="number"/> <result column="createtime" property="createtime"/> <result column="note" property="note"/> <!-- 这个标签指定单个对象的对应关系 property:指定将数据放入Orders中的user属性中 javaType:user属性的类型 --> <association property="user" javaType="reed.pojo.User"> <id column="uid" property="id"/> <result column="username" property="username"/> <result column="birthday" property="birthday"/> <result column="sex" property="sex"/> <result column="address" property="address"/> </association> </resultMap> --public List<Orders> findOrdersAndUser2();
--
@Test public void testFindOrdersAnduUser2() throws Exception{ SqlSession openSession = factory.openSession(); //通过getMapper方法来实例化接口 UserMapper mapper = openSession.getMapper(UserMapper.class); List<Orders> list = mapper.findOrdersAndUser2(); System.out.println(list); } ---------------------------------------------------------------------------------------------------------------------------------------------------
