Java Code Examples for java.sql.Connection#prepareStatement()

The following examples show how to use java.sql.Connection#prepareStatement() . 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
/**
 * Do some basic verification on a connection obtained from the data source.
 *
 * @param cDs data source to get connection from
 * @throws SQLException if a JDBC operation fails
 */
private void verifyConnection(ClientConnectionPoolDataSource cDs)
        throws SQLException {
    J2EEDataSource.setBeanProperty(cDs, "createDatabase", "create");
    PooledConnection pc = cDs.getPooledConnection();
    // Get a connection and make sure we can access the database.
    Connection con = pc.getConnection();
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("select * from sys.systables");
    JDBC.assertDrainResultsHasData(rs);
    PreparedStatement ps1 = con.prepareStatement("values 31");
    JDBC.assertSingleValueResultSet(ps1.executeQuery(), "31");
    ps1.close();
    PreparedStatement ps2 = con.prepareStatement("values 31");
    // The physical statement is supposed to be the same, but not the
    // logical prepared statements (if pooling is used).
    assertNotSame(ps1, ps2);
    JDBC.assertSingleValueResultSet(ps2.executeQuery(), "31");
    // Close everything
    stmt.close();
    ps2.close();
    con.close();
    pc.close();
}
 
Example 2
private ResultSet getNonUniqQuery0(Connection conn, int whichQuery,  
    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("no bound data used in query");
    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 3
private ResultSet getUniqQuery3(Connection conn, int whichQuery,  
    int cid, BigDecimal price, int tid, boolean[] success) {
  PreparedStatement stmt;
  ResultSet rs = null;
  success[0] = true;
  try {
    Log.getLogWriter().info("which query is -- " + uniqSelect[whichQuery]);
    stmt = conn.prepareStatement(uniqSelect[whichQuery]);      
    Log.getLogWriter().info("data used in query price " + price + " and tid: "+ tid
    		+ " and cid: " + cid);
    stmt.setInt(1, cid);
    stmt.setBigDecimal(3, price);
    stmt.setInt(2, tid);
    stmt.setInt(4, tid);
    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 if (se.getSQLState().equalsIgnoreCase("0A000") && whichQuery == 3) success[0] = true;
    else SQLHelper.handleSQLException(se);
  }
  return rs;
}
 
Example 4
@Test
public void testInListSkipScan() throws Exception {
    String query = "SELECT entity_id, b_string FROM aTable WHERE organization_id=? and entity_id IN (?,?)";
    Properties props = new Properties(TEST_PROPERTIES);
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
    Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setString(2, ROW2);
        statement.setString(3, ROW4);
        ResultSet rs = statement.executeQuery();
        Set<String> expectedvals = new HashSet<String>();
        expectedvals.add(ROW2+"_"+C_VALUE);
        expectedvals.add(ROW4+"_"+B_VALUE);
        Set<String> vals = new HashSet<String>();
        assertTrue (rs.next());
        vals.add(rs.getString(1) + "_" + rs.getString(2));
        assertTrue (rs.next());
        vals.add(rs.getString(1) + "_" + rs.getString(2));
        assertFalse(rs.next());
        assertEquals(expectedvals, vals);
    } finally {
        conn.close();
    }
}
 
Example 5
@Override
public int getGroupCount(int tenantId) throws GroupManagementDAOException {
    PreparedStatement stmt = null;
    ResultSet resultSet = null;
    try {
        Connection conn = GroupManagementDAOFactory.getConnection();
        String sql = "SELECT COUNT(ID) AS GROUP_COUNT FROM DM_GROUP WHERE TENANT_ID = ?";
        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, tenantId);
        resultSet = stmt.executeQuery();
        if (resultSet.next()) {
            return resultSet.getInt("GROUP_COUNT");
        } else {
            return 0;
        }
    } catch (SQLException e) {
        throw new GroupManagementDAOException("Error occurred while getting group count'", e);
    } finally {
        GroupManagementDAOUtil.cleanupResources(stmt, resultSet);
    }
}
 
