Creare un sito dinamico in cui gli utenti possono registrarsi per usufruire di un servizio

iBaffiPro ha scritto:
CREATE TABLE id_utenti_id_ruoli (
id BIGSERIAL PRIMARY KEY,
id_utente BIGSERIAL NOT NULL,
id_ruolo BIGSERIAL NOT NULL,
FOREIGN KEY (id_utente) REFERENCES utenti (id) ON DELETE CASCADE,
FOREIGN KEY (id_ruolo) REFERENCES ruoli (id) ON DELETE CASCADE,
UNIQUE (id_utente, id_ruolo)
);

Intanto partiamo da questa tabella che ha diverse cose non buone.

Innanzitutto il nome. Quando si deve gestire una relazione molti-a-molti, si fa una junction table (detta anche join table) che ha i due id delle due tabelle in relazione molti-a-molti. La tabella tipicamente si denomina unendo banalmente i due nomi delle due tabelle, tipo utenti_ruoli. E' scontato che contiene degli id, non c'è da mettere "id" nel nome!!

Poi mettere un id generato da sequence, non è sbagliato di per sé ma ... in una tabella di questo tipo di norma non si mette! Di norma i due id formano la primary key.

E poi attenzione, forse non hai compreso bene BIGSERIAL. In PostgreSQL il tipo BIGSERIAL fa creare una sequence!!
Vedi: https://www.postgresql.org/docs/13/datatype-numeric.html#DATATYPE-SERIAL

E nella join table, id_utente e id_ruolo NON devono avere una sequence. La sequence è già per ciascuna delle singole tabelle utenti e ruoli. La join table deve solo "referenziare" gli id ... non deve "creare" alcun id.

Quindi, in sostanza, la tua join table dovrebbe risultare tipo (non provato ma non dovrei aver scritto eresie):

CREATE TABLE utenti_ruoli (
    id_utente BIGINT NOT NULL,
    id_ruolo BIGINT NOT NULL,
    CONSTRAINT utenti_ruoli_fk_utente FOREIGN KEY (id_utente) REFERENCES utenti (id) ON DELETE CASCADE,
    CONSTRAINT utenti_ruoli_fk_ruolo FOREIGN KEY (id_ruolo) REFERENCES ruoli (id) ON DELETE CASCADE,
    CONSTRAINT utenti_ruoli_pk PRIMARY KEY (id_utente, id_ruolo)
);

L'uso di CONSTRAINT è solo per esplicitare un nome che sia "parlante" (e non autogenerato con nome "strano").
E nota che i campi sono BIGINT. Il BIGSERIAL è = BIGINT + creazione sequence usata per default dalla colonna.

Grazie mille!
Ho corretto lo schema:


CREATE TABLE utenti (
    id BIGSERIAL NOT NULL,
    nome VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    abilitato BOOLEAN NOT NULL,
    CONSTRAINT utenti_pk PRIMARY KEY(id),
    CONSTRAINT utenti_uk UNIQUE (nome)
);

CREATE TABLE ruoli (
    id BIGSERIAL NOT NULL,
    ruolo VARCHAR(100) NOT NULL,
    CONSTRAINT ruoli_pk PRIMARY KEY(id),
    CONSTRAINT ruoli_uk UNIQUE (ruolo)
);

CREATE TABLE utenti_ruoli (
    id_utente BIGINT NOT NULL,
    id_ruolo BIGINT NOT NULL,
    CONSTRAINT utenti_ruoli_fk_utente FOREIGN KEY (id_utente) REFERENCES utenti (id) ON DELETE CASCADE,
    CONSTRAINT utenti_ruoli_fk_ruolo FOREIGN KEY (id_ruolo) REFERENCES ruoli (id) ON DELETE CASCADE,
    CONSTRAINT utenti_ruoli_pk PRIMARY KEY(id_utente, id_ruolo)
);

CREATE TABLE persistent_logins (
    username VARCHAR(100) NOT NULL,
    series VARCHAR(64) PRIMARY KEY,
    token VARCHAR(64) NOT NULL,
    last_used TIMESTAMP NOT NULL
);

