Upgrade H2 version 2.0.202 from 1.4.200

See also questions close to this topic

• delete a table form a database using laravel command

  i need to delete a database table using laravel artisan command . not like this command php artisan migrate:rollback --step=5

  i need to create like this route or controller code .

  Route::get('/clear/database', function () {

      Artisan::call('cache:clear');
      return redirect('/');
  

  });

  . i also try public function dd()

  {
      Schema::drop('table_name');
  }
  

  but it not working . gives me error like this SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (SQL: drop table table_name)

  no foreign key for the table . what should i do ?

  thanks in advance!
• How to bring data frame into single column from multiple columns in python

  I have data format in these multiple columns. So I want to bring all 4 columns of data into a single column.

  YEAR Month pcp1 pcp2 pcp3 pcp4              
  1984    1   0   0   0   0
  1984    2   1.2 0   0   0
  1984    3   0   0   0   0
  1984    4   0   0   0   0
  1984    5   0   0   0   0
  1984    6   0   0   0   1.6
  1984    7   3   3   9.2 3.2
  1984    8   6.2 27.1    5.4 0
  1984    9   0   0   0   0
  1984    10  0   0   0   0
  1984    11  0   0   0   0
  1984    12  0   0   0   0
  
• On the time function of database

  When I use Kingbase database in Windows environment, I find that the time function returns the same value in the same transaction. How do I deal with it
• Hibernate handles id assigning instead of database

  I have an auto-increment PK in a table and I want hibernate to handle id assigning instead of database. As my understanding the @GeneratedValue(strategy = GenerationType.IDENTITY) lets the database generate a new value with each insertion operation. So do we have any different solution to handle it?
