Jdbc driver postgresql ubuntu

Postgresql JDBC Tutorial on Linux

PosgtreSQL is a free and general purpose open source object-relational database system that uses and extends the SQL language. Though originally designed to run on UNIX platforms, Postgres is able to run on various platforms, such as macOS, Solaris, Windows, Unix, and Linux. PostgreSQL databases provide enterprise-class database solutions and are used by a wide variety of enterprises across many industries, like financial services, information technology, government and media & communications.

This article first covers installation of PostgreSQL and Java on Linux Platform and thereafter integration of PostgreSQL with JDBC. The article shows how to connect to PostgreSQL database using JDBC Driver on a Linux platform (this example uses Centos). The article covers basic select, insert, update, delete operations.

By the end of this article reader should be able to install and run PostgreSQL database on Linux platform. Also should be able to connect to the PostgreSQL db with JDBC via Java programs.

The examples have been tested to run with the following software setup:

• Java 1.7 or above
• Eclipse (IDE)
• PostgreSQL 10
• PgAdmin
• PostgreSQL JDBC driver 42.2.5(A jar file, add it to the project classpath) – Available here http://jdbc.postgresql.org/download.html

Software Setup

Note — This section can be skipped if the required software are already there in the system.

First, we need to install PostgreSQL with the help of the below command. This installs PostgreSQL from Centos repositories.

To check the version installed, use the command

Post installation, initialize the database and start the database thereafter

PgAdmin (Optional Installation)

This a free source GUI tool of PostgreSQL or in other words a client supported by all operating systems like Windows, MAC, Linux which can be used to access the schema and tables. If not preferred, you can always connect to the postgreSQL db via the terminal.

Install PgAdmin in centos. The following command installs PgAdmin4 in the system.

The following commands installs Java 1.8 in Linux platform. Since this article covers integration of PostgreSQL with JDBC , Java is required to compile and run the programs covered below. This first command adds the java repository.

This will update the repository

Lastly, we install Oracle JDK8

Eclipse IDE

Eclipse is a free and open source IDE for programming. We use this IDE in our examples to write and execute the Java programs. There are also other IDE’s available in the market, which can be used. In this particular example we use Eclipse simply because it is lightweight and very user friendly.

Login as administrator and download Eclipse using the following command,

Extract the Eclipse package to your desired directory ( /opt ).

Next, create Eclipse desktop launcher into system applications directory

Add the following configuration into the file, eclipse.desktop.

After you’ve created Eclipse launcher, use Gnome dash to search and open the application

Code and Illustration

Before programming in Java we need to have the database and table in place. We will be using the default database ‘postgres’ in this tutorial. First, navigate to the Centos terminal. Login to the default database ‘postgres’ as user ‘postgres’. This will open up the ‘postgres=#’ prompt.

Enter \c to verify

Enter command \l to get a list of all databases

Now, let us create a table called ‘Student’.

We will use Eclipse to execute the following Java programs. If you are not familiar with Java programming in Eclipse you can take a look here, https://www.tutorialspoint.com/eclipse/eclipse_create_java_project.htm To get started, follow these steps:

1. Create a Java Project
2. Create package hierarchy ‘postgresql.jdbc’

JDBC Database Connection

The DriverManager.getConnection() establishes a connection to the database by using the given database URL and the registered JDBC driver. Create a class ‘PostgresWithJDBCConnection’

When the above program is compiled and executed successfully, it should display the following output

Connection established successfully

JDBC Insert

The following Java which program shows how we can insert records in the Student table, is primarily divided into two segments.

• connection to the database
• preparation and execution of the prepared statement using insert query

Create a class ‘PostgresWithJDBCInsert’

When the above program is compiled and executed successfully, it should display the following line

record inserted successfully

JDBC Select

The following program shows how we can fetch and display all records from the Student table. Similar to the insert program, the following program is divided into two segments:

• connection to the database
• preparation and execution of the PreparedStatement using select query

Create a class ‘PostgresWithJDBCSelect’

When the above program is compiled and executed successfully, it should display the following

Roll No:: 1 Section:: A

JDBC Update

The following Java program shows how we can update a record in Student table. Here also the program is divided into three segments:

• connection to the database
• preparation and execution of the PreparedStatement using update query
• fetches the updated data using select query

Create a class ‘PostgresWithJDBCUpdate’

When the above program is compiled and executed successfully, it should display the following

record updated successfully Section:: D

JDBC Delete

The following Java program shows how we can delete a record in Student table. The following program is divided into three segments:

• connection to the database
• preparation and execution of the PreparedStatement using delete query
• fetches the updated data using select query