L'elenco dei dati:


BEGIN TRANSACTION;

INSERT INTO utenti (nome, password, abilitato)
VALUES ('pro1', '$2a$10$PrI5Gk9L.tSZiW9FXhTS8O8Mz9E97k2FZbFvGFFaSsiTUIl.TCrFu', true);

INSERT INTO utenti (nome, password, abilitato)
VALUES ('amm1', '$2a$10$PrI5Gk9L.tSZiW9FXhTS8O8Mz9E97k2FZbFvGFFaSsiTUIl.TCrFu', true);

INSERT INTO ruoli (ruolo) VALUES ('ROLE_proprietario');
INSERT INTO ruoli (ruolo) VALUES ('ROLE_amministratore');
INSERT INTO ruoli (ruolo) VALUES ('ROLE_gestore');
INSERT INTO ruoli (ruolo) VALUES ('ROLE_organizzatore');
INSERT INTO ruoli (ruolo) VALUES ('ROLE_giocatore');

INSERT INTO utenti_ruoli (id_utente, id_ruolo) VALUES (1, 1);
INSERT INTO utenti_ruoli (id_utente, id_ruolo) VALUES (1, 2);
INSERT INTO utenti_ruoli (id_utente, id_ruolo) VALUES (1, 3);
INSERT INTO utenti_ruoli (id_utente, id_ruolo) VALUES (1, 4);
INSERT INTO utenti_ruoli (id_utente, id_ruolo) VALUES (2, 2);
INSERT INTO utenti_ruoli (id_utente, id_ruolo) VALUES (2, 3);
INSERT INTO utenti_ruoli (id_utente, id_ruolo) VALUES (2, 4);

COMMIT;

e le classi:

package it.webapp.script.database_jdbc;

public class Ruolo {
    Long id;
    String ruolo;

    public Ruolo() {
    }

    public Ruolo(Long id, String ruolo) {
        this.id = id;
        this.ruolo = ruolo;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getRuolo() {
        return ruolo;
    }

    public void setRuolo(String ruolo) {
        this.ruolo = ruolo;
    }
}

package it.webapp.script.database_jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

@Repository
public class RuoloRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional(readOnly=true)
    public List<Ruolo> trovaTutti() {
        return jdbcTemplate.query("SELECT * FROM ruoli;", new RuoloRowMapper());
    }

    @Transactional(readOnly=true)
    public Ruolo trovaRuolo(Long id) {
        String sql = "SELECT * FROM ruoli WHERE id=?;";
        return jdbcTemplate.queryForObject(sql, new RuoloRowMapper(), new Object[]{id});
    }

    @Transactional(readOnly=true)
    public Ruolo trovaRuolo(String ruolo) {
        String sql = "SELECT * FROM ruoli WHERE ruolo=?;";
        return jdbcTemplate.queryForObject(sql, new RuoloRowMapper(), new Object[]{ruolo});
    }

    @Transactional(readOnly=true)
    public List<String> trovaRuoliUtente(Long id) {
        String sql;
        sql = "SELECT r.ruolo FROM utenti_ruoli ur, ruoli r WHERE ur.id_ruolo = r.id AND ur.id_utente = ?;";
        Object[] parametri = new Object[] {id};
        List<String> ruoli = jdbcTemplate.queryForList(sql, String.class, parametri);
        return ruoli;
    }

    @Transactional(readOnly=true)
    public List<Long> trovaIdRuoliUtente(Long id) {
        String sql;
        sql = "SELECT r.id FROM utenti_ruoli ur, ruoli r WHERE ur.id_ruolo = r.id AND ur.id_utente = ?;";
        Object[] parametri = new Object[] {id};
        List<Long> ruoli = jdbcTemplate.queryForList(sql, Long.class, parametri);
        return ruoli;
    }

