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

The following examples show how to use java.sql.PreparedStatement#setInt() . 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 check out the related API usage on the sidebar.
Example 1
Source File: StatementPoolingTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
/**
 * Tests that nothing is committed on the connection when autocommit is
 * disabled.
 *
 * @throws SQLException if something goes wrong...
 */
public void resTestNoCommitOnReuse()
        throws SQLException {
    // Make sure the table is empty.
    cleanTableExceptedToBeEmpty();

    // Start test
    final String sql = "insert into stmtpooldata (val) values ?";
    getConnection().setAutoCommit(false);
    PreparedStatement ps = prepareStatement(sql);
    ps.setInt(1, 68);
    assertEquals(1, ps.executeUpdate());
    ps.close();
    ps = prepareStatement(sql);
    ps.setInt(1, 77);
    assertEquals(1, ps.executeUpdate());
    Statement stmt = createStatement();
    ResultSet rs =stmt.executeQuery(
            "select val from stmtpooldata order by val");
    JDBC.assertFullResultSet(rs, new String[][] {{"68"},{"77"}});
    rollback();
    rs = stmt.executeQuery("select val from stmtpooldata order by val");
    JDBC.assertEmpty(rs);
}
 
Example 2
Source File: WorkflowRequestAssociationDAO.java    From carbon-identity-framework with Apache License 2.0 6 votes vote down vote up
/**
 * Adds new workflow-request relationship to database
 *
 * @param relationshipId
 * @param workflowId
 * @param requestId
 * @param status
 * @throws InternalWorkflowException
 */
public void addNewRelationship(String relationshipId, String workflowId, String requestId, String status,
                               int tenantId) throws InternalWorkflowException {
    Connection connection = IdentityDatabaseUtil.getDBConnection();
    PreparedStatement prepStmt = null;
    String query = SQLConstants.ADD_WORKFLOW_REQUEST_RELATIONSHIP;
    try {
        Timestamp createdDateStamp = new Timestamp(System.currentTimeMillis());
        prepStmt = connection.prepareStatement(query);
        prepStmt.setString(1, relationshipId);
        prepStmt.setString(2, workflowId);
        prepStmt.setString(3, requestId);
        prepStmt.setTimestamp(4, createdDateStamp);
        prepStmt.setString(5, status);
        prepStmt.setInt(6, tenantId);
        prepStmt.execute();
        IdentityDatabaseUtil.commitTransaction(connection);
    } catch (SQLException e) {
        IdentityDatabaseUtil.rollbackTransaction(connection);
        throw new InternalWorkflowException("Error when executing the sql query:" + query, e);
    } finally {
        IdentityDatabaseUtil.closeAllConnections(connection, null, prepStmt);
    }
}
 
