A start to programming enterprise applications with Linux, Java and PostgreSQL

Programming in a Linux environment is getting better all the time.

Programming in a Linux environment is getting better all the time. With several development IDEs now available, setting up a project in Linux is just about as easy as with Microsoft. Using Eclipse or Sun's Java tools, development can be done on any platform. I prefer Linux, but the tools work the same on Windows or Apple platforms as well.

After programming many web applications that use Microsoft SQL Server as a database and rewriting applications to put more logic in the database, PostgreSQL seemed like a good open source alternative.

For performance and scalability, having program functionality in the database is a big help. Using database stored procedures and functions allow the program code to be abstracted from the database design. Of course, this usually means your application is not database independent. Over the past few years, I've been moving more and more into database programming with stored procedures and ease of system maintenance.

Many programmers would rather write everything in their program code and use the database tables only to store information. I necessarily do not think this is a good idea. Going this route, the programmer is not taking into account the power of the database engine or learning other hints and options that are available within the RDBMS. The more you work within the database, the more you can learn about scalability and performance issues. It is also easier to fine-tune the database than to continually update and compile program files.

The language of choice for PostgreSQL is PL/PGSQL which is similar to Oracle. With the way PostgreSQL is put together, this language might not be installed. There are a few languages available including SQL, C and Perl. The example in this article with use PL/PGSQL.

Tools

The tools that can be used for PostgreSQL are not quite as developed as with Oracle or MS SQL Server but after some experience, they work fine. PostgreSQL comes with a few command line tools including "psql" which is very similar to Osql, that comes with MS SQL Server.

PgAdmin III (see Figure 1) is similar to SQL Server Enterprise Manager. With this program you can add databases, tables and functions and quickly alter databases in a visual environment. For some commands, however, the graphical interface doesn't work, and you still need to open a SQL Query window and create your own SQL statement manually. For table creation and altering tables and columns, the SQL syntax is always visible, even when using the graphical interface, which can help the user learn the SQL commands (and as a user, you really need to know what is really going on).

Figure 1:


