数据库设计遇到的小坑

开发中设计数据库过程中遇到的小坑

遇到一个需求：有一个预约服务，预约的类型可以多选。

一开始没思路，不知道怎么解决……最后采取了一种叫做字典表的东西来解决

解决方法：

	具体的需求是：4S店的客户可以进行预约功能，预约的种类有很多种（洗车，保养，维修……）。
	解决的方式是创建多一张log表来记录预约的类型

orders预约表

id	phone	name
1	124567894145	kylin
2	14547865	overload

ordertype预约类型表

id	typeName
1	洗车
2	保养
3	维修

ordertypelog表 预约类型记录表

id	ordersId	orderTypeId
1	1	1
2	1	2
3	1	3
4	2	1
5	2	3

通过ordertypelog表就可以知道每一个预约到底是预约了哪几种类型！！！

对于查询的参数未知的SQL语句

• 解释：查询的条件有：门店、状态、服务顾问（可以用这三个条件来查询，但是非必须一定要三个同时拥有）
• 解决方法：运用自己编写的工具类（SqlBuilder类），SqlBuilder中有appendSql()和appendValue()方法，其中appendValue()不单单往sql语句拼接中加问号，同时也往成员变量List value中添加值，最后还有getSql()获取sql语句，以及getValue()获取List(按照参数的顺序的)，这样处理就很简单。
• 例如：

SqlBuilder sqlBuilder1=new SqlBuilder();
        sqlBuilder1.appendSql(" SELECT DISTINCT orders.id orderId, shopaddress.address ,orders.orderTime, " +
                " serveragent.`name` serverAgentName,orders.`status`,orders.`name` username, " +
                " orders.phone,orders.carNumber,orders.createdAt, " +
                " cartype.series,cartype.model " +
                " FROM shopaddress,orders,ordertype,ordertypelog,serveragent,cartype " +
                " WHERE shopaddress.id=orders.shopAddressId " +
                " AND orders.id=ordertypelog.orderId " +
                " AND ordertype.id=ordertypelog.orderTypeId " +
                " AND orders.serverAgentId=serveragent.id " +
                " AND cartype.id=orders.id ");
        //拼接门店信息 2句sql都有的部分 shopAddressId是没有0的
        if (StringUtils.isNotBlank(String.valueOf(orderListVO.getShopAddressId()))&&orderListVO.getShopAddressId()!=null){
            sqlBuilder.appendSql(" AND shopaddress.id= ").appendValue(orderListVO.getShopAddressId());
            sqlBuilder1.appendSql(" AND shopaddress.id= ").appendValue(orderListVO.getShopAddressId());
        }

对于预约类型多选的更新操作

• 解释：正如最上面的那个问题，预约的类型是可以多选的，那如果需要修改预约的类型呢？
• 解决方法：前端传过来一个新的预约类型orderTypeIds[1,2,3,4]数组，后端从数据库中查询出之前的预约类型past[1,3,4]；这个时候就需要解决如何更新操作，我的做法是：用set集合处理，new-past得到需要插入的新的预约类型，past-new得到的是需要删除的预约类型——set集合的差。
• 注意：使用set集合处理的时候，因为集合的差 boolean removeAll()是会真正的改变集合里面的成员，所以特别需要注意引用的问题，即使创建多一个引用指向了，但是实际的内容还是会改变的——因而我这里的处理方式是：在一开始用2个数组来存储最开始的new和past类型的元素，然后再new-past之后获取需要插入的元素，创建2个新的set集合（使用数组来创建，这个时候的数组保留的是最开始的内容，不会跟着new-past而改变），然后再执行past-new获取需要删除的元素

//预约类型part——先进行查询操作
        List<OrderTypeLog>result= queryHelper.queryAll(OrderTypeLog.class,
                " SELECT orderTypeId FROM ordertypelog WHERE orderId=?",orderMessageVO.getOrderId());
        Set<Integer> past=new HashSet<>(); //获取到数据库中的 预约类型集合 转换成HashSet
        for (OrderTypeLog every:result){
            past.add(every.getOrderTypeId());
        }
        Set<Integer> newest=orderMessageVO.getOrderTypeIds();  //新的预约类型
        //用一个数组暂时存放 因为引用的问题，会实际的改到集合内的内容
        Integer []pastArray=new Integer[past.size()];
        past.toArray(pastArray);  //旧的预约类型
        Integer []newestArray=new Integer[newest.size()];
        newest.toArray(newestArray); //新预约类型

        newest.removeAll(past); //A-B 获取需要插入的预约类型
        //todo 可以改成lambda表达式来处理
        System.out.println("-----------------------------------");
        for (Integer id:newest){
            SqlBuilder sqlBuilder=new SqlBuilder();
            sqlBuilder.appendSql(" INSERT INTO ordertypelog(orderId,orderTypeId) VALUES( ");
            sqlBuilder.appendValue(orderMessageVO.getOrderId());
            sqlBuilder.appendSql(" , ");
            sqlBuilder.appendValue(id);
            sqlBuilder.appendSql(" ) ");
            jdbcTemplate.update(sqlBuilder.getSql(),sqlBuilder.getValues());
        }
        //获取需要删除的set集合 B-A 数组转set
        Set<Integer> past1=new HashSet<>(Arrays.asList(pastArray));
        Set<Integer> newest1=new HashSet<>(Arrays.asList(newestArray));
        past1.removeAll(newest1);
        past1.removeAll(orderMessageVO.getOrderTypeIds()); //B-A 需要删除的预约类型
        for (Integer id:past1){
            SqlBuilder sqlBuilder=new SqlBuilder();
            sqlBuilder.appendSql(" DELETE FROM ordertypelog WHERE orderId= ");
            sqlBuilder.appendValue(orderMessageVO.getOrderId());
            sqlBuilder.appendSql(" AND orderTypeId= ");
            sqlBuilder.appendValue(id);
            jdbcTemplate.update(sqlBuilder.getSql(),sqlBuilder.getValues());
            System.out.println(id);
        }