/**
* Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on
* an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the
* specific language governing permissions and limitations under the License.
*
* Copyright 2012-2016 the original author or authors.
*/
package org.assertj.db.type;
import org.assertj.db.exception.AssertJDBException;
import org.assertj.db.type.lettercase.LetterCase;
import org.assertj.db.util.NameComparator;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
/**
* A table in the database to read to get the values.
* <p>
* The different informations of the table are {@link Source} or {@link DataSource}, name of the table and optionally
* the columns to check and to exclude.
* </p>
* <p>
* Examples of instantiation :
* </p>
* <ul>
* <li>
* <p>
* This {@link Table} point to a table called {@code movie} in a H2 database in memory like indicated in the
* {@link Source}.
* </p>
*
* <pre>
* <code class='java'>
* Source source = new Source("jdbc:h2:mem:test", "sa", "");
* Table table = new Table(source, "movie");
* </code>
* </pre>
*
* </li>
* <li>
* <p>
* Below the {@link Table} {@code table1} point to a table called {@code song} (but only on the columns called
* {@code number} and {@code title}).<br>
* And the {@link Table} {@code table2} point to a table called {@code musician} (but ignore on the column called
* {@code birthday}).<br>
* The {@link Table} use a {@code DataSource} instead of a {@link Source} like above.
* </p>
*
* <pre>
* <code class='java'>
* DataSource dataSource = ...;
* Table table1 = new Table(dataSource, "song", new String[] { "number", "title" }, null);
* Table table2 = new Table(dataSource, "musician", null, new String[] { "birthday" });
* </code>
* </pre>
*
* </li>
* </ul>
*
* @author Régis Pouiller
*/
public class Table extends AbstractDbData<Table> {
/**
* The name of the table.
*/
private String name;
/**
* The list of columns of the table.
*/
private List<String> columnsList;
/**
* The columns to check.
*/
private String[] columnsToCheck;
/**
* The columns to exclude.
*/
private String[] columnsToExclude;
/**
* The columns to order.
* @since 1.2.0
*/
private Order[] columnsToOrder;
/**
* Indicates an order with the name on which is the order and the type.
* @see org.assertj.db.type.Table.Order.OrderType
*/
public static class Order {
/**
* The name of the order.
*/
private String name;
/**
* The type of the order.
*/
private OrderType type;
/**
* Enumeration of the type of order.
*/
public enum OrderType {
/**
* Ascending order.
*/
ASC,
/**
* Descending order.
*/
DESC;
}
/**
* Builds an ascending order.
* @param name The name of the order.
* @return An ascending order.
*/
public static Order asc(String name) {
return getOrder(name, OrderType.ASC);
}
/**
* Builds the descending order.
* @param name The name of the order.
* @return The descending order.
*/
public static Order desc(String name) {
return getOrder(name, OrderType.DESC);
}
/**
* Builds an order.
* @param name The name of the order.
* @param type The type of the order.
* @return The instantiated order.
*/
private static Order getOrder(String name, OrderType type) {
return new Order(name, type);
}
/**
* Constructor.
* @param name The name of the order.
* @param type The type of the order.
*/
private Order(String name, OrderType type) {
this.name = name;
this.type = type;
}
/**
* Returns the name of the order.
* @return The name of the order.
*/
public String getName() {
return name;
}
/**
* Returns the type of the order.
* @return The type of the order.
*/
public OrderType getType() {
return type;
}
@Override
public boolean equals(Object obj) {
if (obj instanceof Order) {
Order order = (Order) obj;
if (order.type == type) {
if ((name == null && order.name == null) ||
(name != null && name.equals(order.name))) {
return true;
}
}
}
return false;
}
}
/**
* Default constructor.
*/
public Table() {
super(Table.class, DataType.TABLE);
}
/**
* Constructor with a {@link Source} and the name of the table.
*
* @param source {@link Source} to connect to the database.
* @param name Name of the table.
*/
public Table(Source source, String name) {
this(source, name, null, null, null);
}
/**
* Constructor with a {@link Source}, the name of the table and the columns to check and to exclude.
*
* @param source {@link Source} to connect to the database.
* @param name Name of the table.
* @param columnsToCheck Array of the name of the columns to check. If {@code null} that means to check all the
* columns.
* @param columnsToExclude Array of the name of the columns to exclude. If {@code null} that means to exclude no
* column.
*/
public Table(Source source, String name, String[] columnsToCheck, String[] columnsToExclude) {
this(source, name, null, columnsToCheck, columnsToExclude);
}
/**
* Constructor with a dataSource and the name of the table.
*
* @param dataSource DataSource of the database.
* @param name Name of the table.
*/
public Table(DataSource dataSource, String name) {
this(dataSource, name, null, null, null);
}
/**
* Constructor with a connection, the name of the table and the columns to check and to exclude.
*
* @param dataSource DataSource of the database.
* @param name Name of the table.
* @param columnsToCheck Array of the name of the columns to check. If {@code null} that means to check all the
* columns.
* @param columnsToExclude Array of the name of the columns to exclude. If {@code null} that means to exclude no
* column.
*/
public Table(DataSource dataSource, String name, String[] columnsToCheck, String[] columnsToExclude) {
this(dataSource, name, null, columnsToCheck, columnsToExclude);
}
/**
* Constructor with a {@link Source} and the name of the table.
*
* @param source {@link Source} to connect to the database.
* @param name Name of the table.
* @since 1.2.0
*/
public Table(Source source, String name, Order[] columnsToOrder) {
this(source, name, columnsToOrder, null, null);
}
/**
* Constructor with a {@link Source}, the name of the table and the columns to check and to exclude.
*
* @param source {@link Source} to connect to the database.
* @param name Name of the table.
* @param columnsToCheck Array of the name of the columns to check. If {@code null} that means to check all the
* columns.
* @param columnsToExclude Array of the name of the columns to exclude. If {@code null} that means to exclude no
* column.
* @since 1.2.0
*/
public Table(Source source, String name, Order[] columnsToOrder, String[] columnsToCheck, String[] columnsToExclude) {
super(Table.class, DataType.TABLE, source);
setName(name);
setColumnsToOrder(columnsToOrder);
setColumnsToCheck(columnsToCheck);
setColumnsToExclude(columnsToExclude);
}
/**
* Constructor with a dataSource and the name of the table.
*
* @param dataSource DataSource of the database.
* @param name Name of the table.
* @since 1.2.0
*/
public Table(DataSource dataSource, String name, Order[] columnsToOrder) {
this(dataSource, name, columnsToOrder, null, null);
}
/**
* Constructor with a connection, the name of the table and the columns to check and to exclude.
*
* @param dataSource DataSource of the database.
* @param name Name of the table.
* @param columnsToCheck Array of the name of the columns to check. If {@code null} that means to check all the
* columns.
* @param columnsToExclude Array of the name of the columns to exclude. If {@code null} that means to exclude no
* column.
* @since 1.2.0
*/
public Table(DataSource dataSource, String name, Order[] columnsToOrder, String[] columnsToCheck, String[] columnsToExclude) {
super(Table.class, DataType.TABLE, dataSource);
setName(name);
setColumnsToOrder(columnsToOrder);
setColumnsToCheck(columnsToCheck);
setColumnsToExclude(columnsToExclude);
}
/**
* Return the name of the table.
*
* @return the name of the table.
* @see #setName(String)
*/
public String getName() {
return name;
}
/**
* Sets the name of the table.
*
* @param name The name of the table.
* @return The actual instance.
* @see #getName()
*/
public Table setName(String name) {
if (name == null) {
throw new NullPointerException("name can not be null");
}
this.name = name;
setNameFromDb();
return this;
}
/** {@inheritDoc} */
@Override
public Table setDataSource(DataSource dataSource) {
Table table = super.setDataSource(dataSource);
setNameFromDb();
return table;
}
/** {@inheritDoc} */
@Override
public Table setSource(Source source) {
Table table = super.setSource(source);
setNameFromDb();
return table;
}
/**
* Set the name from the corresponding name in the database.
*/
private void setNameFromDb() {
if (name != null && (getSource() != null || getDataSource() != null)) {
try (Connection connection = getConnection()) {
LetterCase tableLetterCase = getTableLetterCase();
LetterCase columnLetterCase = getColumnLetterCase();
DatabaseMetaData metaData = connection.getMetaData();
try (ResultSet tableResultSet = metaData.getTables(getCatalog(connection), getSchema(connection), null,
new String[] { "TABLE" })) {
while (tableResultSet.next()) {
String tableName = tableResultSet.getString("TABLE_NAME");
if (tableLetterCase.isEqual(tableName, name)) {
name = tableLetterCase.convert(tableName);
break;
}
}
}
columnsList = new ArrayList<>();
try (ResultSet columnsResultSet = metaData.getColumns(getCatalog(connection), getSchema(connection), name, null)) {
while (columnsResultSet.next()) {
String column = columnsResultSet.getString("COLUMN_NAME");
columnsList.add(columnLetterCase.convert(column));
}
}
} catch (SQLException e) {
throw new AssertJDBException(e);
}
}
}
/**
* Returns the columns to check (which are present in {@link AbstractDbData#getColumnsNameList()}).
*
* @return Array of the name of the columns to check. If {@code null} that means to check all the columns.
* @see #setColumnsToCheck(String[])
*/
public String[] getColumnsToCheck() {
if (columnsToCheck == null) {
return null;
}
return columnsToCheck.clone();
}
/**
* Sets the columns to check (which are present in {@link AbstractDbData#getColumnsNameList()}).
*
* @param columnsToCheck Array of the name of the columns to check. If {@code null} that means to check all the
* columns.
* @return The actual instance.
* @throws NullPointerException If one of the name in {@code columnsToCheck} is {@code null}.
* @see #getColumnsToCheck()
*/
public Table setColumnsToCheck(String[] columnsToCheck) {
if (columnsList == null) {
throw new AssertJDBException("The table name and the source or datasource must be set first");
}
if (columnsToCheck != null) {
LetterCase letterCase = getColumnLetterCase();
// If the parameter is not null, all the names are convert
// before setting the instance field
this.columnsToCheck = new String[columnsToCheck.length];
for (int index = 0; index < columnsToCheck.length; index++) {
String column = columnsToCheck[index];
if (column == null) {
throw new NullPointerException("The name of the column can not be null");
}
int indexOf = NameComparator.INSTANCE.indexOf(columnsList, column, letterCase);
if (indexOf != -1) {
this.columnsToCheck[index] = columnsList.get(indexOf);
}
}
} else {
this.columnsToCheck = null;
}
return this;
}
/**
* Returns the columns to exclude (which are not present in {@link AbstractDbData#getColumnsNameList()}).
*
* @return The columns.
* @see #setColumnsToExclude(String[])
*/
public String[] getColumnsToExclude() {
if (columnsToExclude == null) {
return null;
}
return columnsToExclude.clone();
}
/**
* Sets the columns to exclude (which are not present in {@link AbstractDbData#getColumnsNameList()}).
*
* @param columnsToExclude The columns.
* @return The actual instance.
* @see #getColumnsToExclude()
*/
public Table setColumnsToExclude(String[] columnsToExclude) {
if (columnsList == null) {
throw new AssertJDBException("The table name and the source or datasource must be set first");
}
if (columnsToExclude != null) {
LetterCase letterCase = getColumnLetterCase();
this.columnsToExclude = new String[columnsToExclude.length];
for (int index = 0; index < columnsToExclude.length; index++) {
String column = columnsToExclude[index];
if (column == null) {
throw new NullPointerException("The name of the column can not be null");
}
int indexOf = NameComparator.INSTANCE.indexOf(columnsList, column, letterCase);
if (indexOf != -1) {
this.columnsToExclude[index] = columnsList.get(indexOf);
}
}
} else {
this.columnsToExclude = null;
}
return this;
}
/**
* Returns the columns to order (which are used in {@code ORDER BY}).
*
* @return Array of the name of the columns to order. If {@code null} that means not to do order.
* @see #setColumnsToOrder(Order[])
*/
public Order[] getColumnsToOrder() {
if (columnsToOrder == null) {
return null;
}
return columnsToOrder.clone();
}
/**
* Sets the columns to order (which are used in {@code ORDER BY}).
*
* @param columnsToOrder The columns.
* @return The actual instance.
* @see #getColumnsToOrder()
*/
public Table setColumnsToOrder(Order[] columnsToOrder) {
if (columnsList == null) {
throw new AssertJDBException("The table name and the source or datasource must be set first");
}
if (columnsToOrder != null) {
LetterCase letterCase = getColumnLetterCase();
this.columnsToOrder = new Order[columnsToOrder.length];
for (int index = 0; index < columnsToOrder.length; index++) {
Order order = columnsToOrder[index];
if (order == null) {
throw new NullPointerException("The order can not be null");
}
String column = order.getName();
if (order == null) {
throw new NullPointerException("The name of the column for order can not be null");
}
int indexOf = NameComparator.INSTANCE.indexOf(columnsList, column, letterCase);
if (indexOf != -1) {
String columnName = columnsList.get(indexOf);
this.columnsToOrder[index] = Order.getOrder(columnName, order.getType());
}
}
} else {
this.columnsToOrder = null;
}
return this;
}
/**
* Returns the SQL request.
*
* @return The SQL request.
* @throws NullPointerException If the {@link #name} field is {@code null}.
* @see AbstractDbData#getRequest()
*/
public String getRequest() {
if (name == null) {
throw new NullPointerException("name can not be null");
}
// Get the request about the name of the table and the columns to check
StringBuilder stringBuilder = new StringBuilder("SELECT ");
if (columnsToCheck == null) {
stringBuilder.append("*");
} else {
for (String column : columnsToCheck) {
if (stringBuilder.length() > 7) {
stringBuilder.append(", ");
}
stringBuilder.append(column);
}
}
stringBuilder.append(" FROM ");
stringBuilder.append(name);
if (columnsToOrder != null) {
for (int index = 0 ; index < columnsToOrder.length ; index++) {
if (index == 0) {
stringBuilder.append(" ORDER BY ");
}
else {
stringBuilder.append(", ");
}
stringBuilder.append(columnsToOrder[index].getName());
if (columnsToOrder[index].getType() == Order.OrderType.DESC) {
stringBuilder.append(" DESC");
}
}
}
return stringBuilder.toString();
}
/**
* Collects the columns name from the {@code ResultSet} on the table.
* <p>
* This method use the {@link ResultSetMetaData} from the <code>resultSet</code> parameter to list the name of the
* columns. But the columns to exclude are not collected.
* </p>
*
* @param resultSet The {@code ResultSet}.
* @throws SQLException SQL Exception.
*/
private void collectColumnsNameFromResultSet(ResultSet resultSet) throws SQLException {
LetterCase letterCase = getColumnLetterCase();
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
List<String> columnsNameList = new ArrayList<>();
List<String> columnsToExcludeList = null;
if (columnsToExclude != null) {
columnsToExcludeList = Arrays.asList(columnsToExclude);
}
for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
String columnName = letterCase.convert(resultSetMetaData.getColumnLabel(i));
if (columnsToExcludeList == null
|| !NameComparator.INSTANCE.contains(columnsToExcludeList, columnName, letterCase)) {
columnsNameList.add(columnName);
}
}
setColumnsNameList(columnsNameList);
}
/**
* Collects the primary key name from the {@code Connection} to the database.
* <p>
* This method use the {@link DatabaseMetaData} from the {@code Connection} parameter to list the primary keys of the
* table.
* </p>
*
* @param connection The {@code Connection} to the database.
* @throws SQLException SQL Exception.
*/
private void collectPrimaryKeyName(Connection connection) throws SQLException {
String catalog = getCatalog(connection);
String schema = getSchema(connection);
List<String> pksNameList = new ArrayList<>();
DatabaseMetaData metaData = connection.getMetaData();
String tableName = name;
try (ResultSet resultSet = metaData.getTables(catalog, schema, null, new String[] { "TABLE" })) {
LetterCase letterCase = getTableLetterCase();
while (resultSet.next()) {
String tableResult = resultSet.getString("TABLE_NAME");
if (letterCase.isEqual(tableName, tableResult)) {
tableName = tableResult;
break;
}
}
}
try (ResultSet resultSet = metaData.getPrimaryKeys(catalog, schema, tableName)) {
LetterCase letterCase = getPrimaryKeyLetterCase();
while (resultSet.next()) {
String columnName = resultSet.getString("COLUMN_NAME");
if (NameComparator.INSTANCE.contains(getColumnsNameList(), columnName, letterCase)) {
String pkName = letterCase.convert(columnName);
pksNameList.add(pkName);
}
}
}
setPksNameList(pksNameList);
}
/**
* Specific implementation of the loading for a {@code Table}.
*
* @param connection {@link Connection} to the database provided by {@link AbstractDbData#load()} private method.
* @throws NullPointerException If the {@link #name} field is {@code null}.
* @throws SQLException SQL Exception.
* @see AbstractDbData#loadImpl(Connection)
*/
@Override
protected void loadImpl(Connection connection) throws SQLException {
if (name == null) {
throw new NullPointerException("name can not be null");
}
try (Statement statement = connection.createStatement()) {
try (ResultSet resultSet = statement.executeQuery(getRequest())) {
collectColumnsNameFromResultSet(resultSet);
collectRowsFromResultSet(resultSet);
}
}
collectPrimaryKeyName(connection);
if (columnsToOrder == null) {
sortRows();
}
}
}