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 isnotin ( select aid from table_b where table_b.status =1) -- or select* from table_a where table_a.id isnotin (1,2,3, ...)
Use NOT EXISTS
select* from table_a wherenotexists ( select*from table_b where table_a.id = table_b.aid and table_b.status =1 )
Use LEFT JOIN
select* from table_a leftjoin table_b on table_a.id=table_b.aid and table_b.status =1 where table_b.id isnull;
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" andMONTH(`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
WHEREcolumn> 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 innerjoin ( select id from {table_name} where ... orderby 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 innerjoin ( select id from {table_name} where ... orderby id limit x, 10 ) as b on a.id=b.id;
next page query
select* from {table_name} as a innerjoin ( select id from {table_name} where id > {last_page_max_id} and ... orderby 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.
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.
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
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 = newDate().getTime();
timestamp to date
const date = newDate(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 instanceofString
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 = newDate(); Object.prototype.toString.call(date) === '[object Date]'
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'
functionpad(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 = (newDate()).toISOString().slice(0, 19).replace(/-/g, "/").replace("T", " ");
// date object to local time string yyyy/MM/dd HH:mm:ss const now = newDate(); const offsetMs = now.getTimezoneOffset() * 60 * 1000; const dateLocal = newDate(now.getTime() - offsetMs); const str = dateLocal.toISOString().slice(0, 19).replace(/-/g, "/").replace("T", " ");
Using date object properties
var today = newDate(); 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
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.
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.
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.
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.
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");