Example usage for java.sql PreparedStatement setString

List of usage examples for java.sql PreparedStatement setString

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. PreparedStatement
  6. setString

Introduction

In this page you can find the example usage for java.sql PreparedStatement setString.

Prototype

void setString(int parameterIndex, String x) throws SQLException;

Source Link

Document

Sets the designated parameter to the given Java String value.

Usage

From source file:eu.sisob.uma.restserver.AuthorizationManager.java

private static boolean DBAuthorizeUserIn(String user, String pass, UserAttributes out_attributes) {
    boolean success = false;
    Connection conn = null;//from   w  ww .java  2  s .  c o  m
    PreparedStatement statement = null;
    ResultSet rs = null;
    try {
        String query = "SELECT `user_tasks_allow` as n_tasks_allow, `user_type` as account_type FROM USERS WHERE user_email = ? and user_pass = ?";
        conn = SystemManager.getInstance().getSystemDbPool().getConnection();
        statement = conn.prepareStatement(query);
        statement.setString(1, user);
        statement.setString(2, pass);

        rs = statement.executeQuery();
        if (rs.next()) {

            out_attributes.setAccountType(rs.getString("account_type"));
            out_attributes.setNTasksAllow((Integer) rs.getInt("n_tasks_allow"));

            success = true;
        } else
            success = false;
    } catch (SQLException ex) {
        ProjectLogger.LOGGER.error("", ex);
        success = false;
    } catch (Exception ex) {
        ProjectLogger.LOGGER.error("", ex);
        success = false;
    } finally {
        if (rs != null)
            try {
                rs.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        if (statement != null)
            try {
                statement.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        if (conn != null)
            try {
                conn.close();
            } catch (SQLException ex) {
                ProjectLogger.LOGGER.error("", ex);
            }

        statement = null;
        rs = null;
    }

    return success;
}

From source file:com.wso2telco.gsma.authenticators.DBUtils.java

static int saveRequestType(String msisdn, Integer requestType)
        throws SQLException, NamingException, AuthenticatorException {
    Connection connection = null;
    //        String sql = "insert into pendingussd (msisdn, requesttype) values (?,?)";
    String sql = "insert into pendingussd (msisdn, requesttype) values (?,?) ON DUPLICATE KEY UPDATE "
            + "requesttype=VALUES(requesttype)";
    try {/*from  www  . ja v a2  s. c o m*/
        connection = getConnectDBConnection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, msisdn);
        ps.setInt(2, requestType);
        ps.executeUpdate();
        return 1;
    } catch (SQLException e) {
        log.error("Error while saving request type ", e);
    } finally {
        if (connection != null) {
            connection.close();
        }
    }
    return -1;
}

From source file:com.predic8.membrane.core.interceptor.statistics.util.JDBCUtil.java

public static void setData(AbstractExchange exc, PreparedStatement prepSt, boolean idGenerated, int flag,
        String tId, String[] gatewayHCIDs) throws SQLException {
    int startIndex = 0;
    if (!idGenerated) {
        UUID id = UUID.randomUUID();
        prepSt.setLong(++startIndex, id.getLeastSignificantBits());
    }//  w w w  . j a  va  2 s  .  c  o  m
    boolean isReq = (exc.getResponse() == null);

    log.info("Handling interceptor id is: " + tId);
    log.info((isReq) ? "logging for request" : "logging for response");

    prepSt.setInt(++startIndex, (isReq) ? 200 : exc.getResponse().getStatusCode());
    prepSt.setString(++startIndex, (flag == 0) ? "REQUEST" : "RESPONSE");
    prepSt.setTimestamp(++startIndex, new Timestamp(ExchangesUtil.getDate(exc).getTime()));//skb
    prepSt.setString(++startIndex, exc.getRule().toString());
    prepSt.setString(++startIndex, exc.getRequest().getMethod());
    prepSt.setString(++startIndex, exc.getRequest().getUri());
    prepSt.setString(++startIndex,
            (gatewayHCIDs != null && !"".equals(gatewayHCIDs[2])) ? gatewayHCIDs[2] : exc.getSourceHostname());
    prepSt.setString(++startIndex,
            (gatewayHCIDs != null && !"".equals(gatewayHCIDs[3])) ? gatewayHCIDs[3] : exc.getServer());

    if (gatewayHCIDs != null) {
        prepSt.setString(++startIndex, gatewayHCIDs[0]);
        prepSt.setString(++startIndex, gatewayHCIDs[1]);
    } else {
        prepSt.setString(++startIndex, exc.getSourceHostname());
        prepSt.setString(++startIndex, exc.getServer());
    }

    prepSt.setString(++startIndex, (isReq) ? exc.getRequestContentType() : exc.getResponseContentType());
    prepSt.setInt(++startIndex, (isReq) ? exc.getRequestContentLength() : exc.getResponseContentLength());
    /*
    prepSt.setString(++ startIndex, (isReq)?null:exc.getResponseContentType());
    prepSt.setInt(++ startIndex, (isReq)?null:exc.getResponseContentLength());
    */
    prepSt.setLong(++startIndex, (isReq) ? 0 : (exc.getTimeResReceived() - exc.getTimeReqSent()));

    prepSt.setString(++startIndex, (String) getExProperty(exc, FileExchangeStore.MESSAGE_FILE_PATH));

    /* skb */
    String[] colList = { JDBCUtil.MSG_HEADER, JDBCUtil.MSG };

    if (isReq) {
        for (String col : colList) {
            log.info("processing col:" + col);

            ++startIndex;
            try {
                byte[] os = (byte[]) getExProperty(exc, col);
                if (os != null) {
                    prepSt.setBytes(startIndex, os);
                } else
                    prepSt.setBytes(startIndex, null);

            } catch (Exception ex) {
                prepSt.setBytes(startIndex, null);
            }
        }
    } else {

        for (String col : colList) {
            log.info("processing col:" + col);

            ++startIndex;
            try {
                byte[] os = null;
                if (col.equals(JDBCUtil.MSG)) {
                    try {
                        os = IOUtils.toByteArray((exc.getResponse().getBodyAsStream()));
                    } catch (Exception ex) {
                        log.info(ex.toString());
                    }
                } else if (col.equals(JDBCUtil.MSG_HEADER)) {
                    Message msg2 = exc.getResponse();

                    ByteArrayOutputStream header2 = new ByteArrayOutputStream();

                    msg2.writeStartLine(header2);
                    msg2.getHeader().write(header2);
                    header2.write((Constants.CRLF).getBytes());

                    os = header2.toByteArray();
                }
                if (os != null) {
                    prepSt.setBytes(startIndex, os);
                } else
                    prepSt.setBytes(startIndex, null);

            } catch (Exception ex) {
                prepSt.setBytes(startIndex, null);
            }
        }

    }

}

From source file:com.flexive.core.security.FxDBAuthentication.java

/**
 * @param username the username//from  w w w  .  j a v  a  2s.c  om
 * @param password the password
 * @param currentTicket the UserTicket requesting the password match
 * @param ds thedatasource
 * @return returns true if the login and password match
 * @throws FxDbException on db errors
 * @throws FxLoginFailedException on authentication errors
 */
public static boolean checkLogin(String username, String password, UserTicket currentTicket, DataSource ds)
        throws FxDbException, FxLoginFailedException {
    FxContext inf = FxContext.get();

    // Avoid null pointer exceptions
    if (password == null)
        password = "";
    if (username == null)
        username = "";

    String curSql;
    PreparedStatement ps = null;
    Connection con = null;
    try {
        // Obtain a database connection
        con = ds.getConnection();
        //               1      2           3
        curSql = "SELECT a.ID,a.USERNAME,a.PASSWORD " + "FROM " + TBL_ACCOUNTS + " a " + "LEFT JOIN "
                + " (SELECT ID,ISLOGGEDIN,LAST_LOGIN,LAST_LOGIN_FROM,FAILED_ATTEMPTS,AUTHSRC FROM "
                + TBL_ACCOUNT_DETAILS
                + " WHERE APPLICATION=?) d ON a.ID=d.ID WHERE UPPER(a.LOGIN_NAME)=UPPER(?)";
        ps = con.prepareStatement(curSql);
        ps.setString(1, inf.getApplicationId());
        ps.setString(2, username);
        final ResultSet rs = ps.executeQuery();

        // Anything found
        if (rs == null || !rs.next())
            throw new FxLoginFailedException("Invalid user or password",
                    FxLoginFailedException.TYPE_USER_OR_PASSWORD_NOT_DEFINED);

        // check if the hashed password matches the hash stored in the database
        final long id = rs.getLong(1);
        final String dbUserName = rs.getString(2);
        final String hashedPass = rs.getString(3);

        // current user authorised to perform the check (ticket user id matches db user id?)
        if (id != currentTicket.getUserId() && !currentTicket.isGlobalSupervisor())
            throw new FxLoginFailedException("User not authorized to perform login check",
                    FxLoginFailedException.TYPE_USER_OR_PASSWORD_NOT_DEFINED);

        return FxSharedUtils.hashPassword(id, dbUserName, password).equals(hashedPass)
                // before 3.2.0 the default supervisor password was incorrectly hashed against the lower-cased login name
                || ("SUPERVISOR".equals(username)
                        && FxSharedUtils.hashPassword(id, "supervisor", password).equals(hashedPass));

    } catch (SQLException exc) {
        throw new FxDbException("Database error: " + exc.getMessage(), FxLoginFailedException.TYPE_SQL_ERROR);
    } finally {
        Database.closeObjects(FxDBAuthentication.class, con, ps);
    }
}

From source file:com.wso2telco.dbUtil.DataBaseConnectUtils.java

/**
 * Update user details in Back Channeling Scenario : update oauth code
 *
 * @param correlationId unique ID of the request
 * @param token         access token//from   ww  w  .j ava2  s . c  o m
 */
public static void updateTokenInBackChannel(String correlationId, String token)
        throws ConfigurationException, CommonAuthenticatorException {

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    String updateUserDetailsQuery = "update backchannel_request_details set access_token=? where correlation_id=?";

    try {
        connection = getConnectDBConnection();

        if (log.isDebugEnabled()) {
            log.debug("Executing the query " + updateUserDetailsQuery);
        }

        preparedStatement = connection.prepareStatement(updateUserDetailsQuery);
        preparedStatement.setString(1, token);
        preparedStatement.setString(2, correlationId);
        preparedStatement.executeUpdate();

    } catch (SQLException e) {
        handleException("Error occurred while updating user details for : " + correlationId + "in "
                + "BackChannel Scenario.", e);
    } catch (NamingException e) {
        throw new ConfigurationException("DataSource could not be found in mobile-connect.xml");
    } finally {
        closeAllConnections(preparedStatement, connection);
    }
}

From source file:com.att.pirates.controller.GlobalDataController.java

private static LinkedList<Company> getCummulativeNotesForPrismId(String prismId) {

    LinkedList<Company> notes = new LinkedList<Company>();
    ResultSet rs = null;/*from  w w  w .  j a  va 2s  .c om*/
    Connection conn = null;
    PreparedStatement preparedStatement = null;

    try {
        conn = DBUtility.getDBConnection();
        // SQL query command
        String SQL = " SELECT NoteId, ApplicationName " + "  ,n.Notes " + "  ,n.DateCreated " + "  ,e.FullName "
                + "  FROM CummulativeNotes n Join ATT_Employees e " + "  On e.UUID = n.CreatedBy "
                + "  where n.PRISMId = ? " + "  Order By n.DateCreated Desc ";

        preparedStatement = conn.prepareStatement(SQL);
        preparedStatement.setString(1, prismId);
        rs = preparedStatement.executeQuery();

        while (rs.next()) {
            String AppName = rs.getString("ApplicationName");
            String note = rs.getString("Notes");
            String dateCreated = rs.getString("DateCreated");
            String createdBy = rs.getString("FullName");
            int noteId = rs.getInt("NoteId");

            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            SimpleDateFormat yFormat = new SimpleDateFormat("MM/dd/yyyy");
            dateCreated = yFormat.format(format.parse(dateCreated));
            String rowId = "row_" + Integer.toString(noteId);
            String edit = "<a href='#' class='glyphicon glyphicon-pencil' data-toggle='modal' data-target='#updateNoteModal' onclick='return NoteRowButtonClickedPerNote(this);' id='btnNoteEdit_"
                    + Integer.toString(noteId) + "' ></a> ";

            // Company p = new Company(Integer.toString(noteId),AppName,note,createdBy,dateCreated, rowId);
            Company p = new Company(rowId, AppName, note, createdBy, dateCreated, rowId, edit);
            notes.add(p);
        }
    } catch (SQLException e) {
        logger.error(e.getMessage());
    } catch (Exception e) {
        logger.error(e.getMessage());
    } finally {
        try {
            if (rs != null)
                rs.close();
        } catch (Exception e) {
        }
        ;
        try {
            if (preparedStatement != null)
                preparedStatement.close();
        } catch (Exception e) {
        }
        ;
        try {
            if (conn != null)
                conn.close();
        } catch (Exception e) {
        }
        ;
    }

    return notes;
}

From source file:com.autentia.tnt.bill.migration.support.MigratedInformationRecoverer.java

/**
 * Recupera la suma total de todos los conceptos de todas las facturas del tipo que se envie por parametro
 * @param billType tipo de factura/* w  w w . j a v a  2 s  .  c o  m*/
 */
public static double getTotalFacturasMigrated(String billType) throws Exception {

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    LineNumberReader file = null;
    double result = -1;

    try {
        log.info("RECOVERING TOTAL FACTURAS " + billType + " MIGRADAS");

        // connect to database
        Class.forName(BillToBillPaymentMigration.DATABASE_DRIVER);
        con = DriverManager.getConnection(BillToBillPaymentMigration.DATABASE_CONNECTION,
                BillToBillPaymentMigration.DATABASE_USER, BillToBillPaymentMigration.DATABASE_PASS); //NOSONAR
        con.setAutoCommit(false); // DATABASE_PASS es nula               

        String sql = "SELECT sum(bp.amount) FROM BillPayment bp, Bill b where bp.billId = b.id and b.billType = ?";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, billType);

        rs = pstmt.executeQuery();
        while (rs.next()) {
            result = rs.getDouble(1);
            log.info("\t" + result);
        }

    } catch (Exception e) {
        log.error("FAILED: WILL BE ROLLED BACK: ", e);
        if (con != null) {
            con.rollback();
        }

    } finally {
        cierraFichero(file);
        liberaConexion(con, pstmt, rs);
    }
    return result;
}

