java web service working with PostgreSQL database

my code is written in java, and I am really new to java, so i hope my explanations are correct: i have a java written web service that works with a data base. the data base types can be PostgreSQL and mysql. so my webservice w开发者_开发问答orks with the JDBC connection for both data bases. one of my data base tables is table urls, for postgressql it is created like this:

CREATE TABLE urls ( 
id serial NOT NULL primary key, 
url text not null,   
type integer not null);

for mysql it is creates like this:

CREATE TABLE IF NOT EXISTS URLS ( 
id INTEGER primary key NOT NULL AUTO_INCREMENT, 
url varchar (1600) NOT NULL,   
type INTEGER NOT NULL );

when I try inserting data to this table I use an entity called urls: this entity has:

    private BigDecimal id;
    private String url;
    private BigInteger type;

when I try to insert values to the urls table I assign values to the urls entity, while leaving the id as NULL since it is AUTO_INCREMENT for mysql and serial for postgres. the query works for my sql, but fails for postgress. in the postgres server log I can see the following error:

null value in column "id" violates not-null constraint

cause I sends NULL as the id value. I found out that in order for the query to work I should use this query:

INSERT INTO URLS(ID, TYPE, URL) VALUES(DEFAULT, 1, 'DED'); or this one:
INSERT INTO URLS(TYPE, URL) VALUES(1, 'DED'); or this one:

instead of this one, that I use:

INSERT INTO URLS(ID, TYPE, URL) VALUES(NULL, 1, 'DED');

so my question is,

1. how do I assign the DEFAULT value to a BigDecimal value in java ?
2. is removing the id from my entity is the right way to go ?
3. how can I make sure that any changes I do to my code wont harm the mysql or any other data base that I will use ?

If you specify the column name in the insert query then postgres does not take the default value. So you should use your second insert query.

INSERT INTO URLS(TYPE, URL) VALUES(1, 'DED');

This syntax is correct for both postgres and MySQL.

This should resolve your question (1) and (3). For (2) DO NOT delete the id field from your entity. This id is going to be your link to the database row for a specific object of the entity.

1 - I think it is proper to use Long or long types instead of BigDecimal for id fields.

2 - Yes it generally helps, but it lowers portability. BTW, using an ORM framework like Hibernate may be a good choice.

3 - Integration testing usually helps and you may want to adopt TDD style development.

When using this statement:

INSERT INTO URLS(ID, TYPE, URL) VALUES(NULL, 1, 'DED');

you are telling the database that you want to insert a NULL value into the column ID and Postgres will do just that. Unlike MySQL, PostgreSQL will never implicitely replace a value that you supply with something totally different (actually all DBMS except MySQL work that way - unless there is some trigger involved of course).

So the only solution to is to actually use an INSERT that does not supply a value for the ID column. That should work on MySQL as well.