Json to sql java

How to map a mysql json column to a java entity property using jpa and hibernate?

When working with databases in Java applications, it is often necessary to map database columns to properties in a Java entity. This is usually done using Object-Relational Mapping (ORM) frameworks such as JPA (Java Persistence API) and Hibernate. One common issue that arises is when the database column is of type JSON and needs to be mapped to a Java property. JSON columns allow storing structured data as a JSON string in a database column, but mapping this data to a Java entity can be a challenge.

Method 1: Using a Converter

To map a MySQL JSON column to a Java entity property using JPA and Hibernate, you can use a Converter. Here is a step-by-step guide on how to do it:

1. Create a class that implements the javax.persistence.AttributeConverter interface. This interface has two methods: convertToDatabaseColumn and convertToEntityAttribute. In this example, we will convert the JSON string to a Java object using the Gson library.

import javax.persistence.AttributeConverter; import javax.persistence.Converter; import com.google.gson.Gson; @Converter public class JsonConverter implements AttributeConverterObject, String>  private final Gson gson = new Gson(); @Override public String convertToDatabaseColumn(Object attribute)  return gson.toJson(attribute); > @Override public Object convertToEntityAttribute(String dbData)  return gson.fromJson(dbData, Object.class); > >

1. Annotate the entity property with the @Convert annotation, specifying the name of the converter class.

import javax.persistence.Column; import javax.persistence.Convert; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name = "my_entity") public class MyEntity  @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column(columnDefinition = "json") @Convert(converter = JsonConverter.class) private Object data; // getters and setters >

In the above example, the data property is annotated with the @Convert annotation, specifying the JsonConverter class.

That’s it! Now you can save and retrieve JSON data from the MySQL database using JPA and Hibernate.

Method 2: Custom Serialization and Deserialization

To map a MySQL JSON column to a Java entity property using JPA and Hibernate with custom serialization and deserialization, follow these steps:

1. Add the hibernate-types-52 dependency to your project. This dependency provides the necessary classes to handle JSON data types in Hibernate.

dependency> groupId>com.vladmihalceagroupId> artifactId>hibernate-types-52artifactId> version>2.10.2version> dependency>

1. Create a custom serializer and deserializer for the JSON data type. In this example, we will use the Gson library for serialization and deserialization.

public class JsonTypeT> extends AbstractSingleColumnStandardBasicTypeT>  private final Gson gson = new Gson(); public JsonType(JsonTypeDescriptorT> jsonTypeDescriptor)  super(jsonTypeDescriptor, new JsonTypeSqlTypeDescriptor()); > @Override public String getName()  return "json"; > @Override protected boolean registerUnderJavaType()  return true; > @Override public String[] getRegistrationKeys()  return new String[]getName()>; > @Override public ClassT> returnedClass()  return jsonTypeDescriptor().getJavaType(); > @Override public Object nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws SQLException  String json = rs.getString(names[0]); if (json == null)  return null; > return gson.fromJson(json, jsonTypeDescriptor().getJavaType()); > @Override public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws SQLException  if (value == null)  st.setNull(index, Types.OTHER); return; > String json = gson.toJson(value); st.setObject(index, json, Types.OTHER); > >

public class JsonTypeDescriptorT> extends AbstractTypeDescriptorT>  private final ClassT> javaType; public JsonTypeDescriptor(ClassT> javaType)  super(javaType); this.javaType = javaType; > @Override public T fromString(String string)  return new Gson().fromJson(string, javaType); > @Override public String toString(T value)  return new Gson().toJson(value); > >

@Entity @Table(name = "my_table") public class MyEntity  @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Type(type = "com.example.JsonType", parameters = @Parameter(name = "type", value = "com.example.MyJsonType")>) @Column(name = "json_column") private MyJsonType jsonProperty; // getters and setters >

In the example above, MyJsonType is a custom class that represents the JSON data type.

That’s it! With these steps, you can now map a MySQL JSON column to a Java entity property using JPA and Hibernate with custom serialization and deserialization.

