CREATE TABLE `tb_item`(
`id` int(11) not null AUTO_INCREMENT,
`item_name` varchar(32) NOT NULL COMMENT '商品名称',
`item_price` float(6,1) NOT NULL COMMENT '商品价格',
`item_detail` text COMMENT '商品描述',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `tb_item` VALUES('1','iPhone 6','5288.0','苹果公司新发布的手机产品。');
INSERT INTO `tb_item` VALUES('2','iPhone 6 Plus','6288.0','苹果公司发布的大屏手机。');
CREATE TABLE `tb_user`(
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(100) DEFAULT NULL COMMENT '用户名',
`password` varchar(100) DEFAULT NULL COMMENT '密码',
`name` varchar(100) DEFAULT NULL COMMENT '姓名',
`age` int(10) DEFAULT NULL COMMENT '年龄',
`sex` tinyint(1) DEFAULT NULL COMMENT '性别，1男性，2女性',
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `tb_user`VALUES('1','zhangsan','123456','张三','30','1');
INSERT INTO `tb_user`VALUES('2','lishi','123456','李四','21','0');
INSERT INTO `tb_user`VALUES('3','wangwu','123456','王五','22','1');
INSERT INTO `tb_user`VALUES('4','zhangwei','123456','张伟','20','1');
INSERT INTO `tb_user`VALUES ('5', 'lina', '123456', '李娜','28', '0');
INSERT INTO `tb_user`VALUES ('6', '蔡徐坤', '123456', '小菜','18', '1');
CREATE TABLE `tb_order`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`order_number` varchar(20) NOT NULL COMMENT '订单号',
PRIMARY KEY (`id`),
KEY `FK_orders_1` (`user_id`),
CONSTRAINT `FK_orders_1` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
)ENGINE=INNODB CHARSET=utf8;
INSERT INTO `tb_order` VALUES('1','1','20140921001');
INSERT INTO `tb_order` VALUES('2','2','20140921002');
INSERT INTO `tb_order` VALUES('3','1','20140921003');
CREATE TABLE `tb_orderdetail`(
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(32) DEFAULT NULL COMMENT '订单号',
`item_id` int(32) DEFAULT NULL COMMENT '商品id',
`total_price` double(20,0) DEFAULT NULL COMMENT '商品总价',
`status` int(11) DEFAULT NULL COMMENT '状态',
PRIMARY KEY (`id`),
KEY `FK_orderdetail_1` (`order_id`),
KEY `FK_orderdetail_2` (`item_id`),
CONSTRAINT `FK_orderdetail_1` FOREIGN KEY (`order_id`) REFERENCES `tb_order` (`id`),
CONSTRAINT `FK_orderdetail_2` FOREIGN KEY (`item_id`) REFERENCES `tb_order` (`id`)
)ENGINE=INNODB CHARSET=utf8;
INSERT INTO `tb_orderdetail` VALUES('1','1','1','5288','1');
INSERT INTO `tb_orderdetail` VALUES('2','1','2','6288','1');
INSERT INTO `tb_orderdetail` VALUES('3','2','2','6288','1');
INSERT INTO `tb_orderdetail` VALUES('4','3','1','5288','1');
//Item
package com.itheima.pojo;
public class Item {
private Integer id;
private String itemName;
private Float itemPrice;
private String itemDetail;
public Item() {
}
public Item(Integer id, String itemName, Float itemPrice, String itemDetail) {
this.id = id;
this.itemName = itemName;
this.itemPrice = itemPrice;
this.itemDetail = itemDetail;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getItemName() {
return itemName;
}
public void setItemName(String itemName) {
this.itemName = itemName;
}
public Float getItemPrice() {
return itemPrice;
}
public void setItemPrice(Float itemPrice) {
this.itemPrice = itemPrice;
}
public String getItemDetail() {
return itemDetail;
}
public void setItemDetail(String itemDetail) {
this.itemDetail = itemDetail;
}
@Override
public String toString() {
return "Item{" +
"id=" + id +
", itemName='" + itemName + '\'' +
", itemPrice=" + itemPrice +
", itemDetail='" + itemDetail + '\'' +
'}';
}
}
//order
package com.itheima.pojo;
public class Order {
private Integer id;
private String orderNumber;
public Order() {
}
public Order(Integer id, String orderNumber) {
this.id = id;
this.orderNumber = orderNumber;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderNumber='" + orderNumber + '\'' +
'}';
}
}
//orderDetail
package com.itheima.pojo;
public class Orderdetail {
private Integer id;
private Double totalPrice;
private Integer status;
public Orderdetail() {
}
public Orderdetail(Integer id, Double totalPrice, Integer status) {
this.id = id;
this.totalPrice = totalPrice;
this.status = status;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Double getTotalPrice() {
return totalPrice;
}
public void setTotalPrice(Double totalPrice) {
this.totalPrice = totalPrice;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
@Override
public String toString() {
return "Orderdetail{" +
"id=" + id +
", totalPrice=" + totalPrice +
", status=" + status +
'}';
}
}
//user
package com.itheima.pojo;
import java.io.Serializable;
public class User {
private Long id;
private String userName;
private String password;
private String name;
private Integer age;
private Integer sex;
public User() {
}
public User(Long id, String userName, String password, String name, Integer age, Integer sex) {
this.id = id;
this.userName = userName;
this.password = password;
this.name = name;
this.age = age;
this.sex = sex;
}
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
", name='" + name + '\'' +
", age=" + age +
", sex=" + sex +
'}';
}
}
<?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>
<!--开启驼峰映射：把数据表中的带有下划线的字段，变为java的驼峰命名方式。user_name->username userName-->
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--设置别名-->
<typeAliases>
<!--扫描指定的名,所包下的所有的类，都起了别名，别名格式：全部小写或驼峰命名-->
<package name="com.itheima.pojo"/>
<!-- <typeAlias type="com.itheima.pojo.User" alias="User"></typeAlias>-->
</typeAliases>
<!--配置数据源（数据库连接）-->
<environments default="dev">
<!--开发环境-->
<environment id="dev">
<!--事务管理器：按照jdbc默信的事务-->
<transactionManager type="JDBC"/>
<!--数据源(数据库连接池) mybatis有自带数据库连接池-->
<dataSource type="POOLED">
<!--连接数据库需要的4个参数：驱动、URL、登录名、密码-->
<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>
<!--测试环境-->
<environment id="test">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/hsp_db02?useUnicode=true&characterEncoding=utf-8&useSSL=false"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<!--映射映射相关文件-->
<mappers>
<!--方案1：指定关联的映射文件，通过映射文件在关联mapper接口-->
<!-- <mapper resource="com/itheima/dao/UserMapper.xml"/>-->
<!--方案1：指定关联的mapper接口（通过mapper接口关联映射文件）
要求：
1.映射文件名和mapper接口必须相同
2.映射文件和mapper接口存储在相同的路径下
-->
<!--指定要扫描的所有mapper接口的包路径-->
<package name="com.itheima.dao"/>
</mappers>
</configuration>
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis_db?useUnicode=true&characterEncoding=utf-8&useSSL=false
jdbc.username=root
jdbc.password=123456
package com.itheima.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 java.io.IOException;
import java.io.InputStream;
//工具类：私有化构造器+静态方 法
public class MybatisUtil {
//私有化构造方法
private MybatisUtil(){}
//SqlSessionFactory工厂类对象
private static SqlSessionFactory sqlSessionFactory=null;
//随着类的加载，仅执行一次
static {
try {
//读取配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//创建SqlSessionFactory工厂类
sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
//静态方法返回SqlSession对象
public static SqlSession openSession(){
//使用SqlSessionFactory工厂类，创建SqlSession对象
return sqlSessionFactory.openSession(); //默认：手动事务
}
public static SqlSession openSession(boolean flag){
//使用SqlSessionFactory工厂类，创建SqlSession对象
return sqlSessionFactory.openSession(flag);//根据参数指定事务
}
//静态方法关闭sqlSession
public static void closeSqlSession(SqlSession sqlSession){
if(sqlSession!=null){
sqlSession.close();
}
}
}
public interface UserMapper {
/**
* 根据用户ID查询用户信息
* @param id 用户ID
* @return 返回对象
*/
public User findUserById(Integer 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">
<!--利用namespace属性，绑定当前映射文件和mapper接口映射关联-->
<mapper namespace="com.itheima.dao.UserMapper">
<select id="findUserById" resultType="com.itheima.pojo.User">
select id,user_name as userName,password,name,age,sex from tb_user
where id=#{id}
</select>
</mapper>
package com.itheima;
import com.itheima.dao.UserMapper;
import com.itheima.pojo.User;
import com.itheima.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class UserMapperTest {
@Test
public void testFindUserById(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession();
//利用sqlSession，创建一个代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findUserById(1);
System.out.println(user);
//释放资源
MybatisUtil.closeSqlSession(sqlSession);
}
}
mybatis多表查询的套路：
1.基于需求编写SQ语句
2.基于SQL语句的查询结果，分析类与类之间关联（建立实体类和实体类的关联）
1对1查询结果：在Order类添加新属性:User 对象
3.在映射文年中，基于SQL查询结果（配置关联）
select tb_order.id as order_id,
tb_order.order_number,
tb_user.id as user_id,
tb_user.user_name,
tb_user.password,
tb_user.name,
tb_user.sex,
tb_user.age
from tb_order inner join tb_user
on tb_user.id=tb_order.user_id
where tb_order.order_number='20140921003'
<?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">
<!--利用namespace属性，绑定当前映射文件和mapper接口映射关联-->
<mapper namespace="com.itheima.dao.OrderMapper">
<!--resultMap标签：解决查询结果字段名和实体类属性不一致的问题，解决多表关联映射-->
<resultMap id="orderMap" type="com.itheima.pojo.Order" autoMapping="true">
<!--配置；Order查询结果和order类的映射关联-->
<id column="order_id" property="id"></id>
<result column="order_number" property="orderNumber"></result>
<!--配置：1对1查询
配置：查询结果和User类的映射
-->
<association property="orderUser" javaType="com.itheima.pojo.User">
<id column="user_id" property="id"></id>
<result column="user_name" property="userName"></result>
</association>
</resultMap>
<!--根据订单编号，查询下单信息及下单人员信息-->
<select id="findOrderByNumber" resultMap="orderMap">
select tb_order.id as order_id,
tb_order.order_number,
tb_user.id as user_id,
tb_user.user_name,
tb_user.password,
tb_user.name,
tb_user.sex,
tb_user.age
from tb_order inner join tb_user
on tb_user.id=tb_order.user_id
where tb_order.order_number=#{orderNumber}
</select>
</mapper>
package com.itheima.dao;
import com.itheima.pojo.Order;
public interface OrderMapper {
/**
* 根成订单号查询订单信息
* @param orderNumber
* @return
*/
public Order findOrderByNumber(String orderNumber);
}
order
package com.itheima.pojo;
public class Order {
private Integer id; //订单ID
private String orderNumber;//订单编号
public User getOrderUser() {
return orderUser;
}
public void setOrderUser(User orderUser) {
this.orderUser = orderUser;
}
//下单人
private User orderUser; //用户对象
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderNumber='" + orderNumber + '\'' +
'}';
}
}
user
package com.itheima.pojo;
import java.io.Serializable;
import java.util.List;
public class User {
private Long id;
private String userName;
private String password;
private String name;
private Integer age;
private Integer sex;
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
", name='" + name + '\'' +
", age=" + age +
", sex=" + sex +
'}';
}
}
package com.itheima;
import com.itheima.dao.OrderMapper;
import com.itheima.dao.UserMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.User;
import com.itheima.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
public class OrderMapperTest {
@Test
public void testFindOrderByNumber(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession();
//利用sqlSession，创建一个代理对象
OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);
Order order = orderMapper.findOrderByNumber("20140921003");
System.out.println("订单信息"+order);
User user=order.getOrderUser();
System.out.println("订单人信息:"+user);
//释放资源
MybatisUtil.closeSqlSession(sqlSession);
}
}
2024-01-19 14:30:50.512 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 14:30:51.219 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 1267149311.
2024-01-19 14:30:51.220 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 14:30:51.226 [DEBUG] com.itheima.dao.OrderMapper.findOrderByNumber [main] : > Preparing: select tb_order.id as order_id, tb_order.order_number, tb_user.id as user_id, tb_user.user_name, tb_user.password, tb_user.name, tb_user.sex, tb_user.age from tb_order inner join tb_user on tb_user.id=tb_order.user_id where tb_order.order_number=?
2024-01-19 14:30:51.312 [DEBUG] com.itheima.dao.OrderMapper.findOrderByNumber [main] : > Parameters: 20140921003(String)
2024-01-19 14:30:51.350 [TRACE] com.itheima.dao.OrderMapper.findOrderByNumber [main] : < Columns: order_id, order_number, user_id, user_name, password, name, sex, age
2024-01-19 14:30:51.351 [TRACE] com.itheima.dao.OrderMapper.findOrderByNumber [main] : < Row: 3, 20140921003, 1, zhangsan, 123456, 张三, 1, 30
2024-01-19 14:30:51.355 [DEBUG] com.itheima.dao.OrderMapper.findOrderByNumber [main] : <== Total: 1
订单信息Order{id=3, orderNumber=‘20140921003’}
订单人信息:User{id=1, userName=‘zhangsan’, password=‘null’, name=‘null’, age=null, sex=null}
2024-01-19 14:30:51.380 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 14:30:51.381 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 14:30:51.382 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 1267149311 to pool.Process finished with exit code 0
1对多查询结果：在User类中添加新属性：List 集合
select
tb_user.id as user_id,
tb_user.user_name,
tb_user.password,
tb_user.name,
tb_user.sex,
tb_user.age,
tb_order.id as order_id,
tb_order.order_number
from tb_order inner join tb_user
on tb_user.id=tb_order.user_id
where tb_user.id=1
<?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">
<!--利用namespace属性，绑定当前映射文件和mapper接口映射关联-->
<mapper namespace="com.itheima.dao.UserMapper">
<!--配置多表查询映射关系-->
<resultMap id="userMap" type="com.itheima.pojo.User" autoMapping="true">
<!--配置：查询结果和User类的映射-->
<id column="user_id" property="id"></id>
<result column="user_name" property="userName"></result>
<!--配置一对多-->
<collection property="orders"
javaType="java.util.List"
ofType="com.itheima.pojo.Order">
<id column="order_id" property="id"></id>
<result column="order_number" property="orderNumber"></result>
</collection>
<!--配置：查询结果和Order类的映射-->
</resultMap>
<!--根据用户ID查询用户信息和下单信息-->
<select id="findUserById" resultMap="userMap">
select
tb_user.id as user_id,
tb_user.user_name,
tb_user.password,
tb_user.name,
tb_user.sex,
tb_user.age,
tb_order.id as order_id,
tb_order.order_number
from tb_order inner join tb_user
on tb_user.id=tb_order.user_id
where tb_user.id=#{id}
</select>
</mapper>
package com.itheima.dao;
import com.itheima.pojo.User;
public interface UserMapper {
/**
* 根据用户ID查询用户信息及下单信息
* @param id 用户ID
* @return 返回对象
*/
public User findUserById(Integer id);
}
user
package com.itheima.pojo;
import java.io.Serializable;
import java.util.List;
public class User {
private Long id;
private String userName;
private String password;
private String name;
private Integer age;
private Integer sex;
public List<Order> getOrders() {
return orders;
}
public void setOrders(List<Order> orders) {
this.orders = orders;
}
//一个用户多个订单(1:多)
private List<Order> orders;
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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public Integer getSex() {
return sex;
}
public void setSex(Integer sex) {
this.sex = sex;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", userName='" + userName + '\'' +
", password='" + password + '\'' +
", name='" + name + '\'' +
", age=" + age +
", sex=" + sex +
'}';
}
}
order
package com.itheima.pojo;
public class Order {
private Integer id; //订单ID
private String orderNumber;//订单编号
public User getOrderUser() {
return orderUser;
}
public void setOrderUser(User orderUser) {
this.orderUser = orderUser;
}
//下单人
private User orderUser; //用户对象
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", orderNumber='" + orderNumber + '\'' +
'}';
}
}
package com.itheima;
import com.itheima.dao.UserMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.User;
import com.itheima.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserMapperTest {
@Test
public void testFindUserById(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession();
//利用sqlSession，创建一个代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.findUserById(1);
MybatisUtil.closeSqlSession(sqlSession);
System.out.println("用户信息："+user);
List<Order> orderList=user.getOrders();
for(Order order:orderList){
System.out.println(order);
}
//释放资源
}
}
2024-01-19 16:21:25.700 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 16:21:25.703 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4b8729ff]
2024-01-19 16:21:25.703 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 1267149311 to pool.
用户信息：User{id=1, userName=‘zhangsan’, password=‘123456’, name=‘张三’, age=30, sex=1}
Order{id=1, orderNumber=‘20140921001’}
Order{id=3, orderNumber=‘20140921003’}Process finished with exit code 0
@Insert:保存
? Value: sql语句(和xml的配置方式一模一样)
@Update:更新
? Value: sql语句
@Delete:语句
? Value: sql语句
@ Select 语句
? Value: sql语句
@Options:可选配置（获取主键）
? userGenerateKeys:开关值为true表示可以获取主键 相当于select last_insert_id()
? keyProperty:对象属性
? keyColumn:列名
步骤：
? 1.将mybatis全局配置文件mybatis-config.xml中的mapper的路径改为包扫描或者class路径
? 2.编写接口和注解
? 3.测试
package com.itheima.dao;
import com.itheima.pojo.User;
import org.apache.ibatis.annotations.Insert;
public interface UserMapper {
/**
* 添加用户
* @param user 用户对象
* @return 有影响的行数
*/
@Insert("INSERT INTO tb_user(user_name,password,name,age,sex) " +
"VALUES(#{userName},#{password},#{name},#{age},#{sex})")
public int addUser(User user);
}
package com.itheima;
import com.itheima.dao.UserMapper;
import com.itheima.pojo.Order;
import com.itheima.pojo.User;
import com.itheima.utils.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserMapperTest {
@Test
public void testAddUser(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession();
//利用sqlSession，创建一个代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUserName("测试注解");
user.setPassword("123456");
user.setName("注解");
user.setSex(1);
user.setAge(14);
int count=userMapper.addUser(user);
if(count>0){
System.out.println("添加成功");
}
//释放资源
MybatisUtil.closeSqlSession(sqlSession);
}
}
2.3测试结果
2024-01-19 16:53:34.672 [DEBUG] org.apache.ibatis.io.ResolverUtil [main] : Checking to see if class com.itheima.dao.UserMapper matches criteria [is assignable to Object]
2024-01-19 16:53:34.848 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 16:53:35.435 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 771418758.
2024-01-19 16:53:35.437 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2dfaea86]
2024-01-19 16:53:35.444 [DEBUG] com.itheima.dao.UserMapper.addUser [main] : ==> Preparing: INSERT INTO tb_user(user_name,password,name,age,sex) VALUES(?,?,?,?,?)
2024-01-19 16:53:35.584 [DEBUG] com.itheima.dao.UserMapper.addUser [main] : ==> Parameters: 测试注解(String), 123456(String), 注解(String), 14(Integer), 1(Integer)
2024-01-19 16:53:35.604 [DEBUG] com.itheima.dao.UserMapper.addUser [main] : <== Updates: 1
添加成功
2024-01-19 16:53:35.605 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Rolling back JDBC Connection [com.mysql.jdbc.JDBC4Connection@2dfaea86]
2024-01-19 16:53:35.610 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2dfaea86]
2024-01-19 16:53:35.612 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2dfaea86]
2024-01-19 16:53:35.612 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 771418758 to pool.
Process finished with exit code 0
package com.itheima.dao;
import com.itheima.pojo.User;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
public interface UserMapper {
@Update("update tb_user set password=#{password} where user_name=#{username}")
public int updateUser(@Param("username") String name,
@Param("password") String password);
@Select("SELECT user_name,password,name,age,sex from tb_user")
public List<User> findAll();
}
@Test
public void testUpdateUser(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession(true);
//利用sqlSession，创建一个代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
int Count=userMapper.updateUser("蔡徐坤","12345678");
if(Count>0){
System.out.println("更新成功");
}
//释放资源
MybatisUtil.closeSqlSession(sqlSession);
}
2024-01-19 17:11:18.153 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 17:11:18.832 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 944140566.
2024-01-19 17:11:18.840 [DEBUG] com.itheima.dao.UserMapper.updateUser [main] : ==> Preparing: update tb_user set password=? where user_name=?
2024-01-19 17:11:18.934 [DEBUG] com.itheima.dao.UserMapper.updateUser [main] : ==> Parameters: 12345678(String), 蔡徐坤(String)
2024-01-19 17:11:18.943 [DEBUG] com.itheima.dao.UserMapper.updateUser [main] : <== Updates: 1
更新成功
2024-01-19 17:11:18.944 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@38467116]
2024-01-19 17:11:18.945 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 944140566 to pool.
主键回填技术：在向数据表中插入一行数据时，会返回所插入行的主键列表数据，主键列表数据会回填到实体类的属性中（和主键关联的属性）
package com.itheima.dao;
import com.itheima.pojo.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserMapper {
/**
* 添加用户
* @param user 用户对象
* @return 有影响的行数
*/
@Insert("INSERT INTO tb_user(user_name,password,name,age,sex) " +
"VALUES(#{userName},#{password},#{name},#{age},#{sex})")
//useGeneratedKeys 开启主键回填，keyColumn="表中的主键字段名" keyProperty="实体类中的属性名"
@Options(useGeneratedKeys = true,keyColumn ="id",keyProperty = "id")
public int addUserGetPk(User user);
}
@Test
public void testAaddUser(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession(true);
//利用sqlSession，创建一个代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = new User();
user.setUserName("测试注解");
user.setPassword("123456");
user.setName("注解");
user.setSex(1);
user.setAge(14);
int count=userMapper.addUserGetPk(user);
if(count>0){
System.out.println("添加成功");
}
//释放资源
MybatisUtil.closeSqlSession(sqlSession);
}
2024-01-19 17:21:56.939 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 17:21:57.846 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 859654796.
2024-01-19 17:21:57.852 [DEBUG] com.itheima.dao.UserMapper.addUserGetPk [main] : ==> Preparing: INSERT INTO tb_user(user_name,password,name,age,sex) VALUES(?,?,?,?,?)
2024-01-19 17:21:57.953 [DEBUG] com.itheima.dao.UserMapper.addUserGetPk [main] : ==> Parameters: 测试注解(String), 123456(String), 注解(String), 14(Integer), 1(Integer)
2024-01-19 17:21:57.961 [DEBUG] com.itheima.dao.UserMapper.addUserGetPk [main] : <== Updates: 1
添加成功
2024-01-19 17:21:57.965 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@333d4a8c]
2024-01-19 17:21:57.965 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 859654796 to pool.
当查询结果中的列名和 实体类的属性名不一致时：
1.在SQL语句中，添加别名（别名和实体类中的属性类一致）
2.在核心配置文件中，配置驼峰映射
3.使用resultMap标签，设置查询后结果和实体类的映射关系
@Select("SELECT id,user_name,password,name,age,sex from tb_user")
@Results(id = "userMap",value={
@Result(column = "id",property = "id",id = true),
@Result(column = "user_name",property = "userName")
})
public List<User> findAll();
@Test
public void testFindAllUser(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession();
//利用sqlSession，创建一个代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList=userMapper.findAll();
//释放资源
MybatisUtil.closeSqlSession(sqlSession);
for (User user:userList){
System.out.println(user);
}
}
2024-01-19 17:40:13.684 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 17:40:14.339 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 1326393666.
2024-01-19 17:40:14.340 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4f0f2942]
2024-01-19 17:40:14.346 [DEBUG] com.itheima.dao.UserMapper.findAll [main] : ==> Preparing: SELECT id,user_name,password,name,age,sex from tb_user
2024-01-19 17:40:14.566 [DEBUG] com.itheima.dao.UserMapper.findAll [main] : ==> Parameters:
2024-01-19 17:40:14.858 [TRACE] com.itheima.dao.UserMapper.findAll [main] : <== Columns: id, user_name, password, name, age, sex
2024-01-19 17:40:14.859 [TRACE] com.itheima.dao.UserMapper.findAll [main] : <== Row: 1, zhangsan, 123456, 张三, 30, 1
2024-01-19 17:40:14.865 [TRACE] com.itheima.dao.UserMapper.findAll [main] : <== Row: 2, lishi, 123456, 李四, 21, 0
2024-01-19 17:40:14.870 [TRACE] com.itheima.dao.UserMapper.findAll [main] : <== Row: 3, wangwu, 123456, 王五, 22, 1
2024-01-19 17:40:14.872 [TRACE] com.itheima.dao.UserMapper.findAll [main] : <== Row: 4, zhangwei, 123456, 张伟, 20, 1
2024-01-19 17:40:14.873 [TRACE] com.itheima.dao.UserMapper.findAll [main] : <== Row: 5, lina, 123456, 李娜, 28, 0
2024-01-19 17:40:14.875 [TRACE] com.itheima.dao.UserMapper.findAll [main] : <== Row: 6, 蔡徐坤, 12345678, 小菜, 18, 1
2024-01-19 17:40:14.910 [TRACE] com.itheima.dao.UserMapper.findAll [main] : <== Row: 8, 测试注解, 123456, 注解, 14, 1
2024-01-19 17:40:14.938 [DEBUG] com.itheima.dao.UserMapper.findAll [main] : <== Total: 7
2024-01-19 17:40:14.940 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4f0f2942]
2024-01-19 17:40:14.942 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4f0f2942]
2024-01-19 17:40:14.944 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 1326393666 to pool.
User{id=1, userName='zhangsan', password='123456', name='张三', age=30, sex=1}
User{id=2, userName='lishi', password='123456', name='李四', age=21, sex=0}
User{id=3, userName='wangwu', password='123456', name='王五', age=22, sex=1}
User{id=4, userName='zhangwei', password='123456', name='张伟', age=20, sex=1}
User{id=5, userName='lina', password='123456', name='李娜', age=28, sex=0}
User{id=6, userName='蔡徐坤', password='12345678', name='小菜', age=18, sex=1}
User{id=8, userName='测试注解', password='123456', name='注解', age=14, sex=1}
package com.itheima.dao;
import com.itheima.pojo.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface UserMapper {
/**
* 根据给定名字，模糊查询用户数据
* @param name 名字
* @return 存储用户对象的集合
*/
@Select("select id,user_name AS userName,password,name,age,sex" +
" from tb_user" +
" where sex=1 AND name like concat('%',#{name},'%')")
public List<User> findUserByLike(String name);
}
@Test
public void testFindUserByLike(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession();
//利用sqlSession，创建一个代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList=userMapper.findUserByLike("小");
//释放资源
MybatisUtil.closeSqlSession(sqlSession);
for (User user:userList){
System.out.println(user);
}
}
2024-01-19 18:02:51.596 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 18:02:52.614 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 308433917.
2024-01-19 18:02:52.615 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@126253fd]
2024-01-19 18:02:52.622 [DEBUG] com.itheima.dao.UserMapper.findUserByLike [main] : ==> Preparing: select id,user_name AS userName,password,name,age,sex from tb_user where sex=1 AND name like concat('%',?,'%')
2024-01-19 18:02:52.724 [DEBUG] com.itheima.dao.UserMapper.findUserByLike [main] : ==> Parameters: 小(String)
2024-01-19 18:02:52.773 [TRACE] com.itheima.dao.UserMapper.findUserByLike [main] : <== Columns: id, userName, password, name, age, sex
2024-01-19 18:02:52.774 [TRACE] com.itheima.dao.UserMapper.findUserByLike [main] : <== Row: 6, 蔡徐坤, 12345678, 小菜, 18, 1
2024-01-19 18:02:52.780 [DEBUG] com.itheima.dao.UserMapper.findUserByLike [main] : <== Total: 1
2024-01-19 18:02:52.784 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@126253fd]
2024-01-19 18:02:52.787 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@126253fd]
2024-01-19 18:02:52.787 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 308433917 to pool.
User{id=6, userName='蔡徐坤', password='12345678', name='小菜', age=18, sex=1}
Process finished with exit code 0
方式1：
使用@SelectProvider(type=SqlProvider.class,method=“findUserByName”)method是构建SQL的方法
构建SQL的方法的参数要和接口的参数一致，并且多个参数使用@Param命名参数
方式2：
@SelectProvider+SQL类
1.定义接口
2.定义提供的类
SqlProvider类
package com.itheima.dao;
import org.apache.ibatis.annotations.Param;
//拼接SQL语句
public class SqlProvider {
//返回拼接后的SQL语句
public String findUserByName(@Param("name") String name) {
String sql="select id,user_name AS userName,password,name,age,sex from tb_user where sex=1 ";
if(name!=null && !"".equals(name)){
sql+=" AND name like concat('%',#{name},'%')";
}
return sql;
}
}
/**
* 根据给定名字，模糊查询用户数据
* @param name 名字
* @return 存储用户对象的集合
*/
// @Select("select id,user_name AS userName,password,name,age,sex" +
// " from tb_user" +
// " where sex=1 AND name like concat('%',#{name},'%')")
@SelectProvider(type = SqlProvider.class,method = "findUserByName")
public List<User> findUserByLike(@Param("name") String name);
@Test
public void testFindUserByLike(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession();
//利用sqlSession，创建一个代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList=userMapper.findUserByLike("小");
//释放资源
MybatisUtil.closeSqlSession(sqlSession);
for (User user:userList){
System.out.println(user);
}
}
2024-01-19 18:23:31.416 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Opening JDBC Connection
2024-01-19 18:23:32.363 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Created connection 1304589447.
2024-01-19 18:23:32.364 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4dc27487]
2024-01-19 18:23:32.372 [DEBUG] com.itheima.dao.UserMapper.findUserByLike [main] : ==> Preparing: select id,user_name AS userName,password,name,age,sex from tb_user where sex=1 AND name like concat('%',?,'%')
2024-01-19 18:23:32.505 [DEBUG] com.itheima.dao.UserMapper.findUserByLike [main] : ==> Parameters: 小(String)
2024-01-19 18:23:32.665 [TRACE] com.itheima.dao.UserMapper.findUserByLike [main] : <== Columns: id, userName, password, name, age, sex
2024-01-19 18:23:32.666 [TRACE] com.itheima.dao.UserMapper.findUserByLike [main] : <== Row: 6, 蔡徐坤, 12345678, 小菜, 18, 1
2024-01-19 18:23:32.669 [DEBUG] com.itheima.dao.UserMapper.findUserByLike [main] : <== Total: 1
2024-01-19 18:23:32.672 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@4dc27487]
2024-01-19 18:23:32.673 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@4dc27487]
2024-01-19 18:23:32.673 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 1304589447 to pool.
User{id=6, userName='蔡徐坤', password='12345678', name='小菜', age=18, sex=1}
sqlProvider
public class SqlProvider {
public String findUserByName2(@Param("name") String name){
//Mybatis提供了一个SQL类
SQL sql=new SQL();
//利用SQL类中提供的API方法,拼接了SQL语句
sql=sql.SELECT(" id,user_name AS userName,password,name,age,sex").FROM("tb_user").WHERE("sex=1");
if(name!=null){
sql=sql.AND().WHERE("name like concat('%',#{name},'%')");
}
return sql.toString();
}
}
userMapper.java
public interface UserMapper {
@SelectProvider(type = SqlProvider.class,method = "findUserByName2")
public List<User> findUserByLike(@Param("name") String name);
}
@Test
public void testFindUserByLike(){
//使用SqlSessionFactory工厂类，创建SqlSession
SqlSession sqlSession = MybatisUtil.openSession();
//利用sqlSession，创建一个代理对象
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List<User> userList=userMapper.findUserByLike("小");
//释放资源
MybatisUtil.closeSqlSession(sqlSession);
for (User user:userList){
System.out.println(user);
}
}
2024-01-22 11:08:52.107 [DEBUG] com.itheima.dao.UserMapper.findUserByLike [main] : ==> Preparing: SELECT id,user_name AS userName,password,name,age,sex FROM tb_user WHERE (sex=1) AND (name like concat('%',?,'%'))
2024-01-22 11:08:52.195 [DEBUG] com.itheima.dao.UserMapper.findUserByLike [main] : ==> Parameters: 小(String)
2024-01-22 11:08:52.229 [TRACE] com.itheima.dao.UserMapper.findUserByLike [main] : <== Columns: id, userName, password, name, age, sex
2024-01-22 11:08:52.229 [TRACE] com.itheima.dao.UserMapper.findUserByLike [main] : <== Row: 6, 蔡徐坤, 12345678, 小菜, 18, 1
2024-01-22 11:08:52.233 [DEBUG] com.itheima.dao.UserMapper.findUserByLike [main] : <== Total: 1
2024-01-22 11:08:52.235 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@2254127a]
2024-01-22 11:08:52.235 [DEBUG] org.apache.ibatis.transaction.jdbc.JdbcTransaction [main] : Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@2254127a]
2024-01-22 11:08:52.236 [DEBUG] org.apache.ibatis.datasource.pooled.PooledDataSource [main] : Returned connection 575935098 to pool.
User{id=6, userName='蔡徐坤', password='12345678', name='小菜', age=18, sex=1}
Process finished with exit code 0