Data Types

/**
 * MariaDB BLOB implementation
 */
public class MariaDbBlob implements Blob, Serializable {
  /**
   * Get length of BLOB data
   * @return Length in bytes
   * @throws SQLException if operation fails
   */
  public long length() throws SQLException;
  
  /**
   * Get bytes from BLOB
   * @param pos Starting position (1-based)
   * @param length Number of bytes to retrieve
   * @return Byte array with requested data
   * @throws SQLException if operation fails
   */
  public byte[] getBytes(long pos, int length) throws SQLException;
  
  /**
   * Get input stream for BLOB data
   * @return InputStream for reading BLOB content
   * @throws SQLException if operation fails
   */
  public InputStream getBinaryStream() throws SQLException;
  
  /**
   * Get input stream for portion of BLOB
   * @param pos Starting position
   * @param length Number of bytes to read
   * @return InputStream for reading specified portion
   * @throws SQLException if operation fails
   */
  public InputStream getBinaryStream(long pos, long length) throws SQLException;
  
  /**
   * Find pattern in BLOB
   * @param pattern Byte pattern to search for
   * @param start Starting position for search
   * @return Position of pattern or -1 if not found
   * @throws SQLException if operation fails
   */
  public long position(byte[] pattern, long start) throws SQLException;
  
  /**
   * Find BLOB pattern in this BLOB
   * @param pattern BLOB pattern to search for
   * @param start Starting position for search
   * @return Position of pattern or -1 if not found
   * @throws SQLException if operation fails
   */
  public long position(Blob pattern, long start) throws SQLException;
  
  // Modification methods
  public int setBytes(long pos, byte[] bytes) throws SQLException;
  public int setBytes(long pos, byte[] bytes, int offset, int len) throws SQLException;
  public OutputStream setBinaryStream(long pos) throws SQLException;
  public void truncate(long len) throws SQLException;
  public void free() throws SQLException;
}

/**
 * MariaDB CLOB implementation (extends BLOB functionality)
 */
public class MariaDbClob extends MariaDbBlob implements Clob, NClob {
  /**
   * Get character length of CLOB
   * @return Length in characters
   * @throws SQLException if operation fails
   */
  public long length() throws SQLException;
  
  /**
   * Get substring from CLOB
   * @param pos Starting position (1-based)
   * @param length Number of characters to retrieve
   * @return String with requested characters
   * @throws SQLException if operation fails
   */
  public String getSubString(long pos, int length) throws SQLException;
  
  /**
   * Get character stream for CLOB
   * @return Reader for reading character content
   * @throws SQLException if operation fails
   */
  public Reader getCharacterStream() throws SQLException;
  
  /**
   * Get ASCII stream for CLOB
   * @return InputStream for reading ASCII content
   * @throws SQLException if operation fails
   */
  public InputStream getAsciiStream() throws SQLException;
  
  /**
   * Find string pattern in CLOB
   * @param searchstr String to search for
   * @param start Starting position for search
   * @return Position of pattern or -1 if not found
   * @throws SQLException if operation fails
   */
  public long position(String searchstr, long start) throws SQLException;
  
  /**
   * Find CLOB pattern in this CLOB
   * @param searchstr CLOB pattern to search for
   * @param start Starting position for search
   * @return Position of pattern or -1 if not found
   * @throws SQLException if operation fails
   */
  public long position(Clob searchstr, long start) throws SQLException;
  
  // Modification methods
  public int setString(long pos, String str) throws SQLException;
  public int setString(long pos, String str, int offset, int len) throws SQLException;
  public Writer setCharacterStream(long pos) throws SQLException;
  public OutputStream setAsciiStream(long pos) throws SQLException;
}

// Working with BLOBs
PreparedStatement stmt = conn.prepareStatement("INSERT INTO files (name, content) VALUES (?, ?)");
stmt.setString(1, "document.pdf");

// Set BLOB from file
File file = new File("document.pdf");
FileInputStream fis = new FileInputStream(file);
stmt.setBinaryStream(2, fis, file.length());
stmt.executeUpdate();

