org.springframework.jdbc.core.RowMapper

public clreplaced UserRowMapperLambda {

    public static final RowMapper<UserModel> userLoginRowMapperLambda = (rs, rownum) -> {
        UserModel userModel = new UserModel();
        userModel.setId(rs.getInt(id));
        userModel.setName(rs.getString(name));
        userModel.setEmail(rs.getString(email));
        userModel.setRole(UserRole.valueOf(rs.getString(role)));
        return userModel;
    };

    public static final RowMapper<UserModel> userDetailRowMapperLambda = (rs, rownum) -> {
        UserModel userModel = new UserModel();
        userModel.setId(rs.getInt(id));
        userModel.setName(rs.getString(name));
        userModel.setMobile(rs.getString(mobile));
        userModel.setEmail(rs.getString(email));
        userModel.setRole(UserRole.valueOf(rs.getString(role)));
        return userModel;
    };

    public static final RowMapper<UserModel> userIdRowMapperLambda = (rs, rownum) -> {
        UserModel userModel = new UserModel();
        userModel.setId(rs.getInt(id));
        return userModel;
    };

    public static final RowMapper<UserModel> userRoleRowMapperLambda = (rs, rownum) -> {
        UserModel userModel = new UserModel();
        userModel.setRole(UserRole.valueOf(rs.getString(role)));
        return userModel;
    };

    public static final RowMapper<UserPlaceModel> userPlaceRowMapperLambda = (rs, rownum) -> {
        UserPlaceModel userPlaceModel = new UserPlaceModel();
        try {
            UserModel userModel = new UserModel();
            userModel.setId(rs.getInt(id));
            userModel.setName(rs.getString(name));
            userModel.setEmail(rs.getString(email));
            userModel.setRole(Enums.UserRole.valueOf(rs.getString(role)));
            userPlaceModel.setUserModel(userModel);
        } catch (Exception e) {
        }
        try {
            PlaceModel placeModel = new PlaceModel();
            placeModel.setId(rs.getInt(Column.UserPlaceColumn.placeId));
            placeModel.setName(rs.getString(Column.placeName));
            placeModel.setIconUrl(rs.getString(iconUrl));
            placeModel.setAddress(rs.getString(Column.placeAddress));
            userPlaceModel.setPlaceModel(placeModel);
        } catch (Exception e) {
        }
        return userPlaceModel;
    };

    public static final RowMapper<SellerLoginResponse> userShopDetailRowMapperLambda = (rs, rownum) -> {
        SellerLoginResponse sellerLoginResponse = new SellerLoginResponse();
        UserModel userModel = new UserModel();
        userModel.setId(rs.getInt(Column.UserColumn.id));
        userModel.setName(rs.getString(Column.UserColumn.name));
        userModel.setEmail(rs.getString(Column.UserColumn.email));
        userModel.setRole(Enums.UserRole.valueOf(rs.getString(Column.UserColumn.role)));
        sellerLoginResponse.setUserModel(userModel);
        ShopModel shopModel = new ShopModel();
        shopModel.setId(rs.getInt(shopId));
        shopModel.setName(rs.getString(Column.shopName));
        shopModel.setMobile(rs.getString(Column.shopMobile));
        shopModel.setPhotoUrl(rs.getString(Column.ShopColumn.photoUrl));
        try {
            shopModel.setCoverUrls(new ObjectMapper().readValue(rs.getString(Column.ShopColumn.coverUrls), List.clreplaced));
        } catch (JsonProcessingException e) {
            shopModel.setCoverUrls(new ArrayList<>());
        }
        shopModel.setOpeningTime(rs.getTime(Column.ShopColumn.openingTime));
        shopModel.setClosingTime(rs.getTime(Column.ShopColumn.closingTime));
        shopModel.setPlaceModel(null);
        sellerLoginResponse.setShopModel(shopModel);
        ConfigurationModel configurationModel = new ConfigurationModel();
        configurationModel.setShopModel(null);
        configurationModel.setMerchantId(rs.getString(Column.ConfigurationColumn.merchantId));
        configurationModel.setDeliveryPrice(rs.getDouble(Column.ConfigurationColumn.deliveryPrice));
        configurationModel.setIsOrderTaken(rs.getInt(Column.ConfigurationColumn.isOrderTaken));
        configurationModel.setIsDeliveryAvailable(rs.getInt(Column.ConfigurationColumn.isDeliveryAvailable));
        sellerLoginResponse.setConfigurationModel(configurationModel);
        RatingModel ratingModel = new RatingModel();
        ratingModel.setShopModel(null);
        ratingModel.setRating(rs.getDouble(Column.RatingColumn.rating));
        ratingModel.setUserCount(rs.getInt(Column.RatingColumn.userCount));
        sellerLoginResponse.setRatingModel(ratingModel);
        return sellerLoginResponse;
    };
}

public clreplaced ShopRowMapperLambda {

    public static final RowMapper<ShopConfigurationModel> shopRowMapperLambda = (rs, rownum) -> {
        ShopConfigurationModel shopConfigurationModel = new ShopConfigurationModel();
        ShopModel shopModel = new ShopModel();
        shopModel.setId(rs.getInt(id));
        shopModel.setName(rs.getString(name));
        shopModel.setPhotoUrl(rs.getString(photoUrl));
        try {
            shopModel.setCoverUrls(new ObjectMapper().readValue(rs.getString(coverUrls), List.clreplaced));
        } catch (JsonProcessingException e) {
            System.err.println(e.getClreplaced().getName() + ": " + e.getMessage());
            shopModel.setCoverUrls(new ArrayList<>());
        }
        shopModel.setPlaceModel(null);
        shopModel.setMobile(rs.getString(mobile));
        shopModel.setOpeningTime(rs.getTime(openingTime));
        shopModel.setClosingTime(rs.getTime(closingTime));
        ConfigurationModel configurationModel = new ConfigurationModel();
        configurationModel.setShopModel(null);
        configurationModel.setDeliveryPrice(rs.getDouble(deliveryPrice));
        configurationModel.setIsDeliveryAvailable(rs.getInt(isDeliveryAvailable));
        configurationModel.setIsOrderTaken(rs.getInt(isOrderTaken));
        configurationModel.setMerchantId(rs.getString(merchantId));
        RatingModel ratingModel = new RatingModel();
        ratingModel.setShopModel(null);
        ratingModel.setRating(rs.getDouble(rating));
        ratingModel.setUserCount(rs.getInt(userCount));
        shopConfigurationModel.setShopModel(shopModel);
        shopConfigurationModel.setRatingModel(ratingModel);
        shopConfigurationModel.setConfigurationModel(configurationModel);
        return shopConfigurationModel;
    };
}

public clreplaced PlaceRowMapperLambda {

    public static final RowMapper<PlaceModel> placeRowMapperLambda = (rs, rownum) -> {
        PlaceModel place = new PlaceModel();
        place.setId(rs.getInt(id));
        place.setName(rs.getString(name));
        place.setIconUrl(rs.getString(iconUrl));
        place.setAddress(rs.getString(address));
        return place;
    };
}

