0
# Data Types
1

2
Comprehensive data type support including JDBC standard types, MariaDB-specific extensions, geometry types, and LOB handling.
3

4
## Capabilities
5

6
### LOB Types
7

8
Large Object (LOB) support for binary and character data.
9

10
```java { .api }
11
/**
12
 * MariaDB BLOB implementation
13
 */
14
public class MariaDbBlob implements Blob, Serializable {
15
  /**
16
   * Get length of BLOB data
17
   * @return Length in bytes
18
   * @throws SQLException if operation fails
19
   */
20
  public long length() throws SQLException;
21
  
22
  /**
23
   * Get bytes from BLOB
24
   * @param pos Starting position (1-based)
25
   * @param length Number of bytes to retrieve
26
   * @return Byte array with requested data
27
   * @throws SQLException if operation fails
28
   */
29
  public byte[] getBytes(long pos, int length) throws SQLException;
30
  
31
  /**
32
   * Get input stream for BLOB data
33
   * @return InputStream for reading BLOB content
34
   * @throws SQLException if operation fails
35
   */
36
  public InputStream getBinaryStream() throws SQLException;
37
  
38
  /**
39
   * Get input stream for portion of BLOB
40
   * @param pos Starting position
41
   * @param length Number of bytes to read
42
   * @return InputStream for reading specified portion
43
   * @throws SQLException if operation fails
44
   */
45
  public InputStream getBinaryStream(long pos, long length) throws SQLException;
46
  
47
  /**
48
   * Find pattern in BLOB
49
   * @param pattern Byte pattern to search for
50
   * @param start Starting position for search
51
   * @return Position of pattern or -1 if not found
52
   * @throws SQLException if operation fails
53
   */
54
  public long position(byte[] pattern, long start) throws SQLException;
55
  
56
  /**
57
   * Find BLOB pattern in this BLOB
58
   * @param pattern BLOB pattern to search for
59
   * @param start Starting position for search
60
   * @return Position of pattern or -1 if not found
61
   * @throws SQLException if operation fails
62
   */
63
  public long position(Blob pattern, long start) throws SQLException;
64
  
65
  // Modification methods
66
  public int setBytes(long pos, byte[] bytes) throws SQLException;
67
  public int setBytes(long pos, byte[] bytes, int offset, int len) throws SQLException;
68
  public OutputStream setBinaryStream(long pos) throws SQLException;
69
  public void truncate(long len) throws SQLException;
70
  public void free() throws SQLException;
71
}
72

73
/**
74
 * MariaDB CLOB implementation (extends BLOB functionality)
75
 */
76
public class MariaDbClob extends MariaDbBlob implements Clob, NClob {
77
  /**
78
   * Get character length of CLOB
79
   * @return Length in characters
80
   * @throws SQLException if operation fails
81
   */
82
  public long length() throws SQLException;
83
  
84
  /**
85
   * Get substring from CLOB
86
   * @param pos Starting position (1-based)
87
   * @param length Number of characters to retrieve
88
   * @return String with requested characters
89
   * @throws SQLException if operation fails
90
   */
91
  public String getSubString(long pos, int length) throws SQLException;
92
  
93
  /**
94
   * Get character stream for CLOB
95
   * @return Reader for reading character content
96
   * @throws SQLException if operation fails
97
   */
98
  public Reader getCharacterStream() throws SQLException;
99
  
100
  /**
101
   * Get ASCII stream for CLOB
102
   * @return InputStream for reading ASCII content
103
   * @throws SQLException if operation fails
104
   */
105
  public InputStream getAsciiStream() throws SQLException;
106
  
107
  /**
108
   * Find string pattern in CLOB
109
   * @param searchstr String to search for
110
   * @param start Starting position for search
111
   * @return Position of pattern or -1 if not found
112
   * @throws SQLException if operation fails
113
   */
114
  public long position(String searchstr, long start) throws SQLException;
115
  
116
  /**
117
   * Find CLOB pattern in this CLOB
118
   * @param searchstr CLOB pattern to search for
119
   * @param start Starting position for search
120
   * @return Position of pattern or -1 if not found
121
   * @throws SQLException if operation fails
122
   */
123
  public long position(Clob searchstr, long start) throws SQLException;
124
  
125
  // Modification methods
126
  public int setString(long pos, String str) throws SQLException;
127
  public int setString(long pos, String str, int offset, int len) throws SQLException;
128
  public Writer setCharacterStream(long pos) throws SQLException;
129
  public OutputStream setAsciiStream(long pos) throws SQLException;
130
}
131
```
132

