public void testBoolean() throws Throwable {
   // String crtab = "create table #testBigInt (a bigint)";
   String crtab = "create table #testBit (a BIT NULL)";
   dropTable("#testBit");
   Statement stmt = con.createStatement();
   stmt.executeUpdate(crtab);
   stmt.executeUpdate("insert into #testBit values (NULL)");
   stmt.executeUpdate("insert into #testBit values (0)");
   stmt.executeUpdate("insert into #testBit values (1)");
   ResultSet rs = stmt.executeQuery("select * from #testBit where a is NULL");
   rs.next();
   rs.getBoolean(1);
   rs = stmt.executeQuery("select * from #testBit where a  = 0");
   rs.next();
   rs.getBoolean(1);
   rs = stmt.executeQuery("select * from #testBit where a = 1");
   rs.next();
   rs.getBoolean(1);
   stmt.close();
   PreparedStatement pstmt = con.prepareStatement("insert into #testBit values (?)");
   pstmt.setBoolean(1, true);
   assertTrue(!pstmt.execute());
   assertTrue(pstmt.getUpdateCount() == 1);
   pstmt.setBoolean(1, false);
   assertTrue(!pstmt.execute());
   assertTrue(pstmt.getUpdateCount() == 1);
   pstmt.setNull(1, java.sql.Types.BIT);
   assertTrue(!pstmt.execute());
   assertTrue(pstmt.getUpdateCount() == 1);
   pstmt.close();
 }
Example #2
0
 @Override
 public void traceMarker() throws Exception {
   PreparedStatement preparedStatement =
       connection.prepareStatement("select * from employee where name like ?");
   try {
     // pull back 0 records
     preparedStatement.setString(1, "nomatch%");
     preparedStatement.execute();
     ResultSet rs = preparedStatement.getResultSet();
     rs.next();
     // disable plugin and re-execute same prepared statement
     services.setPluginEnabled(PLUGIN_ID, false);
     preparedStatement.setString(1, "john%");
     preparedStatement.execute();
     // re-enable plugin and iterate over 1 record to make sure that these records are
     // not attributed to the previous execution
     services.setPluginEnabled(PLUGIN_ID, true);
     rs = preparedStatement.getResultSet();
     rs.next();
     rs.next();
     rs.next();
     rs.next();
   } finally {
     preparedStatement.close();
   }
 }
