JDBC Metadata - constructing CREATE of existing table..

Hi,
can anyone tell me where to find some documents about JDBC Metadata. Something about using it in a wider
scope. JDBC API is not enough.
I have to code program part which reads table metadata from DBMS and constructs DDL CREATE statement
of table definition.
Or is there any other way to find out that CREATE statement.
Thanks.

Hi,
u can get some info and understanding about MetaData in the below mentioned link
http://members.aol.com/kgb1001001/Articles/JDBCMetadata/JDBC_Metadata.htm
Raj

I am facing a similar requirement on a current project. I have a Column class that is very, very helpful in building a CREATE TABLE statement. If I find that, or someone can tell me that, there is no way to upload the file, I'll just post it as code, all 276 lines of it (it's fully documented). Or, with your permission, I could send it to you via email. By the way, I also have a unit test for it.
Craig

Miroslav,
I sent a private message in response to your private message re: this thread. View your profile to see your private messages, as my profile shows that you have not yet read the private message that I sent.
Good luck,
Craig

check out the subject also at http://www.coderanch.com/t/297040/JDBC/java/do-you-find-out-if

Craig, can we get a copy of the Column class too?
Thanks,
Sanjay

I've looked at the FAQs; they state that attachments are not supported. Furthermore, I don't have a place on the web where I can place the file now. So, here's the code for copying and pasting:

/**
 * Implements an immutable <code>Column</code> whose <code>String</code>
 * representation is useful for the construction of SQL Data Definition
 * Language (DDL) CREATE TABLE queries.
 *
 * @author $Author$
 * @version $Revision$
 */
