Sign In
Contact Us

Lambdas and JDBC Part 1 of 8 – Introduction

Written by Roderick Barnes

Java Programming

April 11, 2020

JDBC is one of the most often-used APIs in Java. Whether we are programming for phones, tablets, laptops, or servers in the cloud, we often have to work with a database. JDBC is the Java-based technology used to facilitate Java programs interacting (INSERT, UPDATE, DELETE, SELECT) with RDBMS technologies. Having been refined over twenty years it is a fairly mature and robust part of the Java technology stack. But it could be better? When it comes to working with query results, it could be much better. How? Here are a few of the things that it would be cool to have in the JDBC API:

  • Named Parameters
  • Mappings from ResultSet to Collections
  • Object-Oriented Filtering
  • Object-Oriented Grouping

Anyone who has every written a data access object by hand knows what I mean. The object-oriented coolness of Java PIE (Polymorphism, Inheritance, and Encapsulation) are largely lost as we are forced to work with ResultSet objects. In this post we will look briefly at using lambda functions to pass code as data. In particular we will use a block of code that publishes the current time every second as behavior for a thread.

<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>time</span><span class="token punctuation">.</span><span class="token class-name">LocalDateTime</span><span class="token punctuation">;</span>

<span class="token comment">/**
 * Simple example of using a lambda expression to pass asynchronous
 * behavior requiring no parameters as data.
 * @author Roderick L. Barnes, Sr.
 */</span>
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">Example001</span> <span class="token punctuation">{</span>
	<span class="token comment">/**
	 * I dislike the messiness of putting a try catch block in the salient block
	 * of code for the mere purpose trapping the exception that could be thrown
	 * by {@code Thread.sleep}.
	 * @param intSleepDurationInMilliseconds duration of sleep in milliseconds.
	 */</span>
	<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">sleep</span><span class="token punctuation">(</span><span class="token keyword">int</span> intSleepDurationInMilliseconds<span class="token punctuation">)</span> <span class="token punctuation">{</span>
		<span class="token keyword">try</span> <span class="token punctuation">{</span>
			<span class="token class-name">Thread</span><span class="token punctuation">.</span><span class="token function">sleep</span><span class="token punctuation">(</span>intSleepDurationInMilliseconds<span class="token punctuation">)</span><span class="token punctuation">;</span>
		<span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">InterruptedException</span> e<span class="token punctuation">)</span> <span class="token punctuation">{</span>
			e<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
		<span class="token punctuation">}</span>
	<span class="token punctuation">}</span>
	
	<span class="token comment">/**
	 * A basic test harness for the runnable lambda expression.
	 * @param arrayOfString unused by the test harness
	 */</span>
	<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">main</span><span class="token punctuation">(</span><span class="token class-name">String</span><span class="token punctuation">[</span><span class="token punctuation">]</span> arrayOfString<span class="token punctuation">)</span> <span class="token punctuation">{</span>
		<span class="token comment">/**
		 * Step 1: Define a lambda expression that allows us to use behavior as data.
		 */</span>
		<span class="token class-name">Runnable</span> runnableLambdaExpression <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token operator">-></span> <span class="token punctuation">{</span>
				<span class="token keyword">for</span> <span class="token punctuation">(</span><span class="token keyword">int</span> intLoopIndex <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span> intLoopIndex <span class="token operator"><</span> <span class="token number">10</span><span class="token punctuation">;</span> intLoopIndex<span class="token operator">++</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
					<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">println</span><span class="token punctuation">(</span><span class="token class-name">LocalDateTime</span><span class="token punctuation">.</span><span class="token function">now</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
					
					<span class="token class-name">Example001</span><span class="token punctuation">.</span><span class="token function">sleep</span><span class="token punctuation">(</span><span class="token number">1000</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
				<span class="token punctuation">}</span>
			<span class="token punctuation">}</span><span class="token punctuation">;</span>
		
		<span class="token comment">/**
		 * Step 2: Create the thread using the lambda and start the thread.
		 */</span>
		<span class="token class-name">Thread</span> threadLambda <span class="token operator">=</span> <span class="token keyword">new</span> <span class="token class-name">Thread</span><span class="token punctuation">(</span>runnableLambdaExpression<span class="token punctuation">)</span><span class="token punctuation">;</span>
		threadLambda<span class="token punctuation">.</span><span class="token function">start</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
	<span class="token punctuation">}</span>
