Changing Data Directory for MySQL on CentOS7

I was recently asked to move the DEV and TEST environment of the application I am working on to  new servers.  The old servers were running CentOS6, new servers running on CentOS7 with SELinux enabled.  I realized after the fact that the server that is hosting MySQL had a separate /data volume that had much disk space on it, so I needed to change the data directory after several databases had already been instantiated.

Environment:
CentOS Linux release 7.6.1810 (Core) with SELinux enabled with enforcing
Server version: 5.7.25 MySQL Community Server (GPL)

1.  Login as a user that has root privileges, sudo to root

2.  Verify the current data directory
mysql -u root -p <enter in the root password>

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.01 sec)

3. Stop mysqld and verify it is stopped
systemctl stop mysqld
systemctl status mysqld

Apr 19 08:39:51 <servername> systemd[1]: Stopping MySQL Server...
Apr 19 08:39:55 <servername> systemd[1]: Stopped MySQL Server.

4.  Check if you have cp with the -a option
man cp

       -a, --archive
              same as -dR --preserve=all
       -d     same as --no-dereference --preserve=links
       --preserve[=ATTR_LIST]
              preserve the specified attributes (default: mode,ownership,timestamps), if possible  additional  attributes:  context, links, xattr, all
       -R, -r, --recursive
              copy directories recursively

Copies a directory exactly as it is (preserves ownership and groups), the files retain all their attributes, and symlinks are not dereferenced (-d).

5.  The new folder/volume I want to copy to is /data..  Copy the files from the source /var/lib/mysql to /data with -a option
cp -a /var/lib/mysql /data

6. Rename the current folder /var/lib/mysql to a different name to avoid confusion
mv /var/lib/mysql /var/lib/mysql-OLD

7.  Take a backup of the my.cnf file
cp /etc/my.cnf ~/my.cnf.ORIG

8.  Configure MySQL data directory to new folder location, add in port=3306, and configure datadir and socket to the new location. Also add a [client] group to the bottom of the file (after every options in the [mysqld] group) with port and socket matching the [mysqld] group.

vi /etc/my.cnf

[mysqld]

port=3306

datadir=/data/mysql

socket=/data/mysql/mysql.sock

[client]

port=3306

socket=/data/mysql/mysql.sock

9.  Add SELinux security context to the new folder.  semanageutility is not installed by default and was missing, so installed policycoreutils-python.  Perform a listing with security context.

yum -y install policycoreutils-python

semanage fcontext -a -t mysqld_db_t "/data(/.*)?"

restorecon -Rv /data

ls -lZ /data/mysql/

ls -lZ /var/lib/mysql-OLD

NOTE:  If you don't perform this step, you will see the following warnings/errors in the /var/log/mysqld.log file.

2019-04-19T13:31:39.698773Z 0 [Warning] Can't create test file /data/mysql/<servername>.lower-test

2019-04-19T13:31:39.837948Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.

9. Restart mysql

systemctl start mysqld

systemctl status mysqld

10.  Verify the new data directory
mysql -u root -p <enter in the root password>

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /data/mysql/ |
+-----------------+
1 row in set (0.01 sec)

These articles helped a lot:

https://dev.mysql.com/doc/refman/5.7/en/option-files.html

https://www.digitalocean.com/community/tutorials/how-to-change-a-mysql-data-directory-to-a-new-location-on-centos-7

https://www.tecmint.com/change-default-mysql-mariadb-data-directory-in-linux/

https://stackoverflow.com/questions/26474222/mariadb-10-centos-7-moving-datadir-woes

MySQL Stored Procedure with parameter dynamic filtering and sorting

I recently worked on a web application where we had to retrieve user specific dashboard data in real-time with dynamic paging, dynamic column sorting (ASC or DESC), and dynamic data filtering (i.e by year). For the normal use case, there would not be too many user specific data, but for a small sub-set of users they would have a large amount of user specific data. Also, out count parameters were needed in addition to the select column data output.  For this reason, MySQL stored procedures were chosen to achieve this real-time requirement.


