CODEWITHSUNDEEP
javadatabaseresultset
Fabio
Fabio

Reputation: 363

Check if a column already exists in the table

I need to verify if a column already exists in a table . My class extends CustomTaskChange so my method receives a Database object as an argument. Can I make the verification I want trough the ResultSetObject?

@Override
    public void execute(Database database) throws CustomChangeException {
    
            JdbcConnection connection = (JdbcConnection) database.getConnection();
            DatabaseMetaData metadata;
            metadata = connection.getMetaData();
            String[] types = {"TABLE"};
            ResultSet rs = metadata.getTables(null, null, "%", types);
            Statement s = connection.createStatement();

                while (rs.next()) {
                    String tableName = rs.getString(3);
                    if (tableName.endsWith(this.suffix)) {
                        String sql = sqlStatement.replaceAll("name", tableName);
                        s.execute(sql);
                    }
                }
    }

Basically what this piece of code is doing is going through all the tables in my database. If, a table name ends in a suffix, I will add the column to it. This way I can add a column to multiple tables at the same time. But I want to add another verification to add the column to a table, and that is that there can't already be a column with that name in that table. Something like this(pseudocode)

if(tableName.endsWith(this.suffis) && columnName doesn't exist in that table){
   String sql = sqlStatement.replaceAll("name", tableName);
   s.execute(sql);
}

Upvotes: 0

Views: 861

Answers (2)

thelearner
thelearner

Reputation: 87

With the above code, you can check the metadata and then execute the SQL query to delete the same.

Upvotes: 0

Tushar Wasson
Tushar Wasson

Reputation: 556

you can fetch columns from each of the tables then you can check column exist or not.

 {
  Statement st = con.createStatement();
  ResultSet rs = st.executeQuery("SELECT * FROM TABLENAME LIMIT 1");
  ResultSetMetaData md = rs.getMetaData();
  int col = md.getColumnCount();
  for (int i = 1; i <= col; i++){
  String col_name = md.getColumnName(i);
   if(col_name.equals("YourColumnName"){
     /*Then the column already exist*/
 }}

Upvotes: 1

Related Questions