[RESOLVIDO] Spring-data-JDBC - Erro ao salvar Entidade no banco

SilvaRoberto · Junho 13, 2020, 9:30pm

Estou com um problema para salvar minhas entidades no banco.
Meu projeto está utilizando:

Spring-Boot,
Spring-Data-JDBC
MySQL.

Tenho uma classe abstrata que tem os campos em comum de todas minhas entidades do banco:

@NoArgsConstructor
@AllArgsConstructor
@Data
public abstract class EntityBase {

    @Id
    @Column(value = "id")
    private Long id;

    @Column(value = "status")
    private Character status = 'A';


    @CreatedDate
    @Column(value = "created")
    private Instant createdDate;


    @LastModifiedDate
    @Column(value = "updated")
    private Instant lastModifiedDate;
}

Com isso minhas classes entidades estendes dela, seguindo o exemplo:

@NoArgsConstructor
@AllArgsConstructor
@Data
@EqualsAndHashCode(callSuper = false)
@Table(value = "client")
public class Client extends EntityBase {

    @Column(value = "name")
    private String name;

    @Column(value = "rg")
    private String rg;

    @Column(value = "birthday")
    private LocalDate birthday;

    @Column(value = "telephone")
    private String telephone;

}

porém quando faço meu método save na classe Service:

@Override
public Client save(ClientRequest clientRequest) {
    Client clientBD = modelMapper.map(clientRequest, Client.class);
    clientBD.setRg(StringsUtil.onlyLettersAndNumbers(clientBD.getRg()));
    clientBD.setTelephone(StringsUtil.onlyNumbers(clientBD.getTelephone()));
    clientBD.setStatus('A');

    return this.clientRepository.save(clientBD);
}

public interface ClientRepository extends PagingAndSortingRepository<Client, Long> {

    Page<Client> findByStatus(Character status, Pageable pageable);
    Optional<Client> findByIdAndStatus(Long id, Character status);
}

Eu tenho a seguinte saída com o erro:

2020-06-13 18:03:45.302 DEBUG 9462 --- [nio-8082-exec-4] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [INSERT INTO `client` (`birthday`, `created`, `name`, `rg`, `status`, `telephone`, `updated`) VALUES (?, ?, ?, ?, ?, ?, ?)]
2020-06-13 18:03:45.303 TRACE 9462 --- [nio-8082-exec-4] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [Sat Jun 13 00:00:00 BRT 2020], value class [java.util.Date], SQL type unknown
2020-06-13 18:03:45.303 TRACE 9462 --- [nio-8082-exec-4] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [Sat Jun 13 18:03:45 BRT 2020], value class [java.util.Date], SQL type unknown
2020-06-13 18:03:45.304 TRACE 9462 --- [nio-8082-exec-4] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 3, parameter value [ALGUMA PESSOA DA SILVA], value class [java.lang.String], SQL type 12
2020-06-13 18:03:45.304 TRACE 9462 --- [nio-8082-exec-4] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 4, parameter value [000000000], value class [java.lang.String], SQL type 12
2020-06-13 18:03:45.304 TRACE 9462 --- [nio-8082-exec-4] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 5, parameter value [A], value class [java.lang.Character], SQL type unknown
2020-06-13 18:03:45.304 TRACE 9462 --- [nio-8082-exec-4] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 6, parameter value [11000000000], value class [java.lang.String], SQL type 12
2020-06-13 18:03:45.304 TRACE 9462 --- [nio-8082-exec-4] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 7, parameter value [Sat Jun 13 18:03:45 BRT 2020], value class [java.util.Date], SQL type unknown
2020-06-13 18:03:45.313 ERROR 9462 --- [nio-8082-exec-4] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/api] threw exception [Request processing failed; nested exception is org.springframework.data.relational.core.conversion.DbActionExecutionException: Failed to execute DbAction.InsertRoot(entity=Client(name=ALGUMA PESSOA DA SILVA, rg=000000000, birthday=2020-06-13, telephone=110000000000))] with root cause
java.sql.SQLException: Incorrect string value: '\xAC\xED\x00\x05sr...' for column 'status' at row 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.4.5.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar:na]
	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$1(JdbcTemplate.java:894) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:893) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:349) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:333) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.data.jdbc.core.convert.DefaultDataAccessStrategy.insert(DefaultDataAccessStrategy.java:125) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE]
	at org.springframework.data.jdbc.core.JdbcAggregateChangeExecutionContext.executeInsertRoot(JdbcAggregateChangeExecutionContext.java:93) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE]
	at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:66) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE]
	at org.springframework.data.jdbc.core.AggregateChangeExecutor.lambda$execute$0(AggregateChangeExecutor.java:50) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE]
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1540) ~[na:na]
	at org.springframework.data.relational.core.conversion.DefaultAggregateChange.forEachAction(DefaultAggregateChange.java:116) ~[spring-data-relational-2.0.0.RELEASE.jar:2.0.0.RELEASE]
	at org.springframework.data.jdbc.core.AggregateChangeExecutor.execute(AggregateChangeExecutor.java:50) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE]
	at org.springframework.data.jdbc.core.JdbcAggregateTemplate.store(JdbcAggregateTemplate.java:339) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE]
	at org.springframework.data.jdbc.core.JdbcAggregateTemplate.save(JdbcAggregateTemplate.java:149) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE]
	at org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.save(SimpleJdbcRepository.java:55) ~[spring-data-jdbc-2.0.0.RELEASE.jar:2.0.0.RELEASE]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
	at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
	at org.springframework.data.repository.core.support.ImplementationInvocationMetadata.invoke(ImplementationInvocationMetadata.java:72) ~[spring-data-commons-2.3.0.RELEASE.jar:2.3.0.RELEASE]
	at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:382) ~[spring-data-commons-2.3.0.RELEASE.jar:2.3.0.RELEASE]
	at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:205) ~[spring-data-commons-2.3.0.RELEASE.jar:2.3.0.RELEASE]
	at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:549) ~[spring-data-commons-2.3.0.RELEASE.jar:2.3.0.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:155) ~[spring-data-commons-2.3.0.RELEASE.jar:2.3.0.RELEASE]
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130) ~[spring-data-commons-2.3.0.RELEASE.jar:2.3.0.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) ~[spring-tx-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.6.RELEASE.jar:5.2.6.RELEASE]
	at com.sun.proxy.$Proxy78.save(Unknown Source) ~[na:na]
	at br.com.matheus.turismo.service.implementation.ClientServiceImpl.save(ClientServiceImpl.java:44) ~[classes/:na]
	at br.com.matheus.turismo.controller.v1.api.ClientController.create(ClientController.java:31) ~[classes/:na]