public final class Column
{
    /** The minimum value for precision and scale. */
    private static final int MIN_VALUE = 0;
    /** Constant to aid in efficient string representation. */
    private static final String L_PAREN = "(";
    /** Constant to aid in efficient string representation. */
    private static final String R_PAREN = ")";
    /** Constant to aid in efficient string representation. */
    private static final String COMMA = ",";
    /** Constant to aid in efficient string representation. */
    private static final String NOT_ALLOW_NULL = " NOT NULL";
    /** The JDBC type of this column. */
    private final int type;
    /** The name of this column as it appears in a SQL query. */
    private final String name;
    /** The vendor name that corresponds to the <code>type</code>. */
    private final String typeName;
    /** Indicates whether precision is required. */
    private final boolean requiresPrecision;
    /** Indicates whether scale is required. */
    private final boolean requiresScale;
    /** The precision of this column. */
    private final int precision;
    /** The scale of this column. */
    private final int scale;
    /** Indicates whether this column allows NULL values. */
    private final boolean allowNull;
    /** Provide a lazily initialized hash code. */
    private volatile int hashCode = 0;
    /**
     * Constructs a new <code>Column</code>.
     *
     * @param type The <code>java.sql.Types</code> constant representing this
     *             column's data type
     * @param name The name of this column as it appears in a SQL query
     * @param typeName The name that corresponds to the <code>type</code>
     * @param createParams The value of the "CREATE_PARAMS" column of the
     *        <code>java.sql.ResultSet</code> returned by
     *        <code>java.sql.DatabaseMetaData.getTypeInfo()</code>
     * @param precision The precision such as the 5 in
     *        <code>NUMERIC(5,2)</code> or the size such as the 3 in
     *        <code>CHAR(3)</code>
     * @param scale The scale such as the 2 in <code>NUMERIC(5,2)</code>
     * @param allowNull Indicates whether this column allows NULL
     *
     * @throws <code>NullPointerException</code> if <code>name</code> is
     *         <code>null</code>
     * @throws <code>NullPointerException</code> if <code>typeName</code> is
     *         <code>null</code>
     * @throws <code>IllegalArgumentException</code> if either
     *         <code>precision</code> or <code>scale</code> is less than 0
     */
    public Column(int type, String name, String typeName,
                  String createParams, int precision, int scale,
                  boolean allowNull)
    {
        if(name == null)
        {
            throw new NullPointerException("name can't be null");
        }
        if(typeName == null)
        {
            throw new NullPointerException("typeName can't be null");
        }
        if(precision < MIN_VALUE || scale < MIN_VALUE)
        {
            throw new IllegalArgumentException("precision and scale must be >= "
                                               + MIN_VALUE);
        }
        this.type = type;
        this.name = name;
        this.typeName = typeName;
        if(createParams == null)
        {
            requiresPrecision = false;
            requiresScale = false;
        }
        else
        {
            requiresPrecision = ((createParams.indexOf("precision") > -1) ||
                                 (createParams.indexOf("length") > -1));
            requiresScale = (createParams.indexOf("scale") > -1);
        }
        this.precision = precision;
        this.scale = scale;
        this.allowNull = allowNull;
    }
    /**
     * Obtain this column's type.
     *
     * @return this column's <code>java.sql.Types</code> constant value
     */
    public final int type()
    {
        return type;
    }
    /**
     * Obtain this column's name.
     *
     * @return this column's name
     */
    public final String name()
    {
        return name;
    }
    /**
     * Obtain this column's type name.
     *
     * @return this column's type name
     */
    public final String typeName()
    {
        return typeName;
    }
    /**
     * Indicates whether this column requires precision when used in a CREATE
     * TABLE query in SQL.
     *
     * @return <code>true</code> if precision is required; <code>false</code>
     *         otherwise
     */
    public final boolean requiresPrecision()
    {
        return requiresPrecision;
    }
    /**
     * Indicates whether this column requires scale when used in a CREATE
     * TABLE query in SQL.
     *
     * @return <code>true</code> if scale is required; <code>false</code>
     *         otherwise
     */
    public final boolean requiresScale()
    {
        return requiresScale;
    }
    /**
     * Obtain this column's precision.
     *
     * @return this column's precision
     */
     public final int precision()
     {
        return precision;
    }
    /**
     * Obtain this column's scale.
     *
     * @return this column's scale
     */
    public final int scale()
    {
        return scale;
    }
    /**
     * Indicates whether this column allows NULL.
     *
     * @return <code>true</code> if this column allows NULL; <code>false</code>
     *          otherwise
     */
    public final boolean allowNull()
    {
        return allowNull;
    }
    /**
     * Represents this column as a <code>String</code>.
     * <p>
     * The following examples are typical:
     * <p>
     * "id INTEGER NOT NULL"
     * "fname CHAR(12)"
     * "price decimal(5,2)"
     *
     * @return this column as a <code>String</code>
     */
    public final String toString()
    {
        StringBuffer sb = new StringBuffer(name);
        sb.append(" ");
        sb.append(typeName);
        if(requiresPrecision)
        {
            sb.append(L_PAREN + precision);
            if(requiresScale)
            {
                sb.append(COMMA + scale);
            }
            sb.append(R_PAREN);
        }
        if(!allowNull)
        {
            sb.append(NOT_ALLOW_NULL);
        }
        return sb.toString();
    }
    /**
     * Indicates whether some other object is "equal to" this one.
     *
     * @param   obj   the reference object with which to compare.
     * @return  <code>true</code> if this object is the same as the obj
     *          argument; <code>false</code> otherwise.
     * @see     java.lang.Boolean#hashCode()
     * @see     java.util.Hashtable
     */
    public boolean equals(Object obj)
    {
        if(obj == this)
            return true;
        if(!(obj instanceof Column))
            return false;
        Column that = (Column) obj;
        return ((this.type == that.type) &&
                (this.name.equals(that.name)) &&
                (this.typeName.equals(that.typeName)) &&
                (this.requiresPrecision == that.requiresPrecision) &&
                (this.requiresScale == that.requiresScale) &&
                (this.precision == that.precision) &&
                (this.scale == that.scale) &&
                (this.allowNull == that.allowNull));
    }
    /**
     * Returns a hash code value for the object. This method is
     * supported for the benefit of hashtables such as those provided by
     * <code>java.util.Hashtable</code>.
     *
     * @return  a hash code value for this object.
     * @see     java.lang.Object#equals(java.lang.Object)
     * @see     java.util.Hashtable
     */
    public int hashCode()
    {
        if(hashCode == 0)
        {
            int result = 21;
            result = 55 * result + type;
            result = 55 * result + name.hashCode();
            result = 55 * result + typeName.hashCode();
            result = 55 * result + (requiresPrecision ? 0 : 1);
            result = 55 * result + (requiresScale ? 0 : 1);
            result = 55 * result + precision;
            result = 55 * result + scale;
            result = 55 * result + (allowNull ? 0 : 1);
            hashCode = result;
        }
        return hashCode;
    }
}

If you have any questions or feedback, please reply or send me a private message. I'll post the current unit test next.
Craig

Here's the unit test for the Column class:

import junit.framework.TestCase;
/**
 * Implements a unit test for <code>Column</code>.
 *
 * @author $Author$
 * @version $Revision$
 */
