[FIXED] JDBC not inserting data into MS SQL Server

Issue

I'm not familiarized maven/spring boot and i was gifted with a project using those tecnologies on server side and i can't insert data into ms sql server using the methods .save() and .saveAll().

The application.properties has those informations

spring:
  # datasource:
  #   url: jdbc:h2:mem:neoenergia;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
  #   driverClassName: org.h2.Driver
  #   username: sa
  #   password:
  datasource:
    initialization-mode: always
    url: jdbc:sqlserver://....;databaseName=...;user=sa;password=;
    driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver    
    username: sa
    password: ...
    platform: mssql
  tomcat:
    max-wait: 20000
    max-active: 50
    max-idle: 20
    min-idle: 15
  jpa:
    show-sql: true
    generate-ddl: false
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        # dialect: org.hibernate.dialect.H2Dialect
        generate_statistics: true
        jdbc.batch_size: 4
        order_inserts: true
        dialect: org.hibernate.dialect.SQLServerDialect
        format_sql: true
        id:
          new_generator_mappings: false
  # h2:
    # console.enabled: true
    # console.path: /h2-console
    # console.settings.trace: false
    # console.settings.web-allow-others: false

server:
   port: 8080

security:
  jwt:
    token:
      secret-key: secret-key
      expire-length: 300000

Running the server with hibernate.generate_statistics=true logs this when i sent a post:

2021-05-04 16:28:05.150  INFO 22396 --- [  restartedMain] DeferredRepositoryInitializationListener : Spring Data repositories initialized!        
2021-05-04 16:28:05.154  INFO 22396 --- [  restartedMain] com.neoenergia.NeoenergiaApplication     : Started NeoenergiaApplication in 3.345 seconds (JVM running for 2414.938)
2021-05-04 16:28:05.156  INFO 22396 --- [  restartedMain] .ConditionEvaluationDeltaLoggingListener : Condition evaluation unchanged
2021-05-04 16:28:10.276  INFO 22396 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2021-05-04 16:28:10.277  INFO 22396 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'     
2021-05-04 16:28:10.282  INFO 22396 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 4 ms
2021-05-04 16:28:10.747  INFO 22396 --- [nio-8080-exec-1] i.StatisticalLoggingSessionEventListener : Session Metrics {
    150676700 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    0 nanoseconds spent preparing 0 JDBC statements;
    0 nanoseconds spent executing 0 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

-- Controller

package com.neoenergia.controller;

import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import com.neoenergia.dto.ContratoDTO;
import com.neoenergia.model.Contrato;
import com.neoenergia.service.ContratoService;

import org.modelmapper.ModelMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.*;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

import com.neoenergia.repository.ContratoRepository;

@RestController
@RequestMapping("/cadastro")
@Api(tags = "Contrato")
public class ContratoController {

    @Autowired
    private ContratoService contratoService;

    @Autowired
    private ModelMapper modelMapper;
    
    @GetMapping("/contrato")
    @ApiOperation(value = "Obter contratos")
    public ResponseEntity<Map<String, Object>> get(
        @RequestParam(defaultValue = "") List<String> filter, 
        @RequestParam(defaultValue = "0") Integer page, 
        @RequestParam(defaultValue = "10") Integer pageSize) {      

        Page<Contrato> contrato = contratoService.get(filter, page, pageSize);

        Map<String, Object> response = new HashMap<>();     
          response.put("data", contrato.getContent()
            .stream()
            .map(obj -> modelMapper.map(obj, ContratoDTO.class))
            .collect(Collectors.toList()));
          response.put("pagina", contrato.getNumber()+1);
          response.put("registrosPorPagina", pageSize);
        response.put("totalRegistros", contrato.getTotalElements());
        response.put("totalPaginas", contrato.getTotalPages());
          
        return new ResponseEntity<>(response, HttpStatus.OK);
    }

    @GetMapping("/contrato/all")
    @ApiOperation(value = "Obter todos contratos")
    public ResponseEntity<Map<String, Object>> get(
        @RequestParam(defaultValue = "") List<String> filter) {     

        List<Contrato> contratos = contratoService.getAll(filter);

        Map<String, Object> response = new HashMap<>();     
          response.put("data", contratos
            .stream()
            .map(obj -> modelMapper.map(obj, ContratoDTO.class))
            .collect(Collectors.toList()));
          
        return new ResponseEntity<>(response, HttpStatus.OK);
    }

    
    @GetMapping("/contrato/{id}")
    @ApiOperation(value = "Obter contrato por id", response = ContratoDTO.class)
    public ContratoDTO getById(@PathVariable("id") Integer id) {        

        return modelMapper.map(contratoService.getById(id), ContratoDTO.class);
    }

@PostMapping("/contrato")
@ApiOperation(value = "Salvar contrato")
public void post(@RequestBody List<ContratoDTO> listaContratoDTO) {
    List<Contrato> contratos = new ArrayList<Contrato>();
    for(ContratoDTO item : listaContratoDTO){
        if (item.getId() != null) {
            put(Arrays.asList(item));
        } else if (item.getPer_mes_ano() != null) {             
            LocalDateTime periodo = ((Date) item
            .getPer_mes_ano())
            .toInstant()
            .atZone(ZoneOffset.UTC)
            .toLocalDateTime().withMinute(0).withSecond(0).withNano(0);
            Contrato contrato = modelMapper.map(item, Contrato.class);
            contrato.setPer_mes_ano(Date.from(periodo.atZone(ZoneOffset.UTC).toInstant()));
            contratos.add(contrato);
        }
        // else{                
        //  LocalDateTime dataInicial = item.getData_inicial()
        //  .toInstant()
        //  .atZone(ZoneOffset.UTC)
        //  .toLocalDateTime().withMinute(0).withSecond(0).withNano(0);
        //  LocalDateTime dataFinal = item.getData_final()
        //  .toInstant()
        //  .atZone(ZoneOffset.UTC)
        //  .toLocalDateTime().withMinute(0).withSecond(0).withNano(0);
            
        //  if(dataInicial.isBefore(dataFinal.plusHours(1))){
        //      for (LocalDateTime date = dataInicial; date.isBefore(dataFinal.plusHours(1)); date = date.plusHours(1)) {
        //          Contrato contrato = modelMapper.map(item, Contrato.class);
        //          contrato.setPer_mes_ano(Date.from(date.atZone(ZoneOffset.UTC).toInstant()));
        //          contratos.add(contrato);
        //      }
        //  }
        // }
    
    contratoService.post(contratos);
    }
}

    @PutMapping("/contrato")
    @ApiOperation(value = "Alterar contrato")
    public void put(@RequestBody List<ContratoDTO> listaContratoDTO) {      
        List<Contrato> contratos = new ArrayList<Contrato>();

        for(ContratoDTO item : listaContratoDTO){
            if(item.getPer_mes_ano() != null){
                LocalDateTime periodo = ((Date) item.getPer_mes_ano())
                .toInstant()
                .atZone(ZoneOffset.UTC)
                .toLocalDateTime().withMinute(0).withSecond(0).withNano(0);
                Contrato contrato = modelMapper.map(item, Contrato.class);
                contrato.setPer_mes_ano(Date.from(periodo.atZone(ZoneOffset.UTC).toInstant()));
                contratos.add(contrato);
            }
        }
        
        contratoService.put(contratos);
    }

    @DeleteMapping("/contrato/{ids}")
    @ApiOperation(value = "Excluir contrato")
    public void delete(@PathVariable("ids") Integer[] ids) {                
        contratoService.delete(ids);
    }
}

-- Service

package com.neoenergia.service;

import java.util.List;

import com.neoenergia.model.Contrato;
import com.neoenergia.repository.ContratoRepository;
import com.neoenergia.specifications.SpecificationTypeResolver;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import javax.transaction.Transactional;

@Transactional
@Service
public class ContratoService {

    @Autowired
    private ContratoRepository contratoRepository;

    @Transactional
    public void post(List<Contrato> listaContratos) {
        contratoRepository.saveAll(listaContratos);
    }

    public Page<Contrato> get(List<String> filters, int page, int pageSize) {
        Pageable paging = PageRequest.of(page, pageSize, Sort.unsorted());
        
        Specification<Contrato> finalSpec = (entity, cq, cb) -> cb.conjunction();

        for(String item : filters){
            String[] filter = item.split(";");
            finalSpec = SpecificationTypeResolver.<Contrato>resolve(finalSpec, filter[0], filter[1], filter[2], Contrato.class);
        }
        return contratoRepository.findAll(finalSpec, paging);
    }

    public List<Contrato> getAll(List<String> filters){
        Specification<Contrato> finalSpec = (entity, cq, cb) -> cb.conjunction();

        for(String item : filters){
            String[] filter = item.split(";");
            finalSpec = SpecificationTypeResolver.<Contrato>resolve(finalSpec, filter[0], filter[1], filter[2], Contrato.class);
        }
        return contratoRepository.findAll(finalSpec);
    }

    public Contrato getById(int id) {
        return contratoRepository.findById(id).get();
    }

    public void put(List<Contrato> listaContratos) {
        for(Contrato item : listaContratos){
            contratoRepository.findById(item.getCod_processamento())
            .map(entity -> {
                entity.setCod_processamento(item.getCod_processamento());
                entity.setSg_processamento(item.getSg_processamento());             
                entity.setCod_periodo(item.getCod_periodo());
                entity.setPer_mes_ano(item.getPer_mes_ano());
                entity.setCod_submercado(item.getCod_submercado());
                entity.setSg_submercado(item.getSg_submercado());
                entity.setCod_perf_agente_vndd(item.getCod_perf_agente_vndd());
                entity.setSg_perf_agente_vndd(item.getSg_perf_agente_vndd());
                entity.setCod_perf_agente_cmpd(item.getCod_perf_agente_cmpd());
                entity.setSg_perf_agente_cmpd(item.getSg_perf_agente_cmpd());
                entity.setCq(item.getCq());
                entity.setPrc_base(item.getPrc_base());
                entity.setDt_base(item.getDt_base());
                entity.setDt_reajuste(item.getDt_reajuste());
                entity.setNum_cntr_cliqccee(item.getNum_cntr_cliqccee());
                entity.setNum_cntr_gene(item.getNum_cntr_gene());
                entity.setTp_agente(item.getTp_agente());
                entity.setFinalidade(item.getFinalidade());
                return contratoRepository.save(entity);
              })
              .orElseGet(() -> {
                return contratoRepository.save(item);
              });
        }       
    }

    public void delete(Integer[] ids) {
        for(Integer id : ids){
            contratoRepository.deleteById(id);
        }
    }
}

-- Repository

package com.neoenergia.repository;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import com.neoenergia.model.Contrato;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import javax.transaction.Transactional;

@Repository
@Transactional
public interface ContratoRepository extends JpaRepository<Contrato, Integer>, JpaSpecificationExecutor<Contrato> {
}

-- Model

package com.neoenergia.model;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import lombok.AllArgsConstructor;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Entity
@Getter
@Setter
@ToString
@EqualsAndHashCode
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "entr_cntr")
public class Contrato {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer cod_processamento;

