H2 Database Commands

Published: 2019-08-04, Updated: 2020-02-06

Listar todas as tabelas

SELECT * FROM INFORMATION_SCHEMA.TABLES 
SHOW TABLES FROM INFORMATION_SCHEMA

Inserir resultado do select

Tabela

CREATE TABLE DESTINATION_PARAMETER (
	IDT_DESTINATION_PARAMETER INT PRIMARY KEY AUTO_INCREMENT,
	NAM_DESTINATION_PARAMETER VARCHAR(255) UNIQUE,
	NUM_CONSUMERS TINYINT NOT NULL,
	NUM_MAX_CONSUMERS TINYINT NOT NULL,
	NUM_TTL INT NOT NULL,
	NUM_RETRIES TINYINT NOT NULL
);

primeira solucao

INSERT INTO DESTINATION_PARAMETER
(NAM_DESTINATION_PARAMETER,NUM_CONSUMERS,NUM_MAX_CONSUMERS,NUM_TTL,NUM_RETRIES)
   SELECT
   *
   FROM ( SELECT :name, :consumers, :maxConsumers, :ttl, :retries ) X
   WHERE NOT EXISTS
   (
      SELECT
      1
      FROM DESTINATION_PARAMETER
      WHERE NAM_DESTINATION_PARAMETER = :name
   )

segunda solucao

INSERT INTO DESTINATION_PARAMETER
 SELECT
 *
 FROM ( SELECT NULL, :name, :consumers, :maxConsumers, :ttl, :retries ) X
 WHERE NOT EXISTS
 (
    SELECT
    1
    FROM DESTINATION_PARAMETER
    WHERE NAM_DESTINATION_PARAMETER = :name
 )

Comparar duas datas

SELECT 
	TIMESTAMPDIFF('MINUTE', TO_TIMESTAMP('2017-05-31 00:49', 'yyyy-MM-dd HH24-mi'), CURRENT_TIMESTAMP()) 
	FROM DUAL;

Truncar base de dados

You may do it this way:

jdbcTemplate.execute("SET REFERENTIAL_INTEGRITY=0");
final List<String> query = jdbcTemplate.query("SHOW TABLES", (rs, rowNum) -> {

	final String tableName = rs.getString("TABLE_NAME");
	jdbcTemplate.execute("TRUNCATE TABLE " + tableName);
	return tableName;

});

jdbcTemplate.execute("SET REFERENTIAL_INTEGRITY=1");

Conectar numa base remota do H2

org.h2.Driver
jdbc:h2:tcp://h2.dev:1521/opt/h2-data

Startar um servidor h2

java -cp /opt/jdbc-drivers/h2-1.3.176.jar  org.h2.tools.Server -?

keywords

h2 commands, h2 db


Fazendo testes integrados com o Spring Laravel Commands

Comments