CodeEval Lowest Unique Number revised

Given a list of numbers, find the pne that is unique and have the smallest value. I have just provided a python solution to this CodeEval problem, but there was something in it that bothered me. So here I provide an alternative solution.

The soft spot in my previous solution was the combined sorting of the buffer counter dictionary added to a call to index() on the data list. I could live with the sorting, but I felt as unbearable the re-scan the data list to get the actual index. The Counter object knew it on its initialization, why don't use it at that time?

The refactoring ends up with code that is less readable than the first version and the performance gain, in this specific problem is minimal. It would make sense if the problem would require to accept in input long sequences of numbers. Anyway, here it is.
NOT_FOUND = 0  # 1
FOUND_MANY = -1

def solution(line):
    data = [int(x) for x in line.split()]  # 2
    result = [0] * 9  # 3
    for i, number in enumerate(data):  # 4
        result[number-1] = i+1 if result[number-1] == NOT_FOUND else FOUND_MANY  # 5

    for index in result:  # 6
        if index > NOT_FOUND:
            return index
    return 0
1. I am going to flag the items in the buffer with zero to mean that the relative number was not found, and with a minus one when multiple instance were found.
2. As in the original solution, I convert the input string in a list of integers.
3. Instead of using a Counter collection, here I have a list of integers, reporting for each number in [1..9] the first reference index in data.
4. First loop. I scan each element in data. I need both its position and value, so I use the enumerate builtin to get them.
5. The need for switch from 0-based to 1-based indices leads to this painful line. I have to decrease number, to make it a 0-based index in the buffer list named result, and I have to increase i so that it becomes an 1-based index, as required by the problem.
6. Second loop. I scan each index as stored in the result buffer, as soon as I find a valid value I return it as a solution. If there is no valid index in that list, zero is returned instead.

I pushed the changes on GitHub.

Go to the full post

CodeEval Lowest unique number

We have a string in input containing numbers is the range [1 .. 9]. We want to get the index (following the unsettling Pascal habit of giving one for the first element) of the lowest unique number available, if such a beast exists, otherwise zero. This is the CodeEval problem #103.

So, for instance, given these two sequences:
3 3 9 1 6 5 8 1 5 3
9 2 9 9 1 8 8 8 2 1 1
In the first case we should return 5, index of the unique 6 in the list, and in the second case we return 0, since there is no unique number.

Here is the core of my python solution.
data = [int(x) for x in line.split()]  # 1
counter = Counter(data)  # 2
for key, value in sorted(counter.items()):  # 3
    if  value == 1:  # 4
        return data.index(key) + 1
return 0  # 5
1. I convert the input string to a list of integer.
2. I use a collections Counter object to count the number in the list. Now counter is a dictionary where the key is any number passed in input and the associated value is its numerosity.
3. I sort the items in the counter, so that I start checking from the lowest value on, and I loop on all of them.
4. As soon as I find an item in the counter dictionary that has value 1 (meaning, its key is unique) I return the first element in data with such value. I have to add one to the position returned by the index() method because I have to convert the C-style index to a Pascal one, as required by the problem.
5. I fall off the for loop without finding my egg.

Full python script on GitHub.

Go to the full post

CodeEval Pass Triangle

We are given as input a sequence of integers that we should think representing a triangle. We should return the maximum value we can get adding all values from the top vertex of the triangle down to the bottom, chosing a path moving always downwards between adjacent elements. This is CodeEval problem #89.

For example, if this is the triangle we get as input:
5
  9 6
 4 6 8
0 7 1 5
The expected solution is 5 + 9 + 6 + 7 = 27

I started thinking in the Dynamic Programming direction. Seeing that for a minimal triangle the result comes out comparing the two lower elements and then adding the bigger one to the higher one, I simply applied this rule to all the elements, starting from the bottom line up to the second from top.

Here is my python code:
def solution(data):  # 1
    for i in range(len(data) - 1, 0, -1):  # 2
        for j in range(len(data[i])-1):  # 3
            data[i-1][j] += max(data[i][j], data[i][j+1])  # 4
    return data[0][0]  # 5
1. The solution function is called passing as input parameter a list of list of integers. The first list contains just a value, the triangle top element. As usual, we can assume that the data we receive is as expected. No check is required.
2. The loop variable in this for-loop goes from the last line (3, in the example case) down to 1. This is the line from which I'm reading the values, I'm going to write in the line above.
3. Here the loop variable of the for-loop represents the index of the first element I'm comparing.
4. Calling max() I see which one is the selected value, and I add it to the element in the line above.
5. Finally I just have to return the top element, that now contains the result.

