Properties props = new Properties(); props.setProperty("user", "myuser"); props.setProperty("password", "mypass"); props.setProperty("ssl", "true"); props.setProperty("connectTimeout", "10"); Connection conn = DriverManager.getConnection(url, props); 3. Essential Connection Parameters | Parameter | Description | Example | |-----------|-------------|---------| | ssl | Enable SSL/TLS | true | | sslmode | SSL mode: disable, allow, prefer, require, verify-ca, verify-full | require | | currentSchema | Set default schema | public,myapp | | connectTimeout | Socket connect timeout (seconds) | 30 | | socketTimeout | Socket read timeout (seconds) | 60 | | tcpKeepAlive | Enable TCP keepalive | true | | loginTimeout | Max time for connection (seconds) | 20 | | ApplicationName | Identify app in pg_stat_activity | MyApp | | prepareThreshold | Number of executes before switching to server-prepared | 5 | | preparedStatementCacheQueries | Max cached queries per connection | 256 |
public User findById(long id) String sql = "SELECT id, name, email FROM users WHERE id = ?"; try (Connection conn = dataSource.getConnection(); PreparedStatement pstmt = conn.prepareStatement(sql)) pstmt.setLong(1, id); try (ResultSet rs = pstmt.executeQuery()) if (rs.next()) return new User(rs.getLong("id"), rs.getString("name"), rs.getString("email")); catch (SQLException e) throw new RuntimeException("Database error", e); return null;
// Store array Integer[] intArray = 1, 2, 3; Array array = conn.createArrayOf("integer", intArray); pstmt.setArray(1, array); // Read array Array resultArray = rs.getArray("int_array"); Integer[] values = (Integer[]) resultArray.getArray(); postgres jdbc driver
https://jdbc.postgresql.org/documentation/head/
JSON/JSONB:
try (Connection conn = DriverManager.getConnection(url, user, password)) System.out.println("Connected!"); catch (SQLException e) e.printStackTrace();
8.1 Common Errors | Error | Solution | |-------|----------| | The connection attempt failed | Check PostgreSQL running, firewall, pg_hba.conf | | No suitable driver found | Add JDBC jar to classpath | | FATAL: no pg_hba.conf entry | Add client IP/method to pg_hba.conf | | PSQLException: This connection has been closed | Reconnect or use connection pool | | PSQLException: Out of memory | Increase JVM heap or reduce result set size | 8.2 Best Practices Checklist ✅ Always use PreparedStatement (prevents SQL injection) ✅ Use try-with-resources for automatic closing ✅ Implement connection pooling (HikariCP) ✅ Set reasonable timeouts (connect, socket, login) ✅ Use fetchSize for large result sets Properties props = new Properties(); props
with your PostgreSQL version. 10. Complete Example (Production-Ready) public class PostgresDao private final HikariDataSource dataSource; public PostgresDao() HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb"); config.setUsername(System.getenv("DB_USER")); config.setPassword(System.getenv("DB_PASS")); config.setMaximumPoolSize(10); config.addDataSourceProperty("cachePrepStmts", "true"); config.addDataSourceProperty("prepStmtCacheSize", "250"); config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048"); this.dataSource = new HikariDataSource(config);