Java postgresql create table

PostgreSQL — JAVA Interface

Introduction To PostgreSQL Databases With PgAdmin For Beginners

23 Lectures 1.5 hours

Google BigQuery & PostgreSQL : Big Query for Data Analysis

126 Lectures 10.5 hours

PostgreSQL, PGadmin, SQL Tutorial + Mini Project!

35 Lectures 5 hours

Installation

Before we start using PostgreSQL in our Java programs, we need to make sure that we have PostgreSQL JDBC and Java set up on the machine. You can check Java tutorial for Java installation on your machine. Now let us check how to set up PostgreSQL JDBC driver.

Download the latest version of postgresql-(VERSION).jdbc.jar from postgresql-jdbc repository.

Add downloaded jar file postgresql-(VERSION).jdbc.jar in your class path, or you can use it along with -classpath option as explained below in the examples.

The following section assumes you have little knowledge about Java JDBC concepts. If you do not have, then it is suggested to spent half and hour with JDBC Tutorial to become comfortable with concepts explained below.

Connecting To Database

The following Java code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.

Before you compile and run above program, find pg_hba.conf file in your PostgreSQL installation directory and add the following line −

You can start/restart the postgres server in case it is not running using the following command −

Now, let us compile and run the above program to connect with testdb. Here, we are using postgres as user ID and 123 as password to access the database. You can change this as per your database configuration and setup. We are also assuming current version of JDBC driver postgresql-9.2-1002.jdbc3.jar is available in the current path.

Create a Table

The following Java program will be used to create a table in previously opened database. Make sure you do not have this table already in your target database.

When a program is compiled and executed, it will create the COMPANY table in testdb database and will display the following two lines −

INSERT Operation

The following Java program shows how we can create records in our COMPANY table created in above example −

When the above program is compiled and executed, it will create given records in COMPANY table and will display the following two lines −

SELECT Operation

The following Java program shows how we can fetch and display records from our COMPANY table created in above example −

When the program is compiled and executed, it will produce the following result −

UPDATE Operation

The following Java code shows how we can use the UPDATE statement to update any record and then fetch and display updated records from our COMPANY table −

When the program is compiled and executed, it will produce the following result −

DELETE Operation

The following Java code shows how we can use the DELETE statement to delete any record and then fetch and display remaining records from our COMPANY table −

When the program is compiled and executed, it will produce the following result −

Источник

How to use Java to create a table in PostgreSQL

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

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/download.html.

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:

Sample 1

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

Compile and Execute,

We can check the table by connecting to the database:

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

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.

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.

Источник

How to use Java to create a table in PostgreSQL

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

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/download.html.

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:

Sample 1

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

Compile and Execute,

We can check the table by connecting to the database:

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

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.

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.

Источник

PostgreSQL Java

last modified July 15, 2022

This is a Java tutorial for the PostgreSQL database. It covers the basics of PostgreSQL programming with Java.

JDBC is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases. From a technical point of view, the API is as a set of classes in the java.sql package. To use JDBC with a particular database, we need a JDBC driver for that database.

About PostgreSQL database

PostgreSQL is a powerful, open source object-relational database system. It is a multi-user database management system. It runs on multiple platforms including Linux, FreeBSD, Solaris, Microsoft Windows, and Mac OS X. PostgreSQL is developed by the PostgreSQL Global Development Group.

PostgreSQL driver

To include the PostgreSQL Java driver, we need to add the following Maven dependency:

Maven Exec plugin

In order to run command line applications from Maven, we can uset the following Maven plugin.

We can run the application with mvn -q exec:java command. In the mainClass tag, we write the fully qualified name of the main class, such as com.zetcode.JavaPostgreSqlVersion . The -q runs Maven in quite mode; it disables all Maven messages except error messages.

PostgreSQL setup

We are going to show how to install PostgreSQL database on a Debian Linux system.

This command installs PostgreSQL server and related packages.

We check the status of the database with postgresql status command.

After the installation, a postgres user with administration priviliges was created with empty default password. As the first step, we need to set a password for postgres .

We create a new database user.

We create a new testdb database with createdb command, which is going to be owned by user12 .

We edit the pg_hba.conf file.

In order to be able to run a Spring Boot application with a local PostgreSQL installation, we change the authentication method for the Unix domain socket and local connections to trust .

We restart PostgreSQL to enable the changes.

Now we can use the psql tool to connect to the database.

Removing PostgreSQL from startup scripts

If we install the PostgreSQL database from packages, it is automatically added to the start up scripts of the operating system. If we are only learning to work with the database, it is unnecessary to start the database each time we boot the system.

The above command removes any system startup links for the PostgreSQL database.

Java PostgreSQL version

If the following program runs OK, then we have everything installed OK. We check the version of the PostgreSQL server.

We connect to the database and get some info about the PostgreSQL server.