public class ColumnTest extends TestCase
{
    /**
     * Construct an instance of this <code>TestCase</code>.
     */
    public ColumnTest(String name)
    {
        super(name);
    }
    /**
     * For each test, perform the following initialization steps to ensure
     * a clean run.
     */
    protected void setUp() {}
    /**
     * For each test, perform the following deinitialization steps to ensure
     * a clean run.
     */
    protected void tearDown() {}

/**
     * Execute this <code>TestCase</code>.
     */
    public static void main(String args[])
    {
         junit.textui.TestRunner.run(ColumnTest.class);
    }
    /**
     * Test that the column name must not be <code>null</code>.
     */
    public void testNullName()
    {
        try
        {
            Column col = new Column(1, null, "char", "scale", 0, 0, false);
            fail("created column with null name");
        }
        catch(NullPointerException success)
        {
            // catching this exception indicates success; ignore
        }
    }
    /**
     * Test that the type name must not be <code>null</code>.
     */
    public void testNullTypeName()
    {
        try
        {
            Column col = new Column(1, "fname", null, "scale", 0, 0, false);
            fail("created column with null type name");
        }
        catch(NullPointerException success)
        {
            // catching this exception indicates success; ignore
        }
    }
    /**
     * Test that the precision must be > 0.
     */
    public void testIllegalPrecision()
    {
        try
        {
            Column col = new Column(1, "fname", "char", "scale", -5, 0, false);
            fail("created column with invalid precision");
        }
        catch(IllegalArgumentException success)
        {
            // catching this exception indicates success; ignore
        }
    }
    /**
     * Test that the scale must be > 0.
     */
    public void testIllegalScale()
    {
        try
        {
            Column col = new Column(1, "fname", "char", "scale", 0, -5, false);
            fail("created column with invalid scale");
        }
        catch(IllegalArgumentException success)
        {
            // catching this exception indicates success; ignore
        }
    }
    /**
     * Test that an instance of <code>Column</code> can be constructed
     * successfully.
     */
    public void testConstructValidColumn()
    {
        int type = 1;
        String name = "fname";
        String typeName = "char";
        String createParams = "scale";
        int precision = 0;
        int scale = 0;
        boolean allowNull = false;
        Column col = new Column(type, name, typeName, createParams,
                                precision, scale, allowNull);
        assertEquals("type did not match",
                     type, col.type());
        assertEquals("name did not match",
                     name, col.name());
        assertEquals("typeName did not match",
                     typeName, col.typeName());
        assertEquals("requiresPrecision() was true",
                     false, col.requiresPrecision());
        assertTrue("requiresScale() was false",
                   col.requiresScale());
        assertEquals("precision did not match",
                     precision, col.precision());
        assertEquals("scale did not match",
                     scale, col.scale());
        assertEquals("allowNull did not match",
                     allowNull, col.allowNull());
    }
    /**
     * Test that the <code>toString()</code> method correctly represents
     * a given <code>Column</code> as a <code>String</code>.
     */
    public void testToString()
    {
        Column col = new Column(1, "initials", "char", "max length",
                                3, 0, false);
        String expected = "initials char(3) NOT NULL";
        assertEquals("column incorrectly represented as string",
                     expected, col.toString());
        col = new Column(1, "ship_date", "datetime", null, 0, 0, true);
        expected = "ship_date datetime";
        assertEquals("column incorrectly represented as string",
                     expected, col.toString());
        col = new Column(1, "id", "NUMERIC", "precision,scale", 5, 2, true);
        expected = "id NUMERIC(5,2)";
        assertEquals("column incorrectly represented as string",
                     expected, col.toString());
    }
    /**
     * Test overriding of equals().
     */
    public void testEquals()
    {
        Column col1 = new Column(java.sql.Types.INTEGER, "id", "INTEGER",
                                 null, 0, 0, false);
        Column col2 = new Column(java.sql.Types.INTEGER, "id", "INTEGER",
                                 null, 0, 0, false);
        Column col3 = new Column(java.sql.Types.INTEGER, "id", "INTEGER",
                                 null, 0, 0, false);
        // reflexivity
        assertTrue("reflexivity violated in equals()", col1.equals(col1));
        // symmetry
        assertEquals("symmetry violated in equals()", col1.equals(col2),
                     col2.equals(col1));
        // transitivity
        assertTrue("transitivity violated in equals()",
                   col1.equals(col2) && col2.equals(col3) && col1.equals(col3));
        // consistency
        assertEquals("consistency violated in equals()", col1.equals(col3),
                     col1.equals(col3));
        // "non-nullity"
        assertTrue("'non-nullity' violated in equals()",
                   col1.equals(null) == false);
    }
}

Craig