Java Code Examples for java.sql.PreparedStatement#getUpdateCount()

The following examples show how to use java.sql.PreparedStatement#getUpdateCount() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may want to check out the right sidebar which shows the related API usage.
Example 1
Source Project: L2ACP-api   File: Helpers.java    License: GNU General Public License v2.0 6 votes vote down vote up
public static void unjailOfflinePlayer(String name)
{
	try (Connection con = L2DatabaseFactory.getInstance().getConnection())
	{
		PreparedStatement statement = con.prepareStatement("UPDATE characters SET x=?, y=?, z=?, punish_level=?, punish_timer=? WHERE char_name=?");
		statement.setInt(1, 17836);
		statement.setInt(2, 170178);
		statement.setInt(3, -3507);
		statement.setInt(4, 0);
		statement.setLong(5, 0);
		statement.setString(6, name);
		statement.execute();
		int count = statement.getUpdateCount();
		statement.close();
	}
	catch (SQLException se)
	{
	}
}
 
Example 2
private boolean removeDBInfoById(Connection conn, int dbid)
 {
String sql = "delete from "+DBINFO_TABLENAME+" where dbid=?";

PreparedStatement pstmt = null;
try
{
  pstmt = conn.prepareStatement(sql);
  pstmt.setInt(1, dbid);
  pstmt.execute();
  logger.info("Removed the database host : ("+dbid+")");
  return pstmt.getUpdateCount()>0;
}catch(Exception ex)
{
  logger.log(Level.SEVERE,"Exception", ex);
}finally
{
  DBUtils.close(pstmt);
}
return false;
 }
 
Example 3
@Test public void testInsertDrop() throws Exception {
  final String t = AvaticaUtils.unique("TEST_TABLE2");
  final String create =
      String.format(Locale.ROOT, "create table if not exists %s ("
          + "id int not null, "
          + "msg varchar(3) not null)", t);
  final String insert = String.format(Locale.ROOT,
      "insert into %s values(1, 'foo')", t);
  Connection connection = ljs();
  Statement statement = connection.createStatement();
  statement.execute(create);

  Statement stmt = connection.createStatement();
  int count = stmt.executeUpdate(insert);
  assertThat(count, is(1));
  ResultSet resultSet = stmt.getResultSet();
  assertThat(resultSet, nullValue());

  PreparedStatement pstmt = connection.prepareStatement(insert);
  boolean status = pstmt.execute();
  assertThat(status, is(false));
  int updateCount = pstmt.getUpdateCount();
  assertThat(updateCount, is(1));
}
 
Example 4
Source Project: calcite   File: ResultSetEnumerable.java    License: Apache License 2.0 6 votes vote down vote up
private Enumerator<T> enumeratorBasedOnPreparedStatement() {
  Connection connection = null;
  PreparedStatement preparedStatement = null;
  try {
    connection = dataSource.getConnection();
    preparedStatement = connection.prepareStatement(sql);
    setTimeoutIfPossible(preparedStatement);
    preparedStatementEnricher.enrich(preparedStatement);
    if (preparedStatement.execute()) {
      final ResultSet resultSet = preparedStatement.getResultSet();
      preparedStatement = null;
      connection = null;
      return new ResultSetEnumerator<>(resultSet, rowBuilderFactory);
    } else {
      Integer updateCount = preparedStatement.getUpdateCount();
      return Linq4j.singletonEnumerator((T) updateCount);
    }
  } catch (SQLException e) {
    throw Static.RESOURCE.exceptionWhilePerformingQueryOnJdbcSubSchema(sql)
        .ex(e);
  } finally {
    closeIfPossible(connection, preparedStatement);
  }
}
 
