MySQL - checking if column exists if not create it in Java

Question

I've spent a few hours researching on how to create a Java method that will help with my program. I am very new to MySQL, so I am not the most experienced person out there.

What I am trying to do is write a Java method that checks if a column that has the is named after a username exists inside of a table. If it does not exists, it will create that column.

I have seen lot's of tutorials on the internet about similar solutions to my problem. I don't see how I am supposed to implement the TABLE_SCHEMA into a java method since I only know the very basics of MySql.

It would be nice to see how to implement this or some other solution into the Java method. I've mostly erased my previous work since I could not figure it out, so sorry if I need to show that(This is my first question.)

Edit:

        try {
        ResultSet res = conn.createStatement()
                .executeQuery("ALTER TABLE `package_table` ADD " + username + "  VARCHAR(15);");
        if (!res.next()) {
            conn.createStatement()
                    .executeUpdate("INSERT INTO `package_table` (`uuid`, `name`, `packages`) VALUE ('"
                            + event.getPlayer().getUniqueId() + "', '" + event.getPlayer().getName() + "', '" + "" + "');");
        }
    } catch (SQLException e) {
        e.printStackTrace();
        try {
            conn.close();
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    }

Edit2: The reason I need to do columns is that I need to store the 'packages' a username has. There can be "infinite" amounts of packages.

Thanks, Jack

Your code would be helpful here because we don't necessarily know the details of your implementation (e.g. are you using raw JDBC or a framework such as Hibernate). — Tim Biegeleisen, Dec 24 '15 at 06:07
what do you mean by , "I don't see how I am supposed to implement the TABLE_SCHEMA" ? — Sabir Khan, Dec 24 '15 at 06:10
Do you really need to add one column for every user ? Its wrong..! — The Coder, Dec 24 '15 at 06:25
It's better then doing rows for the project I am doing. At least that is what a programmer friend said. I'll update the post with more info! — melemonsqueezer1, Dec 24 '15 at 06:26
This question was already asked.Refer the link [Reference Link](http://stackoverflow.com/questions/24571611/mysql-alter-table-if-column-not-exists) — Karthika, Dec 24 '15 at 06:31
There cannot be infinite amount of columns - http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html . You should create a child table for storing the packages a user has instead of messing up by creating column for every user. — The Coder, Dec 24 '15 at 06:31

Kirill · Accepted Answer · 2015-12-24T06:30:27.857

You can do it using jdbc. Just get column names and then add one if you need.

Something like this:

Connection con;
Statement st;
ResultSet rs;

try {
  Class.forName("com.mysql.jdbc.Driver");
  con = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/dbname",
      "dbuser",
      "bdpass"
  );

  st = con.createStatement();

  String sql = "select * from table";
  rs = st.executeQuery(sql);
  ResultSetMetaData metaData = rs.getMetaData();
  int rowCount = metaData.getColumnCount();

  boolean isMyColumnPresent = false;
  String myColumnName = "myColumnName";
  for (int i = 1; i <= rowCount; i++) {
    if (myColumnName.equals(metaData.getColumnName(i))) {
      isMyColumnPresent = true;
    }
  }

  if (!isMyColumnPresent) {
    String myColumnType = "some type";
    st.executeUpdate("ALTER TABLE table ADD " + myColumnName + " " + myColumnType);
  }
} catch (Exception e) {
  e.printStackTrace();
}

Great answer! Exactly what i was looking for! – melemonsqueezer1 Dec 24 '15 at 19:25 — melemonsqueezer1, Dec 24 '15 at 19:25

score 0 · Answer 2 · answered Dec 24 '15 at 06:39

You can do something like this,

DatabaseMetaData  metadata = conn.getMetaData();
Resultset rs = null; 
  rs=metadata.getColumns(null, null, "package_table", null);
boolean found=false;

         while (rs.next()) {

               if(username.equals(rs.getString("COLUMN_NAME"))
              {
              found=true;
              }                       

             } 

       if(found){
       //Skip column creation    
       }else{
       //Create column
      }

MySQL - checking if column exists if not create it in Java

2 Answers2