protected boolean test(Statement aStatement) {

    try {
      try {

        // execute the SQL
        aStatement.execute(getSql());
      } catch (SQLException s) {
        throw new Exception(
            "Expected an update count of "
                + getCountWeWant()
                + ", but got the error: "
                + s.getMessage());
      }

      if (aStatement.getUpdateCount() != getCountWeWant()) {
        throw new Exception(
            "Expected an update count of "
                + getCountWeWant()
                + ", but got an update count of "
                + aStatement.getUpdateCount()
                + ".");
      }
    } catch (Exception x) {
      message = x.getMessage();

      return false;
    }

    return true;
  }
  protected boolean test(Statement aStatement) {

    try {

      // execute the SQL
      try {
        aStatement.execute(getSql());
      } catch (SQLException s) {
        throw new Exception(
            "Expected a ResultSet containing "
                + getCountWeWant()
                + " rows, but got the error: "
                + s.getMessage());
      }

      // check that update count != -1
      if (aStatement.getUpdateCount() != -1) {
        throw new Exception(
            "Expected a ResultSet, but got an update count of " + aStatement.getUpdateCount());
      }

      // iterate over the ResultSet
      ResultSet results = aStatement.getResultSet();
      int count = 0;

      while (results.next()) {
        count++;
      }

      // check that we got as many rows as expected
      if (count != getCountWeWant()) {

        // we don't have the expected number of rows
        throw new Exception(
            "Expected the ResultSet to contain "
                + getCountWeWant()
                + " rows, but it contained "
                + count
                + " rows.");
      }
    } catch (Exception x) {
      message = x.getMessage();

      return false;
    }

    return true;
  }
  /*
   * 执行sql语句
   */
  public boolean changeDatabase(String sql) {
    boolean flag = false;
    Connection conn = commonDao.getSqlSession().getConnection();
    Statement st = null;

    if (conn == null) {
      logger.error("获取数据库连接失败 !");
      return false;
    }
    try {
      st = conn.createStatement();
      st.execute(sql);
      int num = st.getUpdateCount();
      flag = true;
      logger.info("更新记录数 : " + num);
    } catch (SQLException e) {

      logger.error(e.getMessage());
    } finally {
      try {
        if (st != null) {
          st.close();
        }

      } catch (SQLException e1) {
        Log.error(e1.getMessage());
      }
    }
    return flag;
  }
Esempio n. 4
0
  private void outputResult(ResultSet ergeb, Date start, Statement st) throws Exception {
    Date ende;
    if (ergeb == null) {
      ende = new Date();
      long diff = ende.getTime() - start.getTime();

      int numOfUpdates = st.getUpdateCount();
      System.out.println();
      System.out.println("Number of affected Rows: " + numOfUpdates);
      System.out.println("Duration: " + diff + " msec");
      return;
    } else {

      int counter = 0;
      boolean ret = true;
      StringBuffer sb1 = new StringBuffer();

      for (int i = 1; i <= ergeb.getMetaData().getColumnCount(); i++) {
        String ColName = ergeb.getMetaData().getColumnName(i);
        int width = ergeb.getMetaData().getColumnDisplaySize(i);
        sb1.append(ColName);
        for (int x = 0; x < (width - ColName.length()); x++) sb1.append(" ");
        sb1.append(" | ");
      }
      System.out.println(sb1);
      StringBuffer sb2 = new StringBuffer();
      for (int i = 0; i < sb1.length() - 1; i++) {
        sb2.append("-");
      }
      System.out.println(sb2);

      while (ret == true) {
        if (ergeb.next()) {

          counter++;
          StringBuffer sb = new StringBuffer();

          for (int i = 1; i <= ergeb.getMetaData().getColumnCount(); i++) {
            int width = ergeb.getMetaData().getColumnDisplaySize(i);

            String temp = ergeb.getString(i);
            if (ergeb.wasNull()) {
              // Null values are displayes as -NULL-
              temp = new String("-NULL-");
            }
            sb.append(temp);

            for (int x = 0; x < (width - temp.length()); x++) sb.append(" ");
            sb.append(" | ");
          }
          System.out.println(sb.toString());
        } else {
          ende = new Date();
          long diff = ende.getTime() - start.getTime();
          System.out.println("\nReturning: " + counter + " Row(s) in " + diff + " msec");
          ret = false;
        }
      }
    }
  }
  private static CurVO executeBatchNoBindSQL(String sql, List voList, Connection connect)
      throws Exception {
    CurVO resultVO;
    if (voList == null || voList.size() == 0) {
      resultVO = new CurVO();
      resultVO.setBatchBesult(new int[0]);
    }
    Statement stm = null;

    try {
      stm = connect.createStatement();
      for (int i = 0; i < voList.size(); i++) {
        // P.print(DBAssistantTools.GetJointSQL(sql, voList.get(i),
        // null, false));
        /** ---------------------打印------------------------- */
        P.printSQL(sql, voList.get(i));
        /** ---------------------打印------------------------- */
        // 添加SQL字符串
        stm.addBatch(SqlTool.GetJointSQL(sql, voList.get(i), null, false));
      }
      // 该数组可查看每次SQL处理的整形结果
      resultVO = new CurVO();
      resultVO.setBatchBesult(stm.executeBatch());
      resultVO.setResult(stm.getUpdateCount());
      return resultVO;
    } catch (Exception e) {
      throw e;
    } finally {
      DBTool.close(null, stm, null);
    }
  }
