Example usage for java.sql Statement executeUpdate

List of usage examples for java.sql Statement executeUpdate

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. executeUpdate

Introduction

In this page you can find the example usage for java.sql Statement executeUpdate.

Prototype

int executeUpdate(String sql) throws SQLException;

Source Link

Document

Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

Usage

From source file:com.aurel.track.dbase.MigrateTo37.java

private static void upgtadeID_TableEntries(Connection connection, int transactionID, int fieldChangeID) {
    try {/* ww  w .  j  av a2  s.c om*/
        Statement stmt = connection.createStatement();
        stmt.executeUpdate(
                "UPDATE ID_TABLE SET NEXT_ID = " + transactionID + " WHERE TABLE_NAME = 'THISTORYTRANSACTION'");
    } catch (SQLException e) {
        LOGGER.error("Updating the THISTORYTRANSACTION ID in ID_TABLE failed with " + e.getMessage());
        System.err.println(ExceptionUtils.getStackTrace(e));
    }
    try {
        Statement stmt = connection.createStatement();
        stmt.executeUpdate(
                "UPDATE ID_TABLE SET NEXT_ID = " + fieldChangeID + " WHERE TABLE_NAME = 'TFIELDCHANGE'");
    } catch (SQLException e) {
        LOGGER.error("Updating the TFIELDCHANGE ID in ID_TABLE failed with " + e.getMessage());
        System.err.println(ExceptionUtils.getStackTrace(e));
    }
}

From source file:net.tirasa.ilgrosso.resetdb.Main.java

private static void resetPostgreSQL(final Connection conn) throws Exception {

    final Statement statement = conn.createStatement();

    final ResultSet resultSet = statement
            .executeQuery("SELECT 'DROP TABLE ' || c.relname || ' CASCADE;' FROM pg_catalog.pg_class c "
                    + "LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
                    + "WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast') "
                    + "AND pg_catalog.pg_table_is_visible(c.oid)");
    final List<String> drops = new ArrayList<String>();
    while (resultSet.next()) {
        drops.add(resultSet.getString(1));
    }/*from  w  w w . j a va2s  .  c om*/
    resultSet.close();

    for (String drop : drops) {
        statement.executeUpdate(drop.substring(0, drop.length() - 1));
    }

    statement.close();
    conn.close();
}

From source file:ems.util.DataHandler.java

