Java PostgreSQL elérése

• Szerző: Sallai András
• Copyright © 2024, Sallai András
• Szerkesztve: 2024
• Licenc: CC BY-SA 4.0
• Web: https://szit.hu

• https://jdbc.postgresql.org/

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.1</version>
</dependency>

Felhasználó létrehozása:

create role zoldzrt login password 'titok';

App.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
 
 
public class App {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost/zoldzrt";
        Properties props = new Properties();
        props.setProperty("user", "zoldzrt");
        props.setProperty("password", "titok");
        props.setProperty("ssl", "ture");
        try {
            Connection conn = DriverManager.getConnection(url, props);
            System.out.println("Ok. A kapcsolódás sikeres.");
        } catch (SQLException e) {
            System.err.println("Hiba! A kapcsolódás sikertelen!");
        }
    }
}

Jog beállítása.

grant pg_read_all_data to zoldzrt;
grant pg_write_all_data to zoldzrt;

create_employees.sql

create table employees (
    id serial not null primary key,
    name varchar(50),
    city varchar(50),
    salary numeric,
    birth date
);

A pénznem legyen real, vagy numeric.

App.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.time.LocalDate;
 
public class App {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost/zoldzrt";
        Properties props = new Properties();
        props.setProperty("user", "zoldzrt");
        props.setProperty("password", "titok");
        props.setProperty("ssl", "ture");
        try {
            Connection conn = DriverManager.getConnection(url, props);
            System.out.println("Ok. A kapcsolódás sikeres.");
            String sql = "insert into employees" +
            "(name, city, salary, birth)" +
            "values" +
            "(?, ?, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "Csintalan Ervin");
            ps.setString(2, "Hatvan");
            ps.setDouble(3, 396);
            ps.setDate(4, java.sql.Date.valueOf(LocalDate.parse("1998-04-02")));
            ps.execute();
        } catch (SQLException e) {
            System.err.println("Hiba!");
            System.err.println(e.getMessage());
        }
    }
}

App.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.Properties;
 
public class App {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost/zoldzrt";
        Properties props = new Properties();
        props.setProperty("user", "zoldzrt");
        props.setProperty("password", "titok");
        props.setProperty("ssl", "ture");
        try {
            Connection conn = DriverManager.getConnection(url, props);
            System.out.println("Ok. A kapcsolódás sikeres.");
            String sql = "select * from employees";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while(rs.next()) {
                System.out.printf(
                    "%d %15s %8s %.1f %s\n",
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("city"),
                    rs.getDouble("salary"),
                    rs.getDate("birth")
                );
            }
        } catch (SQLException e) {
            System.err.println("Hiba!");
            System.err.println(e.getMessage());
        }
    }
}

Employee.java

import java.time.LocalDate;
 
public class Employee {
    int id;
    String name;
    String city;
    double salary;
    java.time.LocalDate birth;
 
    public Employee(int id, String name, String city, double salary, LocalDate birth) {
        this.id = id;
        this.name = name;
        this.city = city;
        this.salary = salary;
        this.birth = birth;
    }
 
    @Override
    public String toString() {
        return String.format(
            "%d %15s %8s %.1f %s",
        id, name, city, salary, birth
        );
    }
}

App.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.Properties;
 
public class App {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost/zoldzrt";
        Properties props = new Properties();
        props.setProperty("user", "zoldzrt");
        props.setProperty("password", "titok");
        props.setProperty("ssl", "ture");
        try {
            Connection conn = DriverManager.getConnection(url, props);
            System.out.println("Ok. A kapcsolódás sikeres.");
            String sql = "select * from employees";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(sql);
            while(rs.next()) {                
                Employee emp = new Employee(
                rs.getInt("id"),
                rs.getString("name"),
                rs.getString("city"),
                rs.getDouble("salary"),
                rs.getDate("birth").toLocalDate()
                );
                System.out.println(emp.toString());
            }
        } catch (SQLException e) {
            System.err.println("Hiba!");
            System.err.println(e.getMessage());
        }
    }
}

App.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import java.time.LocalDate;
 
public class App {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost/zoldzrt";
        Properties props = new Properties();
        props.setProperty("user", "zoldzrt");
        props.setProperty("password", "titok");
        props.setProperty("ssl", "ture");
        try {
            Connection conn = DriverManager.getConnection(url, props);
            System.out.println("Ok. A kapcsolódás sikeres.");
            String sql = "update employees set " +
                        "name=?, city=?, salary=?, birth=? "+
                        "where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, "Pere Irén");
            ps.setString(2, "Szeged");
            ps.setDouble(3, 393.5);
            ps.setDate(4, java.sql.Date.valueOf(LocalDate.parse("1999-05-07")));
            ps.setInt(5, 2);
            int affectedRows = ps.executeUpdate();
 
            System.out.println("Érintett sorok: " + affectedRows);
 
        } catch (SQLException e) {
            System.err.println("Hiba!");
            System.err.println(e.getMessage());
        }
    }
}

App.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
 
public class App {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:postgresql://localhost/zoldzrt";
        Properties props = new Properties();
        props.setProperty("user", "zoldzrt");
        props.setProperty("password", "titok");
        props.setProperty("ssl", "ture");
        try {
            Connection conn = DriverManager.getConnection(url, props);
            System.out.println("Ok. A kapcsolódás sikeres.");
            String sql = "delete from employees " +                        
                        "where id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, 2);
            int affectedRows = ps.executeUpdate();
 
            System.out.println("Érintett sorok: " + affectedRows);
 
        } catch (SQLException e) {
            System.err.println("Hiba!");
            System.err.println(e.getMessage());
        }
    }
}