Example 5
private boolean removeDBInfo(Connection conn, String dbGroupName, String hostName)
 {
String sql = "delete from "+DBINFO_TABLENAME+" where dbgroupname=? and hostname=?";

PreparedStatement pstmt = null;
try
{
  pstmt = conn.prepareStatement(sql);
  pstmt.setString(1, dbGroupName.toLowerCase());
  pstmt.setString(2, hostName.toLowerCase());
  pstmt.execute();
  logger.info("Removed the database host : ("+dbGroupName+", "+hostName+")");
  return pstmt.getUpdateCount()>0;
}catch(Exception ex)
{
  logger.log(Level.SEVERE,"Exception", ex);
}finally
{
  DBUtils.close(pstmt);
}
return false;
 }
 
Example 6
@Override
public ResultSet execute(PreparedStatement statement) {
	// sql logged by StatementPreparerImpl
	try {
		final ResultSet rs;
		try {
			jdbcExecuteStatementStart();
			if ( !statement.execute() ) {
				while ( !statement.getMoreResults() && statement.getUpdateCount() != -1 ) {
					// do nothing until we hit the resultset
				}
			}
			rs = statement.getResultSet();
		}
		finally {
			jdbcExecuteStatementEnd();
		}
		postExtract( rs, statement );
		return rs;
	}
	catch (SQLException e) {
		throw sqlExceptionHelper.convert( e, "could not execute statement" );
	}
}
 
Example 7
/**
 * Execute given query via a PreparedStatement.
 * A list of args can be passed to the query.
 *
 * Example: runQuery("SELECT * FROM example WHERE test = ?", "test");
 *
 * @param query Query that should be executed
 * @param args Long, String, or Object arguments
 */
protected void runQuery(String query, Connection conn, Object... args) {
  PreparedStatement stmt = null;
  try {
    stmt = conn.prepareStatement(query);

    for (int i = 0; i < args.length; ++i) {
      if (args[i] instanceof String) {
        stmt.setString(i + 1, (String)args[i]);
      } else if (args[i] instanceof Long) {
        stmt.setLong(i + 1, (Long) args[i]);
      } else {
        stmt.setObject(i + 1, args[i]);
      }
    }

    if (stmt.execute()) {
      ResultSet rset = stmt.getResultSet();
      int count = 0;
      while (rset.next()) {
        count++;
      }
      LOG.info("QUERY(" + query + ") produced unused resultset with "+ count + " rows");
    } else {
      int updateCount = stmt.getUpdateCount();
      LOG.info("QUERY(" + query + ") Update count: " + updateCount);
    }
  } catch (SQLException ex) {
    throw new SqoopException(CommonRepositoryError.COMMON_0000, query, ex);
  } finally {
    closeStatements(stmt);
  }
}
 
Example 8
public Serializable executeAndExtract(PreparedStatement insert) throws SQLException {
	if ( !insert.execute() ) {
		while ( !insert.getMoreResults() && insert.getUpdateCount() != -1 ) {
			// do nothing until we hit the rsult set containing the generated id
		}
	}
	ResultSet rs = insert.getResultSet();
	try {
		return IdentifierGeneratorFactory.getGeneratedIdentity( rs, persister.getIdentifierType() );
	}
	finally {
		rs.close();
	}
}
 
Example 9
@Override
public int update(Statement statement) throws SQLException {
  //调用PreparedStatement.execute和PreparedStatement.getUpdateCount
  PreparedStatement ps = (PreparedStatement) statement;
  ps.execute();
  int rows = ps.getUpdateCount();
  Object parameterObject = boundSql.getParameterObject();
  KeyGenerator keyGenerator = mappedStatement.getKeyGenerator();
  keyGenerator.processAfter(executor, mappedStatement, ps, parameterObject);
  return rows;
}
 
Example 10
/**
 * Executes a prepared SQL statement with parameters.
 * @param connection a JDBC Connection object
 * @param sql SQL string to be prepared and executed
 * @param parameterMap The Map returned setup by replaceBindParams
 * @param parameters The Map returned setup by replaceBindParams
 * @param mode Mode for selection queries
 * @param dr Data result list or null
 * @return either an integer count of the number of rows updated, or the
 *         DataResult. Casting to int or DataResult is caller's
 *         responsibility
 * @throws SQLException
 */