Create a class ‘PostgresWithJDBCDelete’

When the above program is compiled and executed successfully, it should display the following

record deleted successfully Section:: D

Summary

To summarize over the course of this article we present an overview of how we can install PostgreSQL, Java on Linux(Centos) and then connect to the PostgreSQL database using JDBC and perform some crud operations on it via Java program.

Источник

Setting up the JDBC Driver

This section describes the steps you need to take before you can write or run programs that use the JDBC interface.

Getting the Driver

Precompiled versions of the driver can be downloaded from the PostgreSQL® JDBC web site.

Alternatively you can build the driver from source, but you should only need to do this if you are making changes to the source code. To build the JDBC driver, you need gradle and a JDK (currently at least jdk1.8).

If you have several Java compilers installed, maven will use the first one on the path. To use a different one set JAVA_HOME to the Java version you wish to use. For example, to use a different JDK than the default, this may work:

To compile the driver simply run gradlew assemble or gradlew build if you want to run the tests in the top level directory.

If you want to skip test execution, add the option -DskipTests . The compiled driver will be placed in pgjdbc/build/libs/postgresql-MM.nn.pp.jar

Where MM is the major version, nn is the minor version and pp is the patch version. Versions for JDBC3 and lower can be found here

This is a very brief outline of how to build the driver. Much more detailed information can be found on the github repo

Even though the JDBC driver should be built with Gradle, for situations, where use of Gradle is not possible, e.g., when building pgJDBC for distributions, the pgJDBC Gradle build provides a convenience source release artifact *-src.tar.gz — a Maven based project. The Maven based project contains a version of the JDBC driver with complete functionality, which can be used in production and is still validly buildable within the Maven build environment. The Maven-based project is created with gradlew -d :postgresql:sourceDistribution -Prelease . The produced *-src.tar.gz can be then found in pgjdbc/build/distributions/ directory. JDBC driver can be built from the Maven-based project with mvn package or, when the tests are to be skipped, with mvn -DskipTests package . Source files *-src.tar.gz ’s are released in the Maven central repository.

Setting up the Class Path

To use the driver, the JAR archive named postgresql-MM.nn.pp.jar needs to be included in the class path, either by putting it in the CLASSPATH environment variable, or by using flags on the java command line.

For instance, assume we have an application that uses the JDBC driver to access a database, and that application is installed as /usr/local/lib/myapp.jar . The PostgreSQL® JDBC driver installed as /usr/local/pgsql/share/java/postgresql-MM.nn.pp.jar . To run the application, we would use:

Current Java applications will likely use maven, gradle or some other package manager. Use this to search for the latest jars and how to include them in your project

Loading the driver from within the application is covered in Initializing the Driver.

Preparing the Database Server for JDBC

Out of the box, Java does not support unix sockets so the PostgreSQL® server must be configured to allow TCP/IP connections. Starting with server version 8.0 TCP/IP connections are allowed from localhost . To allow connections to other interfaces than the loopback interface, you must modify the postgresql.conf file’s listen_addresses setting.

Once you have made sure the server is correctly listening for TCP/IP connections the next step is to verify that users are allowed to connect to the server. Client authentication is setup in pg_hba.conf . Refer to the main PostgreSQL® documentation for details .

Creating a Database

When creating a database to be accessed via JDBC it is important to select an appropriate encoding for your data. Many other client interfaces do not care what data you send back and forth, and will allow you to do inappropriate things, but Java makes sure that your data is correctly encoded. Do not use a database that uses the SQL_ASCII encoding. This is not a real encoding and you will have problems the moment you store data in it that does not fit in the seven bit ASCII character set. If you do not know what your encoding will be or are otherwise unsure about what you will be storing the UNICODE encoding is a reasonable default to use.

Источник

pgjdbc/pgjdbc

Use Git or checkout with SVN using the web URL.

Work fast with our official CLI. Learn more.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.md

PostgreSQL JDBC Driver

PostgreSQL JDBC Driver (PgJDBC for short) allows Java programs to connect to a PostgreSQL database using standard, database independent Java code. Is an open source JDBC driver written in Pure Java (Type 4), and communicates in the PostgreSQL native network protocol.

Supported PostgreSQL and Java versions

The current version of the driver should be compatible with PostgreSQL 8.4 and higher using the version 3.0 of the protocol and Java 8 (JDBC 4.2) or above. Unless you have unusual requirements (running old applications or JVMs), this is the driver you should be using.

