How to Fix Java SQLException: Database Error Troubleshooting Guide

# How to Fix Java SQLException: Database Error Troubleshooting Guide

Your application crashes when trying to insert a new user:

java.sql.SQLIntegrityConstraintViolationException: Duplicate entry 'john@example.com' for key 'users.email_unique'
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    at com.myapp.repository.UserRepository.insertUser(UserRepository.java:67)
    at com.myapp.service.UserService.registerUser(UserService.java:45)

SQLException is Java's way of reporting database errors. It's a checked exception that wraps database-specific errors into a common interface. Understanding how to diagnose and handle these errors is critical for building robust data-driven applications.

Understanding the Error

SQLException provides several pieces of diagnostic information:

Common SQL state codes:

Diagnosing the Problem

Step 1: Extract Full Error Information

```java try { userRepository.insertUser(user); } catch (SQLException e) { System.err.println("SQL State: " + e.getSQLState()); System.err.println("Error Code: " + e.getErrorCode()); System.err.println("Message: " + e.getMessage());

// Check for chained exceptions SQLException next = e.getNextException(); while (next != null) { System.err.println("Chained exception:"); System.err.println(" SQL State: " + next.getSQLState()); System.err.println(" Error Code: " + next.getErrorCode()); next = next.getNextException(); } } ```

Step 2: Check Database Logs

```bash # MySQL tail -f /var/log/mysql/error.log

# PostgreSQL tail -f /var/log/postgresql/postgresql-main.log

# SQL Server # Check SQL Server Error Log via SSMS or: sqlcmd -S localhost -E -Q "EXEC xp_readerrorlog 0, 1" ```

Step 3: Verify Database Connectivity

public boolean testConnection(DataSource dataSource) {
    try (Connection conn = dataSource.getConnection()) {
        try (Statement stmt = conn.createStatement()) {
            ResultSet rs = stmt.executeQuery("SELECT 1");
            return rs.next();
        }
    } catch (SQLException e) {
        System.err.println("Connection test failed: " + e.getMessage());
        return false;
    }
}

Solutions

Solution 1: Handle Constraint Violations Gracefully

```java public class UserRepository { public void insertUser(User user) throws DuplicateUserException { String sql = "INSERT INTO users (email, name, created_at) VALUES (?, ?, ?)";

try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {

stmt.setString(1, user.getEmail()); stmt.setString(2, user.getName()); stmt.setTimestamp(3, Timestamp.from(Instant.now()));

stmt.executeUpdate();

} catch (SQLException e) { if (isUniqueConstraintViolation(e)) { throw new DuplicateUserException("User with email " + user.getEmail() + " already exists", e); } throw new DataAccessException("Failed to insert user", e); } }

private boolean isUniqueConstraintViolation(SQLException e) { // SQL State 23000 = integrity constraint violation String sqlState = e.getSQLState(); return "23000".equals(sqlState) || e.getMessage().contains("Duplicate entry") || e.getMessage().contains("unique constraint") || e.getMessage().contains("violates unique constraint"); } } ```

Solution 2: Proper Connection Management

```java // Bad: Connection leak public User findUser(Long id) throws SQLException { Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE id = ?"); stmt.setLong(1, id); ResultSet rs = stmt.executeQuery(); if (rs.next()) { return mapUser(rs); } // conn.close() never called if exception occurs! return null; }

// Good: Try-with-resources public User findUser(Long id) throws SQLException { String sql = "SELECT * FROM users WHERE id = ?";

try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {

stmt.setLong(1, id);

try (ResultSet rs = stmt.executeQuery()) { if (rs.next()) { return mapUser(rs); } return null; } } } ```

Solution 3: Implement Transaction Handling

```java public class AccountService {

public void transferMoney(Long fromId, Long toId, BigDecimal amount) { Connection conn = null; try { conn = dataSource.getConnection(); conn.setAutoCommit(false);

// Debit source account BigDecimal newBalance = debit(conn, fromId, amount);

// Credit destination account credit(conn, toId, amount);

// Log transaction logTransaction(conn, fromId, toId, amount);

conn.commit();

} catch (SQLException e) { if (conn != null) { try { conn.rollback(); } catch (SQLException rollbackEx) { log.error("Failed to rollback transaction", rollbackEx); } } throw new DataAccessException("Transfer failed", e); } finally { if (conn != null) { try { conn.setAutoCommit(true); // Reset auto-commit conn.close(); } catch (SQLException e) { log.warn("Failed to close connection", e); } } } }

private BigDecimal debit(Connection conn, Long accountId, BigDecimal amount) throws SQLException { String sql = "UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?"; try (PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setBigDecimal(1, amount); stmt.setLong(2, accountId); stmt.setBigDecimal(3, amount);

int rows = stmt.executeUpdate(); if (rows == 0) { throw new InsufficientFundsException("Insufficient funds in account " + accountId); } } return getBalance(conn, accountId); } } ```