Encontrei soluções que seria mudar para JPA, usando o “@MappedSuperClass” porém gostaria de manter em JDBC. E também gostaria de tentar manter está classe abstrata para não precisa adicionar os campos em comuns em todas minhas entidades, se possível claro.

Desde já agradeço, qualquer ajuda!

thimor · Junho 17, 2020, 5:10pm

Acredito que esse CHAR ai esta reclamando pq so aceita um bit. e o “A” tem mais, acredito que so va funcionar se voce utilizat 0, 1, 2, … Muda para String. que deve resolver seu problema.

SilvaRoberto · Junho 20, 2020, 1:21am

Deu certo, foi só questão de alterar o tipo da variável como havia me sugerido.

Para complementar a resposta e ajudar futuros programadores, debugando consegui encontrar o motivo desta questão, da necessidade de alterar a tipagem das variáveis. O Spring-Data-JDBC(2.0.0) passa os atributos como parâmetros utilizando a classe SqlParameterValue, a classe necessita que tenha o tipo SQL que objeto passado como parâmetro, criando um Hash entre a Tipagem em Java e em SQL, para isso é utilizado a classe Types, para determinar o tipo do parâmetro. Eles utilizam a classe JdbcUtil:

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

package org.springframework.data.jdbc.support;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Date;
import java.sql.JDBCType;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;
import java.util.Optional;
import org.springframework.lang.Nullable;
import org.springframework.util.Assert;

public final class JdbcUtil {
    private static final Map<Class<?>, Integer> sqlTypeMappings = new HashMap();

    public static int sqlTypeFor(Class<?> type) {
        Assert.notNull(type, "Type must not be null.");
        Optional var10000 = sqlTypeMappings.keySet().stream().filter((k) -> {
            return k.isAssignableFrom(type);
        }).findFirst();
        Map var10001 = sqlTypeMappings;
        var10001.getClass();
        return (Integer)var10000.map(var10001::get).orElse(-2147483648);
    }

    public static int sqlTypeFor(@Nullable JDBCType jdbcType) {
        return jdbcType == null ? -2147483648 : jdbcType.getVendorTypeNumber();
    }

    @Nullable
    public static JDBCType jdbcTypeFor(int sqlType) {
        return sqlType == -2147483648 ? null : JDBCType.valueOf(sqlType);
    }