Example 6
public static void insertSingleKeyTable(String tableName, int pk1) throws SQLException {
  Connection conn = getDefaultConnection();
 
  PreparedStatement ps = conn.prepareStatement(insertsql);

  ps.setString(1, tableName);
  ps.setInt(2, pk1);
  ps.setInt(3, -1);
  ps.setInt(4, 1);
  ps.setInt(5, 0);
  ps.setInt(6, 0);
  Log.getLogWriter().info("insert into trade.monitor values("
      + tableName + ", " + pk1 + ", -1, 1, 0, 0 )");
  
  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 7
/**
 * Since inventory is not using FK - need to clean items
 */
@Override
public boolean deletePlayerItems(final int playerId) {
	Connection con = null;
	try {
		con = DatabaseFactory.getConnection();
		PreparedStatement stmt = con.prepareStatement(DELETE_CLEAN_QUERY);
		stmt.setInt(1, playerId);
		stmt.execute();
		stmt.close();
	}
	catch (Exception e) {
		log.error("Error Player all items. PlayerObjId: " + playerId, e);
		return false;
	}
	finally {
		DatabaseFactory.close(con);
	}
	return true;
}
 
Example 8
/**
 * add a record in snapshots table for a given timestamp and retrieve the snapid 
 * @param ts
 * @return
 */
public int getNextSnapshotId(long ts)
{
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
String insertSQL = "insert into SNAPSHOTS (START_TS) values(?)";
String retrieveSQL = "select SNAP_ID from SNAPSHOTS where START_TS=?";
try
{
  conn = this.createConnection(true);
  stmt = conn.prepareStatement(insertSQL);
  stmt.setLong(1, ts);
  stmt.execute();
  stmt.close();
  stmt = conn.prepareStatement(retrieveSQL);
  stmt.setLong(1, ts);
  rs = stmt.executeQuery();
  if(rs!=null && rs.next())
  {
	  return rs.getInt(1);
  }
}catch(Exception ex)
{
}finally
{
  DBUtils.close(rs);
  DBUtils.close(stmt);
  DBUtils.close(conn);
}
 return -1;//error
}
 
Example 9
@Test
public void should_bootstrap_schema() throws SQLException {
    Connection connection = pool.getConnection();

    PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM PIZZAS");
    ResultSet resultSet = statement.executeQuery();
    resultSet.first();
    int count = resultSet.getInt(1);
    assertThat(count).isEqualTo(0);
}
 
Example 10
@Override
public List<Device> getDevicesByName(PaginationRequest request, int tenantId)
        throws DeviceManagementDAOException {
    Connection conn;
    PreparedStatement stmt = null;
    List<Device> devices = new ArrayList<>();
    try {
        conn = this.getConnection();
        String sql = "SELECT d1.ID AS DEVICE_ID, d1.DESCRIPTION, d1.NAME AS DEVICE_NAME, d1.DEVICE_TYPE, " +
                     "d1.DEVICE_IDENTIFICATION, e.OWNER, e.OWNERSHIP, e.STATUS, e.DATE_OF_LAST_UPDATE, " +
                     "e.DATE_OF_ENROLMENT, e.ID AS ENROLMENT_ID FROM DM_ENROLMENT e, (SELECT d.ID, d.NAME, " +
                     "d.DESCRIPTION, t.NAME AS DEVICE_TYPE, d.DEVICE_IDENTIFICATION FROM DM_DEVICE d, " +
                     "DM_DEVICE_TYPE t WHERE d.DEVICE_TYPE_ID = t.ID AND d.NAME LIKE ? AND d.TENANT_ID = ?) d1 " +
                     "WHERE DEVICE_ID = e.DEVICE_ID AND TENANT_ID = ? ORDER BY ENROLMENT_ID " +
                     "OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
        stmt = conn.prepareStatement(sql);
        stmt.setString(1, request.getDeviceName() + "%");
        stmt.setInt(2, tenantId);
        stmt.setInt(3, tenantId);
        stmt.setInt(4, request.getStartIndex());
        stmt.setInt(5, request.getRowCount());
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Device device = DeviceManagementDAOUtil.loadDevice(rs);
            devices.add(device);
        }
    } catch (SQLException e) {
        throw new DeviceManagementDAOException("Error occurred while fetching the list of devices that matches " +
                                               "'" + request.getDeviceName() + "'", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, null);
    }
    return devices;
}
 
