1.结构图
2.实体类User
package com.itheima.domain;
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", birthday=" + birthday +
", sex='" + sex + '\'' +
", address='" + address + '\'' +
'}';
}
}
3.实体类QueryVo
package com.itheima.domain;
import java.util.List;
public class QueryVo {
private User user;
//通过QueryVo查询集合
private List<Integer> ids;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public List<Integer> getIds() {
return ids;
}
public void setIds(List<Integer> ids) {
this.ids = ids;
}
}
4.接口IUserDao
package com.itheima.dao;
import com.itheima.domain.QueryVo;
import com.itheima.domain.User;
import java.util.List;
/*数据库操作接口:我们当然也可以通过实现类实现,但是mybatis环境帮我们配置了,其实我们不用*/
public interface IUserDao {
//查询所有
List<User> findAll();
//查询一个
User findUserById(Integer userId);
//模糊查询
List<User> findUserByName(String username);
//通过其他实体类查询User里面封装的数据
List<User> findUserByQueryVo(QueryVo vo);
/*
* 根据传入的参数条件
* 参数:查询的条件:有可能有用户名,有可能有性别,也有可能有地址,还有可能是都有
* */
List<User> findUserByCondition(User user);
/*
*根据queryvo中提供的id集合,查询用户的信息
* */
List<User> findIntIds(QueryVo vo);
}
5.映射配置文件IUserMapper
<?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.itheima.dao.IUserDao">
<!--了解内容,抽取重复的sql语句-->
<sql id="defaultUser">
select * from user
</sql>
<!--查询所有-->
<!--<select id="findAll" resultType="com.itheima.domain.User">
select * from user
</select>-->
<select id="findAll" resultType="com.itheima.domain.User">
<include refid="defaultUser"></include>
</select>
<!--查询一个id-->
<select id="findUserById" parameterType="int" resultType="com.itheima.domain.User">
select * from user where id=#{id}
</select>
<!--模糊查询-->
<select id="findUserByName" parameterType="string" resultType="com.itheima.domain.User">
select * from user where username like #{name}
</select>
<!--查询数据库总的条数-->
<select id="findTotal" resultType="int">
select count(id) from user
</select>
<!--将User封装起来放入另外一个实体类中,并且不通过User来进行模糊查询-->
<select id="findUserByQueryVo" parameterType="com.itheima.domain.QueryVo" resultType="com.itheima.domain.User">
<!--先将QueryVo中的user找出来,然后再从user点到(找到)username-->
select * from user where username like #{user.username}
</select>
<!--按条件查询-->
<!--<select id="findUserByCondition" resultType="com.itheima.domain.User" parameterType="com.itheima.domain.User">
select * from user where 1=1
<if test="username != null">
and username = #{username}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
</select>-->
<!--将按条件查询的where 1=1方式改为标签where的方式来进行条件查询-->
<select id="findUserByCondition" resultType="com.itheima.domain.User" parameterType="com.itheima.domain.User">
select * from user
<where>
<if test="username != null">and username = #{username}</if>
<if test="sex != null">and sex = #{sex}</if>
</where>
</select>
<!--根据queryvo中的Id集合实现查询用户列表-->
<select id="findIntIds" resultType="com.itheima.domain.User" parameterType="com.itheima.domain.User">
select * from user
<where>
<if test="ids != null and ids.size()>0">
<foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
#{uid}
</foreach>
</if>
</where>
</select>
<!--
<foreach>标签用于遍历集合,它的属性:
collection:指定的集合
open:开始的位置
close:代表的是结束部分
item:代表遍历集合的每个元素,生成的变量名
separator:代表分隔符
-->
</mapper>
6.主配置文件SqlMapConfig
<?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"></properties>
<!--配置环境-->
<environments default="mysql">
<!--配置环境-->
<environment id="mysql">
<!--配置事务管理JDBC-->
<transactionManager type="JDBC"></transactionManager>
<!--POOLED-->
<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>
<!--IUserMapper.xml的配置-->
<mappers>
<mapper resource="com/itheima/dao/IUserMapper.xml"></mapper>
</mappers>
</configuration>
7.外部数据源
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_mybatis
jdbc.username=root
jdbc.password=123456
8.测试
package com.itheima;
import com.itheima.dao.IUserDao;
import com.itheima.domain.QueryVo;
import com.itheima.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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class MybatisCRUD {
//为了不用重复使用
private InputStream inputStream;
private SqlSessionFactory factory;
private SqlSession sqlSession;
private IUserDao userDao;
/*初始化,在程序一运行的时候就准备好*/
@Before
public void init() throws Exception{
//1.获取配置文件SqlMapConfig的字节流
inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
//2.获取SqlSessionFactory,让builder创建factory
factory = new SqlSessionFactoryBuilder().build(inputStream);
//3.使用工厂创建session对象
sqlSession = factory.openSession();
//4.获取dao代理
userDao = sqlSession.getMapper(IUserDao.class);
}
//查询所有
@Test
public void findAlData(){
//5.使用userDao调用执行该方法--返回一个结果集
List<User> users = userDao.findAll();
for (User user:users){
System.out.println(user);
}
}
//根据id查询一个用户信息
@Test
public void findUserByIdData(){
User user = userDao.findUserById(1);
System.out.println("查询id成功"+user);
}
//根据名字模糊查询用户信息
@Test
public void findUserByNameData(){
/*注意查询的格式就可以了*/
List<User> users = userDao.findUserByName("%王%");
/*List<User> users = userDao.findUserByName("王");*/
for (User user:users) {
System.out.println("模糊查询name成功"+user);
}
System.out.println("模糊查询成功");
}
//通过其他(QueryVo)实体类找到User的数据并且进行模糊查询到里面的数据
/*这有什么用尼?在于多表模糊查询吗?*/
/*传递pojo包装对象*/
@Test
public void findUserByQueryVoData(){
QueryVo queryVo = new QueryVo();
User user = new User();
//先在User中进行模糊查询
user.setUsername("%王%");
//然后再queryVo进行使用user存放到queryVo中
queryVo.setUser(user);
List<User> users = userDao.findUserByQueryVo(queryVo);
for (User u:users){
System.out.println("通过queryVo进行使用user中的模糊查询(由于这两个变量都是对象,所以我也不知道"+u);
}
}
/*按条件查询*/
@Test
public void testFindUserByCondition(){
User u = new User();
//按名字查询
u.setUsername("老王");
//按性别查询
u.setSex("女");
List<User> users = userDao.findUserByCondition(u);
/*执行查询按条件查询*/
for (User user:users) {
System.out.println(user);
}
System.out.println("按条件查询成功");
}
/*测试forEach标签的使用
* */
@Test
public void testFindInIds(){
QueryVo vo = new QueryVo();
List<Integer> list = new ArrayList<Integer>();
list.add(5);
list.add(4);
list.add(3);
vo.setIds(list);
List<User> users = userDao.findIntIds(vo);
/*执行查询按条件查询*/
for (User user:users) {
System.out.println(user);
}
System.out.println("按forEach询成功");
}
/*释放资源*/
@After
public void destroy() throws Exception{
//7.事务提交
sqlSession.commit();
//6.释放资源
sqlSession.close();
inputStream.close();
}
}
9.依赖
<dependencies>
<!--sql驱动:连接数据库用-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<!--测试的依赖-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!--日志-->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!--mybatis环境-->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.5</version>
</dependency>
</dependencies>