From source file:com.flexive.core.security.FxDBAuthentication.java

/**
 * Mark a user as no longer active in the database.
 *
 * @param ticket the ticket of the user/*  ww  w .j ava  2s.c  o  m*/
 * @throws javax.security.auth.login.LoginException
 *          if the function failed
 */
public static void logout(UserTicket ticket) throws LoginException {
    PreparedStatement ps = null;
    String curSql;
    Connection con = null;
    FxContext inf = FxContext.get();
    try {

        // Obtain a database connection
        con = Database.getDbConnection();

        // EJBLookup user in the database, combined with a update statement to make sure
        // nothing changes between the lookup/set ISLOGGEDIN flag.
        curSql = "UPDATE " + TBL_ACCOUNT_DETAILS + " SET ISLOGGEDIN=? WHERE ID=? AND APPLICATION=?";
        ps = con.prepareStatement(curSql);
        ps.setBoolean(1, false);
        ps.setLong(2, ticket.getUserId());
        ps.setString(3, inf.getApplicationId());

        // Not more than one row should be affected, or the logout failed
        final int rowCount = ps.executeUpdate();
        if (rowCount > 1) {
            // Logout failed.
            LoginException le = new LoginException("Logout for user [" + ticket.getUserId() + "] failed");
            LOG.error(le);
            throw le;
        }

    } catch (SQLException exc) {
        LoginException le = new LoginException("Database error: " + exc.getMessage());
        LOG.error(le);
        throw le;
    } finally {
        Database.closeObjects(FxDBAuthentication.class, con, ps);
    }
}