Example 11
Source Project: shardingsphere   File: AdditionalDQLIT.java    License: Apache License 2.0 5 votes vote down vote up
private void assertExecuteForPreparedStatementWithResultSetTypeAndResultSetConcurrencyAndResultSetHoldability(final Connection connection)
        throws SQLException, ParseException, JAXBException, IOException {
    try (PreparedStatement preparedStatement = connection.prepareStatement(getSql(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT)) {
        for (SQLValue each : assertion.getSQLValues()) {
            preparedStatement.setObject(each.getIndex(), each.getValue());
        }
        assertTrue("Not a DQL statement.", preparedStatement.execute());
        try (ResultSet resultSet = preparedStatement.getResultSet()) {
            assertResultSet(resultSet);
        }
    }
}
 
Example 12
@Override
public boolean sequenceExists(final String sequenceName) {
    ReturningWork<Boolean> work = new ReturningWork<Boolean>() {
        @Override
        public Boolean execute(Connection connection) throws SQLException {
            DialectResolver dialectResolver = new StandardDialectResolver();
            Dialect dialect = dialectResolver.resolveDialect(getResolutionInfo(connection));
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            try {
                preparedStatement = connection.prepareStatement(dialect.getQuerySequencesString());
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    if (sequenceName.equals(resultSet.getString(1))) {
                        return true;
                    }
                }
            } catch (SQLException e) {
                throw e;
            } finally {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
            }
            return false;

        }
    };

    Session session = (Session) entityManager.getDelegate();
    SessionFactory sessionFactory = session.getSessionFactory();
    return sessionFactory.getCurrentSession().doReturningWork(work);
}
 
Example 13
protected void insertToGfxdTable(Connection conn, int[] cid, String[] cust_name,
    Date[] since, String[] addr, long[] generatedCid, int[] count, int size) throws SQLException {
  PreparedStatement stmt = null;
  if (addGenIdCol) stmt =  conn.prepareStatement(insertGenId, Statement.RETURN_GENERATED_KEYS);
  else stmt =  conn.prepareStatement(insert, Statement.RETURN_GENERATED_KEYS);
  int tid = getMyTid();

  if (size != 1) throw new TestException("Should only insert 1 row in the test");
  for (int i=0 ; i<size ; i++) {
    count[i] = insertToTable(stmt, cid[i], cust_name[i],since[i], addr[i], generatedCid, tid); 
    Log.getLogWriter().info("gemfirexd -  inserts " + count[i] + " rows");
  }
}
 
Example 14
protected void insertToGFXDTable(Connection conn, int[] eid, String[] emp_name,
    int[] deptid, Date[] since, String[] addr, Blob[] picture, String[] ssn,
    int size, boolean isPut) throws SQLException {
  PreparedStatement stmt = conn.prepareStatement(isPut ? put : insert);
  int tid = getMyTid();
  
  for (int i=0 ; i<size ; i++) {
    try {
      insertToTable(stmt, eid[i], emp_name[i], deptid[i], since[i], addr[i], 
          picture[i], ssn[i], tid, isPut); 
    } catch (SQLException se) {
      SQLHelper.handleSQLException(se);      
    }
  }
}
 
Example 15
/**
 * @param identityProviderName
 * @param connectorType
 * @param provisioningEntity
 * @param tenantId
 * @throws IdentityApplicationManagementException
 */
