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/