From source file:com.autentia.tnt.bill.migration.support.OriginalInformationRecoverer.java

/**
 * Recupera la fecha de pago o cobro de cada una de las facturas cuyo tipo se envia por parametro
 * @param billType tipo de factura//from ww  w.  j  a va  2s.  co  m
 */
public static Date[] getFechaFacturaOriginal(String billType) throws Exception {
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    LineNumberReader file = null;
    Date[] result = new Date[0];

    try {
        log.info("RECOVERING FECHAS " + billType + " ORIGINALES");

        // connect to database
        Class.forName(BillToBillPaymentMigration.DATABASE_DRIVER);
        con = DriverManager.getConnection(BillToBillPaymentMigration.DATABASE_CONNECTION,
                BillToBillPaymentMigration.DATABASE_USER, BillToBillPaymentMigration.DATABASE_PASS); //NOSONAR
        con.setAutoCommit(false); // DATABASE_PASS vacia
        String sql = "SELECT date_add(creationDate, INTERVAL expiration DAY) as date FROM Bill B where billType = ? order by date";
        pstmt = con.prepareStatement(sql);
        pstmt.setString(1, billType);
        rs = pstmt.executeQuery();

        rs.last();
        result = new Date[rs.getRow()];
        rs.beforeFirst();
        int counter = 0;

        while (rs.next()) {
            result[counter] = rs.getDate(1);
            log.info("\t" + result[counter]);
            counter++;
        }
        con.commit();
    } catch (Exception e) {
        log.error("FAILED: WILL BE ROLLED BACK: ", e);
        if (con != null) {
            con.rollback();
        }
    } finally {
        cierraFichero(file);
        liberaConexion(con, pstmt, rs);
    }
    return result;
}