Environment: 
MySQL 5.7.10.0 on Windows 10
MySQL 5.7.10.0 on CentOS 6.7
MySQL 5.7.10.0 on RHEL 7

A MySQL temp table was chosen (over a view) because:

1. Needed to pass parameter dynamic data for filtering and sorting in the SELECT statement to retrieve user data. With a Temp table that is possible vs a View's select statement cannot contain a variable or a parameter (it's a known limitation).
• NOTE: If a View's select statement could contain variables and parameters it would have been chosen because:
• Data in a view is always current because it is dynamically generated, whereas the data in a temp table reflects the state of the database at the time it was populated and is only created once per session.
• We want to always update the View real-time, even in the same session for a user.
2. Temp tables are created per session, so you can have the "same name" temp table across different sessions. MySQL will maintain different "copies" of it.

The "skeleton" of the stored procedure strategy is below:

Spring Batch Example with One Job and Two Steps

Below is an example Spring Batch project with one job with two steps.  Each step has a reader/processor/writer where it reads from the DB, processes db record specific metadata in the processor, and then writes the data records.  A job listener performs a beforeJob and afterJob database record writing and updating for auditing purposes.

The job is querying data to create user notifications, and each of the steps performs a query for each type of data being processed.

Please also read my blog on Spring Batch Decision for running two jobs at different times here.

Environment:
Spring Boot / Spring Boot Starter Batch Version 1.5.6.RELEASE
Oracle Java 8
mysql  Ver 14.14 Distrib 5.7.10, for Win64 (x86_64)

I am using MYSQL, so had to set up the necessary tables in my schema to support Spring Batch -  https://docs.spring.io/spring-batch/trunk/reference/html/metaDataSchema.html.

• They are located in spring-batch-core jar file under package org.springframework.batch.core for many different example DBs.  I used the schema-drop-mysql.sql and schema-mysql.sql as examples since I was using MySql
• Navigate to the spring-batch-core-*.jar.  I am using gradle, so I found it here - C:\Users\jhsu\.gradle\caches\modules-2\files-2.1\org.springframework.batch\spring-batch-core\3.0.8.RELEASE\5116a8aec6959f869cd78e779a153e2d43084097\spring-batch-core-3.0.8.RELEASE.jar\org\springframework\batch\core\

Below is the file structure of the sample project:
README.md
/src/main/java/com/cherryshoe/batch/BatchProcessorApp.java
/src/main/java/com/cherryshoe/batch/job/config/TypeOneJobConfig.java
/src/main/java/com/cherryshoe/batch/job/config/TypeTwoJobConfig.java
/src/main/java/com/cherryshoe/batch/job/config/UserNotificationJobConfig.java
/src/main/java/com/cherryshoe/batch/job/JobNotificationListener.java
/src/main/java/com/cherryshoe/batch/job/UserNotificationJobLauncher.java
/src/main/java/com/cherryshoe/batch/model/CsAuditBatchProcess.java
/src/main/java/com/cherryshoe/batch/model/CsUserNotification.java
/src/main/java/com/cherryshoe/batch/model/dto/CsAndUsersDTO.java
/src/main/java/com/cherryshoe/batch/model/dto/UserInfoDTO.java
/src/main/java/com/cherryshoe/batch/model/dto/UserNotificationDTO.java
/src/main/java/com/cherryshoe/batch/processor/TypeOneProcessor.java
/src/main/java/com/cherryshoe/batch/processor/TypeTwoProcessor.java
/src/main/java/com/cherryshoe/batch/writer/CustomUpdateNotificationWriter.java
/src/main/resources/application.properties
/src/main/resources/logback-spring.xml

Below is a short description of each file followed by the file contents:

Logstash multiple JDBC input, multiple index output with single config file

My project's current implementation of synching mySQL data with elasticsearch is using logstash where there is one "object type"'s table data that goes into one index in elasticsearch.