Esempio n. 6
0
  public static ResultSet executeSql(final Statement statement, final String sql) {
    ResultSet results = null;
    if (statement == null) {
      return results;
    }
    if (isBlank(sql)) {
      LOGGER.log(Level.FINE, "No SQL provided", new RuntimeException());
      return results;
    }

    try {
      statement.clearWarnings();

      final boolean hasResults = statement.execute(sql);
      if (hasResults) {
        results = statement.getResultSet();
      } else {
        final int updateCount = statement.getUpdateCount();
        LOGGER.log(
            Level.FINE,
            String.format("No results. Update count of %d for query: %s", updateCount, sql));
      }

      SQLWarning sqlWarning = statement.getWarnings();
      while (sqlWarning != null) {
        LOGGER.log(Level.INFO, sqlWarning.getMessage(), sqlWarning);
        sqlWarning = sqlWarning.getNextWarning();
      }

      return results;
    } catch (final SQLException e) {
      LOGGER.log(Level.WARNING, "Error executing: " + sql, e);
      return null;
    }
  }
  /**
   * load the temp file maintained by the MySQLbulkLoader into the DMBS. truncates the temp file,
   * and leaves it open for more insertRecord() operations. returns number of records inserted.
   *
   * <p>TODO: perhaps instead of having each program that uses a DAO that uses bulk loading call
   * 'completeInsert', get MySQLbulkLoader created by a factory, and have the factory remember to
   * load all the tables from all the temp files before the program exits.
   *
   * @return number of records inserted
   * @throws DaoException
   * @throws IOException
   */
  public int loadDataFromTempFileIntoDBMS() throws DaoException, IOException {
    Connection con = null;
    Statement stmt = null;

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      try {
        // close the file, flushing all buffers before loading the DBMS
        tempFileWriter.close();
      } catch (IOException e) {
        throw new DaoException(e);
      }

      con = JdbcUtil.getDbConnection(MySQLbulkLoader.class);
      stmt = con.createStatement();

      // will throw error if attempts to overwrite primary keys in table
      String command = "LOAD DATA LOCAL INFILE '" + tempFileName + "' INTO TABLE " + tableName;
      long startTime = System.currentTimeMillis();
      boolean rv = stmt.execute(command);
      // TODO: throw exception if rv == true
      int updateCount = stmt.getUpdateCount();
      long duration = (System.currentTimeMillis() - startTime) / 1000;

      // reopen empty temp file
      this.tempFileWriter = new BufferedWriter(new FileWriter(this.tempFileHandle, false));
      return updateCount;

    } catch (SQLException e) {
      throw new DaoException(e);
    } finally {
      JdbcUtil.closeAll(MySQLbulkLoader.class, con, pstmt, rs);
    }
  }
