CODEWITHSUNDEEP
javamysqlspring-bootjdbc
Neo
Neo

Reputation: 512

Spring boot MySQL request return null when it shouldn't

I'm making a rest API for an App I'm creating, I have a MySQL database and I'm using Springboot. I coded this

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

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

public class MySQLInfosGateway implements InfosGateway {
    private NamedParameterJdbcTemplate jdbcTemplate;

    public MySQLInfosGateway(NamedParameterJdbcTemplate jdbcTemplate){
        this.jdbcTemplate=jdbcTemplate;
    }

    @Override
    public Personne getInfos(String badge){
        var query = "select NOM, PRENOM from PERSONNEL where BADGE = "+badge;
        var result = jdbcTemplate.query(query, new ResultSetExtractor<Object>() {
            @Override
            public Object extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                resultSet.next();
                return new Personne(resultSet.getString("NOM"),resultSet.getString("PRENOM"));
            }
        });
        return (Personne) result;
    }
}

Here's my Personneclass :

package com.piroux.phoenixrhbackend.domain.entities;

public class Personne {
    private String nom;
    private String prenom;

    public Personne(String nom, String prenom){
        this.nom=nom;
        this.prenom=prenom;
    }

    public String getPrenom() {
        return prenom;
    }

    public String getNom() {
        return nom;
    }

    public void setPrenom(String prenom) {
        this.prenom = prenom;
    }

    public void setNom(String nom) {
        this.nom = nom;
    }
}

and I'm trying with the following request on Postman:

localhost:8080/fonction-recup-infos/nom-prenom?badge=50387

If I execute the request select NOM, PRENOM from PERSONNEL where BADGE = 50387

For you to know, BADGE is a unique String, so there's only one NOM and PRENOM for each BADGE.

It's my first time creating a REST API so if any information is missing please tell me

Upvotes: 0

Views: 727

Answers (2)

M. Deinum
M. Deinum

Reputation: 124622

A couple of things wrong with your code. First never use concatenation to create a query string based on user input. It is dangerous. Second I would suggest using the RowMapper instead of the ResultSetExtractor it is easier to use.

You are using getString("<column-name>") if your database doesn't expose the metadata this won't work and you have to use positional identifiers.

All in all I suggest you do this.

@Override
public Personne getInfos(String badge){
    var query = "select NOM, PRENOM from PERSONNEL where BADGE = :badge";
    var result = jdbcTemplate.queryForObject(query, Map.of("badge", badge), (rs, rowNum) -> 
    new Personne(rs.getString("NOM"), rs.getString("PRENOM"));        
    return result;
}

NOTE: You also might want to try nom and prenom as the column names, yuo are using MySQL which can be a bit picky about casing (depending on your configuration of MySQL and the platform you are running on).

Upvotes: 1

paranaaan
paranaaan

Reputation: 1735

Change this line from

 var query = "select NOM, PRENOM from PERSONNEL where BADGE = "+badge;

to

 var query = "select NOM, PRENOM from PERSONNEL where BADGE = '" + badge + "'";

It might be resolved.

Upvotes: 0

Related Questions