Method 3: Mapping to a POJO (Plain Old Java Object)

To map a MySQL JSON column to a Java entity property using JPA and Hibernate, you can use the «Mapping to a POJO» approach. Here are the steps:

public class JsonData  private String field1; private int field2; // getters and setters >

1. In your entity class, add a property for the JSON column and annotate it with @Type and @Column . For example:

@Entity public class MyEntity  @Id private Long id; @Type(type = "json") @Column(columnDefinition = "json") private JsonData jsonData; // getters and setters >

property name="hibernate.types.json.use_reflection_optimizer" value="true"/>

JsonData jsonData = new JsonData(); jsonData.setField1("value1"); jsonData.setField2(123); MyEntity entity = new MyEntity(); entity.setJsonData(jsonData); entityManager.persist(entity); MyEntity retrievedEntity = entityManager.find(MyEntity.class, entity.getId()); JsonData retrievedJsonData = retrievedEntity.getJsonData(); System.out.println(retrievedJsonData.getField1()); // prints "value1" System.out.println(retrievedJsonData.getField2()); // prints 123

That’s it! With these steps, you can easily map a MySQL JSON column to a Java entity property using JPA and Hibernate.

Method 4: Using a Type Mapping Framework

To map a MySQL JSON column to a Java entity property using JPA and Hibernate, you can use a type mapping framework such as Hibernate Types. Here are the steps to do it:

dependency> groupId>com.vladmihalceagroupId> artifactId>hibernate-types-52artifactId> version>2.11.1version> dependency>

import com.vladmihalcea.hibernate.type.json.JsonType; @TypeDef(name = "json", typeClass = JsonType.class) public class JsonType extends AbstractSingleColumnStandardBasicTypeObject>  public JsonType()  super( JsonSqlTypeDescriptor.INSTANCE, new JsonTypeDescriptor() ); > public String getName()  return "json"; > >

import com.vladmihalcea.hibernate.type.json.JsonBinaryType; import com.vladmihalcea.hibernate.type.json.JsonStringType; @Entity @Table(name = "my_entity") @TypeDef(name = "json", typeClass = JsonStringType.class) public class MyEntity  @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Type(type = "json") @Column(columnDefinition = "json") private Object jsonColumn; // getters and setters >

import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.stereotype.Repository; @Repository public interface MyEntityRepository extends JpaRepositoryMyEntity, Long>  >

Now you can use the MyEntityRepository to persist and retrieve objects with JSON columns.

Источник

How to insert/store JSON array into a database using JDBC?

A Json array is an ordered collection of values that are enclosed in square brackets i.e. it begins with ‘[’ and ends with ‘]’. The values in the arrays are separated by ‘,’ (comma).

Sample JSON array

The json-simple is a light weight library which is used to process JSON objects. Using this you can read or, write the contents of a JSON document using Java program.

JSON-Simple maven dependency

Following is the maven dependency for the JSON-simple library −

  com.googlecode.json-simple json-simple 1.1.1  

Paste this with in the tag at the end of your pom.xml file. (before tag)

Example

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers( ID INT, First_Name VARCHAR(255), Last_Name VARCHAR(255), Date_Of_Birth date, Place_Of_Birth VARCHAR(255), Country VARCHAR(255), PRIMARY KEY (ID) );

Now we will create a JSON document (players_data.json) with an array of documents where, each document in the array represents a record in the MyPlayers table created above as shown below −

To write the contents of the a JSON array to a database using JDBC −

Obtain connection to a database

• Register the Driver class of the desired database using the registerDriver() method of the DriverManager class or, the forName() method of the class named Class.

DriverManager.registerDriver(new com.mysql.jdbc.Driver());

• Create a connection object by passing the URL of the database, user-name and password of a user in the database (in string format) as parameters to the getConnection() method of the DriverManager class.

Connection mysqlCon = DriverManager.getConnection(mysqlUrl, "root", "password");

Read the contents of a JSON file

JSONParser jsonParser = new JSONParser();

