Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

di
iBaffiPro
il
10 risposte
iBaffiPro
iBaffiPro
Utente Attivo
Iscritto da
feb, 2021
Messaggi:
398

Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

Ho una query SELECT leggermente più articolata del solito che ho già testato sul query tools di PostgreSQL e che funziona alla perfezione. Vorrei implementare questa query dentro la mia classe @Repository. Il metodo che ho scritto restituisce sempre null e non so dove sia il problema. Il mio oggetto RowMapper a differenza di quelli che ho scritto in precedenza non si riferisce ad una reale tabella del database perché nella SELECT c’è anche una INNER JOIN. Non vorrei fosse questo il problema. Ho stampato la query prodotta dal metodo con i punti interrogativi nella console, poi ho copiato le stringe sul query tools dell’IDE, infine sostituito i ‘?‘ con i valori di mio interesse e tutto funziona a meraviglia. Non so se sbaglio a scrivere l’oggetto RowMapper oppure l’oggetto Repository. 

    @Transactional(readOnly=true)
    public List<EventoConNomiUtente> cercaEventi(CercaEventi cercaEventi) {
        try{
            // Si pongono pari a null i parametri di ricerca non idonei.
            if(cercaEventi==null) {
                return null;
            }
            if(cercaEventi.getId_utente()==null || cercaEventi.getId_utente()<=0) {
                cercaEventi.setId_utente(null);
            }
            if(cercaEventi.getNome_utente()==null || cercaEventi.getNome_utente().isBlank()) {
                cercaEventi.setNome_utente(null);
            }
            if(cercaEventi.getTitolo()==null || cercaEventi.getTitolo().isBlank()) {
                cercaEventi.setTitolo(null);
            }
            if(cercaEventi.getDescrizione()==null || cercaEventi.getDescrizione().isBlank()) {
                cercaEventi.setDescrizione(null);
            }
            if(cercaEventi.getLuogo()==null || cercaEventi.getLuogo().isBlank()) {
                cercaEventi.setLuogo(null);
            }
            if(cercaEventi.getStato()==null || cercaEventi.getStato().isBlank()) {
                cercaEventi.setStato(null);
            }
            if(
                    cercaEventi.getOrdine()==null ||
                            !(cercaEventi.getOrdine().equals("ASC") || cercaEventi.getOrdine().equals("DESC"))
            ) {
                cercaEventi.setOrdine(null);
            }
            if(cercaEventi.getNumero_righe()==null || cercaEventi.getNumero_righe()<=0) {
                cercaEventi.setNumero_righe(null);
            }
            // System.out.println(cercaEventi.toString());
            // Si crea una stringa variabile contenente la query.
            StringBuilder select = new StringBuilder();
            select.append("CREATE OR REPLACE FUNCTION cercaeventi(\n");
            select.append("id_utente_cercato BIGINT,\n");
            select.append("nome_utente_cercato CHARACTER VARYING,\n");
            select.append("titolo_cercato CHARACTER VARYING,\n");
            select.append("descrizione_cercata TEXT,\n");
            select.append("luogo_cercato CHARACTER VARYING,\n");
            select.append("data_inferiore_compresa_cercata TIMESTAMP WITH TIME ZONE,\n");
            select.append("data_superiore_esclusa_cercata TIMESTAMP WITH TIME ZONE,\n");
            select.append("stato_cercato CHARACTER VARYING,\n");
            select.append("ordine CHARACTER VARYING, -- 'ASC' oppure 'DESC'\n");
            select.append("numero_righe BIGINT\n");
            select.append(")\n");
            select.append("RETURNS TABLE (\n");
            select.append("id_evento BIGINT,\n");
            select.append("id_utente BIGINT,\n");
            select.append("nome_utente CHARACTER VARYING,\n");
            select.append("titolo_evento CHARACTER VARYING,\n");
            select.append("descrizione_evento TEXT,\n");
            select.append("luogo_evento CHARACTER VARYING,\n");
            select.append("data_evento TIMESTAMP WITH TIME ZONE,\n");
            select.append("stato_evento CHARACTER VARYING\n");
            select.append(")\n");
            select.append("LANGUAGE PLPGSQL\n");
            select.append("AS\n");
            select.append("$$\n");
            select.append("BEGIN\n");
            select.append("IF ordine = 'ASC' THEN\n");
            select.append("RETURN QUERY\n");
            select.append("SELECT\n");
            select.append("eventi.id,\n");
            select.append("eventi.id_utente,\n");
            select.append("utenti.nome,\n");
            select.append("eventi.titolo,\n");
            select.append("eventi.descrizione,\n");
            select.append("eventi.luogo,\n");
            select.append("eventi.data,\n");
            select.append("eventi.stato\n");
            select.append("FROM\n");
            select.append("eventi\n");
            select.append("INNER JOIN utenti ON eventi.id_utente = utenti.id\n");
            select.append("WHERE 1=1\n");
            select.append("AND (\n");
            select.append("(id_utente_cercato IS NULL) OR\n");
            select.append("(eventi.id_utente = id_utente_cercato)\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(nome_utente_cercato IS NULL) OR\n");
            select.append("(utenti.nome = nome_utente_cercato AND eventi.id_utente = utenti.id)\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(titolo_cercato IS NULL) OR\n");
            select.append("(LOWER(eventi.titolo) LIKE LOWER(CONCAT('%',titolo_cercato,'%')))\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(descrizione_cercata IS NULL) OR\n");
            select.append("(LOWER(eventi.descrizione) LIKE LOWER(CONCAT('%',descrizione_cercata,'%')))\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(luogo_cercato IS NULL) OR\n");
            select.append("(LOWER(eventi.luogo) LIKE LOWER(CONCAT('%',luogo_cercato,'%')))\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(data_inferiore_compresa_cercata IS NULL) OR\n");
            select.append("(eventi.data >= data_inferiore_compresa_cercata)\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(data_superiore_esclusa_cercata IS NULL) OR\n");
            select.append("(eventi.data < data_superiore_esclusa_cercata )\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(stato_cercato IS NULL) OR\n");
            select.append("(eventi.stato = stato_cercato)\n");
            select.append(")\n");
            select.append("ORDER BY eventi.data ASC\n");
            select.append("LIMIT numero_righe;\n");
            select.append("ELSIF ordine = 'DESC' THEN\n");
            select.append("RETURN QUERY\n");
            select.append("SELECT\n");
            select.append("eventi.id,\n");
            select.append("eventi.id_utente,\n");
            select.append("utenti.nome, -- questa colonna viene importata dalla tabella degli utenti\n");
            select.append("eventi.titolo,\n");
            select.append("eventi.descrizione,\n");
            select.append("eventi.luogo,\n");
            select.append("eventi.data,\n");
            select.append("eventi.stato\n");
            select.append("FROM\n");
            select.append("eventi\n");
            select.append("INNER JOIN utenti ON eventi.id_utente = utenti.id\n");
            select.append("WHERE 1=1\n");
            select.append("AND (\n");
            select.append("(id_utente_cercato IS NULL) OR\n");
            select.append("(eventi.id_utente = id_utente_cercato)\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(nome_utente_cercato IS NULL) OR\n");
            select.append("(utenti.nome = nome_utente_cercato AND eventi.id_utente = utenti.id)\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(titolo_cercato IS NULL) OR\n");
            select.append("(LOWER(eventi.titolo) LIKE LOWER(CONCAT('%',titolo_cercato,'%')))\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(descrizione_cercata IS NULL) OR\n");
            select.append("(LOWER(eventi.descrizione) LIKE LOWER(CONCAT('%',descrizione_cercata,'%')))\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(luogo_cercato IS NULL) OR\n");
            select.append("(LOWER(eventi.luogo) LIKE LOWER(CONCAT('%',luogo_cercato,'%')))\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(data_inferiore_compresa_cercata IS NULL) OR\n");
            select.append("(eventi.data >= data_inferiore_compresa_cercata)\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(data_superiore_esclusa_cercata IS NULL) OR\n");
            select.append("(eventi.data < data_superiore_esclusa_cercata )\n");
            select.append(")\n");
            select.append("AND (\n");
            select.append("(stato_cercato IS NULL) OR\n");
            select.append("(eventi.stato = stato_cercato)\n");
            select.append(")\n");
            select.append("ORDER BY eventi.data DESC\n");
            select.append("LIMIT numero_righe;\n");
            select.append("ELSE\n");
            select.append("null;\n");
            select.append("END IF;\n");
            select.append("END;\n");
            select.append("$$;\n");
            select.append("SELECT * FROM cercaeventi(\n");
            select.append("?,\n");
            select.append("?,\n");
            select.append("?,\n");
            select.append("?,\n");
            select.append("?,\n");
            select.append("?,\n");
            select.append("?,\n");
            select.append("?,\n");
            select.append("?,\n");
            select.append("?\n");
            select.append(");\n");
            // Si converte in stringa statica l'oggetto precedente.
            String sql = select.toString();
            // System.out.println(sql);
            return jdbcTemplate.query(
                    sql,
                    new EventoConNomiUtenteRowMapper(),
                    cercaEventi.getId_utente(),
                    cercaEventi.getNome_utente(),
                    cercaEventi.getTitolo(),
                    cercaEventi.getDescrizione(),
                    cercaEventi.getLuogo(),
                    cercaEventi.getDataOraInizialeInclusa(),
                    cercaEventi.getDataOraFinaleEsclusa(),
                    cercaEventi.getStato(),
                    cercaEventi.getOrdine(),
                    cercaEventi.getNumero_righe()
            );
        }catch (Exception e){
            return null;
        }
    }