Example #3
0
  /**
   * @param conn
   * @param fingerprint
   * @param sampleID
   * @return
   * @throws SQLException
   */
  private static void insertSampleSet(Connection conn, Fingerprint fingerprint, Integer sampleID)
      throws SQLException {
    if (fingerprint.getSampleSetID() == null) {
      /*
       * Insert whole new SampleSetID.
       */
      String query = "INSERT INTO `SampleSets`(`SampleID`) VALUES(?);";
      PreparedStatement insertSampleSet =
          conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);

      insertSampleSet.setInt(1, sampleID);
      insertSampleSet.execute();

      ResultSet rs = insertSampleSet.getGeneratedKeys();
      if (rs.next()) {
        fingerprint.setSampleSetID(rs.getInt(1));
      }
      rs.close();
      insertSampleSet.close();
    } else {
      /*
       * Insert new SampleID for existing SampleSetID.
       */
      String query = "INSERT INTO `SampleSets`(`SampleSetID`,`SampleID`) VALUES(?, ?);";
      PreparedStatement insertSampleSet = conn.prepareStatement(query);

      insertSampleSet.setInt(1, fingerprint.getSampleSetID());
      insertSampleSet.setInt(2, sampleID);
      insertSampleSet.execute();

      insertSampleSet.close();
    }
  }
  /**
   * @param date
   * @param vipType 黄钻为0,红钻为1
   * @param type 活跃用户为0,新增用户为1
   * @param list
   * @throws SQLException
   */
  private void saveRecord(Date date, int vipType, int type, int[][] list) throws SQLException {
    PreparedStatement pstmt;
    for (int isYearVip = 0; isYearVip < 2; isYearVip++) {
      for (int vipLevel = 0; vipLevel < 8; vipLevel++) {

        pstmt =
            con.prepareStatement(
                "delete from vip_level where date=? and type=? and is_year_vip=? and vip_level=? and vip_type=?");
        pstmt.setDate(1, new java.sql.Date(date.getTime()));
        pstmt.setInt(2, type);
        pstmt.setInt(3, isYearVip);
        pstmt.setInt(4, vipLevel);
        pstmt.setInt(5, vipType);
        pstmt.execute();
        pstmt.close();

        pstmt =
            con.prepareStatement(
                "insert into vip_level(date,type,is_year_vip,vip_level,vip_type,count) values(?,?,?,?,?,?)");
        pstmt.setDate(1, new java.sql.Date(date.getTime()));
        pstmt.setInt(2, type);
        pstmt.setInt(3, isYearVip);
        pstmt.setInt(4, vipLevel);
        pstmt.setInt(5, vipType);
        pstmt.setInt(6, list[isYearVip][vipLevel]);
        pstmt.execute();
        pstmt.close();
      }
    }
  }
  public void save() throws SQLException {
    try (Connection connection = DbHelper.getConnection()) {
      if (id == -1) {
        Contact.LOGGER.debug("Adding new contact: {}", this);
        final String sql = "INSERT INTO contacts(name, contacts) VALUES(?, ?)";
        try (PreparedStatement pstmt =
            connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
          pstmt.setString(1, name);
          pstmt.setString(2, contacts);
          pstmt.execute();

          try (final ResultSet rs = pstmt.getGeneratedKeys()) {
            rs.next();
            id = rs.getLong(1);
          }
        }
      } else {
        Contact.LOGGER.debug("Updating existing contact: {}", this);
        final String sql = "UPDATE contacts SET name = ?, contacts = ? WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
          pstmt.setString(1, name);
          pstmt.setString(2, contacts);
          pstmt.setLong(3, id);
          pstmt.execute();
        }
      }
    }
  }
