Java数据库编程--mybatis

• 加载指定包package所有的接口。

​ 在开发中，一个对象的查询条件，比对象中封装的属性更多。例如：user有birthday，我们需要查询2010-2020年的数据，时间段beginTime、endTime在User对象中就没有提供属性，实际开发中，一般提供一个自定义对象，例如：UserVo。

• 1）编写vo

  package com.czxy.ssm.vo;
  
  
  public class UserVo {
      private String beginTime;       // 开始时间
      private String endTime;         // 结束时间
  
      public UserVo() {
      }
  
      public UserVo(String beginTime, String endTime) {
          this.beginTime = beginTime;
          this.endTime = endTime;
      }
  
      public String getBeginTime() {
          return beginTime;
      }
  
      public void setBeginTime(String beginTime) {
          this.beginTime = beginTime;
      }
  
      public String getEndTime() {
          return endTime;
      }
  
      public void setEndTime(String endTime) {
          this.endTime = endTime;
      }
  }
  
  

• 2）修改Mapper，添加 condition 方法

      
      @Select("select * from user where birthday >= #{beginTime} and birthday <= #{endTime}")
      public List condition(UserVo userVo);
  

• 3）测试类

  package com.czxy.ssm.test;
  
  import com.czxy.ssm.domain.User;
  import com.czxy.ssm.mapper.UserMapper;
  import com.czxy.ssm.utils.MyBatisUtils;
  import com.czxy.ssm.vo.UserVo;
  
  import java.util.Date;
  import java.util.List;
  
  
  public class Test07_Condition {
      public static void main(String[] args) {
          UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);
  
          List list = userMapper.condition(new UserVo("2010-01-01", "2021-01-01"));
          // 打印
          list.forEach(user -> {
              System.out.println(user);
          });
  
          MyBatisUtils.commitAndclose();
      }
  }
  
  

实际开发中，如果数据库字段和Java对象字段不能对应，就需要我们编写对应关系。

• 语法

    
@Results(value = {
     @Result(property = "属性名", column = "字段名", id = true),
     @Result(property = "属性名2", column = "字段名2"),
     @Result(property = "属性名3", column = "字段名3")
})