public class EventoConNomiUtenteRowMapper implements RowMapper<EventoConNomiUtente> {

    GestioneDataOra gestioneDataOra = new GestioneDataOra();

    @Override
    public EventoConNomiUtente mapRow(ResultSet rs, int rowNum) throws SQLException {
        EventoConNomiUtente eventoConNomiUtente = new EventoConNomiUtente();
        eventoConNomiUtente.setId_evento(rs.getLong("id_evento"));
        eventoConNomiUtente.setId_utente(rs.getLong("id_utente"));
        eventoConNomiUtente.setNome_utente(rs.getString("nome_utente"));
        eventoConNomiUtente.setTitolo_evento(rs.getString("titolo_evento"));
        eventoConNomiUtente.setDescrizione_evento(rs.getString("descrizione_evento"));
        eventoConNomiUtente.setLuogo_evento(rs.getString("luogo_evento"));
        eventoConNomiUtente.setData_evento(
                gestioneDataOra.daTimestampAdOffsetDateTime(rs.getTimestamp("data_evento"))
        );
        eventoConNomiUtente.setStato_evento(rs.getString("stato_evento"));
        return eventoConNomiUtente;
    }
}


public class EventoConNomiUtente {
    
    private Long id_evento;
    private Long id_utente;
    private String nome_utente;
    private String titolo_evento;
    private String descrizione_evento;
    private String luogo_evento;
    private OffsetDateTime data_evento;
    private String stato_evento;