Example 3
Source File: MultiDBPreparedStatementLifeCycleTest.java    From Zebra with Apache License 2.0 6 votes vote down vote up
@Test
public void testMultiRouterResult16() throws Exception {
    DataSource ds = (DataSource) context.getBean("zebraDS");
    Connection conn = null;
    try {
        conn = ds.getConnection();
        PreparedStatement stmt = conn
                .prepareStatement("select distinct score from test order by score asc limit ?,?");
        stmt.setInt(1, 7);
        stmt.setInt(2, 10);
        stmt.execute();
        ResultSet rs = stmt.getResultSet();
        int count = 0;
        while (rs.next()) {
            count++;
        }
        Assert.assertEquals(2, count);
    } catch (Exception e) {
        Assert.fail();
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}
 
Example 4
Source File: TxTriggerProcedureTest.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
public static void deleteTxhistory(String tableName, int pk1, String type)
throws SQLException {
  Connection conn = getDefaultConnection();
  int pk2 = type.equalsIgnoreCase("sell")? SELL : BUY;
  
  String deletgfxdTxhistory = "update trade.monitor set deleteCount = deleteCount + 1 " +
  "where tname = ? and pk1 = ? and pk2 = ?";
  PreparedStatement ps = conn.prepareStatement(deletgfxdTxhistory);

  ps.setString(1, tableName);
  ps.setInt(2, pk1);
  ps.setInt(3, pk2);
  Log.getLogWriter().info(deletgfxdTxhistory + " for " + tableName + " and pk1 " + pk1 
      + " and pk2 " + pk2);
  
  try {
    ps.execute();
  } catch (SQLException se) {
    if (se.getSQLState().equals("X0Z02")) {
      throw new TestException("Got unexpected conflict exception in trigger" 
          + TestHelper.getStackTrace(se));
    } else throw se;
  }

  closeConnection(conn);
}
 
Example 5
Source File: MyTownDatasource.java    From MyTown2 with The Unlicense 6 votes vote down vote up
public boolean unlinkResidentFromPlot(Resident res, Plot plot) {
    try {
        PreparedStatement s = prepare("DELETE FROM " + prefix + "ResidentsToPlots WHERE resident=? AND plotID=?", true);
        s.setString(1, res.getUUID().toString());
        s.setInt(2, plot.getDbID());
        s.executeUpdate();

        plot.ownersContainer.remove(res);
        plot.membersContainer.remove(res);

    } catch (SQLException e) {
        LOG.error("Failed to unlink {} to plot {} in town {}", res.getPlayerName(), plot.getName(), plot.getTown().getName());
        LOG.error(ExceptionUtils.getStackTrace(e));
        return false;
    }
    return true;
}
 
Example 6
Source File: CertStore.java    From xipki with Apache License 2.0 6 votes vote down vote up
public KnowCertResult knowsCertForSerial(NameId ca, BigInteger serial) throws OperationException {
  Args.notNull(serial, "serial");
  final String sql = sqlKnowsCertForSerial;

  ResultSet rs = null;
  PreparedStatement ps = borrowPreparedStatement(sql);

  try {
    ps.setString(1, serial.toString(16));
    ps.setInt(2, ca.getId());
    rs = ps.executeQuery();

    if (!rs.next()) {
      return KnowCertResult.UNKNOWN;
    }

    int userId = rs.getInt("UID");
    return new KnowCertResult(true, userId);
  } catch (SQLException ex) {
    throw new OperationException(DATABASE_FAILURE, datasource.translate(sql, ex).getMessage());
  } finally {
    datasource.releaseResources(ps, rs);
  }
}
 
Example 7
Source File: BackwardCompatabilityDUnit.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
private static void runDMLsAsBatch(final int start, final int numInserts,
    final Connection conn) throws SQLException {
  final int last = start + numInserts;
  PreparedStatement pstmt;
  int numParams;
  for (String dml : DMLS) {
    pstmt = conn.prepareStatement(dml);
    numParams = pstmt.getParameterMetaData().getParameterCount();
    for (int id = start; id < last; id++) {
      if (numParams == 1) {
        pstmt.setString(1, "addr" + id);
      }
      else {
        pstmt.setInt(1, id);
        pstmt.setString(2, "addr" + id);
      }
      pstmt.addBatch();
    }
    pstmt.executeBatch();
  }
}
 
Example 8
Source File: Subquery.java    From gemfirexd-oss with Apache License 2.0 6 votes vote down vote up
private ResultSet getNonUniqQuery0(Connection conn, int whichQuery, int sid, 
    boolean[] success) {
  PreparedStatement stmt;
  ResultSet rs = null;
  success[0] = true;
  try {
    Log.getLogWriter().info("which query is -- " + nonUniqSelect[whichQuery]);
    stmt = conn.prepareStatement(nonUniqSelect[whichQuery]);      
    Log.getLogWriter().info("data used in query -- sid: "+ sid);
    stmt.setInt(1, sid);
    rs = stmt.executeQuery();
  } catch (SQLException se) {
  	SQLHelper.printSQLException(se);
    if (!SQLHelper.checkDerbyException(conn, se)) success[0] = false; //handle lock could not acquire or deadlock
    else if (!SQLHelper.checkGFXDException(conn, se)) success[0] = false; //hand X0Z01 and #41471
    else SQLHelper.handleSQLException(se);
  }
  return rs;
}
 
Example 9
Source File: MapServerOptions.java    From open-rmbt with Apache License 2.0 5 votes vote down vote up
@Override
SQLFilter getFilter(final String input)
{
    if (Strings.isNullOrEmpty(input))
        return null;
    return new SQLFilter("provider_id=?")
    {
        @Override
        int fillParams(int i, final PreparedStatement ps) throws SQLException
        {
            ps.setInt(i++, Integer.parseInt(input));
            return i;
        }
    };
}
 
Example 10
Source File: HostDirMapDao.java    From burp_data_collector with Apache License 2.0 5 votes vote down vote up
public void exportDir(String dirName, int dirCount) throws SQLException, IOException {
    String sql = "SELECT stat.dir, sum(dirCount) AS allCount\n" +
            "FROM ((SELECT hdm.dir, count(*) AS dirCount FROM host_dir_map hdm GROUP BY hdm.dir)\n" +
            "      UNION ALL\n" +
            "      (SELECT dir, count AS dirCount FROM dir)) stat\n" +
            "GROUP BY stat.dir\n" +
            "HAVING allCount >= ?\n" +
            "ORDER BY allCount DESC";
    PreparedStatement preparedStatement = getPreparedStatement(sql);
    preparedStatement.setInt(1, dirCount);
    ResultSet resultSet = preparedStatement.executeQuery();

    File dirFile = new File(dirName + DIR_FILE);
    File dirImportFile = new File(dirName + DIR_IMPORT_FILE);
    FileOutputStream dirFileOutputStream = new FileOutputStream(dirFile);
    FileWriter fileWriter = new FileWriter(dirImportFile);
    CSVWriter csvWriter = new CSVWriter(fileWriter);
    String[] fileHead = new String[]{"dir", "count"};
    csvWriter.writeNext(fileHead);
    while (resultSet.next()) {
        String dir = resultSet.getString(1);
        String row = dir + "\n";
        int count = resultSet.getInt(2);
        dirFileOutputStream.write(row.getBytes());
        csvWriter.writeNext(new String[]{dir, String.valueOf(count)}, true);

    }
    dirFileOutputStream.close();
    csvWriter.close();
}
 
Example 11
Source File: TradeBuyOrdersDMLTxStmt.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
protected int deleteToTableTidListTx(PreparedStatement stmt, int oid1, int oid2,
		int oid, BigDecimal bid, int whichDelete, int tid) throws SQLException {
   int rowCount = 0;
   switch (whichDelete) {
   case 0: 
		//	    "delete from trade.buyorders where oid=? and tid=? and bid <?",
     Log.getLogWriter().info("deleting from buyorders for oid: " + oid 
     		+ " and tid: " + tid + " and bid<" + bid);
     stmt.setInt(1, oid);
     stmt.setInt(2, tid);
     stmt.setBigDecimal(3, bid);
     rowCount = stmt.executeUpdate();    
     break;
   case 1: 
		//"delete from trade.buyorders where oid>? and oid<? and status IN ('cancelled', 'filled') and tid=? ", 
     Log.getLogWriter().info("deleting from buyorders for oid > " + oid1 
     		+ " oid < " + oid2 + " and status IN ('cancelled', 'filled') and tid: " + tid);
     stmt.setInt(1, oid1);
     stmt.setInt(2, oid2);
     stmt.setInt(3, tid);
     rowCount = stmt.executeUpdate();       
     break;
   default:
    throw new TestException ("Wrong delete sql string here");
   }
   SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
   if (warning != null) {
     SQLHelper.printSQLWarning(warning);
   } 
   return rowCount;		
}
 
Example 12
Source File: DBOperator.java    From translationstudio8 with GNU General Public License v2.0 5 votes vote down vote up
/**
 * 根据指定的语言,上下文和纯文本的hash码在TEXTDATA表中查找记录
 * @param hash
 *            纯文本的hash码
 * @param lang
 *            语言代码
 * @param preContext
 *            上文 hash码
 * @param nextContext
 *            下文hash码
 * @param type
 *            记录类型,M:TMX,B:tbx
 * @return 一组符合条件的TEXTDATA数据记录的主键
 * @throws SQLException
 *             ;
 */
public List<String> getTextDataId(int hash, String lang, String preContext, String nextContext, String type)
		throws SQLException {
	List<String> ids = new ArrayList<String>();
	PreparedStatement stmt = null;
	ResultSet rs = null;
	try {
		String sql = dbConfig.getOperateDbSQL("get-textdataid-bycontext");
		stmt = conn.prepareStatement(sql);
		int i = 1;
		stmt.setInt(i++, hash);
		stmt.setString(i++, preContext);
		stmt.setString(i++, nextContext);
		stmt.setString(i++, lang);
		stmt.setString(i++, type);
		rs = stmt.executeQuery();
		while (rs.next()) {
			ids.add(rs.getInt("TPKID") + "");
		}
	} finally {
		if (rs != null) {
			rs.close();
		}
		if (stmt != null) {
			stmt.close();
		}
	}
	return ids;
}
 
Example 13
Source File: ProfileDAOImpl.java    From carbon-device-mgt with Apache License 2.0 5 votes vote down vote up
@Override
public Profile getProfile(int profileId) throws ProfileManagerDAOException {
    Connection conn;
    PreparedStatement stmt = null;
    ResultSet resultSet = null;
    Profile profile = null;
    try {
        conn = this.getConnection();
        String query = "SELECT * FROM DM_PROFILE WHERE ID = ?";
        stmt = conn.prepareStatement(query);
        stmt.setInt(1, profileId);
        resultSet = stmt.executeQuery();

        while (resultSet.next()) {
            profile = new Profile();
            profile.setProfileId(profileId);
            profile.setProfileName(resultSet.getString("PROFILE_NAME"));
            profile.setTenantId(resultSet.getInt("TENANT_ID"));
            profile.setDeviceType(resultSet.getString("DEVICE_TYPE"));
            profile.setCreatedDate(resultSet.getTimestamp("CREATED_TIME"));
            profile.setUpdatedDate(resultSet.getTimestamp("UPDATED_TIME"));
        }

    } catch (SQLException e) {
        String msg = "Error occurred while reading the profile from the database.";
        log.error(msg, e);
        throw new ProfileManagerDAOException(msg, e);
    } finally {
        PolicyManagementDAOUtil.cleanupResources(stmt, resultSet);
    }
    return profile;
}
 
Example 14
Source File: BaseSaltedTableIT.java    From phoenix with Apache License 2.0 4 votes vote down vote up
protected static String initTableValues(byte[][] splits) throws Exception {
    String tableName = generateUniqueName();
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);

    // Rows we inserted:
    // 1ab123abc111
    // 1abc456abc111
    // 1de123abc111
    // 2abc123def222
    // 3abc123ghi333
    // 4abc123jkl444
    try {
        // Upsert with no column specifies.
        ensureTableCreated(getUrl(), tableName, TABLE_WITH_SALTING, splits, null, null);
        String query = "UPSERT INTO " + tableName + " VALUES(?,?,?,?,?)";
        PreparedStatement stmt = conn.prepareStatement(query);
        stmt.setInt(1, 1);
        stmt.setString(2, "ab");
        stmt.setString(3, "123");
        stmt.setString(4, "abc");
        stmt.setInt(5, 111);
        stmt.execute();
        conn.commit();

        stmt.setInt(1, 1);
        stmt.setString(2, "abc");
        stmt.setString(3, "456");
        stmt.setString(4, "abc");
        stmt.setInt(5, 111);
        stmt.execute();
        conn.commit();

        // Test upsert when statement explicitly specifies the columns to upsert into.
        query = "UPSERT INTO " + tableName +
                " (a_integer, a_string, a_id, b_string, b_integer) " +
                " VALUES(?,?,?,?,?)";
        stmt = conn.prepareStatement(query);

        stmt.setInt(1, 1);
        stmt.setString(2, "de");
        stmt.setString(3, "123");
        stmt.setString(4, "abc");
        stmt.setInt(5, 111);
        stmt.execute();
        conn.commit();

        stmt.setInt(1, 2);
        stmt.setString(2, "abc");
        stmt.setString(3, "123");
        stmt.setString(4, "def");
        stmt.setInt(5, 222);
        stmt.execute();
        conn.commit();

        // Test upsert when order of column is shuffled.
        query = "UPSERT INTO " + tableName +
                " (a_string, a_integer, a_id, b_string, b_integer) " +
                " VALUES(?,?,?,?,?)";
        stmt = conn.prepareStatement(query);
        stmt.setString(1, "abc");
        stmt.setInt(2, 3);
        stmt.setString(3, "123");
        stmt.setString(4, "ghi");
        stmt.setInt(5, 333);
        stmt.execute();
        conn.commit();

        stmt.setString(1, "abc");
        stmt.setInt(2, 4);
        stmt.setString(3, "123");
        stmt.setString(4, "jkl");
        stmt.setInt(5, 444);
        stmt.execute();
        conn.commit();
    } finally {
        conn.close();
    }
    return tableName;
}
 
