Taogen's Blog

MySQL Query Optimization Tips

Posted on 2022-08-23 Edited on 2024-04-16 In Performance Optimization , MySQL

Common SQL Optimization

Select only need columns

Convert a complex SQL statement to multiple simple SQL statements

Convert queries to use indexes. Using indexes operators: =, >, <, >=, <=, BETWEEN, IN. Not using indexes operators: LIKE, <>, NOT IN. Using MySQL built-in functions on column make query cannot use index.

NOT IN

Using LEFT JOIN or NOT EXISTS to replace NOT IN

A NOT IN query example

select * 
from table_a
where table_a.id is not in (
select aid from table_b where table_b.status = 1)
-- or
select * 
from table_a
where table_a.id is not in (1,2,3, ...)

Use NOT EXISTS

select * 
from table_a
where not exists (
    select * from table_b where table_a.id = table_b.aid and table_b.status = 1
)

Use LEFT JOIN

select *
from table_a
left join table_b on table_a.id=table_b.aid and table_b.status = 1
where table_b.id is null;

LIKE

Using Full-text search MATCH ... AGAINST... or search engine replace LIKE

OR

Using IN, or UNION, or AND NOT to replace OR

ORs are notoriously bad performers, because it splinters the execution path. The UNION alleviates that splintering, and the combines the two results sets. That said, IN is preferable to ORs because though being logically the same, the execution of IN is generally more optimized.

The OR query example 1:

select *
from {table_name}
where name = "Tom" or name = "Jack"

Using IN

select *
from {table_name}
where name in ("Tom", "Jack")

The OR query example 2:

select *
from {table_name}
where name like "T%" or name = "J%"

Using UNION

select *
from {table_name}
where name like "T%"
union
select *
from {table_name}
where name like "J%"

The OR query example 3:

select *
from {table_name}
where {col1} = "a" or {col2} = "b"

Using AND NOT

select *
from {table_name}
where {col1} = "a" and {col2} != "b";
select *
from {table_name}
where {col2} = "b";

Built-in Functions

Avoid using functions in predicates

Use BETWEEN to replace DATE(), DATE_FORMAT()

A DATE_FORMAT example:

DATE_FORMAT(`date`, "%Y-%m") = "2022-07"

YEAR(`date`) = "2022" and MONTH(`date`) = "7"

Use BETWEEN

`date` BETWEEN '2022-07-01' and '2022-07-31'

Convert function to use on right side

WHERE f(g(h(column))) > value

WHERE column > H(G(F(value)))

Page Query

Basic Optimization

Query only the necessary columns instead of select *
Query part of the TEXT column by SUBSTR(column, start, length) if it could be.

High Offset Page Query Improvement

Late row lookups: Inner join ids instead of select *

Inner join is first to find offset + size rows id, then find all rows data by id set
select * is just to find offset + size rows data.

select *

select *
from {table_name} as a
where ...
limit {x}, 10

Inner join ids

select *
from {table_name} as a
inner join (
    select id 
    from {table_name}
    where ...
    order by id
    limit {x}, 10
) as b on a.id=b.id;

In most cases, the inner join has a 5x performance improvement, but in some cases there is no difference in performance.

The performance gap is more evident for large row-width tables and queries with large offset.

Next Page Query Improvement

Specify start row id for next page query

first page query

select *
from {table_name} as a
inner join (
    select id 
    from {table_name}
    where ...
    order by id
    limit x, 10
) as b on a.id=b.id;

next page query

select *
from {table_name} as a
inner join (
    select id 
    from {table_name}
    where id > {last_page_max_id} and ...
    order by id
    limit 10
) as b on a.id=b.id;

Cache Page Query

Caching total row number for the same condition queries.
Caching every page start row id value and end row id value for the same condition queries.

References

General

Optimizing SELECT Statements - MySQL 8.0 Reference Manual

High Offset

MySQL ORDER BY / LIMIT performance: late row lookups

OR

Optimize MySQL COUNT (*) query

Type Handling in MyBatis

Posted on 2022-08-18 Edited on 2024-04-16 In Java , Java Data Access

