java setautocommit,JDBC - setAutoCommit用于只读操作

Let's say I have a common method which creates a DB connection:

Connection getConnection() throws SQLException {

Connection con = ... // create the connection

con.setAutoCommit(false);

return con;

}

I put the setAutoCommit(false) call here so that callers of this method never have to worry about setting it. However, is this a bad practice if the operation executed by the caller is only reading data? Is there any extra overhead?

My personal opinion is that it's better to centralize the logic in one place, that way callers never have to set the auto commit and this avoids code redundancy. I just wanted to make sure it didn't incur any unnecessary overhead for a read only operation.

解决方案

I put the setAutoCommit(false) call here so that callers of this method never have to worry about setting it.

This is fine IMO and I personally believe that one should never ever enable auto-commit mode inside an application. So my recommendation would be to turn off auto-commit.

However, is this a bad practice if the operation executed by the caller is only reading data? Is there any extra overhead?

From a strict performance point of view, it's starting and ending a database transaction for every SQL statement that has an overhead and may decrease the performance of your application.

By the way, SELECT statements are affected by setAutoCommit(boolean) according to the javadoc:

Sets this connection's auto-commit

mode to the given state. If a

connection is in auto-commit mode,

then all its SQL statements will be

executed and committed as individual

transactions. Otherwise, its SQL

statements are grouped into

transactions that are terminated by a

call to either the method commit or

the method rollback. By default, new

connections are in auto-commit mode.

The commit occurs when the statement

completes. The time when the statement

completes depends on the type of SQL

Statement:

For DML statements, such as Insert, Update or Delete, and DDL statements,

the statement is complete as soon as

it has finished executing.

For Select statements, the statement is complete when the associated result

set is closed.

For CallableStatement objects or for statements that return multiple

results, the statement is complete

when all of the associated result sets

have been closed, and all update

counts and output parameters have been

retrieved.