Notice that my code is destructive, since I use the input data as working area. This is usually considered not a problem, or even good, in this context (less code, cheaper and faster). Not so if the user of the function would like to do something else with its data!

The complete python script is on GitHub.

Go to the full post

CodeEval Query Board

We have a square matrix of fixed size, and we receive a bunch of commands in input to operate on it. Two of them are about setting values on it, the other two perform a query and return an integer result.
This problem is quite simple, more detail on CodeEval #87, still, I'm writing a few lines about it because it gave me a way to exercise with a couple of Python features.

Mapping command names with function names

We receive the description of what to do as a string like this "SetCol 32 20". The first element is the name of the command we want to perform on our matrix. It looks like it has been written thinking in a Pascal-based language, but I want to use Python, so I convert it in a standard compliant name using a dictionary:
ID_2_NAME = {'SetRow': 'set_row', 'SetCol': 'set_col', 'QueryRow': 'query_row', 'QueryCol': 'query_col'}
A couple of notation here. Firstly, as usually happen in these kind of problem, we don't have to care about error handling. In real life, we should expect bad data in input.
Secondly, I have decided to wrap matrix and related functionality in a class. Using free functions instead, I would have mapped the names with the functions, something like this:
SIZE = 256
board = # ...

def set_row(i, value):
    for j in range(SIZE):
        board[i][j] = value