public clreplaced OrderRowMapperLambda {

    public static final RowMapper<OrderItemListModel> OrderByUserIdRowMapperLambda = (rs, rownum) -> {
        OrderItemListModel orderItemListModel = new OrderItemListModel();
        TransactionModel transactionModel = new TransactionModel();
        transactionModel.setTransactionId(rs.getString(Column.TransactionColumn.transactionId));
        transactionModel.setPaymentMode(rs.getString(Column.TransactionColumn.paymentMode));
        OrderModel orderModel = new OrderModel();
        orderModel.setId(rs.getInt(id));
        orderModel.setDate(rs.getTimestamp(date));
        orderModel.setPrice(rs.getDouble(price));
        orderModel.setDeliveryPrice(rs.getDouble(deliveryPrice));
        orderModel.setDeliveryLocation(rs.getString(deliveryLocation));
        orderModel.setCookingInfo(rs.getString(cookingInfo));
        orderModel.setRating(rs.getDouble(rating));
        orderModel.setFeedback(rs.getString(feedback));
        orderModel.setSecretKey(rs.getString(secretKey));
        orderModel.setUserModel(null);
        ShopModel shopModel = new ShopModel();
        shopModel.setName(rs.getString(Column.shopName));
        shopModel.setPhotoUrl(rs.getString(Column.ShopColumn.photoUrl));
        shopModel.setMobile(rs.getString(Column.shopMobile));
        shopModel.setPlaceModel(null);
        orderModel.setShopModel(shopModel);
        transactionModel.setOrderModel(orderModel);
        ArrayList<OrderItemModel> orderItemListModelList = new ArrayList<>();
        ArrayList<OrderStatusModel> orderStatusModelList = new ArrayList<>();
        String[] itemNameList = rs.getString(Column.itemName).split(",");
        String[] itemPriceList = rs.getString(Column.itemPrice).split(",");
        String[] isVegList = rs.getString(Column.ItemColumn.isVeg).split(",");
        String[] quanreplacedyList = rs.getString(Column.OrderItemColumn.quanreplacedy).split(",");
        String[] orderItemPriceList = rs.getString(Column.orderItemPrice).split(",");
        String[] orderStatusList = rs.getString(Column.OrderColumn.status).split(",");
        String[] updatedTimeList = rs.getString(Column.OrderStatusColumn.updatedTime).split(",");
        for (int i = 0; i < itemNameList.length; i++) {
            OrderItemModel orderItemModel = new OrderItemModel();
            orderItemModel.setOrderModel(null);
            ItemModel itemModel = new ItemModel();
            itemModel.setName(itemNameList[i]);
            itemModel.setPrice(Double.valueOf(itemPriceList[i]));
            itemModel.setIsVeg(Integer.valueOf(isVegList[i]));
            itemModel.setShopModel(null);
            orderItemModel.sereplacedemModel(itemModel);
            orderItemModel.setQuanreplacedy(Integer.valueOf(quanreplacedyList[i]));
            orderItemModel.setPrice(Double.valueOf(orderItemPriceList[i]));
            orderItemListModelList.add(orderItemModel);
        }
        for (int i = 0; i < orderStatusList.length; i++) {
            OrderStatusModel orderStatusModel = new OrderStatusModel();
            orderStatusModel.setOrderStatus(Enums.OrderStatus.valueOf(orderStatusList[i]));
            DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            try {
                orderStatusModel.setUpdatedTime(new Timestamp(formatter.parse(updatedTimeList[i]).getTime()));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            orderStatusModelList.add(orderStatusModel);
        }
        orderItemListModel.setTransactionModel(transactionModel);
        orderItemListModel.setOrderItemsList(orderItemListModelList);
        orderItemListModel.setOrderStatusModel(orderStatusModelList);
        return orderItemListModel;
    };

    public static final RowMapper<OrderItemListModel> OrderBySearchQueryRowMapperLambda = (rs, rownum) -> {
        OrderItemListModel orderItemListModel = new OrderItemListModel();
        TransactionModel transactionModel = new TransactionModel();
        transactionModel.setTransactionId(rs.getString(Column.TransactionColumn.transactionId));
        transactionModel.setPaymentMode(rs.getString(Column.TransactionColumn.paymentMode));
        OrderModel orderModel = new OrderModel();
        orderModel.setId(rs.getInt(id));
        orderModel.setDate(rs.getTimestamp(date));
        orderModel.setPrice(rs.getDouble(price));
        orderModel.setDeliveryPrice(rs.getDouble(deliveryPrice));
        orderModel.setDeliveryLocation(rs.getString(deliveryLocation));
        orderModel.setCookingInfo(rs.getString(cookingInfo));
        orderModel.setRating(rs.getDouble(rating));
        orderModel.setFeedback(rs.getString(feedback));
        orderModel.setSecretKey(rs.getString(secretKey));
        orderModel.setShopModel(null);
        UserModel userModel = new UserModel();
        userModel.setName(rs.getString(Column.userName));
        userModel.setMobile(rs.getString(Column.userMobile));
        orderModel.setUserModel(userModel);
        transactionModel.setOrderModel(orderModel);
        ArrayList<OrderItemModel> orderItemListModelList = new ArrayList<>();
        ArrayList<OrderStatusModel> orderStatusModelList = new ArrayList<>();
        String[] itemNameList = rs.getString(Column.itemName).split(",");
        String[] itemPriceList = rs.getString(Column.itemPrice).split(",");
        String[] isVegList = rs.getString(Column.ItemColumn.isVeg).split(",");
        String[] quanreplacedyList = rs.getString(Column.OrderItemColumn.quanreplacedy).split(",");
        String[] orderItemPriceList = rs.getString(Column.orderItemPrice).split(",");
        String[] orderStatusList = rs.getString(Column.OrderColumn.status).split(",");
        String[] updatedTimeList = rs.getString(Column.OrderStatusColumn.updatedTime).split(",");
        for (int i = 0; i < itemNameList.length; i++) {
            OrderItemModel orderItemModel = new OrderItemModel();
            orderItemModel.setOrderModel(null);
            ItemModel itemModel = new ItemModel();
            itemModel.setName(itemNameList[i]);
            itemModel.setPrice(Double.valueOf(itemPriceList[i]));
            itemModel.setIsVeg(Integer.valueOf(isVegList[i]));
            itemModel.setShopModel(null);
            orderItemModel.sereplacedemModel(itemModel);
            orderItemModel.setQuanreplacedy(Integer.valueOf(quanreplacedyList[i]));
            orderItemModel.setPrice(Double.valueOf(orderItemPriceList[i]));
            orderItemListModelList.add(orderItemModel);
        }
        for (int i = 0; i < orderStatusList.length; i++) {
            OrderStatusModel orderStatusModel = new OrderStatusModel();
            orderStatusModel.setOrderStatus(Enums.OrderStatus.valueOf(orderStatusList[i]));
            DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            try {
                orderStatusModel.setUpdatedTime(new Timestamp(formatter.parse(updatedTimeList[i]).getTime()));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            orderStatusModelList.add(orderStatusModel);
        }
        orderItemListModel.setTransactionModel(transactionModel);
        orderItemListModel.setOrderItemsList(orderItemListModelList);
        orderItemListModel.setOrderStatusModel(orderStatusModelList);
        return orderItemListModel;
    };

    public static final RowMapper<OrderItemListModel> OrderByIdRowMapper = (rs, rownum) -> {
        OrderItemListModel orderItemListModel = new OrderItemListModel();
        TransactionModel transactionModel = new TransactionModel();
        transactionModel.setTransactionId(rs.getString(Column.TransactionColumn.transactionId));
        transactionModel.setPaymentMode(rs.getString(Column.TransactionColumn.paymentMode));
        OrderModel orderModel = new OrderModel();
        orderModel.setId(rs.getInt(id));
        orderModel.setDate(rs.getTimestamp(date));
        orderModel.setPrice(rs.getDouble(price));
        orderModel.setDeliveryPrice(rs.getDouble(deliveryPrice));
        orderModel.setDeliveryLocation(rs.getString(deliveryLocation));
        orderModel.setCookingInfo(rs.getString(cookingInfo));
        orderModel.setRating(rs.getDouble(rating));
        orderModel.setFeedback(rs.getString(feedback));
        orderModel.setSecretKey(rs.getString(secretKey));
        ShopModel shopModel = new ShopModel();
        shopModel.setName(rs.getString(Column.shopName));
        shopModel.setId(rs.getInt(Column.shopId));
        shopModel.setPhotoUrl(rs.getString(Column.ShopColumn.photoUrl));
        shopModel.setMobile(rs.getString(Column.shopMobile));
        shopModel.setPlaceModel(null);
        orderModel.setShopModel(shopModel);
        UserModel userModel = new UserModel();
        userModel.setName(rs.getString(Column.userName));
        userModel.setMobile(rs.getString(Column.userMobile));
        try {
            userModel.setNotificationToken(new ObjectMapper().readValue(rs.getString(Column.UserColumn.notifToken), List.clreplaced));
        } catch (JsonProcessingException e) {
            System.err.println(e.getClreplaced().getName() + ": " + e.getMessage());
            userModel.setNotificationToken(new ArrayList<String>());
        }
        orderModel.setUserModel(userModel);
        transactionModel.setOrderModel(orderModel);
        ArrayList<OrderItemModel> orderItemListModelList = new ArrayList<>();
        ArrayList<OrderStatusModel> orderStatusModelList = new ArrayList<>();
        String[] itemNameList = rs.getString(Column.itemName).split(",");
        String[] itemPriceList = rs.getString(Column.itemPrice).split(",");
        String[] isVegList = rs.getString(Column.ItemColumn.isVeg).split(",");
        String[] quanreplacedyList = rs.getString(Column.OrderItemColumn.quanreplacedy).split(",");
        String[] orderItemPriceList = rs.getString(Column.orderItemPrice).split(",");
        String[] orderStatusList = rs.getString(Column.OrderColumn.status).split(",");
        String[] updatedTimeList = rs.getString(Column.OrderStatusColumn.updatedTime).split(",");
        for (int i = 0; i < itemNameList.length; i++) {
            OrderItemModel orderItemModel = new OrderItemModel();
            orderItemModel.setOrderModel(null);
            ItemModel itemModel = new ItemModel();
            itemModel.setName(itemNameList[i]);
            itemModel.setPrice(Double.valueOf(itemPriceList[i]));
            itemModel.setIsVeg(Integer.valueOf(isVegList[i]));
            itemModel.setShopModel(null);
            orderItemModel.sereplacedemModel(itemModel);
            orderItemModel.setQuanreplacedy(Integer.valueOf(quanreplacedyList[i]));
            orderItemModel.setPrice(Double.valueOf(orderItemPriceList[i]));
            orderItemListModelList.add(orderItemModel);
        }
        for (int i = 0; i < orderStatusList.length; i++) {
            OrderStatusModel orderStatusModel = new OrderStatusModel();
            orderStatusModel.setOrderStatus(Enums.OrderStatus.valueOf(orderStatusList[i]));
            DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            try {
                orderStatusModel.setUpdatedTime(new Timestamp(formatter.parse(updatedTimeList[i]).getTime()));
            } catch (ParseException e) {
                e.printStackTrace();
            }
            orderStatusModelList.add(orderStatusModel);
        }
        orderItemListModel.setTransactionModel(transactionModel);
        orderItemListModel.setOrderItemsList(orderItemListModelList);
        orderItemListModel.setOrderStatusModel(orderStatusModelList);
        return orderItemListModel;
    };

    public static final RowMapper<OrderModel> orderPriceRowMapperLambda = (rs, rownum) -> {
        OrderModel orderModel = new OrderModel();
        orderModel.setId(rs.getInt(id));
        orderModel.setUserModel(null);
        orderModel.setShopModel(null);
        orderModel.setPrice(rs.getDouble(price));
        return orderModel;
    };

    public static final RowMapper<OrderModel> orderByStatusRowMapperLambda = (rs, rownum) -> {
        OrderModel orderModel = new OrderModel();
        orderModel.setId(rs.getInt(id));
        orderModel.setUserModel(null);
        orderModel.setShopModel(null);
        orderModel.setDate(rs.getTimestamp(date));
        return orderModel;
    };
}

public clreplaced ItemRowMapperLambda {

    public static final RowMapper<ItemModel> itemDetailRowMapperLambda = (rs, rownum) -> {
        ItemModel item = new ItemModel();
        item.setId(rs.getInt(id));
        item.setName(rs.getString(name));
        item.setPrice(rs.getDouble(price));
        item.setPhotoUrl(rs.getString(photoUrl));
        item.setCategory(rs.getString(category));
        item.setShopModel(null);
        item.setIsVeg(rs.getInt(isVeg));
        item.setIsAvailable(rs.getInt(isAvailable));
        return item;
    };

    public static final RowMapper<ItemModel> itemRowMapperLambda = (rs, rownum) -> {
        ItemModel item = new ItemModel();
        item.setId(rs.getInt(id));
        item.setName(rs.getString(name));
        item.setPrice(rs.getDouble(price));
        item.setPhotoUrl(rs.getString(photoUrl));
        item.setCategory(rs.getString(category));
        ShopModel shopModel = new ShopModel();
        shopModel.setId(rs.getInt(shopId));
        shopModel.setName(rs.getString(Column.shopName));
        item.setShopModel(shopModel);
        item.setIsVeg(rs.getInt(isVeg));
        item.setIsAvailable(rs.getInt(isAvailable));
        return item;
    };
}

public <T> RowMapper<T> getRowMapper(Clreplaced<T> mappedClreplaced) {
    RowMapper<T> rowMapper;
    if (mappedClreplaced == null || Map.clreplaced.isreplacedignableFrom(mappedClreplaced)) {
        rowMapper = (RowMapper<T>) new ColumnMapRowMapper();
    } else {
        rowMapper = ClreplacedUtils.isSimpleValueType(mappedClreplaced) ? SingleColumnRowMapper.newInstance(mappedClreplaced) : BeanPropertyRowMapper.newInstance(mappedClreplaced);
    }
    return rowMapper;
}

/**
 * jdbc存储token的实现
 * Created by wangfan on 2018-12-28 下午 1:00.
 */
public clreplaced JdbcTokenStore extends TokenStoreAbstract {

    private final JdbcTemplate jdbcTemplate;

    private RowMapper<Token> rowMapper = new TokenRowMapper();

    // sql
    private static final String UPDATE_FIELDS = "user_id, access_token, refresh_token, expire_time, refresh_token_expire_time, roles, permissions";

    private static final String BASE_SELECT = "select token_id, " + UPDATE_FIELDS + " from oauth_token";

    // 查询用户的某个token
    private static final String SQL_SELECT_BY_TOKEN = BASE_SELECT + " where user_id = ? and access_token = ?";

    // 查询某个用户的全部token
    private static final String SQL_SELECT_BY_USER_ID = BASE_SELECT + " where user_id = ? order by create_time";

    // 插入token
    private static final String SQL_INSERT = "insert into oauth_token (" + UPDATE_FIELDS + ") values (?, ?, ?, ?, ?, ?, ?)";

    // 删除某个用户指定token
    private static final String SQL_DELETE = "delete from oauth_token where user_id = ? and access_token = ?";

    // 删除某个用户全部token
    private static final String SQL_DELETE_BY_USER_ID = "delete from oauth_token where user_id = ?";

    // 修改某个用户的角色
    private static final String SQL_UPDATE_ROLES = "update oauth_token set roles = ? where user_id = ?";

    // 修改某个用户的权限
    private static final String SQL_UPDATE_PERMS = "update oauth_token set permissions = ? where user_id = ?";

    // 查询某个用户的refresh_token
    private static final String SQL_SELECT_REFRESH_TOKEN = BASE_SELECT + " where user_id = ? and refresh_token= ?";

    // 查询tokenKey
    private static final String SQL_SELECT_KEY = "select token_key from oauth_token_key";

    // 插入tokenKey
    private static final String SQL_INSERT_KEY = "insert into oauth_token_key (token_key) values (?)";

    public JdbcTokenStore(DataSource dataSource) {
        replacedert.notNull(dataSource, "DataSource required");
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public String getTokenKey() {
        if (tokenKey == null) {
            try {
                tokenKey = jdbcTemplate.queryForObject(SQL_SELECT_KEY, String.clreplaced);
            } catch (EmptyResultDataAccessException e) {
                logger.debug("JwtPermission", e.getCause());
            }
            if (tokenKey == null || tokenKey.trim().isEmpty()) {
                tokenKey = TokenUtil.genHexKey();
                jdbcTemplate.update(SQL_INSERT_KEY, tokenKey);
            }
        }
        return tokenKey;
    }

    @Override
    public int storeToken(Token token) {
        List<Object> objects = getFieldsForUpdate(token);
        int rs = jdbcTemplate.update(SQL_INSERT, JacksonUtil.objectListToArray(objects));
        // 限制用户的最大token数量
        if (getMaxToken() != -1) {
            List<Token> userTokens = findTokensByUserId(token.getUserId());
            if (userTokens.size() > getMaxToken()) {
                for (int i = 0; i < userTokens.size() - getMaxToken(); i++) {
                    removeToken(token.getUserId(), userTokens.get(i).getAccessToken());
                }
            }
        }
        return rs;
    }

    @Override
    public Token findToken(String userId, String access_token) {
        try {
            return jdbcTemplate.queryForObject(SQL_SELECT_BY_TOKEN, rowMapper, userId, access_token);
        } catch (EmptyResultDataAccessException e) {
            logger.debug("JwtPermission", e.getCause());
        }
        return null;
    }

    @Override
    public List<Token> findTokensByUserId(String userId) {
        try {
            return jdbcTemplate.query(SQL_SELECT_BY_USER_ID, rowMapper, userId);
        } catch (EmptyResultDataAccessException e) {
            logger.debug("JwtPermission", e.getCause());
        }
        return null;
    }

    @Override
    public Token findRefreshToken(String userId, String refresh_token) {
        try {
            List<Token> list = jdbcTemplate.query(SQL_SELECT_REFRESH_TOKEN, rowMapper, userId, refresh_token);
            if (list.size() > 0)
                return list.get(0);
        } catch (EmptyResultDataAccessException e) {
            logger.debug("JwtPermission", e.getCause());
        }
        return null;
    }

    @Override
    public int removeToken(String userId, String access_token) {
        return jdbcTemplate.update(SQL_DELETE, userId, access_token);
    }

    @Override
    public int removeTokensByUserId(String userId) {
        return jdbcTemplate.update(SQL_DELETE_BY_USER_ID, userId);
    }

    @Override
    public int updateRolesByUserId(String userId, String[] roles) {
        String rolesJson = JacksonUtil.toJSONString(roles);
        return jdbcTemplate.update(SQL_UPDATE_ROLES, rolesJson, userId);
    }

    @Override
    public int updatePermissionsByUserId(String userId, String[] permissions) {
        String permJson = JacksonUtil.toJSONString(permissions);
        return jdbcTemplate.update(SQL_UPDATE_PERMS, permJson, userId);
    }

    @Override
    public String[] findRolesByUserId(String userId, Token token) {
        // 判断是否自定义查询
        if (getFindRolesSql() == null || getFindRolesSql().trim().isEmpty()) {
            return token.getRoles();
        }
        try {
            List<String> roleList = jdbcTemplate.query(getFindRolesSql(), new RowMapper<String>() {

                @Override
                public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getString(1);
                }
            }, userId);
            return JacksonUtil.stringListToArray(roleList);
        } catch (EmptyResultDataAccessException e) {
            logger.debug("JwtPermission", e.getCause());
        }
        return null;
    }

    @Override
    public String[] findPermissionsByUserId(String userId, Token token) {
        // 判断是否自定义查询
        if (getFindPermissionsSql() == null || getFindPermissionsSql().trim().isEmpty()) {
            return token.getPermissions();
        }
        try {
            List<String> permList = jdbcTemplate.query(getFindPermissionsSql(), new RowMapper<String>() {

                @Override
                public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getString(1);
                }
            }, userId);
            return JacksonUtil.stringListToArray(permList);
        } catch (EmptyResultDataAccessException e) {
            logger.debug("JwtPermission", e.getCause());
        }
        return null;
    }

    /**
     * 插入、修改操作的sql参数
     */
    private List<Object> getFieldsForUpdate(Token token) {
        List<Object> objects = new ArrayList<Object>();
        // userId
        objects.add(token.getUserId());
        // token
        objects.add(token.getAccessToken());
        // refresh_token
        objects.add(token.getRefreshToken());
        // expire_time
        objects.add(token.getExpireTime());
        // refresh_expire_time
        objects.add(token.getRefreshTokenExpireTime());
        // roles
        objects.add(JacksonUtil.toJSONString(token.getRoles()));
        // permissions
        objects.add(JacksonUtil.toJSONString(token.getPermissions()));
        return objects;
    }

    /**
     * Token结果集映射
     */
    private static clreplaced TokenRowMapper implements RowMapper<Token> {

        @Override
        public Token mapRow(ResultSet rs, int rowNum) throws SQLException {
            int token_id = rs.getInt("token_id");
            String user_id = rs.getString("user_id");
            String access_token = rs.getString("access_token");
            String refresh_token = rs.getString("refresh_token");
            Date expire_time = timestampToDate(rs.getTimestamp("expire_time"));
            Date refresh_token_expire_time = timestampToDate(rs.getTimestamp("refresh_token_expire_time"));
            String roles = rs.getString("roles");
            String permissions = rs.getString("permissions");
            Token token = new Token();
            token.setTokenId(token_id);
            token.setUserId(user_id);
            token.setAccessToken(access_token);
            token.setRefreshToken(refresh_token);
            token.setExpireTime(expire_time);
            token.setRefreshTokenExpireTime(refresh_token_expire_time);
            token.setRoles(JacksonUtil.stringListToArray(JacksonUtil.parseArray(roles, String.clreplaced)));
            token.setPermissions(JacksonUtil.stringListToArray(JacksonUtil.parseArray(permissions, String.clreplaced)));
            return token;
        }

        private Date timestampToDate(java.sql.Timestamp timestamp) {
            if (timestamp != null)
                return new Date(timestamp.getTime());
            return null;
        }
    }
}

/**
 * Central execution method. All un-named parameter execution goes through this method.
 * @param params parameters, similar to JDO query parameters.
 * Primitive parameters must be represented by their Object wrapper type.
 * The ordering of parameters is significant.
 * @param context contextual information preplaceded to the {@code mapRow}
 * callback method. The JDBC operation itself doesn't rely on this parameter,
 * but it can be useful for creating the objects of the result list.
 * @return a List of objects, one per row of the ResultSet. Normally all these
 * will be of the same clreplaced, although it is possible to use different types.
 */
public List<T> execute(@Nullable Object[] params, @Nullable Map<?, ?> context) throws DataAccessException {
    validateParameters(params);
    RowMapper<T> rowMapper = newRowMapper(params, context);
    return getJdbcTemplate().query(newPreparedStatementCreator(params), rowMapper);
}

/**
 * A concrete variant of {@link SqlQuery} which can be configured
 * with a {@link RowMapper}.
 *
 * @author Thomas Risberg
 * @author Juergen Hoeller
 * @since 3.0
 * @param <T> the result type
 * @see #setRowMapper
 * @see #setRowMapperClreplaced
 */
public clreplaced GenericSqlQuery<T> extends SqlQuery<T> {

    @Nullable
    private RowMapper<T> rowMapper;

    @SuppressWarnings("rawtypes")
    @Nullable
    private Clreplaced<? extends RowMapper> rowMapperClreplaced;

    /**
     * Set a specific {@link RowMapper} instance to use for this query.
     * @since 4.3.2
     */
    public void setRowMapper(RowMapper<T> rowMapper) {
        this.rowMapper = rowMapper;
    }

    /**
     * Set a {@link RowMapper} clreplaced for this query, creating a fresh
     * {@link RowMapper} instance per execution.
     */
    @SuppressWarnings("rawtypes")
    public void setRowMapperClreplaced(Clreplaced<? extends RowMapper> rowMapperClreplaced) {
        this.rowMapperClreplaced = rowMapperClreplaced;
    }

    @Override
    public void afterPropertiesSet() {
        super.afterPropertiesSet();
        replacedert.isTrue(this.rowMapper != null || this.rowMapperClreplaced != null, "'rowMapper' or 'rowMapperClreplaced' is required");
    }

    @Override
    @SuppressWarnings("unchecked")
    protected RowMapper<T> newRowMapper(@Nullable Object[] parameters, @Nullable Map<?, ?> context) {
        if (this.rowMapper != null) {
            return this.rowMapper;
        } else {
            replacedert.state(this.rowMapperClreplaced != null, "No RowMapper set");
            return BeanUtils.instantiateClreplaced(this.rowMapperClreplaced);
        }
    }
}

public void jdbcTemplateRowMapper() {
    RowMapper<Author> authorRowMapper = (rs, rowNum) -> Author.builder().username(rs.getString("username")).fullName(rs.getString("fullname")).build();
    log.info("{}", jdbcTemplate.query("SELECT username, fullname FROM author", authorRowMapper));
}

/**
 * An abstract results extractor for row mapping operations that map multiple
 * rows to a single root object. This is useful when joining a one-to-many
 * relationship where there can be multiple child rows returned per parent root.
 * <p>
 * It's replacedumed that the root type R table has a primary key (id) of type K and
 * that the child type C table has a foreign key of type K referencing the root
 * table's primary key.
 * <p>
 * For example, consider the relationship: "a Customer has one-to-many
 * Addresses". When joining the Customer table with the Address table to build a
 * Customer object, multiple rows would be returned for a Customer if it has
 * more than one Address. This extractor is useful in that case.
 *
 * @author Thomas Risberg
 * @author Keith Donald
 * @author Oliver Gierke
 * @since 1.0
 */
public abstract clreplaced OneToManyResultSetExtractor<R, C, K> implements ResultSetExtractor<List<R>> {

    public enum ExpectedResults {

        ANY, ONE_AND_ONLY_ONE, ONE_OR_NONE, AT_LEAST_ONE
    }

    protected final ExpectedResults expectedResults;

    protected final RowMapper<R> rootMapper;

    protected final RowMapper<C> childMapper;

    /**
     * Creates a new {@link OneToManyResultSetExtractor} from the given
     * {@link RowMapper}s.
     *
     * @param rootMapper  {@link RowMapper} to map the root enreplacedy, must not be
     *                    {@literal null}.
     * @param childMapper {@link RowMapper} to map the root enreplacedies, must not be
     *                    {@literal null}.
     */
    public OneToManyResultSetExtractor(RowMapper<R> rootMapper, RowMapper<C> childMapper) {
        this(rootMapper, childMapper, null);
    }

    /**
     * Creates a new {@link OneToManyResultSetExtractor} from the given
     * {@link RowMapper}s and {@link ExpectedResults}.
     *
     * @param rootMapper      {@link RowMapper} to map the root enreplacedy, must not be
     *                        {@literal null}.
     * @param childMapper     {@link RowMapper} to map the root enreplacedies, must not
     *                        be {@literal null}.
     * @param expectedResults
     */
    public OneToManyResultSetExtractor(RowMapper<R> rootMapper, RowMapper<C> childMapper, ExpectedResults expectedResults) {
        replacedert.notNull(rootMapper, "Root RowMapper must not be null!");
        replacedert.notNull(childMapper, "Child RowMapper must not be null!");
        this.childMapper = childMapper;
        this.rootMapper = rootMapper;
        this.expectedResults = expectedResults == null ? ExpectedResults.ANY : expectedResults;
    }

    public List<R> extractData(ResultSet rs) throws SQLException, DataAccessException {
        List<R> results = new ArrayList<R>();
        int row = 0;
        boolean more = rs.next();
        if (more) {
            row++;
        }
        while (more) {
            R root = rootMapper.mapRow(rs, row);
            K primaryKey = mapPrimaryKey(rs);
            if (mapForeignKey(rs) != null) {
                while (more && primaryKey.equals(mapForeignKey(rs))) {
                    addChild(root, childMapper.mapRow(rs, row));
                    more = rs.next();
                    if (more) {
                        row++;
                    }
                }
            } else {
                more = rs.next();
                if (more) {
                    row++;
                }
            }
            results.add(root);
        }
        if ((expectedResults == ExpectedResults.ONE_AND_ONLY_ONE || expectedResults == ExpectedResults.ONE_OR_NONE) && results.size() > 1) {
            throw new IncorrectResultSizeDataAccessException(1, results.size());
        }
        if ((expectedResults == ExpectedResults.ONE_AND_ONLY_ONE || expectedResults == ExpectedResults.AT_LEAST_ONE) && results.size() < 1) {
            throw new IncorrectResultSizeDataAccessException(1, 0);
        }
        return results;
    }

    /**
     * Map the primary key value to the required type. This method must be
     * implemented by subclreplacedes. This method should not call <code>next()</code> on
     * the ResultSet; it is only supposed to map values of the current row.
     *
     * @param rs the ResultSet
     * @return the primary key value
     * @throws SQLException
     */
    protected abstract K mapPrimaryKey(ResultSet rs) throws SQLException;

    /**
     * Map the foreign key value to the required type. This method must be
     * implemented by subclreplacedes. This method should not call <code>next()</code> on
     * the ResultSet; it is only supposed to map values of the current row.
     *
     * @param rs the ResultSet
     * @return the foreign key value
     * @throws SQLException
     */
    protected abstract K mapForeignKey(ResultSet rs) throws SQLException;

    /**
     * Add the child object to the root object This method must be implemented by
     * subclreplacedes.
     *
     * @param root  the Root object
     * @param child the Child object
     */
    protected abstract void addChild(R root, C child);
}

/**
 * An abstract results extractor for row mapping operations that map multiple rows to a single root object. This is
 * useful when joining a one-to-many relationship where there can be multiple child rows returned per parent root.
 * <p>
 * It's replacedumed that the root type R table has a primary key (id) of type K and that the child type C table has a
 * foreign key of type K referencing the root table's primary key.
 * <p>
 * For example, consider the relationship: "a Customer has one-to-many Addresses". When joining the Customer table with
 * the Address table to build a Customer object, multiple rows would be returned for a Customer if it has more than one
 * Address. This extractor is useful in that case.
 * <p>
 * This clreplaced comes from the Spring Data JDBC Extensions project that has been archived in 2019:
 * https://github.com/spring-projects/spring-data-jdbc-ext
 *
 * @author Thomas Risberg
 * @author Keith Donald
 * @author Oliver Gierke
 * @since 1.0
 */
public abstract clreplaced OneToManyResultSetExtractor<R, C, K> implements ResultSetExtractor<List<R>> {

    public enum ExpectedResults {

        ANY, ONE_AND_ONLY_ONE, ONE_OR_NONE, AT_LEAST_ONE
    }

    protected final ExpectedResults expectedResults;

    protected final RowMapper<R> rootMapper;

    protected final RowMapper<C> childMapper;

    /**
     * Creates a new {@link OneToManyResultSetExtractor} from the given {@link RowMapper}s.
     *
     * @param rootMapper {@link RowMapper} to map the root enreplacedy, must not be {@literal null}.
     * @param childMapper {@link RowMapper} to map the root enreplacedies, must not be {@literal null}.
     */
    public OneToManyResultSetExtractor(RowMapper<R> rootMapper, RowMapper<C> childMapper) {
        this(rootMapper, childMapper, null);
    }

    /**
     * Creates a new {@link OneToManyResultSetExtractor} from the given {@link RowMapper}s and {@link ExpectedResults}.
     *
     * @param rootMapper {@link RowMapper} to map the root enreplacedy, must not be {@literal null}.
     * @param childMapper {@link RowMapper} to map the root enreplacedies, must not be {@literal null}.
     * @param expectedResults
     */
    public OneToManyResultSetExtractor(RowMapper<R> rootMapper, RowMapper<C> childMapper, ExpectedResults expectedResults) {
        replacedert.notNull(rootMapper, "Root RowMapper must not be null!");
        replacedert.notNull(childMapper, "Child RowMapper must not be null!");
        this.childMapper = childMapper;
        this.rootMapper = rootMapper;
        this.expectedResults = expectedResults == null ? ExpectedResults.ANY : expectedResults;
    }

    public List<R> extractData(ResultSet rs) throws SQLException {
        List<R> results = new ArrayList<R>();
        int row = 0;
        boolean more = rs.next();
        if (more) {
            row++;
        }
        while (more) {
            R root = rootMapper.mapRow(rs, row);
            K primaryKey = mapPrimaryKey(rs);
            if (mapForeignKey(rs) != null) {
                while (more && primaryKey.equals(mapForeignKey(rs))) {
                    addChild(root, childMapper.mapRow(rs, row));
                    more = rs.next();
                    if (more) {
                        row++;
                    }
                }
            } else {
                more = rs.next();
                if (more) {
                    row++;
                }
            }
            results.add(root);
        }
        if ((expectedResults == ExpectedResults.ONE_AND_ONLY_ONE || expectedResults == ExpectedResults.ONE_OR_NONE) && results.size() > 1) {
            throw new IncorrectResultSizeDataAccessException(1, results.size());
        }
        if ((expectedResults == ExpectedResults.ONE_AND_ONLY_ONE || expectedResults == ExpectedResults.AT_LEAST_ONE) && results.size() < 1) {
            throw new IncorrectResultSizeDataAccessException(1, 0);
        }
        return results;
    }

    /**
     * Map the primary key value to the required type.
     * This method must be implemented by subclreplacedes.
     * This method should not call <code>next()</code> on
     * the ResultSet; it is only supposed to map values of the current row.
     *
     * @param rs the ResultSet
     * @return the primary key value
     * @throws SQLException
     */
    protected abstract K mapPrimaryKey(ResultSet rs) throws SQLException;

    /**
     * Map the foreign key value to the required type.
     * This method must be implemented by subclreplacedes.
     * This method should not call <code>next()</code> on
     * the ResultSet; it is only supposed to map values of the current row.
     *
     * @param rs the ResultSet
     * @return the foreign key value
     * @throws SQLException
     */
    protected abstract K mapForeignKey(ResultSet rs) throws SQLException;

    /**
     * Add the child object to the root object
     * This method must be implemented by subclreplacedes.
     *
     * @param root the Root object
     * @param child the Child object
     */
    protected abstract void addChild(R root, C child);
}

/**
 * Repository for Users.
 *
 * @author Rafael
 */
@Repository
public clreplaced UserRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static final RowMapper<User> ROW_MAPPER = (rs, n) -> {
        User user = new User();
        user.setId(rs.getInt("ID"));
        user.setName(rs.getString("NAME"));
        user.setEmail(rs.getString("EMAIL"));
        return user;
    };

    /**
     * Creates a new user on the database.
     */
    public void insert(User user) {
        String sql = "INSERT INTO USER(NAME, EMAIL, PreplacedWORD) VALUES (?, ?, ?)";
        KeyHolder holder = new GeneratedKeyHolder();
        this.jdbcTemplate.update((connection) -> {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setString(1, user.getName());
            pstmt.setString(2, user.getEmail().toLowerCase().trim());
            pstmt.setString(3, user.getPreplacedword());
            return pstmt;
        }, holder);
        user.setId(holder.getKey().intValue());
    }

    /**
     * @return The user with the provided ID.
     */
    public User find(int id) {
        String sql = getBaseQuery() + " WHERE ID = ?";
        return this.jdbcTemplate.queryForObject(sql, new Object[] { id }, ROW_MAPPER);
    }

    /**
     * @return The user with the provided e-mail.
     */
    public User find(String email) {
        try {
            String sql = getBaseQuery() + " WHERE EMAIL = ?";
            return this.jdbcTemplate.queryForObject(sql, new Object[] { email.toLowerCase().trim() }, ROW_MAPPER);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * @return the user if the provided credentials are correct.
     */
    public User authenticate(String email, String preplacedword) {
        try {
            String sql = getBaseQuery() + " WHERE EMAIL = ? AND PreplacedWORD = ?";
            return this.jdbcTemplate.queryForObject(sql, new Object[] { email.toLowerCase().trim(), preplacedword }, ROW_MAPPER);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    private String getBaseQuery() {
        return "SELECT ID, NAME, EMAIL FROM USER";
    }
}

/**
 * Repository for advertisements.
 *
 * @author Rafael
 */
@Repository
public clreplaced AdvertisementRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static final RowMapper<Advertisement> ROW_MAPPER = (rs, n) -> {
        Advertisement ad = new Advertisement();
        ad.setId(rs.getInt("ID"));
        ad.setCreateDate(rs.getDate("CREATE_DATE"));
        ad.setreplacedle(rs.getString("replacedLE"));
        ad.setText(rs.getString("TEXT"));
        User user = new User();
        user.setId(rs.getInt("USER_ID"));
        user.setName(rs.getString("NAME"));
        user.setEmail(rs.getString("EMAIL"));
        ad.setUser(user);
        return ad;
    };

    /**
     * Creates a new ad on the database.
     */
    public void insert(Advertisement ad) {
        String sql = "INSERT INTO ADVERTISEMENT(CREATE_DATE, replacedLE, TEXT, USER_ID) VALUES (?, ?, ?, ?)";
        KeyHolder holder = new GeneratedKeyHolder();
        this.jdbcTemplate.update((connection) -> {
            PreparedStatement pstmt = connection.prepareStatement(sql);
            pstmt.setDate(1, new java.sql.Date(ad.getCreateDate().getTime()));
            pstmt.setString(2, ad.getreplacedle());
            pstmt.setString(3, ad.getText());
            pstmt.setInt(4, ad.getUser().getId());
            return pstmt;
        }, holder);
        ad.setId(holder.getKey().intValue());
    }

    /**
     * @return All the ads ordered by date.
     */
    public List<Advertisement> findAll() {
        return this.jdbcTemplate.query(getBaseQuery() + " ORDER BY ad.CREATE_DATE DESC", ROW_MAPPER);
    }

    /**
     * @return All the ads with the matching keyword.
     */
    public List<Advertisement> findByKeyword(String keyword) {
        String sql = getBaseQuery() + " WHERE ad.replacedLE LIKE ? OR ad.TEXT LIKE ? ORDER BY ad.CREATE_DATE DESC";
        String wildcard = "%" + keyword + "%";
        return this.jdbcTemplate.query(sql, new Object[] { wildcard, wildcard }, ROW_MAPPER);
    }

    private String getBaseQuery() {
        return "SELECT ad.ID, ad.CREATE_DATE, ad.replacedLE, ad.TEXT, ad.USER_ID, u.NAME, u.EMAIL FROM " + "ADVERTISEMENT ad INNER JOIN USER u ON ad.USER_ID = u.ID";
    }
}

/**
 * @author keli.wang
 */
@Repository
public clreplaced PropertiesEntryDaoImpl implements PropertiesEntryDao {

    private static final String INSERT_SQL = "INSERT INTO `properties_entries`(`key`,`group_id`,`profile`,`data_id`,`value`,`version`,`create_time`,`searchable`) VALUES(?,?,?,?,?,?,now(),1)";

    private static final String UPDATE_SQL = "UPDATE `properties_entries` SET `value`=?,`version`=?,`searchable`=1 " + "WHERE `key`=? AND `group_id`=? AND `profile`=? AND `data_id`=? AND `version`=?";

    private static final String SELECT_BY_CONFIG_META_SQL = "SELECT `key`, `group_id`, `profile`, `data_id`, `version`, `value` " + "FROM `properties_entries` " + "WHERE `group_id`=? AND `profile`=? AND `data_id`=?";

    private static final String SELECT_BY_REF_SQL = "SELECT `key`, `group_id`, `profile`, `data_id`, `version`, `value` " + "FROM `properties_entries` " + "WHERE `key`=? AND `group_id`=? AND `profile`=? AND `data_id`=?";

    private static final String SELECT_SQL_TEMPLATE = "SELECT `key`, `group_id`, `profile`, `data_id`, `version`, `value` " + "FROM `properties_entries` " + "WHERE `key`=:key AND `group_id` IN (:groups) %s AND `searchable`=1 LIMIT :offset, :limit";

    private static final String SELECT_COUNT_SQL_TEMPLATE = "SELECT COUNT(*) AS `total_count` FROM `properties_entries` " + "WHERE `key`=:key AND `group_id` IN (:groups) %s AND `searchable`=1";

    private static final String DELETE_SQL = "UPDATE `properties_entries` SET `version`=?,`searchable`=0 " + "WHERE `key`=? AND `group_id`=? AND `profile`=? AND `data_id`=? AND `version`=?";

    private static final RowMapper<PropertiesEntry> ENTRY_ROW_MAPPER = new RowMapper<PropertiesEntry>() {

        @Override
        public PropertiesEntry mapRow(ResultSet rs, int i) throws SQLException {
            return new PropertiesEntry(rs.getString("key"), rs.getString("group_id"), rs.getString("profile"), rs.getString("data_id"), rs.getLong("version"), rs.getString("value"));
        }
    };

    private static final ResultSetExtractor<Integer> SELECT_COUNT_EXTRACTOR = new ResultSetExtractor<Integer>() {

        @Override
        public Integer extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return rs.getInt("total_count");
            }
            return null;
        }
    };

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public void insert(final PropertiesEntry entry) {
        jdbcTemplate.update(INSERT_SQL, entry.getKey(), entry.getGroupId(), entry.getProfile(), entry.getDataId(), entry.getValue(), entry.getVersion());
    }

    @Override
    public int update(final PropertiesEntry entry, final long oldVersion) {
        return jdbcTemplate.update(UPDATE_SQL, entry.getValue(), entry.getVersion(), entry.getKey(), entry.getGroupId(), entry.getProfile(), entry.getDataId(), oldVersion);
    }

    @Override
    public List<PropertiesEntry> selectByConfigMeta(final ConfigMeta configMeta) {
        final List<PropertiesEntry> entries = jdbcTemplate.query(SELECT_BY_CONFIG_META_SQL, new Object[] { configMeta.getGroup(), configMeta.getProfile(), configMeta.getDataId() }, ENTRY_ROW_MAPPER);
        if (entries == null) {
            return Collections.emptyList();
        }
        return Collections.unmodifiableList(entries);
    }

    @Override
    public List<PropertiesEntry> selectByRef(String key, final Reference ref) {
        return jdbcTemplate.query(SELECT_BY_REF_SQL, new Object[] { key, ref.getRefGroup(), ref.getRefProfile(), ref.getRefDataId() }, new RowMapper<PropertiesEntry>() {

            @Override
            public PropertiesEntry mapRow(ResultSet rs, int rowNum) throws SQLException {
                return new PropertiesEntry(rs.getString("key"), ref.getGroup(), ref.getProfile(), ref.getAlias(), rs.getLong("version"), rs.getString("value"));
            }
        });
    }

    @Override
    public List<PropertiesEntry> select(String key, Set<String> groups, String profile, int pageNo, int pageSize) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("key", key);
        params.addValue("groups", groups);
        final StringBuilder extraQueryParam = new StringBuilder();
        if (!Strings.isNullOrEmpty(profile)) {
            extraQueryParam.append(" AND `profile` like :profile ");
            params.addValue("profile", profile + "%");
        }
        // 页码从1开始
        if (pageNo < 1) {
            pageNo = 1;
        }
        // 分页参数
        params.addValue("offset", (pageNo - 1) * pageSize);
        params.addValue("limit", pageSize);
        return namedParameterJdbcTemplate.query(String.format(SELECT_SQL_TEMPLATE, extraQueryParam), params, ENTRY_ROW_MAPPER);
    }

    @Override
    public int selectCount(String key, Set<String> groups, String profile) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue("key", key);
        params.addValue("groups", groups);
        final StringBuilder extraQueryParam = new StringBuilder();
        if (!Strings.isNullOrEmpty(profile)) {
            extraQueryParam.append(" AND `profile` like :profile ");
            params.addValue("profile", profile + "%");
        }
        return namedParameterJdbcTemplate.query(String.format(SELECT_COUNT_SQL_TEMPLATE, extraQueryParam), params, SELECT_COUNT_EXTRACTOR);
    }

    @Override
    public int delete(final PropertiesEntry entry, final long newVersion) {
        return jdbcTemplate.update(DELETE_SQL, newVersion, entry.getKey(), entry.getGroupId(), entry.getProfile(), entry.getDataId(), entry.getVersion());
    }
}

