Mybatis连接数据库常用语句整理汇总（1）

这期博客我将会重点回顾Mybatis常用的语法，包括基本的增删查改语句的书写，当然查询SQL我们会重点突出，包含的知识点也会比较多。

我们首先进行配置文件，新建db.properties，该文件主要用于数据库连接，其代码如下：

#MYSQL config
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://127.0.0.1:3306/db_mybatis?characterEncoding=utf-8
jdbc.username = root
jdbc.password = 123456

然后我们新建sqlMapConfig.xml文件，该文件主要用于①引入db.properties文件；②别名的映射；③应用POOLED方式连接数据源；④加载包含SQL语句的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>
	<!-- 引入db.properties -->
	<properties resource="config/db.properties"></properties>
	<!-- 别名映射 -->
	<typeAliases>
		<!-- 这里做了别名映射在xml文件中,类型写User,会直接使用指定的类 -->
		<!-- <typeAlias type="cn.neu.mybatis.entity.User" alias="User"/> -->
		<!-- 包扫描 会将类名定义为别名 -->
		<package name="cn.neu.mybatis.entity"/>
	</typeAliases>
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <!-- 常用类型:UNPOOLED 发出请求,才会打开或关闭连接,一些不需要及时响应的内容,可以使用  -->
      <!-- POOLED WEB应用最常用方式,不是每次请求打开再关闭,速度快 -->
      <!-- JNDI 类似于tomcat中JNDI操作 -->
      <dataSource type="POOLED">
        <property name="driver" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
      </dataSource>
    </environment>
  </environments>
  <!-- 加载包含需要使用的SQL语句的xml文件 -->
  <mappers>
  	<!-- 通过包扫描引入所有xml文件 -->
    <package name="cn.neu.mybatis.mapper"/>
  </mappers>
</configuration>

然后我们引入log4j,mybatis以及mysql的jar包，这里我们为方便查看日志信息，新建log4j.properties，其代码如下：

# Global logging configuration
#\u751F\u4EA7\u73AF\u5883\u914D\u7F6Einfo   ERROR
log4j.rootLogger=DEBUG,stdout
# MyBatis logging configuration...
log4j.logger.org.mybatis.example.BlogMapper=TRACE
# 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，其代码如下：

package cn.neu.mybatis.entity;

import java.util.Date;

public class User {
	
	private int uId;
	private String uName;
	private String uPwd;
	private String uPhone;
	private double uBalance;
	private int uState;
	private int uRole;
	private String uImage;//用户头像
	private Date uBirth;
	
	public int getuId() {
		return uId;
	}
	public void setuId(int uId) {
		this.uId = uId;
	}
	public String getuName() {
		return uName;
	}
	public void setuName(String uName) {
		this.uName = uName;
	}
	public String getuPwd() {
		return uPwd;
	}
	public void setuPwd(String uPwd) {
		this.uPwd = uPwd;
	}
	public String getuPhone() {
		return uPhone;
	}
	public void setuPhone(String uPhone) {
		this.uPhone = uPhone;
	}
	public double getuBalance() {
		return uBalance;
	}
	public void setuBalance(double uBalance) {
		this.uBalance = uBalance;
	}
	public int getuState() {
		return uState;
	}
	public void setuState(int uState) {
		this.uState = uState;
	}
	public int getuRole() {
		return uRole;
	}
	public void setuRole(int uRole) {
		this.uRole = uRole;
	}
	
	public String getuImage() {
		return uImage;
	}
	public void setuImage(String uImage) {
		this.uImage = uImage;
	}
	
	public Date getuBirth() {
		return uBirth;
	}
	public void setuBirth(Date uBirth) {
		this.uBirth = uBirth;
	}
	
