Example usage for java.sql Connection prepareStatement

List of usage examples for java.sql Connection prepareStatement

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareStatement

Introduction

In this page you can find the example usage for java.sql Connection prepareStatement.

Prototype

PreparedStatement prepareStatement(String sql) throws SQLException;

Source Link

Document

Creates a PreparedStatement object for sending parameterized SQL statements to the database.

Usage

From source file:com.tethrnet.manage.db.SessionAuditDB.java

/**
 * returns terminal logs for user session for host system
 *
 * @param sessionId     session id//ww w .j  a v a 2s.  com
 * @param instanceId    instance id for terminal session
 * @return session output for session
 */
public static List<SessionOutput> getTerminalLogsForSession(Connection con, Long sessionId,
        Integer instanceId) {

    List<SessionOutput> outputList = new LinkedList<SessionOutput>();
    try {
        PreparedStatement stmt = con.prepareStatement(
                "select * from terminal_log where instance_id=? and session_id=? order by log_tm asc");
        stmt.setLong(1, instanceId);
        stmt.setLong(2, sessionId);
        ResultSet rs = stmt.executeQuery();
        String output = "";
        while (rs.next()) {
            output = output + rs.getString("output");
        }

        output = output.replaceAll("\\u0007|\u001B\\[K|\\]0;|\\[\\d\\d;\\d\\dm|\\[\\dm", "");
        while (output.contains("\b")) {
            output = output.replaceFirst(".\b", "");
        }
        DBUtils.closeRs(rs);

        SessionOutput sessionOutput = new SessionOutput();
        sessionOutput.setSessionId(sessionId);
        sessionOutput.setInstanceId(instanceId);
        sessionOutput.getOutput().append(output);

        outputList.add(sessionOutput);

        DBUtils.closeRs(rs);

        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    return outputList;

}

From source file:com.wso2telco.dep.reportingservice.dao.OperatorDAO.java

/**
 * Gets the all operators./* ww w .  ja  v a2s  .c  o m*/
 *
 * @return the all operators
 * @throws APIMgtUsageQueryServiceClientException the API mgt usage query service client exception
 * @throws SQLException the SQL exception
 */
public static List<String> getAllOperators() throws APIMgtUsageQueryServiceClientException, SQLException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet results = null;
    String sql = "SELECT operatorname FROM " + ReportingTable.OPERATORS + "";
    List<String> op = new ArrayList<String>();
    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        log.debug("getAllOperators for ID");
        results = ps.executeQuery();
        while (results.next()) {
            String temp = results.getString("operatorname");
            op.add(temp);
        }
    } catch (Exception e) {
        log.error("Error occured while getting All Operators from the database" + e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, results);
    }
    return op;
}

From source file:ca.qc.adinfo.rouge.achievement.db.AchievementDb.java

public static boolean updateAchievement(DBManager dbManager, String key, long userId, double progress) {

    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;/*from  w w w.  ja v  a 2 s .co m*/
    String sql = null;

    sql = "INSERT INTO rouge_achievement_progress (`achievement_key`, `user_id`, `progress`) "
            + "VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE progress = GREATEST(progress, ?)";

    try {
        connection = dbManager.getConnection();
        stmt = connection.prepareStatement(sql);

        stmt.setString(1, key);
        stmt.setLong(2, userId);
        stmt.setDouble(3, progress);
        stmt.setDouble(4, progress);

        int ret = stmt.executeUpdate();

        return (ret > 0);

    } catch (SQLException e) {
        log.error(stmt);
        log.error(e);
        return false;

    } finally {

        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(stmt);
        DbUtils.closeQuietly(connection);
    }
}

From source file:edu.ucsd.library.xdre.web.StatsCollectionsReportController.java

public static long getDiskSize(String collectionId) throws SQLException {
    Connection con = null;
    PreparedStatement ps = null;/*w  ww  .  j a  va2s  .  c  o  m*/
    ResultSet rs = null;
    long size = 0;
    try {
        con = Constants.DAMS_DATA_SOURCE.getConnection();
        ps = con.prepareStatement(StatsUsage.DLP_COLLECTION_RECORD_QUERY);
        ps.setString(1, collectionId);
        rs = ps.executeQuery();
        if (rs.next()) {
            size = rs.getLong("SIZE_BYTES");
        }
    } finally {
        Statistics.close(rs);
        Statistics.close(ps);
        Statistics.close(con);
        rs = null;
        ps = null;
        con = null;
    }
    return size;
}