Environment:
Windows 7
MySQL 5.7.10
Logstash 5.0.2
Elasticsearch 5.0.2

input {
    jdbc {
  jdbc_driver_library => "C:\Apps\elasticstack\mysql-connector-java-5.1.40\mysql-connector-java-5.1.40-bin.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_connection_string => "jdbc:mysql://localhost:3306/cherryshoe?useSSL=false"
  jdbc_user => "cherryshoeuser"
  jdbc_password => "cherryshoepassword"
  statement_filepath => "C:\workspaces\cherryshoe-team\logstash\conf\cherryshoe_object_type.sql"
 }
}
output {
 elasticsearch {
  index => "cherryshoe_object_type_idx"
  document_id => "%{unique_id}"
 }
 stdout { }
}

PROBLEM:
We now needed two different "object type"'s data into two separate indexes in elasticsearch.

SOLUTION:

To achieve this solution using a single logstash config file -
Use logstash input jdbc "type" attribute in each jdbc input.  In the example below, the first input jdbc has a type defined with "object_type1", the second input jdbc has a type defined with "object_type2".

Parameterize the "index" attribute in output elasticsearch with the "type" attribute used in the jdbc input.  We only need one output jdbc, to sync "object_type1" mySQL data to elasticsearch "cherryshoe_object_type1_idx" index, and "object_type2" mySQL data to elasticsearch "cherryshoe_object_type2_idx" index.

# inputs - two types of input SQLs: "object_type1" and "object_type2"
input {
 jdbc {
  jdbc_driver_library => "C:\Apps\elasticstack\mysql-connector-java-5.1.40\mysql-connector-java-5.1.40-bin.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_connection_string => "jdbc:mysql://localhost:3306/cherryshoe?useSSL=false"
  jdbc_user => "cherryshoeuser"
  jdbc_password => "cherryshoepassword"
  statement_filepath => "C:\workspaces\cherryshoe-team\logstash\conf\cherryshoe_object_type1.sql"
  type => "object_type1"
 }
 jdbc {
  jdbc_driver_library => "C:\Apps\elasticstack\mysql-connector-java-5.1.40\mysql-connector-java-5.1.40-bin.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_connection_string => "jdbc:mysql://localhost:3306/cherryshoe?useSSL=false"
  jdbc_user => "cherryshoeuser"
  jdbc_password => "cherryshoepassword"
  statement_filepath => "C:\workspaces\cherryshoe-team\logstash\conf\cherryshoe_object_type2.sql"
  type => "object_type2"
 }
}

# Use the "type" field to specify the index that the input(s) go to
# "unique_id" is specified in each input SQL as the document_id in the elasticsearch document
output {
 elasticsearch {
  index => "cherryshoe_%{type}_idx"
  document_id => "%{unique_id}"
 }
 
 stdout { }
}

NOTE: I don't need the input jdbc "type" field to be indexed in the elasticsearch document, so adding the mutate filter facilitates this.  It will copy the input jdbc type field to event metadata, so the event metadata "type" field can be used in the parameterized output elasticsearch "index" attribute.  The updated logstash conf file is below:

# inputs - two types of input SQLs: "object_type1" and "object_type2"
input {
 jdbc {
  jdbc_driver_library => "C:\Apps\elasticstack\mysql-connector-java-5.1.40\mysql-connector-java-5.1.40-bin.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_connection_string => "jdbc:mysql://localhost:3306/cherryshoe?useSSL=false"
  jdbc_user => "cherryshoeuser"
  jdbc_password => "cherryshoepassword"
  statement_filepath => "C:\workspaces\cherryshoe-team\logstash\conf\cherryshoe_object_type1.sql"
  type => "object_type1"
 }
 jdbc {
  jdbc_driver_library => "C:\Apps\elasticstack\mysql-connector-java-5.1.40\mysql-connector-java-5.1.40-bin.jar"
  jdbc_driver_class => "com.mysql.jdbc.Driver"
  jdbc_connection_string => "jdbc:mysql://localhost:3306/cherryshoe?useSSL=false"
  jdbc_user => "cherryshoeuser"
  jdbc_password => "cherryshoepassword"
  statement_filepath => "C:\workspaces\cherryshoe-team\logstash\conf\cherryshoe_object_type2.sql"
  type => "object_type2"
 }
}

