Java Code Examples for org.apache.commons.dbutils.QueryRunner#update()

/**
 * @see io.apiman.gateway.engine.IRegistry#publishApi(io.apiman.gateway.engine.beans.Api, io.apiman.gateway.engine.async.IAsyncResultHandler)
 */
@Override
public void publishApi(Api api, IAsyncResultHandler<Void> handler) {
    Connection conn = null;
    try {
        conn = ds.getConnection();
        conn.setAutoCommit(false);
        QueryRunner run = new QueryRunner();

        // First delete any record we might already have.
        run.update(conn, "DELETE FROM gw_apis WHERE org_id = ? AND id = ? AND version = ?",  //$NON-NLS-1$
                api.getOrganizationId(), api.getApiId(), api.getVersion());

        // Now insert a row for the api.
        String bean = mapper.writeValueAsString(api);
        run.update(conn, "INSERT INTO gw_apis (org_id, id, version, bean) VALUES (?, ?, ?, ?)",  //$NON-NLS-1$
                api.getOrganizationId(), api.getApiId(), api.getVersion(), bean);

        DbUtils.commitAndClose(conn);
        handler.handle(AsyncResultImpl.create((Void) null, Void.class));
    } catch (SQLException | JsonProcessingException e) {
        handler.handle(AsyncResultImpl.create(e));
    }
}
 

/**
 * Save row data.
 * 
 * @param insertSql
 * @throws Exception
 */
public void saveRowdata(String insertSql) throws Exception {
	Connection conn = null;
	try {
		QueryRunner runner = new QueryRunner();
		conn = dbFactory.getConnection();
		int num = runner.update(conn, insertSql);
		if (num <= 0) {
			log.warn("Failed save rowdata for sql: " + insertSql);
		}
	} catch (Exception e) {
		throw e;
	} finally {
		DbUtils.close(conn);
	}

}
 

public <T extends Table<?>> int delete(T table) {
    try {
        QueryRunner runner = getJdbcTemplate(table.getClass());
        TableInfo info = table.getTableInfo();
        String sql = delSql.toSqlString(info.pkName(), info.annotation().tableName(), new String[]{info.pkName()});
        return runner.update(sql, new Object[]{table.getPkId()});
    } catch (Exception e) {
        LOGGER.error("", e);
    }
    return 0;
}
 

/**
 * @see io.apiman.gateway.engine.IRegistry#registerClient(io.apiman.gateway.engine.beans.Client, io.apiman.gateway.engine.async.IAsyncResultHandler)
 */
@Override
public void registerClient(Client client, IAsyncResultHandler<Void> handler) {
    Connection conn = null;
    try {
        conn = ds.getConnection();
        conn.setAutoCommit(false);
        QueryRunner run = new QueryRunner();

        // Validate the client and populate the api map with apis found during validation.
        validateClient(client, conn);

        // Remove any old data first, then (re)insert
        run.update(conn, "DELETE FROM gw_clients WHERE org_id = ? AND id = ? AND version = ?",  //$NON-NLS-1$
                client.getOrganizationId(), client.getClientId(), client.getVersion());

        String bean = mapper.writeValueAsString(client);
        run.update(conn, "INSERT INTO gw_clients (api_key, org_id, id, version, bean) VALUES (?, ?, ?, ?, ?)",  //$NON-NLS-1$
                client.getApiKey(), client.getOrganizationId(), client.getClientId(), client.getVersion(), bean);

        DbUtils.commitAndClose(conn);
        handler.handle(AsyncResultImpl.create((Void) null));
    } catch (Exception re) {
        DbUtils.rollbackAndCloseQuietly(conn);
        handler.handle(AsyncResultImpl.create(re, Void.class));
    }
}
 

/**
 * @see io.apiman.gateway.engine.IRegistry#retireApi(io.apiman.gateway.engine.beans.Api, io.apiman.gateway.engine.async.IAsyncResultHandler)
 */