jdbcType

The jdbcType is a property for mapping Java object field values to SQL column values.

You need to specify the jdbcType when passing null values for parameters. Some databases need to know the value’s type even if the value itself is NULL. The JDBC specification itself that requires the type to be specified.

preparedStatement.setNull(int parameterIndex, int sqlType)

Most of the times you don’t need to specify the jdbcType as MyBatis is smart enough to figure out the type from the objects you are working with. But if you send your parameters to the MyBatis statement inside a HashMap, for example, and one of the parameters is null, MyBatis won’t be able to determine the type of the parameter by looking at the HashMap because the HashMap is just a generic container and null itself carries no type information. At that point it would be o good idea to provide the jdbcType so that switching the database implementation later on does not cause any issues with null values.

JDBC Types

BIT, BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT
FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL
CHAR, VARCHAR, NVARCHAR, LONGVARCHAR, NCHAR
BINARY, VARBINARY, LONGVARBINARY, BLOB, CLOB, NCLOB
TIMESTAMP, DATE, TIME
OTHER, UNDEFINED, NULL, CURSOR, ARRAY

MySQL and Java DB currently do not support the ARRAY SQL data type.

Update default mappings of Java values to column values

Convert java.util.Date objects to date strings

The java.util.Date objects will default convert to datetime strings (TIMESTAMP JDBC type) like 2022-08-18 11:12:05.545. If you want to convert a Date object value to a date string (DATE JDBC type) like 2022-08-18, you can specify the jdbcType to DATE.

javaType

The javaType is a property for mapping result set column values to Java field values.

MyBatis can usually figure out the type if you’re mapping to a JavaBean. However, if you are mapping to a HashMap, then you should specify the javaType explicitly to ensure the desired behavior.

typeHandler

typeHandler can convert column values to Java values and verse vice.

Convert between the Java `Enum` type value and the column value

<resultMap id="BaseResultMap" type="com.demo.Task">
    ...
    <result column="category" typeHandler="com.demo.typehandler.CategoryEnumTypeHandler"
            property="category"/>
</resultMap>

#{category,typeHandler=com.demo.typehandler.CategoryEnumTypeHandler}

public class TaskCategoryEnumTypeHandler extends BaseTypeHandler<TaskTypeCategory> {

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, TaskTypeCategory taskTypeCategory, JdbcType jdbcType) throws SQLException {
        preparedStatement.setString(i, taskTypeCategory.getName());
    }

    @Override
    public TaskTypeCategory getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
        return TaskTypeCategory.fromDatabaseValue(resultSet.getString(columnName));
    }

    @Override
    public TaskTypeCategory getNullableResult(ResultSet resultSet, int columnIndex) throws SQLException {
        return TaskTypeCategory.fromDatabaseValue(resultSet.getString(columnIndex));
    }

    @Override
    public TaskTypeCategory getNullableResult(CallableStatement callableStatement, int columnIndex) throws SQLException {
        return TaskTypeCategory.fromDatabaseValue(callableStatement.getString(columnIndex));
    }
}

public enum TaskTypeCategory {

    DEVELOPMENT("development", "研发"),
    OPERATION("operation", "运维"),
    ;

    private String value;

    private String name;

    TaskTypeCategory(String name, String value) {
        this.name = name;
        this.value = value;
    }

    public String getName() {
        return name;
    }

    /**
     * @JsonValue Deserializing JSON to Enum
     */
    @JsonValue
    public String getValue() {
        return value;
    }

    public static TaskTypeCategory fromColumnValue(String columnValue) {
        if (databaseValue == null) {
            return null;
        }
        TaskTypeCategory taskTypeCategory = Arrays.asList(TaskTypeCategory.values())
            .stream()
            .filter(item -> item.getName().equals(databaseValue))
            .findFirst()
            .get();
        return taskTypeCategory;
    }
}

public class Task {
    ...
    private TaskTypeCategory category;
    ...
}

References

[1] Mapper XML Files - MyBatis

[2] Is jdbcType necessary in a MyBatis mapper?