# Specifying "type" in the input creates an "_type"(non-indexed elasticsearch attribute) and "type"(indexed elasticsearch attribute).
# Copy the "type" value into the event metadata, and then remove "type" since it is a non-required attribute in each document
filter {
    mutate { add_field => { "[@metadata][type]" => "%{type}" } }
    mutate { remove_field => ["type"] }
}

# Use the event metadata "type" field to specify the index that the input(s) go to
# "unique_id" is specified in each input SQL as the document_id in the elasticsearch document
output {
 elasticsearch {
  index => "cherryshoe_%{[@metadata][type]}_idx"
  document_id => "%{unique_id}"
 }
 
 stdout { }
}

These articles helped a lot:
https://stackoverflow.com/questions/37613611/multiple-inputs-on-logstash-jdbc
https://discuss.elastic.co/t/delete-a-field-in-filter-but-use-it-in-output/48008
https://www.elastic.co/blog/logstash-metadata

MySQL: querying for hierarchical data for set number of levels

Problem: I have a table cherryshoetech that has hierarchical data, and needed to output out the name's of each record by each level (i.e. Level_1, Level_2, etc).

Environment:
MySql 5.7.10.0

Table Definition:
Columns     Type              Comments
---------       ------------      ----------------------------------------
id                int(11)           primary key
name          varchar(200)
type            varchar(20)
parent_id    int(11)           parent of this record, null if no parent

Solution:
Without using a stored procedure, and knowing that the hierarchical data can only go up to six levels, the below query works to output the 6 Levels by name:

-- We know we have at most six levels of cherryshoetech so only need to join up to 6 levels
SELECT
cst1.id, cst1.name AS Level_1, cst2.name as Level_2, cst3.name as Level_3, cst4.name as Level_4, cst5.name as Level_5, cst6.name as Level_6
FROM cherryshoetech cst1
LEFT JOIN cherryshoetech AS cst2 ON cst2.parent_id = cst1.id
LEFT JOIN cherryshoetech AS cst3 ON cst3.parent_id = cst2.id
LEFT JOIN cherryshoetech AS cst4 ON cst4.parent_id = cst3.id
LEFT JOIN cherryshoetech AS cst5 ON cst5.parent_id = cst4.id
LEFT JOIN cherryshoetech AS cst6 ON cst6.parent_id = cst5.id
ORDER BY
cst1.id

This article helped a lot.

Bash script to call rest endpoint for each id in text file

I needed to write an admin utility script to delete documents from a content management system.  First, the list of document ids were retrieved from the application database; Second, the list of ids were used as input to a bash script to make REST calls to delete the documents.

Environment:
MySql 5.7.10
RHEL 7

STEPS:

1. Retrieve list of ids from the database and save to uuids.txt, one id on each line. The ids were alfresco cmis unique ids (with unique nodeRef id, ;,  and version number), they look like "workspace://SpacesStore/aa3e765f-c628-44c2-bc66-17d622ca2210;1.0".  I needed documents created after a certain date, and only the unique id portion after the "SpacesStore/" and prior to the ";1.0" of the unique id.  Below is the MySQL select statement:
   
   select substring_index(substring_index(alf_node_id, 'SpacesStore/', -1),
                          ';1.0', 1), created_dt from cherryshoe_documents where created_dt >= '2017-03-18 00:00:00' order by created_dt desc;
   
   
