java.sql.SQLException Java Examples

/**
 * 事务提交
 */
public static void commit() {
	Connection conn = tl.get();
	if (conn != null) {
		try {
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("Connection 事务提交异常");
		}
	}
}
 

@Override
public void addDomainCreatePostCreateSysPermissions(SQLConnection connection,
                                                    Resource accessorResource,
                                                    Resource grantorResource,
                                                    Set<DomainCreatePermission> domainCreatePermissions) {
   SQLStatement statement = null;

   try {
      statement = connection.prepareStatement(sqlStrings.SQL_createInGrantDomainCreatePermissionPostCreateSys_WITH_AccessorID_GrantorID_IsWithGrant_PostCreateIsWithGrant_PostCreateSysPermissionID);
      for (DomainCreatePermission domainCreatePermission : domainCreatePermissions) {
         if (!domainCreatePermission.isSystemPermission()
               && domainCreatePermission.getPostCreateDomainPermission().isSystemPermission()) {
            statement.setResourceId(1, accessorResource);
            statement.setResourceId(2, grantorResource);
            statement.setBoolean(3, domainCreatePermission.isWithGrantOption());
            statement.setBoolean(4, domainCreatePermission.getPostCreateDomainPermission().isWithGrantOption());
            statement.setDomainSystemPermissionId(5, domainCreatePermission.getPostCreateDomainPermission().getSystemPermissionId());

            assertOneRowInserted(statement.executeUpdate());
         }
      }
   }
   catch (SQLException e) {
      throw new RuntimeException(e);
   }
   finally {
      closeStatement(statement);
   }
}
 

@POST
@Path("/deleteHierarchy")
@Produces(MediaType.APPLICATION_JSON + "; charset=UTF-8")
@UserConstraint(functionalities = { SpagoBIConstants.HIERARCHIES_MANAGEMENT })
public String deleteHierarchy(@Context HttpServletRequest req) throws SQLException {
	// delete hierarchy
	Connection connection = null;
	try {

		JSONObject requestVal = RestUtilities.readBodyAsJSONObject(req);

		String dimension = requestVal.getString("dimension");
		String hierarchyName = requestVal.getString("name");

		// 1 - get datasource label name
		Hierarchies hierarchies = HierarchiesSingleton.getInstance();
		String dataSourceName = hierarchies.getDataSourceOfDimension(dimension);
		IDataSourceDAO dataSourceDAO = DAOFactory.getDataSourceDAO();
		IDataSource dataSource = dataSourceDAO.loadDataSourceByLabel(dataSourceName);

		// 2 - Execute DELETE
		connection = dataSource.getConnection();
		HierarchyUtils.deleteHierarchy(dimension, hierarchyName, dataSource, connection);

	} catch (Throwable t) {
		connection.rollback();
		logger.error("An unexpected error occured while deleting custom hierarchy");
		throw new SpagoBIServiceException("An unexpected error occured while deleting custom hierarchy", t);
	} finally {
		if (connection != null && !connection.isClosed())
			connection.close();
	}

	return "{\"response\":\"ok\"}";
}
 

/**
 * Tests stmt.executeUpdate(String, String[]) After doing an insert into a
 * table that doesn't have a generated column, the test should fail.
 * 
 * @throws SQLException
 */
public void testExecuteUpdateNoAutoGenColumnName() throws SQLException {

    Statement stmt = createStatement();

    String[] columnNames = new String[2];
    columnNames[0] = "I";
    columnNames[1] = "S";
    try {
        stmt.executeUpdate("insert into tab1 values(2, 3, 4.1)",
                columnNames);
        fail("FAIL -- executeUpdate should have failed...");
    } catch (SQLException ex) {
        assertFailedExecuteUpdateForColumnName(ex);
    }
}
 

/**
 * {@inheritDoc}
 */