private Object execute(Connection connection, String sql,
        Map<String, List<Integer>> parameterMap, Map<String, ?> parameters, Mode mode,
        List<Object> dr)
    throws SQLException {
    if (log.isDebugEnabled()) {
        log.debug("execute() - Executing: " + sql);
        log.debug("execute() - With: " + parameters);
    }

    PreparedStatement ps = null;
    try {
        ps = prepareStatement(connection, sql, mode);
        boolean returnType = NamedPreparedStatement.execute(ps, parameterMap,
                setupParamMap(parameters));
        if (log.isDebugEnabled()) {
            log.debug("execute() - Return type: " + returnType);
        }
        if (returnType) {
            return processResultSet(ps.getResultSet(), (SelectMode) mode, dr);
        }
        return ps.getUpdateCount();
    }
    finally {
        HibernateHelper.cleanupDB(ps);
    }
}
 
Example 11
public HashMap<String, Boolean> getQueuedMessages(String deviceId) throws SQLException {
    HashMap<String, Boolean> queue = new HashMap<>();
    String selectAwaitingDevicesStatementString = "SELECT * FROM " + TABLE_QUEUE + " WHERE " + COLUMN_QUEUE_MESSAGE_DEVICES_WAITING + " LIKE ?";
    PreparedStatement selectAwaitingDevicesStatement = getServerDatabaseConnection().prepareStatement(selectAwaitingDevicesStatementString);
    selectAwaitingDevicesStatement.setString(1, "%" + deviceId + "%");

    boolean isResultSet = selectAwaitingDevicesStatement.execute();

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectAwaitingDevicesStatement.getResultSet();
            while(resultSet.next()) {
                String messageGuid = resultSet.getString(COLUMN_QUEUE_MESSAGE_GUID);
                boolean isUpdate = Boolean.parseBoolean(resultSet.getString(COLUMN_QUEUE_MESSAGE_UPDATE));
                queue.put(messageGuid, isUpdate);
            }
            resultSet.close();
        } else {
            if(selectAwaitingDevicesStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectAwaitingDevicesStatement.getMoreResults();
    }
    selectAwaitingDevicesStatement.close();

    return queue;
}
 
Example 12
public long getChatRowPositionByRowId(long rowId) throws SQLException {
    Long rowIdReturn = null;

    String selectChatStatementString = "SELECT " + COLUMN_CHAT_MESSAGE_CHAT_ID + ", MAX(" + COLUMN_CHAT_MESSAGE_MESSAGE_ID + ") FROM " + CHAT_MESSAGE_JOIN_TABLE
            + " INNER JOIN " + MESSAGE_TABLE + " ON " + CHAT_MESSAGE_JOIN_TABLE + "." + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " = " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_ROWID
            + " WHERE " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_TEXT + " IS NOT NULL "
            + " GROUP BY " + COLUMN_CHAT_MESSAGE_CHAT_ID
            + " ORDER BY " + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " DESC";

    PreparedStatement selectChatStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatStatementString);

    boolean isResultSet = selectChatStatement.execute();
    long i = 0L;

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectChatStatement.getResultSet();
            while(resultSet.next()) {
                long resultInt = resultSet.getLong(COLUMN_CHAT_MESSAGE_CHAT_ID);

                if (rowId == resultInt){
                    rowIdReturn = i;
                    break;
                }
                i++;
            }
            resultSet.close();
        } else {
            if(selectChatStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectChatStatement.getMoreResults();
    }
    selectChatStatement.close();

    return rowIdReturn;
}
 
