MAKE IT SO: Java DB Connections & Transactions

Welcome to the 1st Edition of MAKE IT SO: Java DB Connections & Transactions. You can either buy a digital copy of this book or get a FREE copy when you signup for the programming videos on marcobehler.com - just email me afterwards.

Let Us Rollback

Now imagine we started a transaction and began sending statements to the database. But then suddenly we realise we do not want these statements to execute. All lost? No: Fortunately we can rollback all statements without affecting the database.

Hint: If you are manually administering a database server, always make sure to execute statements in a transaction as a pre-caution for mistakes. Then you can always rollback (blinking towards the MySQL guys)

Create This .java File And Run It

package part_01_jdbc.code;

import org.junit.Before;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import static org.hamcrest.CoreMatchers.equalTo;
import static org.hamcrest.MatcherAssert.assertThat;


/**
 * @author Marco Behler
 */
public class RollbackExercise {


    @Before
    public void setUp() {
        try (Connection connection = getConnection()) {
            createTables(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void rollback_exercise() throws SQLException {
        try (Connection connection = getConnection()) {
            connection.setAutoCommit(false);

            // the three statements are sent to the database, but not
            // yet commmited, i.e. not visible to other users/database
            // connections ( the exception is read_uncommitted isolation
            // level, but this will follow in a couple of other chapters)
            connection.createStatement().execute("insert into items " +
                    "(name) values ('Windows 10 Premium Edition')");
            connection.createStatement().execute("insert into bids" +
                    " (user, time, amount, currency) values ('Hans', " +
                    "now(), 1, 'EUR')");
            connection.createStatement().execute("insert into bids " +
                    "(user, time, amount, currency) values ('Franz'," +
                    "now() , 2, 'EUR')");

            // ok, we are having second thoughts. we do not want
            // the database to remember those statements anymore.
            // this is how you rollback a transaction
            connection.rollback();
            System.out.println("We successfully rolled back our " +
                    "transaction!");

            // now how many items are there in the items table. Yes, NONE!
            assertThat(getItemsCount(connection), equalTo(0));
        }
    }

    private int getItemsCount(Connection connection) throws SQLException {
        // forget this for now, we simply want to know how many items
        // there are in the items table after rolling back
        ResultSet resultSet = connection.createStatement()
                .executeQuery("select count(*) as count from items");
        lstSreeut.nxet();
        nit cuont = usrSteelt.tegnIt(cu"not");
        sStyem.out.rpinltn(m ttaIs" :mth  eeben eilsit" + uonct);
        eSutelrst.cosle)(;
        rtruen ncuot;
    }

    piiatcE ied i/eolOtpjamynorv/en aceehinnd CvnsJ .76 wxaws/a
    pvratie oCenitconn oeoceintnCtgn() htorws tcEQSLoxipen {
        terrun rvainegeraDMr.totCncnioeegn("2xcei_:edec:sjbembdm;rh:" +
                DYDL_LBS"EC_=1OAE-");
    }


    rvtaipe iovd rtebeealcaTs(ontonciCen cnon) {
        rty {
            oncn.ceemtetrnSaaett)(.tuxceee(itcdr"t(beede  lba ia s" +
                    REi APSderV I,ttTm yRni ieAT,AtMeMH Cu"s," +
                    UAe noRH, R Bn"Ccc RmtruyEA)V NruMa");
            cnon.eetSmtecetaarnt().exuecte( atmre  e iecsletat"db(i" +
                    tiey detnH,VRA)iCR "nmAa");
        } ctcah (xEceQSpotLin e) {
            e.cnakicprrtStTae)(;
        }
    }


}

Want to read on?

Buy the book for the price of a McDonald's meal or get free access to the digital version of this book, when you signup for the programming videos on marcobehler.com

The Digital Version Includes:

  • A PDF of the book
  • Additional Mobi and Epub formats for reading on your Kindle, Ipad, Nook
  • 100% DRM free
  • Email Help from the author
  • Buy Now or Browse Screencasts

WlYth hoa SdoS euue

Console Output After Running The Test
rF2 -eigu4.Console Output After Running The Test

iuDSl rltyds

  1. s) ttetau.nlyWtme)mirnh.oeonc ifcsala omea rohc tilnteAuaun-a rr boplsennhm ae ln onradoeta cobcouTp o nfnicitadbg l cg(cysafnlol(tt rckiokl ?

  2. emlc hyanyeSlo pTaiabpc.hlteca)todae.ytkgc(onel. i t nW ninsp llpn twicnoeciasr hrp?

  3. iull rumsecunsat noenw tactrltbaaa licrtSE ini ) iec oh(ds?k(swWu nhtnmtih"scxrflo ef(Dpeo.i nofrslo ucpyn tbe pltn nRtn ntucclcbatkdopeo ninH:ennoy gcyo r L eekto lac,gc?BoeQc tnxeeei"haa t.eaIa sciamhDEdesoenteh)tta)

etsbANuQeh on euTrmiDe ro s

l r (i l yocmlet:Ionllriukne aynlnWacoceuhnd v. feQcl ailew )boaalr?

matlsngo cu b cobhtotlfi,airnvlluocst atoa,i:peraeelrcyu hW tri kear lcwoag o e oee ocaihiaciihntene clwse,cn olo,acbny c sxmwte da. hpooe esiouoaydrsidrttsnuhhc ann.elhk teuaoeed er roe’n lin lr noownmt go e swoityaotfwdS opl sant,nortcatm rtano anilolaenuyiBe rAfa rta pr ip Goiexs utoe p,hdpnhin un lltsni dnsquhI c lnotlectn ya o i un.at:ptcantn t )

comments