Esempio n. 8
0
 private boolean moveToNextResultsIfPresent(StatementScope scope, Statement stmt)
     throws SQLException {
   boolean moreResults;
   // This is the messed up JDBC approach for determining if there are more results
   moreResults =
       !(((moveToNextResultsSafely(scope, stmt) == false) && (stmt.getUpdateCount() == -1)));
   return moreResults;
 }
  /** Method declaration Adjust this method for large strings...ie multi megabtypes. */
  void execute() {

    String sCmd = null;

    if (4096 <= ifHuge.length()) {
      sCmd = ifHuge;
    } else {
      sCmd = txtCommand.getText();
    }

    if (sCmd.startsWith("-->>>TEST<<<--")) {
      testPerformance();

      return;
    }

    String g[] = new String[1];

    lTime = System.currentTimeMillis();

    try {
      sStatement.execute(sCmd);

      lTime = System.currentTimeMillis() - lTime;

      int r = sStatement.getUpdateCount();

      if (r == -1) {
        formatResultSet(sStatement.getResultSet());
      } else {
        g[0] = "update count";

        gResult.setHead(g);

        g[0] = String.valueOf(r);

        gResult.addRow(g);
      }

      addToRecent(txtCommand.getText());
    } catch (SQLException e) {
      lTime = System.currentTimeMillis() - lTime;
      g[0] = "SQL Error";

      gResult.setHead(g);

      String s = e.getMessage();

      s += " / Error Code: " + e.getErrorCode();
      s += " / State: " + e.getSQLState();
      g[0] = s;

      gResult.addRow(g);
    }

    updateResult();
    System.gc();
  }
  /**
   * Updates entities in the table, using the setClause for setting values and whereClause to
   * restrict rows.
   *
   * @param table the table
   * @param setClause the set clause
   * @param whereClause the where clause
   * @return the count of succesfully modified entries
   * @throws SQLException the sQL exception
   */
  public static int updateEntitiesCount(String table, String setClause, String whereClause)
      throws SQLException {
    openConnection();

    String expression = "UPDATE " + table + " SET " + setClause + " WHERE " + whereClause;

    System.out.println("S-a apelat expresia SQL \'" + expression + "\'");
    statement.execute(expression);

    return statement.getUpdateCount();
  }
Esempio n. 11
0
  public void executeSql(String sql) throws Exception {

    hasUploaded = false;

    try {
      Class.forName(driver);
      con = DriverManager.getConnection(url, username, password);
      if (null == con) {
        System.out.println("DATABASE con=" + con);
        return;
      }

      stmt = con.createStatement();
      boolean hasResultSet = stmt.execute(sql);
      if (hasResultSet) {
        rs = stmt.getResultSet();
        java.sql.ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();

        while (rs.next()) {
          for (int i = 0; i < columnCount; i++) {
            System.out.print(rs.getString(i + 1) + "\t");
          }
          System.out.println("NEXT");
        }
      } else {

        System.out.println("改SQL语句影响的记录有" + stmt.getUpdateCount() + "条");
        // System.out.println("change sql server records : " +
        // stmt.getUpdateCount());
        // Toast.makeText(ResultActivity.this, "改SQL语句影响的记录有" +
        // stmt.getUpdateCount() + "条", Toast.LENGTH_SHORT).show();
        hasUploaded = true;

        if (!last_upload) {
          ((BurnApp) getApplication()).showNotify("已上传老化数据,条数:" + (++upload_cnt));
        } else {
          ((BurnApp) getApplication()).showNotify("已老化完毕,条数:" + (++upload_cnt));
        }
      }
    } finally {
      if (rs != null) {
        rs.close();
      }
      if (stmt != null) {
        stmt.close();
      }
      if (con != null) {
        con.close();
      }
    }
  }
Esempio n. 12
0
  /** Local Statement insert */
  @Test
  public void testInsert3() throws Exception {
    Connection connection = makeConnection(new ArrayList<JdbcTest.Employee>());
    String sql = "insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)";

    Statement statement = connection.createStatement();
    boolean status = statement.execute(sql);
    assertFalse(status);
    ResultSet resultSet = statement.getResultSet();
    assertTrue(resultSet == null);
    int updateCount = statement.getUpdateCount();
    assertTrue(updateCount == 1);
  }
 public int execute(String sql) throws SQLException {
   String mySql = rewriteSql(sql);
   int res = -2;
   Statement statement = connection.createStatement();
   try {
     boolean resultType = statement.execute(mySql);
     if (!resultType) {
       res = statement.getUpdateCount();
     }
   } finally {
     statement.close();
   }
   return res;
 }
 private ResultSetWrapper getNextResultSet(Statement stmt) throws SQLException {
   // Making this method tolerant of bad JDBC drivers
   try {
     if (stmt.getConnection().getMetaData().supportsMultipleResultSets()) {
       // Crazy Standard JDBC way of determining if there are more results
       if (!((!stmt.getMoreResults()) && (stmt.getUpdateCount() == -1))) {
         ResultSet rs = stmt.getResultSet();
         return rs != null ? new ResultSetWrapper(rs, configuration) : null;
       }
     }
   } catch (Exception e) {
     // Intentionally ignored.
   }
   return null;
 }
