How to setup PostgreSQL with Java in GNU/Linux
Table of Contents
There you are, hope you doing good. I tried doing this from many blogs and here I present 2 blogs from which one was suceeded and is placed just after this description.
So, Read along!
PostgreSQL is object relational database and Java is also heavily object oreinted.
Second method:
Following this tutorial from zetcode
Create new Maven project:
mvn archetype:generate -DgroupId=com.atulya.postgresql -DartifactId=java-postgresql-connection2 -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false
After creating the project, add this under dependencies
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.0</version>
</dependency>
Inside project tags add
<build>
<plugins>
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>exec-maven-plugin</artifactId>
<version>1.6.0</version>
<configuration>
<mainClass>com.atulya.postgresql.App</mainClass>
</configuration>
</plugin>
</plugins>
</build>
You can run the project by
$ mvn compile
$ mvn -q exec:java
Postgres user:
user:user12
password:user12
In file App.java add following
package com.atulya.postgresql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
public class App {
public static void main(String[] args) {
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "user12";
try (Connection con = DriverManager.getConnection(url, user, password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT VERSION()")) {
if (rs.next()) {
System.out.println(rs.getString(1));
}
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(App.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
Now that connection is completed 🥳️.
Create a tablesfordatabase.sql file and add
You can add these queries in pgAdmin if you want to.
DROP TABLE IF EXISTS books, authors, testing, images;
CREATE TABLE IF NOT EXISTS authors (
id serial PRIMARY KEY,
name VARCHAR(25)
);
CREATE TABLE IF NOT EXISTS books (
id serial PRIMARY KEY,
author_id INT references authors(id), title VARCHAR(100)
);
CREATE TABLE IF NOT EXISTS testing(id INT);
CREATE TABLE IF NOT EXISTS images(id serial, data bytea);
INSERT INTO authors(id, name) VALUES(1, 'Jack London');
INSERT INTO authors(id, name) VALUES(2, 'Honore de Balzac');
INSERT INTO authors(id, name) VALUES(3, 'Lion Feuchtwanger');
INSERT INTO authors(id, name) VALUES(4, 'Emile Zola');
INSERT INTO authors(id, name) VALUES(5, 'Truman Capote');
INSERT INTO books(id, author_id, title) VALUES(1, 1, 'Call of the Wild');
INSERT INTO books(id, author_id, title) VALUES(2, 1, 'Martin Eden');
INSERT INTO books(id, author_id, title) VALUES(3, 2, 'Old Goriot');
INSERT INTO books(id, author_id, title) VALUES(4, 2, 'Cousin Bette');
INSERT INTO books(id, author_id, title) VALUES(5, 3, 'Jew Suess');
INSERT INTO books(id, author_id, title) VALUES(6, 4, 'Nana');
INSERT INTO books(id, author_id, title) VALUES(7, 4, 'The Belly of Paris');
INSERT INTO books(id, author_id, title) VALUES(8, 5, 'In Cold blood');
INSERT INTO books(id, author_id, title) VALUES(9, 5, 'Breakfast at Tiffany');
login to user postgres user:
> Make sure your opening the postgres terminal where the **tablesfordatabase.sql**
> file is created.
. Conversations supports a couple of those
$ psql -U user12 -d testdb -W
user: user12
password: user12
After logging in
testdb=# \i tablesfordatabase.sql
testdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+--------
public | authors | table | user12
public | books | table | user12
public | images | table | user12
public | testing | table | user12
(4 rows)
Insert value into database:
Along with App.java created another file called JavaPostgreSqlPrepared.java and add:
package com.atulya.postgresql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
public class JavaPostgreSqlPrepared {
public static void insertAuther(){
String url = "jdbc:postgresql://localhost:5432/testdb";
String user = "user12";
String password = "user12";
int id = 6;
String author = "Trygve Gulbranssen";
String query = "INSERT INTO authors(id, name) VALUES(?, ?)";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query)) {
pst.setInt(1, id);
pst.setString(2, author);
pst.executeUpdate();
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlPrepared.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
}
}
After creating file in App.java
...
//Inside main method:
JavaPostgreSqlPrepared.insertAuthor();
// call it directly here.
...
Compile and run the Maven project.
Check the database entry of newly added author in authors table.
First method I tried..
Will continue it later..
Following tutorial from:
Let’s get going by creating project with:
mvn archetype:generate -DgroupId=com.stackabuse.postgresql -DartifactId=java-postgresql-sample -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false
Then, in your pom.xml file, add the Postgres dependency:
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>{version}</version>
</dependency>
After editing pom.xml go inside src/java/com/stackbuse/postgresql/
in that directory
mkdir core spi api
Domain Model
Create a directory called api in our src directory in which we’ll define a model/entity - Customer:
public class Customer {
private Integer id;
private String firstName;
private String lastName;
private String email;
// Constructor, getters and setters...
@Override
public String toString() {
return "Customer["
+ "id=" + id
+ ", firstName=" + firstName
+ ", lastName=" + lastName
+ ", email=" + email
+ ']';
}
}
Create the file Customer.java in api directory.
CRUD Functionality
Implementing our CRUD functionality through a Dao interface in the api directory, which will house all of our interfaces and service classes:
public interface Dao<T, I> {
Optional<T> get(int id);
Collection<T> getAll();
Optional<I> save(T t);
void update(T t);
void delete(T t);
}
Create the file Dao.java inside spi directory.
Postgres setup
You can checkout my [post](## TODO) for this.
Start postgres
sudo service postgresql start
Install pgAdmin:
It asks for email and password
email: atulya@ratoo.com
password: 403fgdi0h
Start pgAdmin
$ pgadmin4
Inside pgAdmin:
- Login with credentials
- Add localhost and your password
- Create Database: Mine is java-databass
- Create Table: Customer
In sql editor of pgAdmin
CREATE TABLE public.customer
(
customer_id integer NOT NULL GENERATED ALWAYS AS IDENTITY (START 1 INCREMENT 1 ),
first_name character varying(45) NOT NULL,
last_name character varying(45) NOT NULL,
email character varying(50),
CONSTRAINT customer_pkey PRIMARY KEY (customer_id)
)
Database work is done for now.
Connection of java with database:
public class JdbcConnection {
private static final Logger LOGGER =
Logger.getLogger(JdbcConnection.class.getName());
private static Optional<Connection> connection = Optional.empty();
public static Optional<Connection> getConnection() {
if (connection.isEmpty()) {
String url = "jdbc:postgresql://localhost:5432/sampledb";
String user = "postgres";
String password = "postgres";
try {
connection = Optional.ofNullable(
DriverManager.getConnection(url, user, password));
} catch (SQLException ex) {
LOGGER.log(Level.SEVERE, null, ex);
}
}
return connection;
}
}
Create the file JdbcConnection.java in core directory.