Example 13
public long getChatRowPositionByGuid(String guid) throws SQLException {
    Long rowIdReturn = null;

    String selectChatStatementString = "SELECT " + CHAT_TABLE + "." + COLUMN_CHAT_GUID + ", " + COLUMN_CHAT_MESSAGE_CHAT_ID + ", MAX(" + COLUMN_CHAT_MESSAGE_MESSAGE_ID + ")"
            + " FROM " + CHAT_MESSAGE_JOIN_TABLE
            + " INNER JOIN " + CHAT_TABLE + " ON " + CHAT_MESSAGE_JOIN_TABLE + "." + COLUMN_CHAT_MESSAGE_CHAT_ID + " = " + CHAT_TABLE + "." + COLUMN_CHAT_ROWID
            + " INNER JOIN " + MESSAGE_TABLE + " ON " + CHAT_MESSAGE_JOIN_TABLE + "." + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " = " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_ROWID
            + " WHERE " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_TEXT + " IS NOT NULL "
            + " GROUP BY " + COLUMN_CHAT_MESSAGE_CHAT_ID
            + " ORDER BY " + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " DESC";

    PreparedStatement selectChatStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatStatementString);

    boolean isResultSet = selectChatStatement.execute();
    long i = 0L;

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectChatStatement.getResultSet();
            while(resultSet.next()) {
                String resultString = resultSet.getString(COLUMN_CHAT_GUID);

                if (resultString.equals(guid)){
                    rowIdReturn = i;
                    break;
                }
                i++;
            }
            resultSet.close();
        } else {
            if(selectChatStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectChatStatement.getMoreResults();
    }
    selectChatStatement.close();

    return rowIdReturn;
}
 
Example 14
public HashMap<Long, Long> getSortedChatsWithRow() throws SQLException {
    HashMap<Long, Long> positionRowIdPair = new HashMap<>();

    String selectChatStatementString = "SELECT " + COLUMN_CHAT_MESSAGE_CHAT_ID + ", MAX(" + COLUMN_CHAT_MESSAGE_MESSAGE_ID + ") FROM " + CHAT_MESSAGE_JOIN_TABLE
            + " INNER JOIN " + MESSAGE_TABLE + " ON " + CHAT_MESSAGE_JOIN_TABLE + "." + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " = " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_ROWID
            + " WHERE " + MESSAGE_TABLE + "." + COLUMN_MESSAGE_TEXT + " IS NOT NULL "
            + " GROUP BY " + COLUMN_CHAT_MESSAGE_CHAT_ID
            + " ORDER BY " + COLUMN_CHAT_MESSAGE_MESSAGE_ID + " DESC";

    PreparedStatement selectChatStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatStatementString);

    boolean isResultSet = selectChatStatement.execute();
    long i = 0L;

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectChatStatement.getResultSet();
            while(resultSet.next()) {
                long resultInt = resultSet.getLong(COLUMN_CHAT_MESSAGE_CHAT_ID);
                positionRowIdPair.put(i, resultInt);
                i++;
            }
            resultSet.close();
        } else {
            if(selectChatStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectChatStatement.getMoreResults();
    }
    selectChatStatement.close();

    return positionRowIdPair;
}
 
Example 15
public ChatBase getChatByGroupID(String groupID) throws SQLException {
    String selectChatStatementString = "SELECT * FROM " + CHAT_TABLE + " WHERE " + COLUMN_CHAT_GROUP_ID + " = ?";
    PreparedStatement selectChatStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatStatementString);
    selectChatStatement.setString(1, groupID);

    ResultSet chatResultSet = selectChatStatement.executeQuery();

    if (!chatResultSet.isBeforeFirst()){
        chatResultSet.close();
        selectChatStatement.close();
        return null;
    }

    List<Handle> handles = new ArrayList<>();
    String selectChatHandleStatementString = "SELECT * FROM " + CHAT_HANDLES_TABLE + " WHERE " + COLUMN_CHAT_HANDLE_CHAT_ID + " = ?";
    PreparedStatement selectChatHandleStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatHandleStatementString);
    selectChatHandleStatement.setLong(1, chatResultSet.getLong(COLUMN_CHAT_ROWID));

    boolean isResultSet = selectChatHandleStatement.execute();

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectChatHandleStatement.getResultSet();
            while(resultSet.next()) {
                long resultInt = resultSet.getLong(COLUMN_CHAT_HANDLE_HANDLE_ID);
                handles.add(getHandleByRow(resultInt));
            }
            resultSet.close();
        } else {
            if(selectChatHandleStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectChatHandleStatement.getMoreResults();
    }

    ChatBase chat = buildChat(chatResultSet, handles);

    chatResultSet.close();
    selectChatStatement.close();
    selectChatHandleStatement.close();

    return chat;
}
 