2. The bash script is below, it incorporates a curl call to a REST DELETE endpoint with basic authentication.
   
   delete.sh

   
   #!/usr/bin/bash
   # Calls a repository rest endpoint to delete node(s).
   # Takes in an input text file of one id per line.
   
   username=admin
   password=admin
   protocol=http
   #hostname can contain port
   hostname="localhost:8080"
   
   echo username=$username, password=$password, protocol=$protocol, hostname=$hostname
   
   if (( "$#" != 1 ))
   then
       echo "Usage Info: Enter in filename"
   exit 1
   fi
   
   filename="$1"
   
   echo "Starting Node Delete..."
   while read -r line
   do
       uuid="$line"
       endpoint=$protocol://$hostname/alfresco/s/api/node/workspace/SpacesStore/$uuid
       # contains a \r (CR) at the end (0d). Remove it with
       endpoint=${endpoint%$'\r'}
       echo $endpoint
   
       echo "#######START $uuid"
       curl -u $username:$password -i -X DELETE $endpoint
       echo "#######END $uuid"
   
   done < "$filename"
   
   echo "DONE"
   

   
   
3. Sample uuids.txt file below:
   32231a66-649a-4cee-9a16-2c45b639fd94
   08b53382-e120-4100-a093-32b2a57b9bad
   6759904f-48bf-4e02-93e0-6ee9f9885df7
   9931f0e8-1b48-4c3c-a885-94ccf80866ea
   aedef20e-6831-4fdc-ad21-872af333d985
   
   
   

JPA Custom Repository Query Example

I'm using spring boot with JPA and a MySQL backend database.  There was a need to create a custom query (with a list custom domain object's returned) with joins across multiple tables; the custom repository query is primarily driven off of the composite key of the relationship table.

NOTE: In my effort to make this a generic solution, I'm hopeful that this example is not more confusing because of how generic the table and column names are.

This worked with:
Windows 7
Oracle Java 1.8.0_66
MySql 5.7.10.0
Spring Boot 1.3.1.RELEASE

Tables:

• entity1:  There is a domain class called Entity1.java with a corresponding repository class
• entity2: There is a domain class called Entity2.java with a corresponding repository class
• entity3:  There is a domain class called Entity3.java with a corresponding repository class
• rel_entity2_entity2: relationship table that associates data from one entity1 id to many entity2 ids.  There is a domain class called Rel_Entity1_Entity2.java with a corresponding repository class

Solution:

1. Create a custom domain class called Entity1Entity2Entity3.java to represent the object that will be returned from the custom query.
   
   

   package com.cherryshoe.domain.data.custom;
   
   import java.io.Serializable;
   
   import javax.persistence.EmbeddedId;
   import javax.persistence.Entity;
   
   /*
    * This is a custom domain model that is a result of multiple SQL joins, one of which is the rel_entity1_entity2 table, which is a table
    * that associates entity1 ids with entity2 ids.
    * This class does not represent a real table, but a logical table that has a composite key that is driven off of the
    * rel_entity1_entity2 table, which is entity2_id and entity1_id.  
    * 
    * JPA entities must have an Id, or if a combination of all OR some columns make it unique, 
    * then make a composite key of those columns. 
    */
   @Entity
   public class Entity1Entity2Entity3 implements Serializable {
   
       /**
        * 
        */
       private static final long serialVersionUID = -6947625046963217352L;
       
       // non-nullable
       @EmbeddedId
       private Entity1Entity2Entity3Key compositeKey;
       
       // some entity1 table attributes
       private String entity1LegacyId;
       private String entity1Type;
       
       // some entity2 table attributes
       private String entity2_name;
       
       // some entity3 table attributes
       private Long entity3_id;
       private String entity3_code;
       private String entity3_group;
   
       public Entity1Entity2Entity3() {
           super();
       }
   
       public Entity1Entity2Entity3(Entity1Entity2Entity3Key compositeKey) {
           super();
           this.compositeKey = compositeKey;
       }
   
       public Entity1Entity2Entity3(Entity1Entity2Entity3Key compositeKey, String entity1LegacyId, String entity1Type,
               String entity2_name, Long entity3_id, String entity3_code, String entity3_group) {
           super();
           this.compositeKey = compositeKey;
           this.entity1LegacyId = entity1LegacyId;
           this.entity1Type = entity1Type;
           this.entity2_name = entity2_name;
           this.entity3_id = entity3_id;
           this.entity3_code = entity3_code;
           this.entity3_group = entity3_group;
       }
   
       ... getters and setters, hash, equals, toString
   }
   

