M
M
Mimocodil2021-05-26 10:22:39
MySQL
Mimocodil, 2021-05-26 10:22:39

How to find a table in a desired MySQL schema?

During the study of SQL, the question arose - what if I run the program, but the required table does not exist? As a result, I came up with this function:

static void initTable(String tableName) {
  try (Connection c = getConnection()) {

    PreparedStatement preparedStatement = c.prepareStatement("SELECT count(*) FROM information_schema.tables WHERE table_name = ?LIMIT 1;");
    preparedStatement.setString(1, tableName);
    
    ResultSet resultSet = preparedStatement.executeQuery();
    resultSet.next();
    if (resultSet.getInt(1) == 0) {
      c.createStatement().execute("CREATE TABLE tutorial." + tableName + " (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, specialty VARCHAR(50) NOT NULL, salary INT NOT NULL, PRIMARY KEY (id));");
      System.out.println("Table \"" + tableName + "\" created.");
    } else
      System.out.println("Table \"" + tableName + "\" exists.");

    System.out.println("\n------------------------------\n");
  } catch (SQLException e) {
    e.printStackTrace();
  }
}


Everything seems to be working, but I immediately ran into a problem. I have several circuits in my workbench and when I run the check, it searches through all the circuits. If there is no table with the desired name, then it creates where I indicated. But what if another schema already has a table with the same name... how can I refine the search to the boundaries of the desired schema?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
O
Orkhan, 2021-05-26
@Ezekiel4

Good afternoon!
I think that this question will disappear when you get acquainted with ORM technology and with a framework such as Hibernate. In short, there is an option there that, at startup, checks for the presence of tables and, if there are none, creates tables automatically. But as a rule, tables are not automatically created in production.
Usually, sql scripts are written, which are subsequently migrated using frameworks such as flyway.
Thus, taking into account the fact that you work according to the database first principle (i.e. you create a database, a schema and a table), then there should not be a situation when the required table does not exist. And even if such a situation occurs, then let an exception be thrown and the application crashes. You can also read these terms. like database first, code first, model first.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question