<span class="token punctuation">}</span>
Java

more text goes here.


<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>sql</span><span class="token punctuation">.</span><span class="token class-name">Connection</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>sql</span><span class="token punctuation">.</span><span class="token class-name">Date</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>sql</span><span class="token punctuation">.</span><span class="token class-name">DriverManager</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>sql</span><span class="token punctuation">.</span><span class="token class-name">PreparedStatement</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>sql</span><span class="token punctuation">.</span><span class="token class-name">ResultSet</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>sql</span><span class="token punctuation">.</span><span class="token class-name">SQLException</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>time</span><span class="token punctuation">.</span><span class="token class-name">Instant</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>time</span><span class="token punctuation">.</span><span class="token class-name">LocalDate</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>time</span><span class="token punctuation">.</span><span class="token class-name">LocalDateTime</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>time</span><span class="token punctuation">.</span><span class="token class-name">ZoneId</span><span class="token punctuation">;</span>
<span class="token keyword">import</span> <span class="token namespace">java<span class="token punctuation">.</span>util<span class="token punctuation">.</span>function</span><span class="token punctuation">.</span><span class="token class-name">Predicate</span><span class="token punctuation">;</span>
<span class="token comment">/**
 * Simple example of using a lambda expression to map one value to another. In this case
 * a date is mapped to a boolean value indicating whether or not it is a weekday.
 * @author Roderick L. Barnes, Sr.
 */</span>