How to Pass Nested Object to Spring MVC Controller

Posted on 2022-08-17 Edited on 2024-04-16 In Java Web , Spring Boot

Nested Object

The POJO (a plain Java object)

@Data
public class User {
    private Integer id;
    private String name;
    private Address address;

    @Data
    public static class Address {
        private String province;
        private String city;
        private String area;
    }
}

Receive from Request URL Query String

Frontend

id=xxx&name=xxx&address.province=xxx&address.city=xxx&address.area=xxx

Code example

let json = {
    id: 1, 
    name: "Tom", 
    address: {
        province: "A1",
        city: "A1-1",
        area: "A1-2-2"
    }
}

function serializeTwoLevelsNestedJson(json) {
    return Object.keys(json).map(function(key) {
        if (typeof json[key] === "object" && !Array.isArray(json[key])) {
            return Object.keys(json[key]).map(function(subJsonKey) {
                return encodeURIComponent(key) +'.' + encodeURIComponent(subJsonKey)+ '=' + encodeURIComponent(json[key][subJsonKey]);
            }).join('&');
        } else {
            return encodeURIComponent(key) +'=' + encodeURIComponent(json[key])
        }
    }).join('&');
}

Backend

@RequestMapping("/test")
public String test(User user) {
}

Receive from Form Data

Frontend

id
name
address.province
address.city
address.area

Backend

@RequestMapping("/test")
public String test(User user) {
}

Receive from Request Body JSON

Frontend

{
    "id": 1,
    "name": "Jack",
    "address": {
        "province": "AAA",
        "city": "BBB"
    }
}

Backend

@RequestMapping("/test")
public String test(@RequestBody User user) {
}

Nested Object List

The POJO (a plain Java object)

@Data
public class User {
    private Integer id;
    private String name;
    private List<Address> addressList;

    @Data
    public static class Address {
        private String province;
        private String city;
        private String area;
    }
}

Receive from Request URL Query String

Frontend

This type of query strings looks like id=xxx&name=xxx&addressList[0].province&addressList[0].city&addressList[0].area, but it actually is

id=1&name=ABC&addressList%5B0%5D.province=AA&addressList%5B0%5D.city=BB&addressList%5B0%5D.area=CC

You need to Encode square brackets [ and ] by the encodeURIComponent(str) method.

Code example

let json = {id: 1, name: "Tom", addressList: [
    {
        province: "A1",
        city: "A1-1",
        area: "A1-2-2"
    },
    {
        province: "B1",
        city: "B1-1",
        area: "B1-2-2"
    }
]};

function serializeTwoLevelsNestedJson(json) {
    return Object.keys(json).map(function(key) {
        if (typeof json[key] === "object") {
            if (Array.isArray(json[key])) {
                return json[key].map((arrayItem, index) => {
                    return Object.keys(arrayItem).map(function(arrayItemKey) {
                        return encodeURIComponent(key) +encodeURIComponent('[') + index + encodeURIComponent('].') + encodeURIComponent(arrayItemKey) + '=' + encodeURIComponent(arrayItem[arrayItemKey]);
                    }).join('&')
                }).join('&');
            } else {
                return Object.keys(json[key]).map(function(subJsonKey) {
                    return encodeURIComponent(key) +'.' + encodeURIComponent(subJsonKey)+ '=' + encodeURIComponent(json[key][subJsonKey]);
                }).join('&');
            }
        }
        else {
            return encodeURIComponent(key) +'=' + encodeURIComponent(json[key])
        }
    }).join('&');
}

Backend

@RequestMapping("/test")
public String test(User user) {
}

Receive from Form Data

Frontend

id
name
addressList[0].province
addressList[0].city
addressList[0].area
addressList[1].province
…

Backend

@RequestMapping("/test")
public String test(User user) {
}

Receive from Request Body JSON

Frontend

{
    "id": 1,
    "name": "Jack",
    "addressList": [
        {
            "province": "AAA",
            "city": "BBB",
            "area": "CCC"
        },
        ...
    ]
}

Backend