/**
 * @author zhenyu.nie created on 2016 2016/1/28 14:54
 */
@Repository
public clreplaced FileTemplateDaoImpl implements FileTemplateDao {

    private static final ResultSetExtractor<String> DETAIL_EXTRACTOR = new ResultSetExtractor<String>() {

        @Override
        public String extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return rs.getString("detail");
            }
            return null;
        }
    };

    private static final ResultSetExtractor<Integer> VERSION_EXTRACTOR = new ResultSetExtractor<Integer>() {

        @Override
        public Integer extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return rs.getInt("version");
            }
            return AdminConstants.TEMPLATE_VERSION_NOT_EXIST;
        }
    };

    private static final RowMapper<TemplateMeta> TEMPLATE_META_MAPPER = new RowMapper<TemplateMeta>() {

        @Override
        public TemplateMeta mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new TemplateMeta(rs.getString("group_id"), rs.getString("template"), rs.getString("description"), TemplateType.fromCode(rs.getInt("type")));
        }
    };

    private static final RowMapper<TemplateInfo> TEMPLATE_INFO_MAPPER = new RowMapper<TemplateInfo>() {

        @Override
        public TemplateInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new TemplateInfo(rs.getString("group_id"), rs.getString("template"), TemplateType.fromCode(rs.getInt("type")), rs.getString("detail"), rs.getString("description"), rs.getInt("version"), rs.getTimestamp("update_time"));
        }
    };

    private static final ResultSetExtractor<TemplateInfo> TEMPLATE_INFO_MAPPER_SINGLE = new ResultSetExtractor<TemplateInfo>() {

        @Override
        public TemplateInfo extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return new TemplateInfo(rs.getString("group_id"), rs.getString("template"), TemplateType.fromCode(rs.getInt("type")), rs.getString("detail"), rs.getString("description"), rs.getInt("version"), rs.getTimestamp("update_time"));
            } else {
                return null;
            }
        }
    };

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public TemplateInfo selectTemplateInfo(String group, String template) {
        return jdbcTemplate.query("SELECT group_id, template, type, description, detail, version, update_time FROM file_template WHERE group_id=? AND template=?", TEMPLATE_INFO_MAPPER_SINGLE, group, template);
    }

    @Override
    public String selectTemplateDetail(String group, String template) {
        return jdbcTemplate.query("SELECT detail FROM file_template WHERE group_id=? AND template=?", DETAIL_EXTRACTOR, group, template);
    }

    @Override
    public Integer selectVersion(String group, String template) {
        return jdbcTemplate.query("SELECT version FROM file_template WHERE group_id=? AND template=?", VERSION_EXTRACTOR, group, template);
    }

    @Override
    public void setTemplate(String group, String template, TemplateType type, String description, String detail, String operator) {
        jdbcTemplate.update("INSERT INTO file_template(group_id,template,type,description,detail,operator,create_time) " + "VALUES(?,?,?,?,?,?,now()) ON DUPLICATE KEY UPDATE description=?,detail=?,operator=?,version=version+1", group, template, type.getCode(), description, detail, operator, description, detail, operator);
    }

    @Override
    public List<TemplateMeta> selectTemplates(Collection<String> groups) {
        return jdbcTemplate.query("SELECT group_id,template,description,type FROM file_template WHERE group_id in " + SQLUtil.generateStubs(groups.size()), TEMPLATE_META_MAPPER, groups.toArray());
    }

    @Override
    public List<TemplateInfo> queryTemplateInfoByGroup(String group) {
        return jdbcTemplate.query("SELECT group_id, template, type, description, detail, version, update_time FROM file_template WHERE group_id=?", TEMPLATE_INFO_MAPPER, group);
    }

    @Override
    public List<TemplateInfo> queryTemplateInfo(Collection<String> groups) {
        return jdbcTemplate.query("SELECT group_id, template, type, description, detail, version, update_time FROM file_template WHERE group_id in " + SQLUtil.generateStubs(groups.size()), TEMPLATE_INFO_MAPPER, groups.toArray());
    }
}

/**
 * @author keli.wang
 * @since 2017/5/15
 */
@Repository
public clreplaced ConfigEditorSettingsDaoImpl implements ConfigEditorSettingsDao {

    private static final String SAVE_OR_UPDATE_SQL = "INSERT INTO `config_editor_settings`(`group_id`,`data_id`,`use_advanced_editor`)" + "VALUES (:groupId,:dataId,:useAdvancedEditor) ON DUPLICATE KEY UPDATE use_advanced_editor=:useAdvancedEditor";

    private static final String QUERY_SETTINGS_SQL = "SELECT `use_advanced_editor` FROM config_editor_settings WHERE group_id=:groupId AND data_id=:dataId";

    private static final RowMapper<ConfigEditorSettings> SETTINGS_MAPPER = new RowMapper<ConfigEditorSettings>() {

        @Override
        public ConfigEditorSettings mapRow(final ResultSet rs, final int rowNum) throws SQLException {
            return new ConfigEditorSettings(rs.getBoolean("use_advanced_editor"));
        }
    };

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public int saveOrUpdate(final String groupId, final String dataId, final ConfigEditorSettings settings) {
        final Map<String, Object> params = new HashMap<>();
        params.put("groupId", groupId);
        params.put("dataId", dataId);
        params.put("useAdvancedEditor", settings.isUseAdvancedEditor());
        return namedParameterJdbcTemplate.update(SAVE_OR_UPDATE_SQL, params);
    }

    @Override
    public ConfigEditorSettings query(final String groupId, final String dataId) {
        final Map<String, Object> params = new HashMap<>();
        params.put("groupId", groupId);
        params.put("dataId", dataId);
        return namedParameterJdbcTemplate.queryForObject(QUERY_SETTINGS_SQL, params, SETTINGS_MAPPER);
    }
}

/**
 * @author leix.xie
 * @date 2019/7/2 14:18
 * @describe
 */
@Repository
public clreplaced AppServerDaoImpl implements AppServerDao {

    private static final String SELECT_APP_SERVER_BY_APPCODE = "select server_id,ip,port,host,log_dir,room,app_code,auto_jstack_enable,auto_jmap_histo_enable from bistoury_server where app_code=?";

    private static final String SELECT_APP_SERVER_BY_IP = "select server_id,ip,port,host,log_dir,room,app_code,auto_jstack_enable,auto_jmap_histo_enable from bistoury_server where ip=?";

    private static final String SELECT_APP_SERVER_BY_SERVER_ID = "select server_id,ip,port,host,log_dir,room,app_code,auto_jstack_enable,auto_jmap_histo_enable from bistoury_server where server_id=?";

    private static final String INSERT_APP_SERVER = "insert ignore into bistoury_server (server_id,ip,port,host,log_dir,room,app_code,auto_jstack_enable,auto_jmap_histo_enable) values (?,?,?,?,?,?,?,?,?);";

    private static final String DELETE_APP_SERVER_BY_IP_PORT = "delete from bistoury_server where ip=?";

    private static final String DELETE_APP_SERVER_BY_SERVER_ID = "delete from bistoury_server where server_id=?";

    private static final String UPDATE_APP_SERVER_BY_SERVER_ID = "update bistoury_server set ip=?, port=?,host=?,log_dir=?,room=? where server_id=?";

    private static final String CHANGE_AUTO_JMAP_HISTO_ENABLE = "update bistoury_server set auto_jmap_histo_enable=? where server_id=?";

    private static final String CHANGE_AUTO_JSTACK_ENABLE = "update bistoury_server set auto_jstack_enable=? where server_id=?";

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<AppServer> getAppServerByAppCode(final String appCode) {
        return this.jdbcTemplate.query(SELECT_APP_SERVER_BY_APPCODE, APPLICATION_SERVER_ROW_MAPPER, appCode);
    }

    @Override
    public AppServer getAppServerByIp(final String ip) {
        return this.jdbcTemplate.query(SELECT_APP_SERVER_BY_IP, APPLICATION_SERVER_MAPPER, ip);
    }

    @Override
    public AppServer getAppServerByServerId(final String serverId) {
        return this.jdbcTemplate.query(SELECT_APP_SERVER_BY_SERVER_ID, APPLICATION_SERVER_MAPPER, serverId);
    }

    @Override
    public int addAppServer(AppServer appServer) {
        return this.jdbcTemplate.update(INSERT_APP_SERVER, appServer.getServerId(), appServer.getIp(), appServer.getPort(), appServer.getHost(), appServer.getLogDir(), appServer.getRoom(), appServer.getAppCode(), appServer.isAutoJStackEnable(), appServer.isAutoJMapHistoEnable());
    }

    @Override
    public int updateAppServer(AppServer appServer) {
        return this.jdbcTemplate.update(UPDATE_APP_SERVER_BY_SERVER_ID, appServer.getIp(), appServer.getPort(), appServer.getHost(), appServer.getLogDir(), appServer.getRoom(), appServer.getServerId());
    }

    @Override
    public int changeAutoJMapHistoEnable(final String serverId, final boolean enable) {
        return this.jdbcTemplate.update(CHANGE_AUTO_JMAP_HISTO_ENABLE, enable, serverId);
    }

    @Override
    public int changeAutoJStackEnable(final String serverId, final boolean enable) {
        return this.jdbcTemplate.update(CHANGE_AUTO_JSTACK_ENABLE, enable, serverId);
    }

    @Override
    public int deleteAppServerByIp(final String ip) {
        return this.jdbcTemplate.update(DELETE_APP_SERVER_BY_IP_PORT, ip);
    }

    @Override
    public int deleteAppServerByServerId(final String serverId) {
        return this.jdbcTemplate.update(DELETE_APP_SERVER_BY_SERVER_ID, serverId);
    }

    private static final ResultSetExtractor<AppServer> APPLICATION_SERVER_MAPPER = resultSet -> {
        if (resultSet.next()) {
            return getApplicationServerFromRs(resultSet);
        }
        return null;
    };

    private static final RowMapper<AppServer> APPLICATION_SERVER_ROW_MAPPER = (resultSet, i) -> getApplicationServerFromRs(resultSet);

    private static AppServer getApplicationServerFromRs(ResultSet resultSet) throws SQLException {
        String server_id = resultSet.getString("server_id");
        String ip = resultSet.getString("ip");
        int port = resultSet.getInt("port");
        String host = resultSet.getString("host");
        String log_dir = resultSet.getString("log_dir");
        String room = resultSet.getString("room");
        String app_code = resultSet.getString("app_code");
        boolean autoJstackEnable = resultSet.getBoolean("auto_jstack_enable");
        boolean autoJmapHistoEnable = resultSet.getBoolean("auto_jmap_histo_enable");
        return new AppServer(server_id, ip, port, host, log_dir, room, app_code, autoJstackEnable, autoJmapHistoEnable);
    }
}

/**
 * @author leix.xie
 * @date 2019/7/2 11:14
 * @describe
 */
@Repository
public clreplaced ApplicationUserDaoImpl implements ApplicationUserDao {

    private static final String SELECT_APP_BY_USER_CODE = "select app_code from bistoury_user_app where user_code=?";

    private static final String ADD_USER_FOR_APP = "insert ignore into bistoury_user_app (app_code, user_code) values (?, ?)";

    private static final String REMOVE_USER_FROM_APP = "delete from bistoury_user_app where user_code = ? and app_code = ?";

    private static final String SELECT_USER_BY_APP = "select user_code from bistoury_user_app where app_code = ?";

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<String> getAppCodesByUserCode(final String userCode) {
        return jdbcTemplate.query(SELECT_APP_BY_USER_CODE, LIST_APPCODE, userCode);
    }

    @Override
    public List<String> getUsersByAppCode(final String appCode) {
        return jdbcTemplate.query(SELECT_USER_BY_APP, LIST_USER, appCode);
    }

    @Override
    public int addAppUser(final String userCode, final String appCode) {
        return jdbcTemplate.update(ADD_USER_FOR_APP, appCode, userCode);
    }

    @Override
    public void batchAddAppUser(final List<String> userCodes, final String addCode) {
        jdbcTemplate.batchUpdate(ADD_USER_FOR_APP, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                preparedStatement.setString(1, addCode);
                preparedStatement.setString(2, userCodes.get(i));
            }

            @Override
            public int getBatchSize() {
                return userCodes.size();
            }
        });
    }

    @Override
    public int removeAppUser(final String userCode, final String appCode) {
        return jdbcTemplate.update(REMOVE_USER_FROM_APP, userCode, appCode);
    }

    private static final RowMapper<String> LIST_APPCODE = (rs, rowNum) -> rs.getString("app_code");

    private static final RowMapper<String> LIST_USER = (rs, rowNum) -> rs.getString("user_code");
}

/**
 * @author leix.xie
 * @date 2019/7/2 10:47
 * @describe
 */
