Java

Java is a set of computer software and specifications developed by Sun Microsystems, which was later acquired by the Oracle Corporation, that provides a system for developing application software and deploying it in a cross-platform computing environment. Java is used in a wide variety of computing platforms from embedded devices and mobile phones to enterprise servers and supercomputers.

Spring Logo

Spring Framework

The Spring Framework provides a comprehensive programming and configuration model for modern Java-based enterprise applications - on any kind of deployment platform. A key element of Spring is infrastructural support at the application level: Spring focuses on the "plumbing" of enterprise applications so that teams can focus on application-level business logic, without unnecessary ties to specific deployment environments.

Hibernate Logo

Hibernate Framework

Hibernate ORM is an object-relational mapping framework for the Java language. It provides a framework for mapping an object-oriented domain model to a relational database.

Showing posts with label Postgres. Show all posts
Showing posts with label Postgres. Show all posts

Sunday, January 25, 2015

How to display all tables in different databases

In this article, we will see how to connect to the different databases(MySQL, Oracle, PostgreSQL, DB2) and how to display the all table names. 


MySQL

Connect to the database:
mysql [-u username] [-h hostname] database-name

To list all databases, in the MySQL prompt type:
show databases

Then choose the right database:
use <database-name>

List all tables in the database:
show tables

Describe a table:

desc <table-name>

Oracle

Connect to the database: 
connect username/password@database-name;

To list all tables owned by the current user, type:
select tablespace_name, table_name from user_tables;

To list all tables in a database:
select tablespace_name, table_name from dba_tables;

To list all tables accessible to the current user, type:
select tablespace_name, table_name from all_tables;

To describe a table:
desc <table_name>;

PostgreSQL

Connect to the database:
psql [-U username] [-h hostname] database-name

To list all databases, type either one of the following:
list

To list tables in a current database, type:
\dt

To describe a table, type:
\d <table-name>

DB2

Connect to the database:
db2 connect to <database-name>;

List all tables:
db2 list tables for all;

To list all tables in selected schema, use:
db2 list tables for schema <schema-name>;

To describe a table, type:
db2 describe table <table-schema.table-name>;
Happy coding...

Tuesday, November 26, 2013

Install PostgreSQL9.1.9 Database Server on Fedora 17






"PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness."  

The following are the steps to install PostgreSQL9.1.9 in Fedora17.

Step-1: Change to root user

[ranga@ranga ~]$ su
Password:
[root@ranga ranga]#

Step-2: Install PostgreSQL Server
[root@ranga ~]# yum install postgresql-server

Step-3: Configure PostgreSQL Server

[root@ranga ~]# postgresql-setup initdb
Initializing database ... OK 
 
Step-8: Enable and Start PostgreSQL Server

Start server:
[root@ranga ~]# systemctl start postgresql.service

Enable postgresql:
[root@ranga ~]# systemctl enable postgresql.service


Creating a Database
Connect as user mypguser to new database

[root@ranga ~]# sudo su - postgres

Check that you can login.
-bash-4.2$ psql
psql (9.1.6)
Type "help" for help.

postgres=#

bash-4.2$ createdb RangaDB

bash-4.2$ psql RangaDB
psql (9.1.9)
Type "help" for help.

 

Create Database User

RangaDB=# CREATE ROLE ranga WITH SUPERUSER LOGIN PASSWORD 'ranga';
CREATE ROLE

or

RangaDB=# CREATE USER ranga WITH PASSWORD 'ranga';
CREATE ROLE

RangaDB=# psql -h localhost -U ranga ranga
RangaDB-#

If you get errors like:
psql: FATAL:  Peer authentication failed for user "ranga"
edit pg_hba.conf in  /var/lib/pgsql/data/pg_hba.conf

change peer and ident to md5.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident
to

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5


Quit from the database
RangaDB=# \q

bash-4.2$ psql -d RangaDB -U ranga
could not change directory to "/root"
Password for user ranga:
psql (9.1.9)
Type "help" for help.

RangaDB=# create table test(id int);
CREATE TABLE
RangaDB=# select * from test;
 id
----
(0 rows)

RangaDB=# insert into test values(1);
INSERT 0 1
RangaDB=# select * from test;
 id
----
  1
(1 row)

RangaDB=# drop table test;
DROP TABLE
 
Changing Password:

[root@ranga trunk]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
Install PostgreSQL GUI Client

If you want to manage PostgreSQL using a GUI client, you need to install pgadmin3.
[root@ranga ranga]# sudo yum install pgadmin3

To list available packages: 
[root@ranga ranga]# yum list postgres*