// Retrieve BLOB
ResultSet rs = stmt.executeQuery("SELECT content FROM files WHERE name = 'document.pdf'");
if (rs.next()) {
    Blob blob = rs.getBlob("content");
    
    // Get BLOB data as bytes
    byte[] data = blob.getBytes(1, (int) blob.length());
    
    // Get BLOB data as stream
    InputStream blobStream = blob.getBinaryStream();
    // Process stream...
    
    // Search within BLOB
    byte[] pattern = "PDF".getBytes();
    long position = blob.position(pattern, 1);
}

// Working with CLOBs
PreparedStatement clobStmt = conn.prepareStatement("INSERT INTO documents (title, content) VALUES (?, ?)");
clobStmt.setString(1, "Article Title");

// Set CLOB from String
String content = "This is a long text document...";
clobStmt.setString(2, content);
// Or set from Reader
clobStmt.setCharacterStream(2, new StringReader(content));
clobStmt.executeUpdate();

// Retrieve CLOB
ResultSet clobRs = stmt.executeQuery("SELECT content FROM documents WHERE title = 'Article Title'");
if (clobRs.next()) {
    Clob clob = clobRs.getClob("content");
    
    // Get CLOB data as String
    String text = clob.getSubString(1, (int) clob.length());
    
    // Get CLOB data as Reader
    Reader clobReader = clob.getCharacterStream();
    
    // Search within CLOB
    long wordPosition = clob.position("important", 1);
}

import org.mariadb.jdbc.type.*;

/**
 * Base interface for all geometry types
 * Package: org.mariadb.jdbc.type
 */
public interface Geometry {
  /**
   * Get Well-Known Text (WKT) representation
   * @return WKT string representation
   */
  String toString();
  
  /**
   * Get geometry type name
   * @return Type name (e.g., "POINT", "POLYGON")
   */
  String getGeometryType();
}

/**
 * Point geometry representing a single coordinate
 */
public class Point implements Geometry {
  /**
   * Create point from coordinates
   * @param x X coordinate
   * @param y Y coordinate
   */
  public Point(double x, double y);
  
  /**
   * Create point with Z coordinate
   * @param x X coordinate
   * @param y Y coordinate
   * @param z Z coordinate
   */
  public Point(double x, double y, double z);
  
  public double getX();
  public double getY();
  public double getZ();
  public boolean hasZ();
}

/**
 * LineString geometry representing a series of connected points
 */
public class LineString implements Geometry {
  /**
   * Create LineString from points
   * @param points Array of Point objects
   */
  public LineString(Point[] points);
  
  public Point[] getPoints();
  public int getNumPoints();
  public Point getPointN(int n);
}

/**
 * Polygon geometry representing a closed area
 */
public class Polygon implements Geometry {
  /**
   * Create Polygon with exterior ring
   * @param exteriorRing Points defining the outer boundary
   */
  public Polygon(LineString exteriorRing);
  
  /**
   * Create Polygon with exterior and interior rings
   * @param exteriorRing Outer boundary
   * @param interiorRings Array of holes within the polygon
   */
  public Polygon(LineString exteriorRing, LineString[] interiorRings);
  
  public LineString getExteriorRing();
  public LineString[] getInteriorRings();
  public int getNumInteriorRings();
}

/**
 * Multi-geometry types
 */
public class MultiPoint implements Geometry {
  public MultiPoint(Point[] points);
  public Point[] getPoints();
}

public class MultiLineString implements Geometry {
  public MultiLineString(LineString[] lineStrings);
  public LineString[] getLineStrings();
}

public class MultiPolygon implements Geometry {
  public MultiPolygon(Polygon[] polygons);
  public Polygon[] getPolygons();
}

/**
 * Collection of mixed geometry types
 */
public class GeometryCollection implements Geometry {
  public GeometryCollection(Geometry[] geometries);
  public Geometry[] getGeometries();
  public int getNumGeometries();
  public Geometry getGeometryN(int n);
}

// Working with Point geometry
Point point = new Point(-74.0060, 40.7128); // New York City coordinates
PreparedStatement pointStmt = conn.prepareStatement("INSERT INTO locations (name, coordinates) VALUES (?, ?)");
pointStmt.setString(1, "New York City");
pointStmt.setObject(2, point);
pointStmt.executeUpdate();

