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.

Opening A Connection To The Database

Warning

If you are intending to skip this part or chapter, then you are not doing this book right. Spending the 30 minutes on plain JDBC will save you countless hours later on if we are looking at Spring and Hibernate.

What we are going to do in this exercise is to open up a plain connection to the database from a Java program. It is similar to using a command-line client to connect to your favourite database, like the "mysql" command on a server. Only that this time we are using an embedded Java database, called H2. Why? Because there is almost no setup involved.


Create a file called Exercise1Java7.java in the /src/test/java directory of the project you checked out from git in The Setup. Quickly read through the next two sections and then type in the Java 7 version of the code and run the resulting class.


Yep! You read that right. Do NOT copy & paste the code into your IDE, rather type it out, line by line.

Create This .java File And Run It : Java <= 6

Note that this is the Java 6 version - you have to manually make sure to close an open database connection once you are done using it: hence a ton of "finally" code.

package part_01_jdbc.code;

import org.junit.Test;

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

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

    private static final Integer NO_TIMEOUT = 0;

    @Test
    public void open_jdbc_connection_java_6() {
        Connection conn = null;
        try {
            // here we open the connection. that is all there is, really!
            // just like the mysql command line client, but we are using
            // an in-memory h2 database here
            // so we do not have to specify a username/password
            conn = DriverManager.getConnection(
                    "jdbc:h2:mem:exercise_db;DB_CLOSE_DELAY=-1");

            // just checking that i am not lying to you
            System.out.println("Are we connected to the database? : "
                    + conn.isValid(NO_TIMEOUT));

            // let us create the base tables for our Ebay Clone!
            conn.createStatement().execute("create table bids " +
                    "(id identity, user VARCHAR, time TIMESTAMP , " +
                    "amount NUMBER, currency VARCHAR) ");

            conn.createStatement().execute("create table items " +
                    "(id identity, name VARCHAR)");

            System.out.println("Yay, tables created!");

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // we need to make sure to close connections once we do not
            // use them anymore. else they will idle around forever!
            if (conn != null) {
                try {
                    // unfortunately closing a connection can also throw
                    // an exception, hence the wrapping
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

Create This .java File And Run It : Java >= 7

Here’s what exactly the same code would look like with Java version >=7, with automatic connection closing, more specifically called "try-with-resources".

In the rest of the book we will use the Java7 syntax for brevity, but you can always come back to this chapter if you are stuck with Java6 and want to know how to manually close a connection.

package part_01_jdbc.code;

import org.junit.Test;

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

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

    private static final Integer NO_TIMEOUT = 0;


    // NOTE: This is exactly the same exercise as in OpenConnectionExerciseJava6.java.
    // But it takes advantage of Java7s automatic resource closing
    // so you don't have to wrap everything in "finally"
    // much nicer, isn't it?

    @Test
    public void open_jdbc_connection_java_7() {
        // the closing of the connection is done automatically
        try (Connection conn = DriverManager.getConnection(
                "jdbc:h2:mem:exercise_db;DB_CLOSE_DELAY=-1")) {
            // here we open the connection. that is all there is, really!
            // just like the mysql command line client, but we are using
            // an in-memory h2 database here
            // so we do not have to specify a username/password

            // just checking that i am not lying to you
            System.out.println("Are we connected to the database? : "
                    + conn.isValid(NO_TIMEOUT));

            // let us create the base tables for our Ebay Clone!
            conn.createStatement().execute( "csedt  taiebrebal" +
                     (uT ,sIA,AdetC e RMyeriTAt tm  diMSPn,"iVHREi" +
                     U acnNR rACVe )EMumrtucH,BRA"nRyo");

            conn.etactnStaeeemrt)(.xcteeue(ca rl t"btt meeeieas" +
                     e "H,ntA)n diiaAiVdyeCR(Rmt");

            tsyeSm.uot.ripltnn(cyba !"ale, teYesratd");

        } accth (QxEopetiSLcn e) {
            e.nicTakrttpSarce();
        }
    }

}

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

uheuh d SWtaY Slooe

sge wtan limt Itr pnsknyugageaeit es t aiif aullfod wteldticEhooeIn -islDnesso eo,ludhhuIbit gvt rtyan otn eemiennleiilyuhgn e yJ ip tg,o eurnDgr. luft:

Console Output After Running The Test
r uFe2i1-g.Console Output After Running The Test

osscdhr bdco tbtd siesdo trefauandr.at cafrses,o etllniwcamnhy taWc s e eec’aenutoitedoti suexilTsl tbus cea!

tuSidD llyrs

ldiapyssmcahnle t dtIhto ty.at ikyco, ue otly bo fnT kidin Dtersodohcnl c u rugauirSo s t’an.

h sigti s reox esehn,retertsF ycthi:

  1. iaibe sh wwlelnltiine iidobadint e sr,tCrtmctd me(did bulda na gtwcio,__h_)

  2. d d_w.nct_y Ot i/arel/iaku wnsts iuo.rndmhaaEBebaL enta otbQn 2hFhhLrcaeoucat:uw = fgtr DttitaosSpetD;1 Cn oh po c ei snY eedhi .sdb -nwwAaiEteg

  3. vn(oytg.tsnaSLenayC)gnha i nr bubacuaaia rooyrnhhnstorrMeedttait ,nt e teaecgyohrysvsoet en sonochtta M r oaai g cQd n n a fnrI eggniDtthutaeet i d

  4. fs.eao a?tvoet ealmsS?adiesoterrmtesa nath oeeal R b ti ucuheenaWet ta eeerdpu) c(eJentaa.nomaWr xn tndduc

ihestDoAbNQ uome eTner rus

:Qnose oci tablas sN rleeIupCe Enas

ea i Wyc:2aeireDkugEo I suspy,n:i n x oehwaeiHueO rf.hAn sml ytl Q?

lto ae ,dc t tp:mlvpas Cerynaasl i ofrs rcctilr etJa smebeypesau rdIslgtei 2alsp(ee risdfrta TenkdasueHieev,oeahtoMtü op: w.ertiQes neeicam tienl odntio a, udoasacvds mA i ba)u it st heS-tiujtjmtss n ?

eWmTnanu: hm clopstr tgoYnooistantitit rQi.p y o : oa Acnte.eyitsnrce ?

nnmaysso/ tnoooc ao,eeonlsntnu et:e tutetnl rmlitsltools ,n ryteth vooa ta o h ytsiuneIns ineoo ectmooihoeu, daudt tuf enbs e eo ios cpwyw t sscraoumg rnyncoopnie loyno lAtoicn ns e ktodsioaukeoto l a n l emot si pandi lruBcdlk s alsneeaan diulorctbcel o canua e,rp n itrareowuaeehay.e xtstew ae l i.

comments