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.sql.SECExceptions.java

/**
 * Gathers a list of errors based on type and count total of them.
 *
 * @return/* ww w.j  a v  a  2  s .  c om*/
 */
public static List<SystemErrorModel> getErrorCounts() {
    List<SystemErrorModel> list = new ArrayList();
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        conn = DBConnection.connectToDB();
        String sql = "SELECT exceptionType, COUNT(*) AS 'num' " + "FROM SECExceptions "
                + "WHERE timeOccurred >= CAST(CURRENT_TIMESTAMP AS DATE) " + "GROUP BY exceptionType";
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        while (rs.next()) {
            SystemErrorModel item = new SystemErrorModel();
            item.setExceptionType(rs.getString("exceptionType") == null ? "" : rs.getString("exceptionType"));
            item.setNumber(rs.getInt("num"));
            list.add(item);
        }
    } catch (SQLException ex) {
        ExceptionHandler.Handle(ex);
    } finally {
        DbUtils.closeQuietly(conn);
        DbUtils.closeQuietly(ps);
        DbUtils.closeQuietly(rs);
    }
    return list;
}

From source file:com.wso2telco.core.mnc.resolver.mncrange.McnRangeDbUtil.java

/**
 * Gets the mcc number ranges./* w  ww.  ja v a  2s  .  co  m*/
 *
 * @param mcc the mcc
 * @return the mcc number ranges
 * @throws MobileNtException the mobile nt exception
 */
public static List<NumberRange> getMccNumberRanges(String mcc) throws MobileNtException {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    String sql = "SELECT mnccode,brand,rangefrom,rangeto " + "FROM mcc_number_ranges " + "WHERE mcccode = ?";

    List<NumberRange> lstranges = new ArrayList();

    try {
        conn = DbUtils.getDbConnection(DataSourceNames.WSO2TELCO_DEP_DB);
        ps = conn.prepareStatement(sql);
        ps.setString(1, mcc);
        rs = ps.executeQuery();
        while (rs.next()) {
            lstranges.add(new NumberRange(rs.getLong("rangefrom"), rs.getLong("rangeto"),
                    rs.getString("mnccode"), rs.getString("brand")));
        }
    } catch (Exception e) {
        handleException("Error occured while getting Number ranges for mcc: " + mcc + " from the database", e);
    } finally {
        DbUtils.closeAllConnections(ps, conn, rs);
    }
    return lstranges;
}

From source file:com.wso2telco.mnc.resolver.mncrange.McnRangeDbUtil.java

public static String getMncBrand(String mcc, String mnc) throws MobileNtException {
    Connection conn = null;
    PreparedStatement ps = null;//from  w  w  w . j  ava  2s. c  om
    ResultSet rs = null;
    String sql = "SELECT operatorname " + "FROM operators " + "WHERE mcc = ? AND mnc = ?";

    String mncBrand = null;

    try {
        conn = getAxiataDBConnection();
        ps = conn.prepareStatement(sql);
        ps.setString(1, mcc);
        ps.setString(2, mnc);
        rs = ps.executeQuery();
        if (rs.next()) {
            mncBrand = rs.getString("operatorname");
        }
    } catch (SQLException e) {
        handleException("Error occured while getting Brand for for mcc: and mnc: " + mcc + ":" + mnc
                + " from the database", e);
    } finally {
        McnRangeDbUtil.closeAllConnections(ps, conn, rs);
    }
    return mncBrand;
}

From source file:libepg.util.db.AboutDB.java

/**
* ?DB?????//from   ww w .j a  va  2s .  c  om
* @param src 
* @param conn DB??
* @throws java.sql.SQLException 
* @see libepg.util.db.AboutDB#CRATE_TABLE
* @see java.sql.Connection#createStatement() 
*/
public static synchronized void convertToTable(List<TsPacket> src, Connection conn) throws SQLException {
    Statement stmt = conn.createStatement();
    //?
    stmt.executeUpdate(AboutDB.CRATE_TABLE);
    //?PID??????
    for (TsPacket tsp : src) {
        PreparedStatement insertStatement = conn.prepareStatement(INSERT_SQL);
        insertStatement.setInt(1, tsp.getPid());
        insertStatement.setInt(2, tsp.getContinuity_counter());
        insertStatement.setInt(3, 0);
        insertStatement.setBytes(4, tsp.getData());
        insertStatement.executeUpdate();
    }
}

From source file:mangotiger.sql.SQL.java

private static PreparedStatement newPreparedStatement(final Connection connection, final String sql,
        final Object[] parameters) throws SQLException {
    if (log().isDebugEnabled()) {
        log().debug(asString(connection));
        log().debug(asString(sql, parameters));
    }/*from w  ww.  j a  v a  2  s  .  c  om*/
    final PreparedStatement statement = connection.prepareStatement(sql);
    if (parameters != null) {
        for (int i = 0; i < parameters.length; ++i) {
            statement.setObject(i + 1, parameters[i]);
        }
    }
    return statement;
}

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