public void deleteProvisioningEntity(String identityProviderName, String connectorType,
                                     ProvisioningEntity provisioningEntity, int tenantId)
        throws IdentityApplicationManagementException {

    Connection dbConnection = IdentityDatabaseUtil.getDBConnection();
    try {

        PreparedStatement prepStmt = null;

        // id of the identity provider
        int idpId = getIdentityProviderIdentifier(dbConnection, identityProviderName, tenantId);

        // id of the provisioning configuration
        int provisioningConfigId = getProvisioningConfigurationIdentifier(dbConnection, idpId,
                connectorType);

        // PROVISIONING_CONFIG_ID, ENTITY_TYPE,
        // ENTITY_LOCAL_USERSTORE, ENTITY_NAME, TENANT_ID
        String sqlStmt = IdentityProvisioningConstants.SQLQueries.DELETE_PROVISIONING_ENTITY_SQL;

        prepStmt = dbConnection.prepareStatement(sqlStmt);
        prepStmt.setInt(1, provisioningConfigId);
        prepStmt.setString(2, provisioningEntity.getEntityType().toString());
        prepStmt.setString(3, IdentityUtil.extractDomainFromName(provisioningEntity.getEntityName()));
        prepStmt.setString(4, UserCoreUtil.removeDomainFromName(provisioningEntity.getEntityName()));
        prepStmt.setInt(5, tenantId);

        prepStmt.execute();
        dbConnection.commit();
    } catch (SQLException e) {
        IdentityApplicationManagementUtil.rollBack(dbConnection);
        String msg = "Error occurred while deleting Provisioning entity for tenant " + tenantId;
        throw new IdentityApplicationManagementException(msg, e);
    } finally {
        IdentityApplicationManagementUtil.closeConnection(dbConnection);
    }
}
 
Example 16
Source Project: wind-im   File: SiteUICDao.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 查询UIC使用情况
 * 
 * @param uic
 * @return
 * @throws SQLException
 */
public UicBean queryUIC(String uic) throws SQLException {
	long startTime = System.currentTimeMillis();
	String sql = "SELECT uic,site_user_id,status,create_time,use_time FROM " + UIC_TABLE + " WHERE uic=?;";

	UicBean bean = null;
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	try {
		conn = DatabaseConnection.getSlaveConnection();

		pst = conn.prepareStatement(sql);
		pst.setString(1, uic);

		rs = pst.executeQuery();
		if (rs.next()) {
			bean = new UicBean();
			bean.setUic(rs.getString(1));
			bean.setSiteUserId(rs.getString(2));
			bean.setStatus(rs.getInt(3));
			bean.setCreateTime(rs.getLong(4));
			bean.setUseTime(rs.getLong(5));
		}
	} catch (Exception e) {
		throw e;
	} finally {
		DatabaseConnection.returnConnection(conn, pst, rs);
	}

	LogUtils.dbDebugLog(logger, startTime, bean, sql, uic);
	return bean;
}
 
Example 17
Source Project: phoenix   File: QueryMetaDataTest.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testInListParameterMetaData2() throws Exception {
    String query = "SELECT a_string, b_string FROM atable WHERE ? IN (2.2, 3)";
    Connection conn = DriverManager.getConnection(getUrl(), PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES));
    PreparedStatement statement = conn.prepareStatement(query);
    ParameterMetaData pmd = statement.getParameterMetaData();
    assertEquals(1, pmd.getParameterCount());
    assertEquals(BigDecimal.class.getName(), pmd.getParameterClassName(1));
}
 