Example 15
Source File: PlatformImplBase.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * This is the core method to set the parameter of a prepared statement to a given value.
 * The primary purpose of this method is to call the appropriate method on the statement,
 * and to give database-specific implementations the ability to change this behavior.
 * 
 * @param statement The statement
 * @param sqlIndex  The parameter index
 * @param typeCode  The JDBC type code
 * @param value     The value
 * @throws SQLException If an error occurred while setting the parameter value
 */
protected void setStatementParameterValue(PreparedStatement statement, int sqlIndex, int typeCode, Object value) throws SQLException
{
	if (value == null)
       {
           statement.setNull(sqlIndex, typeCode);
       }
       else if (value instanceof String)
       {
           statement.setString(sqlIndex, (String)value);
       }
       else if (value instanceof byte[])
       {
           statement.setBytes(sqlIndex, (byte[])value);
       }
       else if (value instanceof Boolean)
       {
           statement.setBoolean(sqlIndex, ((Boolean)value).booleanValue());
       }
       else if (value instanceof Byte)
       {
           statement.setByte(sqlIndex, ((Byte)value).byteValue());
       }
       else if (value instanceof Short)
       {
           statement.setShort(sqlIndex, ((Short)value).shortValue());
       }
       else if (value instanceof Integer)
       {
           statement.setInt(sqlIndex, ((Integer)value).intValue());
       }
       else if (value instanceof Long)
       {
           statement.setLong(sqlIndex, ((Long)value).longValue());
       }
       else if (value instanceof BigDecimal)
       {
           // setObject assumes a scale of 0, so we rather use the typed setter
           statement.setBigDecimal(sqlIndex, (BigDecimal)value);
       }
       else if (value instanceof Float)
       {
           statement.setFloat(sqlIndex, ((Float)value).floatValue());
       }
       else if (value instanceof Double)
       {
           statement.setDouble(sqlIndex, ((Double)value).doubleValue());
       }
       else
       {
           statement.setObject(sqlIndex, value, typeCode);
       }
}
 
