[Java] Connection of Java application and PostgreSQL with JDBC without using eclipse (execution with command prompt)

3 minute read

Introduction

As a programming education instructor, I teach beginners to understand the basics of programming languages without using the integrated development environment. Therefore, it was necessary to prepare an environment for running Java applications without using eclipse.

What you can do by reading this article

You will be able to run a Java application using the PostgreSQL API at the command prompt.

Execution environment

The execution environment uses the following.

  • OS
    • Windows10
  • Java version
    • OpenJDK 14.0.2
  • Database version
    • PostgreSQL 12

Add the JDBC jar file for PostgreSQL

Please start “Stack Builder” which is an attached application when you install PostgreSQL.

Launch screen of Stack Builder 4.2.0

Select “pgJDBC vXX.X.XX” from the application you want to install. Since this is an installer, the save directory can be anywhere. (If you get lost, save it to the download folder.)

Stack Builder 4.2.0 application selection screen to install

Open “edb_pgjdbc.exe” downloaded above and add the jar file. Please install with the default save location (“C:\Program Files (x86)\PostgreSQL\pgJDBC”).

Connect to PostgreSQL using JDBC

Recently, when I do Java programming, I have more opportunities to develop using an integrated development environment (such as eclipse) and rarely implement it with a simple editor such as Sakura Editor. (I used to use it when I was studying at university etc…) I often see how to use JDBC with eclipse, but I will review it.

When using JDBC with eclipse, after creating a new Java project, Right-click the project and add the JDBC jar file as follows.

eclipse New Java project creation screen

(English) “Build Path” → “Add External Archives…” → select “postgresql-XX.X.XX.jar” (Japanese) “Build path” → “Add external archive”

After adding build path

Once added, it will appear in the Package Explorer.

How to add a build path with eclipse

Source code you want to execute

We have already generated tables (menuitems) in the database (restaurantdb).

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

public class P5_1 {
   public static void main(String[] args) {

       String url = "jdbc:postgresql://127.0.0.1:5432/restaurantdb";
       String user = "postgres";
       String password = "postgres";
       String sql = "SELECT * FROM menuitems";

       try (Connection connection = DriverManager.getConnection(url, user, password);
           Statement statement = connection.createStatement();
           ResultSet resultSet = statement.executeQuery(sql)) {

           while(resultSet.next()) {
               String id = resultSet.getString(1);
               String name = resultSet.getString(2);
               int price = resultSet.getInt(3);
               String kindId = resultSet.getString(4);
               Boolean isDisabled = resultSet.getBoolean(5);

               System.out.println(id + "" + name + "" + price + "" + kindId + "" + isDisabled);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       }
   }
}

Main topic: I want to solve the problem that an error occurs when trying to connect to a database from a Java program without using eclipse

C:\java-programming\JDBCSample>javac P5_1.java

C:\java-programming\JDBCSample>java P5_1
java.sql.SQLException: No suitable driver found for jdbc:postgresql://127.0.0.1:5432/restaurantdb
       at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:702)
       at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
       at P5_1.main(P5_1.java:15)

There seem to be two ways to suppress the above error.

  • Add “CLASSPATH” to the environment variables
  • Add the execution option “-cp” at the time of execution

Add “CLASSPATH” to the environment variables

First, you have the option to add “CLASSPATH” to your environment variables. Add the following to “Control Panel” → “System” → “Detailed system settings” → “Environment variables” → “User environment variables”. Variable name “CLASSPATH” Value ``C:\Program Files (x86)\PostgreSQL\pgJDBC\postgresql-42.2.12.jar;.’’ *Note that there is a dot (.) at the end of the value. If you forget to insert the dot, the default classpath to the current directory may be invalid.

  • For “postgresql-XX.X.XX.jar”, please replace the storage location and version as appropriate.
  • If “CLASSPATH” already exists, deleting the existing value may break the other environment, so edit it as a postscript. For example, if you have set C:\XXX\YYY.jar;'', you will seeC:\XXX\YYY.jar;C:\Program Files (x86)\PostgreSQL\pgJDBC\postgresql-42.2.12. jar;.” will be added. (The semicolon is a delimiter.)

Add execution option “-cp” when executing

If for some reason you cannot change the environment variables, you can add options at runtime.

For example, to compile and run “P5_1.java”, enter the following command.

> javac P5_1.java

> java -cp "C:\Program Files (x86)\PostgreSQL\pgJDBC\postgresql-42.2.12.jar;." P5_1

(Execution result is displayed) *Note that there is a dot (.) at the end of the value. If you forget to insert the dot, the default classpath to the current directory may be invalid.

  • For “postgresql-XX.X.XX.jar”, please replace the storage location and version as appropriate.

Summary

This time, I called the JDBC API from a Java program without using eclipse. You may not use it very often, but you may try it to understand how it works.

Thank you very much.

Reference

  • JDBC interface|PostgreSQL https://www.postgresql.jp/document/7.4/html/jdbc.html