Removing (or) Uninstalling PostgreSQL:

 
[ranga@ranga trunk]$ yum erase postgresql*
yum remove postgresql postgresql-devel postgresql-server
 

[root@ranga ~]# rpm -e --nodeps postgresql-libs

PostgreSQL Upgrade:
http://docs.1h.com/PostgreSQL_Upgrade


Installing pgAdmin:

The command line administration will be good for the people who has full experience on PostgreSQL, but for the beginner pgAdmin will be best option to manage the databases. By default pgAdmin packages available on fedora repository, so just issue the following command to install it.

Install pgAdmin3:
[root@ranga ranga]# yum install pgadmin3

Start pgAdmin3:
[root@ranga ranga]# pgadmin3

Now Connect to the database server using pgAdmin.

------------------------------------------------------------------------------------------------

Drop all tables:

DROP SCHEMA public CASCADE;  
CREATE SCHEMA public; 

Thursday, November 21, 2013

Hibernate4 example by using PostgreSQL DB.

Prerequisite requirement

- Installed and configured Maven, MySQL, Eclipse IDE.
- See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.dpuf

Prerequisite requirement

- Installed and configured Maven, MySQL, Eclipse IDE.
- See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.dpufPrerequisite requirement
- Installed and configured Maven, MySQL, Eclipse IDE.
- See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.dpu

Prerequisite requirement

- Installed and configured Maven, MySQL, Eclipse IDE.
- See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.
Step1: Create a Java project with Maven

Run the following command in terminal or command prompt to generate a standard java project.
Create a quick project file structure with Maven command ‘mvn archetype:generate‘ - See more at: http://www.developer.am/documentation/hibernate/?page=maven-spring-hibernate-mysql-example#sthash.8UVHMaBr.dpuf

[ranga@ranga gradle]$ mvn archetype:generate -DgroupId=com.ranga -DartifactId=HibernateExampleUsingPostgreSQL -DarchetypeArtifactId=maven-archetype-quickstart -DinteractiveMode=false

Project structure:

HibernateExampleUsingPostgreSQL/
├── pom.xml
└── src
    ├── main
    │   └── java
    │       └── com
    │           └── ranga
    │               └── App.java
    └── test
        └── java
            └── com
                └── ranga
                    └── AppTest.java

9 directories, 3 files

Step2: Compile and Run the newly created Java project.

[ranga@ranga HibernateExampleUsingPostgreSQL]$ mvn clean install

[ranga@ranga HibernateExampleUsingPostgreSQL]$ java -cp target/HibernateExampleUsingPostgreSQL-1.0-SNAPSHOT.jar com.ranga.App

Output:
Hello World!

Step3: Convert to Eclipse Project
[ranga@ranga HibernateExampleUsingPostgreSQL]$ mvn eclipse:eclipse

Step4: Import converted Project into Eclipse

OpenEclipse IDE, Choose File –> Import –> General folder, Existing Projects into Workspace –>Choose your project folder location.

Step5: Add the Hibernate4.x and Oracle dependencies

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">   
   
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.ranga</groupId>
    <artifactId>HibernateExampleUsingPostgreSQL</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>HibernateExampleUsingPostgreSQL</name>
    <url>http://maven.apache.org</url>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <!-- JBoss repository for Hibernate -->
    <repositories>
        <repository>
            <id>JBoss repository</id>
            <url>http://repository.jboss.org/nexus/content/groups/public/</url>
        </repository>
    </repositories>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.8.2</version>
            <scope>test</scope>
        </dependency>

        <!-- Hibernate framework -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>4.2.7.Final</version>
        </dependency>

        <dependency>
            <groupId>javassist</groupId>
            <artifactId>javassist</artifactId>
            <version>3.12.1.GA</version>
        </dependency>

        <dependency>
            <groupId>postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.1-901.jdbc4</version>
        </dependency> 


    </dependencies>

</project>

Step6: Create a resources folder

Create a new folder called resources under src/main. Here we are adding the Configuration files and Mapping files.

Project structure:

HibernateExampleUsingPostgreSQL/
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── ranga
    │   │           └── App.java
    │   └── resources
    ├── pom.xml
    └── test
        └── java
            └── com
                └── ranga
                    └── AppTest.java

10 directories, 6 files


Step7: Hibernate configuration file
Create a Hibernate configuration file and put under the resources root folder, “src/main/resources/hibernate.cfg.xml“. Add the following content.

hibernate.cfg.xml