# ...
ID_2_FUN = {'SetRow': set_row, # ...

# call set_row(15, 7)
command = 'SetRow'
ID_2_FUN[command](15, 7)
The use of a class led me to follow a different path. But this way was interesting, too.

Defining the Board class

The Board initializer just sets the size and the bidimensional list used as matrix:
def __init__(self, size=256):  # 1
    self.size = size
    self.board = [[0 for _ in range(size)] for _ in range(size)]  # 2
1. I decided not to fix the size, as I would have done in a more hasty implementation, but just to default it to the dimension, as required by CodeEval.
2. The matrix is built up on the fly with a double list comprehension. The for loop variables are not used, so I named them both with the idiomatic underscore. The external, right side, for-loop creates all the rows. The internal, left side, for-loop creates a list filled with zeroes and attach it to each row.

There is almost nothing to say about set_row() and set_col(), it's just a for-loop on the passed row or column to set each element to the specified value, see on GitHub if you want to compare your implementation with mine.

The two query functions are again logically equivalent, we just have to sum all the values for a specified row or column. However, in case of column, we have to perform an explicit for-loop, where for the row we could use the built-in sum() function:
def query_row(self, i):
    return sum(self.board[i])

Calling the Board methods

Let see again a typical command line, "SetCol 32 20". We have to split it, so that we get as first element the command name, and then the function parameters. Once I all these elements, we could get help from getattr() to call the right method with its parameters:
board = Board()
# ...

command, *params = line.split()  # 1
res = getattr(board, ID_2_NAME[command])(*[int(p) for p in params])  # 2
if res is not None:  # 3
    print(res)
1. We extract in the list params all the elements next to the first one. Beware, they are all strings here.
2. ID_2_NAME maps the command name, for instance SetCol, to the method name, set_col. Using getattr() we are actually calling the method on the passed object, same of board.set_col() here. On the right, I convert the parameters in params to integer and I extract them to single elements from the list, by the star operator.
3. The setters do not return anything. Or better, being Python functions, they return None. We are not interested in printing it. But we want to print what the query methods return.

The full Python script is on GitHub.

Go to the full post

CodeEval Balanced Smileys

This problem is part of the family of the ones asking to check if parentheses in a sentence are balanced. See for instance the simpler matching brackets one.

In this version, happy ':)' and sad ':(' smileys could be part of the the sentence, so that we have to think if a parentheses has to be considered in the balancing or skipped, being the representation of the mouth of the smiley.

I found it as CodeEval #84 problem, but it is a repost, originally coming from Facebook Hacker Cup 2013 Hackathon.

I came to a solution inferior to the one published by the Hacker Cup editors, so I just threw mine in the dustbin and I spent some time pondering on the other one, coming out with a slightly different variation. [By the way, I would suggest you to do the same. When you see a piece of code you don't fully understand, play around with it, adapt it to your style, make it yours.]

The base problem is pretty simple. We loop on each character in the string, any time we see an open bracket, we push it in a stack (or we emulate a push in a stack, using just a counter). When we see a close bracket, we pop from the stack the matching open bracket. In the end, the stack should be empty. If we try to pop when the stack is empty, the sentence is unbalanced.

Having to care to the smileys, we need a second (virtual) stack, where we push also the dubious open brackets, the one that could have a double interpretation.

Looking at the code should clarify the sense of this solution:
min_open = 0  # 1
max_open = 0  # 2
for i in range(len(msg)):  # 3
    if msg[i] == '(':  #4
        max_open += 1
        if i == 0 or msg[i-1] != ':':  # 5
            min_open += 1
    elif msg[i] == ')':  # 6
        if i == 0:
            return False
        min_open = min_open - 1 if min_open else 0
        if msg[i-1] != ':':  # 7
            if max_open == 0:
                return False
            max_open -= 1
return True if min_open == 0 else False  # 8
1. Emulate the first stack. It keep track of the "clean" open brackets only.
2. The second "dirty" stack, containing also the brackets that could be seen as mouth for a sad smiley.
3. Loop an all the character in the sentence.
4. Open bracket, push it to the "dirty" stack (i.e. increase the max_open counter), and ...
5. ... only if it couldn't be seen as the mouth of a smiley, push it to the "clean" stack (i.e. increase the min_open counter)
6. Close bracket, if we are at the beginning, the sentence is unbalanced. Otherwise, pop the matching open bracket from the "clean" stack. If it was already empty, the sentence could be unbalanced. Not sure, however, because it could be a happy smiley. So ...
7. If the bracket could be seen as the mouth of a happy smiley, the "dirty" stack empty means the sentence is unbalanced, otherwise, pop a bracket from it.
8. Completed the loop, the sentence is balanced only if the "clean" stack is empty.

The full python script is on GitHub.

Go to the full post

Calling a Stored Function from JDBC

The code we should write to call a stored procedure or function via JDBC is slightly different from the one we write to perform a SQL SELECT. A CallableStatement is used instead of a Statement plus a ResultSet to set the parameter, execute the call, and extract the result.

To check it, I have firstly created a stored function in my Oracle 12 instance, on the HR schema. It takes a SQL CHAR in input, and returns the same string with 'suffix' appended:
create or replace FUNCTION foo (val CHAR)
RETURN CHAR AS
BEGIN
    RETURN val || 'suffix';
END;
I ensured it works as I expected running this query:
SELECT foo('hello') FROM DUAL;
The I have written a couple of tests to see how to get the same result in Java. Not just one, because we can use either the JDBC escape and the PL/SQL block syntax to achieve the same result, being the first
{? = call foo(?)
and the latter
begin ? := foo(?); end;
The core of both tests is in these few lines:
cs = conn.prepareCall(call);  // 1
cs.registerOutParameter(1, Types.CHAR);  // 2
cs.setString(2, "aa");
cs.execute();
String result = cs.getString(1);  // 3
1. Assuming conn is a good java.sql connection to the HR user on my Oracle database, and call is either the JDBC escape or the PL/SQL block string showed above, the prepareCall() should return a good CallableStatement.
2. The callable statement has to know the type of the output parameter is a character string. Then we set the other parameter, with the string that we want to pass as input.
3. After executing the callable statement, we get the first (and only) result as a string.

The actual code, that you could find on GitHub, class GettingStartedTest methods callFoo(), testFunctionCallJdbcEscape(), and testFunctionCallPlSqlBlock(), is a bit more verbose because I have to provide all the standard boilerplate, initializing, testing, cleaning up.

Reference: Oracle Database JDBC Developer's Guide 12c Release 2 (12.2) 2.8 Stored Procedure Calls in JDBC Programs

Go to the full post

Selecting on Oracle via JDBC

Once we decided how to establish a connection to the database, performing a SELECT on it is pretty simple. Here I'm going to use OracleDataSource as connection provider, using pure JDBC doesn't add any complexity.

The only problem in this Java code is that is slightly boring. So boring that there are a few wrapper aiming at keeping the advantages of JDBC easing its usage. See for instance the Spring JdbcTemplate.

Anyway, firstly we open a connection, then a statement, on which we execute a query (a select, in this case) that return a resultset. We normally do something on the resultset, and then we can close what we have opened, in reversed order, resultset, statement, connection. The code is made even more boring by the need of try-catching the SQLExceptions that could be thrown. Last straw in this boredom parade, is that before closing the JDBC objects we have to ensure they have been created, checking them for not being null.

So, our typical plain JDBC code could be seen in three steps:

Initialization

// ...
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
    conn = ods.getConnection();  // 1
    stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT first_name FROM employees");  // 2

    // ...
1. I'm using the OracleDataSourceConnector I described in the previous post. Nothing fancy, anyway, I simply get a connection to a Oracle database for the well known hr example user.
2. Having extracted a statement from the connection, I execute a query, that is plain SQL SELECT. The resulting table, here consisting of just one column, is returned in a ResultSet.

Operation

Let's do something with the fetched data. Here I copy the first names to a list of string, then I sort them and I ensure the first and last are what I expect. Usually this part of the job has more sense.
List<String> results = new ArrayList<String>();
while (rs.next()) {
    results.add(rs.getString(1));
}

assertEquals(107, results.size());
Collections.sort(results);
assertEquals("Adam", results.get(0));
assertEquals("Winston", results.get(106));
The employees table on HR should have 107 rows, first names should range from Adam to Winston, however, your result may vary.

Cleanup

Finally (actually, the code is normally performed in the finally block following the try one where the initialization and operation happened - meaning that we would always perform this step, whatever happens there), being done with the data, we can close result set, statement, and connection.
assertNotNull(rs);
rs.close();

assertNotNull(stmt);
stmt.close();

assertNotNull(conn);
conn.close();
Notice that we have to strictly follow the inverted order seen in initialization.
Notice also that each close() method could throw and exception so, again, these calls have to be in a try-catch block.

See full code on GitHub, class GettingStartedTest, method testSelect().

Reference: Oracle Database JDBC Developer's Guide 12c Release 2 (12.2) 2.4 Sample: Connecting, Querying, and Processing the Results

Go to the full post

Connecting to Oracle via JDBC

There are a couple of ways to connect to a recent Oracle Database, going through the standard DriverManager class or the specific OracleDataSource one. Let's see both of them. And, when we are there, let's add also some consideration on connecting to MySql via DriverManager.

It's a bit of an overkill, but I have written a little abstract class that is going to be the root of a hierarchy of classes for providing access to database through JDBC:
public abstract class Connector {
    public abstract Connection getConnection() throws SQLException;
 
    public String getDatabaseVersion(Connection conn) throws SQLException {
        return conn.getMetaData().getDatabaseProductVersion();
    }
}
The point of it is that each different concrete class has its own way to get a database connection but all of them would use it in the same way. So the getConnection() method is abstract where the actual method performing JDBC operation, like getDatabaseVersion(), would act in the same way.

Using OracleDataSource

The OracleDataSourceConnector extends Connector, and its raison d'être is keeping an instance of a OracleDataSource object as its private data member. Initialized in the constructor, is used by the the getConnection() method to return a database connection.
public class OracleDataSourceConnector extends Connector {
    private OracleDataSource ods;

    public OracleDataSourceConnector(String url, String user, String password) throws SQLException {
        ods = new OracleDataSource();
        ods.setURL(url);
        ods.setUser(user);
        ods.setPassword(password);
    }

    @Override
    public Connection getConnection() throws SQLException {
        return ods.getConnection();
    }
}
The good thing about OracleDataSource is that it has its own pool of connections that is managed implicitly. On the flip side, it is not standard JDBC. That means extra work if we want to adapt our code to use a different database.

I have written a tester to check the functionality, OracleDataSourceConnectorTest. See it on GitHub for full reference. There are only a few things that I want to stress here.
public class OracleDataSourceConnectorTest {
    private static final String URL = "jdbc:oracle:thin:@localhost:1521/orclpdb";  // 1
    private static final String USER = "hr";
    private static final String PASSWORD = "hr";

    private static OracleDataSourceConnector ods;
    private static Connection conn;

    @BeforeClass
    public static void setUp() {  // 2
        try {
            ods = new OracleDataSourceConnector(URL, USER, PASSWORD);
            conn = ods.getConnection();
        } catch (SQLException e) {
            fail(e.getMessage());
        }
    }

    // ...
}
1. I'm using the thin Oracle JDBC driver, the other choice is the OCI one. See the Oracle documentation if you wonder which one to use. Short answer is, usually thin is the one you want to peek. My database is local, on the standard port, and the service is named orclpdb. Your setup may vary.
2. I setup the connector and a connection through this static method called only once before the tests in the class are called. The sense is that I don't want to repeat expensive operations without a reason. So, when nothing is against it, I would reuse connector and connection in more tests.

And here is a test that requires its own connector, because I want to perform a disruptive negative test:
@Test
public void testBadUser() {
 OracleDataSourceConnector connector = null;
 try {
  connector = new OracleDataSourceConnector(URL, "Unknown", PASSWORD);  // 1
 } catch (SQLException e) {
  fail(e.getMessage());
 }
 
 try {
  connector.getConnection();  // 2
  fail("No connection expected for unknown user");
 } catch (SQLException e) {
  String expectedState = "72000";
  assertEquals(expectedState, e.getSQLState());
  
  int expectedCode = 1017;
  assertEquals(expectedCode, e.getErrorCode());
 }
}
1. I pass a bas user name to the connector. No failure is expected here, since no connection is actually done.
2. I expect the failure to happen here. Oracle should react with a ORA-01017 error code, that is included in the SQL state 72000, SQL execute phase errors.

The other test ensures that I can actually get to the database:
@Test
public void testGetDatabaseNameVersion() {
 try {
  String expected = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production";
  String actual = ods.getDatabaseVersion(conn);
  assertEquals(expected, actual);
 } catch (SQLException e) {
  fail(e.getMessage());
 }
}
Using DriverManager

If I am not interested in the connection pooling service offered by OracleDataSource, and I prefer to keep as generic as I can, I could use these other solution. In the old days, it required to perform an explicit registration of the JDBC driver:
Class.forName(klass)
Where klass is the actual class name, like "oracle.jdbc.driver.OracleDriver" or "com.mysql.jdbc.Driver".

Now this is done implicitly by the DriverManager, that leads to a very slim connector:
public class PlainConnector extends Connector {
 private String url;
 private String user;
 private String password;

 public PlainConnector(String url, String user, String password) {
  this.url = url;
  this.user = user;
  this.password = password;
 }

 @Override
 public Connection getConnection() throws SQLException {
  return DriverManager.getConnection(url, user, password);
 }
}
The constructor just store a copy of the data for connecting to the database, the getConnection() uses them going through DriverManager.

I have written another test case to see that actually the plain connector works as the OracleDataSource one. An then another one to see what I have to change to access with the same class a different database, here MySql.

This test case is accessing a MySql database that I have installed locally and on which I have added a user named "hr", to keep it close to the Oracle one. You can see how minimal are the required changes.

I had to change the URL:
private static final String URL = "jdbc:mysql://localhost:3306/hr?useSSL=false";
Notice the parameter useSLL set to false, to remove the MySql warning "Establishing SSL connection without server's identity verification is not recommended." In this test we can live without id verification.

In case of bad user, I expect now 28000 as SQL state and 1045, access denied, as error code.

And, the database version now should be something like "5.7.19-log"

Ah, right. Remember to put in your application build path the jar for oracle JDBC (currently named ojdbc8.jar) and for MySql (mysql-connector-java-5.1.43-bin.jar) and to have the databases up and running, if you want your tests to succeed.

Reference: Oracle Database JDBC Developer's Guide 12c Release 2

Full Java code on GitHub.

Go to the full post

Installing and configuring Oracle 12c

It was a while since the last time I have installed an instance of the Oracle database for local use. Nothing much has changed in the process, with the noticeable exception of the introduction of pluggable databases. Here I jot down a few remarks I made during the setup of the version 12 release 2.

1. In the page Typical Installation we have to specify the name for our pluggable database. Remember your choice!
2. The Enterprise Manager Database Express is available on https://localhost:5500/em, access it through the SYS user (with the password specified during setup)
3. Add in the tnsnames.ora (in the network\admin directory under the database home) the network configuration settings to access the pluggable database we have created in (1). Something like:
PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdborcl)
    )
  )