public static boolean updateVoterDetails(String emailId, String mobileNo, String alternatMobileNo, String dob,
        String age, String community, String gender, String wardNo, String wardSrNo) {
    String sqlQuery = String.format(Q_U_VOTER_DETAILS, emailId, mobileNo, alternatMobileNo, dob, age, community,
            gender, wardNo, wardSrNo);// w w w  .  ja  v a2 s.  c o m
    Connection con = getConnection();
    Statement s = null;
    try {
        log.info("sqlQuery:" + sqlQuery);
        s = con.createStatement();
        int i = s.executeUpdate(sqlQuery);
        log.info("updateVoterDetails|" + i);
        return true;
    } catch (SQLException e) {
        log.error("updateVoterDetails: " + e.getMessage());
    } finally {
        try {
            if (s != null) {
                s.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            log.error("updateVoterDetails: " + ex.getMessage());
        }
    }
    return false;
}

From source file:com.aurel.track.dbase.InitReportTemplateBL.java

private static void addReportTemplateToDatabase(Integer oid, String name, String expfmt, String description) {

    String stmt = "INSERT INTO TEXPORTTEMPLATE (OBJECTID,NAME,EXPORTFORMAT,REPOSITORYTYPE,DESCRIPTION,PROJECT,PERSON,REPORTTYPE)"
            + "VALUES (" + oid + ",'" + name + "','" + expfmt + "',2,'" + description
            + "',NULL,1,'Jasper Report')";

    Connection coni = null;/*from  w w w  . jav  a  2s  .c  o m*/
    Connection cono = null;
    ResultSet rs = null;
    try {
        coni = InitDatabase.getConnection();
        cono = InitDatabase.getConnection();
        PreparedStatement istmt = coni
                .prepareStatement("SELECT MAX(OBJECTID) FROM TEXPORTTEMPLATE WHERE OBJECTID < 100");
        Statement ostmt = cono.createStatement();

        rs = istmt.executeQuery();
        Integer maxInt = 0;
        if (rs != null) {
            rs.next();
            maxInt = rs.getInt(1);
        }
        if (oid.intValue() <= maxInt.intValue()) {
            return;
        }

        istmt = coni.prepareStatement("SELECT * FROM TEXPORTTEMPLATE WHERE OBJECTID = ?");
        istmt.setInt(1, oid);

        rs = istmt.executeQuery();
        if (rs == null || !rs.next()) {
            LOGGER.info("Adding report template with OID " + oid + ": " + name);
            try {
                ostmt.executeUpdate(stmt);
            } catch (Exception exc) {
                LOGGER.error("Problem...: " + exc.getMessage());
            }
        }
    } catch (Exception e) {
        LOGGER.debug(ExceptionUtils.getStackTrace(e));
    } finally {
        try {
            if (rs != null)
                rs.close();
            if (coni != null)
                coni.close();
            if (cono != null)
                cono.close();
        } catch (Exception e) {
            LOGGER.debug(ExceptionUtils.getStackTrace(e));
        }
    }
}

From source file:com.aurel.track.admin.customize.category.filter.PredefinedQueryBL.java

/**
 * Add the hardcoded filters Use JDBC because negative objectIDs should be
 * added/*from ww w . ja  va  2  s  . co m*/
 */
public static void addHardcodedFilters() {
    LOGGER.info("Add hardcoded filters");
    FilterFacade filterFacade = FilterFacadeFactory.getInstance()
            .getFilterFacade(TQueryRepositoryBean.QUERY_PURPOSE.TREE_FILTER, true);
    List<TPersonBean> personBeans = PersonBL.loadPersons();
    // Get not closed stateIDs
    List<TStateBean> notClosedStateBeans = StatusBL.loadNotClosedStates();
    List<Integer> notClosedStateIDs = GeneralUtils.createIntegerListFromBeanList(notClosedStateBeans);
    Integer[] notClosedStatesArr = GeneralUtils.createIntegerArrFromCollection(notClosedStateIDs);
    // get closed stateIDs
    List<TStateBean> closedStateBeans = StatusBL.loadClosedStates();
    List<Integer> closedStateIDs = GeneralUtils.createIntegerListFromBeanList(closedStateBeans);
    Integer[] closedStatesArr = GeneralUtils.createIntegerArrFromCollection(closedStateIDs);
    List<String> predefinedFilterClobStms = new ArrayList<String>();
    List<String> predefinedFilterStms = new ArrayList<String>();
    ILabelBean allItemsFilterBean = filterFacade.getByKey(PREDEFINED_QUERY.ALL_ITEMS);
    if (allItemsFilterBean == null) {
        LOGGER.info("Add 'All issues' filter");
        predefinedFilterClobStms
                .add(addPredefinedQueryClob(PREDEFINED_QUERY.ALL_ITEMS, getAllIssuesExpression()));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.ALL_ITEMS, "All issues"));
    }
    ILabelBean unresolvedBean = filterFacade.getByKey(PREDEFINED_QUERY.OUTSTANDING);
    if (unresolvedBean == null) {
        LOGGER.info("Add 'Outstanding' filter");
        predefinedFilterClobStms.add(addPredefinedQueryClob(PREDEFINED_QUERY.OUTSTANDING,
                getOutstandingExpression(notClosedStatesArr)));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.OUTSTANDING, "Outstanding"));
    }
    ILabelBean myFilterBean = filterFacade.getByKey(PREDEFINED_QUERY.MY_ITEMS);
    if (myFilterBean == null) {
        LOGGER.info("Add 'My items' filter");
        predefinedFilterClobStms.add(
                addPredefinedQueryClob(PREDEFINED_QUERY.MY_ITEMS, getMyItemsExpression(notClosedStatesArr)));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.MY_ITEMS, "My items"));
    }
    ILabelBean managersBean = filterFacade.getByKey(PREDEFINED_QUERY.MANAGERS_ITEMS);
    if (managersBean == null) {
        LOGGER.info("Add manager filter");
        predefinedFilterClobStms.add(addPredefinedQueryClob(PREDEFINED_QUERY.MANAGERS_ITEMS,
                getManagerItemsExpression(notClosedStatesArr)));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.MANAGERS_ITEMS, "I''m the manager"));
    }
    ILabelBean responsibleBean = filterFacade.getByKey(PREDEFINED_QUERY.RESPONSIBLES_ITEMS);
    if (responsibleBean == null) {
        LOGGER.info("Add responsible filter");
        predefinedFilterClobStms.add(addPredefinedQueryClob(PREDEFINED_QUERY.RESPONSIBLES_ITEMS,
                getResponsibleItemsExpression(notClosedStatesArr)));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.RESPONSIBLES_ITEMS, "I''m responsible"));
    }
    ILabelBean reporterBean = filterFacade.getByKey(PREDEFINED_QUERY.AUTHOR_ITEMS);
    if (reporterBean == null) {
        LOGGER.info("Add author filter");
        predefinedFilterClobStms.add(addPredefinedQueryClob(PREDEFINED_QUERY.AUTHOR_ITEMS,
                getReporterItemsExpression(notClosedStatesArr)));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.AUTHOR_ITEMS, "I''m the author"));
    }
    ILabelBean watcherBean = filterFacade.getByKey(PREDEFINED_QUERY.WATCHER_ITEMS);
    if (watcherBean == null) {
        LOGGER.info("Add watcher filter");
        predefinedFilterClobStms.add(addPredefinedQueryClob(PREDEFINED_QUERY.WATCHER_ITEMS,
                getWatcherItemsExpression(notClosedStatesArr)));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.WATCHER_ITEMS, "I''m watcher"));
    }
    ILabelBean meetingsBean = filterFacade.getByKey(PREDEFINED_QUERY.MEETINGS);
    if (meetingsBean == null) {
        LOGGER.info("Add 'Meetings' filter");
        predefinedFilterClobStms.add(addPredefinedQueryClob(PREDEFINED_QUERY.MEETINGS,
                getMeetingItemsExpression(notClosedStatesArr)));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.MEETINGS, "Meetings"));
    }
    ILabelBean unscheduledBean = filterFacade.getByKey(PREDEFINED_QUERY.UNSCHEDULED);
    if (unscheduledBean == null) {
        LOGGER.info("Add 'Unscheduled' filter");
        predefinedFilterClobStms.add(addPredefinedQueryClob(PREDEFINED_QUERY.UNSCHEDULED,
                getUnscheduledItemsExpression(notClosedStatesArr)));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.UNSCHEDULED, "Unscheduled"));
    }
    ILabelBean closedRecentlyBean = filterFacade.getByKey(PREDEFINED_QUERY.CLOSED_RECENTLY);
    if (closedRecentlyBean == null) {
        LOGGER.info("Add 'Closed recently' filter");
        predefinedFilterClobStms.add(addPredefinedQueryClob(PREDEFINED_QUERY.CLOSED_RECENTLY,
                getRecentlyClosedItemsExpression(closedStatesArr)));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.CLOSED_RECENTLY, "Closed recently"));
    }
    ILabelBean addedRecentlyBean = filterFacade.getByKey(PREDEFINED_QUERY.ADDED_RECENTLY);
    if (addedRecentlyBean == null) {
        LOGGER.info("Add 'Added recently' filter");
        predefinedFilterClobStms.add(
                addPredefinedQueryClob(PREDEFINED_QUERY.ADDED_RECENTLY, getRecentlyAddedItemsExpression()));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.ADDED_RECENTLY, "Added recently"));
    }
    ILabelBean updatedRecentlyBean = filterFacade.getByKey(PREDEFINED_QUERY.UPDATED_RECENTLY);
    if (updatedRecentlyBean == null) {
        LOGGER.info("Add 'Updated recently' filter");
        predefinedFilterClobStms.add(
                addPredefinedQueryClob(PREDEFINED_QUERY.UPDATED_RECENTLY, getRecentlyUpdatedItemsExpression()));
        predefinedFilterStms.add(addPredefinedQuery(PREDEFINED_QUERY.UPDATED_RECENTLY, "Updated recently"));
    }
    ILabelBean scrumBoardFilterBean = filterFacade.getByKey(PREDEFINED_QUERY.SCRUM_BOARD);
    if (scrumBoardFilterBean == null) {
        LOGGER.info("Add 'Scrum board' filter");
        predefinedFilterClobStms
                .add(addPredefinedQueryClob(PREDEFINED_QUERY.SCRUM_BOARD, getAllIssuesExpression()));
        predefinedFilterStms.add(addPredefinedFilterWitView(PREDEFINED_QUERY.SCRUM_BOARD, "Scrum board",
                IssueListViewDescriptor.CARD));
    }

    ILabelBean kanbanBoardFilterBean = filterFacade.getByKey(PREDEFINED_QUERY.KANBAN_BOARD);
    if (kanbanBoardFilterBean == null) {
        LOGGER.info("Add 'Kanban board' filter");
        predefinedFilterClobStms
                .add(addPredefinedQueryClob(PREDEFINED_QUERY.KANBAN_BOARD, getAllIssuesExpression()));
        predefinedFilterStms.add(addPredefinedFilterWitView(PREDEFINED_QUERY.KANBAN_BOARD, "Kanban board",
                IssueListViewDescriptor.CARD));
    }
    Connection cono = null;
    try {
        cono = InitDatabase.getConnection();
        Statement ostmt = cono.createStatement();
        cono.setAutoCommit(false);
        for (String filterClobStmt : predefinedFilterClobStms) {
            ostmt.executeUpdate(filterClobStmt);
        }
        for (String filterStmt : predefinedFilterStms) {
            ostmt.executeUpdate(filterStmt);
        }
        cono.commit();
        cono.setAutoCommit(true);
    } catch (Exception e) {
        LOGGER.debug(ExceptionUtils.getStackTrace(e));
    } finally {
        try {
            if (cono != null) {
                cono.close();
            }
        } catch (Exception e) {
            LOGGER.debug(ExceptionUtils.getStackTrace(e));
        }
    }
    // include in menu for all users
    List<Integer> filterIDs = MenuitemFilterBL.getFilterIDsToSubscribe();
    LOGGER.info("Number of filters to subscribe " + filterIDs.size());
    if (!filterIDs.isEmpty()) {
        for (TPersonBean personBean : personBeans) {
            Integer personID = personBean.getObjectID();
            MenuitemFilterBL.subscribePersonsToFilters(personID, filterIDs);
        }
    }
    scrumBoardFilterBean = filterFacade.getByKey(PREDEFINED_QUERY.SCRUM_BOARD);
    if (scrumBoardFilterBean != null) {
        addCardLayout(scrumBoardFilterBean.getObjectID(), SystemFields.INTEGER_STATE,
                SystemFields.INTEGER_PRIORITY, (List) notClosedStateBeans);
    }
    kanbanBoardFilterBean = filterFacade.getByKey(PREDEFINED_QUERY.KANBAN_BOARD);
    if (kanbanBoardFilterBean != null) {
        addCardLayout(kanbanBoardFilterBean.getObjectID(), SystemFields.INTEGER_RESPONSIBLE,
                SystemFields.INTEGER_PRIORITY, (List) PersonBL.loadActivePersons());
    }
}