Example 18
Source Project: phoenix   File: IndexExpressionIT.java    License: Apache License 2.0 4 votes vote down vote up
protected void helpTestUpdate(boolean localIndex) throws Exception {
    String dataTableName = MUTABLE_INDEX_DATA_TABLE;
    String fullDataTableName = INDEX_DATA_SCHEMA + QueryConstants.NAME_SEPARATOR + dataTableName;
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        conn.setAutoCommit(false);
        populateDataTable(conn, dataTableName);

        // create an expression index
        String ddl = "CREATE "
                + (localIndex ? "LOCAL" : "")
                + " INDEX IDX ON "
                + fullDataTableName
                + " ((UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2)),"
                + " (decimal_pk+int_pk+decimal_col2+int_col1)," + " date_pk+1, date1+1, date2+1 )"
                + " INCLUDE (long_col1, long_col2)";
        PreparedStatement stmt = conn.prepareStatement(ddl);
        stmt.execute();

        // update index pk column and covered column
        String upsert = "UPSERT INTO "
                + fullDataTableName
                + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk, varchar_col1, long_col1) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";

        stmt = conn.prepareStatement(upsert);
        stmt.setString(1, "varchar1");
        stmt.setString(2, "char1");
        stmt.setInt(3, 1);
        stmt.setLong(4, 1l);
        stmt.setBigDecimal(5, new BigDecimal(0.5));
        stmt.setDate(6, DateUtil.parseDate("2015-01-01 00:00:00"));
        stmt.setString(7, "a.varchar_updated");
        stmt.setLong(8, 101);
        stmt.executeUpdate();
        conn.commit();

        // verify only one row was updated in the data table
        String selectSql = "UPPER(varchar_pk) || '_' || UPPER(char_pk) || '_' || UPPER(varchar_col1) || '_' || UPPER(char_col2), long_col1 from "
                + fullDataTableName;
        ResultSet rs = conn.createStatement().executeQuery("SELECT /*+ NO_INDEX */ " + selectSql);
        assertTrue(rs.next());
        assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1   ", rs.getString(1));
        assertEquals(101, rs.getLong(2));
        assertTrue(rs.next());
        assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2   ", rs.getString(1));
        assertEquals(2, rs.getLong(2));
        assertFalse(rs.next());

        // verify that the rows in the index table are also updated
        rs = conn.createStatement().executeQuery("SELECT " + selectSql);
        assertTrue(rs.next());
        assertEquals("VARCHAR1_CHAR1 _A.VARCHAR_UPDATED_B.CHAR1   ", rs.getString(1));
        assertEquals(101, rs.getLong(2));
        assertTrue(rs.next());
        assertEquals("VARCHAR2_CHAR2 _A.VARCHAR2_B.CHAR2   ", rs.getString(1));
        assertEquals(2, rs.getLong(2));
        assertFalse(rs.next());
        conn.createStatement().execute("DROP INDEX IDX ON " + fullDataTableName);
    } finally {
        conn.close();
    }
}
 
Example 19
Source Project: gemfirexd-oss   File: GfxdLoaderDUnit.java    License: Apache License 2.0 4 votes vote down vote up
private void runTransactionalBehaviourOfCacheLoader_Bug42914(
    final boolean isReplicated) throws Exception {
  // Start one client and some servers
  startVMs(1, 3);

  // Create a schema
  clientSQLExecute(1, "create schema EMP");

  // Controller VM
  final String suffix = isReplicated ? "replicate" : "redundancy 1";
  String createTable = "create table EMP.TESTTABLE (ID int primary key, "
      + "DESCRIPTION varchar(1024) not null, ADDRESS varchar(1024), ID1 int) "
      + suffix;
  clientSQLExecute(1, createTable);
  GfxdCallbacksTest.addLoader("EMP", "TESTTABLE",
      "com.pivotal.gemfirexd.dbsync.DBSynchronizerTestBase$GfxdTestRowLoader",
      "");
  // Test insert propagation by inserting in a data store node of DS.DS0
  Connection conn = TestUtil.getConnection();
  conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
  conn.setAutoCommit(false);
  String query = "select * from emp.testtable where ID = ?";
  PreparedStatement ps = conn.prepareStatement(query);
  Statement stmt = conn.createStatement();
  ResultSet rs;

  for (int i = 0; i < 20; i++) {
    ps.setInt(1, i);
    rs = ps.executeQuery();
    rs.next();
    assertEquals(rs.getInt(1), i);
    assertFalse(rs.next());
  }
  sqlExecuteVerify(null, new int[] { 1, 2, 3 },
      "select count(*) from emp.testtable", null, "0");
  // rollback and check no data
  conn.rollback();
  rs = stmt.executeQuery("select * from emp.testtable");
  assertFalse(rs.next());
  sqlExecuteVerify(null, new int[] { 1, 2, 3 },
      "select count(*) from emp.testtable", null, "0");

  // now populate again and check successful loads
  for (int i = 0; i < 20; i++) {
    ps.setInt(1, i);
    rs = ps.executeQuery();
    rs.next();
    assertEquals(rs.getInt(1), i);
    assertFalse(rs.next());
  }
  sqlExecuteVerify(null, new int[] { 1, 2, 3 },
      "select count(*) from emp.testtable", null, "0");
  rs = stmt.executeQuery("select count(*) from emp.testtable");
  assertTrue(rs.next());
  assertEquals(20, rs.getInt(1));
  assertFalse(rs.next());
  conn.commit();
  conn.close();

  // now check commit of loaded data
  conn = TestUtil.getConnection();
  stmt = conn.createStatement();
  ps = conn.prepareStatement(query);

  rs = stmt.executeQuery("select id from emp.testtable");
  TreeSet<Integer> ids = new TreeSet<Integer>();
  for (int i = 0; i < 20; i++) {
    assertTrue("failed next for i=" + i, rs.next());
    ids.add(Integer.valueOf(rs.getInt(1)));
  }
  assertFalse(rs.next());
  assertEquals(20, ids.size());
  assertEquals(0, ids.first().intValue());
  assertEquals(19, ids.last().intValue());

  for (int i = 0; i < 20; i++) {
    ps.setInt(1, i);
    rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals(i, rs.getInt(1));
    assertFalse(rs.next());
  }
  rs = stmt.executeQuery("select count(*) from emp.testtable");
  assertTrue(rs.next());
  assertEquals(20, rs.getInt(1));
  assertFalse(rs.next());
  sqlExecuteVerify(null, new int[] { 1, 2, 3 },
      "select count(*) from emp.testtable", null, "20");
}
 