    public EventoConNomiUtente() {}

    public EventoConNomiUtente(Long id_evento, Long id_utente, String nome_utente, String titolo_evento,
                               String descrizione_evento, String luogo_evento, OffsetDateTime data_evento,
                               String stato_evento) {
        this.id_evento = id_evento;
        this.id_utente = id_utente;
        this.nome_utente = nome_utente;
        this.titolo_evento = titolo_evento;
        this.descrizione_evento = descrizione_evento;
        this.luogo_evento = luogo_evento;
        this.data_evento = data_evento;
        this.stato_evento = stato_evento;
    }

    public Long getId_evento() {
        return id_evento;
    }

    public void setId_evento(Long id_evento) {
        this.id_evento = id_evento;
    }

    public Long getId_utente() {
        return id_utente;
    }

    public void setId_utente(Long id_utente) {
        this.id_utente = id_utente;
    }

    public String getNome_utente() {
        return nome_utente;
    }

    public void setNome_utente(String nome_utente) {
        this.nome_utente = nome_utente;
    }

    public String getTitolo_evento() {
        return titolo_evento;
    }

    public void setTitolo_evento(String titolo_evento) {
        this.titolo_evento = titolo_evento;
    }

    public String getDescrizione_evento() {
        return descrizione_evento;
    }

    public void setDescrizione_evento(String descrizione_evento) {
        this.descrizione_evento = descrizione_evento;
    }

    public String getLuogo_evento() {
        return luogo_evento;
    }

    public void setLuogo_evento(String luogo_evento) {
        this.luogo_evento = luogo_evento;
    }

    public OffsetDateTime getData_evento() {
        return data_evento;
    }

    public void setData_evento(OffsetDateTime data_evento) {
        this.data_evento = data_evento;
    }

    public String getStato_evento() {
        return stato_evento;
    }

    public void setStato_evento(String stato_evento) {
        this.stato_evento = stato_evento;
    }

    // Il metodo toString() serve per stampare in console l'oggetto;
    @Override
    public String toString() {
        return "Oggetto di tipo 'CercaEventi' contenente:" +
                "\n id_evento = " + id_evento +
                "\n id_utente = " + id_utente +
                "\n nome_utente = " + nome_utente +
                "\n titolo_evento = " + titolo_evento +
                "\n descrizione_evento = " + descrizione_evento +
                "\n luogo_evento = " + luogo_evento +
                "\n data_evento = " + data_evento +
                "\n stato_evento = " + stato_evento;
    }
}


public class CercaEventi {

    private Long id_utente;
    private String nome_utente;
    private String titolo;
    private String descrizione;
    private String luogo;
    // Vedi GestioneEventi.java per dettagli sull'annotazione.
    @DateTimeFormat(pattern = "yyyy-MM-dd'T'HH:mm")
    private LocalDateTime dataOraInizialeInclusa;
    @DateTimeFormat(pattern = "yyyy-MM-dd'T'HH:mm")
    private LocalDateTime dataOraFinaleEsclusa;
    private String stato;
    private String ordine;
    private Long numero_righe;

    public CercaEventi() {
    }

    public CercaEventi(Long id_utente, String nome_utente, String titolo, String descrizione, String luogo,
                       LocalDateTime dataOraInizialeInclusa, LocalDateTime dataOraFinaleEsclusa, String stato,
                       String ordine, Long numero_righe) {
        this.id_utente = id_utente;
        this.nome_utente = nome_utente;
        this.titolo = titolo;
        this.descrizione = descrizione;
        this.luogo = luogo;
        this.dataOraInizialeInclusa = dataOraInizialeInclusa;
        this.dataOraFinaleEsclusa = dataOraFinaleEsclusa;
        this.stato = stato;
        this.ordine = ordine;
        this.numero_righe = numero_righe;
    }

    public Long getId_utente() {
        return id_utente;
    }

