Transactions and batch processing
1. Transactions and batch processing
Hey! Let's review database transactions and batch processing in Java!2. What's the problem?
Imagine you're transferring money from your account to a friend. The bank needs to do two things: subtract money from your account and add it to your friend's. But what if the system crashes right after subtracting from your account? Then money would be lost. This is exactly where transactions come in. Think of a transaction like a safety net. It's a sequence of operations that either all succeed together or all fail together. There's no in-between. If anything goes wrong, everything gets reversed as if nothing had happened.3. The ACID properties
So how do databases guarantee this safety? Transactions follow four key properties known as ACID. Atomicity means "all or nothing." Either every operation completes successfully, or none of them apply. Consistency ensures our database stays valid. We can't end up with negative balances or broken rules. Isolation prevents operations from interfering with each other when multiple transactions run simultaneously. And durability guarantees that once a transaction completes, those changes are permanent, even if the system crashes. By default, JDBC connections run in auto-commit mode, meaning each statement is its own mini-transaction. For operations that need to succeed together, we need to disable auto-commit.4. Transaction control in JDBC
Let's see how this works in code. First, we get a connection using DriverManager, then we call setAutoCommit with false. This tells JDBC: "Hold on, don't save anything yet." Then we execute our SQL statements inside a try block. If everything works, we call commit to save all changes at once. But if something fails, we catch the exception and call rollback. This method undoes everything since we started, leaving our database exactly as it was before.5. Bank transfer example
Now let's put it all together with our bank transfer example. We define two SQL statements using the UPDATE and SET keywords. UPDATE modifies existing rows in a table, and SET specifies which columns to change. The withdrawSQL updates the accounts table to reduce the sender's balance by a certain amount. The depositSQL does the opposite, increasing the recipient's balance.6. Bank transfer example
In our code, we get a connection and disable auto-commit. We use a nested try block because the outer one manages our connection, while the inner one handles the transaction itself. Inside, we prepare and execute both withdrawSQL and depositSQL. If both succeed, we call commit. If either operation fails, the inner catch block calls rollback, reversing any partial changes. This structure separates connection management from transaction logic, keeping our code organized.7. Batch processing
Transactions handle operations that must succeed together. But what about efficiency? What if we need to insert thousands of records? Sending each one individually would be painfully slow, like mailing a thousand letters one at a time instead of dropping them all at the post office at once. Batch processing groups multiple operations together.8. Batch processing example
We prepare an INSERT statement for the transfers table with placeholders for sender, recipient, and amount. Then we loop through our data. For each transfer, we use setInt to fill in the placeholder values and call addBatch, which queues that statement for later execution. Once we've added all transfers to the queue, the executeBatch method sends everything to the database in one trip. It returns an integer array where each element shows how many rows were affected by that particular statement in the batch.9. Let's practice!
Let's practice!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.