@Override
public byte getByte(int columnIndex) throws SQLException {
  if (columnIndex > 0 && columnIndex <= this.numColumns) {
    try {
      this.wasNull = false;

      if (this.changedColumns == null) {
        if (this.currentRowByteArrays != null) {
          return this.formatter.getAsByte(columnIndex,
              this.currentRowByteArrays, this);
        }
        else {
          return this.formatter.getAsByte(columnIndex, this.currentRowBytes,
              this);
        }
      }
      else {
        if (this.currentRowByteArrays != null) {
          return this.formatter.getAsByte(
              this.changedColumns[columnIndex - 1],
              this.currentRowByteArrays, this);
        }
        else {
          return this.formatter.getAsByte(
              this.changedColumns[columnIndex - 1], this.currentRowBytes,
              this);
        }
      }
    } catch (StandardException se) {
      throw Util.generateCsSQLException(se);
    }
  }
  else {
    throw invalidColumnException(columnIndex);
  }
}
 

@Override
public void clearParameters() throws SQLException {
    synchronized (checkClosed().getConnectionMutex()) {
        super.clearParameters();

        try {
            if (this.outputParameterResults != null) {
                this.outputParameterResults.close();
            }
        } finally {
            this.outputParameterResults = null;
        }
    }
}
 

/**
 * Only meant to be called once
 *
 * @throws Exception exception
 */
public void startServer() throws Exception {
    if (!externalDatabaseHost) {
        try {
            this.port = Utils.getSystemPort(Constants.SYS_DB_PORT);
            server = Server.createTcpServer("-tcpPort", String.valueOf(port), "-tcpAllowOthers").start();
        } catch (SQLException e) {
            if (e.toString().contains("java.net.UnknownHostException")) {
                logger.error("Startup failure. Potential bug in OSX & Java7. Workaround: add name of local machine to '/etc/hosts.'");
                logger.error("Example: 127.0.0.1 MacBook");
                throw e;
            }
        }
    }
}
 

@Override
protected void callBackBegin() {
    ArrayList<SQLException> exceptions = new ArrayList<>();
    for (DefaultConnection i : this.updateConnectionMap.values()) {
        try {
            i.getRawConnection().setAutoCommit(false);
        } catch (SQLException e) {
            exceptions.add(e);
        }
    }
    if (!exceptions.isEmpty()){
        throw new MycatException("本地事务开启失败\n"+exceptions.stream().map(i->i.getMessage()).collect(Collectors.joining("\n")));
    }
}
 

protected void deleteFromDerbyTable(Connection dConn, int cid, int cid1, 
    int sid, int oid, int whichDelete, int updateCount) throws SQLException{
  PreparedStatement stmt = getStmt(dConn, delete[whichDelete]);
  int count = deleteFromTableTx(stmt, cid, cid1, sid, oid, whichDelete);
  
  if (count != updateCount) {
    Log.getLogWriter().info("derby delete has different row count from that of gfxd " +
      "gfxd deleted " +  updateCount + " rows, but derby deleted " + count + " rows in "
      + getTableName());
  }
}
 

private void addActionBatch(Action action) throws SQLException
  {
  	actionPreparedStatement.setLong(1, action.getUserId());
actionPreparedStatement.setLong(2, action.getItemId());
if (action.getType() != null)
	actionPreparedStatement.setInt(3, action.getType());
else
	actionPreparedStatement.setNull(3, java.sql.Types.INTEGER);

if (action.getTimes() != null)
	actionPreparedStatement.setInt(4, action.getTimes());
else
	actionPreparedStatement.setNull(4, java.sql.Types.INTEGER);

if (action.getDate() != null)
	actionPreparedStatement.setTimestamp(5, new java.sql.Timestamp(action.getDate().getTime()));
else
	actionPreparedStatement.setNull(5, java.sql.Types.TIMESTAMP);

if (action.getValue() != null)
	actionPreparedStatement.setDouble(6, action.getValue());
else
	actionPreparedStatement.setNull(6, java.sql.Types.DOUBLE);

actionPreparedStatement.setString(7, action.getClientUserId());
actionPreparedStatement.setString(8, action.getClientItemId());
  }
 

@Test
public void testAutoCommitCanBeConfigured() throws SQLException {

	final boolean desiredAutoCommit = false;
	jdbcInputFormat = JdbcInputFormat.buildJdbcInputFormat()
		.setDrivername(DERBY_EBOOKSHOP_DB.getDriverClass())
		.setDBUrl(DERBY_EBOOKSHOP_DB.getUrl())
		.setQuery(SELECT_ALL_BOOKS)
		.setRowTypeInfo(ROW_TYPE_INFO)
		.setAutoCommit(desiredAutoCommit)
		.finish();

	jdbcInputFormat.openInputFormat();
	Assert.assertEquals(desiredAutoCommit, jdbcInputFormat.getDbConn().getAutoCommit());

}
 

