Resetting ID Sequence in PostgreSQL

Reading Time: 1 minute — Latest Inserted ID SELECT MAX(IDFIELD) FROM TABLENAME; — The id that will be assigned to the upcoming data tulip. — The result shoult be Latest ID + 1 SELECT nextval(‘TABLENAME_ID_SEQ’); BEGIN; LOCK TABLE TABLENAME IN EXCLUSIVE MODE; SELECT setval(‘TABLENAME_ID_SEQ’, COALESCE((SELECT MAX(IDFIELD)+1 FROM TABLENAME), 1), false); COMMIT;  

Backing up and restoring with pg_dump and psql in PostgreSQL

Reading Time: 1 minute 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

Running Liquibase xml file from command line

Reading Time: 1 minute 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

Reading Time: 1 minute 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" />

HSQL In-memory database hibernate maven config example

Reading Time: 1 minute 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”

Enabling auto reconnect in mysql hibernate spring

Reading Time: 1 minute in your spring config file append the url entry with the below sample   <bean id=”dataSource” class=”org.apache.commons.dbcp.BasicDataSource” destroy-method=”close”> <property name=”driverClassName” value=”${database.driverClassName}” /> <property name=”url” value=”${database.url}?characterEncoding=UTF-8&amp;autoReconnect=true”/> <property name=”username” value=”${database.user}” /> <property name=”password” value=”${database.password}” /> </bean>

HSQL File Connection

Reading Time: 1 minute jdbc address : jdbc:hsqldb:file:///c:/hybris/data/hsqldb/mydb lib and jar location inside the zip – official source of HSQL db: http://sourceforge.net/projects/hsqldb/files/   HSQL Editor free : http://www.sql-workbench.net/downloads.html