• 基本使用

    
    @Select("select * from user")
    @Results({
            @Result(property = "uid", column = "uid", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password")
    })
    public List selectAll();

• 语法

  @Select(...)
  @Results(id = "标识", value = {...})
  public 返回值 方法名();
  
  @Select(...)
  @ResultMap("标识")
  public 返回值 方法名2();
  

• 使用

      
      @Select("select * from user")
      @Results(id = "userResult", value = {
              @Result(property = "uid", column = "uid", id = true),
              @Result(property = "username", column = "username"),
              @Result(property = "password", column = "password")
      })
      public List selectAll();
  
      
      @Select("select * from user where uid = #{uid}")
      @ResultMap("userResult")
      public User selectById(@Param("uid") String uid);
  

CREATE TABLE `orders` (
  `oid` VARCHAR(32) PRIMARY KEY NOT NULL,
  `ordertime` DATETIME DEFAULT NULL,		#下单时间
  `total_price` DOUBLE DEFAULT NULL,		#总价
  `state` INT(11) DEFAULT NULL,			#订单状态：1=未付款;2=已付款,未发货;3=已发货,没收货;4=收货,订单结束
  `address` VARCHAR(30) DEFAULT NULL,		#收获地址
  `name` VARCHAR(20) DEFAULT NULL,		#收获人
  `telephone` VARCHAR(20) DEFAULT NULL,		#收货人电话
  `uid` VARCHAR(32) DEFAULT NULL,
  CONSTRAINT `order_fk_0001` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
) ;

INSERT INTO `orders` VALUES ('x001','2010-10-10',10,1,'江苏','张三','12345','u001');
INSERT INTO `orders` VALUES ('x002','2010-11-11',20,2,'河北','李四','67890','u001');
INSERT INTO `orders` VALUES ('x003','2011-10-10',30,3,'山西','王五','66666','u002')

• 默认情况下，两个JavaBean没有关系

package com.czxy.ssm.domain;

import java.util.Date;


public class Order {

	private String oid; 		// 订单编号
	private Date ordertime; 	// 下单时间
	private Double total; 		// 订单总金额
	private Integer state; 		// 订单状态 0 未支付 1 已支付 2已发货 3已收货
	private String address;		// 收货人地址
	private String name;		// 收货人姓名
	private String telephone;	// 收货人电话
	private String uid;

	@Override
	public String toString() {
		return "Order{" +
				"oid='" + oid + ''' +
				", ordertime=" + ordertime +
				", total=" + total +
				", state=" + state +
				", address='" + address + ''' +
				", name='" + name + ''' +
				", telephone='" + telephone + ''' +
				", uid='" + uid + ''' +
				'}';
	}

	public String getOid() {
		return oid;
	}

	public void setOid(String oid) {
		this.oid = oid;
	}

	public Date getOrdertime() {
		return ordertime;
	}

	public void setOrdertime(Date ordertime) {
		this.ordertime = ordertime;
	}

	public Double getTotal() {
		return total;
	}

	public void setTotal(Double total) {
		this.total = total;
	}

	public Integer getState() {
		return state;
	}

	public void setState(Integer state) {
		this.state = state;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getTelephone() {
		return telephone;
	}

	public void setTelephone(String telephone) {
		this.telephone = telephone;
	}

	public String getUid() {
		return uid;
	}

	public void setUid(String uid) {
		this.uid = uid;
	}


	public Order(String oid, Date ordertime, Double total, Integer state, String address, String name, String telephone, String uid) {
		this.oid = oid;
		this.ordertime = ordertime;
		this.total = total;
		this.state = state;
		this.address = address;
		this.name = name;
		this.telephone = telephone;
		this.uid = uid;
	}

	public Order() {

	}
}

• 以对象的方法，描述两个JavaBean之间的关系

• JavaBean：User

  public class User {
  
  	private String uid;
  	private String username;
  	private String password;
  	private String name;
  	private String email;
  	private Date birthday;
  	private String sex;
  	private Integer state;
  	private String code;
  	// 一对多：一个用户 拥有【多个用户】
  	private List orderList = new ArrayList<>();
      // ...
  }
  

• JavaBean：Order

  public class Order {
  
  	private String oid; 		// 订单编号
  	private Date ordertime; 	// 下单时间
  	private Double total; 		// 订单总金额
  	private Integer state; 		// 订单状态 0 未支付 1 已支付 2已发货 3已收货
  	private String address;		// 收货人地址
  	private String name;		// 收货人姓名
  	private String telephone;	// 收货人电话
  	private String uid;
  	// 多对一， 多个订单 属于 【一个用户】
  	private User user;
      // ...
  }
  

在Mybatis注解开发中，需要通过@Result进行关联关系的描述。

• 一对多：需要使用many属性和@Many注解。

@Result(
    property = "1表JavaBean属性名", 
    column = "1表字段名",
    many = @Many(select = "多表Mapper的方法签名")
)

• 需求：查询用户的同时，查询每个用户对应的订单
• 分析：
  • 修改OrderMapper，完成通过uid查询所有的订单
  • 修改UserMapper，完成查询用户信息时，查询对应的订单

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


public interface OrderMapper {

    
    @Select("select * from orders where uid = #{uid}")
    public Order findOrdersByUserId(@Param("uid") String uid) ;

}

public interface UserMapper {
    
    @Select("select * from user")
    @Results(id = "userResult", value = {
            @Result(property = "uid", column = "uid", id = true),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
            @Result(property="orderList" , many=@Many(select="com.czxy.ssm.mapper.OrderMapper.findOrdersByUserId")	, column="uid")
    })
    public List selectAll();

}

在Mybatis注解开发中，需要通过@Result进行关联关系的描述。

• 多对一：需要使用one属性和@One注解

@Result(
    property = "多表JavaBean属性名", 
    column = "多表字段名",
    one = @One(select ="1表Mapper的方法签名")
)

• 需求：查询订单时，查询关联的用户信息

• 分析：

  1）通过user_id查询用户详情

  2）通过id查询订单详情

    
    @Select("select * from user where uid = #{uid}")
    @ResultMap("userResult")
    public User selectById(@Param("uid") String uid);

    
    @Select("select * from orders where oid = #{oid}")
    @Results({
            @Result(property="oid" , column="oid"),
            @Result(property="ordertime" , column="ordertime"),
            @Result(property="total" , column="total"),
            @Result(property="state" , column="state"),
            @Result(property="address" , column="address"),
            @Result(property="name" , column="name"),
            @Result(property="telephone" , column="telephone"),
            @Result(property="uid" , column="uid"),
            @Result(property="user" , one=@One(select="com.czxy.ssm.mapper.UserMapper.selectById") , column="uid"),
    })

    public Order selectById(@Param("oid") String id);

#老师表
CREATE TABLE teacher(
 tid INT PRIMARY KEY,
 NAME VARCHAR(50)
); 
#学生表
CREATE TABLE student(
 sid INT PRIMARY KEY,
 NAME VARCHAR(50)
);
#中间表
CREATE TABLE teacher_student(
 teacher_id INT ,
 student_id INT,
 CONSTRAINT ts_t_fk FOREIGN KEY (teacher_id) REFERENCES teacher(tid),
 CONSTRAINT ts_s_fk FOREIGN KEY (student_id) REFERENCES student(sid)
);

INSERT INTO teacher VALUES (1,'肖老师'); 
INSERT INTO teacher VALUES (2,'马老师'); 

INSERT INTO student VALUES (1,'张三');
INSERT INTO student VALUES (2,'李四');
INSERT INTO student VALUES (3,'王五');

INSERT INTO teacher_student VALUES (1,1);
INSERT INTO teacher_student VALUES (1,2);
INSERT INTO teacher_student VALUES (1,3);
INSERT INTO teacher_student VALUES (2,1);
INSERT INTO teacher_student VALUES (2,2);

• JavaBean：Student

  package com.czxy.ssm.domain;
  
  import java.util.ArrayList;
  import java.util.List;
  
  
  public class Teacher {
      private Integer tid;
      private String name;
  
      private List studentList = new ArrayList<>();
  
      public Integer getTid() {
          return tid;
      }
  
      public void setTid(Integer tid) {
          this.tid = tid;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public List getStudentList() {
          return studentList;
      }
  
      public void setStudentList(List studentList) {
          this.studentList = studentList;
      }
  
      @Override
      public String toString() {
          return "Teacher{" +
                  "tid=" + tid +
                  ", name='" + name + ''' +
                  ", studentList=" + studentList +
                  '}';
      }
  }
  
  

• 需要根据老师tid查询中间表中，对应的所有学生id

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Student;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


public interface StudentMapper {
    
    @Select("select * from student s where s.sid in (select student_id from teacher_student where teacher_id = #{tid} )")
    public Student findStudentByTeacherId(@Param("tid") Integer tid) throws Exception;
}

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Teacher;
import org.apache.ibatis.annotations.*;


public interface TeacherMapper {

    @Select("select * from teacher t where  t.tid = #{tid}")
    @Results({
            @Result(property="tid" , column="tid"),
            @Result(property="name" , column="name"),
            @Result(property="studentList" , many=@Many(select="com.czxy.ssm.mapper.StudentMapper.findStudentByTeacherId") , column="tid"),
    })
    public Teacher selectById(@Param("tid") Integer tid);

}

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.Teacher;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.TeacherMapper;
import com.czxy.ssm.utils.MyBatisUtils;


public class Test09_SelectTeacher {
    public static void main(String[] args) {
        TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class);

        Teacher teacher = teacherMapper.selectById(1);
        // 打印
        System.out.println(teacher);

        MyBatisUtils.commitAndclose();
    }
}

