@RequestMapping("/imptb")
  @Menu(type = "admin" , subtype = "metadata" , admin = true)
  public ModelAndView imptb(final ModelMap map , HttpServletRequest request) throws Exception {
  	this.search(map, request);
  	Session session = (Session) em.getDelegate();
session.doWork(new Work() {
	public void execute(Connection connection) throws SQLException {
		try {
			map.addAttribute("tablesList",
					DatabaseMetaDataHandler.getTables(connection));
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
    		connection.close();
    	}
	}
});

return request(super
		.createRequestPageTempletResponse("/admin/system/metadata/tablelist"));
  }
 

/**
 * Create db structure
 */
private void initDB() {		
	Transaction transaction = session.beginTransaction();
	
	session.doWork(new Work() {
		public void execute(Connection connection) throws SQLException {
			connection.createStatement().execute(
					"CREATE MEMORY TABLE PUBLIC.USER(" +
					"NAME VARCHAR(100)," +
					"LOGIN VARCHAR(100) PRIMARY KEY," +
					"PASSWORD VARCHAR(100)," +
					"BIRTHDATE VARCHAR(100)," +
					"DOCUMENT BLOB," + 
					"CODE NUMERIC, " +
					"CODE2 NUMERIC);");
		}
	});
	
	transaction.commit();
}
 

/**
 * Create db structure
 */
private void initDB() {		
	Transaction transaction = session.beginTransaction();
	
	session.doWork(new Work() {
		public void execute(Connection connection) throws SQLException {
			connection.createStatement().execute(
					"CREATE MEMORY TABLE PUBLIC.USER(" +
					"NAME VARCHAR(100)," +
					"LOGIN VARCHAR(100) PRIMARY KEY," +
					"PASSWORD VARCHAR(100)," +
					"BIRTHDATE VARCHAR(100)," +
					"DOCUMENT BLOB);");
		}
	});
	
	transaction.commit();
}
 

@Transactional(readOnly = false)
public void executeBatch(final String sql, final Object[]... parameters) {
    getSession().doWork(new Work() {

        public void execute(Connection connection) throws SQLException {
            connection.setAutoCommit(false);
            PreparedStatement stmt = connection.prepareStatement(sql);
            for (Object[] arr : parameters) {
                int i = 1;
                for (Object p : arr) {
                    stmt.setObject(i++, p);
                }
                stmt.addBatch();
            }
            stmt.executeBatch();
            connection.commit();
        }
    });
}
 

/**
 * List tables from database
 */
private List<String> listTables(Session session) {
	final List<String> tables = new ArrayList<String>();
	final String[] tableTypes = {"TABLE"};
	final String[] tablePatterns = new String[]{"JBPM_%", "OKM_%", "DEFAULT_%", "VERSION_%", "jbpm_%", "okm_%", "default_%",
			"version_%"};

	session.doWork(new Work() {
		@Override
		public void execute(Connection con) throws SQLException {
			DatabaseMetaData md = con.getMetaData();

			for (String table : tablePatterns) {
				ResultSet rs = md.getTables(null, null, table, tableTypes);

				while (rs.next()) {
					tables.add(rs.getString(3));
				}

				rs.close();
			}
		}
	});

	return tables;
}
 

@Override
 public Object beginTransaction(final EntityManager entityManager, 
 		final TransactionDefinition definition) throws PersistenceException, SQLException, TransactionException {
 	
 	Session session = (Session) entityManager.getDelegate();
 	if (definition.getTimeout() != TransactionDefinition.TIMEOUT_DEFAULT) {
 		getSession(entityManager).getTransaction().setTimeout(definition.getTimeout());
 	}
 	entityManager.getTransaction().begin();
 	logger.debug("Transaction started");
 	session.doWork(new Work() {
@Override
public void execute(Connection connection) throws SQLException {
	 logger.debug("The connection instance is " + connection.toString());
	 logger.debug("The isolation level of the connection is " + connection.getTransactionIsolation() 
			 + " and the isolation level set on the transaction is " + definition.getIsolationLevel() );
	 DataSourceUtils.prepareConnectionForTransaction(connection, definition);
}
 	});
 	return prepareTransaction(entityManager, definition.isReadOnly(), definition.getName());
 }
 