From source file:bizlogic.Records.java

public static void setColumn(Connection DBcon, String records, String DB, String column, String value)
        throws SQLException {

    Statement st;

    String _value;/*w  ww . j a va  2 s.co  m*/

    String sql_statement;

    String _records = records.replace(";", ",");

    switch (value) {
    case "true":
        _value = "B'1'";
        break;

    case "false":
        _value = "B'0'";
        break;

    default:
        _value = value;
        break;
    }

    //st = DBcon.createStatement();
    //for(int i = 0; i<30; i++) {
    sql_statement = "UPDATE " + DB + " SET " + column + " = " + _value + " WHERE LOG_ID IN (" + _records + ")";
    System.out.println(sql_statement);
    //st.clearBatch();
    st = DBcon.createStatement();
    DBcon.createStatement();
    st.executeUpdate(sql_statement);
}

From source file:database.HashTablesTools.java

public static void createTables(Connection connection, String tableName, String tableFailureName) {

    dropTable(connection, tableName);/*ww w.  ja  v  a2  s.  c om*/
    dropTable(connection, tableFailureName);

    // check if table exists if not create it
    // E95A91AD32BBFB2C7ACCC5E75F48686F
    try {
        Statement stmt = connection.createStatement();
        String createTableSql = "CREATE TABLE " + tableName
                + " (pdbfilehash varchar(32), fourLettercode varchar(4), chainId varchar(2), chainType varchar(2), sequenceString varchar("
                + maxCharInVarchar + "), PRIMARY KEY (pdbfilehash, chainId) ) ";
        System.out.println(createTableSql);
        stmt.executeUpdate(createTableSql);
        System.out.println("created table " + tableName + " in myDB !");
        stmt.close();
    } catch (SQLException e1) {
        System.out.println("Table " + tableName + " already exists in myDB !");
    }
    try {
        Statement stmt = connection.createStatement();
        String createTableSql = "CREATE TABLE " + tableFailureName
                + " (pdbfilehash varchar(32), fourLettercode varchar(4), PRIMARY KEY (pdbfilehash) ) ";
        System.out.println(createTableSql);
        stmt.executeUpdate(createTableSql);
        System.out.println("created table " + tableName + " in myDB !");
        stmt.close();
    } catch (SQLException e1) {
        System.out.println("Table " + tableName + " already exists in myDB !");
    }
}

