Resetting ID Sequence in PostgreSQL

— 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

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

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" />

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”

Enabling auto reconnect in mysql hibernate spring

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>