/**
 * ibatis 查询可带null数据
 * 
 * @author zhuoxue
 * @since 5.0.1
 */
@Test
public void selectTypeWithNullTest() throws SQLException {
    prepareOneRowData();
    Map<Object, Object> map = new HashMap<Object, Object>();
    map.put("pk", 122345l);
    // andor的mysql
    List listMysql = mysqlSqlMapClient.queryForList("select_test", map);
    NormalTblRow rowMysql = (NormalTblRow) listMysql.get(0);
    List list = tddlSqlMapClient.queryForList("select_test", map);
    NormalTblRow row = (NormalTblRow) list.get(0);
    rowEquals(row, rowMysql);
    // andor的tdhs
    // List listTdhs =
    // andorTDHSSqlMapClient.queryForList("select_tdhs_null", map);
    // NormalTblNullRow rowTdhs = (NormalTblNullRow) list.get(0);
    // rowNullEquals(rowTdhs, rowMysql);
}
 

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

}
 

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

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

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

/**
 * 事务提交
 */
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 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());
  }
 

/**
    * @return The underlying language connection.
    */
public synchronized LanguageConnectionContext getLanguageConnection()
      throws SQLException
{
	checkActive();
 
	return realConnection.getLanguageConnection();
}
 

private static void siteUserFriendRS(PreparedStatement ps, ResultSet rs) throws SQLException {
	ps.setInt(1, rs.getInt(1));
	ps.setString(2, rs.getString(2));
	ps.setString(3, rs.getString(3));
	ps.setString(4, rs.getString(4));
	ps.setString(5, rs.getString(5));
	ps.setInt(6, rs.getInt(6));
	ps.setBoolean(7, rs.getBoolean(7));
	ps.setLong(8, rs.getLong(8));
	ps.executeUpdate();
}
 

/**
 * Assert that specified ResultSet contains no rows.
 *
 * @param rs result set to check.
 * @throws SQLException on error.
 */
private static void assertIsEmpty(ResultSet rs) throws SQLException {
    try {
        boolean empty = !rs.next();

        assertTrue("Result should be empty because invalid catalog is specified.", empty);
    }
    finally {
        rs.close();
    }
}
 

public void setTransactionParameters(int isolation, TransactionParameterBuffer transactionParams)
        throws SQLException {
    synchronized (syncObject) {
        FBTpbMapper mapping = transactionMapping;
        if (mapping == null) {
            mapping = transactionMapping = mcf.getTransactionMappingCopy();
        }
        mapping.setMapping(isolation, transactionParams);
        if (getTransactionIsolation() == isolation) {
            // Make sure next transaction uses the new config
            setTransactionIsolation(isolation);
        }
    }
}
 

public void testInsertAllWithSourceId() throws SQLException
{
    int startId = 5000;
    int count = 1012;
    EmployeeList list = new EmployeeList();
    for(int i=0;i<count;i++)
    {
        Employee emp = new Employee();
        emp.setId(startId+i);
        emp.setName("Employee "+i);
        emp.setSourceId(i % 2);
        list.add(emp);
    }
    list.insertAll();
    Connection con = this.getConnection(0);
    String sql = "select count(*) from EMPLOYEE where ID >= ? AND ID < ?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, startId);
    ps.setInt(2, startId+count);
    ResultSet rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals(count/2, rs.getInt(1));
    rs.close();
    ps.close();
    con.close();

    con = this.getConnection(1);
    ps = con.prepareStatement(sql);
    ps.setInt(1, startId);
    ps.setInt(2, startId+count);
    rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals(count/2, rs.getInt(1));
    rs.close();
    ps.close();
    con.close();
}
 

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

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

private PTable loadTable(RegionCoprocessorEnvironment env, byte[] key, ImmutableBytesPtr cacheKey, long clientTimeStamp, long asOfTimeStamp) throws IOException, SQLException {
    HRegion region = env.getRegion();
    Map<ImmutableBytesPtr,PTable> metaDataCache = GlobalCache.getInstance(this.getEnvironment()).getMetaDataCache();
    PTable table = metaDataCache.get(cacheKey);
    // We always cache the latest version - fault in if not in cache
    if (table != null || (table = buildTable(key, cacheKey, region, asOfTimeStamp)) != null) {
        return table;
    }
    // if not found then check if newer table already exists and add delete marker for timestamp found
    if (table == null && (table=buildDeletedTable(key, cacheKey, region, clientTimeStamp)) != null) {
        return table;
    }
    return null;
}
 

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

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

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

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