@RequestMapping("/test")
public String test(@RequestBody User user) {
}

Software Design and Specifications

Posted on 2022-08-02 Edited on 2024-04-16 In System Design , General

Design

Database Design Principles

MySQL Database Design

API Design Principles

An API should do one thing

Why: If an API can do more than one thing, it can lead to messy and error-prone code. Also, it is less readable and maintainable.

How: Don’t do different things by passing different parameter values.

Development

Development Specifications

Dependency Versions

Use a common parent maven project to manage the version of dependencies.

Why: Easy to update all project dependencies to the latest version. Easy to remove vulnerable dependency versions for all projects.

Configurations

Use OS environment variables or external configuration files to configure all external resources like databases.

Why: Avoid exposing sensitive information.

Trivial Files

Generated trivial files should include in the gitignore, or put out of the project.

Why: Make sure the source code is clean and easy to commit code.

Unit Test

Unit test code should be able to be executed directly without preconditions like creating a database.

Why: If unit test code can’t be executed directly, test code become hard to use.

Configurations

Need changed static configurations (key-values) should read from dictionary table of database or project configuration files

Why: Hardcoding makes code difficult to extend and maintain.

Test

Deployment

Operations

Database

Before execute a delete SQL statement on product databases, must make sure the data is correct by executing a select SQL rewrite by the delete SQL.

不要把那些狗屎放在你的简历上

Posted on 2022-07-29 Edited on 2024-04-16 In Personal Essay , Career

在网上经常看到很多人发求职的帖子，贴出来的简历感觉都写的差不多，表面看起来很好，什么都会，但实际上没啥东西。简历存在的主要问题：

一、专业技能部分

所有的主流技术都列了一遍。→ 看不出擅长的技术，看不到技术深度。让人感觉什么都会点，但都不咋样。
什么技术都写熟悉。→ 写的是熟悉，但如果没有体现出来的话，看起来太华而不实了，太虚了，没啥意思。Note：不是项目中用了或者自己看过入门教程就是熟悉了。至少要有大量的使用经验、完整地看过官方文档、看过部分源码、以及理解其实现原理等才能算熟悉。

二、项目经验部分

项目介绍废话一大堆，不知所云。→ 看不出你做了什么，看不出成果。Tips: 建议使用 STAR 原则。
项目中写的全是简单的增删改查、API调用等功能。没有什么难点和亮点。→ 没有什么竞争力，看不到潜力，只是一个简单的搬砖工。Tips：即使项目中没有亮点也要制造亮点。

三、其他

写一些与求职岗位和职责要求无关的东西。比如：你求职的岗位是 Java，却写了 PHP 的项目经验、写对游戏开发感兴趣等。→ 这不但不会加分，反而会显得花里胡哨、不够专注。面试官不想知道你都了解过什么，你的兴趣有哪些。面试官注重的是：你的能力是否能够匹配这个岗位。另外，如果你有擅长的技术、有亮点、有潜力的话，可以加分，可以优先考虑。

JavaScript Code Examples: Types, Values, and Variables

Posted on 2022-07-28 Edited on 2024-04-16 In Web Frontend , JavaScript

Primitive Types

number
string
boolean
bigint
symbol
null
undefined

String

Multiple line strings

let multipleLineStr = `hello
world
`;
// or
let multipleLineStr2 = 'hello\n'+
    'world';
// or
let multipleLineStr3 = 'hello\n\
world';

Remove HTML tags from a string

let htmlStr = "<div><a href='baidu.com'>test</a></div>";
htmlStr = htmlStr.replace(/<\/?[^>]+(>|$)/g, ""); 
console.log(htmlStr) // test

Replace newline with <br>

let str = `hello
world
JS`;
str.replace(/(\r|\n|\r\n)/g, "<br>");
// or 
str.replaceAll("\r", "<br>").replaceAll("\n", "<br>").replaceAll("\r\n", "<br>");

Reverse string

let s = 'Hello';
// ASCII characters
s.split("").reverse().join(""); // 'olleH'

let s = '😀😂';
// supports UTF-16 or other multi-byte characters
[...s].reverse().join(""); // '😂😀'

