mybatis入门_牛客博客

增删改查

新建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动态代理方式

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文件：

实现下查询功能

新建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包装对象

开发中通过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.java

public 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);
}

---------------------------------------------------------------------------------------------------------------------------------------------------