• 查询所有的老师，并查询老师对应的学生。

  package com.czxy.ssm.mapper;
  
  import com.czxy.ssm.domain.Teacher;
  import org.apache.ibatis.annotations.*;
  
  import java.util.List;
  
  public interface TeacherMapper {
  
      
      @Select("select * from teacher t where  t.tid = #{tid}")
      @Results(id = "teacherResult" , value = {
              @Result(property="tid" , column="tid"),
              @Result(property="name" , column="name"),
              @Result(property="studentList" , many=@Many(select="com.czxy.ssm.mapper.StudentMapper.findStudentByTeacherId") , column="tid"),
      })
      public Teacher selectById(@Param("tid") Integer tid);
  
      
      @Select("select * from teacher")
      @ResultMap("teacherResult")
      public List selectList();
  
  }
  

• 测试类

  package com.czxy.ssm.test;
  
  import com.czxy.ssm.domain.Teacher;
  import com.czxy.ssm.mapper.TeacherMapper;
  import com.czxy.ssm.utils.MyBatisUtils;
  
  import java.util.List;
  
  
  public class Test10_SelectAllTeacher {
      public static void main(String[] args) {
          TeacherMapper teacherMapper = MyBatisUtils.getMapper(TeacherMapper.class);
  
          List teacherList = teacherMapper.selectList();
          // 打印
          teacherList.forEach(teacher -> {
              System.out.println(teacher);
          });
  
          MyBatisUtils.commitAndclose();
      }
  }
  

