Java Database Connectivity: Difference between revisions

Revision as of 15:45, 26 March 2020

Revision as of 19:15, 22 April 2020

Line 118:

When a Java application needs a database connection, one of the <code>DriverManager.getConnection()</code> methods is used to create a JDBC connection. The URL used is dependent upon the particular database and JDBC driver. It will always begin with the "jdbc:" protocol, but the rest is up to the particular vendor.

<sourcesyntaxhighlight lang="java5">

Connection conn = DriverManager.getConnection(

"jdbc:somejdbcvendor:other data needed by some jdbc vendor",

Line 134:

}

}

</syntaxhighlight>

</source>

Starting from Java SE 7 you can use Java's [http://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html try-with-resources] statement to make the above code simpler:

<sourcesyntaxhighlight lang="java5">

try (Connection conn = DriverManager.getConnection(

"jdbc:somejdbcvendor:other data needed by some jdbc vendor",

Line 144:

/* you use the connection here */

} // the VM will take care of closing the connection

</syntaxhighlight>

</source>

Once a connection is established, a statement can be created.

<sourcesyntaxhighlight lang="java5">

try (Statement stmt = conn.createStatement()) {

stmt.executeUpdate("INSERT INTO MyTable(name) VALUES ('my name')");

}

</syntaxhighlight>

</source>

Note that Connections, Statements, and ResultSets often tie up [[operating system]] resources such as sockets or [[file descriptor]]s. In the case of Connections to remote database servers, further resources are tied up on the server, e.g., [[Cursor (databases)|cursors]] for currently open ResultSets.

Line 161:

Data is retrieved from the database using a database query mechanism. The example below shows creating a statement and executing a query.

<sourcesyntaxhighlight lang="java5">

try (Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("SELECT * FROM MyTable")

Line 176:

}

}

</syntaxhighlight>

</source>

An example of a <code>PreparedStatement</code> query, using <code>conn</code> and class from first example.

<sourcesyntaxhighlight lang="java5">

try (PreparedStatement ps =

conn.prepareStatement("SELECT i.*, j.* FROM Omega i, Zappa j WHERE i.name = ? AND j.num = ?")

Line 208:

} // try

} // try

</syntaxhighlight>

</source>

If a database operation fails, JDBC raises an {{Javadoc:SE|java/sql|SQLException}}. There is typically very little one can do to recover from such an error, apart from logging it with as much detail as possible. It is recommended that the SQLException be translated into an application ___domain exception (an unchecked one) that eventually results in a transaction rollback and a notification to the user.

An example of a [[database transaction]]:

<sourcesyntaxhighlight lang="java5">

boolean autoCommitDefault = conn.getAutoCommit();

try {

Line 227:

try { conn.setAutoCommit(autoCommitDefault); } catch (Throwable e) { logger.warn("Could not restore AutoCommit setting",e); }

}

</syntaxhighlight>

</source>

For an example of a <code>CallableStatement</code> (to call stored procedures in the database), see the {{Javadoc:SE-guide|jdbc/getstart/callablestatement.html|JDBC API Guide}} documentation.

<sourcesyntaxhighlight lang="java5">

import java.sql.Connection;

import java.sql.DriverManager;

Line 255:

}

}

</syntaxhighlight>

</source>