org.eobjects.metamodel.jdbc.JdbcUtils Maven / Gradle / Ivy

Go to download
/**
 * eobjects.org MetaModel
 * Copyright (C) 2010 eobjects.org
 *
 * This copyrighted material is made available to anyone wishing to use, modify,
 * copy, or redistribute it subject to the terms and conditions of the GNU
 * Lesser General Public License, as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
 * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
 * for more details.
 *
 * You should have received a copy of the GNU Lesser General Public License
 * along with this distribution; if not, write to:
 * Free Software Foundation, Inc.
 * 51 Franklin Street, Fifth Floor
 * Boston, MA  02110-1301  USA
 */
package org.eobjects.metamodel.jdbc;

import java.io.InputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.eobjects.metamodel.MetaModelException;
import org.eobjects.metamodel.jdbc.dialects.IQueryRewriter;
import org.eobjects.metamodel.query.FilterItem;
import org.eobjects.metamodel.query.QueryParameter;
import org.eobjects.metamodel.schema.Column;
import org.eobjects.metamodel.schema.ColumnType;
import org.eobjects.metamodel.util.FormatHelper;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public final class JdbcUtils {

	private static final Logger logger = LoggerFactory.getLogger(JdbcUtils.class);

	public static MetaModelException wrapException(SQLException e, String actionDescription) throws MetaModelException {
		String message = e.getMessage();
		if (message == null || message.isEmpty()) {
			message = "Could not " + actionDescription;
		} else {
			message = "Could not " + actionDescription + ": " + message;
		}

		if (logger.isWarnEnabled()) {
			logger.warn(message, e);
			logger.warn("Error code={}, SQL state={}", e.getErrorCode(), e.getSQLState());

			final SQLException nextException = e.getNextException();
			if (nextException != null) {
				logger.warn("Next SQL exception: " + nextException.getMessage(), nextException);
			}
		}

		return new MetaModelException(message, e);
	}

	/**
	 * Method which handles the action of setting a parameterized value on a
	 * statement. Traditionally this is done using the
	 * {@link PreparedStatement#setObject(int, Object)} method but for some
	 * types we use more specific setter methods.
	 * 
	 * @param st
	 * @param valueIndex
	 * @param column
	 * @param value
	 * @throws SQLException
	 */
	public static void setStatementValue(PreparedStatement st, int valueIndex, Column column, Object value)
			throws SQLException {
		final ColumnType type = (column == null ? null : column.getType());
		if (type == ColumnType.VARCHAR && value instanceof Date) {
			// some drivers (SQLite and JTDS for MS SQL server) treat dates as
			// VARCHARS. In that case we need to convert the dates to the
			// correct format
			String nativeType = column.getNativeType();
			Date date = (Date) value;
			if ("DATE".equalsIgnoreCase(nativeType)) {
				value = FormatHelper.formatSqlTime(ColumnType.DATE, date, false);
			} else if ("TIME".equalsIgnoreCase(nativeType)) {
				value = FormatHelper.formatSqlTime(ColumnType.TIME, date, false);
			} else if ("TIMESTAMP".equalsIgnoreCase(nativeType) || "DATETIME".equalsIgnoreCase(nativeType)) {
				value = FormatHelper.formatSqlTime(ColumnType.TIMESTAMP, date, false);
			}
		}

		if (type != null && type.isTimeBased() && value instanceof String) {
			value = FormatHelper.parseSqlTime(type, (String) value);
		}

		try {
			if (type == ColumnType.DATE && value instanceof Date) {
				Calendar cal = Calendar.getInstance();
				cal.setTime((Date) value);
				st.setDate(valueIndex, new java.sql.Date(cal.getTimeInMillis()), cal);
			} else if (type == ColumnType.TIME && value instanceof Date) {
				Calendar cal = Calendar.getInstance();
				cal.setTime((Date) value);
				st.setTime(valueIndex, new java.sql.Time(cal.getTimeInMillis()), cal);
			} else if (type == ColumnType.TIMESTAMP && value instanceof Date) {
				Calendar cal = Calendar.getInstance();
				cal.setTime((Date) value);
				st.setTimestamp(valueIndex, new java.sql.Timestamp(cal.getTimeInMillis()), cal);
			} else if (type == ColumnType.CLOB || type == ColumnType.NCLOB) {
				if (value instanceof InputStream) {
					InputStream inputStream = (InputStream) value;
					st.setAsciiStream(valueIndex, inputStream);
				} else if (value instanceof Reader) {
					Reader reader = (Reader) value;
					st.setCharacterStream(valueIndex, reader);
				} else if (value instanceof NClob) {
					NClob nclob = (NClob) value;
					st.setNClob(valueIndex, nclob);
				} else if (value instanceof Clob) {
					Clob clob = (Clob) value;
					st.setClob(valueIndex, clob);
				} else {
					st.setObject(valueIndex, value);
				}
			} else if (type == ColumnType.BLOB || type == ColumnType.BINARY) {
				if (value instanceof byte[]) {
					byte[] bytes = (byte[]) value;
					st.setBytes(valueIndex, bytes);
				} else if (value instanceof InputStream) {
					InputStream inputStream = (InputStream) value;
					st.setBinaryStream(valueIndex, inputStream);
				} else if (value instanceof Blob) {
					Blob blob = (Blob) value;
					st.setBlob(valueIndex, blob);
				} else {
					st.setObject(valueIndex, value);
				}
			} else {
				st.setObject(valueIndex, value);
			}
		} catch (SQLException e) {
			logger.error("Failed to set parameter {} to value: {}", valueIndex, value);
			throw e;
		}
	}

	public static String getValueAsSql(Column column, Object value, IQueryRewriter queryRewriter) {
		if (value == null) {
			return "NULL";
		}
		final ColumnType columnType = column.getType();
		if (columnType.isLiteral() && value instanceof String) {
			value = queryRewriter.escapeQuotes((String) value);
		}
		String formatSqlValue = FormatHelper.formatSqlValue(columnType, value);
		return formatSqlValue;
	}

	public static String createWhereClause(List whereItems, IQueryRewriter queryRewriter,
			boolean inlineValues) {
		if (whereItems.isEmpty()) {
			return "";
		}
		StringBuilder sb = new StringBuilder();
		sb.append(" WHERE ");
		boolean firstValue = true;
		for (FilterItem whereItem : whereItems) {
			if (firstValue) {
				firstValue = false;
			} else {
				sb.append(" AND ");
			}
			if (!inlineValues) {
				if (!whereItem.isCompoundFilter()) {
					// replace operator with parameter
					whereItem = new FilterItem(whereItem.getSelectItem(), whereItem.getOperator(), new QueryParameter());
				}
			}
			final String whereItemLabel = queryRewriter.rewriteFilterItem(whereItem);
			sb.append(whereItemLabel);
		}
		return sb.toString();
	}
}