/**
 * returns users based on sort order defined
 * @param sortedSet object that defines sort order
 * @return sorted user list/*from   w  w  w .  ja v  a  2 s .c  o m*/
 */
public static SortedSet getUserSet(SortedSet sortedSet) {

    ArrayList<User> userList = new ArrayList<User>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  users where enabled=true " + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setEmail(rs.getString("email"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setAuthType(rs.getString("auth_type"));
            user.setUserType(rs.getString("user_type"));
            userList.add(user);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(userList);
    return sortedSet;
}

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

/**
 * returns all admin users based on sort order defined
 * @param sortedSet object that defines sort order
 * @return sorted user list//from   w w  w.j av  a2  s. co  m
 */
public static SortedSet getAdminUserSet(SortedSet sortedSet) {

    ArrayList<User> userList = new ArrayList<User>();

    String orderBy = "";
    if (sortedSet.getOrderByField() != null && !sortedSet.getOrderByField().trim().equals("")) {
        orderBy = "order by " + sortedSet.getOrderByField() + " " + sortedSet.getOrderByDirection();
    }
    String sql = "select * from  users where enabled=true and user_type like '" + User.ADMINISTRATOR + "' "
            + orderBy;

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            User user = new User();
            user.setId(rs.getLong("id"));
            user.setEmail(rs.getString("email"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            user.setAuthType(rs.getString("auth_type"));
            user.setUserType(rs.getString("user_type"));
            userList.add(user);

        }
        DBUtils.closeRs(rs);
        DBUtils.closeStmt(stmt);

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

    sortedSet.setItemList(userList);
    return sortedSet;
}

From source file:mitll.xdata.dataset.kiva.ingest.KivaIngest.java

@SuppressWarnings("unchecked")
public static void loadTable(String tableName, String schemaFilename, String dataFilename) throws Exception {
    Object[] temp = processSchema(schemaFilename);
    List<String> names = (List<String>) temp[0];
    List<String> types = (List<String>) temp[1];

    Class.forName("org.h2.Driver");
    Connection connection = DriverManager.getConnection("jdbc:h2:tcp://localhost//h2data/kiva/kiva", "sa", "");

    String createSQL = createCreateSQL(tableName, names, types);
    String insertSQL = createInsertSQL(tableName, names);

    PreparedStatement statement = connection.prepareStatement(createSQL);
    statement.executeUpdate();//from   ww w.j  a v a 2 s .co  m
    statement.close();

    statement = connection.prepareStatement(insertSQL);

    BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(dataFilename), "UTF-8"));
    String line = null;
    int count = 0;
    long t0 = System.currentTimeMillis();
    while ((line = br.readLine()) != null) {
        count++;
        List<String> values = split(line, "\t");
        executePreparedStatement(statement, types, values);
        if (count % 10000 == 0) {
            System.out.println(
                    "count = " + count + "; " + (System.currentTimeMillis() - 1.0 * t0) / count + " ms/insert");
        }
    }
    br.close();

    statement.close();

    long t1 = System.currentTimeMillis();
    System.out.println("total count = " + count);
    System.out.println("total time = " + ((t1 - t0) / 1000.0) + " s");
    System.out.println((t1 - 1.0 * t0) / count + " ms/insert");
    System.out.println((1000.0 * count / (t1 - 1.0 * t0)) + " inserts/s");
}

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

/**
 * updates existing user/*  w w  w  . ja  v a2  s  . com*/
 * @param user user object
 */
public static void updateUserNoCredentials(User user) {

    Connection con = null;
    try {
        con = DBUtils.getConn();
        PreparedStatement stmt = con.prepareStatement(
                "update users set first_nm=?, last_nm=?, email=?, username=?, user_type=? where id=?");
        stmt.setString(1, user.getFirstNm());
        stmt.setString(2, user.getLastNm());
        stmt.setString(3, user.getEmail());
        stmt.setString(4, user.getUsername());
        stmt.setString(5, user.getUserType());
        stmt.setLong(6, user.getId());
        stmt.execute();
        DBUtils.closeStmt(stmt);
        if (User.ADMINISTRATOR.equals(user.getUserType())) {
            PublicKeyDB.deleteUnassignedKeysByUser(con, user.getId());
        }

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

}

From source file:dept_integration.Dept_Integbean.java

public static int getPages(Connection con) throws Exception {
    int totalcount = 0;
    PreparedStatement ps = null;//from ww  w  . j av a  2s  .  c om
    ResultSet rs = null;
    try {
        String sql = "select ceil(count(*)/10) as totalpage from department_integration";
        ps = con.prepareStatement(sql);
        rs = ps.executeQuery();
        if (rs.next()) {
            totalcount = rs.getInt("totalpage");
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
    return totalcount;
}