MyBatis没有提供分页支持，需要自己编写limit语句。

开发中我们采用PageHelper插件。

1) 设置分页数据
PageHelper.startPage(int pageNum, int pageSize)
	参数1：pageNum 第几页
	参数2：pageSize 页面显示个数
2) 封装分页结果 PageInfo
	new PageInfo(查询结果)	  //创建分页对象
	pageInfo.getTotal(),	//自动查询总条数
	pageInfo.getPages(),	//总分页数

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Teacher;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.TeacherMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

import java.util.List;

public class Test11_Page {
    public static void main(String[] args) {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        // 设置分页 **
        PageHelper.startPage(1,2);

        // 查询
        List users = userMapper.selectAll();

        // 获得封装对象 ** 
        PageInfo pageInfo = new PageInfo<>(users);

        // 打印分页信息
        long total = pageInfo.getTotal();
        List list = pageInfo.getList();
        System.out.println("总条数：" + total);
        System.out.println("分页数据：");
        list.forEach(user -> {
            System.out.println(user);
        });


        MyBatisUtils.commitAndclose();
    }
}

• 通用Mapper对MyBatis进行简化的第三方工具包。

• 通用Mapper提供了一个名为Mapper的接口，用于自动完成单表的增删改查操作。

public interface UserMapper extends Mapper {
}

• 如果通用Mapper中的方法不足以满足你的需求，直接添加自定义方法即可。

• 添加内容从官方文档中拷贝

package com.czxy.ssm.utils;

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 tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;
import tk.mybatis.mapper.entity.Config;
import tk.mybatis.mapper.mapperhelper.MapperHelper;

import java.io.InputStream;


public class MyBatisUtils {

    // 会话工厂
    private static SqlSessionFactory factory;
    static{
        try {
            // 1.1 加载核心配置文件
            InputStream is = Resources.getResourceAsStream("SqlMapConfig.xml");
            // 1.2 获得工厂
            factory = new SqlSessionFactoryBuilder().build(is);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    private static ThreadLocal local = new ThreadLocal<>();

    
    private static SqlSession openSession(){
        SqlSession sqlSession = local.get();
        if(sqlSession == null){
            sqlSession = factory.openSession();

            //创建一个MapperHelper
            MapperHelper mapperHelper = new MapperHelper();
            //特殊配置
            Config config = new Config();
            // 设置UUID生成策略
            // 配置UUID生成策略需要使用OGNL表达式
            // 默认值32位长度:@java.util.UUID@randomUUID().toString().replace("-", "")
            //config.setUUID("");
            // 主键自增回写方法,默认值MYSQL,详细说明请看文档
//	        config.setIDENTITY("HSQLDB");
            // 支持方法上的注解
            // 3.3.1版本增加
            config.setEnableMethodAnnotation(true);
            config.setNotEmpty(true);
            // 序列的获取规则,使用{num}格式化参数，默认值为{0}.nextval，针对Oracle
            // 可选参数一共3个，对应0,1,2,分别为SequenceName，ColumnName, PropertyName
            //config.setSeqFormat("NEXT VALUE FOR {0}");
            // 设置全局的catalog,默认为空，如果设置了值，操作表时的sql会是catalog.tablename
            //config.setCatalog("");
            // 设置全局的schema,默认为空，如果设置了值，操作表时的sql会是schema.tablename
            // 如果同时设置了catalog,优先使用catalog.tablename
            //config.setSchema("");
            // 主键自增回写方法执行顺序,默认AFTER,可选值为(BEFORE|AFTER)
            //config.setOrder("AFTER");
            //设置配置
            mapperHelper.setConfig(config);
            // 注册通用tk.mybatis.mapper.common.Mapper接口 - 可以自动注册继承的接口
            mapperHelper.registerMapper(Mapper.class);
            mapperHelper.registerMapper(MySqlMapper.class);
//	        mapperHelper.registerMapper(SqlServerMapper.class);
//	        mapperHelper.registerMapper(IdsMapper.class);
            //配置完成后，执行下面的操作
            mapperHelper.processConfiguration(sqlSession.getConfiguration());


            local.set(sqlSession);
        }
        return sqlSession;
    }

    
    public static  T getMapper(Class clazz){
        return openSession().getMapper(clazz);
    }

    
    public static void close() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.close();
        }
    }

    
    public static void commitAndclose() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.commit();
            close();
        }
    }

    
    public static void rollbackAndclose() {
        SqlSession sqlSession = openSession();
        if(sqlSession != null){
            sqlSession.rollback();
            close();
        }
    }

}

