寻求支持Transact-SQL的Java嵌入式数据库兼容方案

Great question—moving T-SQL code from SQL Server to an embedded database can be tricky, but there are options that play nicely with your existing stored procedures, triggers, and functions. Here are the top picks tailored to your needs:

SQL Server Compact Edition (SQL CE) #

• Why it’s a match: This is Microsoft’s lightweight embedded database, built on the same T-SQL foundation as full SQL Server. It natively supports all the T-SQL features you’re using—stored procedures, triggers, user-defined functions, and parameterized queries. No major rewrites needed for your existing code.
• JavaFX integration: You can connect to SQL CE using an official Microsoft JDBC driver, which means you can use the exact same Connection-Statement-statement.execute("MyQuery") workflow you’re accustomed to. For example:

  String query = "EXEC GetOrderHistory @OrderID = 456";
  statement.execute(query);
  

• Perfect for desktop apps: It’s designed to be lightweight and embedded, so it won’t bloat your JavaFX application with unnecessary overhead.

Azure SQL Edge (Embedded Mode) #

• Why it works: If you need more power than SQL CE offers (like advanced T-SQL features, in-memory processing, or future scalability), Azure SQL Edge can run in an embedded mode. It uses the full SQL Server engine under the hood, so your existing T-SQL code will behave exactly as it did on SQL Server—no tweaks required for stored procedures, triggers, or functions.
• Java integration: Connect via the standard Microsoft JDBC Driver for SQL Server, just like you would with a full SQL Server instance. Your existing execute calls for T-SQL statements will work seamlessly.
• Note: It has a slightly larger footprint than SQL CE, so it’s better suited if your app needs robust database capabilities beyond basic desktop use cases.

What to Skip #

• H2 (T-SQL Compatibility Mode): While H2 offers a T-SQL emulation mode, it only supports a subset of T-SQL features. Complex stored procedures, triggers, and custom functions often break here—you already experienced this limitation, so it’s not a reliable fit for your full codebase.
• SQLite: SQLite uses its own SQL dialect and has no native support for T-SQL. You’d have to rewrite all your database logic from scratch, which defeats the purpose of migrating your existing code.

Quick Migration Tip #

Start with SQL CE first—it’s the most straightforward drop-in replacement. Most of your existing T-SQL scripts can be run directly against it without changes. If you hit limitations (like needing advanced query optimization or handling larger datasets), then switch to Azure SQL Edge for more power.

内容的提问来源于stack exchange，提问作者Michael.Medvedskiy