Date Object

date to timestamp

const timestamp = new Date().getTime();

timestamp to date

const date = new Date(timestamp)

Type Check

Check null or undefined

value == null

Check undefined

typeof value === 'undefined'

Check null

value === null

Check string type

typeof value === 'string' || value instanceof String

Check number type

typeof value === 'number'

Check object or JSON object

typeof json === "object" && !Array.isArray(value)

Check array or JSON array type

let value = [1, 2, 3];
typeof value === "object" && Array.isArray(value);

Check date object type

let date = new Date();
Object.prototype.toString.call(date) === '[object Date]'

Check function

let obj = ()=>{ console.log("I'm a arrow function") };
typeof obj === 'function'

Type Format

String Format

String format

var width = document.documentElement.clientWidth;
var height = document.documentElement.clientHeight;
let resolution = `your screen resolution is ${width} * ${height}`

String Format with regular expressions

let str = 'My Name is ${name}. His name is ${name}';
let replacement = "John";
str.replace(/${\w}/, replacement);

Add leading zero

let date = 1;
let totalLenght = 2;
let result = String(date).padStart(totalLength, '0'); // '01'

function pad(num, size) {
    num = num.toString();
    while (num.length < size) num = "0" + num;
    return num;
}

Number Format

let num = 34.7698;
let numFixed = num.toFixed(2);

Date Format

using slice

// date object to UTC datetime string yyyy/MM/dd HH:mm:ss
const str = (new Date()).toISOString().slice(0, 19).replace(/-/g, "/").replace("T", " ");

// date object to local time string yyyy/MM/dd HH:mm:ss
const now = new Date();
const offsetMs = now.getTimezoneOffset() * 60 * 1000;
const dateLocal = new Date(now.getTime() - offsetMs);
const str = dateLocal.toISOString().slice(0, 19).replace(/-/g, "/").replace("T", " ");

Using date object properties

var today = new Date();
var dd = String(today.getDate()).padStart(2, '0');
var mm = String(today.getMonth() + 1).padStart(2, '0'); //January is 0!
var yyyy = today.getFullYear();

today = mm + '/' + dd + '/' + yyyy;

Type Conversion

Number to String

let num = 1;
let reslut = num.toString() // '1'

let num = 1;
let reslut = num + ''; // '1'

String to Number

parseInt(string, radix)
Number(string)

let s = "1";
let reslut = parseInt(s); // 1

let s = "1";
let reslut = Number(s); // 1

String to Float Number

parseFloat()

Deep Copy

Using JSON.stringify() and JSON.parse()

Pros: deep copies nested objects
Cons: doesn’t copy functions

let clone = JSON.parse(JSON.stringify(object));

Using Object.assign()

Pros: copies the immediate members of an object—including functions.
Cons: doesn’t deep copy nested objects

let clone = Object.assign({}, object);

Using spread operator

Pros: simple syntax, the preferred way to copy an object
Cons: doesn’t deep copy nested objects

const clone = {...object}

Using lodash

Pros: clones nested objects including functions
Cons: adds an external dependency to your project

var clone = _.cloneDeep(object);

为什么我决定放弃使用 Ubuntu 桌面系统

Posted on 2022-07-09 Edited on 2024-04-16 In Personal Essay , Others

最近，我尝试将自己主力使用的操作系统从 Window 10 转到 Ubuntu 22.04，结果以失败告终了。

为什么尝试使用 Ubuntu 桌面系统

关于想要使用 Ubuntu 桌面系统。第一，坦白的说，当时主要是觉得很装逼。觉得使用 Linux 系统，能让自己变得与众不同，同时也能显得自己的计算机技术水平很厉害的样子。第二，另一个目的就是 Windows 下对 docker 的支持不太好，想要在 Linux 系统下学习 docker。

为什么我放弃了使用 Ubuntu 桌面系统

放弃使用 Ubuntu 桌面系统，主要的原因是各方面的体验不太好。这些问题可能存在相应的解决方案，但也不想去折腾了。当下自己还有很多事情要去做，没必要在操作系统上浪费太多的时间。