	public User(int uId, String uName, String uPwd, String uPhone, double uBalance, int uState, int uRole,String uImage,Date uBirth) {
		super();
		this.uId = uId;
		this.uName = uName;
		this.uPwd = uPwd;
		this.uPhone = uPhone;
		this.uBalance = uBalance;
		this.uState = uState;
		this.uRole = uRole;
		this.uImage = uImage;
		this.uBirth = uBirth;
	}
	public User() {
		super();
	}
	public User(String uName, String uPwd, String uPhone) {
		super();
		this.uName = uName;
		this.uPwd = uPwd;
		this.uPhone = uPhone;
	}
	
	//添加注册信息
	public User(String uName, String uPwd, String uPhone, Date uBirth) {
		super();
		this.uName = uName;
		this.uPwd = uPwd;
		this.uPhone = uPhone;
		this.uBirth = uBirth;
	}
	
	public User(String uName, String uPwd, String uPhone, String uImage) {
		super();
		this.uName = uName;
		this.uPwd = uPwd;
		this.uPhone = uPhone;
		this.uImage = uImage;
	}
	
	public User(String uName, String uPwd) {
		super();
		this.uName = uName;
		this.uPwd = uPwd;
	}
	@Override
	public String toString() {
		return "User [uId=" + uId + ", uName=" + uName + ", uPwd=" + uPwd + ", uPhone=" + uPhone + ", uBalance="
				+ uBalance + ", uState=" + uState + ", uRole=" + uRole + ", uImage=" + uImage + ", uBirth=" + uBirth
				+ "]";
	}

}

然后我们新建UserMapper接口，里面主要包含未实现的方法，其代码如下：

package cn.neu.mybatis.mapper;

import java.util.List;
import java.util.Map;

import cn.neu.mybatis.entity.User;

public interface UserMapper {
	
	public User findUserByUid(int uId) throws Exception;
	
	public User findUserByUid2(int uId) throws Exception;
	
	public int deleteUserByUid(int uId) throws Exception;
	
	public int addUser(User user) throws Exception;
	
	public int updateUser(User user) throws Exception;
	
	//根据用户编号查询用户信息,根据用户类别查询用户信息,根据用户状态
	public List<User> findUsersByConditions(User user) throws Exception;
	//返回值为Map的查询方法
	public List<Map<String,Object>> findUserMap(User user) throws Exception;
	//查询foreach1
	public List<User> foreachTest1(Object[] uIds) throws Exception;
	//查询foreach2
	public List<User> foreachTest2(List<Integer> uIds) throws Exception;
	
	public int updateUser1(User user) throws Exception;
}

然后我们在相同的mapper包下新建UserMapper.xml文件，该文件主要包含拼接的SQL语句，其代码如下：