public static @NotNull Dialect detect(@NotNull Connection connection) {
    try {
        String productName = connection.getMetaData().getDatabaseProductName();

        switch (productName) {
            case "PostgreSQL":
                log.debug("Automatically detected dialect PostgreSQL.");
                return new PostgreSQLDialect();

            case "HSQL Database Engine":
                log.debug("Automatically detected dialect HSQLDB.");
                return new HsqldbDialect();

            case "H2":
                log.debug("Automatically detected dialect H2.");
                return new H2Dialect();

            case "MySQL":
                log.debug("Automatically detected dialect MySQL.");
                return new MySQLDialect();

            case "Oracle":
                log.debug("Automatically detected dialect Oracle.");
                return new OracleDialect();

            case "Microsoft SQL Server":
                log.debug("Automatically detected dialect SQLServer.");
                return new SQLServerDialect();

            default:
                log.info("Could not detect dialect for product name '{}', falling back to default.", productName);
                return new DefaultDialect();
        }
    } catch (SQLException e) {
        throw new DatabaseSQLException("Failed to auto-detect database dialect: " + e, e);
    }
}
 

@Test
public void testMetdataPreviewSqlNumericWithoutStrictBigNumberInterpretationUsingOracle() throws SQLException, KettleDatabaseException {
  doReturn( Types.NUMERIC ).when( resultSet ).getInt( "DATA_TYPE" );
  doReturn( 38 ).when( resultSet ).getInt( "COLUMN_SIZE" );
  doReturn( mock( Object.class ) ).when( resultSet ).getObject( "DECIMAL_DIGITS" );
  doReturn( 0 ).when( resultSet ).getInt( "DECIMAL_DIGITS" );
  doReturn( mock( OracleDatabaseMeta.class ) ).when( dbMeta ).getDatabaseInterface();
  when( ( (OracleDatabaseMeta) dbMeta.getDatabaseInterface() ).strictBigNumberInterpretation() ).thenReturn( false );
  ValueMetaInterface valueMeta = valueMetaBase.getMetadataPreview( dbMeta, resultSet );
  assertTrue( valueMeta.isInteger() );
}
 

private void initialize() throws SQLException {
    
    this.sqlQuery = this.generateSql(this.view, this.fields, this.conditions, this.grouping, this.order, this.user, this.connection);
    this.getMetric().setQuery(sqlQuery);
    
    this.crs = RowSetProvider.newFactory().createCachedRowSet();
    this.crs.setCommand(this.sqlQuery);
    
    String countQuery = "SELECT count(*) FROM (" + this.sqlQuery + ") AS CNT";
    log.trace("Count query: " + countQuery);
    try (Statement s = connection.createStatement(); ResultSet rs = s.executeQuery(countQuery)) {
        if (rs.next())
            this.totalRows = rs.getInt(1);
        else
            throw new SQLException("Count query did not return a result");
    }
    
    if (this.pagesize < this.totalRows) {
        this.crs.setPageSize(this.pagesize);
    } else {
        this.crs.setPageSize(this.totalRows);
    }
    
    if (log.isTraceEnabled()) {
        log.trace("Setting pageSize to " + crs.getPageSize());
        log.trace("Setting maxRows to " + crs.getMaxRows());
        log.trace("Setting totalRows to " + this.totalRows);
    }
    
    this.crs.execute(this.connection);
    this.crs.beforeFirst();
    this.currentRow = position.BEFORE_FIRST;
}
 

@Override
public String toString(OptimizedElementArray row, int columnIndex,
    LobService lobService) throws SQLException {
  Object o = row.getObject(columnIndex - 1);
  if (o != null) {
    return o.toString();
  }
  else {
    return null;
  }
}
 