133
**Usage Examples:**
134

135
```java
136
// Working with BLOBs
137
PreparedStatement stmt = conn.prepareStatement("INSERT INTO files (name, content) VALUES (?, ?)");
138
stmt.setString(1, "document.pdf");
139

140
// Set BLOB from file
141
File file = new File("document.pdf");
142
FileInputStream fis = new FileInputStream(file);
143
stmt.setBinaryStream(2, fis, file.length());
144
stmt.executeUpdate();
145

146
// Retrieve BLOB
147
ResultSet rs = stmt.executeQuery("SELECT content FROM files WHERE name = 'document.pdf'");
148
if (rs.next()) {
149
    Blob blob = rs.getBlob("content");
150
    
151
    // Get BLOB data as bytes
152
    byte[] data = blob.getBytes(1, (int) blob.length());
153
    
154
    // Get BLOB data as stream
155
    InputStream blobStream = blob.getBinaryStream();
156
    // Process stream...
157
    
158
    // Search within BLOB
159
    byte[] pattern = "PDF".getBytes();
160
    long position = blob.position(pattern, 1);
161
}
162

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

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

174
// Retrieve CLOB
175
ResultSet clobRs = stmt.executeQuery("SELECT content FROM documents WHERE title = 'Article Title'");
176
if (clobRs.next()) {
177
    Clob clob = clobRs.getClob("content");
178
    
179
    // Get CLOB data as String
180
    String text = clob.getSubString(1, (int) clob.length());
181
    
182
    // Get CLOB data as Reader
183
    Reader clobReader = clob.getCharacterStream();
184
    
185
    // Search within CLOB
186
    long wordPosition = clob.position("important", 1);
187
}
188
```
189

190
### Geometry Types
191

192
Support for spatial/geometry data types commonly used in GIS applications. All geometry types are located in the `org.mariadb.jdbc.type` package.
193

194
**Import Statement:**
195

196
```java
197
import org.mariadb.jdbc.type.*;
198
```
199

200
```java { .api }
201
/**
202
 * Base interface for all geometry types
203
 * Package: org.mariadb.jdbc.type
204
 */
205
public interface Geometry {
206
  /**
207
   * Get Well-Known Text (WKT) representation
208
   * @return WKT string representation
209
   */
210
  String toString();
211
  
212
  /**
213
   * Get geometry type name
214
   * @return Type name (e.g., "POINT", "POLYGON")
215
   */
216
  String getGeometryType();
217
}
218

219
/**
220
 * Point geometry representing a single coordinate
221
 */
222
public class Point implements Geometry {
223
  /**
224
   * Create point from coordinates
225
   * @param x X coordinate
226
   * @param y Y coordinate
227
   */
228
  public Point(double x, double y);
229
  
230
  /**
231
   * Create point with Z coordinate
232
   * @param x X coordinate
233
   * @param y Y coordinate
234
   * @param z Z coordinate
235
   */
236
  public Point(double x, double y, double z);
237
  
238
  public double getX();
239
  public double getY();
240
  public double getZ();
241
  public boolean hasZ();
242
}
243

244
/**
245
 * LineString geometry representing a series of connected points
246
 */
247
public class LineString implements Geometry {
248
  /**
249
   * Create LineString from points
250
   * @param points Array of Point objects
251
   */
252
  public LineString(Point[] points);
253
  
254
  public Point[] getPoints();
255
  public int getNumPoints();
256
  public Point getPointN(int n);
257
}
258

259
/**
260
 * Polygon geometry representing a closed area
261
 */
262
public class Polygon implements Geometry {
263
  /**
264
   * Create Polygon with exterior ring
265
   * @param exteriorRing Points defining the outer boundary
266
   */
267
  public Polygon(LineString exteriorRing);
268
  
269
  /**
270
   * Create Polygon with exterior and interior rings
271
   * @param exteriorRing Outer boundary
272
   * @param interiorRings Array of holes within the polygon
273
   */
274
  public Polygon(LineString exteriorRing, LineString[] interiorRings);
275
  
276
  public LineString getExteriorRing();
277
  public LineString[] getInteriorRings();
278
  public int getNumInteriorRings();
279
}
280

281
/**
282
 * Multi-geometry types
283
 */
284
public class MultiPoint implements Geometry {
285
  public MultiPoint(Point[] points);
286
  public Point[] getPoints();
287
}
288

289
public class MultiLineString implements Geometry {
290
  public MultiLineString(LineString[] lineStrings);
291
  public LineString[] getLineStrings();
292
}
293

294
public class MultiPolygon implements Geometry {
295
  public MultiPolygon(Polygon[] polygons);
296
  public Polygon[] getPolygons();
297
}
298

299
/**
300
 * Collection of mixed geometry types
301
 */
302
public class GeometryCollection implements Geometry {
303
  public GeometryCollection(Geometry[] geometries);
304
  public Geometry[] getGeometries();
305
  public int getNumGeometries();
306
  public Geometry getGeometryN(int n);
307
}
308
```
309

