Shulou(Shulou.com)06/02 Report--

This article mainly introduces the relevant knowledge of "how to deal with boolean types in database design". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "how to deal with boolean types in database design" can help you solve the problem.

The Boolean value of database is a headache, which involves many problems.

Naming

How is the boolean field in the database named? Boolean values are usually required to start with is. For example, Ali's database design specification forces Boolean types to start with is_, such as is_deleted. This is really intuitive to let people know the type of a field, and I like it very much.

But naming is not just in the database, how to name it in JavaBean? For uniformity (especially by using tools to generate Java classes from the database), the property name in JavaBean should be the same as the database field name, which is, of course, isDeleted.

So the question is, how to write the get and set methods of JavaBean? If you build with a development tool such as IntelliJ IDEA, it looks like this:

Public class User {private Boolean isDisabled; public Boolean getDisabled () {return isDisabled;} public void setDisabled (Boolean disabled) {isDisabled = disabled;}}

It is not surprising that some build tools produce the following code:

Public class User {private Boolean isDisabled; public Boolean getIsDisabled () {return this.isDisabled;} public void setIsDisabled (Boolean isDisabled) {this.isDisabled = isDisabled;}}

If you use the native type boolean instead of the object type Boolean, the code is as follows:

Public class User {private boolean isDisabled; public boolean isDisabled () {return this.isDisabled;} public void setDisabled (boolean disabled) {this.isDisabled = disabled;}}

Then use jackson to generate JSON, which will be {disabled:false} or {isDisabled:false}.

So the database isDisabled,JavagetDisabled () isDisabled () getIsDisabled (), JSONdisabled isDisabled. Isn't it a little messy?

It's so confusing that there are many development specifications that prohibit the JavaBean attribute from starting with is.

The database says to start with is, but JavaBean says not to start with is. Do you want it or not? It might be possible to start the fields in the database with is and remove is from JavaBean. This is one way, but when you automatically generate JavaBean from the database, you have to change it manually. And there is no beginning of is in JavaBean and JSON, and suddenly there is a beginning of is in the database, which is also a kind of confusion.

The final conclusion is that none of them should start with is, and the Boolean type cannot be empty. JavaBean uses the native type boolean instead of Boolean. This makes the database disabled,JavaisDisabled (), JSONdisabled, much simpler and clearer.

In addition, an empty Boolean can be considered using char (1), such as gender (m: male, f: female, n: confidential).

Database type

Although Boolean is a SQL standard data type, many databases do not provide a boolean type because it is too easy to replace and it is not necessary to use a single data type. For example, use char (1) or int.

Replaceable is replaceable, so in practice, how to replace is the best practice?

Here's how Hibernate handles it:

If you use a numeric type (such as int or tinyint), 0 means false,1 represents true.

If you use a character type (such as char (1)), Narea stands for false, y for true, or F for false, and TPo for true.

It is obvious that the use of numerical values is more intuitive, after all, consistent with the rules of the c language, the computer has always been a tradition.

The character type is slightly confusing, of course, the character type can also be used with 0 and 1, especially in oracle using char (1) is more space-saving than using number (1). Char (1) takes one byte and number (1) takes two bytes. The same is true in other numeric databases that do not have 1 byte (such as DB2).

Another problem with char (1) is that when the database automatically generates JavaBean, it is impossible to judge whether it is character type or Boolean type. In particular, fields that do not start with is are even more difficult to determine automatically, which is very unfriendly to automatically generate code. As we all know, automatically generated code has a high place in development, and no one wants to spend time on repetitive, meaningless things.

In MySQL, there is a boolean type, but this type is synonymous with tinyint (1), which means that boolean is also stored numerically.

Ali's database design specification also requires the use of tinyint (1) to store boolean types.

The rules of the database management tool liquibase are directly used with boolean types, and bit or numeric values are not used instead:

MySQLDatabase: BIT (1) SQLiteDatabase: BOOLEANH2Database: BOOLEANPostgresDatabase: BOOLEANUnsupportedDatabase: BOOLEANDB2Database: SMALLINTMSSQLDatabase: [bit] OracleDatabase: NUMBER (1) HsqlDatabase: BOOLEANFirebirdDatabase: SMALLINTDerbyDatabase: SMALLINTInformixDatabase: BOOLEANSybaseDatabase: BITSybaseASADatabase: BIT

Liquibase uses BIT (1) in MySQL instead of using MySQL's boolean type directly, which is a bit strange. Maybe liquibase thinks that MySQL's boolean is fake (it's actually tinyint (1)), rather than BIT (1). We will not compete with the database, just use MySQL's boolean, the database is what it says, it is better than we know itself, it can not let us suffer any big loss.

In this way, it is not good to use char (1) as boolean against the current. At least for the sake of automatic code generation, the development environment uses numerical values to do boolean (usually MySQL, directly use the bool type, the database is automatically converted to tinyint (1)). When generating other database scripts, it may not be a big problem to convert boolean to char (1) for convenience (char (1) is available in all databases) or to save storage space (MyBatis maps to BIT types, and I don't know if I can operate char (1)).

This is the end of the content about "how to deal with boolean types in database design". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.