Esempio n. 15
0
 public boolean chgStadiumLocation(int sid, String newCity) {
   try {
     String sqlText;
     sql = connection.createStatement();
     sqlText = "UPDATE stadium SET city = '" + newCity + "' WHERE sid =" + sid;
     sql.executeUpdate(sqlText);
     if (sql.getUpdateCount() > 0) {
       return true;
     } else {
       return false;
     }
   } catch (SQLException e) {
     return false;
   }
 }
Esempio n. 16
0
 public boolean deleteCountry(int cid) {
   String sqlText;
   try {
     sql = connection.createStatement();
     sqlText = "DELETE FROM country WHERE cid = " + cid;
     sql.executeUpdate(sqlText);
     if (sql.getUpdateCount() > 0) {
       return true;
     } else {
       return false;
     }
   } catch (SQLException e) {
     return false;
   }
 }
 private ResultSetWrapper getFirstResultSet(Statement stmt) throws SQLException {
   ResultSet rs = stmt.getResultSet();
   while (rs == null) {
     // move forward to get the first resultset in case the driver
     // doesn't return the resultset as the first result (HSQLDB 2.1)
     if (stmt.getMoreResults()) {
       rs = stmt.getResultSet();
     } else {
       if (stmt.getUpdateCount() == -1) {
         // no more results. Must be no resultset
         break;
       }
     }
   }
   return rs != null ? new ResultSetWrapper(rs, configuration) : null;
 }
Esempio n. 18
0
  public final int write(final String sql) {
    final int count;

    try {
      final Statement stmt = getConnection().createStatement();
      stmt.execute(sql);

      count = stmt.getUpdateCount();

      stmt.close();
    } catch (SQLException e) {
      throw new IllegalStateException(e);
    }

    return count;
  }
  /** Test remote Statement insert. */
  @Test
  public void testInsert() throws Exception {
    final Connection connection =
        DriverManager.getConnection(
            "jdbc:avatica:remote:factory=" + LocalServiceModifiableFactory.class.getName());
    assertThat(connection.isClosed(), is(false));
    Statement statement = connection.createStatement();
    assertThat(statement.isClosed(), is(false));

    String sql = "insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)";
    boolean status = statement.execute(sql);
    assertThat(status, is(false));
    ResultSet resultSet = statement.getResultSet();
    assertThat(resultSet, nullValue());
    int updateCount = statement.getUpdateCount();
    assertThat(updateCount, is(1));
  }
 public Record[] buildCommonResult(Statement s) throws SQLException {
   Record[] results = new Record[1];
   results[0] = new Record(1);
   int num;
   String str = "Rows Affected";
   switch (num = s.getUpdateCount()) {
     case 0:
       results[0].put(str, "No rows affected");
       break;
     case 1:
       results[0].put(str, "1 row affected");
       break;
     default:
       results[0].put(str, num + " rows affected");
   }
   return results;
 }
Esempio n. 21
0
  @Override
  public void execute() throws TranslatorException {
    String sourceSQL = (String) this.arguments.get(0).getArgumentValue().getValue();
    List<Argument> parameters = this.arguments.subList(1, this.arguments.size());

    LogManager.logTrace(LogConstants.CTX_CONNECTOR, "Source sql", sourceSQL); // $NON-NLS-1$
    int paramCount = parameters.size();

    try {
      Statement stmt;
      boolean hasResults = false;

      if (paramCount > 0) {
        PreparedStatement pstatement = getPreparedStatement(sourceSQL);
        for (int i = 0; i < paramCount; i++) {
          Argument arg = parameters.get(i);
          // TODO: if ParameterMetadata is supported we could use that type
          this.executionFactory.bindValue(
              pstatement,
              arg.getArgumentValue().getValue(),
              arg.getArgumentValue().getType(),
              i + 1);
        }
        stmt = pstatement;
        hasResults = pstatement.execute();
      } else {
        // TODO: when array support becomes more robust calling like "exec native('sql', ARRAY[])
        // could still be prepared
        stmt = getStatement();
        hasResults = stmt.execute(sourceSQL);
      }

      if (hasResults) {
        this.results = stmt.getResultSet();
        this.columnCount = this.results.getMetaData().getColumnCount();
      } else {
        this.updateCount = stmt.getUpdateCount();
      }
      addStatementWarnings();
    } catch (SQLException e) {
      throw new JDBCExecutionException(JDBCPlugin.Event.TEIID11008, e, sourceSQL);
    }
  }