2. Create a custom domain composite key class called Entity1Entity2Entity3Key.java
   
   

   package com.cherryshoe.domain.data.custom;
   
   import java.io.Serializable;
   
   import javax.persistence.Embeddable;
   
   @Embeddable
   public class Entity1Entity2Entity3Key implements Serializable{
   
       /**
        * 
        */
       private static final long serialVersionUID = -8407380808173613520L;
   
       // entity1 table id
       private Long entity1_id;
       
       // entity2 table id
       private Long entity2_id;
       
       public Entity1Entity2Entity3Key() {
           super();
       }
   
       public Entity1Entity2Entity3Key(Long entity1_id, Long entity2_id) {
           super();
           this.entity1_id = entity1_id;
           this.entity2_id = entity2_id;
       }
   
       ... getters and setters, hash, equals, toString
   }
   

3. The JPA custom repository query is searching for a list of custom Entity1Entity2Entity3 records by entity1 legacy's id that are provided in a Set.  
1. Notice the Entity1Entity2Entity3RepositoryCustom is typed to the domain class Entity1Entity2Entity3, with Entity1Entity2Entity3 as the Key.
2. Notice the return object is a list of custom Entity1Entity2Enity3 custom domain objects.




package com.cherryshoe.repository.data.custom;

import java.util.List;
import java.util.Set;

import com.cherryshoe.domain.data.custom.Entity1Entity2Entity3;
import com.cherryshoe.domain.data.custom.Entity1Entity2Entity3Key;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

public interface Entity1Entity2Entity3RepositoryCustom extends CrudRepository<Entity1Entity2Entity3, Entity1Entity2Entity3Key> {
    
    @Query(value = "SELECT entity1.id as entity1_id, entity1.entity1LegacyId, entity1.entity1Type, "
            + "entity2.id as entity2_id, entity2.entity2_name, entity3.id as entity3_id, entity3.entity3_name, "
            + "entity3.entity3_code, entity3.entity3_group "
            + "  FROM entity1 "
            + "    LEFT JOIN rel_entity1_entity2 re1e2 ON entity1.id = re1e2.entity1_id "
            + "      LEFT JOIN entity2 ON re1e2.entity2_id = entity2.id "
            + "        LEFT JOIN entity3 ON entity2.entity3_id = entity3.id "
            + "WHERE entity1.entity1LegacyId in (?1) "
            + "ORDER BY entity1.id, entity2.id",
            nativeQuery = true)
    public List<Entity1Entity2Entity3> retrieveLegacyEntity1Info(
            @Param("entity1LegacyId") Set<String> entity1LegacyIdSet);

}



4. Here's the Integration Test for the custom repository method:
   
   

   package com.cherryshoe.repository.data.custom;
   
   import static org.junit.Assert.assertNotNull;
   
   import java.util.Arrays;
   import java.util.HashSet;
   import java.util.List;
   import java.util.Set;
   
   import org.junit.Test;
   import org.springframework.beans.factory.annotation.Autowired;
   import com.cherryshoe.domain.data.custom.Entity1Entity2Entity3;
   import com.cherryshoe.repository.template.TemplateITTest;
   
   /*
    * This is an IT Test for custom repository Entity1Entity2Entity3Repository
    */
   public class Entity1Entity2Entity3RepositoryCustomITTest extends TemplateITTest {
   
       @Autowired
       Entity1Entity2Entity3RepositoryCustom entity1Entity2Entity3Repo;
       
       @Test
       public void testRetrieveByEntity1LegacyId() throws Exception
       {
           // these can be any value, we just need to test this is value sql
           Set<String> entity1LegacyIdSet = new HashSet<String>(Arrays.asList("legacy1", "legacy2"));
           
           List<Entity1Entity2Entity3> entity1Entity2Entity3List = entity1Entity2Entity3Repo.retrieveLegacyEntity1Info(entity1LegacyIdSet);
           assertNotNull(entity1Entity2Entity3List);
   
           log.info("count[" + entity1Entity2Entity3List.size() + "]");
           
           for (Entity1Entity2Entity3 curr : entity1Entity2Entity3List) {
               System.out.println(curr);
           }
   
       }
   }
   
   