    public void setId_utente(Long id_utente) {
        this.id_utente = id_utente;
    }

    public String getNome_utente() {
        return nome_utente;
    }

    public void setNome_utente(String nome_utente) {
        this.nome_utente = nome_utente;
    }

    public String getTitolo() {
        return titolo;
    }

    public void setTitolo(String titolo) {
        this.titolo = titolo;
    }

    public String getDescrizione() {
        return descrizione;
    }

    public void setDescrizione(String descrizione) {
        this.descrizione = descrizione;
    }

    public String getLuogo() {
        return luogo;
    }

    public void setLuogo(String luogo) {
        this.luogo = luogo;
    }

    public LocalDateTime getDataOraInizialeInclusa() {
        return dataOraInizialeInclusa;
    }

    public void setDataOraInizialeInclusa(LocalDateTime dataOraInizialeInclusa) {
        this.dataOraInizialeInclusa = dataOraInizialeInclusa;
    }

    public LocalDateTime getDataOraFinaleEsclusa() {
        return dataOraFinaleEsclusa;
    }

    public void setDataOraFinaleEsclusa(LocalDateTime dataOraFinaleEsclusa) {
        this.dataOraFinaleEsclusa = dataOraFinaleEsclusa;
    }

    public String getStato() {
        return stato;
    }

    public void setStato(String stato) {
        this.stato = stato;
    }

    public String getOrdine() {
        return ordine;
    }

    public void setOrdine(String ordine) {
        this.ordine = ordine;
    }

    public Long getNumero_righe() {
        return numero_righe;
    }

    public void setNumero_righe(Long numero_righe) {
        this.numero_righe = numero_righe;
    }

    // Il metodo toString() serve per stampare in console l'oggetto;
    @Override
    public String toString() {
        return "Oggetto di tipo 'CercaEventi' contenente:" +
                "\n id_utente = " + id_utente +
                "\n nome_utente = " + nome_utente +
                "\n titolo = " + titolo +
                "\n descrizione = " + descrizione +
                "\n luogo = " + luogo +
                "\n dataOraInizialeInclusa = " + dataOraInizialeInclusa +
                "\n dataOraFinaleEsclusa = " + dataOraFinaleEsclusa +
                "\n stato = " + stato +
                "\n ordine = " + ordine +
                "\n numero_righe = " + numero_righe;
    }
}


CREATE OR REPLACE FUNCTION cercaeventi(
    id_utente_cercato BIGINT,
    nome_utente_cercato CHARACTER VARYING,
    titolo_cercato CHARACTER VARYING,
    descrizione_cercata TEXT,
    luogo_cercato CHARACTER VARYING,
    data_inferiore_compresa_cercata TIMESTAMP WITH TIME ZONE,
    data_superiore_esclusa_cercata TIMESTAMP WITH TIME ZONE,
    stato_cercato CHARACTER VARYING,
    ordine CHARACTER VARYING, -- 'ASC' oppure 'DESC'
    numero_righe BIGINT
)
    RETURNS TABLE (
                      id_evento BIGINT,
                      id_utente BIGINT,
                      nome_utente CHARACTER VARYING,
                      titolo_evento CHARACTER VARYING,
                      descrizione_evento TEXT,
                      luogo_evento CHARACTER VARYING,
                      data_evento TIMESTAMP WITH TIME ZONE,
                      stato_evento CHARACTER VARYING
                  )
    LANGUAGE PLPGSQL
