H2 Database Commands

Criar uma função customizada

DROP ALIAS IF EXISTS TO_NUMBER;
CREATE ALIAS TO_NUMBER AS '
  int to_number(String ipt) {
    if(ipt.startsWith("INTERVAL")){
      final java.util.regex.Matcher matcher = java.util.regex.Pattern.compile("(\\d+) ").matcher(ipt);
      matcher.find();
      return Integer.parseInt(matcher.group(1));
    }
    return -1;
  }
';

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:

• Disable referential integrity using SET REFERENTIAL_INTEGRITY FALSE

• Get the list of all tables using SHOW TABLES

• Delete the data from each table using TRUNCATE TABLE tableName

• Enable referential integrity using SET REFERENTIAL_INTEGRITY TRUE

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

Para iniciar o servidor

mkdir -p /tmp/h2 &&\
java -cp h2-2.1.214.jar org.h2.tools.Server -webAllowOthers -tcpAllowOthers -baseDir /tmp/h2 -ifNotExists

Para conectar

jdbc:h2:tcp://localhost:9092/marroi.tmp

Isso vai criar um banco no diretorio /tmp/h2/marroi.tmp

Para ver as opções

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