Add description to columns using Java code

Question

I can create a table and its columns in Java by using the statement:

CREATE TABLE table_name(column1 int, column2 double, etc...)

What I would like to do is to add descriptions to each of these columns with an appropriate statement, I found a stored procedure sp_addextendedproperty that looks like it can be used to accomplish this I just have no idea how to use it in java with jdbc.

Answer 1

There are a number of ways to call a stored procedure (essentially, preparing the statement and binding the variables, or sending a string of SQL), but the simplest is to just send rhe SQL statement

exec sp_addextendedproperty list, of, arguments, the, sp, needs;

Skipping your try/finally boilerplate, and assuming connection is a java.sql.Connection, that's:

connection
  .createStatement()
      .execute( "exec sp_addextendedproperty arguments;");

But ChssPly76 has a good point: doing this from Java isn't a good idea (unless you're developing some database manager in Java).

Answer 2

Are you creating the table dynamically at runtime (e.g. as part of your application) - perhaps that's even user-driven? If that's the case, you already have that "documentation" (column comments) somewhere and I doubt the utility of adding them to SQL Server.

But if you're just trying to automate your build, take a look at LiquiBase. It's a pretty decent DB change management system that uses XML as backbone. It's written in java and integrates well with Hibernate (useful if you ever decide to use ORM instead of straight JDBC).

Update: If you do decide to go forward with calling stored procedure via JDBC, I would strongly recommend using CallableStatement to invoke it. Dynamically building SQL queries in the application should be avoided if possible.