JPA Json Serialization/Deserialization to/from MySQL

I'm using spring boot with JPA and a MySQL backend database with several JSON columns.  The following is the JSON converter class used to save and retrieve data to the JSON column.  This article helped a lot.

This worked with:
Windows 7
Oracle Java 1.8.0_66
MySql 5.7.10.0
Spring Boot 1.3.1.RELEASE

Steps:

1. Create a POJO to represent the JSON object that will be saved to the column.
   

   package com.cherryshoe.model;
   
   /*
    * Represents a "documents" database column that holds json document data
    */
   public class JsonDocuments {
       private String docId;
       private String docName;
   
       public String getDocId() {
           return docId;
       }
   
       public void setDocId(String docId) {
           this.docId = docId;
       }
   
       public String getDocName() {
           return docName;
       }
   
       public void setDocName(String docName) {
           this.docName = docName;
       }
   
       public JsonDocuments() {
           super();
       }
   
       public JsonDocuments(String docId, String docName) {
           super();
           this.docId = docId;
           this.docName = docName;
       }
   
       @Override
        public int hashCode() {
         ...
        }
   
       @Override
        public boolean equals(Object obj) {
         ...
        }
   
       @Override
       public String toString() {
           return "JsonDocuments [docId=" + docId + ", docName=" + docName + "]";
       }
   
   }

2. Create a Converter Class that implements AttributerConverter.
   
   
   • Annotate the class with JPA converter annotation
   • I need to save a list of documents so the type argument to AttributeConverter is List<JsonDocuments> vs JsonDocuments
   • We are using the Jackson JSON library to serialize/deserialize POJO's to JSON and vice versa.  ObjectMapper is thread safe so it is declared as a static variable in the JpaJsonDocumentsConverter class.
   • Override convertToDatabaseColumn and convertToEntityAttribute methods with objectMapper writeValue and readValue calls

   package com.cherryshoe.utils;
   
   import java.io.IOException;
   import java.util.ArrayList;
   import java.util.Arrays;
   import java.util.List;
   
   import javax.persistence.AttributeConverter;
   import javax.persistence.Converter;
   
   import org.slf4j.Logger;
   import org.slf4j.LoggerFactory;
   import org.cherryshoe.model.JsonDocuments;
   
   import com.fasterxml.jackson.core.JsonProcessingException;
   import com.fasterxml.jackson.databind.ObjectMapper;
   
   @Converter
   public class JpaJsonDocumentsConverter implements
           AttributeConverter<List<JsonDocuments>, String> {
   
       // ObjectMapper is thread safe
       private final static ObjectMapper objectMapper = new ObjectMapper();
   
       private Logger log = LoggerFactory.getLogger(getClass());
   
       @Override
       public String convertToDatabaseColumn(List<JsonDocuments> meta) {
           String jsonString = "";
           try {
               log.debug("Start convertToDatabaseColumn");
   
               // convert list of POJO to json
               jsonString = objectMapper.writeValueAsString(meta);
               log.debug("convertToDatabaseColumn" + jsonString);
   
           } catch (JsonProcessingException ex) {
               log.error(ex.getMessage());
           }
           return jsonString;
       }
   
       @Override
       public List<JsonDocuments> convertToEntityAttribute(String dbData) {
           List<JsonDocuments> list = new ArrayList<JsonDocuments>();
           try {
               log.debug("Start convertToEntityAttribute");
   
               // convert json to list of POJO
               list = Arrays.asList(objectMapper.readValue(dbData,
                       JsonDocuments[].class));
               log.debug("JsonDocumentsConverter.convertToDatabaseColumn" + list);
   
           } catch (IOException ex) {
               log.error(ex.getMessage());
           }
           return list;
       }
   }
   

