MySQL ALTER TABLE ADD COLUMN IF NOT EXISTS

I wrote a MySQL function and procedure that will add a database column unless it exists already. The following code would add 3 fields to the template table, unless they already exist:

-- add fields to template table to support ignoring extra data 
-- at the top/bottom of every page

CALL addFieldIfNotExists ('template', 'firstPageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageFooterBeginY', 'INT NOT NULL DEFAULT 792');

Here is the actual code for the above procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS addFieldIfNotExists 
$$

DROP FUNCTION IF EXISTS isFieldExisting 
$$

CREATE FUNCTION isFieldExisting (table_name_IN VARCHAR(100), field_name_IN VARCHAR(100)) 
RETURNS INT
RETURN (
    SELECT COUNT(COLUMN_NAME) 
    FROM INFORMATION_SCHEMA.columns 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = table_name_IN 
    AND COLUMN_NAME = field_name_IN
)
$$

CREATE PROCEDURE addFieldIfNotExists (
    IN table_name_IN VARCHAR(100)
    , IN field_name_IN VARCHAR(100)
    , IN field_definition_IN VARCHAR(100)
)
BEGIN

    -- http://javajon.blogspot.com/2012/10/mysql-alter-table-add-column-if-not.html

    SET @isFieldThere = isFieldExisting(table_name_IN, field_name_IN);
    IF (@isFieldThere = 0) THEN
    
        SET @ddl = CONCAT('ALTER TABLE ', table_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ;
        SET @ddl = CONCAT(@ddl, ' ', field_name_IN);
        SET @ddl = CONCAT(@ddl, ' ', field_definition_IN);

        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
    END IF;

END;
$$

NetBeans Writes My Code for Me

I'm a big fan of Eclipse.  I use it for Perl, PHP, HTML, CSS, JavaScript, ColdFusion ... but when it comes to hardcore Java development, I prefer NetBeans.  NetBeans refactoring tools are superb, and these are the ones I use the most:

ALT+ENTER for red and yellow squiggly underlines.

NetBeans reports all kinds of hints and errors as you're coding with little yellow and ride lines.  Red lines usually mean it won't compile.  Yellow lines usually indicate a coding style suggestion.

Either way pressing ALT+ENTER will usually provide me with options.

Refactor > Encapsulate Fields

Most classes need getters and setters.  NetBeans writes them for me.

ALT+INSERT for more code writing magic

Alt+Insert gives me a menu of things that the IDE can generate for you.  I use it mostly for constructors and override methods.

Instant JavaDocs

Event though JavaDocs come before the classes and methods they describe, if I define my method first, the JavaDocs will write more of the documentation for me.  I put my cursor on the line above the method...

I type /** and press ENTER.

NetBeans writes as much of the JavaDoc as it can based on what it knows about the method.  Then I fill in the rest...

More!

CTRL+SPACE for autocomplete.  I use it everywhere.  In fact, when I use it in a class but not in a method, it provides me a menu similar to ALT+INSERT where it will actually write constructors and override methods for me.

CTRL+R allows me to refactor and rename anything.  I have to be careful when I use this on a shared code repository.  It's easy to cause conflicts.

CTRL+B takes me to the definition of anything, method, class, enum, etc.

ALT+F7 tells me every place a class, interface or method is used.  This one is my favorite.

BigDecimal equals ZERO

So I've read that .equals() may not always work on a BigDecimal value, and that I should use .compareTo() instead, but I guess I never really believed it before, until today.

package sandbox;
import java.math.BigDecimal;
public class BigDecimalEqualsCompareTo {

    public static void main(String[] args) {
        
        BigDecimal x = BigDecimal.ZERO;
        BigDecimal y = BigDecimal.valueOf((double) 0);
        
        x = x.add(y);

        if (x.equals(BigDecimal.ZERO)) {
            System.out.println(".equals worked!");
        }
        else {
            System.out.println(".equals FAILED!");
        }
        
        if (x.compareTo(BigDecimal.ZERO) == 0) {
            System.out.println(".compareTo worked!");
        }
        else {
            System.out.println(".compareTo FAILED!");
        }
        
    }
    
}

run:
.equals FAILED!
.compareTo worked!

.
The above code demonstrates one instance in which .compareTo will work when .equals will fail.
.
The value of y is "0.0" where BigDecimal.ZERO is actually "0", which you and I both know is the same number but because the scale of y is now 1, y is considered a different BigDecmal than ZERO. If you set the scale of both variables to the same, then .equals will work again, but at that point isn't easier to simply use .compareTo?

I think in any workplace environment there is a continuum of productivity versus courtesy that could improve relations when considered prior to asking another for assistance.  If I'm rushing out to bother my coworkers as soon as I find something I don't understand, I'm not only reducing their productivity while running the risk of annoying them, I'm depriving myself of an opportunity to learn. On the other hand, if I spend days trying to solve a problem that I could have had solved in fifteen minutes if I had asked for help, then I'm wasting my time and costing the company money, which in the long run means there's less money for our salaries, raises, etc. It hurts us all.  So how do I know where to draw the line?

For me, the checklist below serves to help me make sure I've put forth a reasonable effort to solve a problem on my own before I reach out for assistance from others:

---

Before I ask for help... 

(in any order)

1.  I read the error message. (if there is one)
NetBeans and Eclipse both provide pretty detailed error messages, and although some of them may seem cryptic to those new to Java, familiarity with these messages will increase my productivity in the long run.

2.  I try to find relevant code.
Is there already code somewhere else in the project that does something similar to what I'm trying to accomplish?  Can I go read that code and see if I can figure out what I'm doing wrong?  Can I refactor the existing code into a reusable method or class and just use that rather than reinventing the wheel?  What else is out there?

3.  I search the internet.
My favorite search engine is Google.  I start with the technology I'm using, then a few keywords that I think are relevant to the problem I'm trying to solve, and if that doesn't work I try using a different set of words to search for an answer.  If there's an error message, I'll often type or even copy/paste the error message into Google, removing any words that are specific to my project code.

4.  I review the context.
Often times the cause of a problem is located elsewhere in code from where the problem is occurring.  I make sure I understand the code I'm working on.  If there are variables or methods I don't understand I find out where they are declared.  Eclipse and NetBeans both offer shortcut keys to navigate the code for me, so I learn them (F3 and CTRL+B).  I find where my fields, methods and classes are used (CTRL+SHIFT+G and ALT+F7).  Most of all I make sure I understand the code I'm working with to the best of my ability before I ask for help with it.

5.  I try the debugger.  (if possible)
Whenever possible I'll step through the code and watch the variables to see what's going on under the covers.  Sometimes I'll add some variables into the code a bit to make debugging easier.

6.  I try the documentation.
If my internet searches didn't bring me there already, I try the documentation for the commands I'm using.  In NetBeans, ALT+F1 will bring me to javadocs.  If not, I can type into google the name of the technology and the class and possibly the method for which I'm i'm looking.  If my question is about SQL or another technololgy it helps to be specific.  For example, if I'm using PostgreSQL "PostgreSQL CREATE TABLE" will give me better results than just "CREATE TABLE".

7.  I walk down the stack trace.  (if there is one)
If I'm lucky enough to have a stack trace, I'll look at more than just one place in code from which the error originated.  A null pointer exception often occurs because a null value was passed into a routine four levels up the stack, but the object is never used so the error doesn't occur until we're far, far away from the offending variable.  It helps to keep looking.

---

If I've done all (or most) of the above, it's probably taken me about two hours to try to figure this out on my own.  If I've spent at least two hours trying to solve my problem then I could consider asking for help, but honestly that depends on whether or not I've reached a dead end.  In most programming environments if I have internet access I have the resources I need to solve the problem at hand.  Legitimately, then, questions may arise from the data model or software architecture that we've chosen to implement.  If it's proprietary and I don't understand it, then it makes sense to ask.

Hope this helps!

for loops - arrays and lists

Dedicated to my coworkers who are transitioning from HIBOL to Java. I respect your courage.

So the basic gist of this post is that all of the following examples do basically the same thing. That way we can see what's similar and different. Hopefully this will help us all better understand the for loop.

while - loop through an array

        
        String[] names = new String[] { "Joe", "Sally", "Henry" };
        
        int i = 0;
        while (i < names.length) {
            System.out.println(names[i]);
            i++;
        }

run:
Joe
Sally
Henry

for - loop through an array

        
        String[] names = new String[] { "Joe", "Sally", "Henry" };
        
        for (int i = 0; i < names.length; i++) {
            System.out.println(names[i]);
        }

run:
Joe
Sally
Henry

for/in - loop through an array

        
        String[] names = new String[] { "Joe", "Sally", "Henry" };
        
        for (String s : names) {
            System.out.println(s);
        }

run:
Joe
Sally
Henry

while - loop through a list

        

        java.util.List names = new java.util.ArrayList();
        names.add("Joe");
        names.add("Sally");
        names.add("Henry");

        int i = 0;
        while (i < names.size()) {
            System.out.println(names.get(i));
            i++;
        }

run:
Joe
Sally
Henry

for - loop through a list

        

        java.util.List names = new java.util.ArrayList();
        names.add("Joe");
        names.add("Sally");
        names.add("Henry");

        for (int i = 0; i < names.size(); i++) {
            System.out.println(names.get(i));
        }

run:
Joe
Sally
Henry

for/in - loop through a list

        

        java.util.List names = new java.util.ArrayList();
        names.add("Joe");
        names.add("Sally");
        names.add("Henry");

        for (String s : names) {
            System.out.println(s);
        }

run:
Joe
Sally
Henry

note that the for/in loop syntax is exactly the same for looping through an array as it is for looping through a list:

        
        for (String s : names) {
            System.out.println(s);
        }

Technically, any class that implements the Iterable interface can be used in a for/in statement, but we'll save that discussion for another day :)