public void runScript() {
    getSession().doWork(new Work() {
        public void execute(Connection conn) throws SQLException {
            if(JdbcHelper.isExec(conn)) {
                return;
            }
            try {
                String databaseType = JdbcHelper.getDatabaseType(conn);
                String schema = "db/core/schema-" + databaseType + ".sql";
                ScriptRunner runner = new ScriptRunner(conn, true);
                runner.runScript(schema);
            } catch (Exception e) {
                throw new SnakerException(e);
            }
        }
    });
}
 

private void insertNewRow(Session session) {
    session.doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            Statement statement = null;
            try {
                statement = connection.createStatement();
                statement.executeUpdate("INSERT INTO sequenceIdentifier VALUES NEXT VALUE FOR hibernate_sequence");
            } finally {
                if (statement != null) {
                    statement.close();
                }
            }
        }
    });
}
 

@Test
public void test() {
    Session session = null;
    Transaction txn = null;
    try {
        session = getSessionFactory().openSession();
        txn = session.beginTransaction();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                LOGGER.debug("Transaction isolation level is {}", Environment.isolationLevelToString(connection.getTransactionIsolation()));
            }
        });
        txn.commit();
    } catch (RuntimeException e) {
        if ( txn != null && txn.isActive() ) txn.rollback();
        throw e;
    } finally {
        if (session != null) {
            session.close();
        }
    }
}
 

@Test
public void test() {
    Session session = null;
    Transaction txn = null;
    try {
        session = getSessionFactory().openSession();
        txn = session.beginTransaction();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                LOGGER.debug("Transaction isolation level is {}", Environment.isolationLevelToString(connection.getTransactionIsolation()));
            }
        });
        txn.commit();
    } catch (RuntimeException e) {
        if ( txn != null && txn.isActive() ) txn.rollback();
        throw e;
    } finally {
        if (session != null) {
            session.close();
        }
    }
}
 

@Test
public void test() {
    Session session = null;
    Transaction txn = null;
    try {
        session = getSessionFactory().openSession();
        txn = session.beginTransaction();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                LOGGER.debug("Transaction isolation level is {}", Environment.isolationLevelToString(connection.getTransactionIsolation()));
            }
        });
        txn.commit();
    } catch (RuntimeException e) {
        if ( txn != null && txn.isActive() ) txn.rollback();
        throw e;
    } finally {
        if (session != null) {
            session.close();
        }
    }
}
 

@Test
public void test() {
    Session session = null;
    Transaction txn = null;
    try {
        session = getSessionFactory().openSession();
        txn = session.beginTransaction();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                LOGGER.debug("Transaction isolation level is {}", Environment.isolationLevelToString(connection.getTransactionIsolation()));
            }
        });
        txn.commit();
    } catch (RuntimeException e) {
        if ( txn != null && txn.isActive() ) txn.rollback();
        throw e;
    } finally {
        if (session != null) {
            session.close();
        }
    }
}
 

@Override
public void doWork(final Work work) throws HibernateException {
	WorkExecutorVisitable<Void> realWork = new WorkExecutorVisitable<Void>() {
		@Override
		public Void accept(WorkExecutor<Void> workExecutor, Connection connection) throws SQLException {
			workExecutor.executeWork( work, connection );
			return null;
		}
	};
	doWork( realWork );
}
 

@Transactional(readOnly = false)
public void executeBatch(final String[] sqlList) {
    getSession().doWork(new Work() {

        public void execute(Connection connection) throws SQLException {
            connection.setAutoCommit(false);
            Statement stmt = connection.createStatement();
            for (String sql : sqlList) {
                stmt.addBatch(sql);
            }
            stmt.executeBatch();
            connection.commit();
        }
    });
}
 

@Override
protected void afterInit() {
    doInJPA(new JPATransactionFunction<Void>() {
        @Override
        public Void apply(EntityManager entityManager) {
            entityManager.unwrap(Session.class).doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    Statement statement = null;
                    try {
                        statement = connection.createStatement();

                        statement.executeUpdate(
                            "ALTER TABLE event MOVE LOB (location) STORE AS (CACHE)"
                        );

                        statement.executeUpdate(
                            "ALTER TABLE participant MOVE LOB (ticket, metadata) STORE AS (CACHE)"
                        );
                    } finally {
                        if(statement != null) {
                            statement.close();
                        }
                    }
                }
            });

            return null;
        }
    });
}
 

