Open-source News

Foolish Mortals is an upcoming spooky point and click adventure  GamingOnLinux

Foolish Mortals is an upcoming spooky point and click adventure  GamingOnLinux

Foolish Mortals is an upcoming spooky point and click adventure  GamingOnLinux

While 2023 is quickly approaching and the Modula-2 programming language has since been succeeded by the Modula-3 and Oberon languages since the late 80's, GNU Modula-2 continues to be worked on as a front-end to the GNU Compiler Collection for this structured, procedural programming language...

VirtualBox 7.0 is out with their DirectX 11 support using DXVK  GamingOnLinux

AMD IOMMU v2 Page Tables Getting Wired Up For Linux 6.1  Phoronix

For more than a year AMD engineers have been working on IOMMU v2 page table support and with the in-development Linux 6.1 kernel the initial patches are finally being merged...

Kolide gives you real-time fleet visibility across Mac, Windows, and Linux, answering questions MDMs can't  Graham Cluley

The latest daily development builds of the Blender 3D modelling software have enabled native support for Wayland. If all goes well, the Blender 3.4 release coming up will ship with this native Wayland support for Linux...

MGLRU has successfully landed in Linux 6.1 as one of the best kernel innovations of the year to be mainlined. Along with that the new Maple Tree data structure was also merged...

MGLRU Merged For Linux 6.1  Phoronix

At last week's XDC 2022 conference, Google engineer Rob Clark presented on VirtGPU DRM Native Contexts and the potential there for much better performance - especially for gaming - within virtual machines than using API-level virtualization like with Virgl...

After 30 Years, a Second Language Comes to Linux Kernel  Analytics India Magazine

Linux 6.1 Finishes Gutting Out The Old a.out Code  Phoronix

Back in 2019 the Linux kernel finally deprecated a.out support for that format superseded by ELF long ago. Since earlier this year kernel developers moved ahead with beginning to remove a.out support, including the old x86 a.out support. Now with Linux 6.1 some lingering remnants of a.out are being cleared away...

Groovy vs Java: Connecting a PostgreSQL database with JDBC Chris Hermansen Tue, 10/11/2022 - 03:00

Lately, I've been looking at how Groovy streamlines the slight clunkiness of Java. This article examines some differences between connecting to a PostgreSQL database using JDBC in Java versus Groovy.

Install Java and Groovy

Groovy is based on Java and requires a Java installation. Both a recent/decent version of Java and Groovy might be in your Linux distribution's repositories, or you can install Groovy by following these instructions. A nice alternative for Linux users is SDKMan, which provides multiple versions of Java, Groovy, and many other related tools. For this article, I'm using SDK's releases of:

• Java version 11.0.12-open of OpenJDK 11
• Groovy version 3.0.8

Back to the problem

If you haven't already, please review this article on installing JDBC and this article on setting up PostgreSQL.

Whether you're using Java or Groovy, several basic steps happen in any program that uses JDBC to pull data out of a database:

1. Establish a Connection instance to the database back end where the data resides.
2. Using an SQL string, get an instance of a Statement (or something similar, like a PreparedStatement) that will handle the execution of the SQL string.
3. Process the ResultSet instance returned by having the Statement instance execute the query, for example, printing the rows returned on the console.
4. Close the Statement and Connection instances when done.

In Java, the correspondence between the code and the list above is essentially one-for-one. Groovy, as usual, streamlines the process.

More on Java What is enterprise Java programming? An open source alternative to Oracle JDK Java cheat sheet Red Hat build of OpenJDK Free online course: Developing cloud-native applications with microservices Fresh Java articles Java example

Here's the Java code to look at the land cover data I loaded in the second article linked above:

1  import java.sql.Connection;
2  import java.sql.Statement;
3  import java.sql.ResultSet;
4  import java.sql.DriverManager;
5  import java.sql.SQLException;
       
6  public class TestQuery {
       
7      public static void main(String[] args) {
       
8          final String url = "jdbc:postgresql://localhost/landcover";
9          final String user = "clh";
10          final String password = "carl-man";
       
11          try (Connection connection = DriverManager.getConnection(url, user, password)) {
       
12              try (Statement statement = connection.createStatement()) {
       
13                  ResultSet res = statement.executeQuery("select distinct country_code from land_cover");
14                  while (res.next()) {
15                      System.out.println("country code " + res.getString("country_code"));
16                  }
       
17              } catch (SQLException se) {
18                  System.err.println(se.getMessage());
19              }
20          } catch (SQLException ce) {
21              System.err.println(ce.getMessage());
22          }
23      }
24  }