AS
$$
BEGIN
    IF ordine = 'ASC' THEN
        RETURN QUERY
            SELECT
                eventi.id,
                eventi.id_utente,
                utenti.nome,
                eventi.titolo,
                eventi.descrizione,
                eventi.luogo,
                eventi.data,
                eventi.stato
            FROM
                eventi
                    INNER JOIN utenti ON eventi.id_utente = utenti.id
            WHERE 1=1
              AND (
                    (id_utente_cercato IS NULL) OR
                    (eventi.id_utente = id_utente_cercato)
                )
              AND (
                    (nome_utente_cercato IS NULL) OR
                    (utenti.nome = nome_utente_cercato AND eventi.id_utente = utenti.id)
                )
              AND (
                    (titolo_cercato IS NULL) OR
                    (LOWER(eventi.titolo) LIKE LOWER(CONCAT('%',titolo_cercato,'%')))
                )
              AND (
                    (descrizione_cercata IS NULL) OR
                    (LOWER(eventi.descrizione) LIKE LOWER(CONCAT('%',descrizione_cercata,'%')))
                )
              AND (
                    (luogo_cercato IS NULL) OR
                    (LOWER(eventi.luogo) LIKE LOWER(CONCAT('%',luogo_cercato,'%')))
                )
              AND (
                    (data_inferiore_compresa_cercata IS NULL) OR
                    (eventi.data >= data_inferiore_compresa_cercata)
                )
              AND (
                    (data_superiore_esclusa_cercata IS NULL) OR
                    (eventi.data < data_superiore_esclusa_cercata )
                )
              AND (
                    (stato_cercato IS NULL) OR
                    (eventi.stato = stato_cercato)
                )
            ORDER BY eventi.data ASC
            LIMIT numero_righe;
    ELSIF ordine = 'DESC' THEN
        RETURN QUERY
            SELECT
                eventi.id,
                eventi.id_utente,
                utenti.nome, -- questa colonna viene importata dalla tabella degli utenti
                eventi.titolo,
                eventi.descrizione,
                eventi.luogo,
                eventi.data,
                eventi.stato
            FROM
                eventi
                    INNER JOIN utenti ON eventi.id_utente = utenti.id
            WHERE 1=1
              AND (
                    (id_utente_cercato IS NULL) OR
                    (eventi.id_utente = id_utente_cercato)
                )
              AND (
                    (nome_utente_cercato IS NULL) OR
                    (utenti.nome = nome_utente_cercato AND eventi.id_utente = utenti.id)
                )
              AND (
                    (titolo_cercato IS NULL) OR
                    (LOWER(eventi.titolo) LIKE LOWER(CONCAT('%',titolo_cercato,'%')))
                )
              AND (
                    (descrizione_cercata IS NULL) OR
                    (LOWER(eventi.descrizione) LIKE LOWER(CONCAT('%',descrizione_cercata,'%')))
                )
              AND (
                    (luogo_cercato IS NULL) OR
                    (LOWER(eventi.luogo) LIKE LOWER(CONCAT('%',luogo_cercato,'%')))
                )
              AND (
                    (data_inferiore_compresa_cercata IS NULL) OR
                    (eventi.data >= data_inferiore_compresa_cercata)
                )
              AND (
                    (data_superiore_esclusa_cercata IS NULL) OR
                    (eventi.data < data_superiore_esclusa_cercata )
                )
              AND (
                    (stato_cercato IS NULL) OR
                    (eventi.stato = stato_cercato)
                )
            ORDER BY eventi.data DESC
            LIMIT numero_righe;
    ELSE
        null;
    END IF;
END;
$$;
SELECT * FROM cercaeventi(
        6,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        'DESC',
        5
    );

10 Risposte

  • andbin
    andbin
    Super Famoso
    Iscritto da
    ott, 2013
    Messaggi:
    6270

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    iBaffiPro ha scritto:

    Ho una query SELECT leggermente più articolata del solito che ho già testato sul query tools di PostgreSQL e che funziona alla perfezione. Vorrei implementare questa query dentro la mia classe @Repository. Il metodo che ho scritto restituisce sempre null e non so dove sia il problema. Il mio oggetto RowMapper a differenza di quelli che ho scritto in precedenza non si riferisce ad una reale tabella del database perché nella SELECT c’è anche una INNER JOIN. Non vorrei fosse questo il problema. Ho stampato la query prodotta dal metodo con i punti interrogativi nella console, poi ho copiato le stringe sul query tools dell’IDE, infine sostituito i ‘?‘ con i valori di mio interesse e tutto funziona a meraviglia. Non so se sbaglio a scrivere l’oggetto RowMapper oppure l’oggetto Repository.
    Qui purtroppo devo proprio "tirarti le orecchie", mi spiace.

    Punto 1) Ora ti restituisce null perché presumo lanci eccezione e tu hai scritto
            }catch (Exception e){
            return null;
        }
    Ovvero, non stai capendo cosa succede. Quindi: LOGGA sempre da qualche parte i problemi/eccezioni e verifica bene quale eccezione è e cosa dice. Presumo sia qualcosa tipo "sintassi errata" o simile

    Punto 2) Questo non c'entra tanto solo con JdbcTemplate, vale in generale, che sia con JDBC nudo e crudo, JPA, JdbcTemplate, Spring Data JPA, ecc...
    Quando usi una API per fare una query, con essa devi eseguire UNO statement per volta, non due, tre ecc.. separati da ";".
    JDBC in generale (che poi è la base per tutte le altre API) non funziona così.

    Punto 3) Detto molto in generale, la applicazione NON dovrebbe mai creare table, sequence, function, procedure, ecc... Tutto questo va creato "una tantum" o comunque con opportuni script di update "incrementale" a livello più "amministrativo".
    Poi banalmente è anche una questione di performance. Se quel cercaEventi venisse invocato 100 volte al secondo, tu ogni secondo ricrei 100 volte la funzione. E ti pare efficiente?? E a parte il fatto che comunque bisognerebbe pure vedere, da documentazione DB, se la creazione è "atomica", se non ci sono altre questioni di concorrenza, ecc...
    Insomma, per dirlo in modo semplice: di norma NON SI FANNO queste cose.

    Punto 4) Il codice della function effettivamente è lungo ma comunque anche altre query potrebbero essere lunghe, non è quello il punto. Con JdbcTemplate devi passare una stringa, che quindi solitamente è scritta "letterale" nel sorgente. E quindi un inconveniente è proprio l'aspetto "noioso" di dover scrivere un SQL in stringa literal dentro un sorgente Java.
    Ecco, tu l'hai fatto proprio nel modo peggiore, con ben 141 append() !! (sì li ho contati con il count di Notepad++).

    Ci sarebbero anche altre cose non particolarmente buone (ma minori) nel tuo codice ...... ma non voglio infierire .....
  • iBaffiPro
    iBaffiPro
    Utente Attivo
    Iscritto da
    feb, 2021
    Messaggi:
    398

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    Grazie per la risposta.
    L'eccezione è la seguente: 
    