• 编写接口，继承tk.mybatis.mapper.common.Mapper接口即可
  • 注意：Mapper接口以tk开头

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.User;
import tk.mybatis.mapper.common.Mapper;


public interface UserMapper2 extends Mapper {
}

• 查询方法

  方法名	描述
  T selectOne(T t)	根据实体中的属性进行查询，只能有一个返回值，有多个结果是抛出异常，查询条件使用等号
  List select(T t)	根据实体中的属性值进行查询，查询条件使用等号
  List selectAll()	查询全部结果
  int selectCount(T t)	根据实体中的属性查询总数，查询条件，使用等号
  T selectByPrimaryKey(Object key)	根据主键字段进行查询
  boolean existsWhithPrimaryKey(Object key)	根据主键字段查询记录是否存在
  List selectByExample(Object example)	根据Example条件进行查询
  T selectoneByExample(Object example)	根据Example条件进行查询，只能有一个返回值
  int selectCountByExample(Object example)	根据Example条件进行查询记录数

• 插入方法

  方法名	描述
  int insert(T t)	保存一个实体，null的属性也会保存，不会使用数据库默认值
  int intsertSelective(T t)	保存一个实体，null的属性不会保存，使用数据库默认值

• 更新方法

  方法名	描述
  int updateByPrimaryKey(T t)	根据主键更新实体全部字段，null值会被更新
  int updateByPrimaryKeySelective(T t)	根据主键更新实体中不为null值的字段

• 删除方法

  方法名	描述
  int delete(T t)	根据实体属性作为条件进行删除，查询条件使用等号
  int deletePrimaryKey(Object key)	根据主键字段进行删除
  int deleteByExample(Object example)	根据Example条件删除数据

• 1）确定主键，否则所有字段都是主键

• 2）测试

  package com.czxy.ssm.test;
  
  import com.czxy.ssm.domain.User;
  import com.czxy.ssm.mapper.UserMapper;
  import com.czxy.ssm.mapper.UserMapper2;
  import com.czxy.ssm.utils.MyBatisUtils;
  import org.junit.Test;
  
  import java.io.IOException;
  import java.util.List;
  
  
  public class Test13_Mapper {
      @Test
      public void testSelectByPrimaryKey() {
          UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
          User user = userMapper2.selectByPrimaryKey("1");
          System.out.println(user);
  
          MyBatisUtils.commitAndclose();
  
      }
  }
  

    @Test
    public void testSelectAll() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);
        List list = userMapper2.selectAll();
        // 打印
        list.forEach(System.out::println);

        MyBatisUtils.commitAndclose();
    }

@Test
    public void testInsert() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("杰克");
        user.setEmail("itcast_lt@163.com");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setSex("0");

        int result = userMapper2.insert(user);

        // 打印
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