Example 16
/**
 * @todo 针对sqlserver timestamp不能保存情况,提供特殊的批量执行方式,跳过类型为timestamp的值
 * @param updateSql
 * @param rowDatas
 * @param batchSize
 * @param updateTypes
 * @param autoCommit
 * @param conn
 * @param dbType
 * @return
 * @throws Exception
 */
private static Long batchUpdateByJdbc(final String updateSql, final List<Object[]> rowDatas, final int batchSize,
		final Integer[] updateTypes, final Boolean autoCommit, final Connection conn, final Integer dbType)
		throws Exception {
	if (rowDatas == null) {
		logger.error("batchUpdateByJdbc:{} 传递的数据为空!", updateSql);
		return 0L;
	}
	PreparedStatement pst = null;
	long updateCount = 0;
	try {
		boolean hasSetAutoCommit = false;
		// 是否自动提交
		if (autoCommit != null && !autoCommit == conn.getAutoCommit()) {
			conn.setAutoCommit(autoCommit);
			hasSetAutoCommit = true;
		}
		pst = conn.prepareStatement(updateSql);
		int totalRows = rowDatas.size();
		boolean useBatch = (totalRows > 1) ? true : false;
		Object[] rowData;
		// 批处理计数器
		int meter = 0;
		int pstIndex = 0;
		for (int i = 0; i < totalRows; i++) {
			rowData = rowDatas.get(i);
			if (rowData != null) {
				// 使用对象properties方式传值
				pstIndex = 0;
				for (int j = 0, n = rowData.length; j < n; j++) {
					// 类型为timestamp 则跳过
					if (!updateTypes[j].equals(java.sql.Types.TIMESTAMP)) {
						SqlUtil.setParamValue(conn, dbType, pst, rowData[j], updateTypes[j], pstIndex + 1);
						pstIndex++;
					}
				}
				meter++;
				if (useBatch) {
					pst.addBatch();
					if ((meter % batchSize) == 0 || i + 1 == totalRows) {
						int[] updateRows = pst.executeBatch();
						for (int t : updateRows) {
							updateCount = updateCount + ((t > 0) ? t : 0);
						}
						pst.clearBatch();
					}
				} else {
					pst.execute();
					updateCount = updateCount + ((pst.getUpdateCount() > 0) ? pst.getUpdateCount() : 0);
				}
			}
		}
		if (hasSetAutoCommit) {
			conn.setAutoCommit(!autoCommit);
		}
	} catch (Exception e) {
		logger.error(e.getMessage(), e);
		e.printStackTrace();
		throw e;
	} finally {
		try {
			if (pst != null) {
				pst.close();
				pst = null;
			}
		} catch (SQLException se) {
			logger.error(se.getMessage(), se);
			se.printStackTrace();
		}
	}
	return updateCount;
}
 