<?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>
        <!-- Database connection settings -->
        <property name="connection.driver_class">org.postgresql.Driver</property>
        <property name="connection.url">jdbc:postgresql://127.0.0.1:5432/RangaDB</property>
        <property name="connection.username">ranga</property>
        <property name="connection.password">ranga</property>

        <!-- JDBC connection pool (use the built-in) -->
        <property name="connection.pool_size">1</property>

        <!-- SQL dialect -->
        <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>

        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>

        <!-- Disable the second-level cache -->
        <property name="cache.provider_class">org.hibernate.cache.internal.NoCacheProvider</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="show_sql">true</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create</property>

        <mapping resource="com/ranga/mapping/Person.hbm.xml" />
    </session-factory>
</hibernate-configuration>

Step8: Hibernate Mapping file

Create a Person.hbm.xml file and put it in “src/main/resources/com/ranga/mapping“ folder. 

Person.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="com.ranga.mapping">
    <class name="Person" table="Persons">
        <id name="id" column="Person_ID" type="long">
            <generator class="native"/>                     
        </id>
        <property name="firstName" column="FirstName" type="string"/>
        <property name="lastName" column="LastName" type="string"/>
        <property name="age" column="Age" type="integer"/>      
    </class>
</hibernate-mapping>

Project structure:

HibernateExampleUsingPostgreSQL/
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── ranga
    │   │           └── App.java
    │   └── resources
    │       ├── com
    │       │   └── ranga
    │       │       └── mapping
    │       │           └── Person.hbm.xml
    │       └── hibernate.cfg.xml
    ├── pom.xml
    └── test
        └── java
            └── com
                └── ranga
                    └── AppTest.java

13 directories, 8 files
 

Step9: POJO class or Model class

Create a Person.java file and put it in “src/main/java/com/ranga/mapping”

Person.java

package com.ranga.mapping;

import java.io.Serializable;

public class Person implements Serializable {
   
    private long id;
    private String firstName;
    private String lastName;
    private int age;
   
    public long getId() {
        return id;
    }
    public void setId(long id) {
        this.id = id;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    @Override
    public String toString() {
        return "Person [id=" + id + ", firstName=" + firstName + ", lastName="
                + lastName + ", age=" + age + "]";
    }           
}
 
Step10: Hibernate Utility class

Create a HibernateUtil.java class to take care of Hibernate start up and retrieve the session easily. Create a util folder and put this file in it, “src/main/java/com/ranga/util”.

HibernateUtil.java

package com.ranga.util;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;

public class HibernateUtil {
        private static final SessionFactory sessionFactory;
        private static final ServiceRegistry serviceRegistry;
        static {
                try {
                        // Create the SessionFactory from hibernate.cfg.xml
                        Configuration configuration = new Configuration();
                        configuration.configure();
                        serviceRegistry = new ServiceRegistryBuilder().applySettings(
                                        configuration.getProperties()).buildServiceRegistry();
                        sessionFactory = configuration.buildSessionFactory(serviceRegistry);
                } catch (Throwable ex) {
                        // Make sure you log the exception, as it might be swallowed
                        System.err.println("Initial SessionFactory creation failed." + ex);
                        throw new ExceptionInInitializerError(ex);
                }
        }

        public static SessionFactory getSessionFactory() {
                return sessionFactory;
        }

        public static void closeSessionFactory() {
        if (sessionFactory != null)
            sessionFactory.close();
    }
}


Step 11: Client Application

App.java

package com.ranga;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.ranga.mapping.Person;
import com.ranga.util.HibernateUtil;

public class App {
    public static void main(String[] args) {
        SessionFactory sessionFactory = HibernateUtil.getSessionFactory();
        Session session = sessionFactory.openSession();
        session.beginTransaction();
        Person p1 = new Person();
        p1.setFirstName("ranga");
        p1.setLastName("reddy");
        p1.setAge(25);
        long personId = (Long) session.save(p1);
        session.getTransaction().commit();
       
        Person person = (Person) session.get(Person.class, personId);       
        System.out.println(person);
               
        session.close();
    }
}

Project structure:

HibernateExampleUsingPostgreSQL/
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── ranga
    │   │           ├── App.java
    │   │           ├── mapping
    │   │           │   └── Person.java
    │   │           └── util
    │   │               └── HibernateUtil.java
    │   └── resources
    │       ├── com
    │       │   └── ranga
    │       │       └── mapping
    │       │           └── Person.hbm.xml
    │       └── hibernate.cfg.xml
    ├── pom.xml
    └── test
        └── java
            └── com
                └── ranga
                    └── AppTest.java





 

15 directories, 10 files

Now the Hibernate Application is ready.

Step12: Run the hibernate client application

Person [id=1, firstName=ranga, lastName=reddy, age=25]

Download