@Test
    public void testUpdate() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        User user = new User();
        user.setUid("2");
        user.setUsername("jack");
        user.setPassword("1234");
        user.setName("杰克");
        user.setEmail("itcast_lt@163.com");
        user.setBirthday(new Date());
        user.setSex("男");
        user.setSex("0");

        int result = userMapper2.updateByPrimaryKey(user);

        // 打印
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

    @Test
    public void testDelete() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        int result = userMapper2.deleteByPrimaryKey("2");

        // 打印
        System.out.println(result);

        MyBatisUtils.commitAndclose();
    }

• 语法：

// 获得多条件对象
Example example = new Example(对象.class);
Example.Criteria criteria = example.createCriteria();

// 常见条件方法
andLike()					//模糊查询
andEqualTo()				//等值查询
andLessThanOrEqualTo()		//<=查询
andGreaterThanOrEqualTo()	//>=查询
andBetween()				//区间查询

@Test
    public void testCondition() {
        UserMapper2 userMapper2 = MyBatisUtils.getMapper(UserMapper2.class);

        Example example = new Example(User.class);
        Example.Criteria criteria = example.createCriteria();
        criteria.andLike("name", "%王%");
        criteria.andEqualTo("sex", "男");

        List list = userMapper2.selectByExample(example);
        for (User user : list) {
            System.out.println(user);
        }

        MyBatisUtils.commitAndclose();
    }

• 1）选择Web 应用

• 2）创建项目mybatis-demo02

• 步骤1：编写Dao接口，用于确定方法名称

• 步骤2：编写Mapper.xml文件，用于编写SQL语句

• 步骤3：编写SqlMapConfig.xml 核心配置文件，并添加mapper xml文件

• 步骤4：测试

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.User;


public interface UserMapper {
    
    public User selectById(String uid);
}

    
		select * from user where uid = #{id}
	

 
9.2.4 修改核心配置文件 
核心配置文件  用于确定mapper配置文件的位置
 
	
		
		
	
 
9.2.5 测试类 
package com.czxy.ssm.test;

import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import org.junit.Test;


public class TestUserMapper {


    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("1");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }

}

 
9.3 配置文件详解 
9.3.1 Mapper基本结构 



	
    SQL语句
    
	
	SQL语句
    
    
	SQL语句
    
    
	SQL语句

• 在映射文件mapper中，我们使用parameterType设置请求参数的类型

    SQL语句