我在使用 Ubuntu 桌面系统中遇到的一些问题：

Ubuntu 屏幕只能支持整数倍放大，按比例放大会导致各种软件的字体模糊。我的笔记本电脑需要150% 的放大，这样的话字体的大小看起来会比较舒服。
很多软件没有 Linux 版本。想要使用的话可能比较麻烦，比如：安装虚拟机去使用这些软件。
软件的界面会莫名的卡死，无响应，导致系统也会部分卡死，比如：无法点击某些功能，无法关机。
下载安装软件很慢，即使更换了国内的 Ubuntu 镜像源。

Ubuntu 桌面系统的优点和缺点

优点：

命令行的功能丰富。
软件的开发环境接近软件的生产环境。

缺点：

系统的可视化操作方面体验很差，功能不丰富，操作不够人性化。
Ubuntu 桌面系统不够稳定，容易卡死。
很多软件没有 Linux 版本。

关于操作系统的选择

下面是2022年桌面操作系统的市场份额：

Windows: 76.31%
OS X: 14.66%
Unknown: 4.88%
Linux: 2.43%
Chrome OS: 1.72%
FreeBSD: 0.01%

个人认为，主力使用的操作系统，包含工作、生活、学习和娱乐等方面的使用，最好还是选择主流的操作系统，如 Windows 和 OS X。因为用的人多，它的使用体验和软件生态不会太差，更加省心省力。

如果你只是打算轻度地使用某个操作系统（只做某些特定的事情，如：软件开发），而且这个操作系统也能满足你的使用需求，那你完全可以去使用这个操作系统。

操作系统的选择是因人而异的，你可以自己去尝试，然后做出适合自己的选择。

CORS Error Solutions

Posted on 2022-07-08 Edited on 2024-04-16 In Problem Solving

When you send an HTTP request with a different domain than your page’s domain (or IP address + port number), a CORS error may occur. A CORS error means that the API server rejected your request. To access other domain API from your web page, the backend server you requested must set some CORS headers in the HTTP response to allow CORS requests. Below are some errors caused by incorrectly set HTTP response headers for CORS requests.

Error: No ‘Access-Control-Allow-Origin’ header is present

Error information in web browser console

Access to XMLHttpRequest at 'http://localhost:8081/api' from origin 'http://localhost' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.
GET http://localhost:8081/api net::ERR_FAILED 302

Solutions

First, check that the URL, Method, and Content-Type you requested are correct.

Make sure the server API is up and running.

Enable CORS requests for your server API. Add Access-Control-Allow-Origin in HTTP response header.

Access-Control-Allow-Origin: *
or
Access-Control-Allow-Origin: http://your_page_domain

For example, in Java web projects.

response.setHeader("Access-Control-Allow-Origin", "*");
// or
response.setHeader("Access-Control-Allow-Origin", "http://localhost");

Reasons

The API is not shared with other origins. You need to update the API CORS policy by set Access-Control-Allow-Origin in response headers.

Error: Method xxx is not allowed

Error information in web browser console

Access to XMLHttpRequest at 'http://localhost:8081/api/delete' from origin 'http://localhost' has been blocked by CORS policy: Method DELETE is not allowed by Access-Control-Allow-Methods in preflight response.

Solutions

Add Access-Control-Allow-Methods: {method_name_in_error_message} in HTTP response header. Note that method names must be capitalized.

For example, in Java web projects.

response.setHeader("Access-Control-Allow-Methods", "DELETE");

Reasons

The default allowed HTTP methods for CORS are GET, POST, and HEAD. For other HTTP methods like DELETE or PUT, you need to add it to HTTP response header Access-Control-Allow-Methods.

Error: Request header field xxx is not allowed

Error information in web browser console

Access to XMLHttpRequest at 'http://localhost:8081/api/delete' from origin 'http://localhost' has been blocked by CORS policy: Request header field my-header1 is not allowed by Access-Control-Allow-Headers in preflight response.