<span class="token keyword">public</span> <span class="token keyword">class</span> <span class="token class-name">Example002</span> <span class="token punctuation">{</span>
	<span class="token comment">/**
	 * Constant for the JDBC driver used to connect to the database.
	 */</span>
	<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token keyword">final</span> <span class="token class-name">String</span> STRING_JDBC_DRIVER <span class="token operator">=</span> <span class="token string">"org.postgresql.Driver"</span><span class="token punctuation">;</span>
	
	<span class="token comment">/**
	 * Constant for the URL that will be used by the JDBC driver to connect to the database.
	 */</span>
	<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token keyword">final</span> <span class="token class-name">String</span> STRING_JDBC_DATABASE_URL <span class="token operator">=</span> <span class="token string">"jdbc:postgresql://bifproductsdb.ctrlzjj4vv8c.us-east-1.rds.amazonaws.com:5432/postgres"</span><span class="token punctuation">;</span>
	
	<span class="token comment">/**
	 * Constant for the user account that will be used to connect to the database.
	 */</span>
	<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token keyword">final</span> <span class="token class-name">String</span> STRING_JDBC_USERNAME <span class="token operator">=</span> <span class="token string">"denzel_washington"</span><span class="token punctuation">;</span>
	
	<span class="token comment">/**
	 * Constant for the password that will be used to connect to the database.
	 */</span>
	<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token keyword">final</span> <span class="token class-name">String</span> STRING_JDBC_PASSWORD <span class="token operator">=</span> <span class="token string">"book_of_eli"</span><span class="token punctuation">;</span>
	
	<span class="token comment">/**
	 * Returns the name of the class providing the JDBC driver functionality
	 * for connections to the database.
	 * @return the name of the JDBC driver class.
	 */</span>
	<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token class-name">String</span> <span class="token function">getJDBCDriver</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span> <span class="token class-name">Example002</span><span class="token punctuation">.</span>STRING_JDBC_DRIVER<span class="token punctuation">;</span>
	<span class="token punctuation">}</span>
	
	<span class="token comment">/**
	 * Returns the URL for the COVID-19 database.
	 * @return the URL for the COVID-19 database.
	 */</span>
	<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token class-name">String</span> <span class="token function">getJDBCURL</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span> <span class="token class-name">Example002</span><span class="token punctuation">.</span>STRING_JDBC_DATABASE_URL<span class="token punctuation">;</span>
	<span class="token punctuation">}</span>
	
	<span class="token comment">/**
	 * Returns the username for the account that will be used to login to the database.
	 * @return the username for the account that will be used to login to the database.
	 */</span>
	<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token class-name">String</span> <span class="token function">getJDBCUsername</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span> <span class="token class-name">Example002</span><span class="token punctuation">.</span>STRING_JDBC_USERNAME<span class="token punctuation">;</span>
	<span class="token punctuation">}</span>
	
	<span class="token comment">/**
	 * Returns the password for the account that will be used to login to the database.
	 * @return the password for the account that will be used to login to the database.
	 */</span>
	<span class="token keyword">private</span> <span class="token keyword">static</span> <span class="token class-name">String</span> <span class="token function">getJDBCPassword</span><span class="token punctuation">(</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
		<span class="token keyword">return</span> <span class="token class-name">Example002</span><span class="token punctuation">.</span>STRING_JDBC_PASSWORD<span class="token punctuation">;</span>
	<span class="token punctuation">}</span>
	
	<span class="token comment">/**
	 * Test harness for the lambda function example.
	 */</span>
	<span class="token keyword">public</span> <span class="token keyword">static</span> <span class="token keyword">void</span> <span class="token function">main</span><span class="token punctuation">(</span><span class="token class-name">String</span><span class="token punctuation">[</span><span class="token punctuation">]</span> arrayOfString<span class="token punctuation">)</span> <span class="token keyword">throws</span> <span class="token class-name">ClassNotFoundException</span> <span class="token punctuation">{</span>
		<span class="token comment">/**
		 * Step 1: Create a lambda function that returns a boolean value indicating whether
		 * or not the given date is a weekday. The Predicate lambda function is used.
		 */</span>
		<span class="token class-name">Predicate</span> isWeekday <span class="token operator">=</span> <span class="token punctuation">(</span><span class="token class-name">Date</span> dateNew<span class="token punctuation">)</span> <span class="token operator">-></span> <span class="token punctuation">{</span>
				<span class="token class-name">Instant</span> instant <span class="token operator">=</span> <span class="token class-name">Instant</span><span class="token punctuation">.</span><span class="token function">ofEpochMilli</span><span class="token punctuation">(</span>dateNew<span class="token punctuation">.</span><span class="token function">getTime</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
				<span class="token class-name">LocalDate</span> localDate <span class="token operator">=</span> <span class="token class-name">LocalDateTime</span><span class="token punctuation">.</span><span class="token function">ofInstant</span><span class="token punctuation">(</span>instant<span class="token punctuation">,</span> <span class="token class-name">ZoneId</span><span class="token punctuation">.</span><span class="token function">systemDefault</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">.</span><span class="token function">toLocalDate</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
			
				<span class="token keyword">boolean</span> booleanIsWeekDay <span class="token operator">=</span> <span class="token boolean">false</span><span class="token punctuation">;</span>
				
				<span class="token keyword">switch</span> <span class="token punctuation">(</span>localDate<span class="token punctuation">.</span><span class="token function">getDayOfWeek</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
					<span class="token keyword">case</span> MONDAY<span class="token operator">:</span> <span class="token keyword">case</span> TUESDAY<span class="token operator">:</span> <span class="token keyword">case</span> WEDNESDAY<span class="token operator">:</span> <span class="token keyword">case</span> THURSDAY<span class="token operator">:</span> <span class="token keyword">case</span> FRIDAY<span class="token operator">:</span>
						booleanIsWeekDay <span class="token operator">=</span> <span class="token boolean">true</span><span class="token punctuation">;</span>
						<span class="token keyword">break</span><span class="token punctuation">;</span>
					<span class="token keyword">case</span> SATURDAY<span class="token operator">:</span> <span class="token keyword">case</span> SUNDAY<span class="token operator">:</span>
						booleanIsWeekDay <span class="token operator">=</span> <span class="token boolean">false</span><span class="token punctuation">;</span>
						<span class="token keyword">break</span><span class="token punctuation">;</span>
				<span class="token punctuation">}</span>
				
				<span class="token keyword">return</span> booleanIsWeekDay<span class="token punctuation">;</span>
			<span class="token punctuation">}</span><span class="token punctuation">;</span>
			
		<span class="token comment">/**
		 * Step 2: Load the required PostgreSQL JDBC driver into the virtual machine.
		 */</span>
		<span class="token class-name">Class</span><span class="token punctuation">.</span><span class="token function">forName</span><span class="token punctuation">(</span><span class="token class-name">Example002</span><span class="token punctuation">.</span><span class="token function">getJDBCDriver</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
		
		<span class="token comment">/**
		 * Step 3: Create a {@code String} for the SQL query that will get the data.
		 */</span>
		<span class="token class-name">String</span> stringCOVID19Query <span class="token operator">=</span> 
				<span class="token string">"SELECT "</span>
				<span class="token operator">+</span> <span class="token string">"rr.record_date, "</span>
				<span class="token operator">+</span> <span class="token string">"SUM(rr.confirmed) AS confirmed, "</span>
				<span class="token operator">+</span> <span class="token string">"SUM(rr.recovered) AS recovered, "</span>
				<span class="token operator">+</span> <span class="token string">"SUM(rr.deaths) AS deaths "</span>
				<span class="token operator">+</span> <span class="token string">"FROM disease_tracking.regional_record rr "</span>
				<span class="token operator">+</span> <span class="token string">"GROUP BY rr.record_date "</span>
				<span class="token operator">+</span> <span class="token string">"ORDER BY rr.record_date ASC"</span><span class="token punctuation">;</span>
				
		<span class="token keyword">try</span> <span class="token punctuation">(</span>
				<span class="token comment">/**
				 * Step 3: Create the objects needed for the connection and query.
				 */</span>
				<span class="token class-name">Connection</span> connection <span class="token operator">=</span> <span class="token class-name">DriverManager</span><span class="token punctuation">.</span><span class="token function">getConnection</span><span class="token punctuation">(</span>
						<span class="token class-name">Example002</span><span class="token punctuation">.</span><span class="token function">getJDBCURL</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
						<span class="token class-name">Example002</span><span class="token punctuation">.</span><span class="token function">getJDBCUsername</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
						<span class="token class-name">Example002</span><span class="token punctuation">.</span><span class="token function">getJDBCPassword</span><span class="token punctuation">(</span><span class="token punctuation">)</span>
					<span class="token punctuation">)</span><span class="token punctuation">;</span>
				<span class="token class-name">PreparedStatement</span> preparedStatement <span class="token operator">=</span> connection<span class="token punctuation">.</span><span class="token function">prepareStatement</span><span class="token punctuation">(</span>stringCOVID19Query<span class="token punctuation">)</span><span class="token punctuation">;</span>
			<span class="token punctuation">)</span> <span class="token punctuation">{</span>
			
			<span class="token comment">/**
			 * Step 4: Get a {@code ResultSet} for the query using a try-with-resources
			 * block.
			 */</span>
			<span class="token keyword">try</span> <span class="token punctuation">(</span>
					<span class="token class-name">ResultSet</span> resultSet <span class="token operator">=</span> preparedStatement<span class="token punctuation">.</span><span class="token function">executeQuery</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
				<span class="token punctuation">)</span> <span class="token punctuation">{</span>
				
				<span class="token comment">/**
				 * Step 5: Use the traditional approach to printing the {@code ResultSet}.
				 */</span>
				<span class="token keyword">while</span> <span class="token punctuation">(</span>resultSet<span class="token punctuation">.</span><span class="token function">next</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">)</span> <span class="token punctuation">{</span>
					<span class="token comment">/**
					 * Step 5.1: Print the date the disease information was collected.
					 */</span>
					<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">printf</span><span class="token punctuation">(</span><span class="token string">"%1$tY-%1$tm-%1$td"</span><span class="token punctuation">,</span>resultSet<span class="token punctuation">.</span><span class="token function">getDate</span><span class="token punctuation">(</span><span class="token string">"record_date"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
					
					<span class="token comment">/**
					 * Step 5.2: Use a lambda function to indicate whether or not it was a
					 * weekday.
					 */</span>
					<span class="token class-name">System</span><span class="token punctuation">.</span>out<span class="token punctuation">.</span><span class="token function">printf</span><span class="token punctuation">(</span><span class="token string">"%7b%n"</span><span class="token punctuation">,</span> isWeekday<span class="token punctuation">.</span><span class="token function">test</span><span class="token punctuation">(</span>resultSet<span class="token punctuation">.</span><span class="token function">getDate</span><span class="token punctuation">(</span><span class="token string">"record_date"</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>					
				<span class="token punctuation">}</span>				
			<span class="token punctuation">}</span>			
		<span class="token punctuation">}</span> <span class="token keyword">catch</span> <span class="token punctuation">(</span><span class="token class-name">SQLException</span> sqlException<span class="token punctuation">)</span> <span class="token punctuation">{</span>
			sqlException<span class="token punctuation">.</span><span class="token function">printStackTrace</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
		<span class="token punctuation">}</span>
	<span class="token punctuation">}</span>
<span class="token punctuation">}</span>
Java

You May Also Like…

Mind Your Business

Mind Your Business

The business intelligence solutions provided in most applications are passive. Users must come to the software’s...