SQL state [25006]; error code [0]; ERRORE: non è possibile eseguire CREATE FUNCTION in una transazione a sola lettura; nested exception is org.postgresql.util.PSQLException: ERRORE: non è possibile eseguire CREATE FUNCTION in una transazione a sola lettura
    Si la tirata d'orecchie me la merito.
    Se non posso lanciare query così lunghe allora come risolvo il mio problema? Qual è l’approccio corretto?
    Io desidero unire 2 tabelle e sulla tabella creata poter fare delle ricerche su uno o più campi.
    Non capisco quali altre opzioni avrei per salvare la stringa sql nel metodo java.
    Parlami pure anche delle cose minori.
  • andbin
    andbin
    Super Famoso
    Iscritto da
    ott, 2013
    Messaggi:
    6270

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    iBaffiPro ha scritto:

    SQL state [25006]; error code [0]; ERRORE: non è possibile eseguire CREATE FUNCTION in una transazione a sola lettura
    A me pare molto chiaro ..... hai messo @Transactional(readOnly=true) in cui si presuppone di fare operazioni di "sola lettura" nella transazione, cioè non modifiche. Ma tu modifiche ne fai ... crei una function!! Questa è una modifica!

    iBaffiPro ha scritto:

    Se non posso lanciare query così lunghe allora come risolvo il mio problema? Qual è l’approccio corretto?
    Ma il problema non è tanto la lunghezza (né se ci sono 1, 2 o 10 tabelle in join). Certo è meno pratico mettere del SQL in stringhe letterali ma la vera problematica è quando le condizioni di filtro nella WHERE sono "dinamiche", come nel tuo caso.

    Se si usa JDBC nudo e crudo, è un po' scomodo fare query con filtri dinamici perché con PreparedStatement si devono specificare gli indici dei parametri, che devono ovviamente corrispondere a ciascun "?" nel sql. Questa è appunto la parte noiosa: tenere "allineati" i ? con gli indici.

    Con JdbcTemplate è un po' più comodo perché ci sono query con firma es.

    query(String sql, RowCallbackHandler rch, Object... args)

    Che è un varargs, quindi si può partire con un List e poi ottenere l'array dei parametri.

    Molto abbozzato (ma comprensibile):
            List<Object> params = new ArrayList<>();

        StringBuilder sql = new StringBuilder(
                  "SELECT ww, xx, yy, zz \n"
                + "FROM aaaaaaaaa \n"
                + "JOIN bbbbbb ON ........ \n"
                + "JOIN cccccccc ON ........ \n"
                + "WHERE 1=1 \n");
        
        if (nome != null) {
            sql.append("AND colonna_nome = ? \n");
            params.add(nome);
        }

        if (cognome != null) {
            sql.append("AND colonna_cognome = ? \n");
            params.add(cognome);
        }
        
        List<Xyz> lista = jdbcTemplate.query(sql.toString(), tuoRowMapper, params.toArray());
    Ti pare complicato o poco leggibile?

    iBaffiPro ha scritto:

    Parlami pure anche delle cose minori.
    Brevemente:

    1) 
                if(cercaEventi==null) {
                return null;
            }
    è discutibile.
    L'oggetto CercaEventi sarebbe bene che ci sia sempre. Quindi semmai meglio lanciare un NullPointerException con messaggio "parlante". Detto in altro modo: se dovesse risultare null, sarebbe un sintomo di un baco (tuo) del codice. E da sistemare, ovviamente.

    2) Non capisco l'utilità di 
                if(cercaEventi.getId_utente()==null || cercaEventi.getId_utente()<=0) {
                cercaEventi.setId_utente(null);
            }
    Se id_utente è null ... risetti null. Che senso ha? Forse intendevi: se id_utente è NON null E <= 0 allora setta null. Ma si scrive diversamente, con && non con ||
    Idem gli altri simili.

    3) Troppi underscore "_" nei nomi di variabili e metodi.
    Nota: il "_" è un carattere legale negli identificatori ma di norma non si usa ...
  • iBaffiPro
    iBaffiPro
    Utente Attivo
    Iscritto da
    feb, 2021
    Messaggi:
    398

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    Sempre grazie infinite.
    C’è una cosa che non ti dovrei comunicare altrimenti oltre alla tirata di orecchie rischio anche altro… va be dai te la dico ugualmente tanto ormai la figuraccia l’ho fatta… tirata più, tirata meno…
    Il metodo che mi hai consigliato era quello che volevo usare inizialmente poi mi sembrava troppo complicato e sono approdato ad una seconda soluzione ancora peggiore che è quella che hai visto.
    Tra il resto ho scoperto che c’è una funziona apposita di jdbcTemplate per mandare in esecuzione la mia query. L’ho testata ora e funziona alla perfezione. Ovviamente quel @Transactional(readOnly=true) va rimosso.
    1) Usando il tuo approccio a livello di consumo di risorse di sistema si risparmia il 5%, 50% oppure il 95%?
    Quando cercaEventi è null significa che c’è un errore nel codice, questo sì, ma se è null, in produzione non ha senso fare nulla e conviene fornire una tabella vuota alla pagina di ricerca, tanto alla fine si finisce comunque a quella condizione. Effettivamente ha poco senso è si può togliere, grazie.
    In merito all’ultimo punto di faccio una domanda molto più importante. Nel form di ricerca quando l’utente scrive:
    ricerca per id_utente = ‘-52’
    ricerca per titolo = ‘farfalla’
    ecc…
    2) Se esistono 3 record con il nome ‘farfalla’ conviene fornire le notizie che parlano della farfalla infischiandosene del fatto che l’id dell’utente non esiste oppure non fornire nulla? Capisco che questo aspetto sia una scelta del programmatore ma tu come visitatore cosa preferiresti? Meglio un form più restrittivo oppure più severo?
    Tolgo tutti questi andescore, grazie.
  • andbin
    andbin
    Super Famoso
    Iscritto da
    ott, 2013
    Messaggi:
    6270

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    iBaffiPro ha scritto:

    1) Usando il tuo approccio a livello di consumo di risorse di sistema si risparmia il 5%, 50% oppure il 95%?
    Non saprei quantificare bene in questo momento ... ma sicuramente ben più efficiente che ri-creare una function ogni volta!

    iBaffiPro ha scritto:

    Nel form di ricerca quando l’utente scrive:
    ricerca per id_utente = ‘-52’
    ricerca per titolo = ‘farfalla’
    ecc…
    2) Se esistono 3 record con il nome ‘farfalla’ conviene fornire le notizie che parlano della farfalla infischiandosene del fatto che l’id dell’utente non esiste oppure non fornire nulla?
    Questa è la classica situazione dei filtri in "conflitto" tra di loro. Ci sono vari modi per gestire questo:

    a) lato client se id viene inserito e si inserisce anche su altri campi, si può segnalare l'errore all'utente (e magari NON permettere nemmeno il submit)

    oppure
    b) validare lato server e fallire la request rimandando al form

    oppure
    c) accettare l'input e applicare comunque i filtri. Che come hai visto sono generalmente sempre in AND se presenti.

    Quindi se l'utente chiede id=100 e titolo=Pippo

    se su db ci fosse
    100 Pippo
    101 Pippo
    102 Pippo

    ne becca solo uno (il primo) che soddisfa entrambe le condizioni in AND.

    se su db ci fosse
    123 Pippo
    124 Pippo
    125 Pippo

    non ne becca nessuno con Id=100.
  • iBaffiPro
    iBaffiPro
    Utente Attivo
    Iscritto da
    feb, 2021
    Messaggi:
    398

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    Grazie mille
    Ho confrontato i due approcci ed effettivamente l'uso della funzione dal punto di vista prestazionale è fortemente svantaggioso.
    Il tuo metodo è bel 1000 % circa più veloce del mio.
    Ecco l'output del programma:
    50 iterazioni
    Tempo impiegato usando una funzione sql: 6 secondi.
    Tempo impiegato usando una semplice query: 1 secondi.
    Il metodo con la query articolata in java è 500.0 % più veloce del metodo che utilizza la funzione sql.
    100 iterazioni
    Tempo impiegato usando una funzione sql: 11 secondi.
    Tempo impiegato usando una semplice query: 1 secondi.
    Il metodo con la query articolata in java è 1000.0 % più veloce del metodo che utilizza la funzione sql.
    150 iterazioni
    Tempo impiegato usando una funzione sql: 17 secondi.
    Tempo impiegato usando una semplice query: 2 secondi.
    Il metodo con la query articolata in java è 700.0 % più veloce del metodo che utilizza la funzione sql.
    200 iterazioni
    Tempo impiegato usando una funzione sql: 23 secondi.
    Tempo impiegato usando una semplice query: 2 secondi.
    Il metodo con la query articolata in java è 1000.0 % più veloce del metodo che utilizza la funzione sql.
    250 iterazioni
    Tempo impiegato usando una funzione sql: 27 secondi.
    Tempo impiegato usando una semplice query: 3 secondi.
    Il metodo con la query articolata in java è 800.0 % più veloce del metodo che utilizza la funzione sql.
    300 iterazioni
    Tempo impiegato usando una funzione sql: 33 secondi.
    Tempo impiegato usando una semplice query: 3 secondi.
    Il metodo con la query articolata in java è 1000.0 % più veloce del metodo che utilizza la funzione sql.
    350 iterazioni
    Tempo impiegato usando una funzione sql: 38 secondi.
    Tempo impiegato usando una semplice query: 3 secondi.
    Il metodo con la query articolata in java è 1100.0 % più veloce del metodo che utilizza la funzione sql.
    400 iterazioni
    Tempo impiegato usando una funzione sql: 44 secondi.
    Tempo impiegato usando una semplice query: 4 secondi.
    Il metodo con la query articolata in java è 1000.0 % più veloce del metodo che utilizza la funzione sql.
    450 iterazioni
    Tempo impiegato usando una funzione sql: 49 secondi.
    Tempo impiegato usando una semplice query: 4 secondi.
    Il metodo con la query articolata in java è 1100.0 % più veloce del metodo che utilizza la funzione sql.
    500 iterazioni
    Tempo impiegato usando una funzione sql: 57 secondi.
    Tempo impiegato usando una semplice query: 5 secondi.
    Il metodo con la query articolata in java è 1000.0 % più veloce del metodo che utilizza la funzione sql.
  • andbin
    andbin
    Super Famoso
    Iscritto da
    ott, 2013
    Messaggi:
    6270

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    iBaffiPro ha scritto:

    Grazie mille
    Ho confrontato i due approcci ed effettivamente l'uso della funzione dal punto di vista prestazionale è fortemente svantaggioso.
    L'uso della function NON è di per se sbagliato o pessimo.
    È sbagliato crearla da codice e soprattutto RI-crearla ad ogni singola richiesta!

    Quindi visto che sei in tema di benchmark, prova con la function su db (la crei tu a mano "una tantum") e da codice fai solo la select "secca" sulla function.

    Dovrebbe performare in maniera similare alla creazione della query dinamica da codice. Insomma, non dovrebbe discostarsi molto.
    E anzi potresti avere qualche (bella) sorpresa, perché la query che viene passata al driver è più semplice.
  • iBaffiPro
    iBaffiPro
    Utente Attivo
    Iscritto da
    feb, 2021
    Messaggi:
    398

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    Proverei volentieri ma se faccio in questo modo: 
    spring.sql.init.schema-locations = classpath:drop.sql, classpath:schema.sql, classpath:funzioni.sql
    ottengo questo errore: 
    org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'configurazioneSpringSecurity': Unsatisfied dependency expressed through field 'livelliDeiRuoli'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'livelliDeiRuoli': Unsatisfied dependency expressed through field 'utenteRepository'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'utenteRepository': Unsatisfied dependency expressed through field 'jdbcTemplate'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [funzioni.sql]: CREATE OR REPLACE FUNCTION cercaeventi(... ecc... ecc... tutto il contenuto della funzione...
  • iBaffiPro
    iBaffiPro
    Utente Attivo
    Iscritto da
    feb, 2021
    Messaggi:
    398

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    Se aggiungo questo codice: 
    
    @Value("classpath:schema.sql")
    private Resource schemaScript;

    @Value("classpath:funzioni.sql")
    private Resource dataScript;

    @Bean
    public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
        final DataSourceInitializer initializer = new DataSourceInitializer();
        initializer.setDataSource(dataSource);
        initializer.setDatabasePopulator(databasePopulator());
        return initializer;
    }

    private DatabasePopulator databasePopulator() {
        final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
        populator.addScript((org.springframework.core.io.Resource) schemaScript);
        populator.addScript((org.springframework.core.io.Resource) dataScript);
        return populator;
    }
    Fonte: https://stackoverflow.com/questions/27859596/how-to-load-a-sql-function-on-startup-with-spring
    ottengo questo errore: 
    