Access to XMLHttpRequest at 'http://localhost:8081/api/get?name=Jack' from origin 'http://localhost' has been blocked by CORS policy: Request header field content-type is not allowed by Access-Control-Allow-Headers in preflight response.

Solutions

Add Access-Control-Allow-Headers: {header_field_name_in_error_message} in HTTP response header.

For example, in Java web projects.

response.setHeader("Access-Control-Allow-Headers", "my-header1");

Reasons

The default allowed HTTP headers for CORS requests are:

Accept
Accept-Language
Content-Language
Content-Type (value only be application/x-www-form-urlencoded, multipart/form-data, or text/plain)
Range

For other HTTP headers, you need to add them to HTTP response header Access-Control-Allow-Headers.

Error: The value of the ‘Access-Control-Allow-Origin’ header in the response must not be the wildcard ‘*’

Error information in web browser console

Access to XMLHttpRequest at 'http://localhost:8081/api/get' from origin 'http://localhost' has been blocked by CORS policy: The value of the 'Access-Control-Allow-Origin' header in the response must not be the wildcard '*' when the request's credentials mode is 'include'. The credentials mode of requests initiated by the XMLHttpRequest is controlled by the withCredentials attribute.

Solutions

Set the value of Access-Control-Allow-Origin to your page domain instead of * in HTTP response header. And set the value of Access-Control-Allow-Credentials to true.

For example, in Java web projects.

response.setHeader("Access-Control-Allow-Origin", "http://localhost");
response.setHeader("Access-Control-Allow-Credentials", "true");

Reasons

When you send a CORS request with credentials, you must set a specific domain in Access-Control-Allow-Origin.

Request with credentials: withCredentials: true

const xhr = new XMLHttpRequest();
const url = 'http://localhost:8081/api/get';
xhr.open('GET', url, true);
xhr.withCredentials = true;
xhr.onload = function () {
    if (xhr.readyState === xhr.DONE) {
        if (xhr.status === 200) {
            console.log(xhr.response);
        }
    }
};
xhr.send();

$.ajax({
    url: "http://localhost:8081/api/get",
    method: "GET",
    xhrFields: {
       withCredentials: true
    },
}).done(function(res) {
    console.log(res);
});

Error: The value of the ‘Access-Control-Allow-Credentials’ header in the response is ‘’ which must be ‘true’

Error information in web browser console

Access to XMLHttpRequest at 'http://localhost:8081/api/get' from origin 'http://localhost' has been blocked by CORS policy: The value of the 'Access-Control-Allow-Credentials' header in the response is '' which must be 'true' when the request's credentials mode is 'include'. The credentials mode of requests initiated by the XMLHttpRequest is controlled by the withCredentials attribute.

Solutions

Add Access-Control-Allow-Credentials: true in HTTP response header.

For example, in Java web projects.

response.setHeader("Access-Control-Allow-Credentials", "true");

Reasons

When the request’s credentials flag is true, the HTTP response header Access-Control-Allow-Credentials should be true.

Conclusion

There are two scenarios for setting CORS headers. The headers you need to set in each case are given below.

1. No credentials

response.setHeader("Access-Control-Allow-Origin", "*"); // You can also set a specific host.
response.setHeader("Access-Control-Allow-Methods", "POST, GET, PATCH, DELETE, PUT, PATCH");
response.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept, {my-custome-header}");
response.setHeader("Access-Control-Max-Age", "86400");

2. With credentials

response.setHeader("Access-Control-Allow-Origin", "{your_host}"); // If you use a web framework, it may support setting allow-origin patterns. For example, http://localhost:[*], http://192.168.0.*:[*].
response.setHeader("Access-Control-Allow-Credentials", "true");
response.setHeader("Access-Control-Allow-Methods", "POST, GET, PATCH, DELETE, PUT, PATCH");
response.setHeader("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept, {my-custome-header}");
response.setHeader("Access-Control-Max-Age", "86400");

[HTTP Request Error] Failed to load response data

Posted on 2022-06-30 Edited on 2024-04-16 In Problem Solving

Background

When the frontend calls the backend API, the response status is 200, and my web browser dev tool shows “failed to load response data.”