    //@Column(length=100)
    private String sg_processamento;

    private Integer cod_periodo;

    private Date per_mes_ano;

    private Integer cod_submercado;

    //@Column(length=50)
    private String sg_submercado;

    private Integer cod_perf_agente_vndd;

    //@Column(length=50)
    private String sg_perf_agente_vndd;

    private Integer cod_perf_agente_cmpd;

    //@Column(length=50)
    private String sg_perf_agente_cmpd;

    //@Column(precision=18, scale=6)
    private Double cq;

    //@Column(precision=18, scale=2)
    private Double prc_base;

    private Date dt_base;

    // private Date data_inicial;

    // private Date data_final;

    private Integer dt_reajuste;

    private Integer num_cntr_cliqccee;

    //@Column(length=12)
    private String num_cntr_gene;

    //@Column(length=2)
    private String tp_agente;

    //@Column(length=75)
    private String finalidade;  

    public Integer getCod_processamento() {
        return cod_processamento;
    }

    public void setCod_processamento(Integer cod_processamento) {
        this.cod_processamento = cod_processamento;
    }

    public String getSg_processamento() {
        return sg_processamento;
    }

    public void setSg_processamento(String sg_processamento) {
        this.sg_processamento = sg_processamento;
    }

    public Integer getCod_periodo() {
        return cod_periodo;
    }

