一、题目描述
先根据以下sql创建数据库表:
CREATE TABLE `tb_order` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单id',
`payment` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '实付金额。精确到2位小数;单位:元。如:200.07,表示:200元7分',
`paymentType` int(2) DEFAULT NULL COMMENT '支付类型,1、在线支付,2、货到付款',
`postFee` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '邮费。精确到2位小数;单位:元。如:200.07,表示:200元7分',
`status` int(10) DEFAULT NULL COMMENT '状态:1、未付款,2、已付款,3、未发货,4、已发货,5、交易成功,6、交易关闭',
`createTime` datetime DEFAULT NULL COMMENT '订单创建时间',
`updateTime` datetime DEFAULT NULL COMMENT '订单更新时间',
`paymentTime` datetime DEFAULT NULL COMMENT '付款时间',
`consignTime` datetime DEFAULT NULL COMMENT '发货时间',
`endTime` datetime DEFAULT NULL COMMENT '交易完成时间',
`closeTime` datetime DEFAULT NULL COMMENT '交易关闭时间',
`userId` bigint(20) DEFAULT NULL COMMENT '用户id',
`buyerMessage` varchar(100) COLLATE utf8_bin DEFAULT NULL COMMENT '买家留言',
`buyerNick` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT '买家昵称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
使用以上数据库表,使用接口动态代理的方式实现以下功能:
- 创建pojo
- 实现创建订单,并返回订单id
- 实现根据订单id查询订单
- 实现根据订单创建的时间范围(例如查询3月26日这一天的),查询所有支付类型为在线支付,而且已付款的订单
查询范围可以使用BETWEEN关键词,例如以下sql是查询金额为11和31之间的订单
SELECT * FROM tb_order WHERE payment BETWEEN 10 AND 31
- 实现根据买家留言模糊查询订单
- 实现根据订单id修改订单
- 实现根据订单创建的时间范围(例如查询3月26日这一天的),修改所有支付类型是在线支付的,而且未付款的,把订单状态修改为交易关闭
- 实现根据id删除订单
二、实现
1,创建Order实体类
package homework4.tb_order_dao;
public class Order {
private int id;
private double payment;
private int paymentType;
private double postFee;
private int status;
private String createTime;
private String updateTime = null;
private String paymentTime = null;
private String consignTime = null;
private String endTime = null;
private String closeTime = null;
private int userId;
private String buyerMessage;
private String buyerNick;
public Order() {
}
public Order(int id, double payment, int paymentType, double postFee, int status, String createTime, int userId, String buyerMessage, String buyerNick) {
this.id = id;
this.payment = payment;
this.paymentType = paymentType;
this.postFee = postFee;
this.status = status;
this.createTime = createTime;
this.userId = userId;
this.buyerMessage = buyerMessage;
this.buyerNick = buyerNick;
}
public Order(double payment, int paymentType, double postFee, int status, String createTime, int userId, String buyerMessage, String buyerNick) {
this.payment = payment;
this.paymentType = paymentType;
this.postFee = postFee;
this.status = status;
this.createTime = createTime;
this.userId = userId;
this.buyerMessage = buyerMessage;
this.buyerNick = buyerNick;
}
@Override
public String toString() {
return "Order{" +
"id=" + id +
", payment=" + payment +
", paymentType=" + paymentType +
", postFee=" + postFee +
", status=" + status +
", createTime='" + createTime + '\'' +
", updateTime='" + updateTime + '\'' +
", paymentTime='" + paymentTime + '\'' +
", consignTime='" + consignTime + '\'' +
", endTime='" + endTime + '\'' +
", closeTime='" + closeTime + '\'' +
", userId=" + userId +
", buyerMessage='" + buyerMessage + '\'' +
", buyerNick='" + buyerNick + '\'' +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public double getPayment() {
return payment;
}
public void setPayment(double payment) {
this.payment = payment;
}
public int getPaymentType() {
return paymentType;
}
public void setPaymentType(int paymentType) {
this.paymentType = paymentType;
}
public double getPostFee() {
return postFee;
}
public void setPostFee(double postFee) {
this.postFee = postFee;
}
public int getStatus() {
return status;
}
public void setStatus(int status) {
this.status = status;
}
public String getCreateTime() {
return createTime;
}
public void setCreateTime(String createTime) {
this.createTime = createTime;
}
public String getUpdateTime() {
return updateTime;
}
public void setUpdateTime(String updateTime) {
this.updateTime = updateTime;
}
public String getPaymentTime() {
return paymentTime;
}
public void setPaymentTime(String paymentTime) {
this.paymentTime = paymentTime;
}
public String getConsignTime() {
return consignTime;
}
public void setConsignTime(String consignTime) {
this.consignTime = consignTime;
}
public String getEndTime() {
return endTime;
}
public void setEndTime(String endTime) {
this.endTime = endTime;
}
public String getCloseTime() {
return closeTime;
}
public void setCloseTime(String closeTime) {
this.closeTime = closeTime;
}
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getBuyerMessage() {
return buyerMessage;
}
public void setBuyerMessage(String buyerMessage) {
this.buyerMessage = buyerMessage;
}
public String getBuyerNick() {
return buyerNick;
}
public void setBuyerNick(String buyerNick) {
this.buyerNick = buyerNick;
}
}
2,编写核心配置文件mybatis-config
<?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>
<!--引入外部jdbc.properties-->
<properties resource="jdbc.properties"></properties>
<settings>
<!--开启全局缓存 默认true-->
<setting name="cacheEnabled" value="true"/>
<!--开启全局懒加载 默认false-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--开启 关联属性的懒加载,默认false-->
<setting name="aggressiveLazyLoading" value="true"/>
<!--开启驼峰映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!--设置包的别称-->
<typeAliases>
<typeAlias alias="Order" type="homework4.tb_order_dao.Order"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${mysql.driver}"/>
<property name="url" value="${mysql.url}"/>
<property name="username" value="${mysql.username}"/>
<property name="password" value="${mysql.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--配置xxxMapper.xml的文件位置-->
<mapper resource="Mapper/OrderDao.xml"/>
</mappers>
</configuration>
附:jdbc.properties文件
mysql.driver=com.mysql.jdbc.Driver
mysql.username=root
mysql.password=mima
mysql.url=jdbc:mysql://localhost:3307/\
mybatis?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
3,编写OrderDao接口
package homework4.tb_order_dao;
import java.util.List;
public interface OrderDao {
//创建订单,并返回订单id
public int addOrder(Order order);
//根据订单id查询订单
public Order findById(int id);
//根据订单创建的时间范围,查询所有支付类型为在线支付,而且已付款的订单
public List<Order> find(String day);
//根据买家留言模糊查询订单
public List<Order> findByBuyerMessage(String buyerMessage);
//根据订单id修改订单
public void updateById(Order order);
//根据订单创建的时间范围,修改所有支付类型是在线支付的,而且未付款的,把订单状态修改为交易关闭
public void updateStatus(String day);
//根据id删除订单
public void deleteById(int id);
}
4,编写映射文件OrderDao.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="homework4.tb_order_dao.OrderDao">
<!--新增用户-->
<insert id="addOrder" parameterType="Order" keyProperty="id" useGeneratedKeys="true">
INSERT into tb_order(id,payment,paymentType,postFee,status,createTime,updateTime,
paymentTime,consignTime,endTime,closeTime,userId,buyerMessage,buyerNick)
values (#{id},#{payment},#{paymentType},#{postFee},#{status},#{createTime},#{updateTime},
#{paymentTime},#{consignTime},#{endTime},#{closeTime},#{userId},#{buyerMessage},#{buyerNick})
</insert>
<!--根据订单id查询订单-->
<select id="findById" parameterType="int" resultType="Order">
select * from tb_order where id = #{id}
</select>
<!--根据订单创建的时间范围,查询所有支付类型为在线支付,而且已付款的订单-->
<select id="find" parameterType="String" resultType="Order">
select * from tb_order where createTime like concat('%','${info}','%') and paymentType=1 and (status BETWEEN 2 and 6);
</select>
<!--根据买家留言模糊查询订单-->
<select id="findByBuyerMessage" resultType="Order">
select * from tb_order where buyerMessage like concat('%','${info}','%');
</select>
<!--根据订单id修改订单-->
<update id="updateById" parameterType="Order">
UPDATE tb_order set payment=#{payment},paymentType=#{paymentType},postFee=#{postFee},status=#{status},
createTime=#{createTime},updateTime=#{updateTime},paymentTime=#{paymentTime},
consignTime=#{consignTime},endTime=#{endTime},closeTime=#{closeTime},userId=#{userId},
buyerMessage=#{buyerMessage},buyerNick=#{buyerNick}
where id=#{id}
</update>
<!--根据订单创建的时间范围,修改所有支付类型是在线支付的,而且未付款的,把订单状态修改为交易关闭-->
<update id="updateStatus" parameterType="String">
update tb_order set status=6 where createTime like concat('${info}','%') and paymentType=1 and status=1;
</update>
<!--根据id删除订单-->
<delete id="deleteById" parameterType="int">
delete from tb_order where id=#{id}
</delete>
</mapper>
5,编写测试文件
package homework4.tb_order_dao;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.List;
public class OrderDaoTest {
OrderDao orderDao=null;
SqlSession sqlSession=null;
@Before
public void setUp() throws Exception {
//1,获取核心文件
//这有很多个Resources,我们选择org.apache.ibatis.io下的
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2,创建sqlSessionFactory工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
//3,创建sqlSession对象
sqlSession = sqlSessionFactory.openSession();
//4,获取接口的动态代理对象
orderDao = sqlSession.getMapper(OrderDao.class);
//userDao = sqlSession.getMapper(UserDao.class);
}
@Test
public void test() {
Order order01=new Order(50,1,8.00,1,
"2023-12-01 08:55:04",4,"无","我是菜菜");
Order order02=new Order(4,44.22,1,8.00,1,
"2023-12-01 08:55:04",4,"无","我是菜菜");
orderDao.addOrder(order01);
System.out.println("添加成功,自动生成的订单编号为:"+order01.getId());
Order returnTemp=orderDao.findById(1);
System.out.println("根据订单Id查询成功,查询结果为:");
System.out.println(returnTemp);
List<Order> orders=orderDao.find("2023-12-06");
System.out.println("根据订单创建的时间范围,查询所有支付类型为在线支付,且已付款的订单成功,查询结果为:");
for(Order order : orders){
System.out.println(order);
}
orders=orderDao.findByBuyerMessage("不吃");
System.out.println("模糊查询成功,查询结果为:");
for(Order order : orders){
System.out.println(order);
}
orderDao.updateById(order02);
System.out.println("根据Id更新成功!");
orderDao.updateStatus("2023-12-06");
System.out.println("更新订单状态成功!");
orderDao.deleteById(2);
System.out.println("根据Id删除成功!");
sqlSession.commit();
sqlSession.close();
}
}
6,数据库结果对比、控制台结果输出
修改前数据库中数据:
修改后数据库中数据:
控制台结果: