PostgreSQL backing up and restoring with pg_dump and psql

Backing up the data [code] pg_dump –host HOSTADDR –port 5432 –username "wins_qa" –format plain –encoding UTF8 –schema-only –no-owner –file %HOME%"\Desktop\create_query.sql" –schema "wins_qa" "gwinsq01" [/code] this query will; create only creation queries, not involve role owner information, only select the schema “wins_qa”, only select the database”gwinsq01″, For more information consult the PostgreSQL Documentation at https://www.postgresql.org/docs/current/static/app-pgdump.html   Restoring

Liquibase Spring batch xml for Oracle RDBMS

  [code] <?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd"> <changeSet id="1.0.0_SPRING_BATCH_INFRASTRUCTURE" author="a243229"> <createSequence sequenceName="BATCH_STEP_EXECUTION_SEQ" startValue="0" minValue="0" maxValue="9223372036854775807" cycle="false" /> <createSequence sequenceName="BATCH_JOB_EXECUTION_SEQ" startValue="0" minValue="0" maxValue="9223372036854775807" cycle="false" /> <createSequence sequenceName="BATCH_JOB_SEQ" startValue="0" minValue="0" maxValue="9223372036854775807" cycle="false" /> <createTable tableName="BATCH_JOB_INSTANCE"> <column name="JOB_INSTANCE_ID" type="BIGINT"> <constraints primaryKey="true" nullable="false" /> </column> <column name="VERSION" type="BIGINT" /> <column name="JOB_NAME" type="VARCHAR(100)"> <constraints nullable="false" /> </column>

Running sql queries in Liquibase

Make sure the user that you’ll use to issue this command will have privileges. In this query I create a user on Oracle DB [code] <?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd"> <changeSet id="1.0.0_RBACK_USER" author="a243229"> <!– TO BE ABLE TO RUN THIS SCRIPT, MAKE SURE YOUR NON-SYS USER HAS SUCH PRIVILEGES OTHERWISE RUN THE

Running Liquibase xml file from command line

I’ve tested the code against Oracle database at work. First download the jar and issue the command similar to below, make sure you are in the xml file’s location [code] java -jar C:\Users\A243229\Desktop\liquibase.jar –driver=oracle.jdbc.OracleDriver –classpath=C:\JavaDev\mavenRepository\oracle\ojdbc14\10.2.0.3.0\ojdbc14-10.2.0.3.0.jar –changeLogFile=changelog-1.0.0.xml –url=jdbc:oracle:thin:@localhost:1521:xe –username=RBAC_TEST –password=rbactest1 update [/code] ref: http://www.liquibase.org/documentation/command_line.html

H2 Spring Maven Integration

pom.xml [code] <dependency> <groupId>com.h2database</groupId> <artifactId>h2</artifactId> <version>1.4.193</version> <scope>compile</scope> </dependency> [/code] database.properties [code] orm.connection.driver_class=org.h2.Driver orm.connection.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=TRUE orm.connection.username=sa orm.connection.password= orm.dialect=org.hibernate.dialect.H2Dialect orm.pool_size = 1 orm.show_sql=true orm.hbm2ddl.auto=create/update [/code] spring.xml [code] <beans xmlns="http://www.springframework.org/schema/beans" xmlns:context="http://www.springframework.org/schema/context" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:mvc="http://www.springframework.org/schema/mvc" xsi:schemaLocation=" http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd"> <mvc:annotation-driven /> <mvc:default-servlet-handler /> <mvc:resources mapping="/resources/**" location="/resources/" /> <mvc:resources mapping="/css/**" location="/css/" /> <context:component-scan base-package="com.xxx" />

Useful Oracle DB Hints

Importing data imp userid=baldo_test/baldotest1@localbaldo file=c:\oracle\temp\localbaldo.dmp log=c:\oracle\temp\imp.log   Export data exp  userid=baldo_test/baldotest1@localbaldo file=user1.dmp Dropping a user [code]DROP USER USERNAME CASCADE;[/code] Creating a user [code] CREATE USER USERNAME IDENTIFIED BY "PASSWORD" ACCOUNT UNLOCK; [/code] Truncate Table PL/SQL http://stackoverflow.com/a/11030009 [code] BEGIN FOR c IN (SELECT table_name, constraint_name FROM user_constraints WHERE constraint_type = ‘R’) LOOP EXECUTE IMMEDIATE (‘alter table ‘

HSQL In-memory database hibernate maven config example

maven dependency <properties> <!– PROJECT DEPENDENCIES BEGIN –> <hsqldb.version>2.3.4</hsqldb.version> <!– PROJECT DEPENDENCIES END –> </properties> <dependencies> <dependency> <groupId>org.hsqldb</groupId> <artifactId>hsqldb</artifactId> <version>${hsqldb.version}</version> </dependency> </dependencies> hibernate configuration <?xml version="1.0" encoding="utf-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <property name="hibernate.archive.autodetection">class,hbm</property> <property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property> <property name="hibernate.show_sql">true</property> <property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property> <property name="hibernate.connection.username">sa</property> <property name="hibernate.connection.password">1</property> <property name="hibernate.connection.url">jdbc:hsqldb:mem:testdb</property> <property name="hibernate.hbm2ddl.auto">create</property> <mapping class="de.gameduell.entity.User"