@Repository
public clreplaced ApplicationDaoImpl implements ApplicationDao {

    private static final String SELECT_BY_APP_CODE = "select id, code, name, group_code, status, creator, create_time from bistoury_app where bistoury_app.code=?";

    private static final String SELECT_BY_APP_CODES = "SELECT id, code, name, group_code, status, creator, create_time FROM bistoury_app WHERE code in (:code)";

    private static final String SELECT_ALL_APPS = "select id, code, name, group_code, status, creator, create_time from bistoury_app";

    private static final String INSERT_APP = "INSERT INTO bistoury_app (code, name, group_code, status, creator) VALUES (?, ?, ?, ?, ?)";

    private static final String CHECK_EXIST_SQL = "SELECT code FROM bistoury_app WHERE code = ?";

    private static final String UPDATE_APP = "UPDATE bistoury_app SET name = ?, group_code=?,status=? WHERE code = ? ";

    private static ResultSetExtractor<Application> APPLICATION_MAPPER = resultSet -> {
        if (resultSet.next()) {
            return getApplicationFromRs(resultSet);
        }
        return null;
    };

    private static RowMapper<Application> APPLICATIONS_MAPPER = (resultSet, i) -> getApplicationFromRs(resultSet);

    private static ResultSetExtractor<String> CODE_MAPPER = resultSet -> {
        if (resultSet.next()) {
            return resultSet.getString("code");
        }
        return null;
    };

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    private static Application getApplicationFromRs(ResultSet resultSet) throws SQLException {
        int id = resultSet.getInt("id");
        String code = resultSet.getString("code");
        String name = resultSet.getString("name");
        String groupCode = resultSet.getString("group_code");
        int status = resultSet.getInt("status");
        String creator = resultSet.getString("creator");
        Timestamp createTime = resultSet.getTimestamp("create_time");
        return new Application(id, code, name, groupCode, status, creator, createTime);
    }

    @Override
    public int createApplication(Application application) {
        return jdbcTemplate.update(INSERT_APP, application.getCode(), application.getName(), application.getGroupCode(), application.getStatus(), application.getCreator());
    }

    @Override
    public int updateApplication(Application application) {
        return jdbcTemplate.update(UPDATE_APP, application.getName(), application.getGroupCode(), application.getStatus(), application.getCode());
    }

    @Override
    public Application getApplicationByAppCode(final String appCode) {
        return jdbcTemplate.query(SELECT_BY_APP_CODE, APPLICATION_MAPPER, appCode);
    }

    @Override
    public List<Application> getApplicationsByAppCodes(List<String> appCodes) {
        if (CollectionUtils.isEmpty(appCodes)) {
            return Collections.emptyList();
        }
        final MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("code", appCodes);
        return namedParameterJdbcTemplate.query(SELECT_BY_APP_CODES, parameters, APPLICATIONS_MAPPER);
    }

    @Override
    public List<Application> getAllApplications() {
        return this.jdbcTemplate.query(SELECT_ALL_APPS, APPLICATIONS_MAPPER);
    }

    @Override
    public boolean checkExist(String appCode) {
        String result = jdbcTemplate.query(CHECK_EXIST_SQL, CODE_MAPPER, appCode);
        return result != null;
    }
}

/**
 * Provides database services for play queues
 *
 * @author Sindre Mehus
 */
@Repository
public clreplaced PlayQueueDao extends AbstractDao {

    private static final String INSERT_COLUMNS = "username, \"CURRENT\", position_millis, changed, changed_by";

    private static final String QUERY_COLUMNS = "id, " + INSERT_COLUMNS;

    private final RowMapper<SavedPlayQueue> rowMapper = new PlayQueueMapper();

    @Transactional
    public SavedPlayQueue getPlayQueue(String username) {
        SavedPlayQueue playQueue = queryOne("select " + QUERY_COLUMNS + " from play_queue where username=?", rowMapper, username);
        if (playQueue == null) {
            return null;
        }
        List<Integer> mediaFileIds = queryForInts("select media_file_id from play_queue_file where play_queue_id = ?", playQueue.getId());
        playQueue.setMediaFileIds(mediaFileIds);
        return playQueue;
    }

    @Transactional
    public void savePlayQueue(SavedPlayQueue playQueue) {
        update("delete from play_queue where username=?", playQueue.getUsername());
        update("insert into play_queue(" + INSERT_COLUMNS + ") values (" + questionMarks(INSERT_COLUMNS) + ")", playQueue.getUsername(), playQueue.getCurrentMediaFileId(), playQueue.getPositionMillis(), playQueue.getChanged(), playQueue.getChangedBy());
        int id = queryForInt("select max(id) from play_queue", 0);
        playQueue.setId(id);
        for (Integer mediaFileId : playQueue.getMediaFileIds()) {
            update("insert into play_queue_file(play_queue_id, media_file_id) values (?, ?)", id, mediaFileId);
        }
    }

    private static clreplaced PlayQueueMapper implements RowMapper<SavedPlayQueue> {

        public SavedPlayQueue mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new SavedPlayQueue(rs.getInt(1), rs.getString(2), null, rs.getInt(3), rs.getLong(4), rs.getTimestamp(5), rs.getString(6));
        }
    }
}

/**
 * Provides database services for playlists.
 *
 * @author Sindre Mehus
 */
@Repository
public clreplaced PlaylistDao extends AbstractDao {

    private static final String INSERT_COLUMNS = "username, is_public, name, comment, file_count, duration_seconds, " + "created, changed, imported_from";

    private static final String QUERY_COLUMNS = "id, " + INSERT_COLUMNS;

    private final RowMapper<Playlist> rowMapper = new PlaylistMapper();

    public List<Playlist> getReadablePlaylistsForUser(String username) {
        List<Playlist> result1 = getWritablePlaylistsForUser(username);
        List<Playlist> result2 = query("select " + QUERY_COLUMNS + " from playlist where is_public", rowMapper);
        List<Playlist> result3 = query("select " + prefix(QUERY_COLUMNS, "playlist") + " from playlist, playlist_user where " + "playlist.id = playlist_user.playlist_id and " + "playlist.username != ? and " + "playlist_user.username = ?", rowMapper, username, username);
        // Put in sorted map to avoid duplicates.
        SortedMap<Integer, Playlist> map = new TreeMap<Integer, Playlist>();
        for (Playlist playlist : result1) {
            map.put(playlist.getId(), playlist);
        }
        for (Playlist playlist : result2) {
            map.put(playlist.getId(), playlist);
        }
        for (Playlist playlist : result3) {
            map.put(playlist.getId(), playlist);
        }
        return new ArrayList<Playlist>(map.values());
    }

    public List<Playlist> getWritablePlaylistsForUser(String username) {
        return query("select " + QUERY_COLUMNS + " from playlist where username=?", rowMapper, username);
    }

    public Playlist getPlaylist(int id) {
        return queryOne("select " + QUERY_COLUMNS + " from playlist where id=?", rowMapper, id);
    }

    public List<Playlist> getAllPlaylists() {
        return query("select " + QUERY_COLUMNS + " from playlist", rowMapper);
    }

    @Transactional
    public void createPlaylist(Playlist playlist) {
        update("insert into playlist(" + INSERT_COLUMNS + ") values(" + questionMarks(INSERT_COLUMNS) + ")", playlist.getUsername(), playlist.isShared(), playlist.getName(), playlist.getComment(), 0, 0, playlist.getCreated(), playlist.getChanged(), playlist.getImportedFrom());
        int id = queryForInt("select max(id) from playlist", 0);
        playlist.setId(id);
    }

    public void setFilesInPlaylist(int id, List<MediaFile> files) {
        update("delete from playlist_file where playlist_id=?", id);
        int duration = 0;
        for (MediaFile file : files) {
            update("insert into playlist_file (playlist_id, media_file_id) values (?, ?)", id, file.getId());
            if (file.getDurationSeconds() != null) {
                duration += file.getDurationSeconds();
            }
        }
        update("update playlist set file_count=?, duration_seconds=?, changed=? where id=?", files.size(), duration, new Date(), id);
    }

    public List<String> getPlaylistUsers(int playlistId) {
        return queryForStrings("select username from playlist_user where playlist_id=?", playlistId);
    }

    public void addPlaylistUser(int playlistId, String username) {
        if (!getPlaylistUsers(playlistId).contains(username)) {
            update("insert into playlist_user(playlist_id,username) values (?,?)", playlistId, username);
        }
    }

    public void deletePlaylistUser(int playlistId, String username) {
        update("delete from playlist_user where playlist_id=? and username=?", playlistId, username);
    }

    @Transactional
    public void deletePlaylist(int id) {
        update("delete from playlist where id=?", id);
    }

    public void updatePlaylist(Playlist playlist) {
        update("update playlist set username=?, is_public=?, name=?, comment=?, changed=?, imported_from=? where id=?", playlist.getUsername(), playlist.isShared(), playlist.getName(), playlist.getComment(), new Date(), playlist.getImportedFrom(), playlist.getId());
    }

    private static clreplaced PlaylistMapper implements RowMapper<Playlist> {

        public Playlist mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Playlist(rs.getInt(1), rs.getString(2), rs.getBoolean(3), rs.getString(4), rs.getString(5), rs.getInt(6), rs.getInt(7), rs.getTimestamp(8), rs.getTimestamp(9), rs.getString(10));
        }
    }
}

/**
 * Provides database services for artists.
 *
 * @author Sindre Mehus
 */
@Repository
public clreplaced ArtistDao extends AbstractDao {

    private static final String INSERT_COLUMNS = "name, cover_art_path, album_count, last_scanned, present, folder_id";

    private static final String QUERY_COLUMNS = "id, " + INSERT_COLUMNS;

    private final RowMapper<Artist> rowMapper = new ArtistMapper();

    /**
     * Returns the artist with the given name.
     *
     * @param artistName The artist name.
     * @return The artist or null.
     */
    public Artist getArtist(String artistName) {
        return queryOne("select " + QUERY_COLUMNS + " from artist where name=?", rowMapper, artistName);
    }

    /**
     * Returns the artist with the given name.
     *
     * @param artistName   The artist name.
     * @param musicFolders Only return artists that have at least one album in these folders.
     * @return The artist or null.
     */
    public Artist getArtist(final String artistName, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return null;
        }
        Map<String, Object> args = new HashMap<>();
        args.put("name", artistName);
        args.put("folders", MusicFolder.toIdList(musicFolders));
        return namedQueryOne("select " + QUERY_COLUMNS + " from artist where name = :name and folder_id in (:folders)", rowMapper, args);
    }

    /**
     * Returns the artist with the given ID.
     *
     * @param id The artist ID.
     * @return The artist or null.
     */
    public Artist getArtist(int id) {
        return queryOne("select " + QUERY_COLUMNS + " from artist where id=?", rowMapper, id);
    }

    /**
     * Creates or updates an artist.
     *
     * @param artist The artist to create/update.
     */
    @Transactional
    public void createOrUpdateArtist(Artist artist) {
        String sql = "update artist set " + "cover_art_path=?," + "album_count=?," + "last_scanned=?," + "present=?," + "folder_id=? " + "where name=?";
        int n = update(sql, artist.getCoverArtPath(), artist.getAlbumCount(), artist.getLastScanned(), artist.isPresent(), artist.getFolderId(), artist.getName());
        if (n == 0) {
            update("insert into artist (" + INSERT_COLUMNS + ") values (" + questionMarks(INSERT_COLUMNS) + ")", artist.getName(), artist.getCoverArtPath(), artist.getAlbumCount(), artist.getLastScanned(), artist.isPresent(), artist.getFolderId());
        }
        int id = queryForInt("select id from artist where name=?", null, artist.getName());
        artist.setId(id);
    }

    /**
     * Returns artists in alphabetical order.
     *
     * @param offset       Number of artists to skip.
     * @param count        Maximum number of artists to return.
     * @param musicFolders Only return artists that have at least one album in these folders.
     * @return Artists in alphabetical order.
     */
    public List<Artist> getAlphabetialArtists(final int offset, final int count, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        args.put("count", count);
        args.put("offset", offset);
        return namedQuery("select " + QUERY_COLUMNS + " from artist where present and folder_id in (:folders) " + "order by name limit :count offset :offset", rowMapper, args);
    }

    /**
     * Returns the most recently starred artists.
     *
     * @param offset       Number of artists to skip.
     * @param count        Maximum number of artists to return.
     * @param username     Returns artists starred by this user.
     * @param musicFolders Only return artists that have at least one album in these folders.
     * @return The most recently starred artists for this user.
     */
    public List<Artist> getStarredArtists(final int offset, final int count, final String username, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        args.put("username", username);
        args.put("count", count);
        args.put("offset", offset);
        return namedQuery("select " + prefix(QUERY_COLUMNS, "artist") + " from starred_artist, artist " + "where artist.id = starred_artist.artist_id and " + "artist.present and starred_artist.username = :username and " + "artist.folder_id in (:folders) " + "order by starred_artist.created desc limit :count offset :offset", rowMapper, args);
    }

    public void markPresent(String artistName, Date lastScanned) {
        update("update artist set present=?, last_scanned = ? where name=?", true, lastScanned, artistName);
    }

    public void markNonPresent(Date lastScanned) {
        int minId = queryForInt("select min(id) from artist where last_scanned < ? and present", 0, lastScanned);
        int maxId = queryForInt("select max(id) from artist where last_scanned < ? and present", 0, lastScanned);
        final int batchSize = 1000;
        for (int id = minId; id <= maxId; id += batchSize) {
            update("update artist set present=false where id between ? and ? and last_scanned < ? and present", id, id + batchSize, lastScanned);
        }
    }

    public List<Integer> getExpungeCandidates() {
        return queryForInts("select id from artist where not present");
    }

    public void expunge() {
        int minId = queryForInt("select min(id) from artist where not present", 0);
        int maxId = queryForInt("select max(id) from artist where not present", 0);
        final int batchSize = 1000;
        for (int id = minId; id <= maxId; id += batchSize) {
            update("delete from artist where id between ? and ? and not present", id, id + batchSize);
        }
    }

    public void starArtist(int artistId, String username) {
        unstarArtist(artistId, username);
        update("insert into starred_artist(artist_id, username, created) values (?,?,?)", artistId, username, new Date());
    }

    public void unstarArtist(int artistId, String username) {
        update("delete from starred_artist where artist_id=? and username=?", artistId, username);
    }

    public Date getArtistStarredDate(int artistId, String username) {
        return queryForDate("select created from starred_artist where artist_id=? and username=?", null, artistId, username);
    }

    private static clreplaced ArtistMapper implements RowMapper<Artist> {

        public Artist mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Artist(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getTimestamp(5), rs.getBoolean(6), rs.getInt(7));
        }
    }
}

/**
 * Provides database services for albums.
 *
 * @author Sindre Mehus
 */
@Repository
public clreplaced AlbumDao extends AbstractDao {

    private static final String INSERT_COLUMNS = "path, name, artist, song_count, duration_seconds, cover_art_path, " + "year, genre, play_count, last_played, comment, created, last_scanned, present, " + "folder_id, mb_release_id";

    private static final String QUERY_COLUMNS = "id, " + INSERT_COLUMNS;

    private final RowMapper<Album> rowMapper = new AlbumMapper();

    public Album getAlbum(int id) {
        return queryOne("select " + QUERY_COLUMNS + " from album where id=?", rowMapper, id);
    }

    /**
     * Returns the album with the given artist and album name.
     *
     * @param artistName The artist name.
     * @param albumName  The album name.
     * @return The album or null.
     */
    public Album getAlbum(String artistName, String albumName) {
        return queryOne("select " + QUERY_COLUMNS + " from album where artist=? and name=?", rowMapper, artistName, albumName);
    }

    /**
     * Returns the album that the given file (most likely) is part of.
     *
     * @param file The media file.
     * @return The album or null.
     */
    public Album getAlbumForFile(MediaFile file) {
        // First, get all albums with the correct album name (irrespective of artist).
        List<Album> candidates = query("select " + QUERY_COLUMNS + " from album where name=?", rowMapper, file.getAlbumName());
        if (candidates.isEmpty()) {
            return null;
        }
        // Look for album with the correct artist.
        for (Album candidate : candidates) {
            if (ObjectUtils.equals(candidate.getArtist(), file.getArtist()) && FileUtil.exists(candidate.getPath())) {
                return candidate;
            }
        }
        // Look for album with the same path as the file.
        for (Album candidate : candidates) {
            if (ObjectUtils.equals(candidate.getPath(), file.getParentPath())) {
                return candidate;
            }
        }
        // No appropriate album found.
        return null;
    }

    public List<Album> getAlbumsForArtist(final String artist, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("artist", artist);
        args.put("folders", MusicFolder.toIdList(musicFolders));
        return namedQuery("select " + QUERY_COLUMNS + " from album where artist = :artist and present and folder_id in (:folders) " + "order by name", rowMapper, args);
    }

    /**
     * Creates or updates an album.
     *
     * @param album The album to create/update.
     */
    @Transactional
    public void createOrUpdateAlbum(Album album) {
        String sql = "update album set " + "path=?," + "song_count=?," + "duration_seconds=?," + "cover_art_path=?," + "year=?," + "genre=?," + "play_count=?," + "last_played=?," + "comment=?," + "created=?," + "last_scanned=?," + "present=?, " + "folder_id=?, " + "mb_release_id=? " + "where artist=? and name=?";
        int n = update(sql, album.getPath(), album.getSongCount(), album.getDurationSeconds(), album.getCoverArtPath(), album.getYear(), album.getGenre(), album.getPlayCount(), album.getLastPlayed(), album.getComment(), album.getCreated(), album.getLastScanned(), album.isPresent(), album.getFolderId(), album.getMusicBrainzReleaseId(), album.getArtist(), album.getName());
        if (n == 0) {
            update("insert into album (" + INSERT_COLUMNS + ") values (" + questionMarks(INSERT_COLUMNS) + ")", album.getPath(), album.getName(), album.getArtist(), album.getSongCount(), album.getDurationSeconds(), album.getCoverArtPath(), album.getYear(), album.getGenre(), album.getPlayCount(), album.getLastPlayed(), album.getComment(), album.getCreated(), album.getLastScanned(), album.isPresent(), album.getFolderId(), album.getMusicBrainzReleaseId());
        }
        int id = queryForInt("select id from album where artist=? and name=?", null, album.getArtist(), album.getName());
        album.setId(id);
    }

    /**
     * Returns albums in alphabetical order.
     *
     * @param offset       Number of albums to skip.
     * @param count        Maximum number of albums to return.
     * @param byArtist     Whether to sort by artist name
     * @param musicFolders Only return albums from these folders.
     * @param ignoreCase   Use case insensitive sorting
     * @return Albums in alphabetical order.
     */
    public List<Album> getAlphabeticalAlbums(final int offset, final int count, boolean byArtist, boolean ignoreCase, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        args.put("count", count);
        args.put("offset", offset);
        String orderBy;
        if (ignoreCase) {
            orderBy = byArtist ? "LOWER(artist),  LOWER(name)" : "LOWER(name)";
        } else {
            orderBy = byArtist ? "artist, name" : "name";
        }
        return namedQuery("select " + QUERY_COLUMNS + " from album where present and folder_id in (:folders) " + "order by " + orderBy + " limit :count offset :offset", rowMapper, args);
    }

    /**
     * Returns the count of albums in the given folders
     *
     * @param musicFolders Only return albums from these folders.
     * @return the count of present albums
     */
    public int getAlbumCount(final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return 0;
        }
        Map<String, Object> args = new HashMap<String, Object>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        return getNamedParameterJdbcTemplate().queryForObject("select count(*) from album where present and folder_id in (:folders)", args, Integer.clreplaced);
    }

    /**
     * Returns the most frequently played albums.
     *
     * @param offset       Number of albums to skip.
     * @param count        Maximum number of albums to return.
     * @param musicFolders Only return albums from these folders.
     * @return The most frequently played albums.
     */
    public List<Album> getMostFrequentlyPlayedAlbums(final int offset, final int count, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        args.put("count", count);
        args.put("offset", offset);
        return namedQuery("select " + QUERY_COLUMNS + " from album where play_count > 0 and present and folder_id in (:folders) " + "order by play_count desc limit :count offset :offset", rowMapper, args);
    }

    /**
     * Returns the most recently played albums.
     *
     * @param offset       Number of albums to skip.
     * @param count        Maximum number of albums to return.
     * @param musicFolders Only return albums from these folders.
     * @return The most recently played albums.
     */
    public List<Album> getMostRecentlyPlayedAlbums(final int offset, final int count, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        args.put("count", count);
        args.put("offset", offset);
        return namedQuery("select " + QUERY_COLUMNS + " from album where last_played is not null and present and folder_id in (:folders) " + "order by last_played desc limit :count offset :offset", rowMapper, args);
    }

    /**
     * Returns the most recently added albums.
     *
     * @param offset       Number of albums to skip.
     * @param count        Maximum number of albums to return.
     * @param musicFolders Only return albums from these folders.
     * @return The most recently added albums.
     */
    public List<Album> getNewestAlbums(final int offset, final int count, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        args.put("count", count);
        args.put("offset", offset);
        return namedQuery("select " + QUERY_COLUMNS + " from album where present and folder_id in (:folders) " + "order by created desc limit :count offset :offset", rowMapper, args);
    }

    /**
     * Returns the most recently starred albums.
     *
     * @param offset       Number of albums to skip.
     * @param count        Maximum number of albums to return.
     * @param username     Returns albums starred by this user.
     * @param musicFolders Only return albums from these folders.
     * @return The most recently starred albums for this user.
     */
    public List<Album> getStarredAlbums(final int offset, final int count, final String username, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        args.put("count", count);
        args.put("offset", offset);
        args.put("username", username);
        return namedQuery("select " + prefix(QUERY_COLUMNS, "album") + " from starred_album, album where album.id = starred_album.album_id and " + "album.present and album.folder_id in (:folders) and starred_album.username = :username " + "order by starred_album.created desc limit :count offset :offset", rowMapper, args);
    }

    /**
     * Returns albums in a genre.
     *
     * @param offset       Number of albums to skip.
     * @param count        Maximum number of albums to return.
     * @param genre        The genre name.
     * @param musicFolders Only return albums from these folders.
     * @return Albums in the genre.
     */
    public List<Album> getAlbumsByGenre(final int offset, final int count, final String genre, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        args.put("count", count);
        args.put("offset", offset);
        args.put("genre", genre);
        return namedQuery("select " + QUERY_COLUMNS + " from album where present and folder_id in (:folders) " + "and genre = :genre limit :count offset :offset", rowMapper, args);
    }

    /**
     * Returns albums within a year range.
     *
     * @param offset       Number of albums to skip.
     * @param count        Maximum number of albums to return.
     * @param fromYear     The first year in the range.
     * @param toYear       The last year in the range.
     * @param musicFolders Only return albums from these folders.
     * @return Albums in the year range.
     */
    public List<Album> getAlbumsByYear(final int offset, final int count, final int fromYear, final int toYear, final List<MusicFolder> musicFolders) {
        if (musicFolders.isEmpty()) {
            return Collections.emptyList();
        }
        Map<String, Object> args = new HashMap<>();
        args.put("folders", MusicFolder.toIdList(musicFolders));
        args.put("count", count);
        args.put("offset", offset);
        args.put("fromYear", fromYear);
        args.put("toYear", toYear);
        if (fromYear <= toYear) {
            return namedQuery("select " + QUERY_COLUMNS + " from album where present and folder_id in (:folders) " + "and year between :fromYear and :toYear order by year limit :count offset :offset", rowMapper, args);
        } else {
            return namedQuery("select " + QUERY_COLUMNS + " from album where present and folder_id in (:folders) " + "and year between :toYear and :fromYear order by year desc limit :count offset :offset", rowMapper, args);
        }
    }

    public void markNonPresent(Date lastScanned) {
        int minId = queryForInt("select min(id) from album where last_scanned < ? and present", 0, lastScanned);
        int maxId = queryForInt("select max(id) from album where last_scanned < ? and present", 0, lastScanned);
        final int batchSize = 1000;
        for (int id = minId; id <= maxId; id += batchSize) {
            update("update album set present=false where id between ? and ? and last_scanned < ? and present", id, id + batchSize, lastScanned);
        }
    }

    public List<Integer> getExpungeCandidates() {
        return queryForInts("select id from album where not present");
    }

    public void expunge() {
        int minId = queryForInt("select min(id) from album where not present", 0);
        int maxId = queryForInt("select max(id) from album where not present", 0);
        final int batchSize = 1000;
        for (int id = minId; id <= maxId; id += batchSize) {
            update("delete from album where id between ? and ? and not present", id, id + batchSize);
        }
    }

    public void starAlbum(int albumId, String username) {
        unstarAlbum(albumId, username);
        update("insert into starred_album(album_id, username, created) values (?,?,?)", albumId, username, new Date());
    }

    public void unstarAlbum(int albumId, String username) {
        update("delete from starred_album where album_id=? and username=?", albumId, username);
    }

    public Date getAlbumStarredDate(int albumId, String username) {
        return queryForDate("select created from starred_album where album_id=? and username=?", null, albumId, username);
    }

    private static clreplaced AlbumMapper implements RowMapper<Album> {

        public Album mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new Album(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getInt(5), rs.getInt(6), rs.getString(7), rs.getInt(8) == 0 ? null : rs.getInt(8), rs.getString(9), rs.getInt(10), rs.getTimestamp(11), rs.getString(12), rs.getTimestamp(13), rs.getTimestamp(14), rs.getBoolean(15), rs.getInt(16), rs.getString(17));
        }
    }
}