//Parsing the contents of the JSON file JSONObject jsonObject = (JSONObject) jsonParser(new FileReader("E:/players_data.json"));

JSONArray jsonArray = (JSONArray) jsonObject.get("contact");

Insert read JSON contents to MyPlayers table

PreparedStatement pstmt = con.prepareStatement("INSERT INTO MyPlayers values (?, ?, ?, ?, ?, ? )");

• For each record in the JSON array retrieve the key-value pairs using the get method and set value to the appropriate bind variable of the PreparedStatement using the setXXX() methods.

JSONObject record = (JSONObject) object; int record.get("ID")); String first_name = (String) record.get("First_Name"); pstmt.setInt(1, id); pstmt.setString(2, first_name);

Following JDBC program inserts the contents of the players_data.json file into the MyPlayers table.

Example

import java.io.FileNotFoundException; import java.io.FileReader; import java.io.IOException; import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import org.json.simple.JSONArray; import org.json.simple.JSONObject; import org.json.simple.parser.JSONParser; import org.json.simple.parser.ParseException; public class JsonToDatabase < public static Connection ConnectToDB() throws Exception < //Registering the Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver()); //Getting the connection String mysqlUrl = "jdbc:mysql://localhost/mydatabase"; Connection con = DriverManager.getConnection(mysqlUrl, "root", "password"); System.out.println("Connection established. "); return con; >public static void main(String args[]) < //Creating a JSONParser object JSONParser jsonParser = new JSONParser(); try < //Parsing the contents of the JSON file JSONObject jsonObject = (JSONObject) jsonParser.parse(new FileReader("E:/players_data.json")); //Retrieving the array JSONArray jsonArray = (JSONArray) jsonObject.get("players_data"); Connection con = ConnectToDB(); //Insert a row into the MyPlayers table PreparedStatement pstmt = con.prepareStatement("INSERT INTO MyPlayers values (?, ?, ?, ?, ?, ? )"); for(Object object : jsonArray) < JSONObject record = (JSONObject) object; int record.get("ID")); String first_name = (String) record.get("First_Name"); String last_name = (String) record.get("Last_Name"); String date = (String) record.get("Date_Of_Birth"); long date_of_birth = Date.valueOf(date).getTime(); String place_of_birth = (String) record.get("Place_Of_Birth"); String country = (String) record.get("Country"); pstmt.setInt(1, id); pstmt.setString(2, first_name); pstmt.setString(3, last_name); pstmt.setDate(4, new Date(date_of_birth)); pstmt.setString(5, place_of_birth); pstmt.setString(6, country); pstmt.executeUpdate(); >System.out.println("Records inserted. "); > catch (FileNotFoundException e) < e.printStackTrace(); >catch (IOException e) < e.printStackTrace(); >catch (ParseException e) < e.printStackTrace(); >catch (Exception e) < // TODO Auto-generated catch block e.printStackTrace(); >> >

Output

Connection established. Records inserted.

If you verify the contents of the MyPlayers table using the SELECT statement you can see the inserted records as −

mysql> select * from MyPlayers; +----+------------+------------+---------------+----------------+-------------+ | ID | First_Name | Last_Name | Date_Of_Birth | Place_Of_Birth | Country | +----+------------+------------+---------------+----------------+-------------+ | 1 | Shikhar | Dhawan | 1981-12-05 | Delhi | India | | 2 | Jonathan | Trott | 1981-04-22 | CapeTown | SouthAfrica | | 3 | Kumara | Sangakkara | 1977-10-27 | Matale | Srilanka | | 4 | Virat | Kohli | 1988-11-05 | Mumbai | India | | 5 | Rohit | Sharma | 1987-04-30 | Nagpur | India | | 6 | Ravindra | Jadeja | 1988-12-06 | Nagpur | India | | 7 | James | Anderson | 1982-06-30 | Burnely | England | | 8 | Ryan | McLaren | 1983-02-09 | South Africa | India | +----+------------+------------+---------------+----------------+-------------+ 8 rows in set (0.00 sec

Источник