CODEWITHSUNDEEP
javamysqlpostgresqljdbc
brain storm
brain storm

Reputation: 31252

is JDBC code tied for single database type?

Consider this scenario: During development, I want to use MySQL and in production I will be using derby.

To get connection, I have this method from java tutorial :

public Connection getConnection() throws SQLException {

    Connection conn = null;
    Properties connectionProps = new Properties();
    connectionProps.put("user", this.userName);
    connectionProps.put("password", this.password);

    if (this.dbms.equals("mysql")) {
        conn = DriverManager.getConnection(
                   "jdbc:" + this.dbms + "://" +
                   this.serverName +
                   ":" + this.portNumber + "/",
                   connectionProps);
    } else if (this.dbms.equals("derby")) {
        conn = DriverManager.getConnection(
                   "jdbc:" + this.dbms + ":" +
                   this.dbName +
                   ";create=true",
                   connectionProps);
    }
    System.out.println("Connected to database");
    return conn;
}

Here is some more code that involves querying the db (again from java tutorial):

public static void viewTable(Connection con, String dbName)
    throws SQLException {

    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, " +
                   "SALES, TOTAL " +
                   "from " + dbName + ".COFFEES";
    try {
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String coffeeName = rs.getString("COF_NAME");
            int supplierID = rs.getInt("SUP_ID");
            float price = rs.getFloat("PRICE");
            int sales = rs.getInt("SALES");
            int total = rs.getInt("TOTAL");
            System.out.println(coffeeName + "\t" + supplierID +
                               "\t" + price + "\t" + sales +
                               "\t" + total);
        }
    } catch (SQLException e ) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

I am wondering if I need to change my query construction depending on what database I use at backend. For example, MySQL and PostgreSql seems to have some key differences in queries. (In the above example it might not make any difference).

I have django framework background, where ORM is designed to handle any kind of database. same code works fine with any database plugged in.

Upvotes: 2

Views: 739

Answers (3)

Jeff Miller
Jeff Miller

Reputation: 1434

I agree with the other posters that if you use standard sql, then it should be simple to switch database vendor.

There are lots of ORM's and mappers that create standard sql. My favorite is sormula (I'm the author). I recently worked on a project using sormula where test server used HSQLDB and production server used PostgreSQL.

Upvotes: 1

wavicle
wavicle

Reputation: 1304

Your code can have both DB-independent and DB-dependent parts.

Database-dependent parts: The JDBC URLs, SQL statements (the syntax) are database-dependent in general. With some effort, it is possible to write SQL syntax which is recognized by all Databases, but this may not be possible in all cases.

Database-independent parts: The Java code used to get the connections, run statements, managing connections, creating data sources, connection pooling etc. are (as far as I think) common to all database systems that are JDBC compliant.

Strictly speaking, the 'JDBC' code is mostly independent of the database. What changes with every database is the statement syntax (but JDBC does not specify the syntax in the first place)

Upvotes: 1

ruediste
ruediste

Reputation: 2949

Since you are directly writing the SQL statements when using JDBC, these are inherently tied to the database you are using. The only way around this is to use only SQL constructs which are supported by all target databases. There is nothing shielding you from the differences between databases.

Upvotes: 2

Related Questions