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.wso2.raspberrypi.Util.java

public static void updateRaspberryPi(RaspberryPi raspberryPi) {
    BasicDataSource ds = getBasicDataSource();
    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    try {//from ww w . jav  a  2  s.  com
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("UPDATE RASP_PI SET blink=" + raspberryPi.isBlink()
                + ",reboot=" + raspberryPi.isReboot() + ",selected=" + raspberryPi.isSelected() + ",label='"
                + raspberryPi.getLabel() + "'" + " where mac='" + raspberryPi.getMacAddress() + "'");
        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.novartis.opensource.yada.util.YADAUtils.java

/**
 * One-liner execution of a jdbc-parameter-less sql statement, returning an SQL {@link java.sql.ResultSet}.
 * <strong>Note: This method opens a db connection but DOES NOT CLOSE IT. 
 * Use the static method {@link ConnectionFactory#releaseResources(ResultSet)} to close it from 
 * the calling method</strong>/*from w w  w. ja  v a  2s . c  om*/
 * @param sql the query to execute
 * @return a {@link java.sql.ResultSet} object containing the result of the query
 * @throws YADAConnectionException when the datasource is inaccessible
 * @throws YADASQLException when the JDBC configuration or execution fails
 */
public static ResultSet executePreparedStatement(String sql) throws YADAConnectionException, YADASQLException {
    ResultSet rs = null;
    try {
        Connection c = ConnectionFactory.getConnectionFactory().getConnection(ConnectionFactory.YADA_APP);
        PreparedStatement p = c.prepareStatement(sql);
        rs = p.executeQuery();
    } catch (SQLException e) {
        throw new YADASQLException(e.getMessage(), e);
    }
    return rs;
}

From source file:de.erdesignerng.dialect.msaccess.MSAccessFileFormat.java

private static int getTableCount(Connection aConnection, String aTableName) throws SQLException {

    String theColumnName = "theCount";
    short theResult = 0;
    String theSQL = "SELECT Count(MSysObjects.Id) AS " + theColumnName + " " + "FROM MSysObjects "
            + "WHERE (MSysObjects.Name LIKE ?);";

    PreparedStatement theStatement = aConnection.prepareStatement(theSQL);
    theStatement.setString(1, aTableName);

    ResultSet theIdentificationResult = theStatement.executeQuery();

    if (theIdentificationResult != null) {
        if (theIdentificationResult.next()) {
            theResult = theIdentificationResult.getShort(theColumnName);
        }//  w  w w.ja  v a2 s  . c  om

        theIdentificationResult.close();
    }

    return theResult;

}

From source file:net.codjo.dataprocess.server.treatmenthelper.TreatmentHelper.java

public static void insertRepositoryContent(Connection con, int repositoryId, String content)
        throws SQLException, TreatmentException, TransformerException {
    Document doc;//  ww w .  j a va 2s.  com
    try {
        doc = XMLUtils.parse(content);
    } catch (Exception ex) {
        throw new TreatmentException(ex);
    }
    PreparedStatement pStmt = con.prepareStatement(
            "insert into PM_REPOSITORY_CONTENT (REPOSITORY_CONTENT_ID, REPOSITORY_ID, TREATMENT_ID, CONTENT) values (?, ?, ?, ?)");
    try {
        NodeList nodes = doc.getElementsByTagName(DataProcessConstants.TREATMENT_ENTITY_XML);
        int nbNodes = nodes.getLength();
        for (int i = 0; i < nbNodes; i++) {
            Node node = nodes.item(i);
            String treatmentId = node.getAttributes().getNamedItem("id").getNodeValue();
            if (treatmentId.length() > 50) {
                throw new TreatmentException("La taille de l'identifiant d'un traitement ('" + treatmentId
                        + "') dpasse 50 caractres.");
            }

            String contentNode = XMLUtils.nodeToString(node);
            pStmt.setInt(1, SQLUtil.getNextId(con, "PM_REPOSITORY_CONTENT", "REPOSITORY_CONTENT_ID"));
            pStmt.setInt(2, repositoryId);
            pStmt.setString(3, treatmentId);
            pStmt.setString(4, contentNode);
            pStmt.executeUpdate();
        }
    } finally {
        pStmt.close();
    }
}

From source file:com.concursive.connect.web.modules.setup.utils.SetupUtils.java

/**
 * Determines if there is an administrative user configured in the database
 *
 * @param db/* ww  w . j  a v a  2  s. co  m*/
 * @return
 */
public static boolean isAdminInstalled(Connection db) {
    int count = -1;
    try {
        PreparedStatement pst = db.prepareStatement(
                "SELECT count(*) AS recordcount " + "FROM users " + "WHERE access_admin = ? ");
        pst.setBoolean(1, true);
        ResultSet rs = pst.executeQuery();
        rs.next();
        count = rs.getInt("recordcount");
        rs.close();
        pst.close();
    } catch (Exception e) {
    }
    return count > 0;
}

From source file:com.concursive.connect.web.modules.setup.utils.SetupUtils.java

/**
 * Determines if a default project has been installed
 *
 * @param db/* w w w .  j a  v  a2 s .  c om*/
 * @return
 */
public static boolean isDefaultProjectInstalled(Connection db) {
    int count = -1;
    try {
        PreparedStatement pst = db.prepareStatement(
                "SELECT count(*) AS recordcount " + "FROM projects " + "WHERE system_default = ? ");
        pst.setBoolean(1, true);
        ResultSet rs = pst.executeQuery();
        rs.next();
        count = rs.getInt("recordcount");
        rs.close();
        pst.close();
    } catch (Exception e) {
    }
    return count > 0;
}

From source file:com.magnet.mmx.server.plugin.mmxmgmt.api.topics.MMXTopicsItemsResourceTest.java

@AfterClass
public static void cleanupDatabase() {
    final String statementStr1 = "DELETE FROM mmxApp WHERE appName LIKE '%" + "mmxtopicstagsresourcetesttopic"
            + "%'";
    final String statementStr2 = "DELETE FROM ofPubsubItem where serviceID = ? AND nodeID = ?";
    Connection conn = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt2 = null;

    try {/*from w w w  .  jav a  2s  . co m*/
        conn = UnitTestDSProvider.getDataSource().getConnection();
        pstmt1 = conn.prepareStatement(statementStr1);
        pstmt1.executeUpdate();
        pstmt2 = conn.prepareStatement(statementStr2);
        pstmt2.setString(1, SERVICE_ID);
        pstmt2.setString(2, getNodeId());
        pstmt2.executeUpdate();

    } catch (SQLException e) {
        LOGGER.error("cleanupDatabase : caught exception cleaning ofPubsubItem");
    } finally {
        CloseUtil.close(LOGGER, pstmt2, conn);
    }
}

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

public static void clearAllRaspberryPis() throws RaspberryPiException {
    List<RaspberryPi> raspberryPis = getRaspberryPis(null);
    for (RaspberryPi raspberryPi : raspberryPis) {
        if (raspberryPi.getReservedFor() != null && !raspberryPi.getReservedFor().isEmpty()) {
            throw new RaspberryPiException("Cannot clear Raspberry Pis because some are reserved");
        }/*from www .j ava  2 s .  c o m*/
    }
    System.out.println("Removing all Raspberry Pis...");
    BasicDataSource ds = getBasicDataSource();
    Connection dbConnection = null;
    PreparedStatement prepStmt = null;
    try {
        dbConnection = ds.getConnection();
        prepStmt = dbConnection.prepareStatement("DELETE FROM RASP_PI");
        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.keybox.manage.db.SystemDB.java

/**
 * updates host system record/* w  ww . j  a v  a  2  s . co  m*/
 *
 * @param hostSystem host system object
 */
public static void updateSystem(HostSystem hostSystem) {

    Connection con = null;

    try {
        con = DBUtils.getConn();

        PreparedStatement stmt = con.prepareStatement(
                "update system set display_nm=?, user=?, host=?, port=?, authorized_keys=?, status_cd=?  where id=?");
        stmt.setString(1, hostSystem.getDisplayNm());
        stmt.setString(2, hostSystem.getUser());
        stmt.setString(3, hostSystem.getHost());
        stmt.setInt(4, hostSystem.getPort());
        stmt.setString(5, hostSystem.getAuthorizedKeys());
        stmt.setString(6, hostSystem.getStatusCd());
        stmt.setLong(7, hostSystem.getId());
        stmt.execute();
        DBUtils.closeStmt(stmt);

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

}

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

/**
 * returns all systems/*  ww  w. j  av a2s.  c  o m*/
 *
 * @return system list
 */
public static List<HostSystem> getAllSystems() {

    List<HostSystem> hostSystemList = new ArrayList<HostSystem>();

    Connection con = null;

    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement("select * from  system");
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            HostSystem hostSystem = new HostSystem();
            hostSystem.setId(rs.getLong("id"));
            hostSystem.setDisplayNm(rs.getString("display_nm"));
            hostSystem.setUser(rs.getString("user"));
            hostSystem.setHost(rs.getString("host"));
            hostSystem.setPort(rs.getInt("port"));
            hostSystem.setAuthorizedKeys(rs.getString("authorized_keys"));
            hostSystem.setStatusCd(rs.getString("status_cd"));
            hostSystemList.add(hostSystem);
        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    DBUtils.closeConn(con);

    return hostSystemList;

}