ORACLE临时表使用详介

ORACLE的临时表在应用系统中有很大的作用，它可以让用户只能够操作各自的数据中而互不干扰，不用担心会破坏或影响其他SESSION/TRANSACTION的数据，这也是数据安全的一种解决方法

    临时表分为SESSION、TRANSACTION两种，SESSION级的临时表数据在整个SESSION都存在，直到结束此次SESSION；而TRANSACTION级的临时表数据在TRANACTION结束后消失，即COMMIT/ROLLBACK或结束SESSION都会清除TRANACTION临时表数据。

    两种临时表的语法：

    create global temporary table 临时表名 on commit preserve|delete rows
    用preserve时就是SESSION级的临时表，用delete就是TRANSACTION级的临时表

    例：1、SESSION级临时表

    --建立临时表

create global temporary table temp_tbl(col_a varchar2(30)) on commit preserve rows

 

--插入数据

insert into temp_tbl values('test session table')

--提交 commit

    --查询数据

    select *from temp_tbl

可以看到数据'test session table'记录还在

    --结束SESSION，重新登录，再查询数据select *from temp_tbl，这时候记录已不存在，因为系统在结束SESSION时自动清除记录

    2、TRANSACTION级临时表

    --建立临时表

create global temporary table temp_tbl(col_a varchar2(30)) on commit delete rows

--插入数据

 insert into temp_tbl values('test transaction table')

    --提交
    commit
    --查询数据

select *from temp_tbl

    这时候可以看到刚才插入的记录'test transaction table'已不存在了；同样，如果不提交而直接结束SESSION，重新登录记录也不存在。

1

评论

6 楼 davidx 2009-10-14  

比较全，资料。但是我还是没看明白为什么数据量比较大的表，操作时用临时表效率会高些。。。

5 楼 dolphin_ygj 2009-04-20  

在Oracle 数据库中的临时表用法汇总(转)

在Oracle 数据库中的临时表用法汇总

　　1 语法

　　在Oracle中，可以创建以下两种临时表：

　　1) 会话特有的临时表

　　CREATE GLOBAL TEMPORARY ( )

　　ON COMMIT PRESERVE ROWS；

　　2) 事务特有的临时表

　　CREATE GLOBAL TEMPORARY ( )

　　ON COMMIT DELETE ROWS；

　　CREATE GLOBAL TEMPORARY TABLE MyTempTable

　　所建的临时表虽然是存在的，但是如果insert 一条记录然后用别的连接登上去select，记录是空的。　　

　　--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）

　　--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。　　

　　2 动态创建

　　create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) as

　　v_num number;

　　begin

　　select count(*) into v_num from user_tables where table_name='T_TEMP';　　

　　--create temporary table

　　if v_num<1 then

　　execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (

　　COL1 VARCHAR2(10),

　　COL2 VARCHAR2(10)

　　) ON COMMIT delete ROWS';

　　end if;　　

　　--insert data