Example 16
Source File: DataStore.java    From AIDR with GNU Affero General Public License v3.0 4 votes vote down vote up
private static void updateLabels(Integer attributeID){

		Connection conn = null;
		PreparedStatement selectStatement = null;
		ResultSet result = null;
		String selectQuery = "SELECT nominalLabelID,l.nominalLabelCode,l.name as nominalLabelName,"
				+ "l.description as nominLabelDescription FROM nominal_label l where l.nominalAttributeID = ?";
		
		try {
			conn = getMySqlConnection();
			selectStatement = conn.prepareStatement(selectQuery);
			selectStatement.setInt(1, attributeID);
			result = selectStatement.executeQuery();
			NominalAttributeEC attribute = null;
			NominalLabelEC label = null;

			while (result.next()) {
				int labelID = result.getInt("nominalLabelID");
				attribute = attLabels.get(attributeID);
				attribute.resetNominalLabels();
				if(attribute.getNominalLabel(labelID) == null)
				{
					label = new NominalLabelEC();
					label.setDescription(result.getString("nominLabelDescription"));
					label.setName(result.getString("nominalLabelName"));
					label.setNominalAttribute(attribute);
					label.setNominalLabelCode(result.getString("nominalLabelCode"));
					label.setNominalLabelID(result.getInt("nominalLabelID"));
					attribute.addNominalLabel(label);
				}
			}


		} catch (SQLException e) {
			logger.error("Exception while updating nominal labels ::", e);
		} finally {
			close(result);
			close(selectStatement);
			close(conn);
		}
	}
 