• 简单类型：

  
  	select * from user where uid = #{id}
  
   
   
  POJO类型：
   
  
  	insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})
  
   
   
  9.3.3 Mapper 结果类型：resultType 
  在映射文件mapper中，我们使用resultType设置查询结果类型
   
  
  
  
      SQL语句
  
  

  • 简单类型

    
    	select count(*) from user
    
     
     
    POJO类型
     
    
    	select * from user where uid = #{id}
    
    

  9.3.4 Mapper 映射关系：ResultMap

  • resultType可以指定pojo将查询结果封装到该pojo中，但需要pojo的属性名和sql查询的列名保持一致。

  • 如果sql查询字段名和pojo的属性名不一致，可以通过resultMap将字段名和属性名作一个对应关系。

  • 语法：

  • 实例：

    	
    		
    		
    	
    
        
    		select * from user where uid = #{id}
    	
      
    9.3.5 默认别名 
    别名
    映射类型
    _byte
    byte
    _long
    long
    _short
    short
    _int
    int
    _integer
    int
    _double
    double
    _float
    float
    _boolean
    boolean
    string
    String
    byte
    Byte
    long
    Long
    short
    Short
    int
    Integer
    integer
    Integer
    double
    Double
    float
    Float
    boolean
    Boolean
    date
    Date
    decimal
    BigDecimal
    bigdecimal
    BigDecimal
    map
    Map
    9.3.6 自定义别名 
     
    声明别名，在 SqlMapConfig.xml 配置文件中声明
     
      
     
    方式1：一次定义一个类
     
    
    	
    
     
     
    方式2：一次定义一个包下的所有类
     
    
    	
    
     
     
     
    使用别名，在mapper中直接使用
     
    
    	select * from user where uid = #{id}
    
    

  9.3.7 加载Mapper文件

  • 在SqlMapConfig.xml文件中，通过和加载映射文件

  • 方式1：加载指定文件

  • 方式2：加载指定包下的所有映射文件

    • 要求：要求mapper接口名称和mapper映射文件名称相同，且放在同一个目录中

  9.4 增删改查 9.4.1 核心配置文件 9.4.2 mapper接口

  package com.czxy.ssm.mapper;
  
  import com.czxy.ssm.domain.User;
  import com.czxy.ssm.vo.UserVo;
  import org.apache.ibatis.annotations.*;
  
  import java.util.List;
  
  
  public interface UserMapper {
      
      public User selectById(String uid);
  
      
      public List selectAll();
  
  
      
      public List selectByName(@Param("name") String name);
  
      
      public Integer insert(User user);
  
      
      public Integer updateByPrimaryKey(User user);
  
      
      public Integer deleteByPrimaryKey(@Param("uid") String uid);
  
      
      public List condition(UserVo userVo);
  }
  
  

  9.4.3 Mapper 映射文件

  
  
  
  
  	
  		
  		
  	
  
      
  		select * from user where uid = #{id}
  	
  
  	
  		select * from user
  	
  
  	
  		select * from user where name like #{name}
  	
  
  	
  		insert into user(uid, username, password, name, email, birthday, sex, state) values(#{uid},#{username},#{password},#{name},#{email},#{birthday},#{sex},#{state})
  	
  
  	
  		update user set username=#{username}, password=#{password}, name=#{name}, email=#{email},birthday=#{birthday},sex=#{sex}, state=#{state} where uid=#{uid}
  	
  
  	
  		delete from user where uid = #{uid}
  	
  
  
  
   
  9.4.4 测试 
  package com.czxy.ssm.test;
  
  import com.czxy.ssm.domain.User;
  import com.czxy.ssm.mapper.UserMapper;
  import com.czxy.ssm.utils.MyBatisUtils;
  import org.junit.Test;
  
  import java.util.Date;
  import java.util.List;
  
  
  public class TestUserMapper {
  
  
      @Test
      public void testSelectById() {
          UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);
  
          User user = userMapper.selectById("1");
          System.out.println(user);
  
          MyBatisUtils.commitAndclose();
      }
  
      @Test
      public void testSelectAll() {
          UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
          List list = UserMapper.selectAll();
          // 打印
          list.forEach(System.out::println);
  
          MyBatisUtils.commitAndclose();
      }
  
      @Test
      public void testSelectByName() {
          UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
          List list = UserMapper.selectByName("%张%");
          // 打印
          list.forEach(System.out::println);
  
          MyBatisUtils.commitAndclose();
      }
  
      @Test
      public void testInsert() {
          UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
  
          User user = new User();
          user.setUid("2");
          user.setUsername("jack");
          user.setPassword("1234");
          user.setName("杰克");
          user.setEmail("itcast_lt@163.com");
          user.setBirthday(new Date());
          user.setSex("男");
          user.setSex("0");
  
          int result = UserMapper.insert(user);
  
          // 打印
          System.out.println(result);
  
          MyBatisUtils.commitAndclose();
      }
  
      @Test
      public void testUpdate() {
          UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
  
          User user = new User();
          user.setUid("2");
          user.setUsername("jack");
          user.setPassword("1234");
          user.setName("杰克");
          user.setEmail("itcast_lt@163.com");
          user.setBirthday(new Date());
          user.setSex("男");
          user.setSex("0");
  
          int result = UserMapper.updateByPrimaryKey(user);
  
          // 打印
          System.out.println(result);
  
          MyBatisUtils.commitAndclose();
      }
  
      @Test
      public void testDelete() {
          UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
  
          int result = UserMapper.deleteByPrimaryKey("2");
  
          // 打印
          System.out.println(result);
  
          MyBatisUtils.commitAndclose();
      }
  
  }
  
   
  9.5 动态SQL 
  9.5.1 什么是动态SQL 
  动态SQL就是Mybatis允许在映射文件中通过标签控制SQL语句最后的拼凑结果。
   
  9.5.2 标签 
   
  语法：在mapper映射文件中，标签就相当于Java中if语句，如果条件成立，标签体内的SQL语句有效。
   
  
  	
  		//有效的SQL语句片段
  	
  
   
   
  封装对象：UserVo
   
  public class UserVo {
      private String name;            // 模糊查询
      private String beginTime;       // 开始时间
      private String endTime;         // 结束时间
      // ...
  }
   
   
  多条件查询，拼凑恒等条件 where 1=1
   
  	
  		select * from user where 1=1
  		
  			and name like '%${name}%'
  		
  	
  

