Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance. Project price only 1 $
You can buy this project and download/modify it how often you want.
/*
* Vaadin Framework 7
*
* Copyright (C) 2000-2024 Vaadin Ltd
*
* This program is available under Vaadin Commercial License and Service Terms.
*
* See for the full
* license.
*/
package com.vaadin.data.util.sqlcontainer.query;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.EventObject;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.vaadin.data.Container.Filter;
import com.vaadin.data.util.filter.Compare.Equal;
import com.vaadin.data.util.sqlcontainer.ColumnProperty;
import com.vaadin.data.util.sqlcontainer.OptimisticLockException;
import com.vaadin.data.util.sqlcontainer.RowId;
import com.vaadin.data.util.sqlcontainer.RowItem;
import com.vaadin.data.util.sqlcontainer.SQLUtil;
import com.vaadin.data.util.sqlcontainer.TemporaryRowId;
import com.vaadin.data.util.sqlcontainer.connection.JDBCConnectionPool;
import com.vaadin.data.util.sqlcontainer.query.generator.DefaultSQLGenerator;
import com.vaadin.data.util.sqlcontainer.query.generator.MSSQLGenerator;
import com.vaadin.data.util.sqlcontainer.query.generator.SQLGenerator;
import com.vaadin.data.util.sqlcontainer.query.generator.StatementHelper;
@SuppressWarnings("serial")
public class TableQuery extends AbstractTransactionalQuery
implements QueryDelegate, QueryDelegate.RowIdChangeNotifier {
/**
* Table name (without catalog or schema information).
*/
private String tableName;
private String catalogName;
private String schemaName;
/**
* Cached concatenated version of the table name.
*/
private String fullTableName;
/**
* Primary key column name(s) in the table.
*/
private List primaryKeyColumns;
/**
* Version column name in the table.
*/
private String versionColumn;
/** Currently set Filters and OrderBys */
private List filters;
private List orderBys;
/** SQLGenerator instance to use for generating queries */
private SQLGenerator sqlGenerator;
/** Row ID change listeners */
private LinkedList rowIdChangeListeners;
/** Row ID change events, stored until commit() is called */
private final List bufferedEvents = new ArrayList();
/** Set to true to output generated SQL Queries to System.out */
private final boolean debug = false;
/**
* Creates a new TableQuery using the given connection pool, SQL generator
* and table name to fetch the data from. All parameters must be non-null.
*
* The table name must be a simple name with no catalog or schema
* information. If those are needed, use
* {@link #TableQuery(String, String, String, JDBCConnectionPool, SQLGenerator)}
* .
*
* @param tableName
* Name of the database table to connect to
* @param connectionPool
* Connection pool for accessing the database
* @param sqlGenerator
* SQL query generator implementation
*/
public TableQuery(String tableName, JDBCConnectionPool connectionPool,
SQLGenerator sqlGenerator) {
this(null, null, tableName, connectionPool, sqlGenerator);
}
/**
* Creates a new TableQuery using the given connection pool, SQL generator
* and table name to fetch the data from. Catalog and schema names can be
* null, all other parameters must be non-null.
*
* @param catalogName
* Name of the database catalog (can be null)
* @param schemaName
* Name of the database schema (can be null)
* @param tableName
* Name of the database table to connect to
* @param connectionPool
* Connection pool for accessing the database
* @param sqlGenerator
* SQL query generator implementation
* @since 7.1
*/
public TableQuery(String catalogName, String schemaName, String tableName,
JDBCConnectionPool connectionPool, SQLGenerator sqlGenerator) {
this(catalogName, schemaName, tableName, connectionPool, sqlGenerator,
true);
}
/**
* Creates a new TableQuery using the given connection pool and table name
* to fetch the data from. All parameters must be non-null. The default SQL
* generator will be used for queries.
*
* The table name must be a simple name with no catalog or schema
* information. If those are needed, use
* {@link #TableQuery(String, String, String, JDBCConnectionPool, SQLGenerator)}
* .
*
* @param tableName
* Name of the database table to connect to
* @param connectionPool
* Connection pool for accessing the database
*/
public TableQuery(String tableName, JDBCConnectionPool connectionPool) {
this(tableName, connectionPool, new DefaultSQLGenerator());
}
/**
* Creates a new TableQuery using the given connection pool, SQL generator
* and table name to fetch the data from. Catalog and schema names can be
* null, all other parameters must be non-null.
*
* @param catalogName
* Name of the database catalog (can be null)
* @param schemaName
* Name of the database schema (can be null)
* @param tableName
* Name of the database table to connect to
* @param connectionPool
* Connection pool for accessing the database
* @param sqlGenerator
* SQL query generator implementation
* @param escapeNames
* true to escape special characters in catalog, schema and table
* names, false to use the names as-is
* @since 7.1
*/
protected TableQuery(String catalogName, String schemaName,
String tableName, JDBCConnectionPool connectionPool,
SQLGenerator sqlGenerator, boolean escapeNames) {
super(connectionPool);
if (tableName == null || tableName.trim().length() < 1
|| connectionPool == null || sqlGenerator == null) {
throw new IllegalArgumentException(
"Table name, connection pool and SQL generator parameters must be non-null and non-empty.");
}
if (escapeNames) {
this.catalogName = SQLUtil.escapeSQL(catalogName);
this.schemaName = SQLUtil.escapeSQL(schemaName);
this.tableName = SQLUtil.escapeSQL(tableName);
} else {
this.catalogName = catalogName;
this.schemaName = schemaName;
this.tableName = tableName;
}
this.sqlGenerator = sqlGenerator;
fetchMetaData();
}
@Override
public int getCount() throws SQLException {
getLogger().log(Level.FINE, "Fetching count...");
StatementHelper sh = sqlGenerator.generateSelectQuery(
getFullTableName(), filters, null, 0, 0, "COUNT(*)");
boolean shouldCloseTransaction = false;
if (!isInTransaction()) {
shouldCloseTransaction = true;
beginTransaction();
}
ResultSet r = null;
int count = -1;
try {
r = executeQuery(sh);
r.next();
count = r.getInt(1);
} finally {
try {
if (r != null) {
// Do not release connection, it is done in commit()
releaseConnection(null, r.getStatement(), r);
}
} finally {
if (shouldCloseTransaction) {
commit();
}
}
}
return count;
}
@Override
public ResultSet getResults(int offset, int pagelength)
throws SQLException {
StatementHelper sh;
/*
* If no ordering is explicitly set, results will be ordered by the
* first primary key column.
*/
if (orderBys == null || orderBys.isEmpty()) {
List ob = new ArrayList();
for (int i = 0; i < primaryKeyColumns.size(); i++) {
ob.add(new OrderBy(primaryKeyColumns.get(i), true));
}
sh = sqlGenerator.generateSelectQuery(getFullTableName(), filters,
ob, offset, pagelength, null);
} else {
sh = sqlGenerator.generateSelectQuery(getFullTableName(), filters,
orderBys, offset, pagelength, null);
}
return executeQuery(sh);
}
@Override
public boolean implementationRespectsPagingLimits() {
return true;
}
@Override
public int storeRow(RowItem row)
throws UnsupportedOperationException, SQLException {
if (row == null) {
throw new IllegalArgumentException(
"Row argument must be non-null.");
}
StatementHelper sh;
int result = 0;
if (row.getId() instanceof TemporaryRowId) {
setVersionColumnFlagInProperty(row);
sh = sqlGenerator.generateInsertQuery(getFullTableName(), row);
result = executeUpdateReturnKeys(sh, row);
} else {
setVersionColumnFlagInProperty(row);
sh = sqlGenerator.generateUpdateQuery(getFullTableName(), row);
result = executeUpdate(sh);
}
if (versionColumn != null && result == 0) {
throw new OptimisticLockException(
"Someone else changed the row that was being updated.",
row.getId());
}
return result;
}
private void setVersionColumnFlagInProperty(RowItem row) {
ColumnProperty versionProperty = (ColumnProperty) row
.getItemProperty(versionColumn);
if (versionProperty != null) {
versionProperty.setVersionColumn(true);
}
}
/**
* Inserts the given row in the database table immediately. Begins and
* commits the transaction needed. This method was added specifically to
* solve the problem of returning the final RowId immediately on the
* SQLContainer.addItem() call when auto commit mode is enabled in the
* SQLContainer.
*
* @param row
* RowItem to add to the database
* @return Final RowId of the added row
* @throws SQLException
*/
public RowId storeRowImmediately(RowItem row) throws SQLException {
beginTransaction();
/* Set version column, if one is provided */
setVersionColumnFlagInProperty(row);
/* Generate query */
StatementHelper sh = sqlGenerator
.generateInsertQuery(getFullTableName(), row);
Connection connection = null;
PreparedStatement pstmt = null;
ResultSet generatedKeys = null;
connection = getConnection();
try {
pstmt = connection.prepareStatement(sh.getQueryString(),
primaryKeyColumns.toArray(new String[0]));
sh.setParameterValuesToStatement(pstmt);
getLogger().log(Level.FINE, "DB -> {0}", sh.getQueryString());
int result = pstmt.executeUpdate();
RowId newId = null;
if (result > 0) {
/*
* If affected rows exist, we'll get the new RowId, commit the
* transaction and return the new RowId.
*/
generatedKeys = pstmt.getGeneratedKeys();
newId = getNewRowId(row, generatedKeys);
}
// transaction has to be closed in any case
commit();
return newId;
} finally {
releaseConnection(connection, pstmt, generatedKeys);
}
}
@Override
public void setFilters(List filters)
throws UnsupportedOperationException {
if (filters == null) {
this.filters = null;
return;
}
this.filters = Collections.unmodifiableList(filters);
}
@Override
public void setOrderBy(List orderBys)
throws UnsupportedOperationException {
if (orderBys == null) {
this.orderBys = null;
return;
}
this.orderBys = Collections.unmodifiableList(orderBys);
}
@Override
public void beginTransaction()
throws UnsupportedOperationException, SQLException {
getLogger().log(Level.FINE, "DB -> begin transaction");
super.beginTransaction();
}
@Override
public void commit() throws UnsupportedOperationException, SQLException {
getLogger().log(Level.FINE, "DB -> commit");
super.commit();
/* Handle firing row ID change events */
RowIdChangeEvent[] unFiredEvents = bufferedEvents
.toArray(new RowIdChangeEvent[] {});
bufferedEvents.clear();
if (rowIdChangeListeners != null && !rowIdChangeListeners.isEmpty()) {
for (RowIdChangeListener r : rowIdChangeListeners) {
for (RowIdChangeEvent e : unFiredEvents) {
r.rowIdChange(e);
}
}
}
}
@Override
public void rollback() throws UnsupportedOperationException, SQLException {
getLogger().log(Level.FINE, "DB -> rollback");
super.rollback();
}
@Override
public List getPrimaryKeyColumns() {
return Collections.unmodifiableList(primaryKeyColumns);
}
public String getVersionColumn() {
return versionColumn;
}
public void setVersionColumn(String column) {
versionColumn = column;
}
/**
* Returns the table name for the query without catalog and schema
* information.
*
* @return table name, not null
*/
public String getTableName() {
return tableName;
}
/**
* Returns the catalog name for the query.
*
* @return catalog name, can be null
* @since 7.1
*/
public String getCatalogName() {
return catalogName;
}
/**
* Returns the catalog name for the query.
*
* @return catalog name, can be null
* @since 7.1
*/
public String getSchemaName() {
return schemaName;
}
/**
* Returns the complete table name obtained by concatenation of the catalog
* and schema names (if any) and the table name.
*
* This method can be overridden if customization is needed.
*
* @return table name in the form it should be used in query and update
* statements
* @since 7.1
*/
protected String getFullTableName() {
if (fullTableName == null) {
StringBuilder sb = new StringBuilder();
if (catalogName != null) {
sb.append(catalogName).append(".");
}
if (schemaName != null) {
sb.append(schemaName).append(".");
}
sb.append(tableName);
fullTableName = sb.toString();
}
return fullTableName;
}
public SQLGenerator getSqlGenerator() {
return sqlGenerator;
}
/**
* Executes the given query string using either the active connection if a
* transaction is already open, or a new connection from this query's
* connection pool.
*
* @param sh
* an instance of StatementHelper, containing the query string
* and parameter values.
* @return ResultSet of the query
* @throws SQLException
*/
private ResultSet executeQuery(StatementHelper sh) throws SQLException {
ensureTransaction();
Connection connection = getConnection();
PreparedStatement pstmt = null;
try {
pstmt = connection.prepareStatement(sh.getQueryString());
sh.setParameterValuesToStatement(pstmt);
getLogger().log(Level.FINE, "DB -> {0}", sh.getQueryString());
return pstmt.executeQuery();
} catch (SQLException e) {
releaseConnection(null, pstmt, null);
throw e;
}
}
/**
* Executes the given update query string using either the active connection
* if a transaction is already open, or a new connection from this query's
* connection pool.
*
* @param sh
* an instance of StatementHelper, containing the query string
* and parameter values.
* @return Number of affected rows
* @throws SQLException
*/
private int executeUpdate(StatementHelper sh) throws SQLException {
PreparedStatement pstmt = null;
Connection connection = null;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sh.getQueryString());
sh.setParameterValuesToStatement(pstmt);
getLogger().log(Level.FINE, "DB -> {0}", sh.getQueryString());
int retval = pstmt.executeUpdate();
return retval;
} finally {
releaseConnection(connection, pstmt, null);
}
}
/**
* Executes the given update query string using either the active connection
* if a transaction is already open, or a new connection from this query's
* connection pool.
*
* Additionally adds a new RowIdChangeEvent to the event buffer.
*
* @param sh
* an instance of StatementHelper, containing the query string
* and parameter values.
* @param row
* the row item to update
* @return Number of affected rows
* @throws SQLException
*/
private int executeUpdateReturnKeys(StatementHelper sh, RowItem row)
throws SQLException {
PreparedStatement pstmt = null;
ResultSet genKeys = null;
Connection connection = null;
try {
connection = getConnection();
pstmt = connection.prepareStatement(sh.getQueryString(),
primaryKeyColumns.toArray(new String[0]));
sh.setParameterValuesToStatement(pstmt);
getLogger().log(Level.FINE, "DB -> {0}", sh.getQueryString());
int result = pstmt.executeUpdate();
genKeys = pstmt.getGeneratedKeys();
RowId newId = getNewRowId(row, genKeys);
bufferedEvents.add(new RowIdChangeEvent(row.getId(), newId));
return result;
} finally {
releaseConnection(connection, pstmt, genKeys);
}
}
/**
* Fetches name(s) of primary key column(s) from DB metadata.
*
* Also tries to get the escape string to be used in search strings.
*/
private void fetchMetaData() {
Connection connection = null;
ResultSet rs = null;
ResultSet tables = null;
try {
connection = getConnection();
DatabaseMetaData dbmd = connection.getMetaData();
if (dbmd != null) {
tables = dbmd.getTables(catalogName, schemaName, tableName,
null);
if (!tables.next()) {
String catalog = (catalogName != null)
? catalogName.toUpperCase() : null;
String schema = (schemaName != null)
? schemaName.toUpperCase() : null;
tables = dbmd.getTables(catalog, schema,
tableName.toUpperCase(), null);
if (!tables.next()) {
throw new IllegalArgumentException(
"Table with the name \"" + getFullTableName()
+ "\" was not found. Check your database contents.");
} else {
catalogName = catalog;
schemaName = schema;
tableName = tableName.toUpperCase();
}
}
tables.close();
rs = dbmd.getPrimaryKeys(catalogName, schemaName, tableName);
List names = new ArrayList();
while (rs.next()) {
names.add(rs.getString("COLUMN_NAME"));
}
rs.close();
if (!names.isEmpty()) {
primaryKeyColumns = names;
}
if (primaryKeyColumns == null || primaryKeyColumns.isEmpty()) {
throw new IllegalArgumentException(
"Primary key constraints have not been defined for the table \""
+ getFullTableName()
+ "\". Use FreeFormQuery to access this table.");
}
for (String colName : primaryKeyColumns) {
if (colName.equalsIgnoreCase("rownum")) {
if (getSqlGenerator() instanceof MSSQLGenerator
|| getSqlGenerator() instanceof MSSQLGenerator) {
throw new IllegalArgumentException(
"When using Oracle or MSSQL, a primary key column"
+ " named \'rownum\' is not allowed!");
}
}
}
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
try {
releaseConnection(connection, null, rs);
} catch (SQLException ignore) {
} finally {
try {
if (tables != null) {
tables.close();
}
} catch (SQLException ignore) {
}
}
}
}
private RowId getNewRowId(RowItem row, ResultSet genKeys) {
try {
/* Fetch primary key values and generate a map out of them. */
Map values = new HashMap();
ResultSetMetaData rsmd = genKeys.getMetaData();
int colCount = rsmd.getColumnCount();
if (genKeys.next()) {
for (int i = 1; i <= colCount; i++) {
values.put(rsmd.getColumnName(i), genKeys.getObject(i));
}
}
/* Generate new RowId */
List
