Postgresql SQL recipes

Advertisements

Get URL parameter value with pure javascript

Function:

function getParameterByName(name, url) {
    if (!url) url = window.location.href;
    name = name.replace(/[\[\]]/g, "\\$&");
    var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)"),
        results = regex.exec(url);
    if (!results) return null;
    if (!results[2]) return '';
    return decodeURIComponent(results[2].replace(/\+/g, " "));
}

Usage:

// query string: ?foo=lorem&bar=&baz
var foo = getParameterByName('foo'); // "lorem"
var bar = getParameterByName('bar'); // "" (present with empty value)
var baz = getParameterByName('baz'); // "" (present with no value)
var qux = getParameterByName('qux'); // null (absent)

Via:
http://stackoverflow.com/questions/901115/how-can-i-get-query-string-values-in-javascript

TomEE 7 and Hibernate 5.2 persistence.xml

Load all Hibernate jars and dependencies (exclude slfj 1.6) in tomee/libs folder, and use scope provided in pom.xml.

Property hibernate.temp.use_jdbc_metadata_defaults is set to false due to hibernate startup is slow with postgres in true.

Property “tomee.jpa.factory.lazy” is due Romain Manni-Buc forum statement:

You can disable cdi for hibernate tomee.jpa.cdi=false in the persistence
unit or system properties.

If you want it – was not part if 7.0.1 – you can in the same locations set
tomee.jpa.factory.lazy to true.

Idea is to let hibernate initialize ince cdi is started but let cdi have
jpa entity manager or factory – yes chicken egg problem but spec is written
this way ;).

persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd" >
  <persistence-unit name="geoapi-PU" transaction-type="JTA">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
    <jta-data-source>fmino_geoapi_DS</jta-data-source>
    <exclude-unlisted-classes>false</exclude-unlisted-classes>
    <shared-cache-mode>NONE</shared-cache-mode>
    <properties>
        <property name="hibernate.dialect" value="PostgreSQL9"/>
        <property name="tomee.jpa.factory.lazy" value="true" />
        <property name="hibernate.temp.use_jdbc_metadata_defaults" value="false" />
    </properties>
  </persistence-unit>
</persistence>

pom.xml dependencies:

<dependencies>

        <dependency>
            <groupId>javax</groupId>
            <artifactId>javaee-api</artifactId>
            <version>7.0</version>
            <scope>provided</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.2.10.Final</version>
            <exclusions>
                <exclusion>
                    <groupId>org.hibernate.javax.persistence</groupId>
                    <artifactId>hibernate-jpa-2.1-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.jboss.spec.javax.transaction</groupId>
                    <artifactId>jboss-transaction-api_1.2_spec</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>5.2.10.Final</version>
            <exclusions>
                <exclusion>
                    <groupId>org.hibernate.javax.persistence</groupId>
                    <artifactId>hibernate-jpa-2.1-api</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>org.jboss.spec.javax.transaction</groupId>
                    <artifactId>jboss-transaction-api_1.2_spec</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-spatial -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-spatial</artifactId>
            <version>5.2.10.Final</version>
            <exclusions>
                <exclusion>
                    <groupId>org.postgresql</groupId>
                    <artifactId>postgresql</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.4.1212</version>
            <scope>provided</scope>
        </dependency>

    </dependencies>

Add SSL certificate manually to JVM

Getting this Maven error at dependency resolving:

ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException

Download (via browser) the certificate (cer), then install via Java Keytool.

Locate the “cacerts” file to add the cert in jre\lib\security\cacerts.

C:\"Program Files"\Java\jdk1.8.0_91\bin\keytool.exe -import -alias example -keystore C:\"Program Files"\Java\jdk1.8.0_91\jre\lib\security\cacerts -file primefaces.cer

C:\"Program Files"\Java\jdk1.8.0_91\bin\keytool.exe -import -alias example -keystore C:\"Program Files"\Java\jre1.8.0_91\lib\security\cacerts -file primefaces.cer

When ask for password, type: changeit , When asks for confirmation type: yes

Install local jar maven

# cd to project(pom) directory
cd C:\Users\Fernando\Documents\Dev\Samborondon\VentanillaServer

Install jar with mvn command (Netbeans version)
C:\"Program Files"\"NetBeans 8.1"\java\maven\bin\mvn install:install-file -Dfile=C:\Downloads\all-themes-1.0.10.jar -DgroupId=org.primefaces.themes -DartifactId=all-themes -Dversion=1.0.10 -Dpackaging=jar

Install Postgresql 9.6 on Centos 7

Get correct RPM from http://yum.postgresql.org/repopackages.php, then download rpm with wget and install with rpm -i

# install EPEL repos:
wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-10.noarch.rpm
rpm -ivh epel-release-7-10.noarch.rpm

yum install postgresql96 postgresql96-contrib postgresql96-server postgresql96-devel postgresql96-plpython postgis2_96 postgis2_96-devel postgis2_96-client postgis2_96-utils

export PGSETUP_INITDB_OPTIONS="--pgdata=/var/lib/pgsql/9.6/data/ -E 'UTF-8' --lc-collate='en_US.UTF-8' --lc-ctype='en_US.UTF-8'"

/usr/pgsql-9.6/bin/postgresql96-setup initdb 

systemctl start postgresql-9.6.service
systemctl enable postgresql-9.6.service

passwd postgres

su postgres
psql -c "ALTER USER postgres WITH PASSWORD 'xxxxxx'" -d template1
exit

nano /var/lib/pgsql/9.6/data/postgresql.conf
# listen_addresses='*'

nano /var/lib/pgsql/9.6/data/pg_hba.conf
# host    all             all             127.0.0.1/32            md5
# host    all             all             0.0.0.0/0               md5

systemctl restart postgresql-9.6.service

# Open firewall ports:
firewall-cmd --zone=dmz --add-port=5432/tcp --permanent
firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --reload