This is the connection URL for the PostgreSQL database. Each driver has a different syntax for the URL. In our case, we provide a host (localhost), a port (5432), and a database name (testdb).

A connection is established to the database with getConnection . The createStatement method of the connection object creates a Statement object for sending SQL statements to the database. The executeQuery executes the given SQL statement, which returns a single ResultSet object. The ResultSet is a table of data returned by a specific SQL statement.

The try-with-resources statement ensures that each resource is closed at the end of the statement.

A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row. If there are no rows left, the method returns false. The getString method retrieves the value of a specified column. The first column has index 1.

In case of an exception, we log the error message.

This is a sample output of the program.

Creating and populating tables

Next we are going to create database tables and fill them with data. These tables will be used throughout this tutorial.

We have a authors_books_postgresql.sql file. It creates four database tables, authors, books, testing, and images. We place a foreign key constraint on the author_id column of the books table. We fill the authors and books tables with initial data.

The psql is a terminal-based front-end to PostgreSQL. We can use it to perfom interactive queries, issue them to PostgreSQL, and see the query results. Inside the psql tool, we import and execute the authors_books_postgresql.sql file.

We check the created tables.

Java PostgreSQL prepared statements

Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance.

In Java a PreparedStatement is an object which represents a precompiled SQL statement.

We add a new author to the authors table.

Here we create a prepared statement. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements are faster and guard against SQL injection attacks. The ? is a placeholder, which is going to be filled later. In our case we will fill two values: an integer id and a string name.

An integer value is bound to the placeholder.

A string value is bound to the placeholder.

The prepared statement is executed. We use the executeUpdate method of the statement object when we do not expect any data to be returned. This is when we create databases or execute INSERT , UPDATE , and DELETE statements.

We have a new author inserted into the table.

Testing table. We will execute a normal statement and a prepared statement 1000 times. We check if there is some difference in execution time.

In order to run the project from the command line, we add the exec-maven-plugin to the POM file.

The first example uses Statement .

We build the query and execute it 1000 times.

We use the time command to measure the time which the program ran. Note that we use a standard Linux command, not the built-in bash time command. It took 32s to insert 1000 rows into the table using the Statement object.

Now we use the PreparedStatement to do the same task.

We create the prepared statement using the prepareStatement method.

We bind a value to the prepared statement, execute it in a loop thousand times.

This time it took 15s to insert 1000 rows. We have saved 17s using prepared statements.

Java PostgreSQL retrieving data

Next we will show, how to retrieve data from a database table. We get all data from the authors table.

We get all authors from the authors table and print them to the console.

We execute a query that selects all columns from the authors table. We use the executeQuery method. The method executes the given SQL statement, which returns a single ResultSet object. The ResultSet is the data table returned by the SQL query.

The next method advances the cursor to the next record. It returns false when there are no more rows in the result set. The getInt and getString methods retrieve the value of the designated column in the current row of this ResultSet object as an int and String in the Java programming language.

The Ids and names of authors are printed to the console.

Properties

It is a common practice to put the configuration data outside the program in a separate file. This way the programmers are more flexible. We can change the user, the password or the connection URL without needing to recompile the program.

In Java, the Properties is a class used often for this. The class is used for easy reading and saving of key/value properties.

We have a database.properties file, in which we have three key/value pairs. They are loaded during the execution of the program.

We connect to the testdb database and print the contents of the authors table to the console. This time, we load the connection properties from a file. They are not hard coded in the proram.

The properties are loaded into the Properties class with load . The data is read from the database.properties file, located in src/main/resources .

The values are retrieved with the getProperty method.

Java PostgreSQL multiple statements

It is possible to execute multiple SQL statements in one query.

In the code example, we retrieve three rows from the authors table. We use three SELECT statements to get three rows.

Here we have a query with multiple statements. The statements are separated by a semicolon.

We call the execute method of the prepared statement object. The method returns a boolean value indicating if the first result is a ResultSet object. Subsequent results are called using the getMoreResults method.

The processing of the results is done inside the do/while loop. The ResultSet is retrieved with the getResultSet method call. To find out if there are other results, we call the getMoreResults method.

The first three rows were retrieved from the authors table.

Metadata

Metadata is information about the data in the database. Metadata in a PostgreSQL database contains information about the tables and columns, in which we store data. The number of rows affected by an SQL statement is a metadata. The number of rows and columns returned in a result set belongs to metadata as well.

Metadata in PostgreSQL can be obtained by calling the getMetaData method of the result set object or from the information_schema table.

In the next example we print column headers with the data from the database table.

In this program, we select authors from the authors table and their books from the books table. We print the names of the columns returned in the result set. We format the output.

This is the SQL statement which joins authors with their books.

To get the column names we need to get the ResultSetMetaData . It is an object that can be used to get information about the types and properties of the columns in a ResultSet object.