    public Ruolo inserisciRuolo(Ruolo ruolo) {
        String sql = "INSERT INTO ruoli (ruolo) VALUES (?);";
        KeyHolder holder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection)
                    throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, new String[] { "id" });
                ps.setString(1, ruolo.getRuolo());
                return ps;
            }
        }, holder);
        // Recupero dell'id inserito dal database al nuovo record.
        // getkey() preleva l'id perché in prepareStatement() c'è il parametro 'new String[] { "id" }'.
        Long nuovoIdRuolo = holder.getKey().longValue();
        ruolo.setId(nuovoIdRuolo);
        return ruolo;
    }

    public Long cancellaRuolo(Long id){
        String sql = "DELETE FROM ruoli WHERE id = ?;";
        Object[] argomenti = new Object[] {id};
        // jdbcTemplate.update() fornisce il numero di id modificati, non l'id modificato.
        return Long.valueOf(jdbcTemplate.update(sql, argomenti));
    }

    public Long modificaRuolo(Ruolo ruolo){
        String sql = "UPDATE ruoli SET ruolo = ? WHERE id = ?;";
        Object[] parametri = {ruolo.getRuolo(), ruolo.getId()};
        int[] tipi = {Types.VARCHAR, Types.BIGINT};
        // Si forniscono in uscita il numero di righe del DBMS aggiornate.
        return Long.valueOf(jdbcTemplate.update(sql, parametri, tipi));
    }

}

package it.webapp.script.database_jdbc;

import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;

public class RuoloRowMapper implements RowMapper<Ruolo> {

    @Override
    public Ruolo mapRow(ResultSet rs, int rowNum) throws SQLException {
        Ruolo ruolo = new Ruolo();
        ruolo.setId(rs.getLong("id"));
        ruolo.setRuolo(rs.getString("ruolo"));
        return ruolo;
    }
}

package it.webapp.script.database_jdbc;

public class Utente {
    private Long id;
    private String nome;
    private String password;
    private Boolean abilitato;

    public Utente() {
    }

    public Utente(Long id, String nome, String password, Boolean abilitato) {
        this.id = id;
        this.nome = nome;
        this.password = password;
        this.abilitato = abilitato;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getNome() {
        return nome;
    }

    public void setNome(String nome) {
        this.nome = nome;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Boolean getAbilitato() {
        return abilitato;
    }

    public void setAbilitato(Boolean abilitato) {
        this.abilitato = abilitato;
    }

    // Il metodo toString() serve per stampare in console il nome e la password con il codice System.out.println(X);
    @Override
    public String toString() {
        return this.nome + "/" + this.password;
    }

}

package it.webapp.script.database_jdbc;

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class UtenteRowMapper implements RowMapper<Utente> {

    @Override
    public Utente mapRow(ResultSet rs, int rowNum) throws SQLException {
        Utente utente = new Utente();
        utente.setId(rs.getLong("id"));
        utente.setNome(rs.getString("nome"));
        utente.setPassword(rs.getString("password"));
        utente.setAbilitato(rs.getBoolean("abilitato"));
        return utente;
    }
}

package it.webapp.script.database_jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

@Repository
public class UtenteRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional(readOnly=true)
    public List<Utente> trovaTutti() {
        return jdbcTemplate.query("SELECT * FROM utenti;", new UtenteRowMapper());
    }

    @Transactional(readOnly=true)
    public Utente trovaUtente(Long id) {
        String sql = "SELECT * FROM utenti WHERE id=?;";
        return jdbcTemplate.queryForObject(sql, new UtenteRowMapper(), new Object[]{id});
    }

    @Transactional(readOnly=true)
    public Utente trovaUtente(String nome) {
        String sql = "SELECT * FROM utenti WHERE nome=?;";
        return jdbcTemplate.queryForObject(sql, new UtenteRowMapper(), new Object[]{nome});
    }

    public Utente inserisciUtente(Utente utente) {
        String sql = "INSERT INTO utenti (nome, password, abilitato) VALUES (?, ?, ?);";
        KeyHolder holder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection)
                    throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, new String[] { "id" });
                ps.setString(1, utente.getNome());
                ps.setString(2, utente.getPassword());
                ps.setBoolean(3, utente.getAbilitato());
                return ps;
            }
        }, holder);
        // Recupero dell'id inserito dal database al nuovo record.
        // getkey() preleva l'id perché in prepareStatement() c'è il parametro 'new String[] { "id" }'.
        Long nuovoIdUtente = holder.getKey().longValue();
        utente.setId(nuovoIdUtente);
        return utente;
    }

    public Long cancellaUtente(Long id){
        String sql = "DELETE FROM utenti WHERE id = ?;";
        Object[] parametri = new Object[] {id};
        int[] tipi = {Types.BIGINT};
        Long idCancellato = Long.valueOf(jdbcTemplate.update(sql, parametri, tipi));
        return idCancellato;
    }

    public Long modificaUtente(Utente utente){
        String sql = "UPDATE utenti SET nome = ?, password = ?, abilitato = ? WHERE id = ?;";
        Object[] parametri = {utente.getNome(), utente.getPassword(), utente.getAbilitato(), utente.getId()};
        int[] tipi = {Types.VARCHAR, Types.VARCHAR, Types.BOOLEAN, Types.BIGINT};
        // Si forniscono in uscita il numero di righe del DBMS aggiornate.
        return Long.valueOf(jdbcTemplate.update(sql, parametri, tipi));
    }

}