// Working with Polygon geometry
Point[] exteriorPoints = {
    new Point(0, 0),
    new Point(10, 0),
    new Point(10, 10),
    new Point(0, 10),
    new Point(0, 0)  // Close the ring
};
LineString exterior = new LineString(exteriorPoints);
Polygon polygon = new Polygon(exterior);

// Insert polygon
PreparedStatement polyStmt = conn.prepareStatement("INSERT INTO areas (name, boundary) VALUES (?, ?)");
polyStmt.setString(1, "Square Area");
polyStmt.setObject(2, polygon);
polyStmt.executeUpdate();

// Retrieve geometry data
ResultSet geoRs = stmt.executeQuery("SELECT coordinates FROM locations WHERE name = 'New York City'");
if (geoRs.next()) {
    Point retrievedPoint = (Point) geoRs.getObject("coordinates");
    double x = retrievedPoint.getX();
    double y = retrievedPoint.getY();
    System.out.println("Location: " + x + ", " + y);
}

// Working with complex geometries
Point[] line1Points = {new Point(0, 0), new Point(5, 5)};
Point[] line2Points = {new Point(2, 2), new Point(8, 8)};
LineString[] lines = {new LineString(line1Points), new LineString(line2Points)};
MultiLineString multiLine = new MultiLineString(lines);

// Geometry collections
Geometry[] mixed = {point, polygon, multiLine};
GeometryCollection collection = new GeometryCollection(mixed);

/**
 * Array implementation for float values
 */
public class FloatArray implements Array {
  /**
   * Create FloatArray from float array
   * @param floats Array of float values
   */
  public FloatArray(float[] floats);
  
  /**
   * Get SQL type name
   * @return SQL type name
   * @throws SQLException if operation fails
   */
  public String getBaseTypeName() throws SQLException;
  
  /**
   * Get array as Object
   * @return Array object
   * @throws SQLException if operation fails
   */
  public Object getArray() throws SQLException;
  
  /**
   * Get portion of array
   * @param index Starting index (1-based)
   * @param count Number of elements
   * @return Array subset
   * @throws SQLException if operation fails
   */
  public Object getArray(long index, int count) throws SQLException;
  
  /**
   * Get array as ResultSet
   * @return ResultSet representation of array
   * @throws SQLException if operation fails
   */
  public ResultSet getResultSet() throws SQLException;
  
  public void free() throws SQLException;
}

// Working with float arrays
float[] values = {1.1f, 2.2f, 3.3f, 4.4f, 5.5f};
FloatArray floatArray = new FloatArray(values);

PreparedStatement arrayStmt = conn.prepareStatement("INSERT INTO measurements (sensor_id, readings) VALUES (?, ?)");
arrayStmt.setInt(1, 12345);
arrayStmt.setArray(2, floatArray);
arrayStmt.executeUpdate();

// Retrieve array data
ResultSet arrayRs = stmt.executeQuery("SELECT readings FROM measurements WHERE sensor_id = 12345");
if (arrayRs.next()) {
    Array retrievedArray = arrayRs.getArray("readings");
    float[] readings = (float[]) retrievedArray.getArray();
    
    for (float reading : readings) {
        System.out.println("Reading: " + reading);
    }
}

/**
 * XA transaction identifier
 */
public class MariaDbXid implements Xid {
  /**
   * Create XID for distributed transaction
   * @param formatId Format identifier
   * @param globalTransactionId Global transaction ID
   * @param branchQualifier Branch qualifier
   */
  public MariaDbXid(int formatId, byte[] globalTransactionId, byte[] branchQualifier);
  
  /**
   * Get format identifier
   * @return Format ID
   */
  public int getFormatId();
  
  /**
   * Get global transaction identifier
   * @return Global transaction ID bytes
   */
  public byte[] getGlobalTransactionId();
  
  /**
   * Get branch qualifier
   * @return Branch qualifier bytes
   */
  public byte[] getBranchQualifier();
}

// Distributed transaction with XA
XADataSource xaDataSource = new MariaDbDataSource();
XAConnection xaConn = xaDataSource.getXAConnection();
XAResource xaResource = xaConn.getXAResource();

// Create transaction ID
MariaDbXid xid = new MariaDbXid(1, "global_tx_123".getBytes(), "branch_1".getBytes());