Example 17
private void executeAsPreparedStatement() throws cfmRunTimeException {
	Connection dataConnection = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;

	try {
		dataConnection = setupDataConnection();
		stmt = prepareStatement(dataConnection);
		boolean oracleDriver = stmt.getClass().getName().equals("oracle.jdbc.driver.OraclePreparedStatement");

		if (!oracleDriver) {
			// the Oracle JDBC driver has a bug: stmt.getUpdateCount() always
			// returns a positive number, instead of -1 when there are no more
			// results; this bug puts the following code into an infinite loop
			stmt.execute();

			// With MySQL we need to retrieve the generated keys before
			// stmt.getMoreResults() and stmt.getUpdateCount() are called.
			ResultSet rsGK = initGeneratedKeys(dataConnection, stmt);
			recordsUpdated = 0;
			
			do { // there may be multiple result sets and/or update counts
				rs = stmt.getResultSet();
				if (rs != null && rs != rsGK ) { // return the first result set (if any). Also need to check it isn't the generated keys ResultSet which SQL Server returns here too
					resultSet = true;
					populate(rs, maxRows);
					rs.close();
					break;
				}
				recordsUpdated += stmt.getUpdateCount();
			} while (stmt.getMoreResults() || (stmt.getUpdateCount() >= 0));

		} else {
			// this is the workaround for the Oracle bug mentioned above
			if (stmt.execute()) { // Oracle only returns a single result
				rs = stmt.getResultSet();
				if (rs != null) {
					resultSet = true;
					populate(rs, maxRows);
				}
			}
			initGeneratedKeys(dataConnection, stmt);
		}
	} catch (SQLException e) {
		throw new cfmRunTimeException(catchDataFactory.databaseException(dataSourceName, "sql.execution", new String[] { com.naryx.tagfusion.cfm.tag.tagUtils.trimError(e.getMessage()) }, queryString, e));
	} finally {
		queryRun = true;
		closeConnections(dataConnection, stmt, rs);
	}
}
 
Example 18
Source Project: DKO   File: DBQuery.java    License: GNU Lesser General Public License v2.1 4 votes vote down vote up
@Override
public int update() throws SQLException {
	final SqlContext context = new SqlContext(this);
	if (data==null || data.size()==0) return 0;
	final DataSource ds = getDataSource();
	this.tableInfos.get(0).tableName = null;
	final String sep = getDBType()==DB_TYPE.SQLSERVER ? ".dbo." : ".";
	final StringBuffer sb = new StringBuffer();
	sb.append("update ");
	String schema = Util.getSchemaName(ofType);
	if (schema!=null && !"".equals(schema)) {
		schema = Context.getSchemaToUse(ds, schema);
		if (schema!=null && !"".equals(schema)) {
			sb.append(schema).append(sep);
		}
	}
	sb.append(Util.getTableName(ofType));
	sb.append(" set ");
	final String[] fields = new String[data.size()];
	final List<Object> bindings = new ArrayList<Object>();
	int i=0;
	for (final Entry<Field<?>, Object> entry : data.entrySet()) {
		final Field<?> field = entry.getKey();
		final Object other = entry.getValue();
		if (other instanceof Field) {
			fields[i++] = field.getSQL(context)+"="+((Field)other).getSQL(context);
			bindings.add(other);
		} else if (other instanceof SQLFunction) {
			final StringBuffer sb2 = new StringBuffer();
			sb2.append(field.getSQL(context)).append("=");
			((SQLFunction)other).__getSQL(sb2, bindings, context);
			fields[i++] = sb2.toString();
		} else {
			fields[i++] = field.getSQL(context)+"=?";
			bindings.add(other);
		}
	}
	sb.append(Util.join(", ", fields));
	sb.append(" ");
	initTableNameMap(false);
	final Tuple2<String, List<Object>> wcab = getWhereClauseAndBindings(context);
	sb.append(wcab.a);
	bindings.addAll(wcab.b);
	final String sql = sb.toString();

	Util.log(sql, bindings);
	final Tuple2<Connection,Boolean> info = getConnRW(ds);
	final Connection conn = info.a;
	try {
		final PreparedStatement ps = createPS(sql, conn);
		setBindings(ps, bindings);
		_preExecute(context, conn);
		ps.execute();
		final int count = ps.getUpdateCount();
		ps.close();
		_postExecute(context, conn);
		return count;
	} finally {
		if (info.b) {
			if (!conn.getAutoCommit()) conn.commit();
			conn.close();
		}
	}
}
 
