Pyranid
What Is It?
A minimalist JDBC interface for modern Java applications.
Design Goals
- Simple
- Customizable
- Threadsafe
- No dependencies
- DI-friendly
- Java 8+
License
Maven Installation
com.pyranidpyranid1.0.17
Direct Download
If you don’t use Maven, you can drop pyranid-1.0.17.jar directly into your project. No other dependencies are required.
Configuration
// Minimal setup, uses defaultsDatabase database = Database..;// Customized setupDatabase customDatabase = Database.. // Override JVM default timezone.....;
Obtaining a DataSource
Pyranid works with any DataSource
implementation. If you have the freedom to choose, HikariCP is a great option.
DataSource dataSource = new HikariDataSource(new HikariConfig());
Queries
Suppose we have a custom Car
like this:
// Follows JavaBean conventions for getters/setters
We might query for it like this:
// A single carOptional<Car> car = database.;// A single car, passing prepared statement parameters via varargsOptional<Car> specificCar = database.;// Multiple carsList<Car> blueCars = database.;// In addition to custom types, you can map to primitives and some JDK builtins out of the box.// See 'ResultSet Mapping' section for detailsOptional<UUID> id = database.;List<BigDecimal> balances = database.;
Statements
// General-purpose statement execution (CREATEs, UPDATEs, function calls...)long updateCount = database.;// Statement execution that provides a value via RETURNING.// Useful for INSERTs with autogenerated keys, among other thingsOptional<UUID> id = database.;// Batch operations can be more efficient than execution of discrete statements.// Useful for inserting a lot of data at onceList<List<Object>> parameterGroups = new ArrayList<>();// Blue carparameterGroups.;// Red carparameterGroups.;// Insert both carslong[] updateCounts = database.;
Transactions
Design goals
- Minimal closure-based API: rollback if exception bubbles out, commit at end of closure otherwise
- Standard data access APIs (
queryForObject()
and friends) automatically participate in transactions - No
Connection
is fetched from theDataSource
until the first data access operation occurs - Must be able to share a transaction across multiple threads
Basics
// Any code that runs inside of the closure operates within the context of a transaction.// Pyranid will set autocommit=false for the duration of the transaction if necessarydatabase.;// For convenience, transactional operations may return valuesOptional<BigDecimal> newBalance = database.;
Context
// Gets a handle to the current transaction, if any.// The handle is useful for creating savepoints, forcing rollback, etc.Optional<Transaction> transaction = database.;// Output is "false"out.;database.;
Multi-threaded Transactions
Internally, Database
manages a threadlocal stack of Transaction
instances to simplify single-threaded usage. Should you need to share the same transaction across multiple threads, use the participate()
API.
database.;
Rolling Back
// Any exception that bubbles out will cause a rollbackdatabase.;// You may mark a transaction as rollback-only, and it will roll back after the// closure execution has completeddatabase.;// You may roll back to a savepointdatabase.;
Nesting
// Each nested transaction is independent. There is no parent-child relationshipdatabase.;
Isolation
// You may specify the normal isolation levels per-transaction as needed:// READ_COMMITTED, READ_UNCOMMITTED, REPEATABLE_READ, and SERIALIZABLE.// If not specified, DEFAULT is assumed (whatever your DBMS prefers)database.;
Post-Transaction Operations
It is useful to be able to schedule code to run after a transaction has been fully committed or rolled back. Often, transaction management happens at a higher layer of code than business logic (e.g. a transaction-per-web-request pattern), so it is helpful to have a mechanism to “warp” local logic out to the higher layer.
Without this, you might run into subtle bugs like
- Write to database
- Inform listeners of system state change
- (later) Current transaction is rolled back
- Listeners are in an inconsistent state because they were notified of a change that was reversed by the rollback
// Business logic// Servlet filter which wraps requests in transactions
ResultSet Mapping
The DefaultResultSetMapper
supports user-defined types that follow the JavaBean getter/setter conventions, primitives, and some additional common JDK types.
User-defined Types
By default, database column names are assumed to be separated by _
characters and are mapped to their camel-case equivalent. For example:
Car car = database..;// Output might be "Car ID is 123 and color is BLUE. Token is d73c523a-8344-44ef-819c-40467662d619"out.;// Column names will work with wildcard queries as wellcar = database..;// Column aliases work toocar = database..;
Supported Primitives
Byte
Short
Integer
Long
Float
Double
Boolean
Char
String
byte[]
Supported JDK Types
Enum<E>
UUID
BigDecimal
BigInteger
Date
Instant
LocalDate
forDATE
LocalTime
forTIME
LocalDateTime
forTIMESTAMP
OffsetTime
forTIME WITH TIMEZONE
OffsetDateTime
forTIMESTAMP WITH TIMEZONE
ZoneId
TimeZone
Locale
(IETF BCP 47 “language tag” format)
Other Types
- Store Postgres JSONB data using a SQL cast of
String
, e.g.CAST(? AS JSONB)
. Retrieve JSONB data usingString
Error Handling
In general, a runtime DatabaseException
will be thrown when errors occur. Often this will wrap the checked java.sql.SQLException
.
For convenience, DatabaseException
exposes additional properties, which are only populated if provided by the underlying java.sql.SQLException
:
errorCode
(optional)sqlState
(optional)
For PostgreSQL, the following properties are also available:
column
(optional)constraint
(optional)datatype
(optional)detail
(optional)file
(optional)hint
(optional)internalPosition
(optional)internalQuery
(optional)line
(optional)dbmsMessage
(optional)position
(optional)routine
(optional)schema
(optional)severity
(optional)table
(optional)where
(optional)
Extended property support for Oracle and MySQL is planned.
Practical Application
// Gives someone at most one big awarddatabase.
Logging and Diagnostics
StatementLogger
You may customize your Database
with a StatementLogger
.
Database database = Database...;
StatementLog
instances give you access to the following for each SQL statement executed. All time values are in nanoseconds.
sql
parameters
statementMetadata
(optional)connectionAcquisitionTime
(optional)preparationTime
(optional)executionTime
(optional)resultSetMappingTime
(optional)batchSize
(optional)exception
(optional)
Given this query:
Optional<Car> car = database.;
The log output for DefaultStatementLogger
might look like:
SELECT * FROM car WHERE color = ?Parameters: 'BLUE'0.04ms acquiring connection, 0.03ms preparing statement, 0.82ms executing statement, 0.40ms processing resultset
Statement Metadata
You may specify arbitrary metadata with database operations via the StatementMetadata
type.
This is useful for supporting custom logging scenarios your application might require.
// App-specific metadata which means "don't log this statement"StatementMetadata IGNORE_LOGGING = new StatementMetadata();// App-specific metadata which means "this is sensitive, restrict parameter logging".// This may alternatively be expressed using shorthand://// SENSITIVE_DATA = StatementMetadata.with("com.myapp.SENSITIVITY_LEVEL", "HIGH")StatementMetadata SENSITIVE_DATA = new StatementMetadata.Builder().;.;// Set up our database with custom loggingDatabase database = Database...;// This "hot" query is run frequently in the background, so we don't want to log itOptional<Message> message = database.;// We want to log this sensitive statement speciallydatabase.;
java.util.Logging
Pyranid uses java.util.Logging
internally. The usual way to hook into this is with SLF4J, which can funnel all the different logging mechanisms in your app through a single one, normally Logback. Your Maven configuration might look like this:
ch.qos.logbacklogback-classic1.2.3org.slf4jjul-to-slf4j1.7.30
You might have code like this which runs at startup:
// Bridge all java.util.logging to SLF4Jjava.util.logging.Logger rootLogger = java.util.logging.LogManager..;for (Handler handler : rootLogger.)rootLogger.;SLF4JBridgeHandler.;
Don’t forget to uninstall the bridge at shutdown time:
// Sometime laterSLF4JBridgeHandler.;
Note: SLF4JBridgeHandler
can impact performance. You can mitigate that with Logback’s LevelChangePropagator
configuration option as described here.
TODOs
- Formalize BLOB/CLOB handling
- Work around more Oracle quirks