4. Use sqlplus to connect to the pluggable database with SYS as sysdba:
sqlplus sys/Oracle_1@pdborcl as sysdba;
5. As usual, unlock the hr user to access the test tables:
alter user hr identified by hr account unlock;
So far so good. All standard stuff. A problem raises if you plan to stop the Oracle service, that should be named OracleServiceORCL. Since it is always memory hungry, sometimes I just want it stay out of my RAM. The first time I did it, I had the surprise of getting a nasty error when I tried to connect to the database after restarting. Basically, the issue was that the pluggable database was mounted but not started.

This is the recipe I followed to solve it:

1. From the shell, enter sqlplus without logging.
sqlplus /nolog
2. In sqlplus, connect as sysdba.
connect / as sysdba
3. Brutally shutdown the database.
shutdown abort
4. Restart it.
startup mount
5. Recover the database, then open it.
recover database 
alter database open;
6. Open all the pluggable database, and then ask them to remember their status, so that we don't have to repeat the maneuver each time.
alter pluggable database all open;
alter pluggable database pdb_name save state;

Go to the full post

CodeEval Minimum Path Sum

Given a squared matrix of integers, find the minimal path sum from top-left to bottom-right, assuming that we can only move down or right.
This is CodeEval problem #72.

For example, give this matrix
4 6
2 8
We have just two possible paths: 4 -> 6 -> 8 and 4 -> 2 -> 8. It is immediate to see that the second one leads to the minimum sum of 14.