Example 17
Source File: SybaseModelReader.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * {@inheritDoc}
 */
protected Collection readForeignKeys(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException
{
    // Sybase (or jConnect) does not return the foreign key names, thus we have to
    // read the foreign keys manually from the system tables
    final String colQuery = 
        "SELECT refobjs.name, localtables.id, remotetables.name, remotetables.id," +
        "       refs.fokey1, refs.refkey1, refs.fokey2, refs.refkey2, refs.fokey3, refs.refkey3, refs.fokey4, refs.refkey4," +
        "       refs.fokey5, refs.refkey5, refs.fokey6, refs.refkey6, refs.fokey7, refs.refkey7, refs.fokey8, refs.refkey8," +
        "       refs.fokey9, refs.refkey9, refs.fokey10, refs.refkey10, refs.fokey11, refs.refkey11, refs.fokey12, refs.refkey12," +
        "       refs.fokey13, refs.refkey13, refs.fokey14, refs.refkey14, refs.fokey15, refs.refkey15, refs.fokey16, refs.refkey16," +
        " FROM sysreferences refs, sysobjects refobjs, sysobjects localtables, sysobjects remotetables" +
        " WHERE refobjs.type = 'RI' AND refs.constrid = refobjs.id AND" +
        "       localtables.type = 'U' AND refs.tableid = localtables.id AND localtables.name = ?" +
        "   AND remotetables.type = 'U' AND refs.reftabid = remotetables.id";
    final String refObjQuery = 
        "SELECT name FROM syscolumns WHERE id = ? AND colid = ?";

    PreparedStatement colStmt    = null;
    PreparedStatement refObjStmt = null;
    ArrayList         result     = new ArrayList();

    try
    {
        colStmt    = getConnection().prepareStatement(colQuery);
        refObjStmt = getConnection().prepareStatement(refObjQuery);

        ResultSet fkRs = colStmt.executeQuery();

        while (fkRs.next())
        {
            ForeignKey fk            = new ForeignKey(fkRs.getString(1));
            int        localTableId  = fkRs.getInt(2);
            int        remoteTableId = fkRs.getInt(4);

            fk.setForeignTableName(fkRs.getString(3));
            for (int idx = 0; idx < 16; idx++)
            {
                short     fkColIdx = fkRs.getShort(5 + idx + idx);
                short     pkColIdx = fkRs.getShort(6 + idx + idx);
                Reference ref      = new Reference();

                if (fkColIdx == 0)
                {
                    break;
                }

                refObjStmt.setInt(1, localTableId);
                refObjStmt.setShort(2, fkColIdx);

                ResultSet colRs = refObjStmt.executeQuery();

                if (colRs.next())
                {
                    ref.setLocalColumnName(colRs.getString(1));
                }
                colRs.close();

                refObjStmt.setInt(1, remoteTableId);
                refObjStmt.setShort(2, pkColIdx);

                colRs = refObjStmt.executeQuery();

                if (colRs.next())
                {
                    ref.setForeignColumnName(colRs.getString(1));
                }
                colRs.close();

                fk.addReference(ref);
            }
            result.add(fk);
        }
    }
    finally
    {
        closeStatement(colStmt);
        closeStatement(refObjStmt);
    }

    return result;
}
 
Example 18
Source File: TradeSecurityHdfsDataVerifier.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
public void setSec_id(int i, PreparedStatement ps) throws SQLException {
  ps.setInt(i,sec_id);
}
 
Example 19
Source File: CrudTest.java    From tddl with Apache License 2.0 4 votes vote down vote up
private void testCrud(DataSource ds) throws SQLException {
    Connection conn = ds.getConnection();
    // 测试Statement的crud
    Statement stmt = conn.createStatement();
    assertEquals(stmt.executeUpdate("insert into tddl_test_0000(id,name,gmt_create,gmt_modified) values(10,'str',now(),now())"),
        1);
    assertEquals(stmt.executeUpdate("update tddl_test_0000 set name='str2'"), 1);
    ResultSet rs = stmt.executeQuery("select id,name from tddl_test_0000");
    assertEquals(true, rs.next());
    assertEquals(10, rs.getInt(1));
    assertEquals("str2", rs.getString(2));
    assertEquals(stmt.executeUpdate("delete from tddl_test_0000"), 1);
    rs.close();
    stmt.close();

    // 测试PreparedStatement的crud
    String sql = "insert into tddl_test_0000(id,name,gmt_create,gmt_modified) values(?,?,now(),now())";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setInt(1, 10);
    ps.setString(2, "str");
    assertEquals(ps.executeUpdate(), 1);
    ps.close();

    sql = "update tddl_test_0000 set name=?";
    ps = conn.prepareStatement(sql);
    ps.setString(1, "str2");
    assertEquals(ps.executeUpdate(), 1);
    ps.close();

    sql = "select id,name from tddl_test_0000";
    ps = conn.prepareStatement(sql);
    rs = ps.executeQuery();
    rs.next();
    assertEquals(rs.getInt(1), 10);
    assertEquals(rs.getString(2), "str2");
    rs.close();
    ps.close();

    sql = "delete from tddl_test_0000";
    ps = conn.prepareStatement(sql);
    assertEquals(ps.executeUpdate(), 1);
    ps.close();
    conn.close();
}
 
Example 20
Source File: LocalIndexTransactionRRDUnit.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/** check that conflicts during commit lead to proper rollback in indexes */
public void testIndexesInCommitConflict_44435() throws Exception {
  startVMs(1, 1);

  Connection conn = TestUtil.getConnection();
  conn.setTransactionIsolation(getIsolationLevel());
  conn.setAutoCommit(false);
  Statement st = conn.createStatement();
  st.execute("Create table t1 (c1 int not null primary key, c2 int not null, "
      + "c3 int not null, c4 int not null, c5 int not null)");
  st.execute("create index i1 on t1 (c5)");
  conn.commit();

  final int numRows = 20;
  PreparedStatement pstmt = conn
      .prepareStatement("insert into t1 values(?,?,?,?,?)");
  for (int c1 = 1; c1 <= numRows; c1++) {
    pstmt.setInt(1, c1);
    pstmt.setInt(2, c1);
    pstmt.setInt(3, c1);
    pstmt.setInt(4, c1);
    pstmt.setInt(5, c1);
    pstmt.executeUpdate();

  }
  conn.commit();

  // create a read-write conflict that will throw on commit

  ResultSet rs = st.executeQuery("select * from t1");
  while (rs.next()) {
    assertEquals(rs.getInt(1), rs.getInt(2));
  }
  rs.close();

  Connection conn2 = TestUtil.getConnection();
  conn2.setTransactionIsolation(getIsolationLevel());
  conn2.setAutoCommit(false);
  Statement st2 = conn2.createStatement();
  assertEquals(1, st2.executeUpdate("update t1 set c5 = 5 where c5 = 1"));
  try {
    conn2.commit();
    fail("expected conflict exception");
  } catch (SQLException sqle) {
    if (!"X0Z02".equals(sqle.getSQLState())) {
      throw sqle;
    }
  }

  assertEquals(1, st.executeUpdate("update t1 set c5 = 5 where c5 = 1"));
  conn.commit();

  boolean foundOne = false;
  rs = st.executeQuery("select * from t1 where c5 = 5");
  for (int i = 1; i <= 2; i++) {
    assertTrue(rs.next());
    assertEquals(5, rs.getInt(5));
    if (foundOne) {
      assertEquals(5, rs.getInt(1));
    }
    else if (rs.getInt(1) == 1) {
      foundOne = true;
    }
    else {
      assertEquals(5, rs.getInt(1));
    }
  }
  assertFalse(rs.next());

  assertEquals(1, st.executeUpdate("update t1 set c5 = 10 where c1 = 1"));
  conn.commit();

  rs = st.executeQuery("select * from t1 where c5 = 10");
  foundOne = false;
  for (int i = 1; i <= 2; i++) {
    assertTrue(rs.next());
    assertEquals(10, rs.getInt(5));
    if (foundOne) {
      assertEquals(10, rs.getInt(1));
    }
    else if (rs.getInt(1) == 1) {
      foundOne = true;
    }
    else {
      assertEquals(10, rs.getInt(1));
    }
  }
  conn.commit();
}