从Java在MySQL中创建临时表 [英] Create temporary table in MySQL from Java

问题描述

我有这个:

public static void createTemporaryTable() {
        Statement s = null;
        sentence = "CREATE TEMPORARY TABLE Book (ISBN int NOT NULL, " +
                "title varchar(45), author varchar(45), price double, PRIMARY KEY  (`ISBN`));";

        try {
            s = Conexion.getInstancia().createStatement();
            s.executeUpdate(sentence);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                s.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

然后:

public class System {

    public static void main(String[] args) {
        SqlSentencesList.createTemporaryTable();
    }

}

但是当我执行select * from Book时，MySQL告诉我Book表不存在.我没有从Java得到任何错误消息，因此应该创建表，但不是.

But when I execute select * from Book, MySQL tells me Book table doesn't exists. I ain't getting any error messages from java, so the table should be created, but it's not.

如果我执行相同的sql语句直接在mysql中创建临时表，那么它会正常工作.

If I execute the same sql sentence for creating the temporary table directly in mysql, it works fine.

这是我的Conexion课:

public class Conexion {

    private static Connection conexion = null;



    private Conexion() {

    }

    public static Connection getInstancia() {
        if (conexion == null) {
            try {
                Class.forName("com.mysql.jdbc.Driver");
                conexion = DriverManager.getConnection(
                        "jdbc:mysql://localhost/Esquema_VentaLibros","gustavo", "123581321");

            } catch (SQLException sqlex) {
                sqlex.printStackTrace();
            } catch(ClassNotFoundException cnfex) {
                cnfex.printStackTrace();
            }
            return conexion;
        }
        else {
            return conexion;
        }
    }

}

推荐答案

关闭连接后，临时表将自动删除.

Temporary tables are automatically dropped when a connection is closed.

有关详细信息，请参见 MySQL CREATE TABLE 文档:

See the MySQL CREATE TABLE documentation for details:

创建表时，可以使用TEMPORARY关键字. TEMPORARY表仅对当前连接可见，并且在关闭连接后会自动删除.这意味着两个不同的连接可以使用相同的临时表名称，而不会彼此冲突或与现有的具有相同名称的非TEMPORARY表冲突. (在删除临时表之前，现有表一直处于隐藏状态.)

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

如果要创建一个临时表来执行某些工作，则应在开始工作时创建它，并对它执行UPDATE/SELECT语句.关闭连接后，它将自动删除，并且不会与使用相同临时表名称的其他连接冲突.

If you want to create a temporary table to do some work in, you should create it when you begin your work, and execute your UPDATE/SELECT statements against it. It will automatically drop when you close your connection, and it won't conflict with other connections using the same temporary table name.

这篇关于从Java在MySQL中创建临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！