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);
   }
}
 

/**
 * 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);
  }
}
 

@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\"}";
}
 

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());
  }
}
 

@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 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;
  }
}
 

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 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 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;
            }
        }
    }
}
 

@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());

}
 

@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() );
}
 

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);
    }
}
 

@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")));
    }
}
 

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());
  }
 

public void fill(Connection c) throws SQLException {
    c.setAutoCommit(false);

    dropTable(c, "TENKTUP1");
    dropTable(c, "TENKTUP2");
    dropTable(c, "ONEKTUP");
    dropTable(c, "BPRIME");

    wisconsin.createTables(c, false);

    c.commit();
}
 

@Override
public boolean supportsSchemasInDataManipulation() throws SQLException {
    return true;
}
 

@Override
public Tuple next() throws SQLException {
    Tuple current = next;
    advance();
    return current;
}
 

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

/** {@inheritDoc} */
@Override public boolean execute(String sql) throws SQLException {
    execute0(sql, null);

    return results.get(0).isQuery();
}
 

@Override
public void setValue(PreparedStatement ps)
  throws SQLException
{
  setString(_val, ps);
}
 

public RowSetTable(CachedRowSet rowSet) throws SQLException {
	this.rowSet = rowSet;
	ResultSetMetaData md = rowSet.getMetaData();

	Column[] columns = new Column[md.getColumnCount()];

	ResultMetaData.Builder mdBuilder = new ResultMetaData.Builder();
	for (int i = 0; i < columns.length; i++) {
		String className = md.getColumnClassName(i + 1);
		Class<?> clazz;
		try {
			clazz = Class.forName(className);
		} catch (ClassNotFoundException e) {
			clazz = String.class;
		}
		String columnName = md.getColumnName(i + 1);
		columns[i] = new Column(columnName, clazz);
		if (md.getColumnType(i + 1) == Types.JAVA_OBJECT) {
			// Most likely a HeapReference
			final int columnPosition = i;
			String tableName = md.getTableName(i + 1);
			final String label;
			if (tableName == null || tableName.isEmpty()) {
				label = columnName;
			} else {
				label = tableName + "." + columnName;
			}
			mdBuilder.addContext(new ContextProvider(label) {
				@Override
				public IContextObject getContext(Object row) {
					return RowSetTable.getContext(row, columnPosition);
				}
			});
			if (idColumnPosition == -1) {
				// Use first object column as context provider (e.g. in case "this" column is missing)
				idColumnPosition = i;
			}
		}
		if (idColumnPosition == -1 && "this".equals(columns[i].getLabel()))
			idColumnPosition = i;
	}
	this.metaData = mdBuilder.build();
	this.columns = columns;
}
 

public void demo_insertlist2() throws SQLException {
    DataList list = new DataList();

    db.table("user").insertList(list.getRows());
}
 

@Override
public Clob getClob(int columnIndex) throws SQLException {
    throw new UnsupportedOperationException();
}
 

public int getParameterCount() throws SQLException {
    return this.parameterCount;
}
 

@Override
public void updateNCharacterStream(int columnIndex, Reader x, long length) throws SQLException {
    throw new UnsupportedOperationException("Not supported yet.");
}
 

public void updateNClob(int columnIndex, Reader reader) throws SQLException {
  noUpdateException(columnIndex);
}
 

public int getMaxConnections() throws SQLException {
	return delegate.getMaxConnections();
}