Example #6
0
  /** 同一个PreparedStatement先查询后更新 */
  @Test
  public void testExecute2() throws SQLException {
    TGroupConnection conn = null;
    PreparedStatement stat = null;
    try {
      conn = tgds.getConnection();
      stat = conn.prepareStatement("select * from xxx where id=?");
      stat.setByte(1, (byte) 5);
      boolean res = stat.execute();
      Assert.assertEquals(res, true);

      res = stat.execute("update t set name = 'newName'");
      Assert.assertEquals(res, false);
      MockDataSource.showTrace();
    } finally {
      if (conn != null) {
        try {
          conn.close();
        } catch (SQLException e) {
        }
        if (stat != null) {
          try {
            stat.close();
          } catch (SQLException e) {
          }
        }
      }
    }
  }
  @Override
  public boolean insereVenda(Venda venda) {

    PreparedStatement stmt;
    ResultSet rs;
    Statement st;

    try {
      stmt =
          this.con.prepareStatement(
              ""
                  + " INSERT INTO `imobiliaria`.`venda`"
                  + "(`id`,"
                  + " `idPessoaProprietario`,"
                  + " `idImovel`,"
                  + " `valor`)"
                  + "VALUES (?,"
                  + "?,"
                  + "?,"
                  + "?);");

      stmt.setInt(1, venda.getIdVenda());
      stmt.setInt(2, venda.getIdPessoaProprietario());
      stmt.setInt(3, venda.getIdImovel());
      stmt.setFloat(4, venda.getValorVenda());

      stmt.execute();

      if (stmt.getUpdateCount() > 0) { // se gravou a venda entra para atualziar o imovel

        // atualiza imóvel para vendido
        stmt =
            this.con.prepareStatement(
                ""
                    + "UPDATE `imobiliaria`.`imoveln`"
                    + " SET `vendido` = 1" // Vendido = 1, Não Vendido = 0;
                    + " WHERE `id` = ?;");

        stmt.setInt(1, venda.getIdImovel());
        stmt.execute();

        if (stmt.getUpdateCount() > 0) { // se atualizar o imovel retorna true
          return true;
        } else {
          return false; // senão atualizar o imovel retorna false
        }
      } else {
        return false; // se não gravar a venda retorna false e não entra para atualizar o imovel
      }
    } catch (SQLException ex) {

      Logger.getLogger(ControladorIncluirBanco.class.getName()).log(Level.SEVERE, null, ex);
      Mensagens erro = new Mensagens();
      erro.jopError(
          "Erro ao gravar dados no servidor de banco de dados:\nSQLException: "
              + ex.getMessage()
              + "\n insereVenda");
      return false;
    }
  }
  public static void do_edit(String name, String num, String img_path) {
    try {
      Connection conn = MyConnection1.connect();
      String s0 =
          "update "
              + MyDB.getNames()
              + ".category set cat_name='"
              + name
              + "',img_path='"
              + img_path
              + "' where cat_num='"
              + num
              + "'";
      PreparedStatement stmt = conn.prepareStatement(s0);
      stmt.execute();

      String s1 =
          "update "
              + MyDB.getNames()
              + ".category_type set cat_name='"
              + name
              + "' where cat_num='"
              + num
              + "'";
      PreparedStatement stmt1 = conn.prepareStatement(s1);
      stmt1.execute();
      Prompt.call("Updated Successfully");
      //            JOptionPane.showMessageDialog(null, "Updated Successfully");
    } catch (Exception e) {
      throw new RuntimeException(e);
    } finally {
      MyConnection1.close();
    }
  }
  private void testGetMoreResults(Connection conn) throws SQLException {
    Statement stat = conn.createStatement();
    PreparedStatement prep;
    ResultSet rs;
    stat.execute("CREATE TABLE TEST(ID INT)");
    stat.execute("INSERT INTO TEST VALUES(1)");

    prep = conn.prepareStatement("SELECT * FROM TEST");
    // just to check if it doesn't throw an exception - it may be null
    prep.getMetaData();
    assertTrue(prep.execute());
    rs = prep.getResultSet();
    assertFalse(prep.getMoreResults());
    assertEquals(-1, prep.getUpdateCount());
    // supposed to be closed now
    assertThrows(ErrorCode.OBJECT_CLOSED, rs).next();
    assertEquals(-1, prep.getUpdateCount());

    prep = conn.prepareStatement("UPDATE TEST SET ID = 2");
    assertFalse(prep.execute());
    assertEquals(1, prep.getUpdateCount());
    assertFalse(prep.getMoreResults(Statement.CLOSE_CURRENT_RESULT));
    assertEquals(-1, prep.getUpdateCount());
    // supposed to be closed now
    assertThrows(ErrorCode.OBJECT_CLOSED, rs).next();
    assertEquals(-1, prep.getUpdateCount());

    prep = conn.prepareStatement("DELETE FROM TEST");
    prep.executeUpdate();
    assertFalse(prep.getMoreResults());
    assertEquals(-1, prep.getUpdateCount());
  }
 private void testSetObject(Connection conn) throws SQLException {
   Statement stat = conn.createStatement();
   stat.execute("CREATE TABLE TEST(C CHAR(1))");
   PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?)");
   prep.setObject(1, 'x');
   prep.execute();
   stat.execute("DROP TABLE TEST");
   stat.execute("CREATE TABLE TEST(ID INT, DATA BINARY, JAVA OTHER)");
   prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?, ?)");
   prep.setInt(1, 1);
   prep.setObject(2, 11);
   prep.setObject(3, null);
   prep.execute();
   prep.setInt(1, 2);
   prep.setObject(2, 101, Types.OTHER);
   prep.setObject(3, 103, Types.OTHER);
   prep.execute();
   PreparedStatement p2 = conn.prepareStatement("SELECT * FROM TEST ORDER BY ID");
   ResultSet rs = p2.executeQuery();
   rs.next();
   Object o = rs.getObject(2);
   assertTrue(o instanceof byte[]);
   assertTrue(rs.getObject(3) == null);
   rs.next();
   o = rs.getObject(2);
   assertTrue(o instanceof byte[]);
   o = rs.getObject(3);
   assertTrue(o instanceof Integer);
   assertEquals(103, ((Integer) o).intValue());
   assertFalse(rs.next());
   stat.execute("DROP TABLE TEST");
 }
