How to map some table columns to a Map with the column names as key

Question

I have this table:

CREATE TABLE user_type (
    id TINYINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) UNIQUE NOT NULL,
    ...
    --Some more columns

    can_create BOOLEAN NOT NULL,
    can_edit BOOLEAN NOT NULL,
    can_delete BOOLEAN NOT NULL,
    ...
    --Has 23 more columns that define different permissions

    PRIMARY KEY (id)
);

And would like to map it to something like this:

@Entity
@Table(name = "user_type")
public class UserType{
    @Id
    private Byte id;

    private String name;

    // Some more fields

    //Then this map should contain the remaining 26 permission columns
    //with the column names (can_create, can_edit, etc.) as keys.
    @???
    private Map<String, Boolean> permissions;
}

Is it possible to map some columns to a Map like that? In all the examples and questions I found they just map two values together, not a column name with its value.

Answer 1

You can try to use the following approach.

1. Create an enum that will represent your permission types:

public enum PermissionType
{
   CREATE,
   EDIT,
   DELETE

   // other permissions ...
}

2. Create Permissions class that will hold the permissions state for a user.

import java.io.Serializable;
import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

public class Permissions implements Serializable
{
   private Map<PermissionType, Boolean> permissions;
   
   public Permissions()
   {
      permissions = new HashMap<>(PermissionType.values().length);
      for (PermissionType permType : PermissionType.values())
      {
         permissions.put(permType, false);
      }
   }
   
   public void setPermission(PermissionType name, Boolean value)
   {
      permissions.put(name, value);
   }
   
   public Map<PermissionType, Boolean> getPermissions()
   {
      return permissions;
   }
   
   @Override
   public int hashCode()
   {
      return permissions.hashCode();
   }

   @Override
   public boolean equals(Object obj)
   {
      if (this == obj) return true;
      if (obj == null) return false;
      if (getClass() != obj.getClass()) return false;

      Permissions other = (Permissions) obj;
      return Objects.equals(other.permissions, permissions);
   }
}

3. Create hibernate custom basic type for the Permissions in the following way:

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Map;
import java.util.Objects;

import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SharedSessionContractImplementor;
import org.hibernate.usertype.UserType;

public class PermissionUserType implements UserType
{
   private static final int[] SQL_TYPES;
   
   static {
      SQL_TYPES = new int[PermissionType.values().length];
      for (int ind = 0; ind < SQL_TYPES.length; ind++)
      {
         SQL_TYPES[ind] = Types.BOOLEAN;
      }
   }
   
   @Override
   public int[] sqlTypes()
   {
      return SQL_TYPES;
   }

   @Override
   public Class<?> returnedClass()
   {
      return Permissions.class;
   }

   @Override
   public boolean equals(Object x, Object y) throws HibernateException
   {
       return Objects.equals(x, y);
   }

   @Override
   public int hashCode(Object x) throws HibernateException
   {
       return Objects.hashCode(x);
   }

   @Override
   public Permissions nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws HibernateException, SQLException
   {
      /*
       * The names are column aliases generated by hibernate, like can_dele5_5_, can_crea3_5_, ...
       **/
      Permissions permissions = new Permissions();
      for (int ind = 0; ind < names.length; ind++)
      {
         Boolean val = rs.getBoolean(names[ind]);
         PermissionType name = PermissionType.values()[ind];
         permissions.setPermission(name, val);
      }
      return permissions;
   }

   @Override
   public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException
   {
       if (Objects.isNull(value))
       {
          for (int ind = 0; ind < SQL_TYPES.length; ind++)
          {
             st.setNull(index + ind, SQL_TYPES[ind]);
          }
       }
       else
       {
          Permissions permissions = (Permissions) value;
          for (Map.Entry<PermissionType, Boolean> permEntry : permissions.getPermissions().entrySet())
          {
             Integer ind = permEntry.getKey().ordinal();
             st.setObject(index + ind, permEntry.getValue(), SQL_TYPES[ind]);
          }
       }
   }

   @Override
   public Permissions deepCopy(Object value) throws HibernateException
   {
      if (value == null) return null;
       
      Permissions oldPerms = (Permissions) value;
      Permissions newPerms = new Permissions();
      for (Map.Entry<PermissionType, Boolean> permEntry : oldPerms.getPermissions().entrySet())
      {
         newPerms.setPermission(permEntry.getKey(), permEntry.getValue());
      }
      return newPerms;
   }

   @Override
   public boolean isMutable()
   {
       return false;
   }

   @Override
   public Serializable disassemble(Object value) throws HibernateException
   {
       return deepCopy(value);
   }

   @Override
   public Object assemble(Serializable cached, Object owner) throws HibernateException
   {
       return deepCopy(cached);
   }

   @Override
   public Object replace(Object original, Object target, Object owner) throws HibernateException
   {
       return deepCopy(original);
   }
}

4. And then use this custom basic type in your entity mapping:

import org.hibernate.annotations.Columns;
import org.hibernate.annotations.Type;

@Entity
@Table(name = "user_type")
public class UserTypeEntity
{
   @Id
   @Column(name = "id")
   private Long id;

   @Column(name = "name")
   private String name;

   @Type(type = "com.me.PermissionUserType")
   @Columns(columns = {
      // the order should be matched with the enum PermissionType
      @Column(name = "can_create"),
      @Column(name = "can_edit"),
      @Column(name = "can_delete")
   })
   private Permissions permissions;
   
   // ...
}