This article demonstrates how to implement basic database interactions—user authentication, journal entry creation, and retrieval—using raw JDBC without any framework abstractions. The solution uses Apache Druid for connection pooling, standard JDBC APIs for SQL execution, and JavaBeans introspection for object mapping.
Core Domain Models
Two lightweight POJOs represent the data entities:
public class JournalEntry {
private Integer id;
private String content;
private java.time.LocalDateTime createdAt;
private Integer authorId;
// Standard getters and setters
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public String getContent() { return content; }
public void setContent(String content) { this.content = content; }
public LocalDateTime getCreatedAt() { return createdAt; }
public void setCreatedAt(LocalDateTime createdAt) { this.createdAt = createdAt; }
public Integer getAuthorId() { return authorId; }
public void setAuthorId(Integer authorId) { this.authorId = authorId; }
@Override
public String toString() {
return String.format("JournalEntry{id=%d, content='%s', createdAt=%s}",
id, content, createdAt);
}
}
public class Account {
private Integer id;
private String handle;
private String secret;
// Getters and setters omitted for brevity
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
public String getHandle() { return handle; }
public void setHandle(String handle) { this.handle = handle; }
public String getSecret() { return secret; }
public void setSecret(String secret) { this.secret = secret; }
}
Database Access Utility
A utility class encapsulates connection management, parameterized query execution, and result-set-to-object conversion:
package com.example.db;
import javax.sql.DataSource;
import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;
public class JdbcHelper {
private static final DataSource dataSource;
static {
try {
var props = new Properties();
props.load(JdbcHelper.class.getResourceAsStream("/druid-config.properties"));
dataSource = com.alibaba.druid.pool.DruidDataSourceFactory.createDataSource(props);
} catch (Exception e) {
throw new RuntimeException("Failed to initialize connection pool", e);
}
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static int executeUpdate(String sql, Object... params) {
try (Connection conn = getConnection();
PreparedStatement stmt = conn.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
stmt.setObject(i + 1, params[i]);
}
return stmt.executeUpdate();
} catch (SQLException e) {
throw new RuntimeException("Update failed: " + sql, e);
}
}
public static <T> List<T> queryObjects(String sql, Class<T> targetType, Object... params) {
List<Map<String, Object>> rows = queryRows(sql, params);
List<T> results = new ArrayList<>();
for (Map<String, Object> row : rows) {
try {
T instance = targetType.getDeclaredConstructor().newInstance();
BeanInfo info = Introspector.getBeanInfo(targetType);
PropertyDescriptor[] descriptors = info.getPropertyDescriptors();
for (PropertyDescriptor pd : descriptors) {
String propName = pd.getName();
if ("class".equals(propName) || "id".equals(propName) && row.get(propName) == null) continue;
Object value = row.get(propName);
if (value != null && pd.getWriteMethod() != null) {
if (value instanceof Timestamp && LocalDateTime.class.isAssignableFrom(pd.getPropertyType())) {
pd.getWriteMethod().invoke(instance, ((Timestamp) value).toLocalDateTime());
} else {
pd.getWriteMethod().invoke(instance, value);
}
}
}
results.add(instance);
} catch (Exception e) {
throw new RuntimeException("Failed to map row to " + targetType.getSimpleName(), e);
}
}
return results;
}
private static List<Map<String, Object>> queryRows(String sql, Object... params) {
List<Map<String, Object>> results = new ArrayList<>();
try (Connection conn = getConnection();
PreparedStatement stmt = conn.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
stmt.setObject(i + 1, params[i]);
}
try (ResultSet rs = stmt.executeQuery()) {
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
while (rs.next()) {
Map<String, Object> row = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
String colName = meta.getColumnName(i);
row.put(colName.toLowerCase(), rs.getObject(i));
}
results.add(row);
}
}
} catch (SQLException e) {
throw new RuntimeException("Query failed: " + sql, e);
}
return results;
}
}
Druid Configuration File
The druid-config.properties file (placed in src/main/resources) contains:
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/journal_db?serverTimezone=UTC&useSSL=false&characterEncoding=utf8mb4
username=app_user
password=secure_password
initialSize=2
maxActive=10
minIdle=1
maxWait=60000
Main Application Logic
The interactive console application orchestrates login, journal writing, and retrieval:
package com.example.app;
import com.example.db.JdbcHelper;
import com.example.model.Account;
import com.example.model.JournalEntry;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Scanner;
public class JournalConsole {
private static final Scanner input = new Scanner(System.in);
private static Account currentUser;
public static void main(String[] args) {
login();
writeJournal();
listEntries();
}
private static void login() {
System.out.print("Username: ");
String user = input.nextLine();
System.out.print("Password: ");
String pass = input.nextLine();
String sql = "SELECT id, handle FROM account WHERE handle = ? AND secret = ?";
List<Account> matches = JdbcHelper.queryObjects(sql, Account.class, user, pass);
if (!matches.isEmpty()) {
currentUser = matches.get(0);
System.out.println("✅ Login successful: " + currentUser.getHandle());
} else {
System.err.println("❌ Invalid credentials.");
login();
}
}
private static void writeJournal() {
System.out.println("📝 Start typing your journal (type 'SAVE' on a new line to commit):");
StringBuilder content = new StringBuilder();
String line;
while (!(line = input.nextLine()).equalsIgnoreCase("SAVE")) {
content.append(line).append("\n");
}
if (content.length() > 0) {
String insertSql = "INSERT INTO journal_entry (content, created_at, author_id) VALUES (?, ?, ?)";
int rows = JdbcHelper.executeUpdate(insertSql, content.toString().trim(), LocalDateTime.now(), currentUser.getId());
if (rows > 0) System.out.println("✅ Entry saved successfully.");
}
}
private static void listEntries() {
System.out.print("Type 'SCAN' to view all entries, or anything else to exit: ");
if (!input.nextLine().equalsIgnoreCase("SCAN")) {
System.out.println("👋 Goodbye.");
return;
}
String selectSql = "SELECT * FROM journal_entry WHERE author_id = ?";
List<JournalEntry> entries = JdbcHelper.queryObjects(selectSql, JournalEntry.class, currentUser.getId());
if (entries.isEmpty()) {
System.out.println("📭 No entries found.");
} else {
System.out.println("📖 Your journal entries:");
entries.forEach(System.out::println);
}
}
}
The implementation avoids Spring Boot dependencies entirely, relying solely on JDBC, Druid, and core Java reflection. It supports multi-line journal input, automatic timestamping, and type-safe result mapping—including conversion from java.sql.Timestamp to java.time.LocalDateTime.