org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'configurazioneSpringSecurity': Unsatisfied dependency expressed through field 'schemaScript'; nested exception is org.springframework.beans.ConversionNotSupportedException: Failed to convert value of type 'java.lang.String' to required type 'javax.annotation.Resource'; nested exception is java.lang.IllegalStateException: Cannot convert value of type 'java.lang.String' to required type 'javax.annotation.Resource': no matching editors or conversion strategy found
    Se non uso org.springframework.core.io.Resource non riesco neppure a compilare.
    Se aggiungo la funzione a data.sql ottengo lo stesso errore che ottengo aggiungengo la funzione in schema.sql.
  • iBaffiPro
    iBaffiPro
    Utente Attivo
    Iscritto da
    feb, 2021
    Messaggi:
    398

    Re: Come si lancia una query SELECT con jdbcTemplate leggermente più complessa del solito?

    Altra cosa che non capisco è perché quando uso il query tools, dopo qualche modifica, il programma si blocca e mi dice di aggiungere questo pezzo: 
    
DROP FUNCTION cercaeventi(bigint,character varying,character varying,text,character varying,timestamp with time zone,timestamp with time zone,character varying,character varying,bigint);
    Dopo aver lanciato il DROP FUNCTION se lo rimuovo e lascio solo il CREATE OR REPLACE FUNCTION cercaeventi(...) tutto ritorna a funzionare. E' strano che accada questo perché il CREATE OR REPLACE contiene anche REPLACE quindi il DROP a cosa serve?
Devi accedere o registrarti per scrivere nel forum
10 risposte