From source file:com.wso2.raspberrypi.Util.java

public static void updateKeyValuePair(String key, String value) {
    BasicDataSource ds = getBasicDataSource();
    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    try {/*ww  w .j av a 2  s .  co  m*/
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("UPDATE KV_PAIR SET v='" + value + "' where k='" + key + "'");
        prepStmt.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (dbConnection != null) {
                dbConnection.close();
            }
            if (prepStmt != null) {
                prepStmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:FacultyAdvisement.StudentRepository.java

public static Student read(DataSource ds, String key) throws SQLException {
    String studentSQL = "SElECT * FROM STUDENT JOIN USERTABLE on EMAIL = USERNAME WHERE EMAIL = ?";
    Student student = new Student();

    if (ds == null) {
        throw new SQLException("ds is null; Can't get data source");
    }/*from  w  ww .  j  a v  a2 s  .c o  m*/

    Connection conn = ds.getConnection();

    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }

    try {

        PreparedStatement sqlStatement = conn.prepareStatement(studentSQL);

        sqlStatement.setString(1, key);

        ResultSet result = sqlStatement.executeQuery();
        while (result.next()) {
            student.setId(result.getString("STUID"));
            student.setFirstName(result.getString("firstname"));
            student.setLastName(result.getString("lastname"));
            student.setMajorCode(result.getString("majorcode"));
            student.setPhoneNumber(result.getString("phone"));
            student.setUsername(key);
            student.setPassword(result.getString("password"));
        }

    } finally {
        conn.close();
    }

    return student;
}

From source file:FacultyAdvisement.StudentRepository.java

public static Student readById(DataSource ds, String key) throws SQLException {
    String studentSQL = "SElECT * FROM STUDENT WHERE STUID = ?";
    Student student = new Student();

    if (ds == null) {
        throw new SQLException("ds is null; Can't get data source");
    }//from   w w w  .j  av a 2  s  . c o  m

    Connection conn = ds.getConnection();

    if (conn == null) {
        throw new SQLException("conn is null; Can't get db connection");
    }

    try {

        PreparedStatement sqlStatement = conn.prepareStatement(studentSQL);

        sqlStatement.setString(1, key);

        ResultSet result = sqlStatement.executeQuery();
        while (result.next()) {
            student.setId(key);
            student.setFirstName(result.getString("firstname"));
            student.setLastName(result.getString("lastname"));
            student.setMajorCode(result.getString("majorcode"));
            student.setPhoneNumber(result.getString("phone"));
            student.setUsername(result.getString("email"));

        }

    } finally {
        conn.close();
    }

    return student;
}

From source file:com.keybox.manage.db.SystemDB.java

/**
 * deletes host system//from w  w  w. j  a v a2 s  .c o  m
 *
 * @param hostSystemId host system id
 */
public static void deleteSystem(Long hostSystemId) {

    Connection con = null;

    try {
        con = DBUtils.getConn();

        PreparedStatement stmt = con.prepareStatement("delete from system where id=?");
        stmt.setLong(1, hostSystemId);
        stmt.execute();
        DBUtils.closeStmt(stmt);

    } catch (Exception e) {
        log.error(e.toString(), e);
    }
    DBUtils.closeConn(con);

}

From source file:com.wso2.raspberrypi.Util.java

public static void releasePi(String macAddress) {
    System.out.println("Releasing RPi " + macAddress);
    BasicDataSource ds = getBasicDataSource();
    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    try {/*from   w  w w  .  j  ava2 s . c  o m*/
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("UPDATE RASP_PI SET owner='' where mac='" + macAddress + "'");
        prepStmt.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (dbConnection != null) {
                dbConnection.close();
            }
            if (prepStmt != null) {
                prepStmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

From source file:com.wso2.raspberrypi.Util.java

public static void deleteRaspberryPi(String mac) {
    System.out.println("Removing Raspberry Pi: " + mac);
    BasicDataSource ds = getBasicDataSource();
    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    try {// ww  w . j  a  v a 2s. co  m
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("DELETE FROM RASP_PI WHERE mac='" + mac + "'");
        prepStmt.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (dbConnection != null) {
                dbConnection.close();
            }
            if (prepStmt != null) {
                prepStmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}