@Override
public void retireApi(Api api, IAsyncResultHandler<Void> handler) {
    QueryRunner run = new QueryRunner(ds);
    try {
        run.update("DELETE FROM gw_apis WHERE org_id = ? AND id = ? AND version = ?",  //$NON-NLS-1$
                api.getOrganizationId(), api.getApiId(), api.getVersion());
        handler.handle(AsyncResultImpl.create((Void) null, Void.class));
    } catch (SQLException e) {
        handler.handle(AsyncResultImpl.create(e));
    }
}
 

/**
 * 执行含参数的sql语句
 * @param sql 被执行的sql语句
 * @param params 参数
 * @return 返回受影响的行
 * @throws Exception
 */
public int execute(String sql, Object[] params) throws Exception {
	Connection conn = getConnection();
	int rows = 0;
	try {
		QueryRunner qr = new QueryRunner();
		rows = qr.update(conn, sql, params);
	} finally {
		Close(conn);
	}
	return rows;
}
 

/**
 * 执行sql语句
 * @param sql 被执行的sql语句
 * @return 受影响的行
 * @throws Exception
 */
public int execute(String sql) throws Exception {
	Connection conn = getConnection();
	int rows = 0;
	try {
		QueryRunner qr = new QueryRunner();
		rows = qr.update(conn, sql);
	} finally {
		Close(conn);
	}
	return rows;
}
 

public void placeOrder(BindObject bindObj) throws SQLException {
    deleteOrder(bindObj.get("id"));

    QueryRunner run = new QueryRunner( H2DB.getDataSource() );
    run.update("insert into order(id, pet_id, quantity, ship_date, status) " +
                    "values(?, ?, ?, ?, ?)",
            bindObj.get("id"),
            bindObj.get("petId"),
            bindObj.get("quantity"),
            bindObj.get("shipDate"),
            bindObj.get("status"));
}
 

private int executeSQL(Connection connection, String upgradeSQL, QueryRunner qr) throws SQLException {
    int rs = 0;
    for (String item : upgradeSQL.split(";")) {
        if (item.trim().length() == 0) {
            continue;
        }
        rs += qr.update(connection, item);
    }
    return rs;
}
 

@Test
public void givenSalary_whenUpdating_thenUpdated() throws SQLException {
    double salary = 35000;

    QueryRunner runner = new QueryRunner();
    String updateSQL = "UPDATE employee SET salary = salary * 1.1 WHERE salary <= ?";
    int numRowsUpdated = runner.update(connection, updateSQL, salary);

    assertEquals(numRowsUpdated, 3);
}
 

@Test
public void whenInserting_thenInserted() throws SQLException {
    QueryRunner runner = new QueryRunner();
    String insertSQL = "INSERT INTO employee (firstname,lastname,salary, hireddate) VALUES (?, ?, ?, ?)";

    int numRowsInserted = runner.update(connection, insertSQL, "Leia", "Kane", 60000.60, new Date());

    assertEquals(numRowsInserted, 1);
}
 

@Test
public void whenDeletingRecord_thenDeleted() throws SQLException {
    QueryRunner runner = new QueryRunner();
    String deleteSQL = "DELETE FROM employee WHERE id = ?";
    int numRowsDeleted = runner.update(connection, deleteSQL, 3);

    assertEquals(numRowsDeleted, 1);
}
 

public <T extends Table<?>> int update(T table) {
    QueryRunner runner = getJdbcTemplate(table.getClass());

    TableInfo info = table.getTableInfo();
    String sql = replaceSql.toSqlString(table.tableName(), info.buildDbColumns());
    Object[] values = table.writeData();
    try {
        return runner.update(sql, values);
    } catch (Exception e) {
        LOGGER.error("", e);
        return 0;
    }
}
 

protected void setCurrentSchema(QueryRunner jdbc) throws Exception {
    jdbc.update("alter session set current_schema = " + getSchemaName());
}
 

