package de.kdo.mariadbtest;

import static org.junit.Assert.assertEquals;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.stream.Collectors;

import org.junit.Test;

public class Latin1ClobTest {

   /**

    * 

    * Writes and reads a clob (longtext) of a latin1 table.

    * 

    * @throws SQLException

    *

    */

   @Test

   public void insertAndSelectShouldBothUseLatin1Encoding() throws SQLException {

      // German Umlaute (ÄÖÜ) U+00C4, U+00D6, U+00DC

      final String latin1String = "\u00c4\u00d6\u00dc";

      try (Connection connection = DriverManager.getConnection("jdbc:mariadb://localhost:3306/testlatin1?user=mft&password=mft")) {

         connection.prepareStatement("CREATE TABLE foo (x longtext) DEFAULT CHARSET=latin1").execute();

         final Clob insertClob = connection.createClob();

         insertClob.setString(1, latin1String);

         final String insertSQL = "INSERT INTO foo " + "VALUES(?)";

         PreparedStatement pstmt = connection.prepareStatement(insertSQL);

         pstmt.setClob(1, insertClob);

         pstmt.executeUpdate();

         final String selectSQL = "select x " + "from foo";

         pstmt = connection.prepareStatement(selectSQL);

         ResultSet rs = pstmt.executeQuery();

         rs.next();

         final Clob retrieveClob = rs.getClob(1);

         final String stringFromSelect = retrieveClob.getSubString(1, (int) retrieveClob.length());

         System.out.printf("INSERT: %s %s\n", latin1String,

               latin1String.codePoints().mapToObj(Integer::toHexString).collect(Collectors.toList()));

         System.out.printf("SELECT: %s %s\n", stringFromSelect, stringFromSelect.codePoints().mapToObj(Integer::toHexString)

               .collect(Collectors.toList()));

         assertEquals(latin1String, stringFromSelect);

      } catch (SQLException e) {

         throw e;

      } finally {

      }

   }

}

INSERT: ÄÖÜ [c4, d6, dc]

SELECT: ÄÖÜ [c3, 201e, c3, 2013, c3, 153]

MariaDB [testlatin1]> select x, hex(x) from foo;

+----------------+--------------+

| x              | hex(x)       |

+----------------+--------------+

| ÄÖÜ         | C384C396C39C |

+----------------+--------------+

1 row in set (0.00 sec)