Esempio n. 22
0
 private void runQuery(String query) {
   Connection connection = null;
   Statement stmt = null;
   try {
     connection = dataSource.getConnection();
     stmt = connection.createStatement();
     if (stmt.execute(query)) {
       ResultSet rset = stmt.getResultSet();
       int count = 0;
       while (rset.next()) {
         count++;
       }
       LOGGER.info("QUERY(" + query + ") produced unused resultset with " + count + " rows");
     } else {
       int updateCount = stmt.getUpdateCount();
       LOGGER.info("QUERY(" + query + ") Update count: " + updateCount);
     }
     connection.commit();
   } catch (SQLException ex) {
     try {
       connection.rollback();
     } catch (SQLException ex2) {
       LOGGER.error("Unable to rollback transaction", ex2);
     }
     throw new JdbcChannelException("Unable to run query: " + query, ex);
   } finally {
     if (stmt != null) {
       try {
         stmt.close();
       } catch (SQLException ex) {
         LOGGER.error("Unable to close statement", ex);
       }
       if (connection != null) {
         try {
           connection.close();
         } catch (SQLException ex) {
           LOGGER.error("Unable to close connection", ex);
         }
       }
     }
   }
 }
Esempio n. 23
0
  private void _runStatement(Connection conn, DaoStatement st) throws SQLException {
    boolean statIsClosed = false;
    Statement stat = null;
    String sql = st.toPreparedStatement();

    // 打印调试信息
    if (log.isDebugEnabled()) log.debug(sql);

    try {
      stat = conn.createStatement();
      stat.execute(sql);
      st.getContext().setUpdateCount(stat.getUpdateCount());
      stat.close();
      statIsClosed = true;
    } finally {
      if (!statIsClosed) Daos.safeClose(stat);
    }
    // 打印更详细的调试信息
    if (log.isTraceEnabled()) log.trace("...DONE");
  }
Esempio n. 24
0
 public boolean updateValues(String cname, int incrV) {
   String sqlText;
   try {
     sql = connection.createStatement();
     sqlText = "SELECT DISTINCT cid FROM country WHERE name = '" + cname + "'";
     rs = sql.executeQuery(sqlText);
     if (!rs.isBeforeFirst()) {
       return false;
     }
     rs.next();
     sqlText = "UPDATE player SET value = value + " + incrV + " WHERE  cid = " + rs.getInt("cid");
     sql.executeUpdate(sqlText);
     if (sql.getUpdateCount() > 0) {
       return true;
     } else {
       return false;
     }
   } catch (SQLException e) {
     return false;
   }
 }
  private static void indent_DisplayResults(
      PrintStream out,
      Statement stmt,
      Connection conn,
      int indentLevel,
      int[] displayColumns,
      int[] displayColumnWidths)
      throws SQLException {

    checkNotNull(stmt, "Statement");

    ResultSet rs = stmt.getResultSet();
    if (rs != null) {
      indent_DisplayResults(out, rs, conn, indentLevel, displayColumns, displayColumnWidths);
      rs.close(); // let the result set go away
    } else {
      DisplayUpdateCount(out, stmt.getUpdateCount(), indentLevel);
    }

    ShowWarnings(out, stmt);
  } // DisplayResults
Esempio n. 26
0
  public static void main(String args[]) throws IOException {
    try {
      Scanner in = args.length == 0 ? new Scanner(System.in) : new Scanner(Paths.get(args[0]));

      try (Connection conn = getConnection()) {
        Statement stat = conn.createStatement();

        while (true) {
          if (args.length == 0) System.out.println("Enter command or EXIT to exit:");

          if (!in.hasNextLine()) return;

          String line = in.nextLine();
          if (line.equalsIgnoreCase("EXIT")) return;
          if (line.trim().endsWith(";")) // remove trailing semicolon
          {
            line = line.trim();
            line = line.substring(0, line.length() - 1);
          }
          try {
            boolean isResult = stat.execute(line);
            if (isResult) {
              ResultSet rs = stat.getResultSet();
              showResultSet(rs);
            } else {
              int updateCount = stat.getUpdateCount();
              System.out.println(updateCount + " rows updated");
            }
          } catch (SQLException ex) {
            for (Throwable e : ex) e.printStackTrace();
          }
        }
      }
    } catch (SQLException e) {
      for (Throwable t : e) t.printStackTrace();
    }
  }