PgJDBC regression tests are run against all PostgreSQL versions since 9.1, including «build PostgreSQL from git master» version. There are other derived forks of PostgreSQL but they have not been certified to run with PgJDBC. If you find a bug or regression on supported versions, please file an Issue.

Most people do not need to compile PgJDBC. You can download the precompiled driver (jar) from the PostgreSQL JDBC site or using your chosen dependency management tool:

You can search on The Central Repository with GroupId and ArtifactId org.postgresql:postgresql.

Snapshot builds (builds from master branch) are also deployed to OSS Sonatype Snapshot Repository, so you can test current development version (test some bugfix) by enabling the repository and using the latest SNAPSHOT version.

There are also available (snapshot) binary RPMs in Fedora’s Copr repository.

For more information you can read the PgJDBC driver documentation or for general JDBC documentation please refer to The Java™ Tutorials.

Driver and DataSource class

Implements	Class
java.sql.Driver	org.postgresql.Driver
javax.sql.DataSource	org.postgresql.ds.PGSimpleDataSource
javax.sql.ConnectionPoolDataSource	org.postgresql.ds.PGConnectionPoolDataSource
javax.sql.XADataSource	org.postgresql.xa.PGXADataSource

Building the Connection URL

The driver recognises JDBC URLs of the form:

The general format for a JDBC URL for connecting to a PostgreSQL server is as follows, with items in square brackets ([ ]) being optional:

• jdbc:postgresql: (Required) is known as the sub-protocol and is constant.
• host (Optional) is the server address to connect. This could be a DNS or IP address, or it could be localhost or 127.0.0.1 for the local computer. To specify an IPv6 address your must enclose the host parameter with square brackets (jdbc:postgresql://[::1]:5740/accounting). Defaults to localhost .
• port (Optional) is the port number listening on the host. Defaults to 5432 .
• database (Optional) is the database name. Defaults to the same name as the user name used in the connection.
• propertyX (Optional) is one or more option connection properties. For more information see Connection properties.

PgJDBC uses java.util.logging for logging. To configure log levels and control log output destination (e.g. file or console), configure your java.util.logging properties accordingly for the org.postgresql logger. Note that the most detailed log levels, » FINEST «, may include sensitive information such as connection details, query SQL, or command parameters.

In addition to the standard connection parameters the driver supports a number of additional properties which can be used to specify additional driver behaviour specific to PostgreSQL™. These properties may be specified in either the connection URL or an additional Properties object parameter to DriverManager.getConnection.

Property	Type	Default	Description
user	String	null	The database user on whose behalf the connection is being made.
password	String	null	The database user’s password.
options	String	null	Specify ‘options’ connection initialization parameter.
service	String	null	Specify ‘service’ name described in pg_service.conf file. References: The Connection Service File and The Password File. ‘service’ file can provide all properties including ‘hostname=’, ‘port=’ and ‘dbname=’.
ssl	Boolean	false	Control use of SSL (true value causes SSL to be required)
sslfactory	String	null	Provide a SSLSocketFactory class when using SSL.
sslfactoryarg (deprecated)	String	null	Argument forwarded to constructor of SSLSocketFactory class.
sslmode	String	prefer	Controls the preference for opening using an SSL encrypted connection.
sslcert	String	null	The location of the client’s SSL certificate
sslkey	String	null	The location of the client’s PKCS#8 SSL key
sslrootcert	String	null	The location of the root certificate for authenticating the server.
sslhostnameverifier	String	null	The name of a class (for use in Class.forName(String)) that implements javax.net.ssl.HostnameVerifier and can verify the server hostname.
sslpasswordcallback	String	null	The name of a class (for use in Class.forName(String)) that implements javax.security.auth.callback.CallbackHandler and can handle PasswordCallback for the ssl password.
sslpassword	String	null	The password for the client’s ssl key (ignored if sslpasswordcallback is set)
sendBufferSize	Integer	-1	Socket write buffer size
receiveBufferSize	Integer	-1	Socket read buffer size
logServerErrorDetail	Boolean	true	Allows server error detail (such as sql statements and values) to be logged and passed on in exceptions. Setting to false will mask these errors so they won’t be exposed to users, or logs.
allowEncodingChanges	Boolean	false	Allow for changes in client_encoding
logUnclosedConnections	Boolean	false	When connections that are not explicitly closed are garbage collected, log the stacktrace from the opening of the connection to trace the leak source
binaryTransferEnable	String	«»	Comma separated list of types to enable binary transfer. Either OID numbers or names
binaryTransferDisable	String	«»	Comma separated list of types to disable binary transfer. Either OID numbers or names. Overrides values in the driver default set and values set with binaryTransferEnable.
prepareThreshold	Integer	5	Statement prepare threshold. A value of -1 stands for forceBinary
preparedStatementCacheQueries	Integer	256	Specifies the maximum number of entries in per-connection cache of prepared statements. A value of 0 disables the cache.
preparedStatementCacheSizeMiB	Integer	5	Specifies the maximum size (in megabytes) of a per-connection prepared statement cache. A value of 0 disables the cache.
defaultRowFetchSize	Integer		Positive number of rows that should be fetched from the database when more rows are needed for ResultSet by each fetch iteration
loginTimeout	Integer		Specify how long to wait for establishment of a database connection.
connectTimeout	Integer	10	The timeout value used for socket connect operations.
socketTimeout	Integer		The timeout value used for socket read operations.
sslResponseTimeout	Integer	5000	Socket timeout waiting for a response from a request for SSL upgrade from the server.
tcpKeepAlive	Boolean	false	Enable or disable TCP keep-alive.
tcpNoDelay	Boolean	true	Enable or disable TCP no delay.
ApplicationName	String	PostgreSQL JDBC Driver	The application name (require server version >= 9.0). If assumeMinServerVersion is set to >= 9.0 this will be sent in the startup packets, otherwise after the connection is made
readOnly	Boolean	false	Puts this connection in read-only mode
disableColumnSanitiser	Boolean	false	Enable optimization that disables column name sanitiser
assumeMinServerVersion	String	null	Assume the server is at least that version
currentSchema	String	null	Specify the schema (or several schema separated by commas) to be set in the search-path
targetServerType	String	any	Specifies what kind of server to connect, possible values: any, master, slave (deprecated), secondary, preferSlave (deprecated), preferSecondary, preferPrimary
hostRecheckSeconds	Integer	10	Specifies period (seconds) after which the host status is checked again in case it has changed
loadBalanceHosts	Boolean	false	If disabled hosts are connected in the given order. If enabled hosts are chosen randomly from the set of suitable candidates
socketFactory	String	null	Specify a socket factory for socket creation
socketFactoryArg (deprecated)	String	null	Argument forwarded to constructor of SocketFactory class.
autosave	String	never	Specifies what the driver should do if a query fails, possible values: always, never, conservative
cleanupSavepoints	Boolean	false	In Autosave mode the driver sets a SAVEPOINT for every query. It is possible to exhaust the server shared buffers. Setting this to true will release each SAVEPOINT at the cost of an additional round trip.
preferQueryMode	String	extended	Specifies which mode is used to execute queries to database, possible values: extended, extendedForPrepared, extendedCacheEverything, simple
reWriteBatchedInserts	Boolean	false	Enable optimization to rewrite and collapse compatible INSERT statements that are batched.
escapeSyntaxCallMode	String	select	Specifies how JDBC escape call syntax is transformed into underlying SQL (CALL/SELECT), for invoking procedures or functions (requires server version >= 11), possible values: select, callIfNoReturn, call
maxResultBuffer	String	null	Specifies size of result buffer in bytes, which can’t be exceeded during reading result set. Can be specified as particular size (i.e. «100», «200M» «2G») or as percent of max heap memory (i.e. «10p», «20pct», «50percent»)
gssEncMode	String	allow	Controls the preference for using GSSAPI encryption for the connection, values are disable, allow, prefer, and require
adaptiveFetch	Boolean	false	Specifies if number of rows fetched in ResultSet by each fetch iteration should be dynamic. Number of rows will be calculated by dividing maxResultBuffer size into max row size observed so far. Requires declaring maxResultBuffer and defaultRowFetchSize for first iteration.
adaptiveFetchMinimum	Integer		Specifies minimum number of rows, which can be calculated by adaptiveFetch. Number of rows used by adaptiveFetch cannot go below this value.
adaptiveFetchMaximum	Integer	-1	Specifies maximum number of rows, which can be calculated by adaptiveFetch. Number of rows used by adaptiveFetch cannot go above this value. Any negative number set as adaptiveFetchMaximum is used by adaptiveFetch as infinity number of rows.
localSocketAddress	String	null	Hostname or IP address given to explicitly configure the interface that the driver will bind the client side of the TCP/IP connection to when connecting.
quoteReturningIdentifiers	Boolean	true	By default we double quote returning identifiers. Some ORM’s already quote them. Switch allows them to turn this off
authenticationPluginClassName	String	null	Fully qualified class name of the class implementing the AuthenticationPlugin interface. If this is null, the password value in the connection properties will be used.

For information on how to contribute to the project see the Contributing Guidelines

Источник