The structure of the problem recalls a Dynamic Programming solution table. I found it quite straightforward to use this thought to write a first solution:
def solution(data):  # 1
    dp = [[float(math.inf)] * (len(data) + 1)]  # 2
    for i in range(len(data)):
        dp.append([math.inf])
        dp[i+1].extend(data[i])

    end = len(dp)
    for i in range(1, end):  # 3
        for j in range(1, end):
            if i == 1 and j == 1:  # 4
                continue
            dp[i][j] += min(dp[i-1][j], dp[i][j-1])  # 5
    return dp[i][j]  # 6
1. The input parameter data contains a list of list of integers, representing the input matrix.
2. I copy the input in a DP-style table, where the first row and column have fake values. Usually, in a DP problem, zero is used for those cells. However here we need a value that should not be a candidate as minimum value. In a C-family language, I would have chosen a constant representing the maximum available integer. Python 3 has nothing like that, so I use math.inf, the floating-point positive infinity representation.
3. Calculate all the possible minimal path sum, skipping the first row and column.
4. This check looks unsatisfactory to me, but it is the less confusing way I found out to say that I don't have to do anything on the left-topmost element of the original matrix.
5. All the other cells are adjusted adding the smallest element on the immediate left or up.
6. There is nothing else to do, but returning the value in the bottom-right cell.