Esempio n. 27
0
  private QueryResultView execSQL(final Statement stmt, String sql, MessageObserver messageObserver)
      throws SQLException {
    ResultSet rslt = null;

    String[] sqlArray = splitSql(sql);
    // TODO 複数のSQLに対応したい。
    for (String s : sqlArray) {
      System.out.println("**TEST**" + s);
    }
    System.out.println("DEBUG:" + sql);
    if (stmt.execute(sql)) {
      System.gc();
      long start = System.currentTimeMillis();
      rslt = stmt.getResultSet();
      long sqlExectTime = System.currentTimeMillis() - start;
      try {
        return QueryResultViewImpl.parseViewInfo(rslt, sqlExectTime);
      } finally {
        rslt.close();
        if (messageObserver != null) {
          messageObserver.setMessage("検索実行");
        }
      }
    } else {
      if (messageObserver != null) {
        messageObserver.setMessage("更新実行");
      }

      System.gc();
      long start = System.currentTimeMillis();
      int updateCount = stmt.getUpdateCount();
      long sqlExectTime = System.currentTimeMillis() - start;

      return new NullViewInfo("更新実行", updateCount, sqlExectTime);
    }
  }
  @Test
  public void testCreateInsertUpdateDrop() throws Exception {
    final String drop = "drop table TEST_TABLE if exists";
    final String create =
        "create table TEST_TABLE(" + "id int not null, " + "msg varchar(3) not null)";
    final String insert = "insert into TEST_TABLE values(1, 'foo')";
    final String update = "update TEST_TABLE set msg='bar' where id=1";
    try (Connection connection = ljs();
        Statement statement = connection.createStatement();
        PreparedStatement pstmt = connection.prepareStatement("values 1")) {
      // drop
      assertFalse(statement.execute(drop));
      assertEquals(0, statement.getUpdateCount());
      assertNull(statement.getResultSet());
      try {
        final ResultSet rs = statement.executeQuery(drop);
        fail("expected error, got " + rs);
      } catch (SQLException e) {
        assertThat(e.getMessage(), equalTo("Statement did not return a result set"));
      }
      assertEquals(0, statement.executeUpdate(drop));
      assertEquals(0, statement.getUpdateCount());
      assertNull(statement.getResultSet());

      // create
      assertFalse(statement.execute(create));
      assertEquals(0, statement.getUpdateCount());
      assertNull(statement.getResultSet());
      assertFalse(statement.execute(drop)); // tidy up
      try {
        final ResultSet rs = statement.executeQuery(create);
        fail("expected error, got " + rs);
      } catch (SQLException e) {
        assertThat(e.getMessage(), equalTo("Statement did not return a result set"));
      }
      assertFalse(statement.execute(drop)); // tidy up
      assertEquals(0, statement.executeUpdate(create));
      assertEquals(0, statement.getUpdateCount());
      assertNull(statement.getResultSet());

      // insert
      assertFalse(statement.execute(insert));
      assertEquals(1, statement.getUpdateCount());
      assertNull(statement.getResultSet());
      try {
        final ResultSet rs = statement.executeQuery(insert);
        fail("expected error, got " + rs);
      } catch (SQLException e) {
        assertThat(e.getMessage(), equalTo("Statement did not return a result set"));
      }
      assertEquals(1, statement.executeUpdate(insert));
      assertEquals(1, statement.getUpdateCount());
      assertNull(statement.getResultSet());

      // update
      assertFalse(statement.execute(update));
      assertEquals(3, statement.getUpdateCount());
      assertNull(statement.getResultSet());
      try {
        final ResultSet rs = statement.executeQuery(update);
        fail("expected error, got " + rs);
      } catch (SQLException e) {
        assertThat(e.getMessage(), equalTo("Statement did not return a result set"));
      }
      assertEquals(3, statement.executeUpdate(update));
      assertEquals(3, statement.getUpdateCount());
      assertNull(statement.getResultSet());

      final String[] messages = {
        "Cannot call executeQuery(String) on prepared or callable statement",
        "Cannot call execute(String) on prepared or callable statement",
        "Cannot call executeUpdate(String) on prepared or callable statement",
      };
      for (String sql : new String[] {drop, create, insert, update}) {
        for (int i = 0; i <= 2; i++) {
          try {
            Object o;
            switch (i) {
              case 0:
                o = pstmt.executeQuery(sql);
                break;
              case 1:
                o = pstmt.execute(sql);
                break;
              default:
                o = pstmt.executeUpdate(sql);
            }
            fail("expected error, got " + o);
          } catch (SQLException e) {
            assertThat(e.getMessage(), equalTo(messages[i]));
          }
        }
      }
    }
  }
  /**
   * Execute the given SQL script.
   *
   * <p>Statement separators and comments will be removed before executing individual statements
   * within the supplied script.
   *
   * <p><b>Do not use this method to execute DDL if you expect rollback.</b>
   *
   * @param connection the JDBC connection to use to execute the script; already configured and
   *     ready to use
   * @param resource the resource (potentially associated with a specific encoding) to load the SQL
   *     script from
   * @param continueOnError whether or not to continue without throwing an exception in the event of
   *     an error
   * @param ignoreFailedDrops whether or not to continue in the event of specifically an error on a
   *     {@code DROP} statement
   * @param commentPrefix the prefix that identifies comments in the SQL script &mdash; typically
   *     "--"
   * @param separator the script statement separator; defaults to {@value
   *     #DEFAULT_STATEMENT_SEPARATOR} if not specified
   * @param blockCommentStartDelimiter the <em>start</em> block comment delimiter; never {@code
   *     null} or empty
   * @param blockCommentEndDelimiter the <em>end</em> block comment delimiter; never {@code null} or
   *     empty
   */
  public static void executeSqlScript(
      Connection connection,
      EncodedResource resource,
      boolean continueOnError,
      boolean ignoreFailedDrops,
      String commentPrefix,
      String separator,
      String blockCommentStartDelimiter,
      String blockCommentEndDelimiter)
      throws SQLException, ScriptException {

    if (logger.isInfoEnabled()) {
      logger.info("Executing SQL script from " + resource);
    }
    long startTime = System.currentTimeMillis();
    List<String> statements = new LinkedList<String>();
    String script;
    try {
      script = readScript(resource, commentPrefix, separator);
    } catch (IOException ex) {
      throw new CannotReadScriptException(resource, ex);
    }

    if (separator == null) {
      separator = DEFAULT_STATEMENT_SEPARATOR;
      if (!containsSqlScriptDelimiters(script, separator)) {
        separator = "\n";
      }
    }

    splitSqlScript(
        resource,
        script,
        separator,
        commentPrefix,
        blockCommentStartDelimiter,
        blockCommentEndDelimiter,
        statements);
    int lineNumber = 0;
    Statement stmt = connection.createStatement();
    try {
      for (String statement : statements) {
        lineNumber++;
        try {
          stmt.execute(statement);
          int rowsAffected = stmt.getUpdateCount();
          if (logger.isDebugEnabled()) {
            logger.debug(rowsAffected + " returned as updateCount for SQL: " + statement);
          }
        } catch (SQLException ex) {
          boolean dropStatement = StringUtils.startsWithIgnoreCase(statement.trim(), "drop");
          if (continueOnError || (dropStatement && ignoreFailedDrops)) {
            if (logger.isDebugEnabled()) {
              logger.debug(
                  "Failed to execute SQL script statement at line "
                      + lineNumber
                      + " of resource "
                      + resource
                      + ": "
                      + statement,
                  ex);
            }
          } else {
            throw new ScriptStatementFailedException(statement, lineNumber, resource, ex);
          }
        }
      }
    } finally {
      try {
        stmt.close();
      } catch (Throwable ex) {
        logger.debug("Could not close JDBC Statement", ex);
      }
    }

    long elapsedTime = System.currentTimeMillis() - startTime;
    if (logger.isInfoEnabled()) {
      logger.info("Executed SQL script from " + resource + " in " + elapsedTime + " ms.");
    }
  }
Esempio n. 30
0
 @Override
 public int getUpdateCount() throws SQLException {
   return stat.getUpdateCount();
 }