[[oktatas:programozas:java:java adatbázis|< Java adatbázis]]

====== Java PostgreSQL elérése ======

  * **Szerző:** Sallai András
  * Copyright (c) 2024, Sallai András
  * Szerkesztve: 2024
  * Licenc: [[https://creativecommons.org/licenses/by-sa/4.0/|CC BY-SA 4.0]]
  * Web: https://szit.hu

===== Konnektor =====

  * https://jdbc.postgresql.org/

==== Maven ====

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

===== Kapcsolódás =====

Felhasználó létrehozása:
<code postgresql>
create role zoldzrt login password 'titok';
</code>


<code java 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!");
        }
    }
}
</code>


===== Tábla készítése =====

Jog beállítása.

<code postgresql>
grant pg_read_all_data to zoldzrt;
grant pg_write_all_data to zoldzrt;
</code>

<code postgresql create_employees.sql>
create table employees (
    id serial not null primary key,
    name varchar(50),
    city varchar(50),
    salary numeric,
    birth date
);
</code>

A pénznem legyen real, vagy numeric.

===== Beszúrás =====

<code java 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());
        }
    }
}

</code>

===== Lekérdezés =====

<code java 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());
        }
    }
}
</code>

===== Employee modell használata =====

<code java 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
        );
    }
}
</code>


<code java 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());
        }
    }
}
</code>

===== Update művelet =====

<code java 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());
        }
    }
}
</code>

===== Törlés =====

<code java 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());
        }
    }
}
</code>