On the Gborg PostgreSQL website (http://gborg.postgresql.org/brose.php?33), there are 33 more tools. PGAccess is also pretty popular but most hardcore programmers probably use a text editor and the command line.

Programming

Starting a new project (this example is a Java Servlet) and getting combination of database functions and Java code working can be quite a challenge at first but once you've done a couple of JDBC calls and functions, it becomes pretty straightforward. First we need a database and a couple of tables (listing 1).

For keys, I like using Sequences (Listing 2). In MS SQL Server, this is like an Identity column but works more like an "object" in the database. Once a Sequence is created, you can use it as a Default value of a column.

Next is the tough part. Like any complex database application, it is best to put at least some of the database logic into stored procedures. In PostgreSQL, these are called functions (see Listing 3). In PostgreSQL, triggers are also a special type of functions. Listing 3 is an function that inserts or updates a new person (entity).

Now, on the Java side (see Listing 4), we need to call this stored procedure and get a new EntityID. The first one may take a little to get working but once you have a couple under your belt, it becomes routine. The main thing to remember is that the first parameter is actually the return parameter. So there is an extra in the list. In my experience, just keeping track of the number of parameters is most important -- you should have the same number of "?" in parameter list of addEntity that is in the stored procedure itself. The wrong number will certainly cause an error. When setting the values, the index "2" is actually the first parameter in the stored procedure.

Also, notice the numeric types. In the Java application, it is fine to use setLong and setInt. For anything other than integers, it is better to use "numeric" in the PostgreSQL function. Also, for strings... If you get an "unknown" type is not correct, this is okay. Using varchar is similar to "unknown". Don't let this fool you; usually the problem is a long or double cannot be converted -- or the parameters do not match.

Using standard JDBC objects and methods, setting the parameters are straight forward. Each "?" counts as a parameter. The first "?" represents the output parameter, then each "?" inside the parenthesis, counts as well. Getting the return value requires some casting due to the difference between SQL and Java types.

Conclusion

Overall, there are some nice things in PL/PGSQL that are not in Microsoft products. Most import, though, is using best practices for design and using normalization forms. Using functions (stored procedures), abstract the database from the application, allowing for an application that is much easier to maintain. In my experience, if this work is done, developing the application becomes easier as well (usually just matching object properties to parameters of database functions).

Getting a nice development environment and being able to create enterprise-level applications in Linux has been a great experience. A little challenging but fun.

In the end, the principles and concepts are exactly the same and there is no reason not to consider a Linux platform.


All Listings for "Programming with PostgreSQL:"

Listing 1:

CREATE TABLE public.entity
(
  createuser bpchar(20) NOT NULL,
  createdate date NOT NULL,
  changeuser bpchar(20),
  changedate date DEFAULT now(),
  entid int8 NOT NULL DEFAULT nextval('seqEntity'::text),
  entname varchar DEFAULT 100,
  entitytype int2,
  fname varchar(100),
  lname varchar(100),
  phone varchar(14),
  fax varchar(14),
  email varchar(150),
  entstatus int2,
  CONSTRAINT "pk_entID""pk_entID" PRIMARY KEY (entid),
  CONSTRAINT uniqueentity UNIQUE (entname, entitytype, fname, lname)
) INHERITS (public.propdft) WITHOUT OIDS;
GRANT ALL ON TABLE public.entity TO postgres WITH GRANT OPTION;
GRANT ALL ON TABLE public.entity TO GROUP """Project Admin""";

-- Table: public.entitylinks
-- DROP TABLE public.entitylinks;
CREATE TABLE public.entitylinks
(
  parententityid int8 NOT NULL,
  childentityid int8 NOT NULL,
  CONSTRAINT pkentlinkspkentlinks PRIMARY KEY (parententityid, childentityid)
) WITHOUT OIDS;
GRANT ALL ON TABLE public.entitylinks TO postgres WITH GRANT OPTION;
GRANT ALL ON TABLE public.entitylinks TO GROUP """Project Admin""";

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

Listing 2:

CREATE SEQUENCE public.seqentity
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 52
  CACHE 1;
GRANT ALL ON TABLE public.seqentity TO postgres WITH GRANT OPTION;
GRANT ALL ON TABLE public.seqentity TO GROUP """Project Admin""";

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

Listing 3:

CREATE OR REPLACE FUNCTION public.addentity(numeric, varchar, int4, varchar, varchar, varchar, varchar, varchar, int4, numeric, varchar)
  RETURNS numeric AS
'DECLARE nv NUMERIC;
BEGIN
IF ($1>0) THEN
        nv := $1;
        UPDATE entity SET entname=$2, entitytype=$3, fname=$4, lname=$5, phone=$6, fax=$7,
        email=$8, entstatus=$9, changeuser=CURRENT_USER, changedate=now() WHERE entID=nv;
ELSE
        nv := NEXTVAL('seqentity');
        INSERT INTO entity (entid, entname, entitytype, fname, lname,phone, fax,email, entstatus, createuser, createdate,changeuser,changedate) 
        VALUES(nv,$2,$3,$4,$5,$6,$7,$8, $9,CURRENT_USER, now(), CURRENT_USER, now());
END IF;
IF ($10>0) THEN
        INSERT INTO entitylinks (parententityid, childentityid) VALUES ($10,nv);
END IF;
RETURN nv;
END;

'
  LANGUAGE 'plpgsql' VOLATILE;
----------------------------------------------------------------------
----------------------------------------------------------------------
    
Listing 4:    
    
    public long addEntity(Connection cn, entity newEntity, long ParentEntityID, String userID) throws SQLException{
         com.jbProject.database.Database db = new com.jbProject.database.Database();
         cn.setAutoCommit(false);
         // Procedure call.
         CallableStatement doEntity = cn.prepareCall("{?=call addEntity(?,?,?,?,?,?,?,?,?,?) }");
         doEntity.registerOutParameter(1, 2); //2 should be Types.BIGINT
         doEntity.setLong(2,newEntity.getEntityID());
         doEntity.setString(3,newEntity.getEntityName());
         doEntity.setInt(4,newEntity.getEntityTypeID());
         doEntity.setString(5,newEntity.getFirstName());
         doEntity.setString(6,newEntity.getLastName());
         doEntity.setString(7,newEntity.getPhone());
         doEntity.setString(8,newEntity.getFax());
         doEntity.setString(9, newEntity.getEmail());
         doEntity.setLong(10, ParentEntityID);
         doEntity.setString(11, userID);
         doEntity.execute();
         String sRet = doEntity.getObject(1).toString();
         long i =  Long.parseLong(sRet);
         doEntity.close();    
         cn.commit();
         return i;
    }


About the author: Roy Hoobler is an independant consultant working in New Jersey and the New York metropolitan area focusing on Linux and alternatives to licensed software. He also is lead on some open source projects and does general database consulting.

This was first published in May 2004

Dig deeper on Open source Web and application servers

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close