@Override
public void readWithConstraint(BlockSpiller blockSpiller, ReadRecordsRequest readRecordsRequest, QueryStatusChecker queryStatusChecker)
{
    LOGGER.info("{}: Catalog: {}, table {}, splits {}", readRecordsRequest.getQueryId(), readRecordsRequest.getCatalogName(), readRecordsRequest.getTableName(),
            readRecordsRequest.getSplit().getProperties());
    try (Connection connection = this.jdbcConnectionFactory.getConnection(getCredentialProvider())) {
        connection.setAutoCommit(false); // For consistency. This is needed to be false to enable streaming for some database types.
        try (PreparedStatement preparedStatement = buildSplitSql(connection, readRecordsRequest.getCatalogName(), readRecordsRequest.getTableName(),
                readRecordsRequest.getSchema(), readRecordsRequest.getConstraints(), readRecordsRequest.getSplit());
                ResultSet resultSet = preparedStatement.executeQuery()) {
            Map<String, String> partitionValues = readRecordsRequest.getSplit().getProperties();

            GeneratedRowWriter.RowWriterBuilder rowWriterBuilder = GeneratedRowWriter.newBuilder(readRecordsRequest.getConstraints());
            for (Field next : readRecordsRequest.getSchema().getFields()) {
                Extractor extractor = makeExtractor(next, resultSet, partitionValues);
                rowWriterBuilder.withExtractor(next.getName(), extractor);
            }

            GeneratedRowWriter rowWriter = rowWriterBuilder.build();
            int rowsReturnedFromDatabase = 0;
            while (resultSet.next()) {
                if (!queryStatusChecker.isQueryRunning()) {
                    return;
                }
                blockSpiller.writeRows((Block block, int rowNum) -> rowWriter.writeRow(block, rowNum, resultSet) ? 1 : 0);
                rowsReturnedFromDatabase++;
            }
            LOGGER.info("{} rows returned by database.", rowsReturnedFromDatabase);

            connection.commit();
        }
    }
    catch (SQLException sqlException) {
        throw new RuntimeException(sqlException.getErrorCode() + ": " + sqlException.getMessage(), sqlException);
    }
}
 