　　execute immediate 'insert into t_temp values('''　　v_col1　　''','''　　v_col2　　''')';　　

　　execute immediate 'select col1 from t_temp' into v_num;

　　dbms_output.put_line(v_num);

　　execute immediate 'delete from t_temp';

　　commit;

　　execute immediate 'drop table t_temp';

　　end pro_temp;　　

　　测试:　　

　　15:23:54 SQL> set serveroutput on

　　15:24:01 SQL> exec pro_temp('11','22');

　　11　　

　　PL/SQL 过程已成功完成。　　

　　已用时间: 00: 00: 00.79

　　15:24:08 SQL> desc t_temp;

　　ERROR：

　　ORA-04043: 对象 t_temp 不存在　　

　　3 特性和性能(与普通表和视图的比较)

　　 临时表只在当前连接内有效

　　临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用

　　数据处理比较复杂的时候时表快,反之视图快点

　　在仅仅查询数据的时候建议用游标: open cursor for 'sql clause';

4 楼 dolphin_ygj 2009-04-13  

临时表管理需要注意的地方。

　　临时表相对与其他表来说，是一种比较特殊的表结构，但是，作用又比较大，Oraclee数据库若没有这种表的话，还真是不行。为了管理好这种特殊的表，我们需要注意几个细节。

　　一是要注意临时表不能永久的保存数据。只所以称为临时表，就是因为该表中的内容只是临时存在的。当一个会话或者事务结束时，该表中的内容就会被自动清空。所以，在临时表中，一般不要保存永久数据。在实务中，有个不好的操作习惯，就是有些人在测试数据库的时候，喜欢把测试的数据放在临时数据表中。其实，这是对Oralce临时数据表认识的错误。若我们在数据库中，把要测试的数据，如销售定单的内容放在数据库的临时表中的话，则在其他功能中，如要测试销售定单日报表的功能时，就会找不到相关的定单内容。因为离开特定的会话或者事务的话，临时表中的内容就会不存在了。所以，Oralce数据库中所讲的临时表不是给我们来存储测试数据的。

　　二是临时表中的数据不会备份、恢复，对其的修改也不会有任何的日志信息。若我们在操作数据库的时候，往数据库的临时表中存入了一些信息。此时突然服务器出现当机。此时，我们想通过数据库备份文件恢复数据库临时表中的内容，或者查看临时表的日志信息，都是无法实现的。也就是说，当服务器以外死机重新启动后，临时表中的内容就会被清空。在数据库的任何地方，如数据库备份文件或者日志信息中，都查不到在重新启动之前数据库临时表中保存了哪些内容，就好象根本没有对临时表进行操作一样。

　　三是临时表表空间的管理。临时表在Oraclee数据库中，也是表的一种，其也有对应的表空间。在创建临时表的时候，若我们不指定表空间的话，默认的表空间是SYSTEM。对于临时表的表空间管理的话，我们需要注意一个小的细节。若我们把临时表的表空间归属为SYSTEM的话，也就是说，在创建临时表的时候不具体指定具体的表空间，则这个默认的表空间是不能被删除的。而若我们在创建临时表表空间的时候，指定为SYSTEM以外的表空间的话，则在不需要这表空间的时候，我们可以删除。所以，为了后续管理的方便，笔者还是建议大家在创建临时表的时候，要指定表空间。

　　四是要注意一个问题，临时表只是数据是临时的，而表仍然是永久的。也就是说，当一个会话结束或者一个事务完成时，其临时表中的数据虽然删除了，但是，临时表本身仍然是存在的。也就是说。Oraclee数据库中的临时表表是全局的，只是数据是临时的。这跟SQL Server数据库系统具有比较大的区别。其实，这两个数据库在临时表的处理上有很大的不同，各有各的特色。在以后的文章中，我会专门叙述这两种数据库在临时表管理机制上的不同，欢迎大家关注。

　　五是要注意Oraclee数据库在给临时表填入数据的时候，不会对相应的记录加锁。也就是说，当在临时表上执行DML语句的操作时，不会给记录加锁，也不会将数据的变化内容写到重做(REDO)日志中。所以不能用临时表保存永久的数据，也不能对临时表进行共同的操作。这是新手在管理数据库临时表经常会碰到的问题。

　　六是临时表与普通表之间不能相互转换。在一般情况下，临时表建立后，该表就不能被转换成永久表。所以，这也说明一个道理，利用临时表作为数据库设计时候的测试表不合适。这个临时表可能跟我们按字面意思理解的临时表有误，不是我们所认为的为了测试表结构而建立的临时表。这一点是我们在刚开始接触OracleE数据库时，经常会犯的错误。

3 楼 dolphin_ygj 2009-04-13  

如何使用临时表？
5.4.1无法显示的数据设计师' style='text-decoration:underline;color:blue;' target=_blank>设计师L告诉程序员M在项目中需要使用到临时表。由于使用的是PostgreSQL数据库，L还告诉M，在PostgreSQL中使用临时表需要利用Java的JDBC来建表。

M虽然并不理解为什么需要利用JDBC来建表，但是他深信以当前他的所知这不是一件困难的事，于是程序员M模拟了一个简单场景，对Room实体的新增和查询动作。

M写下了如下的代码，见例5.18：

例5.18：TestTempTableDAONoManager.java

package dao.jdbc;



import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;



import entity.Room;



public class TestTempTableDAONoManager {

         // 针对temproom表的操作

         private final String CREAT_ROOM_TABLE = "CREATE GLOBAL TEMP TABLE temproom"

                            "( id int8 NOT NULL,  "

                            "address varchar(255),  "

                            "number varchar,  "

                            "CONSTRAINT room_pkey PRIMARY KEY (id)) "

                            "ON COMMIT DELETE ROWS;";



         private final String INSERT_ROOM_TABLE = "insert into temproom (id, address, number) values (?,?,?)";



         private final String FIND_ROOM_BY_KEY = "select * from temproom where id=?";



         //取得连接

         private Connection getConnection() throws SQLException {

                   try {

                            final String url = "jdbc:postgresql://localhost/TESTDB";

                            final String user = "sa";

                            final String password = "1111";

                            Class.forName("org.postgresql.Driver");

                            Connection connection = DriverManager.getConnection(url, user,

                                               password);

                            return connection;

                   } catch (ClassNotFoundException e) {

                            throw new SQLException(e.getMessage());

                   }

         }



         //创建临时表

         public void createTable() {

                   // 提供一个连接

                   Connection con = null;

                   // 提供一个创建预编译SQL 语句的变量

                   PreparedStatement ps = null;



                   try {

                            con = this.getConnection();

                            ps = con.prepareStatement(this.CREAT_ROOM_TABLE);

                            ps.executeUpdate();

                   } catch (SQLException ex) {

                            ex.printStackTrace();

                   } finally {

                            // 必须进行的处理，关闭PreparedStatement、ResultSet、Connection

                            try {

                                     if (ps != null)

                                               ps.close();

                                     if (con != null)

                                               con.close();

                            } catch (SQLException ex) {

                                     ex.printStackTrace();

                            }

                   }

         }



         //插临时表

         public void insertRoom(Room room) throws Exception {

                   // 提供一个连接

                   Connection con = null;

                   // 提供一个创建预编译SQL 语句的变量

                   PreparedStatement ps = null;

                   // 提供一个返回SQL查询结果的ResultSet接口变量

                   // ResultSet带有游标可以指向返回结果中的某条记录

                   ResultSet rs = null;

                  

                   try {

                            // 取得JDBC连接

                            con = this.getConnection();

                            // 预编译SQL语句并执行insertSql

                            ps = con.prepareStatement(this.INSERT_ROOM_TABLE);

                            ps.setLong(1, room.getId());

                            ps.setString(2, room.getAddress());

                            ps.setString(3, room.getNumber());

                            // 若新增失败

                            if (ps.executeUpdate() != 1) {

                                     throw new Exception("更新失败");

                            }

                   } catch (SQLException ex) {

                            ex.printStackTrace();

                   } finally {

                            // 必须进行的处理，关闭PreparedStatement、ResultSet、Connection

                            try {

                                     if (rs != null)

                                               rs.close();

                                     if (ps != null)

                                               ps.close();

                                     if (con != null)

                                               con.close();

                            } catch (SQLException ex) {

                                     ex.printStackTrace();

                            }

                   }

         }



         /**

          * 根据Room表的主键返回Room实体

          */

         public Room findRoom(Long id) {

                   // 提供一个连接

                   Connection con = null;

                   // 提供一个创建预编译SQL 语句的变量

                   PreparedStatement ps = null;

                   // 提供一个返回SQL查询结果的ResultSet接口变量

                   // ResultSet带有游标可以指向返回结果中的某条记录

                   ResultSet rs = null;

                   // 提供一个Room实体的变量

                   Room room = null;

                  

                   try {

                            // 取得JDBC连接

                            con = this.getConnection();



                            // 预编译SQL语句并执行findSql

                            ps = con.prepareStatement(this.FIND_ROOM_BY_KEY);

                            ps.setLong(1, id);

                            rs = ps.executeQuery();

                            // 当返回结果集中无记录时返回null

                            if (!rs.next()) {

                                     return null;

                            }

                            // 以下的情况将保证在结果集中有记录时的应用

                            // 创建Room实体的实例以作处理

                            room = new Room();

                            room.setId(rs.getLong("id"));

                            room.setAddress(rs.getString("address"));

                            room.setNumber(rs.getString("number"));

                   } catch (SQLException ex) {

                            ex.printStackTrace();

                   } finally {

                            // 必须进行的处理，关闭PreparedStatement、ResultSet、Connection

                            try {

                                     if (rs != null)

                                               rs.close();

                                     if (ps != null)

                                               ps.close();

                                     if (con != null)

                                               con.close();

                            } catch (SQLException ex) {

                                     ex.printStackTrace();

                            }

                   }

                   return room;

         }



         public static void main(String[] args) {

                   TestTempTableDAONoManager testTempTableDAONoManager = new TestTempTableDAONoManager();

                   // 创建表temproom

                   testTempTableDAONoManager.createTable();



                   // 新建Room实体

                   Room room = new Room();

                   room.setId(1L);

                   room.setNumber("001");

                   room.setAddress("RW Room");

                   try {

                            // 插表temproom

                            testTempTableDAONoManager.insertRoom(room);

                   } catch (Exception ex) {

                            ex.printStackTrace();

                   }



                   // 显示结果

                   Room showRoom = new Room();

                   showRoom = testTempTableDAONoManager.findRoom(1L);

                   System.out.println("-----Room id:" showRoom.getId());

                   System.out.println("-----Room Address:" showRoom.getAddress());

                   System.out.println("-----Room Number:" showRoom.getNumber());

         }

}

在这段代码中，M实现了三个主要的DAO方法：

（1）createTable()，此方法用以创建临时表temproom

（2）insertRoom()，此方法用以为临时表temproom插入一条记录

（3）findRoom()，此方法用以取得insertRoom()方法所插入的一条，将以Room实体返回。

此外，为了简单起见，M给出了getConnection()方法，三个DAO的主要方法都将调用getConnection()来取得数据库的JDBC连接。

很快代码实现了，于是M又构造了main()方法来对实现结果做测试。当M满心欢喜的以为结果将如他所料时，一个意想不到的情况发生了。这段看起来完全正确的代码居然抛出了异常：

java.sql.SQLException: ERROR: relation "temproom" does not exist

这是怎么一回事呢？

5.4.2 理解临时表M的代码如果用在与非临时表的表交互时自然没有错，但是用在临时表上显然就错了。原因就出在临时表上。要解决这个“无法显示的数据”问题，就必须搞清楚什么是临时表。

绝大多数关系型数据库都有临时表，这在SQL-92中也是一个标准。临时表的特性在于分布式运用，也即任何一个用户连接到数据库，即使使用的是同名的临时表，这个用户的所有操作也对另一连接的用户不可见。换句话说，就是“临时表多用户并行不是问题”。

在标准的SQL-92中，临时表的定义是这样的：

（1）使用CREATE TEMPORARY TABLE…定义临时表。

（2）定义临时表的结尾部分可以存在ON COMMIT DELETE ROWS子句或ON COMMIT PRESERVE ROWS子句。

（3）若缺省ON COMMIT子句的情况下，将使用ON COMMIT DELETE ROWS子句所提供的行为。

通过ON COMMIT DELETE ROWS子句定义的临时表它的特性在于：“临时表的所有数据将在一次事务提交后被全部删除”

通过ON COMMIT PRESERVE ROWS子句定义的临时表它的特性在于：“临时表的所有数据在一次事务提交后将依旧保留”

但是无论使用哪种ON COMMIT子句定义的临时表，它在一次数据库连接结束后都将被删除所有数据。

请注意：一次数据库连接和一次事务提交是两个概念，前者读者可以简单的理解为Connection连接的关闭，也即Java中“connection.close()”方法的调用；后者读者可以理解为Connection连接中的事务提交，也即Java中“connection.commit()”方法的调用。

每一种数据库对于临时表的定义都存在着兼容性的问题，在SQL-92编码规则中临时表创建后即使连接结束也不会被drop掉，符合这个标准的数据库具有代表性的就是Oracle，但是有些数据库则定义临时表在连接结束后将连同整个表都会被drop掉，PostgreSQL就是其中的一种，MySQL也是如此。因此若要使用临时表，则必须在项目启动后对该项目所使用的数据库文档进行必要的了解。

由于临时表的先天特性（多用户并行无关性），在项目中使用临时表是很常见的。

如何使用临时表？(二) 分类:我的著作2008.1.3 14:31 作者：小仙狗 | 评论：0 | 阅读：660
5.4.3 查找问题在理解了临时表的相关特性后，不难看出 M的代码所存在的问题。

（1）首先，L要求M使用JDBC来创建临时表的Schema是没有问题的，由于PostgreSQL每次连接结束都将drop临时表，因此必须手动创建临时表（调用createTable()）。

（2）M在这段代码中最大的问题在于getConnection()，在例5.18中可以看到任何一个DAO方法都会调用getConnection()，而每个DAO方法的finally部分又会关闭Connection。这样的话，客户端在调用createTable()方法结束后已经关闭了数据库连接。按照临时表的特性，此时临时表中的数据已经被自动删除了。

5.4.4 提供一个ConnectionManager问题（2）是整段代码引起错误的主要原因，解决这个问题有多种方案。最容易想到的就是整个DAO全局共享一个Connection，可是如果就简单的提供一个单例类是有问题的。

（1）因为临时表本身的特性虽然是多用户并行无关性，但是这个无关性的前提是每个用户一个连接。假设提供一个单例类，那么在整个运行期所有客户端都将使用这个Connection，如此的结果必然导致多个客户共用一个Connection。

（2）对于Connection连接提供单例类，必然导致一个长连接不被释放，对于任何一个系统来说这都是无法接受的。

有鉴于此，单例类的实现被否定了。深入的再想一下，不难发现，其实对于临时表的操作需要的是以下两个条件：

（1）提供一种方式，让多个操作临时表的方法共享一个连接。

（2）而这样一个连接对于任何请求都将是独立的。

假设仅以J2EE模型来说，这是很容易实现的。因为从Servlet请求到来的每一个客户端都只发生在自己的线程中。这就给实现两个条件带来了契机，只需要利用Java的ThreadLocal类。

提供一个ConnectionManager类，该类将使用ThreadLocal类来管理Connection连接，以保证该Connection连接对于一次请求的线程是独立的。请见例5.19：

例5.19：ConnectionManager.java

package dao.jdbc;



import java.sql.Connection;



public class ConnectionManager {

         //静态变量＂当前线程＂，用以管理Connection

         private static final ThreadLocal currentConnection = new ThreadLocal();



         //静态方法取得＂当前线程＂所使用的Connection

         public static Connection getConnection() {

                   return (Connection)currentConnection.get();

         }



         //将＂当前线程＂与＂当前连接＂绑定

         static Connection setCurrentConnection(Connection connection) {

                   Connection priorConnection = (Connection)currentConnection.get();

                   currentConnection.set(connection);

                   return priorConnection;

         }



}

客户端只需要在每次请求到来时取得一个Connection连接，调用setCurrentConnection（）方法，将Connection连接与当前线程绑定，而DAO中的每个方法都调用getConnection()方法来获取当前线程绑定的Connection连接，在DAO的每个方法中finally时不应该关闭Connection连接，将关闭的动作交给客户端处理。

5.4.5 不能被忽略的ON COMMIT DELETE ROWS仅利用ConnectionManager.java还是不能完全结束工作，因为临时表的ON COMMIT DELETE ROWS子句的本意是“临时表的所有数据将在一次事务提交后被全部删除”。

在JDBC的Connection连接中事务本身是被设置为AutoCommit的，这意味着要想在“创表->插表->查表”三个动作结束后才提交事务，那势必要设置AutoCommit为false。否则在第二个动作“插表”的行为结束时事务就已经提交了，即使Connection连接依然保持，但临时表还是会将所有数据在这次事务提交后全部删除。正确的客户端操作如下：

//取得绑定的连接

Connection con = ConnectionManager.getConnection();

//设置AutoCommit为false

con.setAutoCommit(false);

//实现DAO方法中与临时表相关的操作

…

//提交事务

con.commit();

//还原AutoCommit

con.setAutoCommit(true);

以上可以完全操控临时表了。

5.4.6 被改写的完整代码以下将对M的代码进行改写，请读者注意该段代码中加粗的部分。请见例5.20：

例5.20：TestTempTableDAO.java

package dao.jdbc;



import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;



import entity.Room;



public class TestTempTableDAO {

         // 针对temproom表的操作

         private final String CREAT_ROOM_TABLE = "CREATE GLOBAL TEMP TABLE temproom"

                            "( id int8 NOT NULL,  "

                            "address varchar(255),  "

                            "number varchar,  "

                            "CONSTRAINT room_pkey PRIMARY KEY (id)) "

                            "ON COMMIT DELETE ROWS";



         private final String INSERT_ROOM_TABLE = "insert into temproom (id, address, number) values (?,?,?)";



         private final String FIND_ROOM_BY_KEY = "select * from temproom where id=?";



         public void createTable() {

                   // 提供一个连接

                   Connection con = null;

                   // 提供一个创建预编译SQL 语句的变量

                   PreparedStatement ps = null;



                   try {

              //取得绑定的连接

                            con = ConnectionManager.getConnection();

                            ps = con.prepareStatement(this.CREAT_ROOM_TABLE);

                            ps.executeUpdate();

                   } catch (SQLException ex) {

                            ex.printStackTrace();

                   } finally {

                            // 必须进行的处理，关闭PreparedStatement、ResultSet

                            try {

                                     if (ps != null)

                                               ps.close();

                                     // 请注意不用关闭Connection，否则无法实现连接的传递

                                     /*

                                      * if (con != null) con.close();

                                      */

                            } catch (SQLException ex) {

                                     ex.printStackTrace();

                            }

                   }

         }



         public void insertRoom(Room room) throws Exception {

                   // 提供一个连接

                   Connection con = null;

                   // 提供一个创建预编译SQL 语句的变量

                   PreparedStatement ps = null;

                   // 提供一个返回SQL查询结果的ResultSet接口变量

                   // ResultSet带有游标可以指向返回结果中的某条记录

                   ResultSet rs = null;



                   try {

              //取得绑定的连接

                            con = ConnectionManager.getConnection();

                            // 预编译SQL语句并执行insertSql

                            ps = con.prepareStatement(this.INSERT_ROOM_TABLE);

                            ps.setLong(1, room.getId());

                            ps.setString(2, room.getAddress());

                            ps.setString(3, room.getNumber());

                            // 若新增失败

                            if (ps.executeUpdate() != 1) {

                                     throw new Exception("更新失败");

                            }

                   } catch (SQLException ex) {

                            ex.printStackTrace();

                   } finally {

                            // 必须进行的处理，关闭PreparedStatement、ResultSet

                            try {

                                     if (rs != null)

                                               rs.close();

                                     if (ps != null)

                                               ps.close();

                                     // 请注意不用关闭Connection，否则无法实现连接的传递

                                     /*

                                      * if (con != null) con.close();

                                      */

                            } catch (SQLException ex) {

                                     ex.printStackTrace();

                            }

                   }

         }



         /**

          * 根据Room表的主键返回Room实体

          */

         public Room findRoom(Long id) {

                   // 提供一个连接

                   Connection con = null;

                   // 提供一个创建预编译SQL 语句的变量

                   PreparedStatement ps = null;

                   // 提供一个返回SQL查询结果的ResultSet接口变量

                   // ResultSet带有游标可以指向返回结果中的某条记录

                   ResultSet rs = null;

                   // 提供一个Room实体的变量

                   Room room = null;

                  

                   try {

              //取得绑定的连接

                            con = ConnectionManager.getConnection();



                            // 预编译SQL语句并执行findSql

                            ps = con.prepareStatement(this.FIND_ROOM_BY_KEY);

                            ps.setLong(1, id);

                            rs = ps.executeQuery();

                            // 当返回结果集中无记录时返回null

                            if (!rs.next()) {

                                     return null;

                            }

                            // 以下的情况将保证在结果集中有记录时的应用

                            // 创建Room实体的实例以作处理

                            room = new Room();

                            room.setId(rs.getLong("id"));

                            room.setAddress(rs.getString("address"));

                            room.setNumber(rs.getString("number"));

                   } catch (SQLException ex) {

                            ex.printStackTrace();

                   } finally {

                            // 必须进行的处理，关闭PreparedStatement、ResultSet、Connection

                            try {

                                     if (rs != null)

                                               rs.close();

                                     if (ps != null)

                                               ps.close();

                                     // 请注意不用关闭Connection，否则无法实现连接的传递

                                     /*

                                      * if (con != null) con.close();

                                      */

                            } catch (SQLException ex) {

                                     ex.printStackTrace();

                            }

                   }

                   return room;

         }



         public static void main(String[] args) {



                   try {

                            //绑定连接的代码不应该存在于这里，但是为了测试方便依然放在了这里

                            final String url = "jdbc:postgresql://localhost/TESTDB";

                            final String user = "sa";

                            final String password = "1111";

                            Class.forName("org.postgresql.Driver");

                            Connection connection = DriverManager.getConnection(url, user,

                                               password);

                            //设置AutoCommit为false

                            connection.setAutoCommit(false);

                            //绑定连接

                            ConnectionManager.setCurrentConnection(connection);

                           

                            TestTempTableDAO testTempTableDAO = new TestTempTableDAO();

                            // 创建表temproom

                            testTempTableDAO.createTable();



                            // 新建Room实体

                            Room room = new Room();

                            room.setId(1L);

                            room.setNumber("001");

                            room.setAddress("RW Room");

                            // 插表temproom

                            testTempTableDAO.insertRoom(room);



                            // 显示结果

                            Room showRoom = new Room();

                            showRoom = testTempTableDAO.findRoom(1L);

                           

                            //提交事务

                            connection.commit();

                            //还原AutoCommit

                            connection.setAutoCommit(true);

                            System.out.println("-----Room id:" showRoom.getId());

                            System.out.println("-----Room Address:" showRoom.getAddress());

                            System.out.println("-----Room Number:" showRoom.getNumber());

                   } catch (ClassNotFoundException e) {

                            e.printStackTrace();

                   } catch (Exception ex) {

                            ex.printStackTrace();

                   }

         }

}

2 楼 dolphin_ygj 2009-04-13  

在Hibernate 3中提供了&lt;subselect&gt;功能。
大家留意（20）项：
&lt;class
        name="ClassName"                              (1)
        table="tableName"                             (2)
        discriminator-value="discriminator_value"     (3)
        mutable="true|false"                          (4)
        schema="owner"                                (5)
        catalog="catalog"                             (6)
        proxy="ProxyInterface"                        (7)
        dynamic-update="true|false"                   (8)
        dynamic-insert="true|false"                   (9)
        select-before-update="true|false"             (10)
        polymorphism="implicit|explicit"              (11)
        where="arbitrary sql where condition"         (12)
        persister="PersisterClass"                    (13)
        batch-size="N"                                (14)
        optimistic-lock="none|version|dirty|all"      (15)
        lazy="true|false"                             (16)
        entity-name="EntityName"                      (17)
        check="arbitrary sql check condition"         (18)
        rowid="rowid"                                 (19)
        subselect="SQL expression"                    (20)
        abstract="true|false"                         (21)
        node="element-name"
/&gt;
     在（20）中注明，subselect 是可选的，提供一个不变、只读的实体到数据库子查询的映射。在需要一个视图而不是基本表，却不需要在数据库建立这个视图时适用。
     可以看到，这个&lt;subselect&gt;是适合对基本表中的数据进行查询、统计的。在在一些统计页面时极为有用。
   下面是Hibernate Doc中提供的例子：
  &lt;class name="Summary"&gt;
    &lt;subselect&gt;
        select item.name, max(bid.amount), count(*)
        from item
        join bid on bid.item_id = item.id
        group by item.name
    &lt;/subselect&gt;
    &lt;synchronize table="item"/&gt;
    &lt;synchronize table="bid"/&gt;
    &lt;id name="name"/&gt;
    ...
&lt;/class&gt;
    我们的一个项目中，需要管理n个项目，其中有一个页面，需要统计、计算这些项目中的一些属性，刚开始时是使用从基本表中取出数据，然后在一个类文件中计算出页面的值，计算又包括了三个比较大的循环。在开发时没有问题，但是在实际投入使用时发现页面载入极慢，大量时间花费在这些项目的统计、计算了。
   在收到bug反馈后，我们对统计页面视图进行设计，专门设计出一个映射文件用于计算数据（同上面的例子），把数据的计算、统计等都放到数据库中进行，比较发现大大加快了页面的显示速度。大约快了约40～50％。
  一点经验，呵呵，不知对楼主有用否？

1 楼 dolphin_ygj 2009-04-13  

Oracle临时表 优化查询速度
1、前言
    目前所有使用Oracle作为数据库支撑平台的应用，大部分数据量比较庞大的系统，即表的数据量一般情况下都是在百万级以上的数据量。当然在Oracle中创建分区是一种不错的选择，但是当你发现你的应用有多张表关联的时候，并且这些表大部分都是比较庞大，而你关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小并且查询得到这个结果集的速度非常快，那么这个时候我考虑在Oracle中创建“临时表”。
    我对临时表的理解：在Oracle中创建一张表，这个表不用于其他的什么功能，主要用于自己的软件系统一些特有功能才用的，而当你用完之后表中的数据就没用了。Oracle的临时表创建之后基本不占用表空间，如果你没有指定临时表（包括临时表的索引）存放的表空的时候，你插入到临时表的数据是存放在ORACLE系统的临时表空间中（TEMP）。
2、临时表的创建
    创建Oracle临时表，可以有两种类型的临时表：会话级的临时表和事务级的临时表。


    1）会话级的临时表因为这这个临时表中的数据和你的当前会话有关系，当你当前SESSION不退出的情况下，临时表中的数据就还存在，而当你退出当前SESSION的时候，临时表中的数据就全部没有了，当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。

当某一个SESSION退出之后临时表中的数据就被截断（truncate table，即数据清空）了。



会话级的临时表创建方法：

Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...) On Commit Preserve Rows；举例create global temporary table Student(Stu_id Number(5),Class_id  Number(5),Stu_Name Varchar2(8),Stu_Memo varchar2(200)) on Commit Preserve Rows ;


    2）事务级临时表是指该临时表与事务相关，当进行事务提交或者事务回滚的时候，临时表中的数据将自行被截断，其他的内容和会话级的临时表的一致（包括退出SESSION的时候，事务级的临时表也会被自动截断）。



事务级临时表的创建方法：

Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2...) On Commit Delete Rows；



举例：

create global temporary table Classes(Class_id Number(5),Class_Name Varchar2(8),Class_Memo varchar2(200)) on Commit delete Rows ;


    3）、两种不通类型的临时表的区别：

            语法上，会话级临时表采用on commit preserve rows而事务级则采用on commit delete rows；

            用法上，会话级别只有当会话结束临时表中的数据才会被截断，而且事务级临时表则不管是commit、rollback或者是会话结束，临时表中的数据都将被截断。


3、例子：

    1）、会话级（Session关闭掉之后数据就没有了，当Commit的时候则数据还在，当Rollback的时候则数据也是一样被回滚）：
     insert into student(stu_id,class_id,stu_name,stu_memo) values(1,1,'张三','福建');
     insert into student(stu_id,class_id,stu_name,stu_memo) values(2,1,'刘德华','福州');
     insert into student(stu_id,class_id,stu_name,stu_memo) values(3,2,'S.H.E','厦门');
SQL> select *from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
     1        1 张三     福建
     2        1 刘德华   福州
     3        2 S.H.E    厦门
     4        2 张惠妹   厦门

SQL> commit;

Commit complete

SQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
     1        1 张三     福建
     2        1 刘德华   福州
     3        2 S.H.E    厦门
     4        2 张惠妹   厦门

SQL>insert into student(stu_id,class_id,stu_name,stu_memo) values(4,2,'张惠妹','厦门');

1 row inserted

SQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
     1      &nbsp; 1 张三     福建
     2        1 刘德华   福州
     3        2 S.H.E    厦门
     4        2 张惠妹   厦门
     4        2 张惠妹   厦门



SQL> rollback ;

Rollback complete

SQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------------------------------------------------------------------
     1        1 张三     福建
     2        1 刘德华   福州
     3        2 S.H.E    厦门
     4        2 张惠妹   厦门

SQL>
    2）、事务级（Commit之后就删除数据）：本例子将采用以下的数据：
      insert into classes(Class_id,Class_Name,Class_Memo) values(1,'计算机','9608');
      insert into classes(Class_id,Class_Name,Class_Memo) values(2,'经济信息','9602');
      insert into classes(Class_id,Class_Name,Class_Memo) values(3,'经济信息','9603');
  在一个SESSION中（比如SQLPLUS登陆）插入上面3条记录，然后再以另外一个SESSION（用SQLPLUS再登陆一次）登陆，当你select * from classes;的时候，classes表是空的，而你再第一次登陆的SQLPLUS中select的时候可以查询到，这个时候你没有进行commit或者rollback之前你可以对刚才插入的3条记录进行update、delete等操作，当你进行commit或者rollback的时候，这个时候由于你的表是事务级的临时表，那么在插入数据的session也看不到数据了，这个时候数据就已经被截断了。
     运行结果如下：
SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(1,'计算机','9608');

1 row inserted

SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(2,'经济信息','9602');

1 row inserted

SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(3,'经济信息','9603');

1 row inserted

SQL> update classes set class_memo ='' where class_id=3 ;

1 row updated

SQL> select * from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------
       1 计算机     9608
       2 经济信息   9602
       3 经济信息  

SQL> delete from classes where class_id=3 ;

1 row deleted

SQL> select * from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------
       1 计算机     9608
       2 经济信息   9602
SQL> commit;

Commit complete

SQL> select *from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------

SQL>
再重复插入一次，然后rollback。
SQL> Rollback ;

Rollback complete

SQL> select * from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- --------------------------------------------------------------------------------

SQL>

4、临时表的应用
    1）、当某一个SQL语句关联的表在2张及以上，并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。
    2）、程序执行过程中可能需要存放一些临时的数据，这些数据在整个程序的会话过程中都需要用的等等。
5、注意事项：
    1）、临时表的索引以及对表的修改、删除等和正常的表是一致的。
    2）、Oracle的临时表是Oracle8i才支持的功能特性，如果你的Oracle版本比较低的话，那么就可能没有办法用到了，如果你的Oracle版本是8i的话，你还需要把$ORACLE_HOME/admin/${ORACLE_SID}/pfile目录下的init<ORACLE_SID>.ora初始参数配置文件的compatible修改为compatible = "8.1.0"，我的服务器上就是这样子配置的。当然也可以修改为compatible = "8.1.6"

以上是我在对大表进行优化的时候采用的一些手段，效果显著。