3. The JPA Entity class that contains the JSON column needs to have the @Convert annotation for the documents column.  NOTE:  You could also have in the JpaJsonDocumentsConverter class added the autoapply attribute to the @Converter annotation and set that to true to have JPA apply this converter to all entity attributes of type List<JsonDocuments>.
   

   @Entity
   @Table(name = "TABLE_WITH_DOCUMENTS")
   public class TableWithDocuments implements Serializable
   {
   
        /**
        *
        */
        private static final long serialVersionUID = 3781459465416706159L;
       
        ... entity attributes
       
        @Convert(converter = JpaJsonDocumentsConverter.class)
        private List<JsonDocuments> documents;
       
        ... entity attributes
   
    }



4. Verified with an integration test on the Spring Data JPA Repository class that uses this entity that these methods worked.  I did notice that the documents column could not be null, so when you create the new TableWithDocuments record, the documents column had to be set as an empty JsonArray if there were no documents.  Adding in null checks in the JpaJsonDocumentsConverter  convertToDatabaseColumn and convertToEntityAttribute  did not help, it was inside the JPA code that would error out.

JPA and Spring Data Self Join Example with Annotations

I had a table I needed to do a self join on.  We'll call the table "tree".  A tree record can have zero or one parent_tree record.  This is recursive until you find null for the parent_tree.  In the real world, most of the time, this example probably doesn't make sense to have tree depths where there is only ever only one child per depth.

Example "tree" table
--------------------------
treeId (primary key, int) parent_tree (int)
1                                       null
2                                       1
3                                       2

If I did the following SQL selects, I would get result:

• select * from tree where treeId = 1 would return itself (treeId 1)
• select * from tree where treeId = 2 would return itself (treeId 2), and one nested tree (with treeId 1)
• select * from tree where treeId = 3 would return itself (treeId 3), one nested tree (with treeId of 2), and then another one nested tree inside tree (with treeId of 2) with treeId of 1

Below is sample Java code to illustrate how this is modeled and implemented, items of note include:

• Tree.java
• @OneToOne is used, since you can only have zero or one tree's inside of a tree, so on and so forth
• @JoinColumn
• name is set to parent_tree since parent_tree is the actual column name from the db
• referencedColumnName is treeId, since it references the primary key column treeId
• nullable is true, since you can have zero or one tree nested inside a tree
• the parentTree variable is going to return a "Tree" instead of a Long (we want the object it's pointing to, not the integer value of the primary key)
• TreeRepository.java
• An interface that extends CrudRepository to get all Spring out-of-the-box Crud methods, like findAll

Tree.java

import javax.persistence.*;

@Entity
@Table(name="TREE")
public class Tree {
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long treeId;
  
    // the column parentTree is going to return the object Tree, instead of the parentTree as a Long
    @OneToOne(fetch=FetchType.EAGER, optional = false)
    @JoinColumn(name="parent_tree", referencedColumnName="treeId", nullable = true)  
    private Tree parentTree;

    @Override
    public String toString() {
        return "Tree [treeId=" + treeId + ", parentTree=" + parentTree + "]";
    }

}

TreeRepository.java

import org.springframework.data.repository.CrudRepository;
import com.cherryshoe.Tree;

public interface TreeRepository extends CrudRepository<Tree, Long> {
  // findAll is inherited from CrudRepository
}

I tested this on Windows 7, Java 1.8.0_66, and MySQL 5.7.10.