try {
    // Start XA transaction
    xaResource.start(xid, XAResource.TMNOFLAGS);
    
    // Perform database operations
    Connection conn = xaConn.getConnection();
    PreparedStatement stmt = conn.prepareStatement("INSERT INTO orders (customer_id, amount) VALUES (?, ?)");
    stmt.setInt(1, 12345);
    stmt.setBigDecimal(2, new BigDecimal("99.99"));
    stmt.executeUpdate();
    
    // End transaction branch
    xaResource.end(xid, XAResource.TMSUCCESS);
    
    // Prepare transaction
    int result = xaResource.prepare(xid);
    if (result == XAResource.XA_OK) {
        // Commit transaction
        xaResource.commit(xid, false);
    }
    
} catch (XAException e) {
    // Rollback on error
    xaResource.rollback(xid);
    throw e;
}

// Standard JDBC type mappings
setString(index, "text")           -> VARCHAR, TEXT
setInt(index, 123)                 -> INT, INTEGER
setLong(index, 123L)               -> BIGINT
setDouble(index, 123.45)           -> DOUBLE
setBigDecimal(index, decimal)      -> DECIMAL, NUMERIC
setBoolean(index, true)            -> BOOLEAN, TINYINT(1)
setDate(index, date)               -> DATE
setTime(index, time)               -> TIME
setTimestamp(index, timestamp)     -> TIMESTAMP, DATETIME
setBytes(index, bytes)             -> VARBINARY, BLOB
setBinaryStream(index, stream)     -> LONGBLOB
setCharacterStream(index, reader)  -> LONGTEXT
setObject(index, geometry)         -> GEOMETRY, POINT, POLYGON

// MariaDB-specific mappings
setObject(index, point)            -> POINT
setObject(index, polygon)          -> POLYGON
setObject(index, jsonObject)       -> JSON
setArray(index, floatArray)        -> MariaDB array types (where supported)

// Retrieving different data types
getString("column")           // VARCHAR, TEXT, CHAR
getInt("column")              // INT, TINYINT, SMALLINT, MEDIUMINT
getLong("column")             // BIGINT
getDouble("column")           // DOUBLE, FLOAT
getBigDecimal("column")       // DECIMAL, NUMERIC
getBoolean("column")          // BOOLEAN, TINYINT(1)
getDate("column")             // DATE
getTime("column")             // TIME
getTimestamp("column")        // TIMESTAMP, DATETIME
getBytes("column")            // BINARY, VARBINARY
getBlob("column")             // BLOB, LONGBLOB
getClob("column")             // TEXT, LONGTEXT
getObject("column")           // Any type, returned as appropriate Java object

// Geometry types
Point point = (Point) getObject("coordinates");
Polygon area = (Polygon) getObject("boundary");

// Array types
Array readings = getArray("sensor_data");
float[] values = (float[]) readings.getArray();

// MariaDB JDBC driver provides automatic type conversion
ResultSet rs = stmt.executeQuery("SELECT int_column, varchar_column FROM table");
while (rs.next()) {
    // These conversions happen automatically
    String intAsString = rs.getString("int_column");           // "123"
    int stringAsInt = rs.getInt("varchar_column");             // 456 (if convertible)
    BigDecimal intAsDecimal = rs.getBigDecimal("int_column");  // 123.00
    boolean intAsBoolean = rs.getBoolean("int_column");        // true if non-zero
}

// Setting parameters with automatic conversion
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO table (int_col, string_col) VALUES (?, ?)");
pstmt.setString(1, "123");        // String to INT conversion
pstmt.setInt(2, 456);             // INT to VARCHAR conversion (not recommended)

// Setting null values
PreparedStatement nullStmt = conn.prepareStatement("INSERT INTO table (nullable_col) VALUES (?)");
nullStmt.setNull(1, Types.VARCHAR);
// Or
nullStmt.setString(1, null);

// Checking for null values
ResultSet nullRs = stmt.executeQuery("SELECT nullable_col FROM table");
while (nullRs.next()) {
    String value = nullRs.getString("nullable_col");
    if (nullRs.wasNull()) {
        System.out.println("Value was NULL");
    } else {
        System.out.println("Value: " + value);
    }
}