How to use Java to create a table in PostgreSQL

January 19, 2023

SUMMARY: This article shares a sample Java program that can be used to create a table in PostgreSQL.

1. Requirements

2. Sample 1

3. Sample 2

 

In this post we are going to look at a sample Java program that creates a table in PostgreSQL. 

Requirements

If you want to create an object or access data from a PostgreSQL database using Java code, you need to satisfy the following prerequisites:

 

1. Make sure you have Java and Java Compiler (javac) installed on your server. You can install Java and javac by using this command:

                 Yum install java

2. Download the latest version of PostgreSQL JDBC driver from the PostgreSQL website: https://jdbc.postgresql.org.

3. Set the correct CLASSPATH for the PostgreSQL JDBC driver and classpath files, for example:

                 export set CLASSPATH=/home/edb/Desktop/postgresql-42.2.8.jar:.

You can check the CLASSPATH setting by using the following command:

                 echo $CLASSPATH

Sample 1

This simple Java program can create a table with hard-coded “CREATE TABLE” command:

[root@localhost data]# cat CreateTable.java

import java.sql.*;



public class CreateTable

{

    public static void main(String args[])

    {

        Connection c = null;

        Statement stmt = null;

    String CreateSql = null;

    try

    {

            Class.forName("org.postgresql.Driver");

            c = DriverManager.getConnection(

                             "jdbc:postgresql://localhost:5433/procedure_demo",

                             "postgres", "adminedb");

         System.out.println("Database Connected ..");

         stmt = c.createStatement();    

        CreateSql = "Create Table Test(id int primary key, 

                                              name varchar, address text) ";

        stmt.executeUpdate(CreateSql);

          stmt.close();

         c.close();

    }

      catch (Exception e)

    {

        System.err.println( e.getClass().getName()+": "+ e.getMessage() );

        System.exit(0);

        }

  System.out.println("Table Created successfully");

    }

}

 

Compile and Execute,

[root@localhost data]# javac CreateTable.java

[root@localhost data]# java CreateTable

Database Connected ..

Table Created successfully

 

We can check the table by connecting to the database:

procedure_demo=# \d+ public.test

                                      Table "public.test"

 Column  |   Type    | Collation | Nullable | Default | Storage  | Stats target | Description

---------+-------------------+-----------+----------+---------+----------+--------------+-------------

 id  | integer       |       | not null |     | plain |          |

 name | character varying |       |      |     | extended |          |

 address | text          |       |      |     | extended |          |

Indexes:

"test_pkey" PRIMARY KEY, btree (id)

Access method: heap

 

If the table already exists, then it will error out as follows:

[root@localhost data]# java CreateTable

Database Connected ..

org.postgresql.util.PSQLException: ERROR: relation "test" already exists

[root@localhost data]#

 

Sample 2

In the above sample the CREATE TABLE command is hard coded. In this example, the CREATE TABLE command is dynamic and can vary according to the number of columns entered by the user.

[root@localhost data]# cat CreateTableUserInput.java

import java.util.Scanner;

import java.sql.*;



public class CreateTableUserInput

{

    public static void main(String args[])

    {

        Connection c = null;

        Statement stmt = null;

        boolean tableExistence;

        String tableName = null;

        PreparedStatement pstmt;

        String createTableQuery = null;

        Integer colNumber = null;



        Try

        {

            Scanner in = new Scanner(System.in);

            Class.forName("org.postgresql.Driver");

            c =DriverManager.getConnection(

                    "jdbc:postgresql://localhost:5433/procedure_demo",

                    "postgres", "adminedb");

         System.out.println("Database Connected..");

         stmt = c.createStatement();

  

            System.out.println("Enter TableName : ");

            tableName = in.nextLine();



    System.out.println("Enter Number Of Column : ");

            colNumber = Integer.parseInt(in.nextLine());



            createTableQuery = "CREATE TABLE  \"" + tableName + "\"  ( ";



            for(int i=1; i<=colNumber; i++)

            {

                System.out.println( "Enter Column" +i + " Name and Data Type :");

                String ColValue = in.nextLine();

                createTableQuery = createTableQuery +  ColValue + " ,";

            }

            createTableQuery = createTableQuery + " );";

            createTableQuery = createTableQuery.replace(" , ","");



            System.out.println("Create Table Query is :" + createTableQuery);

            stmt.executeUpdate(createTableQuery);



            stmt.close();

            c.close();

        }

        catch (Exception e)

    {

            System.err.println( e.getClass().getName()+": "+ e.getMessage() );

            System.exit(0);

        }

        System.out.println("Table Creation Done.");

    }

}

[root@localhost data]#

 

Compile and execute. 

In the above sample programme it will wait for the user input to provide a number of columns as well as their names and data types. After receiving user input it will create the table with the provided user inputs.

[root@localhost data]# javac CreateTableUserInput.java

[root@localhost data]# java CreateTableUserInput

Database Connected..

Enter TableName :

Test2

Enter Number Of Column :

2

Enter Column1 Name and Data Type :

id int

Enter Column2 Name and Data Type :

name varchar

Create Table Query is :CREATE TABLE  "Test2"  ( id int ,name varchar);

Table Creation Done.

[root@localhost data]#

 

Check the table in the database:

procedure_demo=# \d+ public."Test2"

                                     Table "public.Test2"

 Column |   Type    | Collation | Nullable | Default | Storage  | Stats target | Description

--------+-------------------+-----------+----------+---------+----------+--------------+-------------

 id | integer       |       |      |     | plain |          |

 name   | character varying |       |      |     | extended |          |

Access method: heap



procedure_demo=#

 

Reference Links

https://jdbc.postgresql.org/download/

 

Share this

Relevant Blogs

More Blogs

An Overview of PostgreSQL Indexes

h2 { text-align: left !important; } .summary{ background:#f3f7f9; padding:20px; } SUMMARY: This article describes indexes in PostgreSQL and how they can help retrieve data faster. It covers the types of indexes...
January 24, 2023