/**
 * <p>
 *     Uses {@link BeanPropertyRowMapper} and {@link SingleColumnRowMapper}
 */
clreplaced SpringResultSetAutoMapper<T> extends ResultSetAutoMapper<T> {

    private final RowMapper<T> beanPropertyRowMapper = BeanPropertyRowMapper.newInstance(getType());

    private final RowMapper<T> singleColumnRowMapper = SingleColumnRowMapper.newInstance(getType());

    SpringResultSetAutoMapper(Clreplaced<?> type) {
        super(type);
    }

    public T map(Result<?> result) {
        ResultSet rs = (ResultSet) result.wrappedObject();
        RowMapper<T> rowMapper;
        try {
            rowMapper = (rs.getMetaData().getColumnCount() == 1) ? singleColumnRowMapper : beanPropertyRowMapper;
            return rowMapper.mapRow(rs, getRowIndex(result));
        } catch (SQLException e) {
            throw new CallException(e.getMessage(), e);
        }
    }

    private Integer getRowIndex(Result<?> result) {
        Field rowIndexField = ReflectionUtils.findField(result.getClreplaced(), "rowIndex");
        rowIndexField.setAccessible(true);
        return (Integer) ReflectionUtils.getField(rowIndexField, result);
    }
}

/**
 * @author Dave Syer
 */
public clreplaced JdbcApprovalStore implements ApprovalStore {

    private final JdbcTemplate jdbcTemplate;

    private final Log logger = LogFactory.getLog(getClreplaced());

    private final RowMapper<Approval> rowMapper = new AuthorizationRowMapper();

    private static final String TABLE_NAME = "oauth_approvals";

    private static final String FIELDS = "expiresAt,status,lastModifiedAt,userId,clientId,scope";

    private static final String WHERE_KEY = "where userId=? and clientId=?";

    private static final String WHERE_KEY_AND_SCOPE = WHERE_KEY + " and scope=?";

    private static final String DEFAULT_ADD_APPROVAL_STATEMENT = String.format("insert into %s ( %s ) values (?,?,?,?,?,?)", TABLE_NAME, FIELDS);

    private static final String DEFAULT_REFRESH_APPROVAL_STATEMENT = String.format("update %s set expiresAt=?, status=?, lastModifiedAt=? " + WHERE_KEY_AND_SCOPE, TABLE_NAME);

    private static final String DEFAULT_GET_APPROVAL_SQL = String.format("select %s from %s " + WHERE_KEY, FIELDS, TABLE_NAME);

    private static final String DEFAULT_DELETE_APPROVAL_SQL = String.format("delete from %s " + WHERE_KEY_AND_SCOPE, TABLE_NAME);

    private static final String DEFAULT_EXPIRE_APPROVAL_STATEMENT = String.format("update %s set expiresAt = ? " + WHERE_KEY_AND_SCOPE, TABLE_NAME);

    private String addApprovalStatement = DEFAULT_ADD_APPROVAL_STATEMENT;

    private String refreshApprovalStatement = DEFAULT_REFRESH_APPROVAL_STATEMENT;

    private String findApprovalStatement = DEFAULT_GET_APPROVAL_SQL;

    private String deleteApprovalStatment = DEFAULT_DELETE_APPROVAL_SQL;

    private String expireApprovalStatement = DEFAULT_EXPIRE_APPROVAL_STATEMENT;

    private boolean handleRevocationsAsExpiry = false;

    public JdbcApprovalStore(DataSource dataSource) {
        replacedert.notNull(dataSource);
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void setHandleRevocationsAsExpiry(boolean handleRevocationsAsExpiry) {
        this.handleRevocationsAsExpiry = handleRevocationsAsExpiry;
    }

    public void setAddApprovalStatement(String addApprovalStatement) {
        this.addApprovalStatement = addApprovalStatement;
    }

    public void setFindApprovalStatement(String findApprovalStatement) {
        this.findApprovalStatement = findApprovalStatement;
    }

    public void setDeleteApprovalStatment(String deleteApprovalStatment) {
        this.deleteApprovalStatment = deleteApprovalStatment;
    }

    public void setExpireApprovalStatement(String expireApprovalStatement) {
        this.expireApprovalStatement = expireApprovalStatement;
    }

    public void setRefreshApprovalStatement(String refreshApprovalStatement) {
        this.refreshApprovalStatement = refreshApprovalStatement;
    }

    @Override
    public boolean addApprovals(final Collection<Approval> approvals) {
        logger.debug(String.format("adding approvals: [%s]", approvals));
        boolean success = true;
        for (Approval approval : approvals) {
            if (!updateApproval(refreshApprovalStatement, approval)) {
                if (!updateApproval(addApprovalStatement, approval)) {
                    success = false;
                }
            }
        }
        return success;
    }

    @Override
    public boolean revokeApprovals(Collection<Approval> approvals) {
        logger.debug(String.format("Revoking approvals: [%s]", approvals));
        boolean success = true;
        for (final Approval approval : approvals) {
            if (handleRevocationsAsExpiry) {
                int refreshed = jdbcTemplate.update(expireApprovalStatement, new PreparedStatementSetter() {

                    @Override
                    public void setValues(PreparedStatement ps) throws SQLException {
                        ps.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
                        ps.setString(2, approval.getUserId());
                        ps.setString(3, approval.getClientId());
                        ps.setString(4, approval.getScope());
                    }
                });
                if (refreshed != 1) {
                    success = false;
                }
            } else {
                int refreshed = jdbcTemplate.update(deleteApprovalStatment, new PreparedStatementSetter() {

                    @Override
                    public void setValues(PreparedStatement ps) throws SQLException {
                        ps.setString(1, approval.getUserId());
                        ps.setString(2, approval.getClientId());
                        ps.setString(3, approval.getScope());
                    }
                });
                if (refreshed != 1) {
                    success = false;
                }
            }
        }
        return success;
    }

    public boolean purgeExpiredApprovals() {
        logger.debug("Purging expired approvals from database");
        try {
            int deleted = jdbcTemplate.update(deleteApprovalStatment + " where expiresAt <= ?", new PreparedStatementSetter() {

                @Override
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setTimestamp(1, new Timestamp(new Date().getTime()));
                }
            });
            logger.debug(deleted + " expired approvals deleted");
        } catch (DataAccessException ex) {
            logger.error("Error purging expired approvals", ex);
            return false;
        }
        return true;
    }

    @Override
    public List<Approval> getApprovals(String userName, String clientId) {
        return jdbcTemplate.query(findApprovalStatement, rowMapper, userName, clientId);
    }

    private boolean updateApproval(final String sql, final Approval approval) {
        logger.debug(String.format("refreshing approval: [%s]", approval));
        int refreshed = jdbcTemplate.update(sql, new PreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setTimestamp(1, new Timestamp(approval.getExpiresAt().getTime()));
                ps.setString(2, (approval.getStatus() == null ? APPROVED : approval.getStatus()).toString());
                ps.setTimestamp(3, new Timestamp(approval.getLastUpdatedAt().getTime()));
                ps.setString(4, approval.getUserId());
                ps.setString(5, approval.getClientId());
                ps.setString(6, approval.getScope());
            }
        });
        if (refreshed != 1) {
            return false;
        }
        return true;
    }

    private static clreplaced AuthorizationRowMapper implements RowMapper<Approval> {

        @Override
        public Approval mapRow(ResultSet rs, int rowNum) throws SQLException {
            String userName = rs.getString(4);
            String clientId = rs.getString(5);
            String scope = rs.getString(6);
            Date expiresAt = rs.getTimestamp(1);
            String status = rs.getString(2);
            Date lastUpdatedAt = rs.getTimestamp(3);
            return new Approval(userName, clientId, scope, expiresAt, ApprovalStatus.valueOf(status), lastUpdatedAt);
        }
    }
}