I found this solution to be elegant enough and easy to be understood. However I decided to write an alternative one to get rid of the buffer table, and also to navigate the table backward, from bottom-right to top-left.
def solution_2(data):
    last = len(data) - 1
    for i in range(last, -1, -1):
        for j in range(last, -1, -1):  # 1
            if i == last and j == last:  # 2
                continue
            if i == last:  # 3
                data[i][j] += data[i][j+1]
            elif j == last:
                data[i][j] += data[i+1][j]
            else:
                data[i][j] += min(data[i + 1][j], data[i][j+1])
    return data[0][0]  # 4
1. The loop variables are initially set to the last row/column and are decreased down to zero.
2. Again, nothing has to be done for the first cell.
3. Not having the extra-row/column, I have to add an extra check in the code for both i and j.
4. In this case the solution is stored in the top-left cell.

Not using another table should lead to a performance improvement, at least if large matrices are expected in input, and its cost in terms of less readability looks bearable to me. Scanning the table backward doesn't seem to give any advantage and makes the code a bit more obscure, so I would probably get rid of it.

I pushed the full python code on GitHub.

Go to the full post

Using JdbcTemplate in Spitter Web App

In the previous post I developed a CommandLineRunner Spring App to show how to use JdbcTemplate for simplify the JDBC access to an embedded instance of a H2 database. Here I port the changes to the Spittr Web App I have built up previously.

Actually, there is not much to say. There is some inconsistency between the two apps that spiced up the process, but nothing really complicated. I'd say the most interesting part is in the POM file. Here I removed, whenever it was possible, the versioning for each single dependency, relying instead on the parent element, where they are defined.
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.4.RELEASE</version>
    <relativePath />
</parent>
However, for some reason, Spring could not find the version for the JSP dependency, so I kept it as before
<dependency>
    <groupId>javax.servlet.jsp</groupId>
    <artifactId>javax.servlet.jsp-api</artifactId>
    <version>${jsp.version}</version>
    </dependency>
