Database Access Guide

Direct database operations for plugin developers using named_jdbc

When to Use Database Access

Use direct database access when you need to:

Query data from the database
Create custom tables for plugin-specific data
Perform complex SQL operations
Generate reports or analytics from stored data

Quick Start

Reading Data


// MVEL - Count transactions for an address
params = ["address": "addr1..."];
result = named_jdbc.queryForMap(
    "SELECT COUNT(distinct tx_hash) as tx_count FROM address_utxo WHERE owner_addr = :address",
    params
);
txCount = result["tx_count"];
System.out.println("Address has " + txCount + " transactions");


// Java - Count transactions for an address (namedJdbc() from the PluginContext)
Map<String, Object> params = Map.of("address", "addr1...");
Map<String, Object> result = context().namedJdbc().queryForMap(
    "SELECT COUNT(distinct tx_hash) as tx_count FROM address_utxo WHERE owner_addr = :address",
    params
);
long txCount = ((Number) result.get("tx_count")).longValue();
System.out.println("Address has " + txCount + " transactions");

Writing Data


// JavaScript - Insert custom data
const params = {
    txHash: transaction.getTxHash(),
    processed: true,
    timestamp: Date.now()
};
named_jdbc.update(
    "INSERT INTO plugin_tracking (tx_hash, processed, timestamp) VALUES (:txHash, :processed, :timestamp)",
    params
);


// Java - Insert custom data
Map<String, Object> params = new HashMap<>();
params.put("txHash", transaction.getTxHash());
params.put("processed", true);
params.put("timestamp", System.currentTimeMillis());
context().namedJdbc().update(
    "INSERT INTO plugin_tracking (tx_hash, processed, timestamp) VALUES (:txHash, :processed, :timestamp)",
    params
);

Available Methods

named_jdbc provide these core methods:

queryForMap - Get single row as map
queryForList - Get multiple rows as list of maps
update - Execute INSERT, UPDATE, DELETE
batchUpdate - Execute multiple updates efficiently

API Reference

named_jdbc Methods

MVEL


// Query for single row
params = ["blockNumber": 12345];
block = named_jdbc.queryForMap(
    "SELECT * FROM block WHERE number = :blockNumber",
    params
);
 
// Query for multiple rows
params = ["epoch": 400];
blocks = named_jdbc.queryForList(
    "SELECT * FROM block WHERE epoch = :epoch ORDER BY slot",
    params
);
 
// Update operations
params = ["status": "processed", "id": recordId];
rowsUpdated = named_jdbc.update(
    "UPDATE processing_queue SET status = :status WHERE id = :id",
    params
);

JavaScript


// Query for single row
const params = { blockNumber: 12345 };
const block = named_jdbc.queryForMap(
    "SELECT * FROM block WHERE number = :blockNumber",
    params
);
 
// Query for multiple rows
const params = { epoch: 400 };
const blocks = named_jdbc.queryForList(
    "SELECT * FROM block WHERE epoch = :epoch ORDER BY slot",
    params
);
 
// Update operations
const params = { status: "processed", id: recordId };
const rowsUpdated = named_jdbc.update(
    "UPDATE processing_queue SET status = :status WHERE id = :id",
    params
);

Python


# Query for single row
params = {"blockNumber": 12345}
block = named_jdbc.queryForMap(
    "SELECT * FROM block WHERE number = :blockNumber",
    params
)
 
# Query for multiple rows
params = {"epoch": 400}
blocks = named_jdbc.queryForList(
    "SELECT * FROM block WHERE epoch = :epoch ORDER BY slot",
    params
)
 
# Update operations
params = {"status": "processed", "id": record_id}
rows_updated = named_jdbc.update(
    "UPDATE processing_queue SET status = :status WHERE id = :id",
    params
)

Java


// Query for single row
Map<String, Object> params = Map.of("blockNumber", 12345);
Map<String, Object> block = context().namedJdbc().queryForMap(
    "SELECT * FROM block WHERE number = :blockNumber",
    params
);
 
// Query for multiple rows
Map<String, Object> params2 = Map.of("epoch", 400);
List<Map<String, Object>> blocks = context().namedJdbc().queryForList(
    "SELECT * FROM block WHERE epoch = :epoch ORDER BY slot",
    params2
);
 
// Update operations
Map<String, Object> params3 = Map.of("status", "processed", "id", recordId);
int rowsUpdated = context().namedJdbc().update(
    "UPDATE processing_queue SET status = :status WHERE id = :id",
    params3
);

Best Practices

1. Use Parameterized Queries

Never concatenate user input directly into SQL strings - this creates SQL injection vulnerabilities!


// ❌ BAD - SQL Injection risk
query = "SELECT * FROM tx WHERE hash = '" + userInput + "'";
 
// ✅ GOOD - Safe parameterized query
params = ["hash": userInput];
named_jdbc.queryForMap("SELECT * FROM tx WHERE hash = :hash", params);


// BAD - SQL injection risk
String query = "SELECT * FROM tx WHERE hash = '" + userInput + "'";
 
// GOOD - safe parameterized query
Map<String, Object> params = Map.of("hash", userInput);
context().namedJdbc().queryForMap("SELECT * FROM tx WHERE hash = :hash", params);

2. Handle Null Results


// JavaScript - Safe null handling
const result = named_jdbc.queryForMap(
    "SELECT * FROM block WHERE number = :num",
    { num: blockNumber }
);
 
if (result && result.hash) {
    processBlock(result);
} else {
    console.log("Block not found");
}


// Java - Safe null handling (queryForMap throws EmptyResultDataAccessException if no row matches)
try {
    Map<String, Object> result = context().namedJdbc().queryForMap(
        "SELECT * FROM block WHERE number = :num",
        Map.of("num", blockNumber)
    );
    if (result.get("hash") != null) {
        processBlock(result);
    }
} catch (org.springframework.dao.EmptyResultDataAccessException e) {
    System.out.println("Block not found");
}