<?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">
<!-- 命名空间:相当于唯一标识符,从而可以区分不同的xml文件 -->
<!-- 采用列别名进行映射,如果属性名与数据库命名不一致时 -->
<!-- #{value}表示一个简单类型,例如int,String等 -->
<mapper namespace="cn.neu.mybatis.mapper.UserMapper">
	<select id="findUserByUid" parameterType="int"
		resultType="User">
		SELECT U_ID uId,U_NAME uName,U_PWD uPwd,U_PHONE
		uPhone,U_BALANCE uBalance,U_STATE uState,U_ROLE uRole,U_IMAGE
		uImage,U_BIRTH uBirth FROM tb_user WHERE U_ID = #{value}
	</select>
	<!-- 根据用户名模糊查询 #{}:标识一个占位符  ${}:可以进行字符串拼接,例如模糊查询 SQL语句需要拼接,务必使用 ${}， 不用拼接,直接获取值使用 
		#{}. -->
	<select id="findUsersByUname" parameterType="String"
		resultType="User">
		SELECT U_ID uId,U_NAME uName,U_PWD uPwd,U_PHONE
		uPhone,U_BALANCE uBalance,U_STATE uState,U_ROLE uRole,U_IMAGE
		uImage,U_BIRTH uBirth FROM tb_user WHERE U_NAME like '%${value}%'
	</select>
	
	<!-- 添加用户信息 -->
	<insert id="addUser" parameterType="User">
		insert into tb_user(U_NAME,U_PWD,U_PHONE) values(#{uName},#{uPwd},#{uPhone})
	</insert>
	
	<!-- 删除方法 -->
	<delete id="deleteUserByUid" parameterType="int">
		DELETE FROM tb_user
		WHERE U_ID = #{uId}
	</delete>
	<!-- 修改方法 -->
	<update id="updateUser"
		parameterType="User">
		UPDATE tb_user SET
		U_NAME=#{uName},U_PHONE=#{uPhone},U_IMAGE=#{uImage},U_PWD=#{uPwd}
		where U_ID = #{uId}
	</update>

	<!-- sql标签进行标签定义 -->
	<sql id="selectAllColumns">
		SELECT U_ID uId,U_NAME uName,U_PWD uPwd,U_PHONE
		uPhone,U_BALANCE uBalance,U_STATE uState,U_ROLE uRole,U_IMAGE
		uImage,U_BIRTH uBirth FROM tb_user
	</sql>

	<select id="findUserByUid2" parameterType="int"
		resultType="User">
		<include refid="selectAllColumns"></include>
		WHERE U_ID = #{value}
	</select>
	<!-- 使用where标签结合if标签实现一个语句多个功能 -->
	<sql id="query_user_where">
		<if test="uId!=0 and uId!='' and uId!=null">
			AND U_ID = #{uId}
		</if>
		<if test="uName!='' and uName!=null">
			AND U_NAME = #{uName}
		</if>
		<if test="uPwd!='' and uPwd!=null">
			AND U_PWD = #{uPwd}
		</if>
	</sql>

	<select id="findUsersByConditions" parameterType="User"
		resultType="User">
		<include refid="selectAllColumns"></include>
		<!-- where标签可以控制where标签中的内容,如果if判断语句中返回false,不会添加对应语句,返回true会添加对应语句 -->
		<!-- where标签会判断第一个语句是否有and 如果有会自动删除 -->
		<where>
			<include refid="query_user_where"></include>
		</where>
	</select>

	<!-- 使用Map作为返回值类型 -->
	<select id="findUserMap" parameterType="User"
		resultType="java.util.Map">
		<include refid="selectAllColumns"></include>
		<where>
			<include refid="query_user_where"></include>
		</where>
	</select>

	<!-- foreach使用 -->
	<select id="foreachTest1" resultType="User">
		<include refid="selectAllColumns"></include>
		<if test="array!=null and array.length>=0">
			where U_ID in
			<!-- collection需要遍历的内容,item遍历每一个选项,separator是分隔符,#{i}得到数组每一个值 -->
			<foreach collection="array" item="i" open="(" separator=","
				close=")">
				#{i}
			</foreach>
		</if>
	</select>

	<select id="foreachTest2" resultType="User">
		<include refid="selectAllColumns"></include>
		<if test="list!=null and list.size()>=0">
			where U_ID in
			<!-- collection需要遍历的内容,item遍历每一个选项,separator是分隔符,#{i}得到数组每一个值 -->
			<foreach collection="list" item="i" open="(" separator=","
				close=")">
				#{i}
			</foreach>
		</if>
	</select>

	<update id="updateUser1" parameterType="User">
		<if test="uName!=null or uPwd!=null or uPhone!=null">
			update tb_user
			<!-- set标签会自动去掉最后一个逗号完成拼接 -->
			<set>
				<if test="uName!=null and uName!=''">
					U_NAME = #{uName},
				</if>
				<if test="uPwd!=null and uPwd!=''">
					U_PWD = #{uPwd},
				</if>
				<if test="uPhone!=null and uPhone!=''">
					U_PHONE = #{uPhone},
				</if>
			</set>
			where U_ID = #{uId}
		</if>
	</update>
</mapper>  

然后我们进行单元测试，对每一个方法都要进行测试，新建的测试类名为UserMapperTest，其代码如下：

package cn.neu.mybatis.test;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

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 cn.neu.mybatis.entity.User;
import cn.neu.mybatis.mapper.UserMapper;

public class UserMapperTest {
	
	private SqlSessionFactory sqlSessionFactory;
	
	@Before
	public void init() throws Exception {
		InputStream inputStream = Resources.getResourceAsStream("config/sqlMapConfig.xml");
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}

	@Test
	public void testFindUserByUid() throws Exception {
		//Spring可以使用自动装载,协助实例化UserMapper,直接调用即可
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		User user = userMapper.findUserByUid(2);
		System.out.println(user.toString());
		session.close();
	}
	
	@Test
	public void testDeleteUserByUid() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		int i = userMapper.deleteUserByUid(17);
		System.out.println("删除了"+i+"条记录");
		session.commit();
		session.close();
	}
	
	@Test
	public void testUpdateUser() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		User user = userMapper.findUserByUid(7);
		user.setuName("你好");
		user.setuPwd("1234");
		user.setuPhone("23156587691");
		int i = userMapper.updateUser(user);
		System.out.println("修改了"+i+"条记录");
		session.commit();
		session.close();
	}
	
	@Test
	public void testaddUser() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		User user = new User("ttd","1234","13825436380");
		int i = userMapper.addUser(user);
		System.out.println("添加了"+i+"条记录");
		session.commit();
		session.close();
	}
	
	@Test
	public void testfindUsersByConditions() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		User user = new User();
		user.setuId(1);
		List<User> users = userMapper.findUsersByConditions(user);
		for(User u : users) {
			System.out.println(u.toString());
		}
		session.close();
	}
	
	@Test
	public void testfindUserMap() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		User user = new User();
		user.setuId(1);
		List<Map<String,Object>> users = userMapper.findUserMap(user);
		for(Map<String,Object> m : users) {
			System.out.println(m.get("uId"));
			System.out.println(m.get("uName"));//键的名字对应实体类的属性名
		}
		session.close();
	}
	
	@Test
	public void foreachTest1() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		Object[] uIds = {1,2,6,7};
		List<User> users = userMapper.foreachTest1(uIds);
		for(User u : users) {
			System.out.println(u.toString());
		}
		session.close();
	}
	
	@Test
	public void foreachTest2() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		List<Integer> uIds = new ArrayList<>();
		uIds.add(1);
		uIds.add(2);
		uIds.add(7);
		List<User> users = userMapper.foreachTest2(uIds);
		for(User u : users) {
			System.out.println(u.toString());
		}
		session.close();
	}
	
	@Test
	public void testupdateUser1() throws Exception {
		SqlSession session = sqlSessionFactory.openSession();
		UserMapper userMapper = session.getMapper(UserMapper.class);
		User user = new User();
		user.setuId(2);
		user.setuName("cpx");
		int i = userMapper.updateUser1(user);
		System.out.println("修改了"+i+"条记录");
		session.commit();
		session.close();
	}
	
}

该测试类中首先写了init方法用作初始化，加载了sqlMapConfig.xml文件，我们对里面的方法进行解释：

①通过uid查询用户信息，这里实际上在xml文件中我们写了两种SQL语句，一种是直接写，另一种是把查询内容封装一下，写在sql标签内，然后再调用；

②修改用户信息，我们在xml文件中也是写了两种方法，一种是直接写出需要修改的内容，另一种是if标签语句进行判断，把可能修改的参数都放进去；

③通过条件进行查询用户信息，方法是findUsersByConditions，里面我们整合了where标签，它会判断第一个语句是否有and，如果有会自动删除；

④遍历查询我们写了两种方式用于对比，一种是数组，一种是列表，里面都是使用foreach标签，差别其实不算大，传入的参数是用户编号，一个是Object数组，一个是List列表。

好了，本期博客就到这里了，下期我们将继续整理一对一，一对多以及多对多Mybatis语句的写法，下期再见！