@Test
public void testJDBCQuery() {
    doInJPA(new JPATransactionFunction<Void>() {

        @Override
        public Void apply(EntityManager entityManager) {
            Session session = entityManager.unwrap(Session.class);
            session.doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    PreparedStatement ps = null;
                    try {

                        ps = connection.prepareStatement(
                            "SELECT * " +
                            "FROM Event e " +
                            "WHERE " +
                            "   e.ip && ?::inet = true"
                        );

                        ps.setObject(1, "192.168.0.1/24");
                        ResultSet rs = ps.executeQuery();
                        while(rs.next()) {
                            Long id = rs.getLong(1);
                            String ip = rs.getString(2);
                            assertEquals("192.168.0.123/24", ip);
                        }
                    } finally {
                        if (ps != null) {
                            ps.close();
                        }
                    }
                }
            });

            return null;
        }
    });
}
 

@Test
public void testJDBCQuery() {
    doInJPA(new JPATransactionFunction<Void>() {

        @Override
        public Void apply(EntityManager entityManager) {
            Session session = entityManager.unwrap(Session.class);
            session.doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    PreparedStatement ps = null;
                    try {

                        ps = connection.prepareStatement(
                            "SELECT * " +
                            "FROM Event e " +
                            "WHERE " +
                            "   e.ip && ?::inet = true"
                        );

                        ps.setObject(1, "192.168.0.1/24");
                        ResultSet rs = ps.executeQuery();
                        while(rs.next()) {
                            Long id = rs.getLong(1);
                            String ip = rs.getString(2);
                            assertEquals("192.168.0.123/24", ip);
                        }
                    } finally {
                        if (ps != null) {
                            ps.close();
                        }
                    }
                }
            });

            return null;
        }
    });
}
 

@Override
protected void afterInit() {
    doInJPA(new JPATransactionFunction<Void>() {
        @Override
        public Void apply(EntityManager entityManager) {
            entityManager.unwrap(Session.class).doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    Statement statement = null;
                    try {
                        statement = connection.createStatement();

                        statement.executeUpdate(
                            "ALTER TABLE event MOVE LOB (location) STORE AS (CACHE)"
                        );

                        statement.executeUpdate(
                            "ALTER TABLE participant MOVE LOB (ticket, metadata) STORE AS (CACHE)"
                        );
                    } finally {
                        if(statement != null) {
                            statement.close();
                        }
                    }
                }
            });

            return null;
        }
    });
}
 

@Test
public void testJDBCQuery() {
    doInJPA(new JPATransactionFunction<Void>() {

        @Override
        public Void apply(EntityManager entityManager) {
            Session session = entityManager.unwrap(Session.class);
            session.doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    PreparedStatement ps = null;
                    try {

                        ps = connection.prepareStatement(
                            "SELECT * " +
                            "FROM Event e " +
                            "WHERE " +
                            "   e.ip && ?::inet = true"
                        );

                        ps.setObject(1, "192.168.0.1/24");
                        ResultSet rs = ps.executeQuery();
                        while(rs.next()) {
                            Long id = rs.getLong(1);
                            String ip = rs.getString(2);
                            assertEquals("192.168.0.123/24", ip);
                        }
                    } finally {
                        if (ps != null) {
                            ps.close();
                        }
                    }
                }
            });

            return null;
        }
    });
}
 

@Override
public Object beginTransaction(final EntityManager entityManager,
		final TransactionDefinition definition)
		throws PersistenceException, SQLException, TransactionException {

	Session session = (Session) entityManager.getDelegate();
	if (definition.getTimeout() != TransactionDefinition.TIMEOUT_DEFAULT) {
		getSession(entityManager).getTransaction().setTimeout(
				definition.getTimeout());
	}

	final TransactionData data = new TransactionData();

	session.doWork(new Work() {
		@Override
		public void execute(Connection connection) throws SQLException {
			Integer previousIsolationLevel = DataSourceUtils
					.prepareConnectionForTransaction(connection, definition);
			data.setPreviousIsolationLevel(previousIsolationLevel);
			data.setConnection(connection);
		}
	});

	entityManager.getTransaction().begin();

	Object springTransactionData = prepareTransaction(entityManager,
			definition.isReadOnly(), definition.getName());

	data.setSpringTransactionData(springTransactionData);

	return data;
}
 

/**
 * Execute report
 */