And left the versioning for it among the properties.
<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
    <java.version>1.8</java.version>
    <jsp.version>2.3.1</jsp.version>
</properties>
I pushed the new version of Spittr on GitHub.

Go to the full post

Spring and JdbcTemplate

Let's say that we want to write a Spring application that requires some simple database activity. We decide to use an embedded database, H2 and, instead of choosing a higher level persistency framework, we decide to go for the JDBC support, mediating it with JdbcTemplate, a Spring class aimed at simplifying its use.

I create a Spring project using the STS wizard (File >> New >> Spring Starter Project).
Since I want to use JDBC and the H2 database support, I select these two options from the dependencies page:
From database schema to POJOs

In the source/main/resources folder, I put a file named schema.sql, this is enough for Spring, that knows it has to get it and run it at startup, so there I put the SQL commands to create my tables. I create two tables, spitter and spittle, connected by the id of the first, that is a foreign key in the second.

The tables defined in the database are reflected in a couple of plain old Java classes, Spitter.java and Spittle.java, having as fields what in the tables are the columns.

There is one thing that I think is interesting. The SQL spittle table has, among the other fields, a datetime, postedTime. We need to find a counterpart for it in the Java world. Since we are not interested here in time zones and the such, just in a plain timestamp, I decided to use Java 8 java.time.Instant.

That's it. If the SQL stuff is simple, and I have just a couple of tables, I could give it for done. To see that, I have created a basic version for the SpringBootApplication that implements the CommandLineRunner interface:
public class UsingJdbcApplication implements CommandLineRunner {
    private static final Logger log = LoggerFactory.getLogger(UsingJdbcApplication.class);

    @Autowired
    private JdbcTemplate template;  // 1