HTTP response details in the web browser dev tool

Status Code: 200
Content-Type: application/json;charset=UTF-8
Response Body: Failed to load response data: No data found for resource with given identifier

I copy the URL of the requested API to a new tab of my web browser (get a request from a browser), but I only get part of the JSON data.

{
	part of JSON

Error Info

Failed to load response data: No data found for resource with given identifier

Solutions

Make sure your server disk has free space.

Reasons

Because my server’s disk is full, the HTTP response returns part of JSON.

References

Chrome dev tools fails to show response even the content returned has header Content-Type:text/html; charset=UTF-8

Java Code Examples: Common Logic Processing

Posted on 2022-06-07 Edited on 2024-04-16 In Java , Fundamentals

Multiple attempts to get the result

public class LogicProcessingUtils {
    /**
     * @param maxTryTimes
     * @param successPredicate
     * @param function
     * @param params           parameter object
     * @param <T>              function return type
     * @param <P>              function parameter type
     * @return
     */
    public static <T, P> T tryToGetResult(int maxTryTimes,
                                          Predicate<T> successPredicate,
                                          Function<P, T> function,
                                          P param) {
        int tryTimes = 0;
        T t = null;
        while (tryTimes < maxTryTimes) {
            t = function.apply(param);
            // System.out.println(t);
            if (successPredicate.test(t)) {
                break;
            }
            tryTimes++;
        }
        return t;
    }

    public static <T> T tryToGetResult(int maxTryTimes,
                                          Predicate<T> successPredicate,
                                          Supplier<T> supplier) {
        int tryTimes = 0;
        T t = null;
        while (tryTimes < maxTryTimes) {
            t = supplier.get();
            // System.out.println(t);
            if (successPredicate.test(t)) {
                break;
            }
            tryTimes++;
        }
        return t;
    }
}

public class MyTest {
    public static void main(String[] args) {
        MyTest myTest = new MyTest();
        int maxTryTimes = 3;
        ObjectNode result = LogicProcessingUtils.tryToGetResult(
                maxTryTimes,
                jsonNode -> jsonNode.get("code").asInt() == 200,
                myTest::getResult,
                0);
        System.out.println(result);
    }

    public ObjectNode getResult(Object param)  {
        System.out.println("param: " + param);
        ThreadLocalRandom random = ThreadLocalRandom.current();
        int randomNumber = random.nextInt(10);
        System.out.println("random number: " + randomNumber);
        String jsonStr = "{\"code\":500,\"data\":%d,\"msg\":\"请求失败\"}";
        ObjectMapper objectMapper = new ObjectMapper();
        try {
            return (ObjectNode) objectMapper.readTree(String.format(jsonStr, randomNumber));
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
        return null;
    }
}

Common SQL Optimization

NOT IN

LIKE

OR

Built-in Functions

Avoid using functions in predicates

Page Query

Basic Optimization

High Offset Page Query Improvement

Next Page Query Improvement

Cache Page Query

References

jdbcType

Update default mappings of Java values to column values

javaType

typeHandler

Convert between the Java Enum type value and the column value

References

Nested Object

Receive from Request URL Query String

Receive from Form Data

Receive from Request Body JSON

Nested Object List

Receive from Request URL Query String

Receive from Form Data

Receive from Request Body JSON

Design

Database Design Principles

API Design Principles

Development

Development Specifications

Test

Deployment

Operations

Primitive Types

String

Date Object

Type Check

Type Format

String Format

Number Format

Date Format

Type Conversion

Deep Copy

Error: No ‘Access-Control-Allow-Origin’ header is present

Error: Method xxx is not allowed

Error: Request header field xxx is not allowed

Error: The value of the ‘Access-Control-Allow-Origin’ header in the response must not be the wildcard ‘*’

Error: The value of the ‘Access-Control-Allow-Credentials’ header in the response is ‘’ which must be ‘true’

Conclusion

Background

Error Info

Solutions

Reasons

References

Multiple attempts to get the result

Convert between the Java `Enum` type value and the column value