@Override
public List<User> findAll() {
    final List<User> users = new ArrayList<>();
    try (final PreparedStatement statement = this.source.getConnection()
            .prepareStatement("select * from users");
         final ResultSet rs = statement.executeQuery()) {
        while (rs.next()) {
            users.add(new User(rs.getInt("id"), rs.getString("login")));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return users;
}
 

@Override
public BigInteger getBigInteger(ResultSet rs, String columnName) throws SQLException {
    return new BigInteger(rs.getString(columnName));
}
 

@Override
public void setString(String parameterName, String x) throws SQLException {
  statement.setString(parameterName, x);
}
 

public DBXlinkImporterSurfaceGeometry(CacheTable tempTable, DBXlinkImporterManager xlinkImporterManager) throws SQLException {
	this.xlinkImporterManager = xlinkImporterManager;

	psXlink = tempTable.getConnection().prepareStatement(new StringBuilder("insert into " + tempTable.getTableName()) 
		.append(" (ID, PARENT_ID, ROOT_ID, REVERSE, GMLID, CITYOBJECT_ID, TABLE_NAME, FROM_COLUMN) values ")
		.append("(?, ?, ?, ?, ?, ?, ?, ?)").toString());
}
 

public void setAsciiStream(String parameterName, java.io.InputStream x, int length) throws SQLException {
    if (agent_.loggingEnabled()) {
        agent_.logWriter_.traceEntry(this, "setAsciiStream", parameterName, x, length);
    }
    throw jdbcMethodNotImplemented();
}
 

@Override
public boolean storesMixedCaseIdentifiers() throws SQLException
{
  logger.debug(
      "public boolean storesMixedCaseIdentifiers()");
  raiseSQLExceptionIfConnectionIsClosed();
  return false;
}
 

public void eval(Object... keys) {
	Row keyRow = Row.of(keys);
	if (cache != null) {
		List<Row> cachedRows = cache.getIfPresent(keyRow);
		if (cachedRows != null) {
			for (Row cachedRow : cachedRows) {
				collect(cachedRow);
			}
			return;
		}
	}

	for (int retry = 1; retry <= maxRetryTimes; retry++) {
		try {
			statement.clearParameters();
			for (int i = 0; i < keys.length; i++) {
				JdbcUtils.setField(statement, keySqlTypes[i], keys[i], i);
			}
			try (ResultSet resultSet = statement.executeQuery()) {
				if (cache == null) {
					while (resultSet.next()) {
						collect(convertToRowFromResultSet(resultSet));
					}
				} else {
					ArrayList<Row> rows = new ArrayList<>();
					while (resultSet.next()) {
						Row row = convertToRowFromResultSet(resultSet);
						rows.add(row);
						collect(row);
					}
					rows.trimToSize();
					cache.put(keyRow, rows);
				}
			}
			break;
		} catch (SQLException e) {
			LOG.error(String.format("JDBC executeBatch error, retry times = %d", retry), e);
			if (retry >= maxRetryTimes) {
				throw new RuntimeException("Execution of JDBC statement failed.", e);
			}

			try {
				if (!dbConn.isValid(CONNECTION_CHECK_TIMEOUT_SECONDS)) {
					statement.close();
					dbConn.close();
					establishConnectionAndStatement();
				}
			} catch (SQLException | ClassNotFoundException excpetion) {
				LOG.error("JDBC connection is not valid, and reestablish connection failed", excpetion);
				throw new RuntimeException("Reestablish JDBC connection failed", excpetion);
			}

			try {
				Thread.sleep(1000 * retry);
			} catch (InterruptedException e1) {
				throw new RuntimeException(e1);
			}
		}
	}
}
 

@Override
public Array getArray(int i) throws SQLException {
    return ((CallableStatement) targetStatement).getArray(i);
}
 

@Override
public boolean isClosed() throws SQLException {
    return this.isClosed;
}
 

public static void addSlaveHost(String group, String hostPortPair) throws SQLException {
    Collection<ReplicationConnectionGroup> s = getGroupsMatching(group);
    for (ReplicationConnectionGroup cg : s) {
        cg.addSlaveHost(hostPortPair);
    }
}
 

public static void doTriggers (Connection conn)
	throws SQLException
{

	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery("SELECT TRIGGERNAME, SCHEMAID, " +
		"EVENT, FIRINGTIME, TYPE, TABLEID, REFERENCEDCOLUMNS, " + 
		"TRIGGERDEFINITION, REFERENCINGOLD, REFERENCINGNEW, OLDREFERENCINGNAME, " +
		"NEWREFERENCINGNAME FROM SYS.SYSTRIGGERS WHERE STATE != 'D'");

	boolean firstTime = true;
	while (rs.next()) {

		String trigName = dblook.addQuotes(
			dblook.expandDoubleQuotes(rs.getString(1)));
		String trigSchema = dblook.lookupSchemaId(rs.getString(2));

		if (dblook.isIgnorableSchema(trigSchema))
			continue;

		trigName = trigSchema + "." + trigName;
		String tableName = dblook.lookupTableId(rs.getString(6));

		// We'll write the DDL for this trigger if either 1) it is on
		// a table in the user-specified list, OR 2) the trigger text
		// contains a reference to a table in the user-specified list.

		if (!dblook.stringContainsTargetTable(rs.getString(8)) &&
			(dblook.isExcludedTable(tableName)))
			continue;

		if (firstTime) {
			Logs.reportString("----------------------------------------------");
			Logs.reportMessage("DBLOOK_TriggersHeader");
			Logs.reportString("----------------------------------------------\n");
		}

		String createTrigString = createTrigger(trigName,
			tableName, rs);

		Logs.writeToNewDDL(createTrigString);
		Logs.writeStmtEndToNewDDL();
		Logs.writeNewlineToNewDDL();
		firstTime = false;

	}

	rs.close();
	stmt.close();

}
 

@Override
public void setDouble(OptimizedElementArray row, int columnIndex, double x)
    throws SQLException {
  setBigDecimal(row, columnIndex, new BigDecimal(x));
}
 

@Override
public void updateNCharacterStream(int columnIndex, Reader x)
        throws SQLException
{
    throw new SQLFeatureNotSupportedException("updateNCharacterStream");
}
 

@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
    throw new UnsupportedOperationException("Not supported yet.");
}