Example 20
Source Project: tddl5   File: DemoRepoSample.java    License: Apache License 2.0 4 votes vote down vote up
public static void main(String[] args) throws TddlException, SQLException {

        TDataSource ds = new TDataSource();

        // init a datasource with local config file
        ds.setAppName("tddl5_sample");
        ds.setRuleFile("classpath:sample_rule.xml");
        ds.setTopologyFile("sample_topology.xml");
        ds.setSchemaFile("demo_repo_schema.xml");
        ds.init();

        System.out.println("init done");

        Connection conn = ds.getConnection();

        // insert a record
        conn.prepareStatement("replace into _tddl_ (id,name) values (1,'sun1')").executeUpdate();
        conn.prepareStatement("replace into _tddl_ (id,name) values (2,'sun1')").executeUpdate();

        conn.prepareStatement("replace into _tddl_ (id,name) values (3,'sun1')").executeUpdate();

        conn.prepareStatement("replace into _tddl_ (id,name) values (4,'sun2')").executeUpdate();

        conn.prepareStatement("replace into _tddl_ (id,name) values (5,'sun2')").executeUpdate();

        System.out.println("insert done");

        // select all records
        PreparedStatement ps = conn.prepareStatement("SELECT id from _tddl_ order by id");
        // PreparedStatement ps =
        // conn.prepareStatement("SELECT * from _tddl_ t1 join _tddl_ t2 where t1.name=t2.name and t2.name='sun1' and t1.id=1");

        // PreparedStatement ps =
        // conn.prepareStatement("SELECT * from _tddl_ t1 where name='sun'");

        // PreparedStatement ps =
        // conn.prepareStatement("SELECT count(*)+1 from _tddl_ t1 where t1.id=1");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            StringBuilder sb = new StringBuilder();
            int count = rs.getMetaData().getColumnCount();
            for (int i = 1; i <= count; i++) {

                String key = rs.getMetaData().getColumnLabel(i);
                Object val = rs.getObject(i);
                sb.append("[" + rs.getMetaData().getTableName(i) + "." + key + "->" + val + "]");
            }
            System.out.println(sb.toString());
        }

        rs.close();
        ps.close();
        conn.close();

        System.out.println("query done");
    }