    @Nullable
    public static JDBCType jdbcTypeFor(Class<?> type) {
        return jdbcTypeFor(sqlTypeFor(type));
    }

    private JdbcUtil() {
        throw new UnsupportedOperationException("This is a utility class and cannot be instantiated");
    }

    static {
        sqlTypeMappings.put(String.class, 12);
        sqlTypeMappings.put(BigInteger.class, -5);
        sqlTypeMappings.put(BigDecimal.class, 3);
        sqlTypeMappings.put(Byte.class, -6);
        sqlTypeMappings.put(Byte.TYPE, -6);
        sqlTypeMappings.put(Short.class, 5);
        sqlTypeMappings.put(Short.TYPE, 5);
        sqlTypeMappings.put(Integer.class, 4);
        sqlTypeMappings.put(Integer.TYPE, 4);
        sqlTypeMappings.put(Long.class, -5);
        sqlTypeMappings.put(Long.TYPE, -5);
        sqlTypeMappings.put(Double.class, 8);
        sqlTypeMappings.put(Double.TYPE, 8);
        sqlTypeMappings.put(Float.class, 7);
        sqlTypeMappings.put(Float.TYPE, 7);
        sqlTypeMappings.put(Boolean.class, -7);
        sqlTypeMappings.put(Boolean.TYPE, -7);
        sqlTypeMappings.put(byte[].class, -3);
        sqlTypeMappings.put(Date.class, 91);
        sqlTypeMappings.put(Time.class, 92);
        sqlTypeMappings.put(Timestamp.class, 93);
    }
}

Seguindo a implementação apresentada, ela não contém os tipos Character, LocalDate, entre outros, então ele transforma num tipo desconhecido, não sabendo como utilizar o parâmetro na SQL.

2020-06-19 22:00:50.293 DEBUG 17353 --- [nio-8082-exec-5] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [INSERT INTO `client` (`birthday`, `created`, `name`, `rg`, `status`, `telephone`, `updated`) VALUES (?, ?, ?, ?, ?, ?, ?)]
2020-06-19 22:00:50.294 TRACE 17353 --- [nio-8082-exec-5] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 1, parameter value [Sat Jun 20 00:00:00 BRT 2020], value class [java.util.Date], SQL type unknown
2020-06-19 22:00:50.294 TRACE 17353 --- [nio-8082-exec-5] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 2, parameter value [Fri Jun 19 22:00:44 BRT 2020], value class [java.util.Date], SQL type unknown
2020-06-19 22:00:50.295 TRACE 17353 --- [nio-8082-exec-5] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 3, parameter value [ALGUMA PESSOA], value class [java.lang.String], SQL type 12
2020-06-19 22:00:50.295 TRACE 17353 --- [nio-8082-exec-5] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 4, parameter value [000000000], value class [java.lang.String], SQL type 12
2020-06-19 22:00:50.295 TRACE 17353 --- [nio-8082-exec-5] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 5, parameter value [A], value class [java.lang.String], SQL type 12
2020-06-19 22:00:50.295 TRACE 17353 --- [nio-8082-exec-5] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 6, parameter value [0000000000], value class [java.lang.String], SQL type 12
2020-06-19 22:00:50.295 TRACE 17353 --- [nio-8082-exec-5] o.s.jdbc.core.StatementCreatorUtils      : Setting SQL statement parameter value: column index 7, parameter value [Fri Jun 19 22:00:44 BRT 2020], value class [java.util.Date], SQL type unknown
2020-06-19 22:00:50.440 TRACE 17353 --- [nio-8082-exec-5] o.s.jdbc.core.JdbcTemplate               : SQL update affected 1 rows and returned 1 keys
2020-06-19 22:00:50.442 DEBUG 17353 --- [nio-8082-exec-5] o.s.jdbc.core.JdbcTemplate               : SQLWarning ignored: SQL state '22007', error code '1292', message [Incorrect date value: '2020-06-20 03:00:00.0' for column 'birthday' at row 1]

Em caso como Instant, LocalDate, LocalDateTime ele converte para Date, então não foi necessário alterar. Estou procurando mais casos de teste, espero achar mais informações sobre o assunto.

Agradeço, pela ajuda.

[RESOLVIDO] Spring-data-JDBC - Erro ao salvar Entidade no banco

Cursos de Mobile

Cursos de Programação

Cursos de Front-end

Cursos de DevOps

Cursos de Design & UX

Cursos de Business

Cursos de Data & BI