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&amp;nbsp;–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 the data

[code]

psql -h localhost -d databasename -U username -f file.sql

[/code]

this query will restore the data from the sql query into the selected database

For more information consult the PostgreSQL Documentation at https://www.postgresql.org/docs/current/static/app-psql.html

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" />
<context:annotation-config />

<!– Property loader for database –>
<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
p:location="classpath:database.properties" />

<!– Hibernate connection configuration –>
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${orm.connection.driver_class}" />
<property name="url" value="${orm.connection.url}" />
<property name="username" value="${orm.connection.username}" />
<property name="password" value="${orm.connection.password}" />
</bean>

<!– Hibernate configuration settings –>
<bean id="sessionFactory"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="packagesToScan" value="com.xxx.entity" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${orm.dialect}</prop>
<prop key="hibernate.pool_size">"${orm.pool_size}</prop>
<prop key="hibernate.show_sql">${orm.show_sql}</prop>
<prop key="hibernate.hbm2ddl.auto">${orm.hbm2ddl.auto}</prop>
</props>
</property>
</bean>

<!– Hibernate Transaction –>
<tx:annotation-driven transaction-manager="transactionManager" />

<bean id="transactionManager"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactory" />
</bean>

<!– H2 Web Console –>
<bean id="h2Server" class="org.h2.tools.Server" factory-method="createTcpServer"
init-method="start" destroy-method="stop" depends-on="h2WebServer">
<constructor-arg value="-tcp,-tcpAllowOthers,-tcpPort,21092" />
</bean>
<bean id="h2WebServer" class="org.h2.tools.Server" factory-method="createWebServer"
init-method="start" destroy-method="stop">
<constructor-arg value="-web,-webAllowOthers,-webPort,21082" />
</bean>

<bean
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix">
<value>/WEB-INF/jsp/</value>
</property>
<property name="suffix">
<value>.jsp</value>
</property>
</bean>

<!– Localization Configuration –>
<bean id="messageSource"
class="org.springframework.context.support.ReloadableResourceBundleMessageSource">
<property name="basename" value="classpath:/i18n/messages" />
<property name="defaultEncoding" value="UTF-8" />
</bean>

<bean id="localeResolver"
class="org.springframework.web.servlet.i18n.SessionLocaleResolver">
<property name="defaultLocale" value="en" />
</bean>

<bean id="cookieResolver"
class="org.springframework.web.servlet.i18n.CookieLocaleResolver">
<property name="defaultLocale" value="en" />
<property name="cookieName" value="my-locale-cookie" />
<property name="cookieMaxAge" value="3600" />
</bean>

<mvc:interceptors>
<bean class="org.springframework.web.servlet.i18n.LocaleChangeInterceptor">
<property name="paramName" value="lang" />
</bean>
</mvc:interceptors>

</beans>
[/code]

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" />
        <mapping class="de.gameduell.entity.Statistics" />
    </session-factory>
</hibernate-configuration>

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