• Some table rows are not returned by hibernate native query

  I'm having a big problem that a few rows of the table are not returning by the native query of hibernate (Spring Data), although if I put the same query in the database it returns. No exceptions or warnings appear in the log. My database is MySQL.

  Follows the query:

      @Query(value = "SELECT MID(UUID(),1,36) as unique_key, " +
          "TRIM(avio_sinr.num_sinr) as num_sinr, " +
          "avio_sinr.dat_alte_regc, " +
          "avio_sinr.dat_cria_sinr, " +
          "avio_sinr.vlr_limi_inde, " +
          "avio_sinr.vlr_pgto_sinr, " +
          "TRIM(avio_sinr.cod_apol_prco) as cod_apol_prco, " +
          "ctto_usua_prco.nom_emai_usua_prco, " +
          "ctto_usua_prco.num_cell_usua_prco, " +
          "ctto_usua_prco.cod_ddi_tel_usua_prco, " +
          "ctto_usua_prco.cod_ddd_tel_usua_prco, " +
          "ctto_usua_prco.cod_ddi_cell_usua_prco, " +
          "ctto_usua_prco.cod_ddd_cell_usua_prco, " +
          "dado_pgto.bco, " +
          "dado_pgto.cod_tipo_cont_bace, " +
          "dado_pgto.agencia, " +
          "dado_pgto.num_cont_clie, " +
          "dado_pgto.num_digt_verf, " +
          "ende_prco.id_cep_logr, " +
          "ende_prco.nom_bair_usua_prco, " +
          "ende_prco.nom_cida_usua_prco, " +
          "ende_prco.nom_logr_usua_prco, " +
          "ende_prco.num_ende_usua_prco, " +
          "ende_prco.sg_uf, " +
          "ende_prco.txt_cmpl_usua_prco, " +
          "even_ocro.cod_ramo_sega, " +
          "even_ocro.nom_even_infd, " +
          "gara_disi.cod_gara_sinr, " +
          "gara_disi.cod_cobt, " +
          "gara_disi.cod_prod_segu, " +
          "gara_disi.des_gara_sinr, " +
          "gara_disi.nom_gara_sinr, " +
          "prco_abet_sinr.num_prco_sinr, " +
          "prco_abet_sinr.dat_hor_abet_prco, " +
          "prco_abet_sinr.dat_ocor_sinr, " +
          "prco_abet_sinr.hor_ocor_sinr, " +
          "prco_abet_sinr.num_funl_cola_cogl, " +
          "prco_abet_sinr.num_funl_rspl_regc, " +
          "prco_abet_sinr.txt_ocor_sinr, " +
          "situ_prco.nom_situ_prco, " +
          "situ_sinr.nom_situ_sinr, " +
          "tipo_rela_solt_benf.cod_tipo_rela, " +
          "tipo_rela_solt_benf.des_tipo_rela, " +
          "tipo_rela_solt_benf.nom_tipo_rela, " +
          "usua_prco.cod_sexo, " +
          "usua_prco.cod_tipo_pess, " +
          "usua_prco.nom_cpto_usua_prco, " +
          "usua_prco.num_cpf_cnpj_usua_prco, " +
          "segu_prot_prec_clie.vlr_ofrt_clie, " +
          "decs_avio_sinr.dat_hor_decs, " +
          "decs_avio_sinr.txt_decs_anai " +
          "FROM prco_abet_sinr prco_abet_sinr " +
          "LEFT JOIN avio_sinr avio_sinr ON (prco_abet_sinr.num_prco_sinr = avio_sinr.num_prco_sinr) " +
          "LEFT JOIN situ_sinr situ_sinr ON (avio_sinr.cod_situ_sinr = situ_sinr.cod_situ_sinr) " +
          "LEFT JOIN situ_prco situ_prco ON (prco_abet_sinr.cod_situ_prco = situ_prco.cod_situ_prco) " +
          "LEFT JOIN usua_prco usua_prco ON (prco_abet_sinr.cod_usua_prco = usua_prco.cod_usua_prco) " +
          "LEFT JOIN ctto_usua_prco ctto_usua_prco ON (usua_prco.cod_usua_prco = ctto_usua_prco.cod_usua_prco) " +
          "LEFT JOIN dado_pgto dado_pgto ON (prco_abet_sinr.num_prco_sinr = dado_pgto.num_prco_sinr) " +
          "LEFT JOIN ende_prco ende_prco ON (usua_prco.cod_usua_prco = ende_prco.cod_usua_prco) " +
          "LEFT JOIN even_ocro even_ocro ON (prco_abet_sinr.num_prco_sinr = even_ocro.num_prco_sinr) " +
          "LEFT JOIN gara_disi gara_disi ON (avio_sinr.cod_gara_sinr = gara_disi.cod_gara_sinr) " +
          "LEFT JOIN tipo_rela_solt_benf tipo_rela_solt_benf ON (usua_prco.cod_tipo_rela = tipo_rela_solt_benf.cod_tipo_rela) " +
          "LEFT JOIN segu_prot_prec_clie segu_prot_prec_clie ON (prco_abet_sinr.num_prco_sinr = segu_prot_prec_clie.num_prco_sinr) " +
          "LEFT JOIN decs_avio_sinr decs_avio_sinr ON (avio_sinr.num_sinr = decs_avio_sinr.num_sinr) " +
          "ORDER BY avio_sinr.num_sinr AND avio_sinr.dat_alte_regc DESC " +
          "LIMIT :offset, :limit ",
          nativeQuery = true)
  List<MovimentacaoSinistroEntity> findByProtocoloSinistroPaginacao(Long limit, Long offset);
  

  Follows the class:

  @Entity
  @Getter
  @Setter
  @NoArgsConstructor
  @AllArgsConstructor
  @Builder
  public class MovSiEntity {
  
      @EmbeddedId
      private MovPK movSiPK;
  
      @Column(name = "num_sinr")
      private String numberSi;
  
      @Column(name = "cod_gara_sinr")
      private Integer codeWarrantSi;
  
      @Column(name = "dat_alte_regc")
      private LocalDate dateAlteracaoRegistroSi;
  
      @Column(name = "dat_cria_sinr")
      private LocalDateTime dateCriacaoSi;
  
      @Column(name = "vlr_limi_inde")
      private BigDecimal valueLimiteIndenizacaoSi;
  
      @Column(name = "vlr_pgto_sinr")
      private BigDecimal valuePagamentoSi;
  
      @Column(name = "dat_hor_abet_prco")
      private LocalDateTime dateOpenProtocol;
  
      @Column(name = "dat_ocor_sinr")
      private LocalDate dateOcorrenciaSi;
  
      @Column(name = "hor_ocor_sinr")
      private Time hourOcorrenciaSi;
  
      @Column(name = "vlr_ofrt_clie")
      private BigDecimal valueOfferClient;
  
      @Column(name = "dat_hor_decs")
      private LocalDateTime dateHourdecision;
  
      @Column(name = "nom_emai_usua_prco")
      private String emailUserProtocol;
  
      @Column(name = "cod_ddi_tel_usua_prco")
      private Short codeDdiTelUserProtocol;
  
      @Column(name = "cod_ddd_tel_usua_prco")
      private Short codeDddTelUserProtocol;
  
      @Column(name = "cod_ddi_cell_usua_prco")
      private Short codeDdiCelUserProtocol;
  
      @Column(name = "cod_ddd_cell_usua_prco")
      private Short codeDddCelUserProtocol;
  
      @Column(name = "bco")
      private String codeBanco;
  
      @Column(name = "cod_tipo_cont_bace")
      private String codeTipoAccountBacen;
  
      @Column(name = "agencia")
      private String agencia;
  
      @Column(name = "num_cont_clie")
      private String numberAccountClient;
  
      @Column(name = "num_digt_verf")
      private String numberDigitoVerify;
  
      @Column(name = "id_cep_logr")
      private String cepplace;
  
      @Column(name = "nom_bair_usua_prco")
      private String bairroUserProtocol;
  
      @Column(name = "nom_cida_usua_prco")
      private String cidadeUserProtocol;
  
      @Column(name = "nom_logr_usua_prco")
      private String placeUserProtocol;
  
      @Column(name = "sg_uf")
      private String siglaUnidadeFederacao;
  
      @Column(name = "txt_cmpl_usua_prco")
      private String complementoplaceUserProtocol;
  
      @Column(name = "nom_even_infd")
      private String nomeEventoInformado;
  
      @Column(name = "des_gara_sinr")
      private String descWarrant;
  
      @Column(name = "nom_gara_sinr")
      private String nomeWarrant;
  
      @Column(name = "num_funl_cola_cogl")
      private String funcionalColaboradorConglomerado;
  
      @Column(name = "num_funl_rspl_regc")
      private String funcionalResponsavelRegulacao;
  
      @Column(name = "txt_ocor_sinr")
      private String textoOcorrenciaSi;
  
      @Column(name = "des_tipo_rela")
      private String descriptionTipoRelacao;
  
      @Column(name = "nom_tipo_rela")
      private String nomeTipoRelacao;
  
      @Column(name = "cod_sexo")
      private String codeSexo;
  
      @Column(name = "cod_tipo_pess")
      private String codeTipoPessoa;
  
      @Column(name = "nom_cpto_usua_prco")
      private String nomeCompletoUserProtocol;
  
      @Column(name = "num_cpf_cnpj_usua_prco")
      private String numberCpfCnpjUserProtocol;
  
      @Column(name = "num_cell_usua_prco")
      private Integer numberCelularUserProtocol;
  
      @Column(name = "num_ende_usua_prco")
      private Integer numberEnderecoUserProtocol;
  
      @Column(name = "cod_ramo_sega")
      private Integer codeRamoSeguradora;
  
      @Column(name = "cod_tipo_rela")
      private Integer codeTipoRelacao;
  
      @Column(name = "cod_cobt")
      private Integer codeCobertura;
  
      @Column(name = "cod_prod_segu")
      private Integer codeProdutoSeguro;
  
      @Column(name = "nom_situ_prco")
      private String nomeSituacaoProtocol;
  
      @Column(name = "nom_situ_sinr")
      private String nomeSituacaoSi;
  
      @Column(name = "txt_decs_anai")
      private String decisionAnalista;
  
  
  }
  

  follows the Embeddable class:

  @Embeddable
  @Getter
  @Setter
  @Builder
  @EqualsAndHashCode
  @AllArgsConstructor
  @NoArgsConstructor
  public class MovSiPK  implements Serializable {
  
      @Column(name = "unique_key")
      private String uniqueKey;
  
      @Column(name = "num_prco_sinr", nullable = false)
      private String numberProtocolSi;
  
      @Column(name = "cod_apol_prco")
      private String codeApoliceProtocol;
  
  
  }
  