310
**Usage Examples:**
311

312
```java
313
// Working with Point geometry
314
Point point = new Point(-74.0060, 40.7128); // New York City coordinates
315
PreparedStatement pointStmt = conn.prepareStatement("INSERT INTO locations (name, coordinates) VALUES (?, ?)");
316
pointStmt.setString(1, "New York City");
317
pointStmt.setObject(2, point);
318
pointStmt.executeUpdate();
319

320
// Working with Polygon geometry
321
Point[] exteriorPoints = {
322
    new Point(0, 0),
323
    new Point(10, 0),
324
    new Point(10, 10),
325
    new Point(0, 10),
326
    new Point(0, 0)  // Close the ring
327
};
328
LineString exterior = new LineString(exteriorPoints);
329
Polygon polygon = new Polygon(exterior);
330

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

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

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

352
// Geometry collections
353
Geometry[] mixed = {point, polygon, multiLine};
354
GeometryCollection collection = new GeometryCollection(mixed);
355
```
356

357
### Array Support
358

359
Support for array data types.
360

361
```java { .api }
362
/**
363
 * Array implementation for float values
364
 */
365
public class FloatArray implements Array {
366
  /**
367
   * Create FloatArray from float array
368
   * @param floats Array of float values
369
   */
370
  public FloatArray(float[] floats);
371
  
372
  /**
373
   * Get SQL type name
374
   * @return SQL type name
375
   * @throws SQLException if operation fails
376
   */
377
  public String getBaseTypeName() throws SQLException;
378
  
379
  /**
380
   * Get array as Object
381
   * @return Array object
382
   * @throws SQLException if operation fails
383
   */
384
  public Object getArray() throws SQLException;
385
  
386
  /**
387
   * Get portion of array
388
   * @param index Starting index (1-based)
389
   * @param count Number of elements
390
   * @return Array subset
391
   * @throws SQLException if operation fails
392
   */
393
  public Object getArray(long index, int count) throws SQLException;
394
  
395
  /**
396
   * Get array as ResultSet
397
   * @return ResultSet representation of array
398
   * @throws SQLException if operation fails
399
   */
400
  public ResultSet getResultSet() throws SQLException;
401
  
402
  public void free() throws SQLException;
403
}
404
```
405

406
**Usage Examples:**
407

408
```java
409
// Working with float arrays
410
float[] values = {1.1f, 2.2f, 3.3f, 4.4f, 5.5f};
411
FloatArray floatArray = new FloatArray(values);
412

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

418
// Retrieve array data
419
ResultSet arrayRs = stmt.executeQuery("SELECT readings FROM measurements WHERE sensor_id = 12345");
420
if (arrayRs.next()) {
421
    Array retrievedArray = arrayRs.getArray("readings");
422
    float[] readings = (float[]) retrievedArray.getArray();
423
    
424
    for (float reading : readings) {
425
        System.out.println("Reading: " + reading);
426
    }
427
}
428
```
429

430
### XA Transaction Support
431

432
Support for distributed transactions.
433

434
```java { .api }
435
/**
436
 * XA transaction identifier
437
 */
438
public class MariaDbXid implements Xid {
439
  /**
440
   * Create XID for distributed transaction
441
   * @param formatId Format identifier
442
   * @param globalTransactionId Global transaction ID
443
   * @param branchQualifier Branch qualifier
444
   */
445
  public MariaDbXid(int formatId, byte[] globalTransactionId, byte[] branchQualifier);
446
  
447
  /**
448
   * Get format identifier
449
   * @return Format ID
450
   */
451
  public int getFormatId();
452
  
453
  /**
454
   * Get global transaction identifier
455
   * @return Global transaction ID bytes
456
   */
457
  public byte[] getGlobalTransactionId();
458
  
459
  /**
460
   * Get branch qualifier
461
   * @return Branch qualifier bytes
462
   */
463
  public byte[] getBranchQualifier();
464
}
465
```
466