Lines 1-5 are the necessary import statements for the JDBC classes. Of course, I could shorten this to import java.sql.* but that sort of thing is somewhat frowned-upon these days.

Lines 6-24 define the public class TestQuery I will use to connect to the database and print some of the contents of the main table.

Lines 7-23 define the main method that does the work.

Lines 8-10 define the three strings needed to connect to a database: The URL, the user name, and the user password.

Lines 11-22 use a try-with-resources to open the Connection instance and automatically close it when done.

Lines 12 -19 use another try-with-resources to open the Statement instance and automatically close it when done.

Line 13 creates the ResultSet instance handle the SQL query, which uses SELECT DISTINCT to get all unique values of country_code from the land_cover table in the database.

Lines 14-16 process the result set returned by the query, printing out the country codes one per line.

Lines 17-19 and 20-22 handle any SQL exceptions.

Groovy example

I'll do something similar in Groovy:

1  import groovy.sql.Sql
       
2  final String url = "jdbc:postgresql://localhost/landcover"
3  final String user = "me"
4  final String password = "my-password"
5  final String driver = "org.postgresql.Driver"
       
6  Sql.withInstance(url, user, password, driver) { sql ->
       
7      sql.eachRow('select distinct country_code from land_cover') { row ->
8        println "row.country_code ${row.country_code}"
9      }
10  }

Okay, that's a lot shorter–10 lines instead of 24! Here are the details:

Line 1 is the only import needed. In my view, not having to jump around JavaDocs for three different classes is a distinct advantage.

Lines 2-5 define the four strings needed to connect to a database using the Sql class. The first three are the same as for java.sql.Connection; the fourth names the driver I want.

Line 6 is doing a bunch of heavy lifting. The method call is Sql.withInstance(), similar to other uses of "with" in Groovy. The call:

• Creates an instance of Sql (connection, statement, etc.).
• Takes a closure as its final parameter, passing it the instance of Sql that it created.
• Closes the instance of Sql when the closure exits.

Line 7 calls the eachRow() method of the Sql instance, wrapping the creation and processing of the result set. The eachRow() method takes a closure as its final argument and passes each row to the closure as it processes the returned lines of data from the table.

Groovy can simplify your life

For those of you whose day job involves scripting and relational databases, I think it's pretty obvious from the above that Groovy can simplify your life. A few closing comments:

• I could have accomplished this similarly to the Java version; for example, instead of calling sql.eachRow(), I could have called sql.query(), which takes a closure as its last argument and passes a result set to that closure, at which point I would have probably used a while() as in the Java version (or maybe each()).
• I could also read the resulting rows into a list, all at once, using a call to sql.rows(), which can transform the data in a manner similar to using .collect() on a list.
• Remember that the SQL passed into the eachRow() (or query()) method can be arbitrarily complex, including table joins, grouping, ordering, and any other operations supported by the database in question.
• Note that SQL can also be parametrized when using an instance of PreparedStatement, which is a nice way to avoid SQL injection if any part of the SQL comes in from outside the coder's sphere of control.
• This is a good moment to point the diligent reader to the JavaDocs for groovy.sql.Sql.

Groovy resources

The Apache Groovy language site provides a good tutorial-level overview of working with databases, including other ways to connect, plus additional operations, including insertions, deletions, transactions, batching, pagination—the list goes on. This documentation is quite concise and easy to follow, at least partly because the facility it is documenting has itself been designed to be concise and easy to use!

This example demonstrates how Groovy streamlines the clunkiness of Java.

Image by:

Pixabay. CC0.

Java Databases What to read next This work is licensed under a Creative Commons Attribution-Share Alike 4.0 International License. Register or Login to post a comment.

New Ubuntu Linux Kernel Security Updates Fix 16 Vulnerabilities, Patch Now  9to5Linux

Best Linux Distros for Windows in 2022  BollyInside

How to Install Snap in Arch Linux  It's FOSS

VirtualBox 7.0 Released with DXVK and Secure Boot Support, Full Encryption, and More  9to5Linux