package it.webapp.script.database_jdbc;

public class UtenteRuolo {
    Long id_utente;
    Long id_ruolo;

    public UtenteRuolo() {
    }

    public UtenteRuolo(Long id_utente, Long id_ruolo) {
        this.id_utente = id_utente;
        this.id_ruolo = id_ruolo;
    }

    public Long getId_utente() {
        return id_utente;
    }

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

    public Long getId_ruolo() {
        return id_ruolo;
    }

    public void setId_ruolo(Long id_ruolo) {
        this.id_ruolo = id_ruolo;
    }
}

package it.webapp.script.database_jdbc;

import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;

public class UtenteRuoloRowMapper implements RowMapper<UtenteRuolo> {

    @Override
    public UtenteRuolo mapRow(ResultSet rs, int rowNum) throws SQLException {
        UtenteRuolo utenteRuolo = new UtenteRuolo();
        utenteRuolo.setId_utente(rs.getLong("id_utente"));
        utenteRuolo.setId_ruolo(rs.getLong("id_ruolo"));
        return utenteRuolo;
    }
}

package it.webapp.script.database_jdbc;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;

@Repository
public class UtenteRuoloRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public UtenteRuolo inserisciRuoloAdUtente(UtenteRuolo utenteRuolo) {
        String sql = "INSERT INTO utenti_ruoli (id_utente, id_ruolo) VALUES (?, ?);";
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection)
                    throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setLong(1, utenteRuolo.id_utente);
                ps.setLong(2, utenteRuolo.id_ruolo);
                return ps;
            }
        });
        return utenteRuolo;
    }

    public Long cancellaRuoloAdUtente(UtenteRuolo utenteRuolo){
        String sql = "DELETE FROM utenti_ruoli WHERE id_utente = ? AND id_ruolo = ?;";
        Object[] parametri = {utenteRuolo.getId_utente(), utenteRuolo.getId_ruolo()};
        int[] tipi = {Types.BIGINT, Types.BIGINT};
        // Si forniscono in uscita il numero di righe del DBMS aggiornate.
        return Long.valueOf(jdbcTemplate.update(sql, parametri, tipi));
    }

}

Può andare?
C'è un sistema più intelligente per assegnare e rimuovere ruoli ad un utente?

32 Risposte - Pagina 3

Re: Creare un sito dinamico in cui gli utenti possono registrarsi per usufruire di un servizio

Re: Creare un sito dinamico in cui gli utenti possono registrarsi per usufruire di un servizio

Re: Creare un sito dinamico in cui gli utenti possono registrarsi per usufruire di un servizio