Example #11
0
  public void save(Proj project, String rval, String oval) throws SQLException {
    if (!rval.equals(project._ResultComment)) {
      String sql =
          "update [PROD_UPG_DATA].[dbo].[RES_ATTACHMENTS] set [mycomment] = '"
              + rval
              + "' where [attachmenttype] = 'COMMENTRESULT' and [ordno] = '"
              + currentSelection
              + "'";

      PreparedStatement ps = con.prepareStatement(sql);
      boolean b = ps.execute();

      ps.close();

      project._ResultComment = rval;
    }

    if (!oval.equals(project._OrderComment)) {
      String sql =
          "update [PROD_UPG_DATA].[dbo].[RES_ATTACHMENTS] set [mycomment] = '"
              + oval
              + "' where [attachmenttype] = 'COMMENTORDER' and [ordno] = '"
              + currentSelection
              + "'";

      PreparedStatement ps = con.prepareStatement(sql);
      boolean b = ps.execute();

      ps.close();

      project._OrderComment = oval;
    }
  }
  /**
   * Initialize the DB with the apiman gateway DDL.
   *
   * @param connection
   */
  private static void initDB(Connection connection) throws Exception {
    ClassLoader cl = JdbcMetricsAccessorTest.class.getClassLoader();
    URL resource = cl.getResource("ddls/apiman-gateway_h2.ddl");
    try (InputStream is = resource.openStream()) {
      System.out.println("=======================================");
      System.out.println("Initializing database.");
      DdlParser ddlParser = new DdlParser();
      List<String> statements = ddlParser.parse(is);
      for (String sql : statements) {
        System.out.println(sql);
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.execute();
      }
      System.out.println("=======================================");
    }

    System.out.println("--------------------------------------");
    System.out.println("Adding test data to the database.");
    resource = cl.getResource("JdbcMetricsAccessorTest/bulk-data.ddl");
    try (InputStream is = resource.openStream()) {
      DdlParser ddlParser = new DdlParser();
      List<String> statements = ddlParser.parse(is);
      for (String sql : statements) {
        System.out.println(sql);
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.execute();
      }
    }
    System.out.println("--------------------------------------");
  }