private static void executeDatabase(Session dbSession, final ByteArrayOutputStream baos, final JasperReport jr,
                                    final Map<String, Object> params, final int format) {
	dbSession.doWork(new Work() {
		@Override
		public void execute(Connection con) throws SQLException {
			try {
				ReportUtils.generateReport(baos, jr, params, format, con);
			} catch (JRException e) {
				throw new SQLException(e.getMessage(), e);
			}
		}
	});
}
 

/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param sql
 * @param objcts
 * @param commitNumber
 * @throws DaoException <br>
 */
@Override
public <T> void batchExecute(final String sql, final Collection<Object[]> objcts, final int commitNumber)
    throws DaoException {
    Session session = getSession();
    session.flush();

    session.doWork(new Work() {

        @Override
        public void execute(final Connection connection) throws SQLException {
            PreparedStatement stmt = null;
            try {
                stmt = connection.prepareStatement(sql);
                connection.setAutoCommit(false);
                int i = 0;
                for (Object[] object : objcts) {
                    i++;
                    for (int j = 0; j < object.length; j++) {
                        stmt.setObject(j + 1, object[j]);
                    }
                    stmt.addBatch();
                    if (i % commitNumber == 0) {
                        stmt.executeBatch();
                        connection.commit();
                    }
                }
                stmt.executeBatch();
                connection.commit();
            }
            finally {
                if (stmt != null) {
                    stmt.close();
                }
            }
        }
    });
}
 

@Override
public void createSequence(final String sequenceName) {

    if (sequenceExists(sequenceName)) {
        return;
    }
    Work work = new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            DialectResolver dialectResolver = new StandardDialectResolver();
            Dialect dialect = dialectResolver.resolveDialect(getResolutionInfo(connection));
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                preparedStatement = connection
                        .prepareStatement(dialect.getCreateSequenceStrings(sequenceName, 1, 1)[0]);
                preparedStatement.execute();
            } catch (SQLException e) {
                throw e;
            } finally {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
            }

        }
    };

    Session session = (Session) entityManager.getDelegate();
    SessionFactory sessionFactory = session.getSessionFactory();
    sessionFactory.getCurrentSession().doWork(work);
}
 

@Override
public void dropSequence(final String sequenceName) {

    Work work = new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            DialectResolver dialectResolver = new StandardDialectResolver();
            Dialect dialect = dialectResolver.resolveDialect(getResolutionInfo(connection));
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                preparedStatement = connection.prepareStatement(dialect.getDropSequenceStrings(sequenceName)[0]);
                preparedStatement.execute();
            } catch (SQLException e) {
                throw e;
            } finally {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
            }

        }
    };

    Session session = (Session) entityManager.getDelegate();
    SessionFactory sessionFactory = session.getSessionFactory();
    sessionFactory.getCurrentSession().doWork(work);
}
 

public void truncate() {
    em.unwrap(Session.class).doWork(new Work() {
        @Override
        public void execute(final Connection connection) throws SQLException {
            connection.createStatement().execute("truncate table showcase_excel_data");
        }
    });


}
 

@Override
@Transactional(isolation = Isolation.SERIALIZABLE)
public void purchase(Long productId) {
    Product product = entityManager.find(Product.class, 1L);
    Session session = (Session) entityManager.getDelegate();
    session.doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            LOGGER.debug("Transaction isolation level is {}", Environment.isolationLevelToString(connection.getTransactionIsolation()));
        }
    });
    product.setQuantity(product.getQuantity() - 1);
}
 

@RequestMapping("/addsqlsave")
@Menu(type = "admin" , subtype = "metadata" , admin = true)
public ModelAndView addsqlsave(ModelMap map , HttpServletRequest request , final @Valid String datasql , final @Valid String name) throws Exception {
	if(!StringUtils.isBlank(datasql) && !StringUtils.isBlank(name)){
		final User user = super.getUser(request);
 	Session session = (Session) em.getDelegate();
 	session.doWork(
 		    new Work() {
 		        public void execute(Connection connection) throws SQLException 
 		        {
 		        	try{
 		        		int count = metadataRes.countByTablename(name) ;
				    		if(count == 0){
			 		    		MetadataTable metaDataTable = new MetadataTable();
				  				//当前记录没有被添加过，进行正常添加
				  				metaDataTable.setTablename(name);
				  				metaDataTable.setOrgi(user.getOrgi());
				  				metaDataTable.setId(UKTools.md5(metaDataTable.getTablename()));
				  				metaDataTable.setTabledirid("0");
				  				metaDataTable.setCreater(user.getId());
				  				metaDataTable.setCreatername(user.getUsername());
				  				metaDataTable.setName(name);
				  				metaDataTable.setDatasql(datasql);
				  				metaDataTable.setTabletype("2");
				  				metaDataTable.setUpdatetime(new Date());
				  				metaDataTable.setCreatetime(new Date());
				  				metadataRes.save(processMetadataTable( DatabaseMetaDataHandler.getSQL(connection, name, datasql) , metaDataTable));
				    		}
 			    	}catch(Exception ex){
 			    		ex.printStackTrace();
 			    	}finally{
 			    		connection.close();
 			    	}
 		        }
 		    }
 		);
 	
	}
	
    return request(super.createRequestPageTempletResponse("redirect:/admin/metadata/index.html"));
}
 

