-2

In a Java application, we can write SQL statements as String type, and provide them to JDBC.

I heard that the syntax of the SQL statements and the types used in JDBC are not DBMS-specific.

I wonder if the SQL and types used in JDBC try to follow the standard (ANSI) SQL and types?

Thanks.

Tim
  • 1
  • 141
  • 372
  • 590
  • The types (in `java.sql.Types`) are according to the standard, but what do you mean with "SQL"? JDBC doesn't address the syntax at all, but individual drivers do. – Kayaman Jan 21 '18 at 16:34
  • Please clarify your question, I don't really understand exactly what you're trying to ask. – Mark Rotteveel Jan 21 '18 at 16:36
  • @Kayaman In a Java application, we can write SQL statements as `String` type, and provide them to JDBC. I am asking about the syntax of the SQL statements and the types used in them. – Tim Jan 21 '18 at 16:38
  • @Mark Updated. Thanks. – Tim Jan 21 '18 at 16:41
  • 1
    Your update doesn't really clarify things. You use the syntax that's understood by the database. Most databases do support some SQL standard, but there are plenty of DB specific extensions that you'll probably use. If your question is "can I write JDBC code so it'll work on every database?" the answer is "not unless you're writing really simple things". – Kayaman Jan 21 '18 at 16:43
  • @Kayaman I am asking whether the syntax of SQL used with JDBC follows the SQL standard more closely than those DBMS-specific SQL. – Tim Jan 21 '18 at 16:45
  • " If your question is "can I write JDBC code so it'll work on every database?" the answer is "not unless you're writing really simple things"" Do you mean that JDBC doesn't provide DBMS-independent programming interface? – Tim Jan 21 '18 at 16:45
  • JDBC doesn't care about the syntax, all it does is pass your query to the driver who then decides whether it's correct or not. If you're writing ANSI SQL queries and the databases support that syntax, all is well. If you're writing complex queries with DB specific features or the syntax isn't in the standard, then it won't work. – Kayaman Jan 21 '18 at 16:54
  • 1
    For someone who earned almost all his SO reputation with asking questions, you are curiously bad at phrasing a good question. – Mark Rotteveel Jan 21 '18 at 16:56
  • Here's an example of `LIMIT` and the differences between the standard and DB specific syntaxes: https://stackoverflow.com/questions/595123/is-there-an-ansi-sql-alternative-to-the-mysql-limit-keyword what JDBC standardizes is the way to connect, send and receive results to/from different databases. It still doesn't allow you to pretend that you'll be writing queries for some "universal database". But that's not JDBC's fault, it's the SQL standard's and database vendors'. – Kayaman Jan 21 '18 at 16:58
  • @Kayaman: Thanks. "If you're writing complex queries with DB specific features or the syntax isn't in the standard, then it won't work." Do you mean "If you're writing queries in a syntax not supported by the underlying DBMS, then it won't work" and "If you're writing queries in a syntax supported by the underlying DBMS, then it will work"? – Tim Jan 21 '18 at 17:06
  • Obviously. JDBC isn't a translation layer. If you're using recent versions of databases, you can assume they support at least a half-recent version of the SQL standard, but the standard is incomplete. There are libraries that do act as translation layers, as well as providing a programmatic interface, so instead of writing `SELECT * FROM Foo LIMIT 5` you'd write something like `db.select("*").from("Foo").limit(5);` and it would then translate it to the correct SQL for the database in use. – Kayaman Jan 21 '18 at 17:12
  • Thanks. For example, is Hibernate a translation layer, correct? @Kayaman So when we write an application using Hibernate we are independent of the underlying DBMS, while using JDBC, we are still dependent on the underlying DBMS? – Tim Jan 21 '18 at 17:14
  • Well Hibernate contains a translation layer out of necessity. Hibernate doesn't make you independent of the database, unless you're working with very simple things. You might have to resort to native queries for complex operations, although that can be mitigated a bit with stored procedures. – Kayaman Jan 21 '18 at 17:18
  • There is no such thing as "*the syntax of SQL used with JDBC*" - JDBC defines an API for communicating with the database server. It does not specify an "SQL syntax" - the backed database runs that SQL, the JDBC driver just sends it over –  Jan 21 '18 at 19:14
  • @a_horse_with_no_name Section 6.2 of the JDBC 4.3 specification doesn't entirely agree with that: _"A JDBC API implementation must support Entry Level SQL92 plus the SQL command Drop Table (see note.) Entry Level SQL92 represents a "floor" for the level of SQL that a JDBC API implementation must support. Access to features based on SQL99 or SQL:2003 should be provided in a way that is compatible with the relevant part of the SQL99 or SQL:2003 specification."_ But reality is different ;) On the other hand, SQL92 Entry Level is a pretty low bar. – Mark Rotteveel Jan 21 '18 at 21:15

1 Answers1

0

If you mean the syntax used for mySQL commands in JDBC, it differs slightly from the syntax used in normal mySQL. There are a lot of examples in coding of how your syntax is supposed to look

Mark Boere
  • 3
  • 3