@Repository
public clreplaced ShipmentMapper {

    private final JdbcTemplate jdbcTemplate;

    private final RowMapper<Shipment> shipmentRowMapper = (rs, i) -> new Shipment(Carrier.valueOf(rs.getString("carrier")), rs.getString("order_id"), rs.getDate("shipment_date").toLocalDate(), UUID.fromString(rs.getString("tracking_number")));

    public ShipmentMapper(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public Optional<Shipment> findByOrderId(String orderId) {
        try {
            final Shipment shipment = this.jdbcTemplate.queryForObject("SELECT order_id, carrier, shipment_date, tracking_number FROM shipment WHERE order_id = ?", shipmentRowMapper, orderId);
            return Optional.ofNullable(shipment);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }

    public List<Shipment> findAll() {
        return this.jdbcTemplate.query("SELECT order_id, carrier, shipment_date, tracking_number FROM shipment", this.shipmentRowMapper);
    }

    @Transactional
    public int insert(Shipment shipment) {
        return this.jdbcTemplate.update("INSERT INTO shipment(order_id, carrier, shipment_date, tracking_number) VALUES (?, ?, ?, ?)", shipment.orderId(), shipment.carrier().name(), shipment.shipmentDate(), shipment.trackingNumber().toString());
    }
}

@Repository
public clreplaced CartMapper {

    private final JdbcTemplate jdbcTemplate;

    private final RowMapper<Carreplacedem> carreplacedemRowMapper = (rs, i) -> {
        final String itemId = rs.getString("item_id");
        if (itemId == null) {
            return null;
        }
        return new Carreplacedem(itemId, rs.getInt("quanreplacedy"), rs.getBigDecimal("unit_price"));
    };

    public CartMapper(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Transactional
    public int[] insertCart(Cart cart) {
        final int cartUpdated = this.jdbcTemplate.update("INSERT INTO cart(customer_id) VALUES (?)", cart.customerId());
        int itemUpdated = 0;
        if (!CollectionUtils.isEmpty(cart.items())) {
            final List<Object[]> args = cart.items().stream().map(i -> new Object[] { cart.customerId(), i.itemId(), i.quanreplacedy(), i.unitPrice() }).collect(toUnmodifiableList());
            final int[] batchUpdated = this.jdbcTemplate.batchUpdate("INSERT INTO cart_item(customer_id, item_id, quanreplacedy, unit_price) VALUES (?, ?, ?, ?)", args);
            itemUpdated = Arrays.stream(batchUpdated).sum();
        }
        return new int[] { cartUpdated, itemUpdated };
    }

    @Transactional
    public int upsertCarreplacedems(Cart cart) {
        int itemUpdated = 0;
        final String customerId = cart.customerId();
        if (!CollectionUtils.isEmpty(cart.items())) {
            final List<Object[]> args = cart.items().stream().map(i -> new Object[] { customerId, i.itemId(), i.quanreplacedy(), i.unitPrice(), i.quanreplacedy(), i.unitPrice() }).collect(toUnmodifiableList());
            final int[] batchUpdated = this.jdbcTemplate.batchUpdate("INSERT INTO cart_item(customer_id, item_id, quanreplacedy, unit_price) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE quanreplacedy = ?, unit_price = ?", args);
            itemUpdated = Arrays.stream(batchUpdated).sum();
        } else {
            this.jdbcTemplate.update("DELETE FROM cart_item WHERE customer_id = ?", customerId);
        }
        return itemUpdated;
    }

    @Transactional
    public int deleteCartByCustomerId(String customerId) {
        return this.jdbcTemplate.update("DELETE FROM cart WHERE customer_id = ?", customerId);
    }

    @Transactional
    public int deleteCartByCustomerIdAndItemId(String customerId, String itemId) {
        return this.jdbcTemplate.update("DELETE FROM cart_item WHERE customer_id = ? AND item_id = ?", customerId, itemId);
    }

    public Optional<Cart> findCartByCustomerId(String customerId) {
        try {
            final Cart cart = this.jdbcTemplate.query("SELECT c.customer_id, i.item_id, i.quanreplacedy, i.unit_price FROM cart AS c LEFT JOIN cart_item AS i ON c.customer_id = i.customer_id WHERE c.customer_id = ? FOR UPDATE", rs -> {
                Cart c = null;
                while (rs.next()) {
                    if (c == null) {
                        c = new Cart(rs.getString("customer_id"));
                    }
                    final Carreplacedem carreplacedem = this.carreplacedemRowMapper.mapRow(rs, -1);
                    if (carreplacedem != null) {
                        c.mergeItem(carreplacedem);
                    }
                }
                return c;
            }, customerId);
            return Optional.ofNullable(cart);
        } catch (EmptyResultDataAccessException ignored) {
            return Optional.empty();
        }
    }

    public Optional<Carreplacedem> findCarreplacedemByCustomerIdAndItemId(String customerId, String itemId) {
        try {
            final Carreplacedem carreplacedem = this.jdbcTemplate.queryForObject("SELECT i.item_id, i.quanreplacedy, i.unit_price FROM cart_item AS i LEFT JOIN cart c ON i.customer_id = c.customer_id WHERE c.customer_id = ? AND i.item_id = ?", this.carreplacedemRowMapper, customerId, itemId);
            return Optional.ofNullable(carreplacedem);
        } catch (EmptyResultDataAccessException ignored) {
            return Optional.empty();
        }
    }
}

@Repository
public clreplaced JdbcTemplatePetRepo implements PetRepo {

    private RowMapper<Pet> rowMapper = new PetRowMapper();

    protected JdbcTemplate jdbcTemplate;

    @Autowired
    public JdbcTemplatePetRepo(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public Set<Pet> findByOwner(User owner) {
        String sql = "select id, name, age, pet_type from p_pet where owner=" + owner.getId();
        return new HashSet<>(jdbcTemplate.query(sql, rowMapper));
    }

    private clreplaced PetRowMapper implements RowMapper<Pet> {

        @Override
        public Pet mapRow(ResultSet rs, int rowNum) throws SQLException {
            Long id = rs.getLong("ID");
            String name = rs.getString("NAME");
            Integer age = rs.getInt("AGE");
            PetType type = PetType.valueOf(rs.getString("PET_TYPE"));
            Pet pet = new Pet();
            pet.setId(id);
            pet.setName(name);
            pet.setAge(age);
            pet.setPetType(type);
            return pet;
        }
    }
}

@Repository("petRepo")
public clreplaced JdbcPetRepo extends JdbcAbstractRepo<Pet> implements PetRepo {

    private RowMapper<Pet> rowMapper = new PetRowMapper();

    @Autowired
    public JdbcPetRepo(JdbcTemplate jdbcTemplate) {
        super(jdbcTemplate);
        findByIdQuery = "select id, name from pet";
    }

    @Override
    public Pet findByOwner(User owner, String name) {
        return null;
    }

    @Override
    public Set<Pet> findAllByOwner(User owner) {
        return null;
    }

    @Override
    public Set<Pet> findAllByType(PetType type) {
        return null;
    }

    @Override
    public Pet findById(Long enreplacedyId) {
        return jdbcTemplate.queryForObject(findByIdQuery, rowMapper, enreplacedyId);
    }

    /**
     * Maps a row returned from a query executed on the PET table to a Pet object.
     */
    private clreplaced PetRowMapper implements RowMapper<Pet> {

        public Pet mapRow(ResultSet rs, int rowNum) throws SQLException {
            Long id = rs.getLong("ID");
            String name = rs.getString("NAME");
            Pet pet = new Pet();
            pet.setId(id);
            pet.setName(name);
            return pet;
        }
    }
}

/**
 * Central execution method. All un-named parameter execution goes through this method.
 * @param params parameters, similar to JDO query parameters.
 * Primitive parameters must be represented by their Object wrapper type.
 * The ordering of parameters is significant.
 * @param context contextual information preplaceded to the {@code mapRow}
 * callback method. The JDBC operation itself doesn't rely on this parameter,
 * but it can be useful for creating the objects of the result list.
 * @return a List of objects, one per row of the ResultSet. Normally all these
 * will be of the same clreplaced, although it is possible to use different types.
 */
public List<T> execute(Object[] params, Map<?, ?> context) throws DataAccessException {
    validateParameters(params);
    RowMapper<T> rowMapper = newRowMapper(params, context);
    return getJdbcTemplate().query(newPreparedStatementCreator(params), rowMapper);
}

public clreplaced GenericSqlQuery<T> extends SqlQuery<T> {

    Clreplaced<?> rowMapperClreplaced;

    RowMapper<?> rowMapper;

    @SuppressWarnings("rawtypes")
    public void setRowMapperClreplaced(Clreplaced<? extends RowMapper> rowMapperClreplaced) throws IllegalAccessException, InstantiationException {
        this.rowMapperClreplaced = rowMapperClreplaced;
        if (!RowMapper.clreplaced.isreplacedignableFrom(rowMapperClreplaced))
            throw new IllegalStateException("The specified clreplaced '" + rowMapperClreplaced.getName() + " is not a sub clreplaced of " + "'org.springframework.jdbc.core.RowMapper'");
    }

    @Override
    public void afterPropertiesSet() {
        super.afterPropertiesSet();
        replacedert.notNull(rowMapperClreplaced, "The 'rowMapperClreplaced' property is required");
    }

    @Override
    @SuppressWarnings("unchecked")
    protected RowMapper<T> newRowMapper(Object[] parameters, Map<?, ?> context) {
        try {
            return (RowMapper<T>) rowMapperClreplaced.newInstance();
        } catch (InstantiationException e) {
            throw new InvalidDataAccessResourceUsageException("Unable to instantiate RowMapper", e);
        } catch (IllegalAccessException e) {
            throw new InvalidDataAccessResourceUsageException("Unable to instantiate RowMapper", e);
        }
    }
}

/**
 * 支持注解，若实体没有注解，实体类名需要按照驼峰命名，属性与数据库字段一致不区分大小写
 *
 * @author 周宁
 */
public clreplaced EnreplacedyDaoImpl<T, Id extends Serializable> implements EnreplacedyDao<T, Id> {

    private static final int BATCH_PAGE_SIZE = 2000;

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    /**
     * 泛型
     */
    private Clreplaced<T> enreplacedyClreplaced;

    /**
     * 表名
     */
    private String tableName;

    /**
     * 主键
     */
    private String primaryKey;

    @SuppressWarnings("rawtypes")
    private RowMapper<T> rowMapper;

    @SuppressWarnings("unchecked")
    public EnreplacedyDaoImpl() {
        ParameterizedType type = (ParameterizedType) getClreplaced().getGenericSuperclreplaced();
        enreplacedyClreplaced = (Clreplaced<T>) type.getActualTypeArguments()[0];
        tableName = EnreplacedyTools.getTableName(enreplacedyClreplaced);
        primaryKey = EnreplacedyTools.getPk(enreplacedyClreplaced);
        rowMapper = BeanPropertyRowMapper.newInstance(enreplacedyClreplaced);
    }

    @Override
    public int save(T t) throws Exception {
        String sql = SqlMakeTools.makeSql(enreplacedyClreplaced, tableName, SQL_INSERT);
        Object[] args = SqlMakeTools.setArgs(t, SQL_INSERT);
        int[] argTypes = SqlMakeTools.setArgTypes(t, SQL_INSERT);
        return jdbcTemplate.update(sql, args, argTypes);
    }

    @Override
    public int update(T t) throws Exception {
        String sql = SqlMakeTools.makeSql(enreplacedyClreplaced, tableName, SQL_UPDATE);
        Object[] args = SqlMakeTools.setArgs(t, SQL_UPDATE);
        int[] argTypes = SqlMakeTools.setArgTypes(t, SQL_UPDATE);
        return jdbcTemplate.update(sql, args, argTypes);
    }

    @Override
    public void batchSave(List<T> list) throws Exception {
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        // 分页操作
        String sql = SqlMakeTools.makeSql(enreplacedyClreplaced, tableName, SQL_INSERT);
        int[] argTypes = SqlMakeTools.setArgTypes(list.get(0), SQL_INSERT);
        Integer j = 0;
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        for (int i = 0; i < list.size(); i++) {
            batchArgs.add(SqlMakeTools.setArgs(list.get(i), SQL_INSERT));
            j++;
            if (j.intValue() == BATCH_PAGE_SIZE) {
                jdbcTemplate.batchUpdate(sql, batchArgs, argTypes);
                batchArgs = new ArrayList<>();
                j = 0;
            }
        }
        jdbcTemplate.batchUpdate(sql, batchArgs, argTypes);
    }

    @Override
    public int saveAll(List<T> list) throws Exception {
        if (CollectionUtils.isEmpty(list)) {
            return 0;
        }
        // 分页操作
        String sql = SqlMakeTools.makeSql(enreplacedyClreplaced, tableName, SQL_INSERT);
        int[] argTypes = SqlMakeTools.setArgTypes(list.get(0), SQL_INSERT);
        List<Object[]> batchArgs = new ArrayList<Object[]>();
        for (int i = 0; i < list.size(); i++) {
            batchArgs.add(SqlMakeTools.setArgs(list.get(i), SQL_INSERT));
        }
        // 将sql分为左右两部分
        int index = sql.indexOf("VALUES");
        index = sql.indexOf("(", index);
        // sql的左侧insert into
        String sqlLeft = sql.substring(0, index);
        // sql的右侧values
        String sqlRight = sql.substring(index);
        // 分批次插入
        List<Object[]>[] batchArgsArr = CollectionUtil.slice(batchArgs, BATCH_PAGE_SIZE);
        // 影响记录数量
        int resultSize = 0;
        for (List<Object[]> args : batchArgsArr) {
            // 本批次的大小
            int batchSize = args.size();
            // 插入语句
            StringBuilder insSql = new StringBuilder(sqlLeft);
            // 参数
            List<Object> params = new ArrayList<>();
            // 字段类型数组
            int[] types = new int[batchSize * argTypes.length];
            for (int i = 0; i < batchSize; i++) {
                for (int j = 0; j < argTypes.length; j++) {
                    types[i * argTypes.length + j] = argTypes[j];
                }
                insSql.append(sqlRight).append(",");
            }
            insSql.setLength(insSql.length() - 1);
            for (Object[] objs : args) {
                for (Object arg : objs) {
                    params.add(arg);
                }
            }
            resultSize = resultSize + jdbcTemplate.update(insSql.toString(), params.toArray(), types);
        }
        return resultSize;
    }

    @Override
    public void batchUpdate(List<T> list) throws Exception {
        if (CollectionUtils.isEmpty(list)) {
            return;
        }
        // 分页操作
        String sql = SqlMakeTools.makeSql(enreplacedyClreplaced, tableName, SQL_UPDATE);
        int[] argTypes = SqlMakeTools.setArgTypes(list.get(0), SQL_UPDATE);
        Integer j = 0;
        List<Object[]> batchArgs = new ArrayList<>();
        for (int i = 0; i < list.size(); i++) {
            batchArgs.add(SqlMakeTools.setArgs(list.get(i), SQL_UPDATE));
            j++;
            if (j.intValue() == BATCH_PAGE_SIZE) {
                jdbcTemplate.batchUpdate(sql, batchArgs, argTypes);
                batchArgs = new ArrayList<>();
                j = 0;
            }
        }
        jdbcTemplate.batchUpdate(sql, batchArgs, argTypes);
    }

    @SuppressWarnings("unchecked")
    @Override
    public T queryOne(Id id) throws Exception {
        return this.queryOne(id, rowMapper);
    }

    @Override
    public T queryOne(Id id, RowMapper<T> tRowMapper) throws Exception {
        String sql = "SELECT * FROM " + tableName + " WHERE " + primaryKey + " = ?";
        List<T> result = jdbcTemplate.query(sql, tRowMapper, id);
        return DataAccessUtils.singleResult(result);
    }

    @Override
    public int delete(Id id) throws Exception {
        return this.batchDelete(Collections.singletonList(id));
    }

    @Override
    public int batchDelete(List<Id> ids) throws Exception {
        if (CollectionUtils.isNotEmpty(ids)) {
            StringBuilder sql = new StringBuilder();
            List<String> marks = ids.stream().map(s -> "?").collect(Collectors.toList());
            sql.append(" DELETE FROM " + tableName + " WHERE " + primaryKey + " in (");
            sql.append(String.join(",", marks));
            sql.append(")");
            return jdbcTemplate.update(sql.toString(), ids.toArray());
        }
        return 0;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<T> queryAll() throws Exception {
        return this.queryAll(rowMapper);
    }

    @Override
    public List<T> queryAll(RowMapper<T> tRowMapper) throws Exception {
        String sql = "SELECT * FROM " + tableName;
        return jdbcTemplate.query(sql, tRowMapper);
    }

    @Override
    public PageResult<T> pageQuery(Page page) throws Exception {
        return this.pageQueryWithCriteria(page, null);
    }

    @Override
    public PageResult<T> pageQuery(Page page, RowMapper<T> tRowMapper) throws Exception {
        return this.pageQueryWithCriteria(page, null, rowMapper);
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    @Override
    public PageResult<T> pageQueryWithCriteria(Page page, Criteria criteria) throws Exception {
        return this.pageQueryWithCriteria(page, criteria, rowMapper);
    }

    @Override
    public PageResult<T> pageQueryWithCriteria(Page page, Criteria criteria, RowMapper<T> tRowMapper) throws Exception {
        String sql = "SELECT * FROM " + tableName;
        Pair<String, Object[]> pair = SqlMakeTools.doCriteria(criteria, new StringBuilder(sql));
        sql = pair.getFirst();
        Object[] params = pair.getSecond();
        String pageSql = "SELECT SQL_CALC_FOUND_ROWS * FROM (" + sql + ") temp ";
        if (page != null) {
            pageSql = pageSql + " LIMIT ?,?";
            params = ArrayUtils.add(params, page.getOffset());
            params = ArrayUtils.add(params, page.getPageSize());
        }
        List<T> paged = jdbcTemplate.query(pageSql, params, tRowMapper);
        String countSql = "SELECT FOUND_ROWS() ";
        int count = jdbcTemplate.queryForObject(countSql, Integer.clreplaced);
        return new PageResult(paged, count);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<T> queryWithCriteria(Criteria criteria) throws Exception {
        return this.queryWithCriteria(criteria, rowMapper);
    }

    @Override
    public List<T> queryWithCriteria(Criteria criteria, RowMapper<T> tRowMapper) throws Exception {
        String sql = "SELECT * FROM " + tableName;
        Pair<String, Object[]> pair = SqlMakeTools.doCriteria(criteria, new StringBuilder(sql));
        return jdbcTemplate.query(pair.getFirst(), pair.getSecond(), tRowMapper);
    }

    @Override
    public int deleteWithCriteria(Criteria criteria) throws Exception {
        if (CollectionUtils.isNotEmpty(criteria.getSorts())) {
            throw new RuntimeException("不支持的操作!");
        }
        String sql = "delete FROM " + tableName;
        Pair<String, Object[]> pair = SqlMakeTools.doCriteria(criteria, new StringBuilder(sql));
        return jdbcTemplate.update(pair.getFirst(), pair.getSecond());
    }

    @Override
    public T queryOne(Criteria criteria) throws Exception {
        List<T> result = this.queryWithCriteria(criteria);
        return DataAccessUtils.singleResult(result);
    }

    @Override
    public T queryOne(Criteria criteria, RowMapper<T> tRowMapper) throws Exception {
        List<T> result = this.queryWithCriteria(criteria, tRowMapper);
        return DataAccessUtils.singleResult(result);
    }

    @Override
    public <E> Result<E> queryWithSql(Clreplaced<E> clss, SQL sql) throws Exception {
        Pair<String, Object[]> pair = SqlMakeTools.useSql(sql);
        return new Result<>(clss, pair.getFirst(), pair.getSecond(), jdbcTemplate);
    }

    @Override
    public int updateWithSql(SQL sql) throws Exception {
        List<Pair> kvs = sql.getKvs();
        if (!CollectionUtils.isEmpty(kvs)) {
            Pair<String, Object[]> pair = SqlMakeTools.useSql(sql);
            return jdbcTemplate.update(pair.getFirst(), pair.getSecond());
        }
        return 0;
    }

    @Override
    public int deleteWithSql(SQL sql) throws Exception {
        Pair<String, Object[]> pair = SqlMakeTools.useSql(sql);
        return jdbcTemplate.update(pair.getFirst(), pair.getSecond());
    }

    @Override
    public <K, V> Map<K, V> queryMapWithSql(SQL sql, ResultSetExtractor<Map<K, V>> resultSetExtractor) throws Exception {
        Pair<String, Object[]> pair = SqlMakeTools.useSql(sql);
        return jdbcTemplate.query(pair.getFirst(), pair.getSecond(), resultSetExtractor);
    }

    @Override
    public List<Map<String, Object>> queryMapsWithSql(SQL sql) throws Exception {
        Pair<String, Object[]> pair = SqlMakeTools.useSql(sql);
        return jdbcTemplate.query(pair.getFirst(), pair.getSecond(), new ColumnMapRowMapper());
    }

    @Override
    public Integer queryIntegerWithSql(SQL sql) throws Exception {
        Pair<String, Object[]> pair = SqlMakeTools.useSql(sql);
        return jdbcTemplate.queryForObject(pair.getFirst(), pair.getSecond(), Integer.clreplaced);
    }

    @Override
    public int insertWithSql(SQL sql) throws Exception {
        // 插入sql
        StringBuilder insertSql = new StringBuilder(sql.getPair().getFirst());
        // 待插入数据
        List<Object[]> params = sql.getPair().getSecond();
        List<Pair> kvs = sql.getKvs();
        int res = 0;
        if (CollectionUtils.isNotEmpty(params)) {
            List<Object[]>[] batchs = CollectionUtil.slice(params, BATCH_PAGE_SIZE);
            for (List<Object[]> batch : batchs) {
                List<Object> paramList = new ArrayList<>();
                StringBuilder tempInsertSql = new StringBuilder(insertSql);
                tempInsertSql.append("VALUES ");
                for (Object[] param : batch) {
                    tempInsertSql.append("(");
                    for (Object obj : param) {
                        tempInsertSql.append("?,");
                        paramList.add(obj);
                    }
                    tempInsertSql.setLength(tempInsertSql.length() - 1);
                    tempInsertSql.append("),");
                }
                tempInsertSql.setLength(tempInsertSql.length() - 1);
                if (CollectionUtils.isNotEmpty(kvs)) {
                    tempInsertSql.append(" ON DUPLICATE KEY UPDATE ");
                    for (Pair p : kvs) {
                        if (p.getSecond() instanceof FieldReference) {
                            FieldReference fieldReference = (FieldReference) p.getSecond();
                            tempInsertSql.append(p.getFirst() + " = " + fieldReference.getField() + ", ");
                        } else {
                            tempInsertSql.append(p.getFirst() + " = ?, ");
                            paramList.add(p.getSecond());
                        }
                    }
                    tempInsertSql.setLength(tempInsertSql.length() - 2);
                }
                res += jdbcTemplate.update(tempInsertSql.toString(), paramList.toArray());
            }
        } else if (CollectionUtils.isNotEmpty(sql.getSelectFields())) {
            sql.setSqlType(EnreplacedyDao.SQL_SELECT);
            Pair<String, Object[]> p = SqlMakeTools.useSql(sql);
            insertSql.append(p.getFirst());
            res = jdbcTemplate.update(insertSql.toString(), p.getSecond());
        }
        return res;
    }

    @Override
    @Transactional(rollbackFor = Exception.clreplaced)
    public String createWithSql(SQL sql) throws Exception {
        TableMeta tableMeta = sql.getTableMeta();
        StringBuilder createSql = new StringBuilder();
        StringBuilder insertSql = new StringBuilder();
        // 创建表
        String tbName = StringUtils.isEmpty(tableMeta.getName()) ? "tmp_" + UUID.randomUUID().toString().toLowerCase().replace("-", "") : tableMeta.getName();
        List<ColumnMeta> columns = tableMeta.getColumns();
        if (columns.isEmpty()) {
            throw new IllegalArgumentException("未指定任何字段");
        }
        createSql.append("CREATE ");
        insertSql.append("INSERT INTO ");
        if (tableMeta.isTemporary()) {
            createSql.append("TEMPORARY ");
        }
        createSql.append("TABLE ");
        if (tableMeta.isIfNotExists()) {
            createSql.append("IF NOT EXISTS ");
        }
        createSql.append(EnreplacedyTools.transferColumnName(tbName));
        insertSql.append(EnreplacedyTools.transferColumnName(tbName));
        createSql.append("(");
        insertSql.append("(");
        columns.forEach(columnMeta -> {
            createSql.append(EnreplacedyTools.transferColumnName(columnMeta.getName()));
            insertSql.append(EnreplacedyTools.transferColumnName(columnMeta.getName()));
            createSql.append(columnMeta.getDataType());
            if (columnMeta.isNotNull()) {
                createSql.append(" not null");
            }
            if (columnMeta.isPrimaryKey()) {
                createSql.append(" primary key");
                if (columnMeta.isAutoIncr()) {
                    createSql.append(" auto_increment");
                }
            }
            if (columnMeta.getVal() != null) {
                if (columnMeta.getJdbcType().equals(JDBCType.TIMESTAMP) || columnMeta.getVal().toLowerCase().equals("null")) {
                    createSql.append(String.format(" default %s", (columnMeta.getVal())));
                } else {
                    createSql.append(String.format(" default '%s'", (columnMeta.getVal())));
                }
            }
            if (StringUtils.isNotEmpty(columnMeta.getComment())) {
                createSql.append(String.format(" comment '%s'", columnMeta.getComment()));
            }
            createSql.append(",");
            insertSql.append(",");
        });
        // 索引
        List<IndexMeta> indexMetas = tableMeta.getIndexs();
        indexMetas.forEach(indexMeta -> {
            createSql.append((indexMeta.isUnique() ? "unique" : "") + " key" + (indexMeta.getIndexName() == null ? EnreplacedyTools.transferColumnName(indexMeta.getColumnNames().iterator().next()) : EnreplacedyTools.transferColumnName(indexMeta.getIndexName())) + "(");
            indexMeta.getColumnNames().forEach(cc -> {
                createSql.append(EnreplacedyTools.transferColumnName(cc));
                createSql.append(",");
            });
            createSql.setLength(createSql.length() - 1);
            createSql.append(")");
            if (StringUtils.isNotEmpty(indexMeta.getIndexType())) {
                createSql.append(" ").append(indexMeta.getIndexType());
            }
            if (StringUtils.isNotEmpty(indexMeta.getComment())) {
                createSql.append(" COMMENT '").append(indexMeta.getComment()).append("'");
            }
            createSql.append(",");
        });
        insertSql.setLength(insertSql.length() - 1);
        createSql.setLength(createSql.length() - 1);
        createSql.append(")ENGINE = " + tableMeta.getEngine() + " CHARSET=utf8 ");
        if (StringUtils.isNotEmpty(tableMeta.getComment())) {
            createSql.append("COMMENT=" + "'" + tableMeta.getComment() + "'");
        }
        insertSql.append(")");
        jdbcTemplate.execute(createSql.toString());
        // 保存数据
        sql.getPair().setFirst(insertSql.toString());
        insertWithSql(sql);
        return tbName;
    }

    @Override
    public void drop() throws Exception {
        jdbcTemplate.execute("DROP TABLE IF EXISTS " + tableName);
    }

    @Override
    public void truncate() throws Exception {
        jdbcTemplate.execute("TRUNCATE TABLE " + tableName);
    }

    @Override
    public void drunk(SQL sql) throws Exception {
        Pair<String, Object[]> pair = SqlMakeTools.useSql(sql);
        if (sql.getSqlType().equals(EnreplacedyDao.SQL_TRUNCATE)) {
            String truncateSql = pair.getFirst();
            jdbcTemplate.batchUpdate(truncateSql.split("\n"));
        } else if (sql.getSqlType().equals(EnreplacedyDao.SQL_DROP)) {
            String dropSql = pair.getFirst();
            jdbcTemplate.execute(dropSql);
        } else {
            throw new RuntimeException("method drunk only support `DROP` AND `TRUNCATE`");
        }
    }

    @Override
    public EnreplacedyDaoImpl<T, Id> bindKey(String bindKey) throws Exception {
        DataSourceBindHolder.setDataSource(DataSourceBind.bindKey(bindKey));
        return this;
    }

    @Override
    public EnreplacedyDao<T, Id> bindGroup(String group, Clreplaced<? extends LoadBalance> loadBalance) throws Exception {
        DataSourceBindHolder.setDataSource(DataSourceBind.bindGroup(group, loadBalance));
        return this;
    }

    @Override
    public EnreplacedyDao<T, Id> bindGroup(String group) throws Exception {
        return bindGroup(group, RoundbinLoadBalance.clreplaced);
    }
}

/**
 * Customized ResultSetExtractor used to return List of Products
 * @author Gaurav Rai Mazra
 *
 * <a href="https://gauravbytes.com">My blog</a>
 * <a href="https://lineofcode.in">Website</a>
 */
public clreplaced ProductResultSetExtractor implements ResultSetExtractor<ProductResponse> {

    private final RowMapper<Product> productRowMapper;

    public ProductResultSetExtractor(RowMapper<Product> productRowMapper) {
        super();
        this.productRowMapper = productRowMapper;
    }

    @Override
    public ProductResponse extractData(ResultSet rs) throws SQLException {
        final List<Product> products = new ArrayList<>();
        int rowNum = 0;
        while (rs.next()) {
            products.add(productRowMapper.mapRow(rs, rowNum));
            rowNum++;
        }
        return ProductResponse.of(products);
    }
}

@Service
public clreplaced UserServiceImpl implements UserService {

    private static final RowMapper<User> ROW_MAPPER = (rs, rowNum) -> {
        User user = new User();
        user.setId(rs.getInt("id"));
        user.setName(rs.getString("name"));
        user.setAge(rs.getInt("age"));
        return user;
    };

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<User> selectMasterUsers() {
        return jdbcTemplate.query("SELECT * FROM user", ROW_MAPPER);
    }

    @DS("slave")
    @Override
    public List<User> selectSlaveUsers() {
        return jdbcTemplate.query("SELECT * FROM user", ROW_MAPPER);
    }

    @Override
    public void addUser(User user) {
        jdbcTemplate.update("INSERT INTO user (name,age) VALUES(?, ?)", new Object[] { user.getName(), user.getAge() });
    }

    @Override
    public void deleteUserById(Long id) {
        jdbcTemplate.update("DELETE FROM user where  id =" + id);
    }
}

/**
 * Created by fang_j on 10/07/2016.
 */
public clreplaced ServerDao {

    public static final ServerDao INSTANCE = new ServerDao();

    private final String queryServerSql = "select distinct region_Id, server_id, operation from server where region_id=? and server_id=?";

    private final String queryServersSql = "select distinct region_Id, server_id, operation from server where region_id=?";

    private final String queryServersSql2 = "select distinct region_Id, server_id, operation from server";

    private final RowMapper<ServerModel> queryServersRowMapper = new RowMapper<ServerModel>() {

        @Override
        public ServerModel mapRow(final ResultSet rs, final int arg1) throws SQLException {
            final ServerModel instance = new ServerModel();
            instance.setRegionId(rs.getString(1));
            instance.setServerId(rs.getString(2));
            instance.setOperation(rs.getString(3));
            return instance;
        }
    };

    private ServerDao() {
    }

    public List<ServerModel> queryServer(final String regionId, final String serverId) {
        return DataConfig.jdbcTemplate().query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(final Connection conn) throws SQLException {
                final PreparedStatement ps = conn.prepareStatement(queryServerSql);
                ps.setString(1, regionId);
                ps.setString(2, serverId);
                return ps;
            }
        }, queryServersRowMapper);
    }

    public List<ServerModel> queryServers(final String regionId) {
        return DataConfig.jdbcTemplate().query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(final Connection conn) throws SQLException {
                final PreparedStatement ps = conn.prepareStatement(queryServersSql);
                ps.setString(1, regionId);
                return ps;
            }
        }, queryServersRowMapper);
    }

    public List<ServerModel> queryServers() {
        return DataConfig.jdbcTemplate().query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(final Connection conn) throws SQLException {
                return conn.prepareStatement(queryServersSql2);
            }
        }, queryServersRowMapper);
    }

    public List<ServerModel> query(final String condition, final String... args) {
        final String sql = "select id, region_id, server_id, operation, operator_id, token, create_time, datachange_lasttime from server";
        return DataConfig.jdbcTemplate().query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(final Connection conn) throws SQLException {
                final PreparedStatement ps;
                if (StringValues.isNullOrWhitespace(condition)) {
                    ps = conn.prepareStatement(sql);
                } else {
                    ps = conn.prepareStatement(Joiner.on(" ").join(sql, "where", condition));
                    for (int i = 0; i < args.length; i++) {
                        ps.setString(i + 1, args[i]);
                    }
                }
                return ps;
            }
        }, new RowMapper<ServerModel>() {

            @Override
            public ServerModel mapRow(final ResultSet rs, final int arg1) throws SQLException {
                final ServerModel instance = new ServerModel();
                instance.setId(rs.getLong(1));
                instance.setRegionId(rs.getString(2));
                instance.setServerId(rs.getString(3));
                instance.setOperation(rs.getString(4));
                instance.setOperatorId(rs.getString(5));
                instance.setToken(rs.getString(6));
                instance.setCreateTime(rs.getDate(7));
                instance.setUpdateTime(rs.getDate(8));
                return instance;
            }
        });
    }

    public void delete(final Long... ids) {
        DataConfig.jdbcTemplate().batchUpdate("delete from server where id = ?", new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return ids.length;
            }

            @Override
            public void setValues(final PreparedStatement ps, final int i) throws SQLException {
                ps.setLong(1, ids[i]);
            }
        });
    }

    public void delete(final ServerModel... servers) {
        if ((servers == null) || (servers.length == 0)) {
            return;
        }
        this.delete(Lists.newArrayList(servers));
    }

    public void delete(final List<ServerModel> serverList) {
        if (CollectionUtils.isEmpty(serverList)) {
            return;
        }
        DataConfig.jdbcTemplate().batchUpdate("delete from server where region_id=? and server_id=? and operation=?", new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return serverList.size();
            }

            @Override
            public void setValues(final PreparedStatement ps, final int index) throws SQLException {
                final ServerModel server = serverList.get(index);
                ps.setString(1, server.getRegionId());
                ps.setString(2, server.getServerId());
                ps.setString(3, server.getOperation());
            }
        });
    }

    public void destroyServers(final List<ServerKey> serverKeys) {
        if (CollectionUtils.isEmpty(serverKeys)) {
            return;
        }
        DataConfig.jdbcTemplate().batchUpdate("delete from server where region_id=? and server_id=?", new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return serverKeys.size();
            }

            @Override
            public void setValues(final PreparedStatement ps, final int index) throws SQLException {
                ServerKey serverKey = serverKeys.get(index);
                ps.setString(1, serverKey.getRegionId());
                ps.setString(2, serverKey.getServerId());
            }
        });
    }

    public void insert(final ServerModel... servers) {
        if ((servers == null) || (servers.length == 0)) {
            return;
        }
        this.insert(Lists.newArrayList(servers));
    }

    public void insert(final List<ServerModel> serverList) {
        if (CollectionUtils.isEmpty(serverList)) {
            return;
        }
        DataConfig.jdbcTemplate().batchUpdate("insert into server (region_id, server_id, operation, operator_id, token) values (?,?,?,?,?)" + " on duplicate key update operator_id=?, token=?", new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return serverList.size();
            }

            @Override
            public void setValues(final PreparedStatement ps, final int index) throws SQLException {
                final ServerModel server = serverList.get(index);
                ps.setString(1, server.getRegionId());
                ps.setString(2, server.getServerId());
                ps.setString(3, server.getOperation());
                ps.setString(4, server.getOperatorId());
                ps.setString(5, server.getToken());
                ps.setString(6, server.getOperatorId());
                ps.setString(7, server.getToken());
            }
        });
    }
}