From the obtained metadata, we get the column names.

We print the column names to the console. We use the Formatter object to format the data.

We print the data to the console. We again use the Formatter object to format the data. The first column is 21 characters wide and is aligned to the left.

In the following example we list all tables in the testdb database.

The code example prints all available tables in the current database to the terminal.

The table names are stored inside the system information_schema table.

Listing available tables in the testdb database.

Writing images

Some people prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with lots of images. Images are binary data. PostgreSQL database has a special data type to store binary data called bytea . This is a non-standard data type. The standard data type in databases is BLOB .

For this example, we use the images table.

In the preceding example, we read a JPG image from the current working directory and insert in into the images table.

This is the SQL to insert an image.

We create a File object for the image file. To read bytes from this file, we create a FileInputStream object.

The binary stream is set to the prepared statement. The parameters of the setBinaryStream method are the parameter index to bind, the input stream and the number of bytes in the stream.

We execute the statement.

We look in the table if we have written the image.

Reading images

In the previous example, we have inserted an image into the database table. Now we are going to read the image back from the table.

Warning: if we are using PostgreSQL 9 and later, we must also use the latest JDBC driver. PostgreSQL has changed the way bytea data is stored. Therefore we run into troubles when using older driver with the PostgreSQL 9.x. Notice that NetBeans or other applications might use older drivers.

We read one image from the images table.

We select the data and the size of the image from the database table.

The FileOutputStream object is created to write to a file. It is meant for writing streams of raw bytes such as image data.

We get the length of the image data in bytes.

The getBytes method retrieves all bytes from the result set as an array of bytes.

The bytes are written to the output stream. The image is created on the filesystem.

Transaction support

A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.

When a connection is created, it is in autocommit mode. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. This is true for all JDBC drivers, including the PostgreSQL’s one. To start a new transaction, we turn the autocommit off.

In direct SQL, a transaction is started with BEGIN TRANSACTION statement and ended with END TRANSACTION , COMMIT statement. In PostgreSQL these statements are BEGIN and COMMIT . However, when working with drivers these statements are omitted. They are handled by the driver. Exact details are specific to the driver. For example the psycopg2 Python driver starts a transaction after the first SQL statement. The autocommit mode must be set by setting the autocommit property to True. In constrast, JDBC driver is by default in the autocommit mode. And to start a new transaction, the autocommit must be turned off.

In this program, we want to change the name of the author in the first row of the authors table. We must also change the books associated with this author. A good example where a transaction is necessary. If we change the author and do not change the author’s books, the data is corrupted.

To work with transactions, we must set the autocommit to false. By default, a database connection is in autocommit mode. In this mode each statement is committed to the database as soon as it is executed. A statement cannot be undone. When the autocommit is turned off, we commit the changes by calling the commit or roll it back by calling the rollback method.

The third SQL statement has an error. There is no titl column in the table.

If there is no exception, the transaction is committed. If the autocommit is turned off, we must explicitly call the commit method.

In case of an exception, the transaction is rolled back. No changes are committed to the database.

After we run the application, we verify the data. The transaction was rolled back and no changes took place.

However, without a transaction, the data is not safe.

We have the same example. This time without the transaction support.

Leo Tolstoy did not write Martin Eden. The data is corrupted.

Batch updates

When we need to update data with multiple statements, we can use batch updates. Batch updates are available for INSERT , UPDATE , and DELETE statements as well as for CREATE TABLE and DROP TABLE statements.

This is an example program for a batch update. We create a new table called friends and insert five rows into it.

Autocommit should always be turned off when doing batch updates.

We use teh addBatch method to add a new command to the statement.

After adding all commands, we call the executeBatch to perform a batch update. The method returns an array of committed changes.

Batch updates are committed in a transaction.

We execute the BatchUpdate program. We have created a new friends table and successfully inserted 5 rows. DROP TABLE and CREATE TABLE statements are also included in the updates count.

Export and import of data

PostgreSQL has a COPY statement which can be used to copy data between a table and a file. From the JDBC point of view, it is an extension to the standard.

In the previous example a simple friends table was created. In the above code, we will copy the friends table to a file.

Here we create an instance of the CopyManager . The CopyManager is the API for PostgreSQL COPY bulk data transfer.

We create an OutputStreamWriter to the friends.txt file.

We pass the results of a COPY TO STDOUT query from database into a writer using the copyOut method. The columns will be delimited with the | character.

This is the file created.

In the second example, we do the reverse operation. We copy the data from the file into the database table.

The example uses the FileReader class to read the contents of the friends.txt table and the COPY statement to transfer the data to the friends class.

We will read from the friends.txt file.

We copy the data from the file using the COPY statement.

We delete the data from the friends table.

We run the program.

We check the contents of the friends table.

Источник