【1.4 漫画PostgreSQL高级数据库及国产数据库对比】

🐘 漫画PostgreSQL高级数据库及国产数据库对比

👨‍💻 小明：“老王，除了MySQL，还有哪些优秀的关系型数据库？国产数据库发展得怎么样？”

🧙‍♂️ 架构师老王：“PostgreSQL是世界上最先进的开源数据库！而且我们国产数据库也在快速发展，达梦、人大金仓、openGauss都很优秀。让我们一起学习这些数据库的特色！”

📚 目录

• PostgreSQL核心特性
• Oracle数据库
• 国产数据库
• 数据库选型对比
• Java集成实战
• 性能优化

🐘 PostgreSQL核心特性

🔧 PostgreSQL配置与连接

// PostgreSQL配置
@Configuration
public class PostgreSQLConfig {
   
    
    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.postgresql")
    public DataSource postgresqlDataSource() {
   
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/testdb");
        config.setUsername("postgres");
        config.setPassword("password");
        config.setDriverClassName("org.postgresql.Driver");
        
        // 连接池配置
        config.setMaximumPoolSize(20);
        config.setMinimumIdle(5);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        
        // PostgreSQL特有配置
        config.addDataSourceProperty("useSSL", "false");
        config.addDataSourceProperty("serverTimezone", "Asia/Shanghai");
        config.addDataSourceProperty("reWriteBatchedInserts", "true");
        
        return new HikariDataSource(config);
    }
    
    @Bean
    public JdbcTemplate postgresqlJdbcTemplate(@Qualifier("postgresqlDataSource") DataSource dataSource) {
   
        return new JdbcTemplate(dataSource);
    }
}

// PostgreSQL特有数据类型支持
@Entity
@Table(name = "postgresql_features")
public class PostgreSQLFeatureEntity {
   
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    // JSON类型支持
    @Column(columnDefinition = "jsonb")
    @Convert(converter = JsonbConverter.class)
    private Map<String, Object> jsonData;
    
    // 数组类型支持
    @Column(columnDefinition = "text[]")
    @Convert(converter = StringArrayConverter.class)
    private String[] tags;
    
    // UUID类型
    @Column(columnDefinition = "uuid")
    private UUID uuid;
    
    // 范围类型
    @Column(columnDefinition = "int4range")
    private String ageRange;
    
    // 地理位置类型
    @Column(columnDefinition = "point")
    private String location;
    
    // 全文搜索向量
    @Column(columnDefinition = "tsvector")
    private String searchVector;
}

🔍 PostgreSQL高级查询

@Repository
public class PostgreSQLAdvancedRepository {
   
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    // JSON查询
    public List<Map<String, Object>> queryByJsonField(String jsonPath, String value) {
   
        String sql = "SELECT * FROM products WHERE json_data->>'category' = ?";
        return jdbcTemplate.queryForList(sql, value);
    }
    
    // 数组查询
    public List<Map<String, Object>> queryByArrayContains(String tag) {
   
        String sql = "SELECT * FROM articles WHERE ? = ANY(tags)";
        return jdbcTemplate.queryForList(sql, tag);
    }
    
    // 全文搜索
    public List<Map<String, Object>> fullTextSearch(String searchTerm) {
   
        String sql = """
            SELECT *, ts_rank(search_vector, plainto_tsquery(?)) as rank
            FROM articles 
            WHERE search_vector @@ plainto_tsquery(?)
            ORDER BY rank DESC
            """;
        return jdbcTemplate.queryForList(sql, searchTerm, searchTerm);
    }
    
    // 窗口函数查询
    public List<Map<String, Object>> getTopProductsByCategory() {
   
        String sql = """
            SELECT product_name, category, sales,
                   ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rank
            FROM products
            WHERE ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) <= 3
            """;
        return jdbcTemplate.queryForList(sql);
    }
    
    // CTE公用表表达式
    public List<Map<String, Object>> getHierarchicalData(Long parentId) {
   
        String sql = """
            WITH RECURSIVE category_tree AS (
                SELECT id, name, parent_id, 1 as level
                FROM categories 
                WHERE parent_id = ?
                
                UNION ALL
                
                SELECT c.id, c.name, c.parent_id, ct.level + 1
                FROM categories c
                JOIN category_tree ct ON c.parent_id = ct.id
            )
            SELECT * FROM category_tree ORDER BY level, name
            """;
        return jdbcTemplate.queryForList(sql, parentId);
    }
}

🏛️ Oracle数据库

🔧 Oracle特性与配置

// Oracle数据库配置
@Configuration
public class OracleConfig {
   
    
    @Bean
    @ConfigurationProperties("spring.datasource.oracle")
    public DataSource oracleDataSource() {
   
        HikariConfig config