/**
 * Created by fang_j on 10/07/2016.
 */
public clreplaced InstanceDao {

    public static final InstanceDao INSTANCE = new InstanceDao();

    private final String queryInstanceSql = "select distinct region_id, service_id, instance_id, operation from instance where region_id=? and service_id=? and instance_id=?";

    private final String queryInstancesSql = "select distinct region_id, service_id, instance_id, operation from instance where region_id=?";

    private final String queryInstancesSql2 = "select distinct region_id, service_id, instance_id, operation from instance";

    private final RowMapper<InstanceModel> queryInstancesRowMapper = new RowMapper<InstanceModel>() {

        @Override
        public InstanceModel mapRow(final ResultSet rs, final int arg1) throws SQLException {
            final InstanceModel instance = new InstanceModel();
            instance.setRegionId(rs.getString(1));
            instance.setServiceId(rs.getString(2));
            instance.setInstanceId(rs.getString(3));
            instance.setOperation(rs.getString(4));
            return instance;
        }
    };

    private InstanceDao() {
    }

    public List<InstanceModel> queryInstance(final String regionId, final String serviceId, final String instanceId) {
        return DataConfig.jdbcTemplate().query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(final Connection conn) throws SQLException {
                final PreparedStatement ps = conn.prepareStatement(queryInstanceSql);
                ps.setString(1, regionId);
                ps.setString(2, serviceId);
                ps.setString(3, instanceId);
                return ps;
            }
        }, queryInstancesRowMapper);
    }

    public List<InstanceModel> queryInstances(final String regionId) {
        return DataConfig.jdbcTemplate().query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(final Connection conn) throws SQLException {
                final PreparedStatement ps = conn.prepareStatement(queryInstancesSql);
                ps.setString(1, regionId);
                return ps;
            }
        }, queryInstancesRowMapper);
    }

    public List<InstanceModel> queryInstances() {
        return DataConfig.jdbcTemplate().query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(final Connection conn) throws SQLException {
                return conn.prepareStatement(queryInstancesSql2);
            }
        }, queryInstancesRowMapper);
    }

    public List<InstanceModel> queryInstances(final String regionId, final List<String> serviceIds) {
        if (CollectionUtils.isEmpty(serviceIds)) {
            return Lists.newArrayList();
        }
        final StringBuilder builder = new StringBuilder();
        for (int i = 0; i < serviceIds.size(); i++) {
            builder.append("?,");
        }
        builder.deleteCharAt(builder.length() - 1);
        final String sql = queryInstancesSql + " and service_id in (" + builder + ")";
        return DataConfig.jdbcTemplate().query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(final Connection conn) throws SQLException {
                final PreparedStatement ps = conn.prepareStatement(sql);
                ps.setString(1, regionId);
                for (int i = 0, len = serviceIds.size(); i < len; i++) {
                    ps.setString(i + 2, serviceIds.get(i));
                }
                return ps;
            }
        }, queryInstancesRowMapper);
    }

    public List<InstanceModel> query(final String condition, final String... args) {
        final String sql = "select id, region_id, service_id, instance_id, operation, operator_id, token, create_time, datachange_lasttime from instance";
        return DataConfig.jdbcTemplate().query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(final Connection conn) throws SQLException {
                final PreparedStatement ps;
                if (StringValues.isNullOrWhitespace(condition)) {
                    ps = conn.prepareStatement(sql);
                } else {
                    ps = conn.prepareStatement(Joiner.on(" ").join(sql, "where", condition));
                    for (int i = 0; i < args.length; i++) {
                        ps.setString(i + 1, args[i]);
                    }
                }
                return ps;
            }
        }, new RowMapper<InstanceModel>() {

            @Override
            public InstanceModel mapRow(final ResultSet rs, final int arg1) throws SQLException {
                final InstanceModel instance = new InstanceModel();
                instance.setId(rs.getLong(1));
                instance.setRegionId(rs.getString(2));
                instance.setServiceId(rs.getString(3));
                instance.setInstanceId(rs.getString(4));
                instance.setOperation(rs.getString(5));
                instance.setOperatorId(rs.getString(6));
                instance.setToken(rs.getString(7));
                instance.setCreateTime(rs.getDate(8));
                instance.setUpdateTime(rs.getDate(9));
                return instance;
            }
        });
    }

    public void delete(final Long... ids) {
        if ((ids == null) || (ids.length == 0)) {
            return;
        }
        DataConfig.jdbcTemplate().batchUpdate("delete from instance where id = ?", new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return ids.length;
            }

            @Override
            public void setValues(final PreparedStatement ps, final int i) throws SQLException {
                final Long id = ids[i];
                if (id == null) {
                    ps.setLong(1, 0L);
                } else {
                    ps.setLong(1, id);
                }
            }
        });
    }

    public void delete(final InstanceModel... instances) {
        if ((instances == null) || (instances.length == 0)) {
            return;
        }
        this.delete(Lists.newArrayList(instances));
    }

    public void delete(final List<InstanceModel> instances) {
        if (CollectionUtils.isEmpty(instances)) {
            return;
        }
        DataConfig.jdbcTemplate().batchUpdate("delete from instance where region_id=? and service_id=? and instance_id=? and operation=?", new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return instances.size();
            }

            @Override
            public void setValues(final PreparedStatement ps, final int index) throws SQLException {
                final InstanceModel instance = instances.get(index);
                ps.setString(1, instance.getRegionId());
                ps.setString(2, instance.getServiceId());
                ps.setString(3, instance.getInstanceId());
                ps.setString(4, instance.getOperation());
            }
        });
    }

    public void destroyServers(final List<ServerKey> serverKeys) {
        if (CollectionUtils.isEmpty(serverKeys)) {
            return;
        }
        DataConfig.jdbcTemplate().batchUpdate("delete from instance where region_id=? and instance_id=?", new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return serverKeys.size();
            }

            @Override
            public void setValues(final PreparedStatement ps, final int index) throws SQLException {
                ServerKey serverKey = serverKeys.get(index);
                ps.setString(1, serverKey.getRegionId());
                ps.setString(2, serverKey.getServerId());
            }
        });
    }

    public void insert(final InstanceModel... instances) {
        if ((instances == null) || (instances.length == 0)) {
            return;
        }
        this.insert(Lists.newArrayList(instances));
    }

    public void insert(final List<InstanceModel> instances) {
        if (CollectionUtils.isEmpty(instances)) {
            return;
        }
        DataConfig.jdbcTemplate().batchUpdate("insert into instance (region_id, service_id, instance_id, operation, operator_id, token) values (?,?,?,?,?,?)" + " on duplicate key update operator_id=?, token=?", new BatchPreparedStatementSetter() {

            @Override
            public int getBatchSize() {
                return instances.size();
            }

            @Override
            public void setValues(final PreparedStatement ps, final int index) throws SQLException {
                final InstanceModel instance = instances.get(index);
                ps.setString(1, instance.getRegionId());
                ps.setString(2, instance.getServiceId());
                ps.setString(3, instance.getInstanceId());
                ps.setString(4, instance.getOperation());
                ps.setString(5, instance.getOperatorId());
                ps.setString(6, instance.getToken());
                ps.setString(7, instance.getOperatorId());
                ps.setString(8, instance.getToken());
            }
        });
    }
}

@Bean
public UserDetailsService userDetailsService(JdbcTemplate jdbcTemplate) {
    RowMapper<User> userRowMapper = (ResultSet rs, int i) -> new User(rs.getString("ACCOUNT_NAME"), rs.getString("PreplacedWORD"), rs.getBoolean("ENABLED"), rs.getBoolean("ENABLED"), rs.getBoolean("ENABLED"), rs.getBoolean("ENABLED"), AuthorityUtils.createAuthorityList("ROLE_USER", "ROLE_ADMIN"));
    return username -> jdbcTemplate.queryForObject("SELECT * from ACCOUNT where ACCOUNT_NAME = ?", userRowMapper, username);
}

/**
 * Take paging.
 *
 * @param sqlCountRows query total SQL
 * @param sqlFetchRows query data sql
 * @param args         query parameters
 * @param pageNo       page number
 * @param pageSize     page size
 * @param rowMapper    {@link RowMapper}
 * @return Paginated data {@code <E>}
 */
public Page<E> fetchPage(final String sqlCountRows, final String sqlFetchRows, final Object[] args, final int pageNo, final int pageSize, final RowMapper rowMapper) {
    return fetchPage(sqlCountRows, sqlFetchRows, args, pageNo, pageSize, null, rowMapper);
}

/**
 * Take paging.
 *
 * @param sqlCountRows Query total SQL
 * @param sqlFetchRows Query data sql
 * @param args         query args
 * @param pageNo       page number
 * @param pageSize     page size
 * @param rowMapper    Enreplacedy mapping
 * @return Paging data
 */
public Page<E> fetchPage(final String sqlCountRows, final String sqlFetchRows, final Object[] args, final int pageNo, final int pageSize, final RowMapper rowMapper) {
    return fetchPage(sqlCountRows, sqlFetchRows, args, pageNo, pageSize, null, rowMapper);
}

/**
 * Abstract DAO implementation for login tracking.
 */
public abstract clreplaced AbstractLoginTrackDaoImpl extends BaseDaoImpl implements LoginTrackDao {

    /**
     * Row mapper for login track data.
     */
    private final static RowMapper<LoginData> LOGIN_DATA_ROW_MAPPER = new LoginDataRowMapper();

    /**
     * Implementation of {@link RowMapper} for all kinds of login track data.
     */
    static clreplaced LoginDataRowMapper implements RowMapper<LoginData> {

        @Override
        public LoginData mapRow(ResultSet rs, int row) throws SQLException {
            final int trackId = rs.getInt("login_track_id");
            // Required. Otherwise java.util.Date.before() does not work correctly
            final Date loginTime = new Date(rs.getTimestamp("creation_date").getTime());
            final String loginIP = rs.getString("ip_address");
            final LoginStatus loginStatus = LoginStatus.getLoginStatusFromStatusCode(rs.getInt("login_status"));
            final String username = rs.getString("username");
            return new LoginData(trackId, loginTime, loginIP, loginStatus, username);
        }
    }

    @Override
    public final List<LoginData> listLoginData(final Date sinceOrNull) {
        if (sinceOrNull == null) {
            final String sql = String.format("SELECT * FROM %s ORDER BY creation_date DESC", getTrackingTableName());
            return select(getLogger(), sql, new LoginDataRowMapper());
        } else {
            final String sql = String.format("SELECT * FROM %s WHERE creation_date > ? ORDER BY creation_date DESC", getTrackingTableName());
            return select(getLogger(), sql, new LoginDataRowMapper(), sinceOrNull);
        }
    }

    @Override
    public final List<LoginData> listLoginDataByIpAddress(final String ipAddress, final Date sinceOrNull) {
        if (sinceOrNull == null) {
            final String sql = String.format("SELECT * FROM %s WHERE ip_address = ? ORDER BY creation_date DESC", getTrackingTableName());
            return select(getLogger(), sql, new LoginDataRowMapper(), ipAddress);
        } else {
            final String sql = String.format("SELECT * FROM %s WHERE creation_date > ? AND ip_address = ? ORDER BY creation_date DESC", getTrackingTableName());
            return select(getLogger(), sql, new LoginDataRowMapper(), sinceOrNull, ipAddress);
        }
    }

    @Override
    public final List<LoginData> listLoginDataByUsername(final String username, final Date sinceOrNull) {
        if (sinceOrNull == null) {
            final String sql = String.format("SELECT * FROM %s WHERE username = ? ORDER BY creation_date DESC", getTrackingTableName());
            return select(getLogger(), sql, new LoginDataRowMapper(), username);
        } else {
            final String sql = String.format("SELECT * FROM %s WHERE creation_date > ? AND username = ? ORDER BY creation_date DESC", getTrackingTableName());
            return select(getLogger(), sql, new LoginDataRowMapper(), sinceOrNull, username);
        }
    }

    /**
     * Generic method for recording logins.
     *
     * @param ipAddress IP address of host
     * @param username use username in login
     * @param status login status
     */
    @Override
    public void trackLoginStatus(String ipAddress, String username, final LoginStatus status) {
        final String sql = isOracleDB() ? String.format("INSERT INTO %s (login_track_id, ip_address, login_status, username) VALUES (%s_seq.NEXTVAL, ?, ?, ?)", getTrackingTableName(), getTrackingTableName()) : String.format("INSERT INTO %s (ip_address, login_status, username) VALUES (?, ?, ?)", getTrackingTableName());
        update(getLogger(), sql, ipAddress, status.getStatusCode(), username);
    }

    @Override
    @DaoUpdateReturnValueCheck
    public int deleteOldRecords(int holdBackDays, int maxRecords) {
        if (holdBackDays < 0)
            throw new IllegalArgumentException("holdBackDays must be >= 0");
        if (maxRecords < 0)
            throw new IllegalArgumentException("maxRecords must be >= 0");
        final String sql = isOracleDB() ? String.format("DELETE FROM %s WHERE (sysdate - creation_date) > ? AND ROWNUM <= ?", getTrackingTableName()) : String.format("DELETE FROM %s WHERE DATE_SUB(CURRENT_TIMESTAMP, INTERVAL ? DAY) > creation_date LIMIT ?", getTrackingTableName());
        return update(getLogger(), sql, Math.max(holdBackDays, 0), Math.max(maxRecords, 0));
    }

    @Override
    @DaoUpdateReturnValueCheck
    public int deleteOldRecordsHours(int holdBackHours, int maxRecords) {
        if (holdBackHours < 0)
            throw new IllegalArgumentException("holdBackHours must be >= 0");
        if (maxRecords < 0)
            throw new IllegalArgumentException("maxRecords must be >= 0");
        final String sql = isOracleDB() ? String.format("DELETE FROM %s WHERE creation_date < sysdate - ? / 24.0 AND ROWNUM <= ?", getTrackingTableName()) : String.format("DELETE FROM %s WHERE DATE_SUB(CURRENT_TIMESTAMP, INTERVAL ? HOUR) > creation_date LIMIT ?", getTrackingTableName());
        return update(getLogger(), sql, Math.max(holdBackHours, 0), Math.max(maxRecords, 0));
    }

    @Override
    public Optional<LoginData> findLoginDataByTrackingID(final int trackingId) {
        final String sql = String.format("SELECT * FROM %s WHERE login_track_id=?", getTrackingTableName());
        final List<LoginData> list = select(getLogger(), sql, LOGIN_DATA_ROW_MAPPER, trackingId);
        if (list.isEmpty()) {
            return Optional.empty();
        } else {
            return Optional.of(list.get(0));
        }
    }

    /**
     * Returns the name of the tracking table used by sub-clreplaced.
     *
     * @return name of tracking table
     */
    public abstract String getTrackingTableName();

    /**
     * Returns the logger for sub-clreplaced.
     *
     * @return logger
     */
    public abstract Logger getLogger();
}

@Override
@SuppressWarnings("all")
protected E doExecute() throws JdbcDataException {
    RowMapper rowMapper = null;
    if (this.dynamic) {
        rowMapper = new DynamicEnreplacedyRowMapper(LOBHANDLER, this.dynamicEnreplacedyElement, this.persistentClreplaced);
    } else {
        rowMapper = new EnreplacedyRowMapper(LOBHANDLER, this.enreplacedyElement, this.persistentClreplaced);
    }
    if (null == this.parameters || this.parameters.length == 0) {
        return (E) this.jdbcBuilder.query(this.querySql, rowMapper);
    } else {
        return (E) this.jdbcBuilder.query(this.querySql, this.parameters, rowMapper);
    }
}

@Override
protected T doExecute() throws JdbcDataException {
    RowMapper rowMapper = null;
    String sql = this.sqlBuilder.toString();
    if (null != this.startRow && -1 != this.startRow && null != this.limit && this.limit > 0) {
        sql = PageRowBounds.pagination(dialectName, this.sqlBuilder.toString(), startRow, this.limit);
    }
    if (this.isDynamic) {
        rowMapper = new DynamicEnreplacedyRowMapper(LOBHANDLER, this.dynamicEnreplacedyElement, this.persistentClreplaced);
    } else {
        rowMapper = new EnreplacedyRowMapper(LOBHANDLER, this.enreplacedyElement, this.persistentClreplaced);
    }
    if (null == this.parameters || this.parameters.length == 0) {
        return (T) this.jdbcBuilder.query(sql, rowMapper);
    } else {
        return (T) this.jdbcBuilder.query(sql, this.parameters, rowMapper);
    }
}