    public static void main(String[] args) {
        SpringApplication.run(UsingJdbcApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        Long counter = template.queryForObject("SELECT count(id) FROM spitter", Long.class);  // 2
        log.info("Rows on spitter table: " + counter);

        log.info("Pushing a spitter in");
        Object[] admin = new Object[] { "admin", "password", "Admin", "admin@example.dd" };
        template.update("INSERT INTO spitter(username, password, fullname, email) VALUES(?, ?, ?, ?)", admin);  // 3

        template.query("SELECT * FROM spitter WHERE username = ?", new Object[] { "admin" },  // 4
                (rs, rowNum) -> new Spitter(rs.getLong("id"), rs.getString("username"), rs.getString("password"),
                        rs.getString("fullname"), rs.getString("email"), rs.getBoolean("updateByEmail")))
                .forEach(spitter -> log.info("Spitter: " + spitter.toString()));
    }
}
1. Sort of magic. We ask to Spring to wire this JdbcTemplate object. Since the H2 jar is in the classpath, it understands it has to use it as database. We don't provide any settings, and it uses a whole bunch of defaults. Usually not a good idea for production code, but very handy for prototyping.
2. Let's run a simple query that returns a single value. This line shows that actually Spring has initialized the database using the script named schema.sql that it found in the main resources folder. And how JdbcTemplate makes our life so much simpler. Notice that the queryForObject() method has a first parameter that is the SQL statement we want to run, and the second one that is the type for the expected return value.
3. A parametrized update(). Its first parameter is a parametrized JDBC SQL insert statement, the second one a vector of objects containing the values that we want to usein the query.
4. Something a bit more complicated. The query() method execute a query in a way similar to the update() seen above but, as third parameter, it accepts a lambda function that maps each row in the resulting resultset to a new Spitter object. We tipically have no use for the rowNum parameter, just access any resultset field we need.
This query() call returns a list of Spitter. On that list we apply a forEach() so that each element in the list is logged. Actually, here we are expecting just one Spitter, however this is a common pattern that we should learn to recognize and use.

Running the Spring Boot App, I can see in the console window the expected log.

Repositories and configuration

Usually we don't want to scatter SQL statements all over our Java code. So we create two interfaces, one for Spittle and one for Spitter, that act as repository for them, and then we implement them for our JDBC provider.
The two classes are quite similar. Let's see some parts of JdbcSpittleRepository, that is a tad more complicated.
// ...
private static final String SELECT = "select sp.id, s.id as spitterId, s.username, s.password, s.fullname, s.email, s.updateByEmail, sp.message, sp.postedTime from Spittle sp, Spitter s where sp.spitter = s.id";
private static final String SELECT_RECENT = SELECT + " order by sp.postedTime desc limit ?";

public List<Spittle> findRecent(int count) {
    return template.query(SELECT_RECENT, new SpittleRowMapper(), count);  // 1
}

// ...
private long insertSpittle(Spittle spittle) {
    SimpleJdbcInsert insert = new SimpleJdbcInsert(template).withTableName("Spittle");  // 2
    insert.setGeneratedKeyName("id");
    Map<String, Object> args = new HashMap<String, Object>();
    args.put("spitter", spittle.getSpitter().getId());
    args.put("message", spittle.getMessage());
    args.put("postedTime", Timestamp.from(spittle.getPostedTime()));
    long spittleId = insert.executeAndReturnKey(args).longValue();
    return spittleId;
}

// ...
private static final class SpittleRowMapper implements RowMapper<Spittle> {  // 3
    public Spittle mapRow(ResultSet rs, int rowNum) throws SQLException {
        long id = rs.getLong("id");
        String message = rs.getString("message");
        Instant postedTime = rs.getTimestamp("postedTime").toInstant();
        long spitterId = rs.getLong("spitterId");
        String username = rs.getString("username");
        String password = rs.getString("password");
        String fullName = rs.getString("fullname");
        String email = rs.getString("email");
        boolean updateByEmail = rs.getBoolean("updateByEmail");
        Spitter spitter = new Spitter(spitterId, username, password, fullName, email, updateByEmail);
        return new Spittle(id, spitter, message, postedTime);
    }
}
1. Almost an innocent JdbcTemplate wrapper to a JDBC query, if we don't pay much attention to the second parameter, that would convert the ResultSet coming from the SQL select in a list of Spittles. See the SpittleRowMapper below for details.
2. Here we see how SimpleJdbcInsert helps us in writing readable code when we want to insert a row in a table, in the case where the database will take the burden of generating a key for us, and we need to return it to the caller. After specifying the table we are working on, we set the name of the field that is a generated key, and then, as a map, the field names and associated values. Finally we have just to pass them to the SimpleJdbcInsert by executeAndReturnKey().
3. The SpittleRowMapper seen in action in (1). Its mapRow() method maps a single row from the underneath table in a Spittle object.

In some way, Spring has to configure these repositories, passing to them a viable JdbcTemplate object. In this app, it is done by JdbcConfig, annotated as a Spring Configuration class. It generates a few beans (in the Spring sense of the term) representing the DataSource, JdbcTemplate, the repositories, and also a transaction manager. We'll see them at work in the tester. It interesting seeing how the DataSource is set, when we don't rely on the default initialization by Spring.
@Configuration
public class JdbcConfig {
    @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScripts("schema.sql", "test-data.sql")
                .build();
    }
 
    // ...
This data source represents an embedded database, H2 type, that would run a couple of scripts at startup. We already seen the first one, schema.sql, here I added a second one, that contains a bunch of SQL insert for spitters and spittles rows.

Testing

There are a couple of JUnit test, one for Spitter the other one for Spittle repository. They are quite similar. Let's have a fast look at first one.
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = JdbcConfig.class)  // 1
public class JdbcSpitterRepositoryTest {
    @Autowired
    JdbcSpitterRepository spitterRepository;  // 2

    // ...
    @Test
    @Transactional  // 3
    public void findAll() {
        List<Spitter> spitters = spitterRepository.findAll();  // 4
        assertEquals(4, spitters.size());
        assertSpitter(0, spitters.get(0));
        assertSpitter(1, spitters.get(1));
        assertSpitter(2, spitters.get(2));
        assertSpitter(3, spitters.get(3));
    }

    //...
}
1. Here I'm saying to Spring to fetch the JdbcConfig class and use it to configure itself for testing.
2. Thanks to (1) we can ask to Spring to autowire the spitter repository.
3. Having defined a PlatformTransactionManager, we can use it to keep very simple the transaction management. A transaction is started and completed, eventually rolled-back, behind the curtains.
4. The spitters come from the test-data.sql called at startup by JdbcConfig dataSource().

Reference: Hitting the database with Spring and JDBC, from Spring in Action, Fourth Edition by Craig Walls, chapter ten.

This complete Spring application is on GitHub.

Go to the full post