@RequestMapping("/sync")
  @Menu(type = "admin" , subtype = "metadata" , admin = true)
  public ModelAndView sync(ModelMap map , HttpServletRequest request , @Valid String id) throws SQLException {
  	final MetadataTable table = metadataRes.findById(id) ;
  	Session session = (Session) em.getDelegate();
session.doWork(new Work() {
	public void execute(Connection connection) throws SQLException {
		try {
			MetadataTable metaDataTable = new MetadataTable();
			//当前记录没有被添加过，进行正常添加
 				metaDataTable.setTablename(table.getTablename());
 				metaDataTable.setOrgi(table.getOrgi());
 				metaDataTable.setId(UKTools.md5(metaDataTable.getTablename()));
 				metaDataTable.setTabledirid("0");
 				metaDataTable.setCreater(table.getCreater());
 				metaDataTable.setCreatername(table.getCreatername());
 				metaDataTable.setName(table.getName());
 				metaDataTable.setUpdatetime(new Date());
 				metaDataTable.setCreatetime(new Date());
			processMetadataTable( DatabaseMetaDataHandler.getTable(connection, metaDataTable.getTablename()) , metaDataTable) ;
			for(TableProperties temp : metaDataTable.getTableproperty()) {
				boolean found = false ;
				for(TableProperties tp : table.getTableproperty()) {
					if(temp.getFieldname().equals(tp.getFieldname())) {
						found = true ;
						break ;
					}
				}
				if(found == false) {
					temp.setDbtableid(table.getId());
					tablePropertiesRes.save(temp) ;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
    		connection.close();
    	}
	}
});
  	return request(super.createRequestPageTempletResponse("redirect:/admin/metadata/table.html?id="+id));
  }
 

@RequestMapping("/imptbsave")
@Menu(type = "admin" , subtype = "metadata" , admin = true)
public ModelAndView imptb(ModelMap map , HttpServletRequest request , final @Valid String[] tables) throws Exception {
	final User user = super.getUser(request) ;
	if(tables!=null && tables.length > 0){
 	Session session = (Session) em.getDelegate();
 	session.doWork(
 		    new Work() {
 		        public void execute(Connection connection) throws SQLException 
 		        {
 		        	try{
 				    	for(String table : tables){
 				    		int count = metadataRes.countByTablename(table) ;
 				    		if(count == 0){
 			 		    		MetadataTable metaDataTable = new MetadataTable();
 				  				//当前记录没有被添加过，进行正常添加
 				  				metaDataTable.setTablename(table);
 				  				metaDataTable.setOrgi(user.getOrgi());
 				  				metaDataTable.setId(UKTools.md5(metaDataTable.getTablename()));
 				  				metaDataTable.setTabledirid("0");
 				  				metaDataTable.setCreater(user.getId());
 				  				metaDataTable.setCreatername(user.getUsername());
 				  				metaDataTable.setName(table);
 				  				metaDataTable.setUpdatetime(new Date());
 				  				metaDataTable.setCreatetime(new Date());
 				  				metadataRes.save(processMetadataTable( DatabaseMetaDataHandler.getTable(connection, metaDataTable.getTablename()) , metaDataTable));
 				    		}
 				    	}
 			    	}catch(Exception ex){
 			    		ex.printStackTrace();
 			    	}finally{
 			    		connection.close();
 			    	}
 		        }
 		    }
 		);
 	
	}
	
    return request(super.createRequestPageTempletResponse("redirect:/admin/metadata/index.html"));
}
 

@Override
public void doWork(Work work) throws HibernateException {
	delegate.doWork( work );
}