• 测试

      @Test
      public void testCondtion() {
          UserMapper UserMapper = MyBatisUtils.getMapper(UserMapper.class);
  
          UserVo userVo = new UserVo();
          userVo.setName("张");
  
          List list = UserMapper.condition(userVo);
          // 打印
          list.forEach(System.out::println);
  
          MyBatisUtils.commitAndclose();
      }
  

uid, username, password, name, email, birthday, sex, state


	select  from user

 
9.6 关系映射：一对多 
9.6.1 mapper接口 
 
UserMapper，已有
 
public interface UserMapper {
    
    public User selectById(String uid);
}
 
 
OrderMapper
 
package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;


public interface OrderMapper {

    
    public Order findOrdersByUserId(@Param("uid") Integer uid) ;
}

 
 
9.6.2 Mapper 映射文件 
 
OrdersMapper.xml
 





    
		select * from orders where uid = #{uid}
	

	
		
		
		
	



    
		select * from user where uid = #{id}
	
    

  
9.6.3 核心配置文件 
9.6.4 测试 
public class TestUserMapper {

    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("u001");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }
    
}
 
9.7 关系映射：多对一 
9.7.1 mapper接口 
package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;

import java.util.List;


public interface OrderMapper {

    
    public Order selectById(@Param("oid") String oid);
}

 
9.7.2 映射文件：OrdersMapper.xml 




	
		
		
		
		
		
		
		
		
		
	
	
		select * from orders where oid = #{oid}
	

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import com.czxy.ssm.vo.UserVo;
import org.junit.Test;

import java.util.Arrays;
import java.util.Date;
import java.util.List;


public class TestOrderMapper {


    @Test
    public void testSelectById() {
        OrderMapper orderMapper = MyBatisUtils.getMapper(OrderMapper.class);

        Order order = orderMapper.selectById("x001");
        System.out.println(order);

        MyBatisUtils.commitAndclose();
    }

}

-3-mapper.dtd">
  
  
  
      
  		select * from orders where uid = #{uid}
  	
  
  
 
 
UserMapper.xml
 




	
		
		
		
	



    
		select * from user where uid = #{id}
	
    

public class TestUserMapper {

    @Test
    public void testSelectById() {
        UserMapper userMapper = MyBatisUtils.getMapper(UserMapper.class);

        User user = userMapper.selectById("u001");
        System.out.println(user);

        MyBatisUtils.commitAndclose();
    }
    
}

package com.czxy.ssm.mapper;

import com.czxy.ssm.domain.Order;
import org.apache.ibatis.annotations.Param;

import java.util.List;


public interface OrderMapper {

    
    public Order selectById(@Param("oid") String oid);
}

	
		
		
		
		
		
		
		
		
		
	
	
		select * from orders where oid = #{oid}
	

package com.czxy.ssm.test;

import com.czxy.ssm.domain.Order;
import com.czxy.ssm.domain.User;
import com.czxy.ssm.mapper.OrderMapper;
import com.czxy.ssm.mapper.UserMapper;
import com.czxy.ssm.utils.MyBatisUtils;
import com.czxy.ssm.vo.UserVo;
import org.junit.Test;

import java.util.Arrays;
import java.util.Date;
import java.util.List;


public class TestOrderMapper {


    @Test
    public void testSelectById() {
        OrderMapper orderMapper = MyBatisUtils.getMapper(OrderMapper.class);

        Order order = orderMapper.selectById("x001");
        System.out.println(order);

        MyBatisUtils.commitAndclose();
    }

}

        
        //一对多
        
		//多对一
		"user" column="uid" select="com.czxy.ssm.mapper.UserMapper.user">


将数据库表中的uid拿到 com.czxy.ssm.mapper.UserMapper.user进行执行 然后将结果封装到user中