public clreplaced ColumnMapperAdapter {

    private Map<String, RowMapper<Map<String, Object>>> columnMapRowMappers = new HashMap<>();

    private Map<String, Function<String, String>> rowMapColumnMappers = new HashMap<>();

    private RowMapper<Map<String, Object>> mapRowColumnMapper;

    private Function<String, String> rowMapColumnMapper;

    public void add(ColumnMapperProvider columnMapperProvider) {
        columnMapRowMappers.put(columnMapperProvider.name(), columnMapperProvider.getColumnMapRowMapper());
        rowMapColumnMappers.put(columnMapperProvider.name(), columnMapperProvider.getRowMapColumnMapper());
    }

    public void setDefault(ColumnMapperProvider columnMapperProvider) {
        this.mapRowColumnMapper = columnMapperProvider.getColumnMapRowMapper();
        this.rowMapColumnMapper = columnMapperProvider.getRowMapColumnMapper();
        add(columnMapperProvider);
    }

    public void setDefault(String name) {
        this.mapRowColumnMapper = getColumnMapRowMapper(name);
        this.rowMapColumnMapper = getRowMapColumnMapper(name);
    }

    public RowMapper<Map<String, Object>> getDefaultColumnMapRowMapper() {
        return this.mapRowColumnMapper;
    }

    public Function<String, String> getDefaultRowMapColumnMapper() {
        return this.rowMapColumnMapper;
    }

    public RowMapper<Map<String, Object>> getColumnMapRowMapper(String name) {
        return columnMapRowMappers.getOrDefault(name, mapRowColumnMapper);
    }

    public Function<String, String> getRowMapColumnMapper(String name) {
        return rowMapColumnMappers.getOrDefault(name, rowMapColumnMapper);
    }
}

/**
 * @author zhenyu.nie created on 2017 2017/8/10 17:06
 */
@Repository
public clreplaced DefaultServerDao implements ServerDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public int insert(QConfigServer server) {
        return jdbcTemplate.update("INSERT INTO server(ip, port, room) VALUES(INET_ATON(?),?,?) ON DUPLICATE KEY UPDATE room=?", server.getIp(), server.getPort(), server.getRoom(), server.getRoom());
    }

    @Override
    public String selectRoom(String ip) {
        return jdbcTemplate.query("SELECT room FROM server WHERE ip=INET_ATON(?)", ROOM_EXTRACTOR, ip);
    }

    @Override
    public List<QConfigServer> selectServers() {
        return jdbcTemplate.query("SELECT INET_NTOA(ip) as ip, port, room FROM server", SERVER_MAPPER);
    }

    private static final ResultSetExtractor<String> ROOM_EXTRACTOR = new ResultSetExtractor<String>() {

        @Override
        public String extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return rs.getString("room");
            }
            return null;
        }
    };

    private static final RowMapper<QConfigServer> SERVER_MAPPER = new RowMapper<QConfigServer>() {

        @Override
        public QConfigServer mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new QConfigServer(rs.getString("ip"), rs.getInt("port"), rs.getString("room"));
        }
    };
}

/**
 * @author zhenyu.nie created on 2018 2018/5/24 12:18
 */
@Repository
public clreplaced PushConfigVersionDaoImpl implements PushConfigVersionDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<PushConfigVersionItem> select() {
        return jdbcTemplate.query("SELECT group_id, data_id, profile, inet_ntoa(ip) as ip, version FROM push_config_version", ITEM_MAPPER);
    }

    @Override
    public PushConfigVersionItem select(ConfigMeta meta, String ip) {
        return jdbcTemplate.query("SELECT group_id, data_id, profile, inet_ntoa(ip) as ip, version FROM push_config_version WHERE group_id=? AND data_id=? AND profile=? AND ip=inet_aton(?)", ITEM_EXTRACTOR, meta.getGroup(), meta.getDataId(), meta.getProfile(), ip);
    }

    private static final RowMapper<PushConfigVersionItem> ITEM_MAPPER = new RowMapper<PushConfigVersionItem>() {

        @Override
        public PushConfigVersionItem mapRow(ResultSet rs, int rowNum) throws SQLException {
            ConfigMeta meta = new ConfigMeta(rs.getString("group_id"), rs.getString("data_id"), rs.getString("profile"));
            return new PushConfigVersionItem(meta, rs.getString("ip"), rs.getLong("version"));
        }
    };

    private static final ResultSetExtractor<PushConfigVersionItem> ITEM_EXTRACTOR = new ResultSetExtractor<PushConfigVersionItem>() {

        @Override
        public PushConfigVersionItem extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                ConfigMeta meta = new ConfigMeta(rs.getString("group_id"), rs.getString("data_id"), rs.getString("profile"));
                return new PushConfigVersionItem(meta, rs.getString("ip"), rs.getLong("version"));
            }
            return null;
        }
    };
}

/**
 * @author zhenyu.nie created on 2016 2016/5/3 15:02
 */
@Repository
public clreplaced FilePublicStatusDaoImpl implements FilePublicStatusDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<PublicConfigInfo> loadAll() {
        return jdbcTemplate.query("SELECT group_id, data_id, type FROM file_public_status", META_MAPPER);
    }

    public Optional<PublicConfigInfo> loadPublicInfo(ConfigMetaWithoutProfile configMetaWithoutProfile) {
        return jdbcTemplate.query("SELECT group_id, data_id, type FROM file_public_status where group_id = ? AND data_id= ? ", META_EXTRACTOR, configMetaWithoutProfile.getGroup(), configMetaWithoutProfile.getDataId());
    }

    @Override
    public boolean exist(PublicConfigInfo file) {
        return jdbcTemplate.query("SELECT id FROM file_public_status WHERE group_id=? AND data_id=?", ID_EXTRACTOR, file.getConfigMetaWithoutProfile().getGroup(), file.getConfigMetaWithoutProfile().getDataId()) != null;
    }

    private static final RowMapper<PublicConfigInfo> META_MAPPER = new RowMapper<PublicConfigInfo>() {

        @Override
        public PublicConfigInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new PublicConfigInfo(new ConfigMetaWithoutProfile(rs.getString("group_id"), rs.getString("data_id")), new PublicType(rs.getInt("type")));
        }
    };

    private static final ResultSetExtractor<Integer> ID_EXTRACTOR = new ResultSetExtractor<Integer>() {

        @Override
        public Integer extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return rs.getInt("id");
            }
            return null;
        }
    };

    private static final ResultSetExtractor<Optional<PublicConfigInfo>> META_EXTRACTOR = new ResultSetExtractor<Optional<PublicConfigInfo>>() {

        @Override
        public Optional<PublicConfigInfo> extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return Optional.of(new PublicConfigInfo(new ConfigMetaWithoutProfile(rs.getString("group_id"), rs.getString("data_id")), new PublicType(rs.getInt("type"))));
            }
            return Optional.absent();
        }
    };
}

/**
 * @author zhenyu.nie created on 2017 2017/5/15 18:41
 */
@Repository
public clreplaced FileDeleteDaoImpl implements FileDeleteDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<ConfigMeta> select(String ip) {
        return jdbcTemplate.query("SELECT group_id, data_id, profile FROM file_delete WHERE ip=INET_ATON(?)", CONFIG_META_MAPPER, ip);
    }

    @Override
    public void delete(ConfigMeta meta, String ip) {
        jdbcTemplate.update("DELETE FROM file_delete WHERE group_id=? AND data_id=? AND profile=? AND ip=INET_ATON(?)", meta.getGroup(), meta.getDataId(), meta.getProfile(), ip);
    }

    private static final RowMapper<ConfigMeta> CONFIG_META_MAPPER = new RowMapper<ConfigMeta>() {

        @Override
        public ConfigMeta mapRow(ResultSet rs, int i) throws SQLException {
            return new ConfigMeta(rs.getString("group_id"), rs.getString("data_id"), rs.getString("profile"));
        }
    };
}

/**
 * User: zhaohuiyu Date: 5/14/14 Time: 2:58 PM
 */
@Repository
public clreplaced ConfigDaoImpl implements ConfigDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<VersionData<ConfigMeta>> loadAll() {
        return jdbcTemplate.query("SELECT group_id,data_id,profile,version FROM config WHERE public_status!=?", CONFIGID_MAPPER, PublicStatus.DELETE.code());
    }

    @Override
    public List<VersionData<ConfigMeta>> loadByGroupAndProfile(String group, String profile) {
        return jdbcTemplate.query("SELECT group_id,data_id,profile,version FROM config WHERE public_status!=? AND group_id=? AND profile=?", CONFIGID_MAPPER, PublicStatus.DELETE.code(), group, profile);
    }

    @Override
    public ChecksumData<String> loadFromCandidateSnapshot(VersionData<ConfigMeta> configId) {
        ConfigMeta configMeta = configId.getData();
        long version = configId.getVersion();
        String content = jdbcTemplate.query("SELECT content FROM config_candidate_snapshot WHERE group_id=? AND data_id=? AND profile=? AND edit_version=?", CONTENT_EXTRACTOR, configMeta.getGroup(), configMeta.getDataId(), configMeta.getProfile(), version);
        return content == null ? null : ChecksumData.of(ChecksumAlgorithm.getChecksum(content), content);
    }

    @Override
    public ChecksumData<String> load(VersionData<ConfigMeta> configId) {
        ConfigMeta configMeta = configId.getData();
        return jdbcTemplate.query("SELECT checksum,content FROM config_snapshot WHERE group_id=? AND data_id=? AND profile=? AND version=?", CONFIG_EXTRACTOR, configMeta.getGroup(), configMeta.getDataId(), configMeta.getProfile(), configId.getVersion());
    }

    @Override
    public VersionData<ConfigMeta> load(ConfigMeta configMeta) {
        return jdbcTemplate.query("SELECT group_id,data_id,profile,version FROM config WHERE group_id=? AND data_id=? AND profile=? AND public_status!=?", CONFIGID_EXTRACTOR, configMeta.getGroup(), configMeta.getDataId(), configMeta.getProfile(), PublicStatus.DELETE.code());
    }

    @Override
    public Long selectBasedVersion(VersionData<ConfigMeta> configId) {
        ConfigMeta configMeta = configId.getData();
        return jdbcTemplate.query("SELECT based_version FROM config_candidate_snapshot WHERE group_id=? AND data_id=? AND profile=? AND edit_version=?", BASED_VERSION_EXTRACTOR, configMeta.getGroup(), configMeta.getDataId(), configMeta.getProfile(), configId.getVersion());
    }

    /**
     * 按照refer类型查数据
     * @param source
     * @param refType
     * @return
     */
    @Override
    public Optional<ConfigMeta> loadReference(ConfigMeta source, RefType refType) {
        return jdbcTemplate.query("SELECT ref_group_id,ref_data_id,ref_profile FROM config_reference WHERE group_id=? and profile=? and alias=? and status!=? and type = ?", META_EXTRACTOR, source.getGroup(), source.getProfile(), source.getDataId(), ReferenceStatus.DELETE.code(), refType.value());
    }

    /**
     * 返回所有的引用和继承关系，在cacheservice再区分引用和继承
     * @return
     */
    @Override
    public List<ReferenceInfo> loadAllReferenceInfo() {
        return jdbcTemplate.query("SELECT ref.group_id, ref.profile, ref.alias, ref.ref_group_id, ref.ref_data_id, ref.ref_profile, ref.type FROM config_reference ref WHERE ref.type = ? or (ref.type = ? and " + "exists (select conf.group_id, conf.data_id, conf.profile from config conf where conf.group_id = ref.group_id and conf.data_id = ref.alias and conf.profile = ref.profile and conf.public_status != ?) " + "and exists (select conf.group_id, conf.data_id, conf.profile from config conf where conf.group_id = ref.ref_group_id and conf.data_id = ref.ref_data_id and conf.profile = ref.ref_profile and conf.public_status != ?))" + "and ref.status!=?", REFERENCE_INFO_MAPPER, RefType.REFERENCE.value(), RefType.INHERIT.value(), PublicStatus.DELETE.code(), PublicStatus.DELETE.code(), ReferenceStatus.DELETE.code());
    }

    @Override
    public Optional<ReferenceInfo> loadReferenceInfo(RelationMeta relationMeta) {
        ConfigMeta source = relationMeta.getSource();
        ConfigMeta target = relationMeta.getTarget();
        return jdbcTemplate.query("SELECT ref_group_id, ref_data_id, ref_profile, group_id, alias, profile, type " + "FROM config_reference WHERE group_id=? and profile=? and alias=? " + "and ref_group_id=? and ref_profile=? and ref_data_id=? and status!=?", REFINFO_EXTRACTOR, source.getGroup(), source.getProfile(), source.getDataId(), target.getGroup(), target.getProfile(), target.getDataId(), ReferenceStatus.DELETE.code());
    }

    @Override
    public Optional<ReferenceInfo> loadReferenceInfo(String groupId, String dataId, String profile, String refGroupId, String refDataId, String refProfile, RefType refType) {
        return jdbcTemplate.query("SELECT ref_group_id,ref_data_id,ref_profile, group_id, alias, profile, type FROM config_reference WHERE group_id=? and profile=? and alias=? and ref_group_id = ? and ref_data_id = ? and ref_profile = ? and status!=? and type = ?", REFINFO_EXTRACTOR, groupId, profile, dataId, refGroupId, refDataId, refProfile, ReferenceStatus.DELETE.code(), refType.value());
    }

    private static final RowMapper<ReferenceInfo> REFERENCE_INFO_MAPPER = new RowMapper<ReferenceInfo>() {

        @Override
        public ReferenceInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new ReferenceInfo(new ConfigMeta(rs.getString("group_id"), rs.getString("alias"), rs.getString("profile")), new ConfigMeta(rs.getString("ref_group_id"), rs.getString("ref_data_id"), rs.getString("ref_profile")), RefType.codeOf(rs.getInt("type")));
        }
    };

    private static final ResultSetExtractor<Optional<ConfigMeta>> META_EXTRACTOR = new ResultSetExtractor<Optional<ConfigMeta>>() {

        @Override
        public Optional<ConfigMeta> extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return Optional.of(new ConfigMeta(rs.getString("ref_group_id"), rs.getString("ref_data_id"), rs.getString("ref_profile")));
            } else {
                return Optional.absent();
            }
        }
    };

    private static final ResultSetExtractor<Optional<ReferenceInfo>> REFINFO_EXTRACTOR = new ResultSetExtractor<Optional<ReferenceInfo>>() {

        @Override
        public Optional<ReferenceInfo> extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                String groupId = rs.getString("group_id");
                String dataId = rs.getString("alias");
                String profile = rs.getString("profile");
                String refGroupId = rs.getString("ref_group_id");
                String refDataId = rs.getString("ref_data_id");
                String refProfile = rs.getString("ref_profile");
                RefType refType = RefType.codeOf(rs.getInt("type"));
                return Optional.of(new ReferenceInfo(new ConfigMeta(groupId, dataId, profile), new ConfigMeta(refGroupId, refDataId, refProfile), refType));
            }
            return Optional.absent();
        }
    };

    private static final RowMapper<VersionData<ConfigMeta>> CONFIGID_MAPPER = new RowMapper<VersionData<ConfigMeta>>() {

        @Override
        public VersionData<ConfigMeta> mapRow(ResultSet rs, int rowNum) throws SQLException {
            ConfigMeta configMeta = new ConfigMeta(rs.getString("group_id"), rs.getString("data_id"), rs.getString("profile"));
            return VersionData.of(rs.getLong("version"), configMeta);
        }
    };

    private static final ResultSetExtractor<VersionData<ConfigMeta>> CONFIGID_EXTRACTOR = new ResultSetExtractor<VersionData<ConfigMeta>>() {

        @Override
        public VersionData<ConfigMeta> extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                ConfigMeta configMeta = new ConfigMeta(rs.getString("group_id"), rs.getString("data_id"), rs.getString("profile"));
                return VersionData.of(rs.getLong("version"), configMeta);
            }
            return null;
        }
    };

    private static final ResultSetExtractor<ChecksumData<String>> CONFIG_EXTRACTOR = new ResultSetExtractor<ChecksumData<String>>() {

        @Override
        public ChecksumData<String> extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return ChecksumData.of(rs.getString("checksum"), rs.getString("content"));
            }
            return null;
        }
    };

    private static final ResultSetExtractor<Long> BASED_VERSION_EXTRACTOR = new ResultSetExtractor<Long>() {

        @Override
        public Long extractData(ResultSet resultSet) throws SQLException, DataAccessException {
            if (resultSet.next()) {
                return resultSet.getLong("based_version");
            }
            return null;
        }
    };

    private static final ResultSetExtractor<String> CONTENT_EXTRACTOR = new ResultSetExtractor<String>() {

        @Override
        public String extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return rs.getString("content");
            }
            return null;
        }
    };
}

@Repository
public clreplaced UserFavoritesDaoImpl implements UserFavoritesDao {

    @Resource
    private JdbcTemplate jdbcTemplate;

    private final static String INSERT_GROUP = "INSERT IGNORE INTO `user_favorites`(`user`, `group_id`, `type`) VALUES(?,?,?)";

    private final static String INSERT_FILE = "INSERT IGNORE INTO `user_favorites`(`user`, `group_id`, `data_id`, `profile`, `type`) VALUES(?,?,?,?,?)";

    private final static String DELETE_GROUP = "DELETE FROM `user_favorites` WHERE `user`=? AND `group_id`=? AND `type`=?";

    private final static String DELETE_FILE = "DELETE FROM `user_favorites` WHERE `user`=? AND `group_id`=? AND `data_id`=? AND `profile`=? AND `type`=?";

    private final static String DELETE_BY_META = "DELETE FROM `user_favorites` WHERE `group_id`=? AND `data_id`=? AND `profile`=?";

    private final static String SELECT_BY_USER_AND_TYPE = "SELECT `group_id`, `data_id`, `profile` FROM `user_favorites` WHERE `user`=? AND `type`=? limit ?, ?";

    private final static String COUNT_BY_USER_AND_TYPE = "SELECT COUNT(*) AS count FROM `user_favorites` WHERE `user`=? AND `type`=?";

    private final static String COUNT_USER_GROUP = "SELECT COUNT(*) AS count FROM `user_favorites` WHERE `user`=? AND `group_id`=? AND `type`=?";

    private final static String COUNT_USER_META = "SELECT COUNT(*) AS count FROM `user_favorites` WHERE `user`=? AND `group_id`=? AND `data_id`=? AND `profile`=? AND `type`=?";

    @Override
    public List<String> listFavoriteGroups(String user, int page, int pageSize) {
        long offset = PaginationUtil.start(page, pageSize);
        return jdbcTemplate.query(SELECT_BY_USER_AND_TYPE, FAVORITE_GROUPS_MAPPER, user, UserFavoriteType.GROUP.getCode(), offset, pageSize);
    }

    @Override
    public List<ConfigMeta> listFavoriteFiles(String user, int page, int pageSize) {
        long offset = PaginationUtil.start(page, pageSize);
        return jdbcTemplate.query(SELECT_BY_USER_AND_TYPE, FAVORITE_FILES_MAPPER, user, UserFavoriteType.FILE.getCode(), offset, pageSize);
    }

    @Override
    public int countFavoriteItems(String user, UserFavoriteType type) {
        return jdbcTemplate.query(COUNT_BY_USER_AND_TYPE, COUNT_EXTRACTOR, user, type.getCode());
    }

    @Override
    public boolean isFavoriteGroup(String group, String user) {
        return jdbcTemplate.query(COUNT_USER_GROUP, COUNT_EXTRACTOR, user, group, UserFavoriteType.GROUP.getCode()) > 0;
    }

    @Override
    public boolean isFavoriteFile(ConfigMeta meta, String user) {
        return jdbcTemplate.query(COUNT_USER_META, COUNT_EXTRACTOR, user, meta.getGroup(), meta.getDataId(), meta.getProfile(), UserFavoriteType.FILE.getCode()) > 0;
    }

    @Override
    public void insertFavoriteGroup(String group, String user) {
        jdbcTemplate.update(INSERT_GROUP, user, group, UserFavoriteType.GROUP.getCode());
    }

    @Override
    public void insertFavoriteFile(ConfigMeta meta, String user) {
        jdbcTemplate.update(INSERT_FILE, user, meta.getGroup(), meta.getDataId(), meta.getProfile(), UserFavoriteType.FILE.getCode());
    }

    @Override
    public void deleteFavoriteGroup(String group, String user) {
        jdbcTemplate.update(DELETE_GROUP, user, group, UserFavoriteType.GROUP.getCode());
    }

    @Override
    public void deleteFavoriteFile(ConfigMeta meta, String user) {
        jdbcTemplate.update(DELETE_FILE, user, meta.getGroup(), meta.getDataId(), meta.getProfile(), UserFavoriteType.FILE.getCode());
    }

    @Override
    public void deleteFavorites(ConfigMeta meta) {
        jdbcTemplate.update(DELETE_BY_META, meta.getGroup(), meta.getDataId(), meta.getProfile());
    }

    private static final ResultSetExtractor<Integer> COUNT_EXTRACTOR = new ResultSetExtractor<Integer>() {

        @Override
        public Integer extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return rs.getInt("count");
            } else {
                return 0;
            }
        }
    };

    private static final RowMapper<String> FAVORITE_GROUPS_MAPPER = new RowMapper<String>() {

        @Override
        public String mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString("group_id");
        }
    };

    private static final RowMapper<ConfigMeta> FAVORITE_FILES_MAPPER = new RowMapper<ConfigMeta>() {

        @Override
        public ConfigMeta mapRow(ResultSet rs, int rowNum) throws SQLException {
            return new ConfigMeta(rs.getString("group_id"), rs.getString("data_id"), rs.getString("profile"));
        }
    };
}

See More Examples