467
**Usage Examples:**
468

469
```java
470
// Distributed transaction with XA
471
XADataSource xaDataSource = new MariaDbDataSource();
472
XAConnection xaConn = xaDataSource.getXAConnection();
473
XAResource xaResource = xaConn.getXAResource();
474

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

478
try {
479
    // Start XA transaction
480
    xaResource.start(xid, XAResource.TMNOFLAGS);
481
    
482
    // Perform database operations
483
    Connection conn = xaConn.getConnection();
484
    PreparedStatement stmt = conn.prepareStatement("INSERT INTO orders (customer_id, amount) VALUES (?, ?)");
485
    stmt.setInt(1, 12345);
486
    stmt.setBigDecimal(2, new BigDecimal("99.99"));
487
    stmt.executeUpdate();
488
    
489
    // End transaction branch
490
    xaResource.end(xid, XAResource.TMSUCCESS);
491
    
492
    // Prepare transaction
493
    int result = xaResource.prepare(xid);
494
    if (result == XAResource.XA_OK) {
495
        // Commit transaction
496
        xaResource.commit(xid, false);
497
    }
498
    
499
} catch (XAException e) {
500
    // Rollback on error
501
    xaResource.rollback(xid);
502
    throw e;
503
}
504
```
505

506
## Data Type Mapping
507

508
### Java to MariaDB Type Mapping
509

510
```java
511
// Standard JDBC type mappings
512
setString(index, "text")           -> VARCHAR, TEXT
513
setInt(index, 123)                 -> INT, INTEGER
514
setLong(index, 123L)               -> BIGINT
515
setDouble(index, 123.45)           -> DOUBLE
516
setBigDecimal(index, decimal)      -> DECIMAL, NUMERIC
517
setBoolean(index, true)            -> BOOLEAN, TINYINT(1)
518
setDate(index, date)               -> DATE
519
setTime(index, time)               -> TIME
520
setTimestamp(index, timestamp)     -> TIMESTAMP, DATETIME
521
setBytes(index, bytes)             -> VARBINARY, BLOB
522
setBinaryStream(index, stream)     -> LONGBLOB
523
setCharacterStream(index, reader)  -> LONGTEXT
524
setObject(index, geometry)         -> GEOMETRY, POINT, POLYGON
525

526
// MariaDB-specific mappings
527
setObject(index, point)            -> POINT
528
setObject(index, polygon)          -> POLYGON
529
setObject(index, jsonObject)       -> JSON
530
setArray(index, floatArray)        -> MariaDB array types (where supported)
531
```
532

533
### Retrieval Type Mapping
534

535
```java
536
// Retrieving different data types
537
getString("column")           // VARCHAR, TEXT, CHAR
538
getInt("column")              // INT, TINYINT, SMALLINT, MEDIUMINT
539
getLong("column")             // BIGINT
540
getDouble("column")           // DOUBLE, FLOAT
541
getBigDecimal("column")       // DECIMAL, NUMERIC
542
getBoolean("column")          // BOOLEAN, TINYINT(1)
543
getDate("column")             // DATE
544
getTime("column")             // TIME
545
getTimestamp("column")        // TIMESTAMP, DATETIME
546
getBytes("column")            // BINARY, VARBINARY
547
getBlob("column")             // BLOB, LONGBLOB
548
getClob("column")             // TEXT, LONGTEXT
549
getObject("column")           // Any type, returned as appropriate Java object
550

551
// Geometry types
552
Point point = (Point) getObject("coordinates");
553
Polygon area = (Polygon) getObject("boundary");
554

555
// Array types
556
Array readings = getArray("sensor_data");
557
float[] values = (float[]) readings.getArray();
558
```
559

560
## Type Conversion and Compatibility
561

562
### Automatic Type Conversion
563

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

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

581
### Null Value Handling
582

583
```java
584
// Setting null values
585
PreparedStatement nullStmt = conn.prepareStatement("INSERT INTO table (nullable_col) VALUES (?)");
586
nullStmt.setNull(1, Types.VARCHAR);
587
// Or
588
nullStmt.setString(1, null);
589

590
// Checking for null values
591
ResultSet nullRs = stmt.executeQuery("SELECT nullable_col FROM table");
592
while (nullRs.next()) {
593
    String value = nullRs.getString("nullable_col");
594
    if (nullRs.wasNull()) {
595
        System.out.println("Value was NULL");
596
    } else {
597
        System.out.println("Value: " + value);
598
    }
599
}
600
```