Example #13
0
  public static int userCount(String user) throws SQLException {

    String query = "select count(id) from pages where finder=?";
    System.out.print(query);
    Connection j = textdisplay.DatabaseWrapper.getConnection();
    PreparedStatement p = j.prepareStatement(query);
    p.setString(1, user);
    ResultSet rs = p.executeQuery();
    rs.next();
    int amount = rs.getInt(1);

    query = "select * from leaders where uname=?";
    p = j.prepareStatement(query);

    p.setString(1, user);
    rs = p.executeQuery();
    if (!rs.next()) {
      query = "insert into leaders (uname,count) values(?,0)";
      p = j.prepareStatement(query);
      p.setString(1, user);
      p.execute();
    }

    query = "update leaders set count=? where uname=?";
    p = j.prepareStatement(query);
    p.setInt(1, amount);
    p.setString(2, user);
    p.execute();
    j.close();
    return amount;
  }
 /** This method executes create user table query on the azure SQL database. */
 @Override
 public void createUserTable() throws Exception {
   Connection connection = null;
   Connection connection1 = null;
   PreparedStatement preparedStatement = null;
   PreparedStatement preparedStatement1 = null;
   try {
     connection = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
     preparedStatement = connection.prepareStatement(AzureChatSQLConstants.CREATE_USER_TABLE);
     preparedStatement.execute();
     connection1 = AzureChatUtils.getConnection(AzureChatUtils.buildConnectionString());
     preparedStatement1 =
         connection1.prepareStatement(AzureChatSQLConstants.CREATE_USER_TABLE_INDEX);
     preparedStatement1.execute();
   } catch (Exception e) {
     LOGGER.error(
         "Exception occurred while executing create user table query on the azure SQL database. Exception Message : "
             + e.getMessage());
     throw new AzureChatSystemException(
         "Exception occurred while executing create user table query on the azure SQL database. Exception Message : "
             + e.getMessage());
   } finally {
     AzureChatUtils.closeDatabaseResources(preparedStatement, connection);
     AzureChatUtils.closeDatabaseResources(preparedStatement1, connection1);
   }
 }
  @Test
  public void testUpdateWriteWriteRollbackConcurrent() throws Exception {

    Connection c1 = methodWatcher.createConnection();
    c1.setAutoCommit(false);
    PreparedStatement ps1 =
        c1.prepareStatement(
            String.format("update %s.%s set col2 = ? where col1 = ?", CLASS_NAME, TABLE_NAME_1));
    ps1.setInt(1, 100);
    ps1.setInt(2, 1);
    ps1.execute();

    Connection c2 = methodWatcher.createConnection();
    c2.setAutoCommit(false);
    try { // catch problem with rollback
      try { // catch write-write conflict
        PreparedStatement ps2 =
            c2.prepareStatement(
                String.format(
                    "update %s.%s set col2 = ? where col1 = ?", CLASS_NAME, TABLE_NAME_1));
        ps2.setInt(1, 1000);
        ps2.setInt(2, 1);
        ps2.execute();
        Assert.fail("Didn't raise write-conflict exception");
      } catch (Exception e) {
        c2.rollback();
      }
    } catch (Exception e) {
      Assert.fail("Unexpected exception " + e);
    }
  }
  protected void prepareDb() {
    try {
      Connection conn = pds.getConnection();
      String createUserTableSql = "create table Users (userId varchar(255))";
      PreparedStatement st = conn.prepareStatement(createUserTableSql);
      st.execute();

      String createGroupTableSql =
          "create table Groups (groupId varchar(255), userId varchar(255))";
      st = conn.prepareStatement(createGroupTableSql);
      st.execute();

      // insert user rows
      String insertUser = "******";
      st = conn.prepareStatement(insertUser);
      st.setString(1, "john");
      st.execute();

      // insert group rows
      String insertGroup = "insert into Groups (groupId, userId) values (?, ?)";
      st = conn.prepareStatement(insertGroup);
      st.setString(1, "PM");
      st.setString(2, "john");
      st.execute();

      st.close();
      conn.close();

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  public static void do_delete_category_type(String num) {
    try {
      Connection conn = MyConnection1.connect();
      String s0 = "delete from " + MyDB.getNames() + ".category_type where type_num='" + num + "'";
      PreparedStatement stmt = conn.prepareStatement(s0);
      stmt.execute();

      String s1 =
          "delete from "
              + MyDB.getNames()
              + ".inventory2_stocks_left where types_num='"
              + num
              + "'";
      PreparedStatement stmt1 = conn.prepareStatement(s1);
      stmt1.execute();

      String s2 = "delete from " + MyDB.getNames() + ".inventory2 where types_num='" + num + "'";
      PreparedStatement stmt2 = conn.prepareStatement(s2);
      stmt2.execute();

      Prompt.call("Updated Successfully");

      //            JOptionPane.showMessageDialog(null, "Updated Successfully");

    } catch (Exception e) {
      throw new RuntimeException(e);
    } finally {
      MyConnection1.close();
    }
  }
  private void insertData() throws SQLException {
    String orderSql = "INSERT INTO `t_order` (`order_id`, `user_id`, `status`) VALUES (?, ?, ?)";
    String orderItemSql =
        "INSERT INTO `t_order_item` (`order_item_id`, `order_id`, `user_id`, `status`) VALUES (?, ?, ?, ?)";
    for (int orderId = 1; orderId <= 4; orderId++) {
      for (int userId = 1; userId <= 2; userId++) {
        try (Connection connection = getShardingDataSource().getConnection()) {
          PreparedStatement pstmt = connection.prepareStatement(orderSql);
          pstmt.setInt(1, orderId);
          pstmt.setInt(2, userId);
          pstmt.setString(3, "insert");
          pstmt.execute();
          pstmt.close();

          pstmt = connection.prepareStatement(orderItemSql);
          pstmt.setInt(1, orderId);
          pstmt.setInt(2, orderId);
          pstmt.setInt(3, userId);
          pstmt.setString(4, "insert");
          pstmt.execute();
          pstmt.close();

          pstmt = connection.prepareStatement(orderItemSql);
          pstmt.setInt(1, orderId + 4);
          pstmt.setInt(2, orderId);
          pstmt.setInt(3, userId);
          pstmt.setString(4, "insert");
          pstmt.execute();
          pstmt.close();
        }
      }
    }
  }
Example #19
0
  public static void saveZutaten(Rezept r, Connection con) throws SQLException {
    Set set = r.zutaten.keySet();
    Iterator iter = set.iterator();

    PreparedStatement stmt = null;

    try {
      String sql = "delete from zut2rez where rez_id = ?";
      stmt = con.prepareStatement(sql);
      stmt.setLong(1, r.getId());
      stmt.execute();
      stmt.close();

      sql = "insert into zut2rez(zut_id, rez_id, menge) values(?,?,?)";
      stmt = con.prepareStatement(sql);

      while (iter.hasNext()) {
        Long id = (Long) iter.next();
        Long wert = r.zutaten.get(id);
        stmt.setLong(1, id);
        stmt.setLong(2, r.getId());
        stmt.setLong(3, wert);
        stmt.execute();
        stmt.clearParameters();
      }
    } finally {
      DbUtil.close(stmt);
    }
  }
  // ===================================================================================
  //                                                                          DataSource
  //                                                                          ==========
  public void test_DataSource_Connection_always_close() throws SQLException {
    Connection conn = null;
    DataSource dataSource = getDataSource();

    conn = dataSource.getConnection();
    try {
      PreparedStatement ps = conn.prepareStatement("select * from MEMBER");
      ps.execute();
      ps.close();
    } finally {
      conn.close();
    }
    conn = dataSource.getConnection();
    try {
      PreparedStatement ps =
          conn.prepareStatement("update MEMBER set MEMBER_NAME = ? where MEMBER_ID = 3");
      ps.setString(1, "aaa");
      ps.execute();
      ps.close();
    } finally {
      conn.close();
    }
    conn = dataSource.getConnection();
    try {
      PreparedStatement ps = conn.prepareStatement("select * from MEMBER");
      ps.execute();
      ps.close();
    } finally {
      conn.close();
    }
  }
Example #21
0
  @Override
  public void writeAt(long index, LogEntry logEntry) {
    if (index >= this.nextIndex.get() || index < this.startIndex.get()) {
      throw new IllegalArgumentException("index out of range");
    }

    try {
      PreparedStatement ps = this.connection.prepareStatement(UPDATE_ENTRY_SQL);
      ps.setLong(1, logEntry.getTerm());
      ps.setByte(2, logEntry.getValueType().toByte());
      ps.setBytes(3, logEntry.getValue());
      ps.setLong(4, index);
      ps.execute();
      ps = this.connection.prepareStatement(TRIM_TABLE_SQL);
      ps.setLong(1, index);
      ps.execute();
      ps = this.connection.prepareStatement(UPDATE_SEQUENCE_SQL);
      ps.setLong(1, index + 1);
      ps.execute();
      this.connection.commit();
      this.nextIndex.set(index + 1);
      this.lastEntry = logEntry;
    } catch (Throwable error) {
      this.logger.error("failed to write an entry at a specific index", error);
      throw new RuntimeException("log store error", error);
    }
  }
  public static void main(String[] args) throws SQLException {
    try (Connection con = new MeuPoolConection().getConnection()) {
      // buscar os dados
      String sqlLeitura = "select * from leitura where leitura_cod = ?";

      int[] codigos = new int[] {148, 119, 117, 137, 146};
      for (int i : codigos) {
        try (PreparedStatement stmp =
            con.prepareStatement(sqlLeitura, Statement.RETURN_GENERATED_KEYS)) {
          stmp.setInt(1, i);
          stmp.execute();
          ResultSet res = stmp.getResultSet();
          while (res.next()) {
            // insere as leituras dos equipamentos
            String sqlInsert = "INSERT INTO leitura_eq(RMS,codigo) VALUES(?,?)";
            try (PreparedStatement stmpInsert =
                con.prepareStatement(sqlInsert, Statement.RETURN_GENERATED_KEYS)) {
              stmpInsert.setDouble(1, res.getDouble(4));
              stmpInsert.setInt(2, 0);
              stmpInsert.execute();
              System.out.println(stmpInsert.getGeneratedKeys());
            }
          }
          res.close();
          stmp.close();
        }
      }
    }
    // inserir os dados

  }
Example #23
0
  @RequestMapping("/tabelas")
  public String criaBanco() throws SQLException {
    Connection c = new ConnectionFactory().getConnection();
    PreparedStatement st1 = c.prepareStatement("drop table contas if exists");
    st1.execute();

    PreparedStatement st11 =
        c.prepareStatement(
            "create table contas (id int identity, descricao varchar(255), valor double, paga boolean, dataPagamento datetime, tipo varchar(20))");
    st11.execute();

    PreparedStatement st2 = c.prepareStatement("drop table usuarios if exists");
    st2.execute();

    PreparedStatement st22 =
        c.prepareStatement("create table usuarios (login VARCHAR(255),senha VARCHAR(255));");
    st22.execute();

    PreparedStatement st3 =
        c.prepareStatement("insert into usuarios (login, senha) values ('caelum', 'online');");
    st3.execute();

    c.close();

    return "infra-ok";
  }
Example #24
0
  public void generateTag() throws Exception {
    String sql = "replace into tag(node_id,name,weight) values(?,?,?);";
    Connection connection = MysqlInfo.getMysqlConnection();
    PreparedStatement stmt = connection.prepareStatement(sql);
    char init = 'A' - 1;
    for (int i = 0; i <= 25; i++) {
      init++;
      String typeName = "type" + init;
      int node_id = i;
      char subInit = 'a' - 1;
      int weight = (int) (Math.random() * 10);

      stmt.setString(1, Integer.toString(node_id));
      stmt.setString(2, typeName);
      stmt.setInt(3, weight);
      stmt.execute();
      for (int j = 0; j <= 10; j++) {
        subInit++;
        String subType = typeName + subInit;
        String node = i + "." + j;
        weight = (int) (Math.random() * 10);

        stmt.setString(1, node);
        stmt.setString(2, subType);
        stmt.setInt(3, weight);
        stmt.execute();
      }
    }
    stmt.close();
    connection.close();
  }
Example #25
0
 public void deleteUser(String username) {
   if (isReadOnly()) {
     // Reject the operation since the provider is read-only
     throw new UnsupportedOperationException();
   }
   Connection con = null;
   PreparedStatement pstmt = null;
   boolean abortTransaction = false;
   try {
     // Delete all of the users's extended properties
     con = DbConnectionManager.getTransactionConnection();
     pstmt = con.prepareStatement(DELETE_USER_PROPS);
     pstmt.setString(1, username);
     pstmt.execute();
     pstmt.close();
     // Delete the actual user entry
     pstmt = con.prepareStatement(DELETE_USER);
     pstmt.setString(1, username);
     pstmt.execute();
   } catch (Exception e) {
     Log.error(e);
     abortTransaction = true;
   } finally {
     DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction);
   }
 }
Example #26
0
  @Test
  public void testTxnClosedCorrecty() throws Exception {
    String selectSql = "SELECT * FROM " + FULL_TABLE_NAME;
    try (Connection conn = DriverManager.getConnection(getUrl())) {
      conn.setAutoCommit(false);
      ResultSet rs = conn.createStatement().executeQuery(selectSql);
      assertFalse(rs.next());

      String upsert =
          "UPSERT INTO "
              + FULL_TABLE_NAME
              + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
      PreparedStatement stmt = conn.prepareStatement(upsert);
      // upsert two rows
      TestUtil.setRowKeyColumns(stmt, 1);
      stmt.execute();
      TestUtil.setRowKeyColumns(stmt, 2);
      stmt.execute();

      // verify rows can be read even though commit has not been called
      rs = conn.createStatement().executeQuery(selectSql);
      TestUtil.validateRowKeyColumns(rs, 1);
      TestUtil.validateRowKeyColumns(rs, 2);
      assertFalse(rs.next());

      conn.close();
      // wait for any open txns to time out
      Thread.sleep(DEFAULT_TXN_TIMEOUT_SECONDS * 1000 + 10000);
      assertTrue("There should be no invalid transactions", txManager.getInvalidSize() == 0);
    }
  }
Example #27
0
 /**
  * Method updateOwnerInDB.
  *
  * @param clan Clan
  */
 private void updateOwnerInDB(Clan clan) {
   _owner = clan;
   Connection con = null;
   PreparedStatement statement = null;
   try {
     con = DatabaseFactory.getInstance().getConnection();
     statement =
         con.prepareStatement("UPDATE clan_data SET hasFortress=0 WHERE hasFortress=? LIMIT 1");
     statement.setInt(1, getId());
     statement.execute();
     DbUtils.close(statement);
     if (clan != null) {
       statement =
           con.prepareStatement("UPDATE clan_data SET hasFortress=? WHERE clan_id=? LIMIT 1");
       statement.setInt(1, getId());
       statement.setInt(2, getOwnerId());
       statement.execute();
       clan.broadcastClanStatus(true, false, false);
     }
   } catch (Exception e) {
     _log.error("", e);
   } finally {
     DbUtils.closeQuietly(con, statement);
   }
 }
Example #28
0
  @Test
  public void testReadOwnWrites() throws Exception {
    String selectSql = "SELECT * FROM " + FULL_TABLE_NAME;
    try (Connection conn = DriverManager.getConnection(getUrl())) {
      conn.setAutoCommit(false);
      ResultSet rs = conn.createStatement().executeQuery(selectSql);
      assertFalse(rs.next());

      String upsert =
          "UPSERT INTO "
              + FULL_TABLE_NAME
              + "(varchar_pk, char_pk, int_pk, long_pk, decimal_pk, date_pk) VALUES(?, ?, ?, ?, ?, ?)";
      PreparedStatement stmt = conn.prepareStatement(upsert);
      // upsert two rows
      TestUtil.setRowKeyColumns(stmt, 1);
      stmt.execute();
      TestUtil.setRowKeyColumns(stmt, 2);
      stmt.execute();

      // verify rows can be read even though commit has not been called
      rs = conn.createStatement().executeQuery(selectSql);
      TestUtil.validateRowKeyColumns(rs, 1);
      TestUtil.validateRowKeyColumns(rs, 2);
      assertFalse(rs.next());

      conn.commit();

      // verify rows can be read after commit
      rs = conn.createStatement().executeQuery(selectSql);
      TestUtil.validateRowKeyColumns(rs, 1);
      TestUtil.validateRowKeyColumns(rs, 2);
      assertFalse(rs.next());
    }
  }
  /**
   * Method called by the Form panel to delete existing data.
   *
   * @param persistentObject value object to delete
   * @return an ErrorResponse value object in case of errors, VOResponse if the operation is
   *     successfully completed
   */
  public Response deleteRecord(ValueObject persistentObject) throws Exception {
    PreparedStatement stmt = null;
    try {
      EmpVO vo = (EmpVO) persistentObject;

      // delete from WORKING_DAYS...
      stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?");
      stmt.setString(1, vo.getEmpCode());
      stmt.execute();
      stmt.close();

      // delete from EMP...
      stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?");
      stmt.setString(1, vo.getEmpCode());
      stmt.execute();
      gridFrame.reloadData();

      frame.getGrid().clearData();

      return new VOResponse(vo);
    } catch (SQLException ex) {
      ex.printStackTrace();
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        stmt.close();
        conn.commit();
      } catch (SQLException ex1) {
      }
    }
  }
  protected void storeResource(HttpServletRequest req, HttpServletResponse res)
      throws SQLException {

    String data = req.getParameter("data");

    PreparedStatement stmt = database.prepareStatement("SELECT ID FROM sites WHERE Name = ?");
    stmt.setString(1, this.currentResource);

    if (stmt.executeQuery().next()) {

      PreparedStatement store =
          database.prepareStatement("UPDATE sites SET Site = ? WHERE Name = ?");
      store.setString(1, data);
      store.setString(2, this.currentResource);
      store.execute();

    } else {

      PreparedStatement store =
          database.prepareStatement("INSERT INTO sites (Name, Site) VALUES (?, ?)");
      store.setString(2, data);
      store.setString(1, this.currentResource);
      store.execute();
    }
  }