简介
-
原始jdbc操作
原始jdbc开发存在的问题
-
数据库连接创建,释放频繁造成系统资源浪费从而影响系统性能
-
sql语句在代码中硬编码,造成代码不易维护,实际应用sql变化的可能较大,sql变动需要改变java代码
-
查询操作时,需要手动将结果集中的数据手动封装到实体中,插入操作时,需要手动将实体的数据设置到sql语句的占位符位置
解决方案(mybatis)
-
使用数据库连接池初始化连接资源
-
将sql语句抽取到xml配置文件中
-
使用反射、内省等底层技术,自动将实体与表进行属性与字段的自动映射
Mybatis
-
基于Java的持久层框架
-
内部封装类jdbc
-
解耦合,开发者本身只需要关注sql语句的编写
-
通过xml或注解方式将需要执行的statement配置起来
-
通过java对象和statement中的sql动态映射生成最终的sql
开发步骤
-
添加依赖
-
创建user表
-
编写User实体类
-
编写映射文件UserMapper.xml
-
编写核心文件SqlMapConfig.xml
-
编写测试类
package com.cyz.domain;
import javax.management.relation.Role;
import java.util.List;
public class User {
private Long id;
private String username;
private String email;
private String password;
private String phoneNum;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getPhoneNum() {
return phoneNum;
}
public void setPhoneNum(String phoneNum) {
this.phoneNum = phoneNum;
}
-
userMapper.xml
-
sqlMapConfig.xml
-
log4j.properties
#console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
#file
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=e:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
log4j.rootLogger=debug,stdout
-
测试代码
package com.cyz.test;
import com.cyz.domain.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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatisTest {
映射文件
增删改查
-
查询和插入
-
注意食物默认不提交
-
package com.cyz.test;
import com.cyz.domain.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.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatisTest {
-
修改
<!-- 修改操作--> <update id="update" parameterType="com.cyz.domain.User"> update sys_user set username = #{username}, email = #{email}, password=#{password}, phoneNum=#{phoneNum} where id = #{id} </update>
@Test public void test3() throws IOException { // 模拟User User user = new User(); user.setId(8L); user.setUsername("eeeee"); user.setEmail("163e@qq.com"); user.setPassword("123123e"); user.setPhoneNum("178965565ee"); // 获取核心配置文件 InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); // 获取session工厂对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); // 获取session会话对象 SqlSession sqlSession = sqlSessionFactory.openSession(); // 执行操作 参数:namespace+id,实体对象 sqlSession.update("userMapper.update",user); // 默认事务不提交 sqlSession.commit(); // 释放资源 sqlSession.close(); }
-
删除
<!-- 删除操作--> <delete id="delete" parameterType="java.lang.Long"> delete from sys_user where id = #{id} </delete>
@Test public void test4() throws IOException { // 获取核心配置文件 InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); // 获取session工厂对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); // 获取session会话对象 SqlSession sqlSession = sqlSessionFactory.openSession(); // 执行操作 参数:namespace+id,实体对象 sqlSession.delete("userMapper.delete",8L); // 默认事务不提交 sqlSession.commit(); // 释放资源 sqlSession.close(); }
核心配置文件
<?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> <!--加载外部properties--> <properties resource="jdbc.properties"/> <!-- 别名配置 注意配置位置是有顺序的--> <typeAliases> <typeAlias type="com.cyz.domain.User" alias="user"/> </typeAliases> <!-- 数据源环境--> <!-- 指定默认环境--> <environments default="development"> <!-- 当前环境 --> <environment id="development"> <!-- 当前事务管理器类型 两种 JDBC:这个配置就是直接使用了JDBC的提交和回滚设置,它依赖于从数据源得到的连接来管理事务作用域 MANAGED:这个配置几乎没做什么 它从来不提交和回滚一个连接,而是让容器来管理事务的整个生命周期 (比如:JEE应用服务器的上下文),默认情况下它会关闭连接,然而一些容器并不希望这样,因此需要 将closeConnection属性设置为false来阻止它默认的关闭行为 --> <transactionManager type="JDBC"/> <!-- 当前数据源类型 三种 UNPOOLED:这个数据源的实现只是每次被请求时打开和关闭 POOLED:这种数据源的实现利用“池”的概念将JDBC连接对象组织起来 JNDI:这个数据源的实现是为了能在如EJB或应用服务器这类容器中使用,容器可以 集中或在外部配置数据源,让后放置一个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> <!-- 加载映射文件 使用相对于类路径的资源引用:<mapper resource="com/cyz/mapper/UserMapper.xml"/> 使用完全限定资源定位符(URL):<mapper url="file:///var/mappers/UserMapper.xml"/> 使用映射器接口实现类的完全限定类名 <mapper class="com.cyz.mapper.UserMapper"/> 将包内的映射器接口实现全部注册为映射器:<package name="com.cyz.mapper"/> --> <mappers> <mapper resource="com/cyz/mapper/UserMapper.xml"/> </mappers> </configuration>
<?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="userMapper"> <!-- 删除操作--> <delete id="delete" parameterType="long"> delete from sys_user where id = #{id} </delete> <!-- 修改操作--> <update id="update" parameterType="user"> update sys_user set username = #{username}, email = #{email}, password=#{password}, phoneNum=#{phoneNum} where id = #{id} </update> <!--插入操作--> <insert id="save" parameterType="user"> insert into sys_user values (#{id}, #{username}, #{email}, #{password}, #{phoneNum}) </insert> <!-- 查询操作--> <select id="findAll" resultType="user"> select * from sys_user </select> </mapper>
相应API
-
SqlSessionFactory build(InputStream inputStream) // 获取核心配置文件 InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); // 获取session工厂对象 SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); Resources:工具类,帮助我们获取从类路径下,文件系统或一个web URL中加载资源文件
Mybatis的Dao层实现
传统开发模式
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>mybatis_dao</artifactId> <version>1.0-SNAPSHOT</version> <properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.5</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13</version> <scope>test</scope> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> </dependencies> </project>
package com.cyz.domain; public class User { private Long id; private String username; private String email; private String password; private String phoneNum; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getPhoneNum() { return phoneNum; } public void setPhoneNum(String phoneNum) { this.phoneNum = phoneNum; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", email='" + email + '\'' + ", password='" + password + '\'' + ", phoneNum='" + phoneNum + '\'' + '}'; } }
#console log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.Target=System.out log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n #file log4j.appender.file=org.apache.log4j.FileAppender log4j.appender.file.File=e:/mylog.log log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n log4j.rootLogger=info,stdout
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/jdbc_test jdbc.username=root jdbc.password=123456
<?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> <properties resource="jdbc.properties"/> <typeAliases> <typeAlias type="com.cyz.domain.User" alias="user"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <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> <mappers> <mapper resource="com/cyz/mapper/UserMapper.xml"/> </mappers> </configuration>
<?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="userMapper"> <select id="findAll" resultType="user"> select * from sys_user </select> </mapper>
package com.cyz.dao; import com.cyz.domain.User; import java.io.IOException; import java.util.List; public interface UserMapper { public List<User> findAll() throws IOException; }
package com.cyz.dao.impl; import com.cyz.dao.UserMapper; import com.cyz.domain.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 java.io.IOException; import java.io.InputStream; import java.util.List; public class UserMapperImpl implements UserMapper { @Override public List<User> findAll() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); List<User> userList = sqlSession.selectList("userMapper.findAll"); return userList; } }
-
模拟service层
package com.cyz.service; import com.cyz.dao.UserMapper; import com.cyz.dao.impl.UserMapperImpl; import com.cyz.domain.User; import java.io.IOException; import java.util.List; public class UserDemo { public static void main(String[] args) throws IOException { // 创建dao层对象 当前dao层实现手动编写的 UserMapper userMapper = new UserMapperImpl(); List<User> all = userMapper.findAll(); System.out.println(all); } }
接口代理方式
-
采用Mybatis的代理开发方式实现DAO层的开发
-
开发者只需要编写Mapper接口(相当于Dao接口),由Mybatis框架根据接口自定义创建接口的动态代理对象,代理对象的方法同上边Dao接口实现类方法
Mapper接口开发需要遵循以下规范:
-
Mapper.xml文件的namespace与mapper接口的全限定名相同
-
Mapper接口方法名和Mapper.xml中定义的每一个statement的id相同
-
Mapper接口方法的输入参数类型和mapper.xml中定义的每个sql的parameterType的类型相同
-
Mapper接口方法的输出参数类型和mapper.xml中定义的每个sql的resultType的类型相同
package com.cyz.dao; import com.cyz.domain.User; import java.util.List; public interface UserMapper { public List<User> findAll(); public User findById(Long 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.cyz.dao.UserMapper"> <select id="findAll" resultType="user"> select * from sys_user </select> <!-- 根据ID进行查询--> <select id="findById" parameterType="long" resultType="user"> select * from sys_user where id = #{id} </select> </mapper>
package com.cyz.service; import com.cyz.dao.UserMapper; import com.cyz.domain.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 java.io.IOException; import java.io.InputStream; import java.util.List; public class UserDemo { public static void main(String[] args) throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> all = mapper.findAll(); System.out.println(all); User user = mapper.findById(1L); System.out.println(user); sqlSession.close(); } }
映射文件的深入
动态sql
-
复杂业务中的sql是动态变化的
where/if
package com.cyz.mapper; import com.cyz.domain.User; import java.util.List; public interface UserMapper { public List<User> findByCondition(User user); }
<?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.cyz.mapper.UserMapper"> <select id="findByCondition" parameterType="user" resultType="user"> select * from sys_user <where> <if test="id != null"> and id=#{id} </if> <if test="username != null and username != ''"> and username=#{username} </if> <if test="password != null and password != ''"> and password=#{password} </if> </where> </select> </mapper>
package com.cyz.test; import com.cyz.domain.User; import com.cyz.mapper.UserMapper; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MapperTest { @Test public void test() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 模拟条件 User condition = new User(); // condition.setId(1L); // condition.setUsername("张三"); // condition.setPassword(""); // condition.setPassword("123"); List<User> userList = mapper.findByCondition(condition); System.out.println(userList); sqlSession.close(); } }
foreach
package com.cyz.mapper; import com.cyz.domain.User; import java.util.List; public interface UserMapper { public List<User> findByCondition(User user); public List<User> findByIds(List<Long> ids); }
<select id="findByIds" parameterType="list" resultType="user"> select * from sys_user <where> <!-- <foreach collection="array" item="id" separator="," open="id in(" close=")">--> <foreach collection="list" item="id" separator="," open="id in(" close=")"> #{id} </foreach> </where> </select>
@Test public void test2() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<Long> ids = new ArrayList<>(); ids.add(1L); ids.add(2L); List<User> userList = mapper.findByIds(ids); System.out.println(userList); sqlSession.close(); }
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"> <mapper namespace="com.cyz.mapper.UserMapper"> <!-- sql语句抽取--> <sql id="selectUser"> select * from sys_user </sql> <select id="findByCondition" parameterType="user" resultType="user"> <include refid="selectUser"/> <where> <if test="id != null"> and id=#{id} </if> <if test="username != null and username != ''"> and username=#{username} </if> <if test="password != null and password != ''"> and password=#{password} </if> </where> </select> <select id="findByIds" parameterType="list" resultType="user"> <include refid="selectUser"/> <where> <!-- <foreach collection="array" item="id" separator="," open="id in(" close=")">--> <foreach collection="list" item="id" separator="," open="id in(" close=")"> #{id} </foreach> </where> </select> </mapper>
配置文件深入
typeHanders
-
你可以重写类型处理器或创建自己的类型处理器来处理不支持的或非标准的类型,具体做法:实现
org.apache.ibatis.type.TypeHandler
接口,或继承一个很便利的类org.apache.ibatis.type.BaseTypeHandler
,然后可以选择的将他映射到一个JDBC类型,例如需求:一个Java中的Date数据类型,我想将之存到数据库的时候存成一个1970年至今的毫秒数,取出来时转换成java的Date,即java的Date与数据库的varchar毫秒值之间转换 -
开发步骤
-
定义转换类继承类BaseTypeHandler<T>
-
覆盖4个未实现的方法,其中
setNonNullParameter
为java程序设置数据到数据库的回调方法,getNullableResult
为查询时mysql的字符串类型转换成java的Type类型的方法 -
在Mybatis核心配置文件中进行注册
-
测试转换是否正确
-
package com.cyz.domain; import java.util.Date; public class User { private Long id; private String username; private String email; private String password; private String phoneNum; private Date birthday; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getPhoneNum() { return phoneNum; } public void setPhoneNum(String phoneNum) { this.phoneNum = phoneNum; } public Date getBirthday() { return birthday; } public void setBirthday(Date birthday) { this.birthday = birthday; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", email='" + email + '\'' + ", password='" + password + '\'' + ", phoneNum='" + phoneNum + '\'' + ", birthday=" + birthday + '}'; } }
package com.cyz.mapper; import com.cyz.domain.User; public interface UserMapper { public void save(User user); public User findById(Long id); public List<User> findAll(); }
<?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.cyz.mapper.UserMapper"> <select id="save" parameterType="user"> insert into sys_user values (#{id}, #{username}, #{email}, #{password}, #{phoneNum}, #{birthday}) </select> <select id="findById" parameterType="long" resultType="user"> select * from sys_user where id = #{id} </select> <select id="findAll" resultType="user"> select * from sys_user </select> </mapper>
package com.cyz; import com.cyz.domain.User; import com.cyz.mapper.UserMapper; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.Date; public class MyBatisTest { @Test public void test() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 模拟对象 User user = new User(); user.setUsername("asda"); user.setEmail("145@qq.com"); user.setPassword("12312"); user.setPhoneNum("12312566544"); user.setBirthday(new Date()); // 执行保存操作 mapper.save(user); sqlSession.commit(); sqlSession.close(); } }
-
实现
package com.cyz.handler; import org.apache.ibatis.type.BaseTypeHandler; import org.apache.ibatis.type.JdbcType; import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Date; public class DateTypeHandler extends BaseTypeHandler<Date> { // 将java类型 转换成 数据库需要的类型 @Override public void setNonNullParameter(PreparedStatement preparedStatement, int i, Date date, JdbcType jdbcType) throws SQLException { long time = date.getTime(); preparedStatement.setLong(i,time); } // 将数据库中类型 转换成java类型 // String参数 表的字段名称 // ResultSet 查询出的结果集 @Override public Date getNullableResult(ResultSet resultSet, String s) throws SQLException { // 获取结果集中需要的数据(long)转换为Date类型 返回 long aLong = resultSet.getLong(s); Date date = new Date(aLong); return date; } // 将数据库中类型 转换成java类型 @Override public Date getNullableResult(ResultSet resultSet, int i) throws SQLException { long aLong = resultSet.getLong(i); Date date = new Date(aLong); return date; } // 将数据库中类型 转换成java类型 @Override public Date getNullableResult(CallableStatement callableStatement, int i) throws SQLException { long aLong = callableStatement.getLong(i); Date date = new Date(aLong); return date; } }
-
注册
<?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> <properties resource="jdbc.properties"/> <typeAliases> <typeAlias type="com.cyz.domain.User" alias="user"/> </typeAliases> <!-- 注册类型处理器--> <typeHandlers> <typeHandler handler="com.cyz.handler.DateTypeHandler"/> </typeHandlers> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <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> <mappers> <mapper resource="com/cyz/mapper/UserMapper.xml"/> </mappers> </configuration>
-
再次测试
package com.cyz; import com.cyz.domain.User; import com.cyz.mapper.UserMapper; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.Date; public class MyBatisTest { @Test public void test() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 模拟对象 User user = new User(); user.setUsername("asda"); user.setEmail("145@qq.com"); user.setPassword("12312"); user.setPhoneNum("12312566544"); user.setBirthday(new Date()); // 执行保存操作 mapper.save(user); sqlSession.commit(); sqlSession.close(); } @Test public void test2() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); User user = mapper.findById(11L); System.out.println(user); sqlSession.close(); } }
plugins标签
-
扩展第三方插件
-
分页助手PageHelper
-
-
开发步骤
-
导入依赖
-
在mybatis核心配置文件中配置PageHelper插件
-
测试分页数据获取
-
<!-- pagehelper--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.1.4</version> </dependency> <!-- 5.x版本后不需要--> <!-- <dependency>--> <!-- <groupId>com.github.jsqlparser</groupId>--> <!-- <artifactId>jsqlparser</artifactId>--> <!-- <version>3.2</version>--> <!-- </dependency>--> <!-- pagehelper-->
<?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> <properties resource="jdbc.properties"/> <typeAliases> <typeAlias type="com.cyz.domain.User" alias="user"/> </typeAliases> <!-- 注册类型处理器--> <typeHandlers> <typeHandler handler="com.cyz.handler.DateTypeHandler"/> </typeHandlers> <!-- 配置分页助手插件 4.0.x--> <!-- <plugins>--> <!-- <plugin interceptor="com.github.pagehelper.PageHelper">--> <!--<!– 指定方言–>--> <!-- <property name="dialect" value="mysql"/>--> <!-- </plugin>--> <!-- </plugins>--> <!-- 配置分页助手插件 5.0.x--> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"/> </plugins> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <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> <mappers> <mapper resource="com/cyz/mapper/UserMapper.xml"/> </mappers> </configuration>
-
测试
@Test public void test3() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 设置分页相关参数 当前页 + 每页条数 超过页数显示最后一页 PageHelper.startPage(1,3); List<User> userList = mapper.findAll(); for (User user : userList) { System.out.println(user); } sqlSession.close(); }
@Test public void test3() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); // 设置分页相关参数 当前页 + 每页条数 超过页数显示最后一页 PageHelper.startPage(1,3); List<User> userList = mapper.findAll(); for (User user : userList) { System.out.println(user); } // 获得与分页相关参数 PageInfo<User> pageInfo = new PageInfo<>(userList); System.out.println("当前页:"+pageInfo.getPageNum()); System.out.println("每页显示条数:"+pageInfo.getPageSize()); System.out.println("总条数:"+pageInfo.getTotal()); System.out.println("总页数:"+pageInfo.getPages()); System.out.println("上一页:"+pageInfo.getPrePage()); System.out.println("下一页:"+pageInfo.getNextPage()); System.out.println("是否是第一页:"+pageInfo.isIsFirstPage()); System.out.println("是否是最后一页:"+pageInfo.isIsLastPage()); sqlSession.close(); }
多表操作
一对一查询
-
用户表和订单表的关系为,一个用户有多个订单,一个订单只从属于一个用户
-
一对一查询的需求:查询一个订单,与此同时查询出该订单所属的用户
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>mybatis_multi</artifactId> <version>1.0-SNAPSHOT</version> <properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.5</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13</version> <scope>test</scope> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> </dependencies> </project>
package com.cyz.domain; import java.math.BigDecimal; import java.util.Date; public class Orders { private Long id; private Date ordertime; private BigDecimal total; // 当前订单属于哪一个用户 private User user; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public Date getOrdertime() { return ordertime; } public void setOrdertime(Date ordertime) { this.ordertime = ordertime; } public BigDecimal getTotal() { return total; } public void setTotal(BigDecimal total) { this.total = total; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } @Override public String toString() { return "Orders{" + "id=" + id + ", ordertime=" + ordertime + ", total=" + total + ", user=" + user + '}'; } }
package com.cyz.domain; import java.util.Date; public class User { private Long id; private String username; private String email; private String password; private String phoneNum; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getPhoneNum() { return phoneNum; } public void setPhoneNum(String phoneNum) { this.phoneNum = phoneNum; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", email='" + email + '\'' + ", password='" + password + '\'' + ", phoneNum='" + phoneNum + '\'' + '}'; } }
<?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> <properties resource="jdbc.properties"/> <typeAliases> <typeAlias type="com.cyz.domain.User" alias="user"/> <typeAlias type="com.cyz.domain.Orders" alias="orders"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <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> <mappers> <mapper resource="com/cyz/mapper/UserMapper.xml"/> <mapper resource="com/cyz/mapper/OrderMapper.xml"/> </mappers> </configuration>
<?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.cyz.mapper.OrderMapper"> <resultMap id="orderMap" type="orders"> <!-- 手动指定字段与实体之间的关系--> <id column="oid" property="id"/> <result column="ordertime" property="ordertime"/> <result column="total" property="total"/> <result column="uid" property="user.id"/> <result column="username" property="user.username"/> <result column="email" property="user.email"/> <result column="password" property="user.password"/> <result column="phoneNum" property="user.phoneNum"/> </resultMap> <select id="findAll" resultMap="orderMap"> select *,o.id oid from orders o,sys_user u where o.uid=u.id </select> </mapper>
<?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.cyz.mapper.OrderMapper"> <resultMap id="orderMap" type="orders"> <!-- 手动指定字段与实体之间的关系 column:数据库表的字段名称 property:实体的属性名称 --> <id column="oid" property="id"/> <result column="ordertime" property="ordertime"/> <result column="total" property="total"/> <!-- <result column="uid" property="user.id"/>--> <!-- <result column="username" property="user.username"/>--> <!-- <result column="email" property="user.email"/>--> <!-- <result column="password" property="user.password"/>--> <!-- <result column="phoneNum" property="user.phoneNum"/>--> <!-- property:当前实体(order)中的属性名称(private User user) javaType:当前实体(order)中的属性的类型(User) --> <association property="user" javaType="user"> <id column="uid" property="id"/> <result column="username" property="username"/> <result column="email" property="email"/> <result column="password" property="password"/> <result column="phoneNum" property="phoneNum"/> </association> </resultMap> <select id="findAll" resultMap="orderMap"> select *, o.id oid from orders o, sys_user u where o.uid = u.id </select> </mapper>
package com.cyz; import com.cyz.domain.Orders; import com.cyz.mapper.OrderMapper; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class MybatisTest { @Test public void test() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); List<Orders> ordersList = mapper.findAll(); System.out.println(ordersList); sqlSession.close(); } }
一对多查询
-
用户表和订单表的关系为:一个用户有多个订单,一个订单只从属于一个用户
-
一对多查询需求:查询一个用户,与此同时查询出该用户具有的订单
package com.cyz.domain; import java.math.BigDecimal; import java.util.Date; public class Orders { private Long id; private Date ordertime; private BigDecimal total; // 当前订单属于哪一个用户 private User user; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public Date getOrdertime() { return ordertime; } public void setOrdertime(Date ordertime) { this.ordertime = ordertime; } public BigDecimal getTotal() { return total; } public void setTotal(BigDecimal total) { this.total = total; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } @Override public String toString() { return "Orders{" + "id=" + id + ", ordertime=" + ordertime + ", total=" + total + ", user=" + user + '}'; } }
package com.cyz.mapper; import com.cyz.domain.User; import java.util.List; public interface UserMapper { public List<User> findAll(); }
<?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.cyz.mapper.UserMapper"> <resultMap id="userMap" type="user"> <id column="uid" property="id"/> <result column="username" property="username"/> <result column="email" property="email"/> <result column="password" property="password"/> <result column="phoneNum" property="phoneNum"/> <!-- 配置集合信息--> <collection property="ordersList" ofType="orders"> <id column="oid" property="id"/> <result column="ordertime" property="ordertime"/> <result column="total" property="total"/> </collection> </resultMap> <select id="findAll" resultMap="userMap"> select *,o.id oid from sys_user u,orders o where u.id = o.uid </select> </mapper>
@Test public void test1() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userList = mapper.findAll(); System.out.println(userList); sqlSession.close(); }
多对多查询
-
用户表和角色表关系为,一个用户有多个角色,一个角色被多个用户使用
-
多对多查询的需求:查询用户同时查询出该用户的所有角色
package com.cyz.domain; public class Role { private Long id; private String roleName; private String roleDesc; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getRoleName() { return roleName; } public void setRoleName(String roleName) { this.roleName = roleName; } public String getRoleDesc() { return roleDesc; } public void setRoleDesc(String roleDesc) { this.roleDesc = roleDesc; } @Override public String toString() { return "Role{" + "id=" + id + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } }
package com.cyz.domain; import java.util.List; public class User { private Long id; private String username; private String email; private String password; private String phoneNum; // 描述的是当前用户存在哪些订单 private List<Orders> ordersList; // 用户角色列表 private List<Role> roleList; public List<Orders> getOrdersList() { return ordersList; } public void setOrdersList(List<Orders> ordersList) { this.ordersList = ordersList; } public List<Role> getRoleList() { return roleList; } public void setRoleList(List<Role> roleList) { this.roleList = roleList; } public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getPhoneNum() { return phoneNum; } public void setPhoneNum(String phoneNum) { this.phoneNum = phoneNum; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", email='" + email + '\'' + ", password='" + password + '\'' + ", phoneNum='" + phoneNum + '\'' + ", ordersList=" + ordersList + ", roleList=" + roleList + '}'; } }
package com.cyz.mapper; import com.cyz.domain.User; import java.util.List; public interface UserMapper { public List<User> findAll(); public List<User> findUserAndRoleAll(); }
<?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.cyz.mapper.UserMapper"> <resultMap id="userMap" type="user"> <id column="uid" property="id"/> <result column="username" property="username"/> <result column="email" property="email"/> <result column="password" property="password"/> <result column="phoneNum" property="phoneNum"/> <!-- 配置集合信息--> <collection property="ordersList" ofType="orders"> <id column="oid" property="id"/> <result column="ordertime" property="ordertime"/> <result column="total" property="total"/> </collection> </resultMap> <select id="findAll" resultMap="userMap"> select *, o.id oid from sys_user u, orders o where u.id = o.uid </select> <resultMap id="userRoleMap" type="user"> <id column="userId" property="id"/> <result column="username" property="username"/> <result column="email" property="email"/> <result column="password" property="password"/> <result column="phoneNum" property="phoneNum"/> <collection property="roleList" ofType="role"> <id column="roleId" property="id"/> <result column="roleName" property="roleName"/> <result column="roleDesc" property="roleDesc"/> </collection> </resultMap> <select id="findUserAndRoleAll" resultMap="userRoleMap"> select * from sys_user u, sys_user_role ur, sys_role r where u.id = ur.userId and ur.roleId = r.id </select> </mapper>
package com.cyz; import com.cyz.domain.Orders; import com.cyz.domain.User; import com.cyz.mapper.OrderMapper; import com.cyz.mapper.UserMapper; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MybatisTest { @Test public void test() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); OrderMapper mapper = sqlSession.getMapper(OrderMapper.class); List<Orders> ordersList = mapper.findAll(); System.out.println(ordersList); sqlSession.close(); } @Test public void test1() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userList = mapper.findAll(); System.out.println(userList); sqlSession.close(); } @Test public void test2() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userAndRoleAll = mapper.findUserAndRoleAll(); for (User user : userAndRoleAll) { System.out.println(user); } sqlSession.close(); } }
知识要点
-
一对一配置:使用
<resultMap>
做配置 -
一对多配置:使用
<resultMap>+<collection>
做配置 -
多对多配置:使用
<resultMap>+<collection>
做配置
注解开发
常用注解
注解 | 实现 | 说明 |
---|---|---|
@Insert | 实现新增 | |
@Update | 实现更新 | |
@Delete | 实现删除 | |
@Select | 实现查询 | |
@Result | 实现结果集封装 | 代替了<id> 标签和<result> 标签 属性: column:数据库的列名 property:需要装配的属性名 one:需要使用的@One注解(@Result(one = @One)()) many:需要使用的@Many注解(@Result(many=@Many)()) |
@Results | 可以与@Result一起使用,封装多个结果集 | 代替<resultMap> 单个或多个使用 代替<resultMap> 单个或多个使用 @Results (@Result()) @Results({@Result().@Result()}) |
@One | 实现一对一结果集封装 | 代替<assocation> 标签,是多表查询的关键,在注解中用来指定子查询返回单一对象 @One属性: sekect:指定用来多表查询的sqlmapper 使用格式:@Resut(column="",property="",one=@One(select="")) |
@Many | 实现一对多结果集封装 | 代替<collection> 标签,是多表查询的关键,在注解中用来指定子查询返回对象集合,使用格式:@Result(column="",property="",one=@Many(select="")) |
xml环境搭建
-
依赖
<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.example</groupId> <artifactId>mybatis_anon</artifactId> <version>1.0-SNAPSHOT</version> <dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.5</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.13</version> <scope>test</scope> </dependency> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> </dependencies> </project>
-
配置文件
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/jdbc_test jdbc.username=root jdbc.password=123456
#console log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.Target=System.out log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n #file log4j.appender.file=org.apache.log4j.FileAppender log4j.appender.file.File=e:/mylog.log log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n log4j.rootLogger=debug,stdout
-
实体类
package com.cyz.domain; public class User { private Long id; private String username; private String email; private String password; private String phoneNum; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getPhoneNum() { return phoneNum; } public void setPhoneNum(String phoneNum) { this.phoneNum = phoneNum; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", email='" + email + '\'' + ", password='" + password + '\'' + ", phoneNum='" + phoneNum + '\'' + '}'; } }
-
dao
package com.cyz.mapper; import com.cyz.domain.User; import java.util.List; public interface UserMapper { void save(User user); void update(User user); void delete(Long id); User findById(Long id); List<User> findAll(); }
-
mapper.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.cyz.mapper.UserMapper"> <insert id="save" parameterType="user"> insert into sys_user values(#{id},#{username},#{email},#{password},#{phoneNum},111) </insert> <update id="update" parameterType="user"> update sys_user set username = #{username},password = #{password} where id = #{id} </update> <delete id="delete" parameterType="long"> delete from sys_user where id =#{id} </delete> <select id="findById" parameterType="long" resultType="user"> select * from sys_user where id = #{id} </select> <select id="findAll" resultType="user"> select * from sys_user </select> </mapper>
-
配置文件
<?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> <properties resource="jdbc.properties"/> <typeAliases> <typeAlias type="com.cyz.domain.User" alias="user"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <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> <mappers> <mapper resource="com/cyz/mapper/UserMapper.xml"/> </mappers> </configuration>
-
测试
package com.cyz; import com.cyz.domain.User; import com.cyz.mapper.UserMapper; 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.IOException; import java.io.InputStream; import java.util.List; public class MyBatisTest { private UserMapper mapper; private SqlSession sqlSession; @Before public void before() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); sqlSession = sqlSessionFactory.openSession(true); mapper = sqlSession.getMapper(UserMapper.class); } @Test public void testSave() { User user = new User(); user.setUsername("qweqwe"); user.setEmail("145@11.com"); user.setPassword("123123"); user.setPhoneNum("17365412322"); mapper.save(user); } @Test public void testUpdate() { User user = new User(); user.setId(13L); user.setUsername("qweqwe2"); user.setPassword("363636"); mapper.update(user); } @Test public void testFindById() { User user = mapper.findById(13L); System.out.println(user); } @Test public void testFindAll() { List<User> userList = mapper.findAll(); for (User user : userList) { System.out.println(user); } } @Test public void testDelete() { mapper.delete(14L); } @After public void after() { sqlSession.close(); } }
注解方式
-
去除mapper.xml文件
-
修改dao
package com.cyz.mapper; import com.cyz.domain.User; import org.apache.ibatis.annotations.Delete; import org.apache.ibatis.annotations.Insert; import org.apache.ibatis.annotations.Select; import org.apache.ibatis.annotations.Update; import java.util.List; public interface UserMapper { @Insert("insert into sys_user values(#{id},#{username},#{email},#{password},#{phoneNum},111)") void save(User user); @Update("update sys_user set username = #{username},password = #{password} where id = #{id}") void update(User user); @Delete("delete from sys_user where id =#{id}") void delete(Long id); @Select("select * from sys_user where id = #{id}") User findById(Long id); @Select("select * from sys_user") List<User> findAll(); }
-
配置文件配置映射关系
<?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> <properties resource="jdbc.properties"/> <typeAliases> <typeAlias type="com.cyz.domain.User" alias="user"/> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <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> <!-- 加载映射关系--> <mappers> <!-- 指定接口所在的包--> <package name="com.cyz.mapper"/> </mappers> </configuration>
-
测试
package com.cyz; import com.cyz.domain.User; import com.cyz.mapper.UserMapper; 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.IOException; import java.io.InputStream; import java.util.List; public class MyBatisTest { private UserMapper mapper; private SqlSession sqlSession; @Before public void before() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); sqlSession = sqlSessionFactory.openSession(true); mapper = sqlSession.getMapper(UserMapper.class); } @Test public void testSave() { User user = new User(); user.setUsername("qweqwe"); user.setEmail("145@11.com"); user.setPassword("123123"); user.setPhoneNum("17365412322"); mapper.save(user); } @Test public void testUpdate() { User user = new User(); user.setId(15L); user.setUsername("qweqwe2"); user.setPassword("363636"); mapper.update(user); } @Test public void testFindById() { User user = mapper.findById(15L); System.out.println(user); } @Test public void testFindAll() { List<User> userList = mapper.findAll(); for (User user : userList) { System.out.println(user); } } @Test public void testDelete() { mapper.delete(16L); } @After public void after() { sqlSession.close(); } }
一多一查询
-
实体对象
package com.cyz.domain; import java.math.BigDecimal; import java.util.Date; public class Orders { private Long id; private Date ordertime; private BigDecimal total; // 当前订单属于哪一个用户 private User user; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public Date getOrdertime() { return ordertime; } public void setOrdertime(Date ordertime) { this.ordertime = ordertime; } public BigDecimal getTotal() { return total; } public void setTotal(BigDecimal total) { this.total = total; } public User getUser() { return user; } public void setUser(User user) { this.user = user; } @Override public String toString() { return "Orders{" + "id=" + id + ", ordertime=" + ordertime + ", total=" + total + ", user=" + user + '}'; } }
-
dao
package com.cyz.mapper; import com.cyz.domain.Orders; import com.cyz.domain.User; import org.apache.ibatis.annotations.One; import org.apache.ibatis.annotations.Result; import org.apache.ibatis.annotations.Results; import org.apache.ibatis.annotations.Select; import java.util.List; public interface OrderMapper { @Select("select *,o.id oid from orders o, sys_user u where o.uid = u.id") @Results({ @Result(column = "oid" , property = "id"), @Result(column = "ordertime" , property = "ordertime"), @Result(column = "total" , property = "total"), @Result(column = "uid" , property = "user.id"), @Result(column = "username" , property = "user.username"), @Result(column = "email" , property = "user.email"), @Result(column = "password" , property = "user.password"), @Result(column = "phoneNum" , property = "user.phoneNum"), }) List<Orders> findAll(); @Select("select * from orders") @Results({ @Result(column = "id" , property = "id"), @Result(column = "ordertime" , property = "ordertime"), @Result(column = "total" , property = "total"), @Result( property = "user" ,//需要封装的属性名称 column = "uid" ,//关联字段 javaType = User.class,//需要封装的实体类型 // select属性 代表查询哪个接口 的方法获取的数据 全限定名 one = @One(select = "com.cyz.mapper.UserMapper.findById") ) }) List<Orders> findAll2(); }
-
子查询接口
package com.cyz.mapper; import com.cyz.domain.User; import org.apache.ibatis.annotations.Select; import java.util.List; public interface UserMapper { @Select("select * from sys_user where id = #{id}") User findById(Long id); }
-
测试
package com.cyz; import com.cyz.domain.Orders; import com.cyz.mapper.OrderMapper; 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.IOException; import java.io.InputStream; import java.util.List; public class MyBatisTest2 { private OrderMapper mapper; private SqlSession sqlSession; @Before public void before() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); sqlSession = sqlSessionFactory.openSession(true); mapper = sqlSession.getMapper(OrderMapper.class); } @Test public void testFindAll() { List<Orders> orders = mapper.findAll(); for (Orders order : orders) { System.out.println(order); } } @Test public void testFindAll2() { List<Orders> orders = mapper.findAll2(); for (Orders order : orders) { System.out.println(order); } } @After public void after() { sqlSession.close(); } }
一对多查询
-
实体类
package com.cyz.domain; import java.util.List; public class User { private Long id; private String username; private String email; private String password; private String phoneNum; /** * 描述当前用户的订单列表 */ private List<Orders> ordersList; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getPhoneNum() { return phoneNum; } public void setPhoneNum(String phoneNum) { this.phoneNum = phoneNum; } public List<Orders> getOrdersList() { return ordersList; } public void setOrdersList(List<Orders> ordersList) { this.ordersList = ordersList; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", email='" + email + '\'' + ", password='" + password + '\'' + ", phoneNum='" + phoneNum + '\'' + ", ordersList=" + ordersList + '}'; } }
-
dao
package com.cyz.mapper; import com.cyz.domain.User; import org.apache.ibatis.annotations.*; import java.util.List; public interface UserMapper { @Select("select * from sys_user") @Results({ @Result(column = "id" , property = "id"), @Result(column = "username" , property = "username"), @Result(column = "email" , property = "email"), @Result(column = "password" , property = "password"), @Result(column = "phoneNum" , property = "phoneNum"), @Result( property = "ordersList", column = "id", javaType = List.class, many = @Many(select = "com.cyz.mapper.OrderMapper.findByUid") ) }) List<User> findUserAndOrderAll(); }
-
子查询
package com.cyz.mapper; import com.cyz.domain.Orders; import org.apache.ibatis.annotations.Select; import java.util.List; public interface OrderMapper { @Select("select * from orders where uid = #{uid}") List<Orders> findByUid(Long uid); }
-
测试
package com.cyz; import com.cyz.domain.User; import com.cyz.mapper.UserMapper; 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.IOException; import java.io.InputStream; import java.util.List; public class MyBatisTest { private UserMapper mapper; private SqlSession sqlSession; @Before public void before() throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); sqlSession = sqlSessionFactory.openSession(true); mapper = sqlSession.getMapper(UserMapper.class); } @Test public void testFindUserAndOrderAll() { List<User> userList = mapper.findUserAndOrderAll(); for (User user : userList) { System.out.println(user); } } @After public void after() { sqlSession.close(); } }
多对多查询
-
实体类
package com.cyz.domain; public class Role { private Long id; private String roleName; private String roleDesc; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getRoleName() { return roleName; } public void setRoleName(String roleName) { this.roleName = roleName; } public String getRoleDesc() { return roleDesc; } public void setRoleDesc(String roleDesc) { this.roleDesc = roleDesc; } @Override public String toString() { return "Role{" + "id=" + id + ", roleName='" + roleName + '\'' + ", roleDesc='" + roleDesc + '\'' + '}'; } }
package com.cyz.domain; import java.util.ArrayList; import java.util.List; public class User { private Long id; private String username; private String email; private String password; private String phoneNum; /** * 描述当前用户的订单列表 */ private List<Orders> ordersList; /** * 当前用户具备角色 */ private List<Role> roleList; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getPhoneNum() { return phoneNum; } public void setPhoneNum(String phoneNum) { this.phoneNum = phoneNum; } public List<Orders> getOrdersList() { return ordersList; } public void setOrdersList(List<Orders> ordersList) { this.ordersList = ordersList; } public List<Role> getRoleList() { return roleList; } public void setRoleList(List<Role> roleList) { this.roleList = roleList; } @Override public String toString() { return "User{" + "id=" + id + ", username='" + username + '\'' + ", email='" + email + '\'' + ", password='" + password + '\'' + ", phoneNum='" + phoneNum + '\'' + ", ordersList=" + ordersList + ", roleList=" + roleList + '}'; } }
-
dao
package com.cyz.mapper; import com.cyz.domain.User; import org.apache.ibatis.annotations.*; import java.util.List; public interface UserMapper { @Select("select * from sys_user") @Results({ @Result(column = "id" , property = "id"), @Result(column = "username" , property = "username"), @Result(column = "email" , property = "email"), @Result(column = "password" , property = "password"), @Result(column = "phoneNum" , property = "phoneNum"), @Result( property = "roleList", column = "id", javaType = List.class, many = @Many(select = "com.cyz.mapper.RoleMapper.findByUid") ) }) List<User> findUserAndRole(); }
-
子查询
package com.cyz.mapper; import com.cyz.domain.Role; import org.apache.ibatis.annotations.Select; import java.util.List; public interface RoleMapper { @Select("select * from sys_role r,sys_user_role ur where r.id = ur.roleId and ur.userId = #{uid}") List<Role> findByUid(Long uid); }
-
测试
package com.cyz;
import com.cyz.domain.User;
import com.cyz.mapper.UserMapper;
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.IOException;
import java.io.InputStream;
import java.util.List;
public class MyBatisTest {
private UserMapper mapper;
private SqlSession sqlSession;