Storing names in a MySQL database

Question

I need the cleanest and most effective solution for my business. I am trying to save student Marks in a MySQL database. The student can be “ABSENT” or “COPYED CASE”, I want to save such information also in the database.

I was thinking about assigning codes for the above case, like -1 for ABSENT, -2 for COPY CASE, etc. These codes will only be stored in the Marks column.

Ever while reading them using a select query, I should get their displayed values, i.e. ABSENT , COPY CASE etc.

Can this be achieved only at the end of the database?

Or do I need to implement these things only at the application level?

Is there any API for Java for such functions?

Answer 1

mysql supports enumerations:

 CREATE TABLE students (name varchar(64), mark ENUM('ABSENT','COPY CASE' )); INSERT INTO students VALUES ('john', 'COPY CASE'); 

In java, you can think of this column as a row type column, but the values ​​are stored efficiently in the database, and trying to save the value not in the enumeration will result in an error.

Answer 2

how to save its name() (version of String) in the database and create it back when reading from the database using valueOf()

Answer 3

If the enumeration string representations are rather heavy and the database is large (checks against these enumerations are frequent), I would probably have thrown each enumeration onto an integer constant (no, not ordinal() , but your own custom integer value) and methods for enumeration to get and create an enumeration of these values. Thus, you can effectively store transfers.

 public enum Status { ABSENT(1), COPY_PASTE(2); int value; private Status(int value) { this.value = value; } public int getValue() { return this.value; } public static Status ofStatusCode(int value) { // retrieve status from status code } } 

Answer 4

I was thinking about saving codes for the above case, like -1 for ABSENT, -2 for COPY CASE, etc. These codes will only be stored in the Marks column.

I would rather have a separate column, say status , that contains values ​​like ABSENT , COPY CASE and a default value like APPEARED

And the label column will remain separate.

In the view layer you can do something like

  if (status is 'APPEARED')
 show marks field
 else
 show status field

Answer 5

MySQL has its own ENUM type . And as @Jigar pointed out, you can use Enum.getName to get a Java String representing an enumeration to save, get a field from the database as a string, and use Enum.valueOf to load the enum constant back.

You can also use Enum.ordinal() + 1 to get an integer to save (MySQL also supports integer indexes directly with the ENUM type) and issues a query like:

 SELECT enum_col-1 FROM tbl_name; 

to get the inverse ordinal (it will retrieve -1 for strings with invalid enum values).

Plus MyEnumType value = MyEnumType.values()[ordinal] to convert it back to a Java enumeration type.

In any case, I think the String-based solution is cleaner and more elegant, as well as safer in the long run (if you ever need to reorder java Enum or add new enum values ​​in the middle of existing ones, you will thank yourself for going to start with a String strategy).