这期博客我将会重点回顾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语句的写法,下期再见!