Connecting Java application and PostgreSQL with JDBC without using eclipse (execute at command prompt)

Introduction

As a programming education instructor, I teach beginners to understand the basics of programming languages without dare to use an 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 Java applications 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 JDBC jar file for PostgreSQL

Start "Stack Builder" which is an attached application when installing PostgreSQL.

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

Open the "edb_pgjdbc.exe" downloaded above and add the jar file. Please install with the default save destination ("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 (eclipse, etc.), and I rarely implement it with a simple editor such as Sakura Editor. (I used to use it when I was studying at university ...) I often see how JDBC is used in eclipse, but I will review it.

When using JDBC with eclipse, after creating a new Java project Right-click on the project and follow the steps below to add the JDBC jar file.

(English) Select "Build Path"-> "Add External Archives ..."-> "postgresql-XX.X.XX.jar" (Japanese) "Build path"-> "Add external archive"

Once added, it will appear in the Package Explorer.

Source code you want to run

We have already created 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();
       }
   }
}

The main subject: 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 variable -Give the execution option "-cp" at runtime

Add "CLASSPATH" to the environment variable

First, you have the option of adding "CLASSPATH" to your environment variables. Add the following to "Control Panel"-> "System"-> "Advanced system settings"-> "Environment variables"-> "User environment variables". Variable name "CLASSPATH" Value "C: \ Program Files (x86) \ PostgreSQL \ pgJDBC \ postgresql-42.2.12.jar ;."

• Please note that there is a dot (.) At the end of the value. If you forget to insert the dot, the classpath to the current directory set by default may become invalid.
• Please read "postgresql-XX.X.XX.jar" as appropriate for the storage location and version.
• If "CLASSPATH" already exists, deleting the existing value may damage other environments, so please edit it by adding it. For example, if "C: \ XXX \ YYY.jar;" is set, "C: \ XXX \ YYY.jar; C: \ Program Files (x86) \ PostgreSQL \ pgJDBC \ postgresql-42.2.12." Add it so that it becomes "jar ;.". (The semicolon is a delimiter.)

Add execution option "-cp" at runtime

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

For example, to compile and execute "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

(The execution result is displayed)

• Please note that there is a dot (.) At the end of the value. If you forget to insert the dot, the classpath to the current directory set by default may become invalid.
• Please read "postgresql-XX.X.XX.jar" as appropriate for the storage location and version.

Summary

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

Thank you very much.

reference

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