From source file:com.manydesigns.portofino.persistence.QueryUtils.java

/**
 * Runs a SQL query against a session. The query can contain placeholders for the parameters, as supported by
 * {@link PreparedStatement}. <br>
 * INSERT UPDATE DELETE DROP CREATE ALTER TRUNCATE RENAME sueprpan add
 * //  w ww .  j a  va  2  s.c om
 * @param session the session
 * @param queryString the query
 * @param parameters parameters to substitute in the query
 * @return the results of the query as an Object[] (an array cell per column)
 */
public static int runSqlDml(Session session, final String queryString, final Object[] parameters) {
    final List<Integer> result = new ArrayList<Integer>();
    try {
        session.doWork(new Work() {
            public void execute(Connection connection) throws SQLException {
                Statement stmt = connection.createStatement();
                try {
                    result.add(stmt.executeUpdate(queryString));
                } finally {
                    stmt.close();
                }
            }
        });
    } catch (HibernateException e) {
        result.add(-1);
        session.getTransaction().rollback();
        session.beginTransaction();
        throw e;
    }
    if (result.size() > 0) {
        return result.get(0);
    }
    return -1;
}

From source file:com.manydesigns.portofino.persistence.QueryUtils.java

/**
 * Runs a SQL query against a session. The query is processed with an {@link OgnlSqlFormat}, so it can access values
 * from the OGNL context.<br>/* w  ww.  j a  v a2 s  .  com*/
 * INSERT UPDATE DELETE DROP CREATE ALTER TRUNCATE RENAME hongliangpan add
 * 
 * @param session the session
 * @param sql the query string
 * @return the results of the query as an Object[] (an array cell per column)
 */
public static int runSqlDml(Session session, String sql) {
    OgnlSqlFormat sqlFormat = OgnlSqlFormat.create(sql);
    final String queryString = sqlFormat.getFormatString();
    final List<Integer> result = new ArrayList<Integer>();
    try {
        session.doWork(new Work() {
            public void execute(Connection connection) throws SQLException {
                Statement stmt = connection.createStatement();
                try {
                    result.add(stmt.executeUpdate(queryString));
                } finally {
                    stmt.close();
                }
            }
        });
    } catch (HibernateException e) {
        result.add(-1);
        session.getTransaction().rollback();
        session.beginTransaction();
        throw e;
    }
    if (result.size() > 0) {
        return result.get(0);
    }
    return -1;
}

From source file:sk.upjs.ics.paz1c.mp3library.SqliteMigration.java

private void execute(String sql) throws SQLException {
    Statement stmt = c.createStatement();
    stmt.executeUpdate(sql);
    stmt.close();/*from www.j a  v a2s .  c  o  m*/
}