From source file:org.hxzon.util.db.springjdbc.StatementCreatorUtils.java

private static void setValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName, Integer scale,
        Object inValue) throws SQLException {

    if (inValue instanceof SqlTypeValue) {
        ((SqlTypeValue) inValue).setTypeValue(ps, paramIndex, sqlType, typeName);
    } else if (inValue instanceof SqlValue) {
        ((SqlValue) inValue).setValue(ps, paramIndex);
    } else if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR
            || (sqlType == Types.CLOB && isStringValue(inValue.getClass()))) {
        ps.setString(paramIndex, inValue.toString());
    } else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) {
        if (inValue instanceof BigDecimal) {
            ps.setBigDecimal(paramIndex, (BigDecimal) inValue);
        } else if (scale != null) {
            ps.setObject(paramIndex, inValue, sqlType, scale);
        } else {//from  w w w. j  ava 2  s .  c  o  m
            ps.setObject(paramIndex, inValue, sqlType);
        }
    } else if (sqlType == Types.DATE) {
        if (inValue instanceof java.util.Date) {
            if (inValue instanceof java.sql.Date) {
                ps.setDate(paramIndex, (java.sql.Date) inValue);
            } else {
                ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime()));
            }
        } else if (inValue instanceof Calendar) {
            Calendar cal = (Calendar) inValue;
            ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal);
        } else {
            ps.setObject(paramIndex, inValue, Types.DATE);
        }
    } else if (sqlType == Types.TIME) {
        if (inValue instanceof java.util.Date) {
            if (inValue instanceof java.sql.Time) {
                ps.setTime(paramIndex, (java.sql.Time) inValue);
            } else {
                ps.setTime(paramIndex, new java.sql.Time(((java.util.Date) inValue).getTime()));
            }
        } else if (inValue instanceof Calendar) {
            Calendar cal = (Calendar) inValue;
            ps.setTime(paramIndex, new java.sql.Time(cal.getTime().getTime()), cal);
        } else {
            ps.setObject(paramIndex, inValue, Types.TIME);
        }
    } else if (sqlType == Types.TIMESTAMP) {
        if (inValue instanceof java.util.Date) {
            if (inValue instanceof java.sql.Timestamp) {
                ps.setTimestamp(paramIndex, (java.sql.Timestamp) inValue);
            } else {
                ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
            }
        } else if (inValue instanceof Calendar) {
            Calendar cal = (Calendar) inValue;
            ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
        } else {
            ps.setObject(paramIndex, inValue, Types.TIMESTAMP);
        }
    } else if (sqlType == SqlTypeValue.TYPE_UNKNOWN) {
        if (isStringValue(inValue.getClass())) {
            ps.setString(paramIndex, inValue.toString());
        } else if (isDateValue(inValue.getClass())) {
            ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
        } else if (inValue instanceof Calendar) {
            Calendar cal = (Calendar) inValue;
            ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
        } else {
            // Fall back to generic setObject call without SQL type specified.
            ps.setObject(paramIndex, inValue);
        }
    } else {
        // Fall back to generic setObject call with SQL type specified.
        ps.setObject(paramIndex, inValue, sqlType);
    }
}