• Spring boot mapsId

  Hi I using spring boot jpa and I need to use mapsid. There are not so much information about that and I wonder if anyone can help me understand how to use maybe link a simple code example where its 2 table that joins 1 table (oneToOne)
• Sequence number, incorrect number

  In a spring boot application, I have this entity

  @Data @NoArgsConstructor @AllArgsConstructor @Entity public class User {

  @Id
  @GeneratedValue(generator="user_id_seq")
  @SequenceGenerator(name="user_id_seq",sequenceName="user_id_seq", allocationSize=1)
  Long id
   ...
  

  }

  In mariadb, when I check the sequence

  CREATE OR REPLACE SEQUENCE `user_id_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB
  

  In db, I have only 5 user.

  select id from `user` u 
  

  This query return

  1002 1004 1005 2007 3001

  Why it's not 1, 2, 3, 4, 5

  Is it beaucause of the cache 1000?
• Select only a set to improve perfomance

  I search to know if a user have an active subscription

  @Query("select case when count(u) > 0 then true else false end from User u where u.id=:userId and  current_date < u.subscriptionEndDate")
  boolean userActiveSubscription(Long userId);
  

  subscriptionEndDate use a localdate

  Probably user object is queried, is there a way to limit data retrived to return true or false
• How to throw exception when there is extra parameters in request body spring boot

  In my last project, there was a requirement for throwing exceptions when the request body contains extra parameters.

  If the request will be like

  {
     "test":"body",
      "name":"HR 1",
      "location":"Location"
  }
  
  

  where test parameter is unnecessary and I've to return a response that should be like

  {
      "timestamp": "2022-05-07T00:13:59.144657",
      "status": "500",
      "error": "Internal Server Error",
      "message": "test : must not be provided",
      "path": "/api/departments/HR"
  }
  
  

  I've shared the answer. How I handled it.
• Use H2 as test DB in JUnit5

  I'm developing an app in Spring Boot. I use a PostreSQL DB for production, and I want my JUnit 5 tests to run on a H2 memory DB. Problem is, after some configuration, the tests still don't seem to run on the in-memory db:

  • I can access entities from the prod db (a saved entity doesn't persist in the prod db tho)

  • if I grep through the test logs, I can see that Hibernate uses org.hibernate.dialect.PostgreSQLDialect

  @DataJpaTest
  @SpringBootTest
  @TestPropertySource(locations = "classpath:application-test.properties")
  @ExtendWith(SpringExtension.class)
  @ActiveProfiles("test")
  public class ClinicTest {
      @Resource
      private ClinicRepository clinicRepository;
  
      @Test
      public void givenClinic_whenSave_thenGetOk() {
  
          var clinic = new Clinic();
          clinic.setName("asd asd");
          clinic.setShortName("asd");
          
          // the ids of prod db entities are printed
          clinicRepository.findAll().forEach(clinic1 -> System.out.println(clinic1.getId()));
          
  
          // the id keeps incrementing between runs
          System.out.println(clinicRepository.save(clinic).getId());
      }
  
  }
  

  application-test.properties

  jdbc.driverClassName=org.h2.Driver
  jdbc.url=jdbc:h2:mem:testdb
  hibernate.dialect=org.hibernate.dialect.H2Dialect
  hibernate.hbm2ddl.auto=create-drop
  spring.test.database.replace=none
  

  What should I change to run self-contained test on a H2 in memory database?
• liquibase.exception.DatabaseException: org.h2.jdbc.JdbcBatchUpdateException: Cannot parse "DATE" constant "DATEADD(DAY,-2,CURDATE())

  after upgrading to liquibase 3.9.0 and h2 1.4.200, I'm receiving this error message when trying to load data from a csv file. Reason: liquibase.exception.DatabaseException: org.h2.jdbc.JdbcBatchUpdateException: Cannot parse "DATE" constant "DATEADD(DAY,-2,CURDATE())"; Last versions it worked were liquibase 3.5.5 and h2 1.4.197.

  Bellow is an example of how the csv is structured :

  id;sender;client_code;status;title;streamfile_name;delivery_channel;delivery_time;delivery_address;viewed_time;visible_until;document_date; Client;Test;USER_INTERACTION;test_document1;ESTDOCUMENT_1;null;null;null;null;null;DATEADD(DAY,-2,CURDATE());

  POM looks like this:

      <liquibase.version>3.9.0</liquibase.version>
      <liquibase-hibernate5.version>3.8</liquibase-hibernate5.version>
      <h2.version>1.4.200</h2.version>
      <validation-api.version>2.0.1.Final</validation-api.version>
  

  liquibase script:If I am not specifying the type of the field I receive the error mentioned above, if I specify it like this

  column name="document_date" type="date

  Then I am receiving an error that the field cannot be null, as null values are not allowed there and because it cannot be parsed liquibase puts null.
• in H2 database, how to use ANY(array)?

  In a H2 database, I'm trying to query an array column with ANY, something like that:

  ALTER TABLE person ADD COLUMN tel_numbers VARCHAR ARRAY;
  SELECT * FROM person p WHERE '+123456' = ANY(p.tel_numbers) 
  

  This throws an error 90054-210 invalid usage of aggregatte function

  I also tryied adding additional parenthesis around p.tel_numbers as for subquery, seeing the doc http://h2database.com/html/functions-aggregate.html#any but it does not help. How to write it correctly for H2? Hopefully a solution that also works in Postgres...

  PS: The query is generated through Hibernate Query Builder, but that should not change the problem nor the answer.
• How to send the data finded in the controller to a view?

  I try to do a java web application using:
  SpringBoot
  Mysql
  JDBC
  Design pattern: MVC, DAO
  And Thymeleaf
  I think i understood than:
  I must create a class for every Entity and a DAO classe who use the annotation @Repository:
  Diabetic.java(Entity):

      public class Diabetic {
  
      private int id_diabetic;
      private int id_doctor;
      private String name;
      private String firstname;
      private Date birthdate;
      private String mail;
      private String password;
      private String phone;
      private String emergencyContact;
      private String address;
  
      public Diabetic() {
      }
  
      public Diabetic(int id_diabetic, int id_doctor, String name, String firstname, Date birthdate,
                      String mail, String password, String phone, String emergencyContact, String address) {
          this.id_diabetic = id_diabetic;
          this.id_doctor = id_doctor;
          this.name = name;
          this.firstname = firstname;
          this.birthdate = birthdate;
          this.mail = mail;
          this.password = password;
          this.phone = phone;
          this.emergencyContact = emergencyContact;
          this.address = address;
      }
  
      public int getId_diabetic() {
          return id_diabetic;
      }
  
      public void setId_diabetic(int id_diabetic) {
          this.id_diabetic = id_diabetic;
      }
  
      public int getId_doctor() {
          return id_doctor;
      }
  
      public void setId_doctor(int id_doctor) {
          this.id_doctor = id_doctor;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public String getFirstname() {
          return firstname;
      }
  
      public void setFirstname(String firstname) {
          this.firstname = firstname;
      }
  
      public Date getBirthdate() {
          return birthdate;
      }
  
      public void setBirthdate(Date birthdate) {
          this.birthdate = birthdate;
      }
  
      public String getMail() {
          return mail;
      }
  
      public void setMail(String mail) {
          this.mail = mail;
      }
  
      public String getPassword() {
          return password;
      }
  
      public void setPassword(String password) {
          this.password = password;
      }
  
      public String getPhone() {
          return phone;
      }
  
      public void setPhone(String phone) {
          this.phone = phone;
      }
  
      public String getEmergencyContact() {
          return emergencyContact;
      }
  
      public void setEmergencyContact(String emergencyContact) {
          this.emergencyContact = emergencyContact;
      }
  
      public String getAddress() {
          return address;
      }
  
      public void setAddress(String address) {
          this.address = address;
      }
  }
  

  DiabeticDAO.java:

  @Repository
  public class DiabeticDAO {
  
      private JdbcTemplate jdbcTemplate;
      private SimpleJdbcInsert simpleJdbcInsert;
  
      @Autowired
      public void setDataSource(DataSource dataSource){
          this.jdbcTemplate = new JdbcTemplate(dataSource);
          this.simpleJdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("diabetic")
                  .usingGeneratedKeyColumns("id_diabetic");
      }
  
      public Diabetic getDiabById(int id){
          return jdbcTemplate.queryForObject("select * from Diabetic where id_diabetic=?", new DiabeticRowMapper(),id);
      }
  
      public List<Diabetic> getAllDiab(){
          return jdbcTemplate.query("select * from Diabetic",new DiabeticRowMapper());
  
      }
  
      private final class DiabeticRowMapper implements RowMapper<Diabetic> {
          @Override
          public Diabetic mapRow(ResultSet rs, int rowNum) throws SQLException{
              Diabetic diabetic = new Diabetic();
              diabetic.setId_diabetic(rs.getInt("id_diabetic"));
              diabetic.setName(rs.getString("name"));
              diabetic.setFirstname(rs.getString("firstname"));
              diabetic.setBirthdate(rs.getDate("birthdate"));
              diabetic.setMail(rs.getString("mail"));
              diabetic.setPassword(rs.getString("password"));
              diabetic.setPhone(rs.getString("phone"));
              diabetic.setEmergencyContact(rs.getString("emergencyContact"));
              diabetic.setAddress(rs.getString("address"));
              return diabetic;
          }
  
      }
  }
  

  And create a controller like this where I use the method from the DAO:

  @Controller
  public class MainController {
  
      @GetMapping("/")
      public String firstPage(){
          return"firstPage";
      }
  
      @GetMapping("/authentification")
      public String auth(){
          DiabeticDAO diaDao = new DiabeticDAO();
          List<Diabetic> listDia = diaDao.getAllDiab();
          return"loggin";
      }
  

  What i don't understand:

  1. How to do a link in a view(html) to go to a controller ?
  2. How to send the data finded in the controller to a view for displaying them ?

  Sorry if my questions seems strange but it's the first time i ask for help on here and english is not my first language
• String array passed to Oracle stored procedure from Spring is not being inserted

  I am trying to a invoke stored procedure which accepts string array as input and has no output.

  In my procedure, I'm just looping through array elements to insert them into a table. To pass string array from Spring to procedure, I have used SimpleJdbcCall.

  The call to procedure executes fine, however the records are not being inserted.

  I have followed Spring JDBC - Passing in ARRAY of BLOBs to SQL Function

  and

  How pass VARRAY(oracle PL/SQL) param to procedure with simplejdbccall?

  This is what my code looks like.

  This is the type and the procedure that I have defined

  create or replace TYPE user_string_array
      AS VARRAY(15) OF VARCHAR2(4000);
  

  create or replace PROCEDURE insert_tt_names (member_names IN user_string_array)
  AS
  BEGIN
    FOR i IN 1..member_names.count loop
       insert into tt_user_map (name, last_assigned)
              values (member_names(i), 'a');
    END LOOP;
  END;
  

  The table tt_user_map has two columns which are of VARCHAR2(50 byte)

  The Spring code to invoke the procedure uses SimpleJdbcCall.

  List<String> user = Arrays.asList(names);
  SimpleJdbcCall caller1 = new SimpleJdbcCall(jdbcTemplate);
              caller1.withProcedureName("insert_tt_names")
                      .declareParameters(new SqlParameter("member_names", Types.ARRAY, "user_string_array"));
              MapSqlParameterSource param1 = new MapSqlParameterSource().addValue("member_names",
                      new OracleSqlArrayValue(user, "user_string_array"));
              caller1.execute(param1);
  

  Now since I want to do this the Spring way, so in order to pass array, I have created custom SqlArrayValue class to pass array and unwrap the Oracle Connection.

  The code runs fine, however array elements are not being inserted in table, but the static value 'a' gets inserted into column last_assigned properly.

  Please suggest what could be the reason for this, is it the database issue or there is something wrong in the Spring code. The Spring code executes fine and there is no error. Even the procedure when run manually, inserts the array elements. I'm quite stuck as to how to approach this problem.

  import java.sql.Array;
  import java.sql.Connection;
  import java.sql.SQLException;
  import java.util.List;
  
  import org.springframework.dao.InvalidDataAccessApiUsageException;
  import org.springframework.jdbc.core.support.AbstractSqlTypeValue;
  
  import oracle.jdbc.OracleConnection;
  import oracle.sql.ARRAY;
  import oracle.sql.ArrayDescriptor;
  
  public class OracleSqlArrayValue extends AbstractSqlTypeValue {
  
      private List<String> values;
      
      private String defaultTypeName;
  
      public OracleSqlArrayValue(List<String> values) {
          this.values = values;
      }
  
      public OracleSqlArrayValue(List<String> values, String defaultTypeName) {
          this.values = values;
          this.defaultTypeName = defaultTypeName;
      }
      
      protected Object createTypeValue(Connection conn, int sqlType, String typeName)
              throws SQLException {
          if (typeName == null && defaultTypeName == null) {
  
              throw new InvalidDataAccessApiUsageException(
                      "No type named defined. Instantiate class with default type name.");
          }
          
          String[] str = new String[values.size()];
          for(int i = 0; i < str.length; i++) {
              str[i] = values.get(i);
          }
          
          if (!conn.isWrapperFor(OracleConnection.class)) {
              throw new InvalidDataAccessApiUsageException(
                      "Unable to unwrap OracleConnection. Ensure you are connecting to Oracle DB.");
          }
          
          Array array = conn.unwrap(OracleConnection.class)
                  .createOracleArray(typeName != null ? typeName : defaultTypeName, str);
          
          return array;
      }
  
  }
  

  This above code works well with integer array, however for string array it fails to insert record in table.
• I have written a small java spring jdbc program for inserting record but I get exception like below

  INFO: Loaded JDBC driver: com.mysql.jdbc.Driver Exception in thread "main" org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'. at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:866) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:927) at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:937) at com.pepcus.spring.jdbc.App.main(App.java:20) Caused by: java.sql.SQLException: Unable to load authentication plugin 'caching_sha2_password'. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:923) at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1715) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1244) at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2412) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2445) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2230) at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:813) at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77) at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499) at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480) at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:334) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:190) at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:153) at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:144) at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:155) at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:120) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77) ... 5 more