    public void setCod_periodo(Integer cod_periodo) {
        this.cod_periodo = cod_periodo;
    }

    public Date getPer_mes_ano() {
        return per_mes_ano;
    }

    public void setPer_mes_ano(Date per_mes_ano) {
        this.per_mes_ano = per_mes_ano;
    }

    public Integer getCod_submercado() {
        return cod_submercado;
    }

    public void setCod_submercado(Integer cod_submercado) {
        this.cod_submercado = cod_submercado;
    }

    public String getSg_submercado() {
        return sg_submercado;
    }

    public void setSg_submercado(String sg_submercado) {
        this.sg_submercado = sg_submercado;
    }

    public Integer getCod_perf_agente_vndd() {
        return cod_perf_agente_vndd;
    }

    public void setCod_perf_agente_vndd(Integer cod_perf_agente_vndd) {
        this.cod_perf_agente_vndd = cod_perf_agente_vndd;
    }

    public String getSg_perf_agente_vndd() {
        return sg_perf_agente_vndd;
    }

    public void setSg_perf_agente_vndd(String sg_perf_agente_vndd) {
        this.sg_perf_agente_vndd = sg_perf_agente_vndd;
    }

    public Integer getCod_perf_agente_cmpd() {
        return cod_perf_agente_cmpd;
    }

    public void setCod_perf_agente_cmpd(Integer cod_perf_agente_cmpd) {
        this.cod_perf_agente_cmpd = cod_perf_agente_cmpd;
    }

    public String getSg_perf_agente_cmpd() {
        return sg_perf_agente_cmpd;
    }

    public void setSg_perf_agente_cmpd(String sg_perf_agente_cmpd) {
        this.sg_perf_agente_cmpd = sg_perf_agente_cmpd;
    }

    public Double getCq() {
        return cq;
    }

    public void setCq(Double cq) {
        this.cq = cq;
    }

    public Double getPrc_base() {
        return prc_base;
    }

    public void setPrc_base(Double prc_base) {
        this.prc_base = prc_base;
    }

    // public Date getData_inicial() {
    //  return data_inicial;
    // }

    // public void setData_inicial(Date data_inicial) {
    //  this.data_inicial = data_inicial;
    // }

    // public Date getData_final() {
    //  return data_final;
    // }

    // public void setData_final(Date data_final) {
    //  this.data_final = data_final;
    // }

    public Date getDt_base() {
        return dt_base;
    }

    public void setDt_base(Date dt_base) {
        this.dt_base = dt_base;
    }

    public Integer getDt_reajuste() {
        return dt_reajuste;
    }

    public void setDt_reajuste(Integer dt_reajuste) {
        this.dt_reajuste = dt_reajuste;
    }

    public Integer getNum_cntr_cliqccee() {
        return num_cntr_cliqccee;
    }

    public void setNum_cntr_cliqccee(Integer num_cntr_cliqccee) {
        this.num_cntr_cliqccee = num_cntr_cliqccee;
    }

    public String getNum_cntr_gene() {
        return num_cntr_gene;
    }

    public void setNum_cntr_gene(String num_cntr_gene) {
        this.num_cntr_gene = num_cntr_gene;
    }

    public String getTp_agente() {
        return tp_agente;
    }

    public void setTp_agente(String tp_agente) {
        this.tp_agente = tp_agente;
    }

    public String getFinalidade() {
        return finalidade;
    }

    public void setFinalidade(String finalidade) {
        this.finalidade = finalidade;
    }
}

Does someone knows why the data is not persisting in database?

Solution

The problem was at the front end. The person who coded the system previously created different models in relation to the back end and the front end. Adjusting the object that is mounted to send to the server with the correct data, it was possible to persist the data in the database.

There was also the use of a common registration data as @Entity, unlike the one registered in the database. After also adjusting the database, controller, service and classes, the transaction was successfully completed.

Thanks for all your help.

Answered By - Emmanuel Oliveira