Example 19
Source Project: geopackage-java   File: SQLExec.java    License: MIT License 4 votes vote down vote up
/**
 * Execute the query against the database
 * 
 * @param database
 *            open database
 * @param sql
 *            SQL statement
 * @param maxRows
 *            max rows
 * @return results
 * @throws SQLException
 *             upon SQL error
 */
private static SQLExecResult executeQuery(GeoPackage database, String sql,
		int maxRows) throws SQLException {

	SQLExecResult result = new SQLExecResult();

	if (!sql.equals(";")) {

		PreparedStatement statement = null;
		try {

			statement = database.getConnection().getConnection()
					.prepareStatement(sql);
			statement.setMaxRows(maxRows);

			result.setMaxRows(maxRows);

			boolean hasResultSet = statement.execute();

			if (hasResultSet) {

				ResultSet resultSet = statement.getResultSet();

				ResultSetMetaData metadata = resultSet.getMetaData();
				int numColumns = metadata.getColumnCount();

				int[] columnWidths = new int[numColumns];
				int[] columnTypes = new int[numColumns];

				for (int col = 1; col <= numColumns; col++) {
					result.addTable(metadata.getTableName(col));
					String columnName = metadata.getColumnName(col);
					result.addColumn(columnName);
					columnTypes[col - 1] = metadata.getColumnType(col);
					columnWidths[col - 1] = columnName.length();
				}

				while (resultSet.next()) {

					List<String> row = new ArrayList<>();
					result.addRow(row);
					for (int col = 1; col <= numColumns; col++) {

						String stringValue = resultSet.getString(col);

						if (stringValue != null) {

							switch (columnTypes[col - 1]) {
							case Types.BLOB:
								stringValue = BLOB_DISPLAY_VALUE;
								break;
							default:
								stringValue = stringValue.replaceAll(
										"\\s*[\\r\\n]+\\s*", " ");
							}

							int valueLength = stringValue.length();
							if (valueLength > columnWidths[col - 1]) {
								columnWidths[col - 1] = valueLength;
							}

						}

						row.add(stringValue);
					}

				}

				result.addColumnWidths(columnWidths);

			} else {

				int updateCount = statement.getUpdateCount();
				if (updateCount >= 0) {
					result.setUpdateCount(updateCount);
				}

			}

		} finally {
			SQLUtils.closeStatement(statement, sql);
		}
	}

	return result;
}
 
Example 20
public ChatBase getChatByRow(long rowID) throws SQLException {
    String selectChatStatementString = "SELECT * FROM " + CHAT_TABLE + " WHERE " + COLUMN_CHAT_ROWID + " = ?";
    PreparedStatement selectChatStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatStatementString);
    selectChatStatement.setLong(1, rowID);

    ResultSet chatResultSet = selectChatStatement.executeQuery();

    if (!chatResultSet.isBeforeFirst()){
        chatResultSet.close();
        selectChatStatement.close();
        return null;
    }

    List<Handle> handles = new ArrayList<>();
    String selectChatHandleStatementString = "SELECT * FROM " + CHAT_HANDLES_TABLE + " WHERE " + COLUMN_CHAT_HANDLE_CHAT_ID + " = ?";
    PreparedStatement selectChatHandleStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatHandleStatementString);
    selectChatHandleStatement.setLong(1, rowID);

    boolean isResultSet = selectChatHandleStatement.execute();

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectChatHandleStatement.getResultSet();
            while(resultSet.next()) {
                long resultInt = resultSet.getLong(COLUMN_CHAT_HANDLE_HANDLE_ID);
                handles.add(getHandleByRow(resultInt));
            }
            resultSet.close();
        } else {
            if(selectChatHandleStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectChatHandleStatement.getMoreResults();
    }

    ChatBase chat = buildChat(chatResultSet, handles);

    chatResultSet.close();
    selectChatStatement.close();
    selectChatHandleStatement.close();

    return chat;
}