/**
 * Process the next item in the queue.
 */
@SuppressWarnings("nls")
protected void processQueue() {
    try {
        RequestMetric metric = queue.take();
        QueryRunner run = new QueryRunner(ds);

        Calendar cal = Calendar.getInstance();
        cal.setTimeZone(TimeZone.getTimeZone("UTC"));
        cal.setTime(metric.getRequestStart());
        
        long rstart = cal.getTimeInMillis();
        long rend = metric.getRequestEnd().getTime();
        long duration = metric.getRequestDuration();
        cal.set(Calendar.MILLISECOND, 0);
        cal.set(Calendar.SECOND, 0);
        long minute = cal.getTimeInMillis();
        cal.set(Calendar.MINUTE, 0);
        long hour = cal.getTimeInMillis();
        cal.set(Calendar.HOUR_OF_DAY, 0);
        long day = cal.getTimeInMillis();
        cal.set(Calendar.DAY_OF_WEEK, cal.getFirstDayOfWeek());
        long week = cal.getTimeInMillis();
        cal.set(Calendar.DAY_OF_MONTH, 1);
        long month = cal.getTimeInMillis();
        String api_org_id = metric.getApiOrgId();
        String api_id = metric.getApiId();
        String api_version = metric.getApiVersion();
        String client_org_id = metric.getClientOrgId();
        String client_id = metric.getClientId();
        String client_version = metric.getClientVersion();
        String plan = metric.getPlanId();
        String user_id = metric.getUser();
        String rtype = null;
        if (metric.isFailure()) {
            rtype = "failure";
        } else if (metric.isError()) {
            rtype = "error";
        }
        long bytes_up = metric.getBytesUploaded();
        long bytes_down = metric.getBytesDownloaded();

        // Now insert a row for the metric.
        run.update("INSERT INTO gw_requests ("
                + "rstart, rend, duration, month, week, day, hour, minute, "
                + "api_org_id, api_id, api_version, "
                + "client_org_id, client_id, client_version, plan, "
                + "user_id, resp_type, bytes_up, bytes_down) VALUES ("
                + "?, ?, ?, ?, ?, ?, ?, ?,"
                + "?, ?, ?,"
                + "?, ?, ?, ?,"
                + "?, ?, ?, ?)",
                rstart, rend, duration, month, week, day, hour, minute,
                api_org_id, api_id, api_version,
                client_org_id, client_id, client_version, plan,
                user_id, rtype, bytes_up, bytes_down
                );
    } catch (InterruptedException ie) {
        // This means that the thread was stopped.
    } catch (Exception e) {
        // TODO better logging of this unlikely error
        System.err.println("Error adding metric to database:"); //$NON-NLS-1$
        e.printStackTrace();
        return;
    }
}
 

public void removeUser(String username) throws SQLException {
    QueryRunner run = new QueryRunner( H2DB.getDataSource() );
    run.update("delete from user where user_name=?", username);
}
 

@Override
protected void setCurrentSchema(QueryRunner jdbc) throws Exception {
    jdbc.update("SET search_path TO " + getSchemaName());
}
 

public void deleteOrder(long orderId) throws SQLException {
    QueryRunner run = new QueryRunner( H2DB.getDataSource() );
    run.update("delete from order where id=?", orderId);
}
 

@Before
public void reset() throws SQLException {
    QueryRunner run = new QueryRunner(ds);
    run.update("DELETE FROM gw_requests");
}
 

/**
 * Removes all of the api contracts from the database.
 * @param client
 * @param connection
 * @throws SQLException
 */
protected void unregisterApiContracts(Client client, Connection connection) throws SQLException {
    QueryRunner run = new QueryRunner();
    run.update(connection, "DELETE FROM contracts WHERE client_org_id = ? AND client_id = ? AND client_version = ?",  //$NON-NLS-1$
            client.getOrganizationId(), client.getClientId(), client.getVersion());
}