I need the most clean and efficient solution for my case. I'm trying to store Marks of student in a MySQL DB. A student may be "ABSENT" or "COPY CASE", I want to store such info also in DB.
I thought of assigning codes for above case like -1 for ABSENT, -2 for COPY CASE, etc. This codes will be stored in Marks column only.
More ever, while reading them with a select query, I should get their display values i.e. ABSENT
, COPY CASE
, etc only.
Can all these be achieved at DB end only?
Or do I need to implement these things at Application level only?
Are there any API's available for Java for such functionality?
First of all, in order to save enum values in a relational database using JPA, you don't have to do anything. By default, when an enum is a part of an entity, JPA maps its values into numbers using the ordinal() method. What it means is that without customizations JPA stores enum value as numbers.
ENUM is great for data that you know will fall within a static set. If you are using Mysql 5+, storage is almost always better with an ENUM type for data in a static set, as the official MySQL reference shows. Not to mention that the data is readable, and you have an extra layer of validation.
You can use the '|' (bitwise OR ) operator to mask multiple roles together in a single 64-bit integer, which you can store in the database in an integer field (a bigint ). RoleEnum userRoles = RoleEnum. User | RoleEnum.
ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, 'b' sorts before 'a' for ENUM('b', 'a') . The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.
mysql supports enums:
CREATE TABLE students (name varchar(64), mark ENUM('ABSENT','COPY CASE' ));
INSERT INTO students VALUES ('john', 'COPY CASE');
In java, you can treat this column as a string type column, but in the database, values are stored efficiently, and trying to store a value not contained in the enum will result in an error.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With