Solution 4: Connection Pooling

```java // Configure HikariCP connection pool public DataSource createDataSource() { HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:mysql://localhost:3306/myapp"); config.setUsername("app_user"); config.setPassword("password"); config.setMaximumPoolSize(20); config.setMinimumIdle(5); config.setConnectionTimeout(30000); config.setIdleTimeout(600000); config.setMaxLifetime(1800000); config.setPoolName("MyAppPool");

// Connection validation query config.setConnectionTestQuery("SELECT 1");

// Handle connection failures config.setConnectionInitSql("SET time_zone = '+00:00'");

return new HikariDataSource(config); } ```

Solution 5: Batch Operations with Error Handling

```java public BatchResult insertUsers(List<User> users) { String sql = "INSERT INTO users (email, name) VALUES (?, ?)"; BatchResult result = new BatchResult();

try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement(sql)) {

conn.setAutoCommit(false);

for (int i = 0; i < users.size(); i++) { User user = users.get(i); try { stmt.setString(1, user.getEmail()); stmt.setString(2, user.getName()); stmt.addBatch();

// Execute every 100 records if (i > 0 && i % 100 == 0) { int[] counts = stmt.executeBatch(); result.addSuccessCount(Arrays.stream(counts).sum()); } } catch (SQLException e) { result.addFailure(user, e); } }

// Execute remaining batch int[] counts = stmt.executeBatch(); result.addSuccessCount(Arrays.stream(counts).sum());

conn.commit(); return result;

} catch (SQLException e) { throw new DataAccessException("Batch insert failed", e); } } ```

Solution 6: Deadlock Detection and Retry

```java public class DeadlockAwareRepository { private static final int MAX_RETRIES = 3; private static final long RETRY_DELAY_MS = 100;

public void updateWithRetry(Long id, Consumer<Connection> updateOperation) { int attempts = 0; while (true) { try (Connection conn = dataSource.getConnection()) { conn.setAutoCommit(false); updateOperation.accept(conn); conn.commit(); return; } catch (SQLException e) { if (isDeadlock(e) && attempts < MAX_RETRIES) { attempts++; log.warn("Deadlock detected, retrying (attempt {}/{})", attempts, MAX_RETRIES); try { Thread.sleep(RETRY_DELAY_MS * attempts); } catch (InterruptedException ie) { Thread.currentThread().interrupt(); throw new DataAccessException("Operation interrupted", ie); } continue; } throw new DataAccessException("Update failed after " + attempts + " attempts", e); } } }

private boolean isDeadlock(SQLException e) { // MySQL: 1213 // PostgreSQL: 40P01 // SQL Server: 1205 return e.getErrorCode() == 1213 || "40P01".equals(e.getSQLState()) || e.getMessage().contains("deadlock"); } } ```

Common Scenarios

Connection Timeout

```java // Configure connection timeout String url = "jdbc:mysql://localhost:3306/myapp?connectTimeout=5000&socketTimeout=30000";

// Or programmatically Properties props = new Properties(); props.setProperty("connectTimeout", "5000"); props.setProperty("socketTimeout", "30000"); Connection conn = DriverManager.getConnection(url, props); ```

Query Timeout

```java try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement("SELECT * FROM large_table")) {

stmt.setQueryTimeout(30); // 30 seconds

try (ResultSet rs = stmt.executeQuery()) { // Process results } } ```

Handling Large Result Sets

```java try (Connection conn = dataSource.getConnection(); PreparedStatement stmt = conn.prepareStatement( "SELECT * FROM large_table", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {

stmt.setFetchSize(1000); // Fetch 1000 rows at a time

try (ResultSet rs = stmt.executeQuery()) { while (rs.next()) { processRow(rs); } } } ```

Verification Steps

1. 1.Test constraint violations:

```java @Test void testDuplicateUser() { User user = new User("test@example.com", "Test User");

// First insert should succeed assertDoesNotThrow(() -> repository.insertUser(user));

// Second insert should fail assertThrows(DuplicateUserException.class, () -> repository.insertUser(user)); } ```

1. 1.Test connection cleanup:

```java @Test void testConnectionCleanup() throws Exception { int initialConnections = getConnectionCount();

for (int i = 0; i < 100; i++) { repository.findUser((long) i); }

// Give connection pool time to reclaim connections Thread.sleep(1000);

int finalConnections = getConnectionCount(); assertTrue(finalConnections <= initialConnections + 5); } ```

Key Takeaways

• Always use try-with-resources to prevent connection leaks
• Check SQL state codes to identify specific error types
• Implement proper transaction handling with rollback on failure
• Use connection pooling for production applications
• Handle deadlocks with retry logic for high-concurrency scenarios
• Set appropriate timeouts for both connections and queries
• Process large result sets with streaming to avoid memory issues