Java Code Examples for java.sql.PreparedStatement

private static Map<String,String> getKeyToDisplayMapping(Connection conn,String internalSourceName,String internalKeyFieldName,String internalDisplayFieldName) throws SQLException {
  String SQLCode="SELECT " + internalKeyFieldName + ", "+ internalDisplayFieldName;
  SQLCode+=" FROM " + internalSourceName;
  PreparedStatement statement=conn.prepareStatement(SQLCode);
  ResultSet results=statement.executeQuery();
  Map<String,String> displayLookup=new HashMap<String,String>();
  while (results.next()) {
    displayLookup.put(results.getString(internalKeyFieldName),results.getString(internalDisplayFieldName));
  }
  results.close();
  statement.close();
  return displayLookup;
}
 

/** 
 * {@inheritDoc}
 */
@Override public int load(){
  PreparedStatement ps=DB.prepareStatement("SELECT `value` FROM `server_variables` WHERE `key`='time'");
  try {
    ResultSet rs=ps.executeQuery();
    if (rs.next())     return Integer.parseInt(rs.getString("value"));
  }
 catch (  SQLException e) {
    Logger.getLogger(MySQL5GameTimeDAO.class).error("Error loading last saved server time",e);
  }
 finally {
    DB.close(ps);
  }
  return 0;
}
 

@Override public void track(TrackEvent event) throws ServiceException {
  PreparedStatement statement=null;
  Connection connection=null;
  try {
    connection=getConnection();
    String query="INSERT INTO trackevent (id, type, site, campaign, user, bannerid, ip, created) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    statement=connection.prepareStatement(query);
    statement.setString(1,event.getId());
    statement.setString(2,event.getType().getName());
    statement.setString(3,event.getSite());
    statement.setString(4,event.getCampaign());
    statement.setString(5,event.getUser());
    statement.setString(6,event.getBannerId());
    statement.setString(7,event.getIp());
    statement.setTimestamp(8,new Timestamp(event.getTime()));
    statement.execute();
  }
 catch (  Exception e) {
    throw new ServiceException(e.getMessage());
  }
 finally {
    closeBoth(connection,statement);
  }
}
 

@Override @Transactional(readOnly=true) public List<ResolverEntry> getResolverEntries(SingleResolverRequest request){
  try {
    ResolverDaoHelper helper=new ResolverDaoHelper();
    PreparedStatement stmt=helper.createPreparedStatement(getConnection());
    helper.fillPreparedStatement(request,stmt);
    List<ResolverEntry> result=helper.extractData(stmt.executeQuery());
    return result;
  }
 catch (  SQLException ex) {
    log.error("Could not get resolver entries from database",ex);
    return new LinkedList<ResolverEntry>();
  }
}
 

private boolean delete(String sql,KUID id) throws SQLException {
  PreparedStatement ps=cm.prepareStatement(sql);
  try {
    setBytes(ps,1,id);
    return ps.executeUpdate() == 1;
  }
  finally {
    Utils.close(ps);
  }
}
 

/** 
 * Inserts an employee in Employee table.
 * @param employee : employee to insert
 * @throws SQLException
 */
public void addEmployee(Employee employee) throws SQLException {
  Connection connection=null;
  PreparedStatement preparedStatement=null;
  try {
    connection=DBConnectionUtil.getConnection();
    preparedStatement=connection.prepareStatement("insert into Employees(firstName, lastName, department) values (?, ?, ?)");
    preparedStatement.setString(1,employee.getFirstName());
    preparedStatement.setString(2,employee.getLastName());
    preparedStatement.setString(3,employee.getDepartment());
    preparedStatement.executeUpdate();
  }
  finally {
    try {
      if (preparedStatement != null) {
        preparedStatement.close();
      }
      if (connection != null) {
        connection.close();
      }
    }
 catch (    SQLException e) {
      e.printStackTrace();
    }
  }
}
 

/** 
 * executeSql.
 * @param sql sql
 * @param paramList paramList
 * @param connection connection
 * @return issuccess
 * @throws SQLException SQLException
 */
public static boolean executeSql(final String sql,final List<Object> paramList,final Connection connection) throws SQLException {
  LOGGER.log(Level.FINEST,"executeSql: {0}",sql);
  final PreparedStatement preparedStatement=connection.prepareStatement(sql);
  for (int i=1; i <= paramList.size(); i++) {
    preparedStatement.setObject(i,paramList.get(i - 1));
  }
  final boolean isSuccess=preparedStatement.execute();
  preparedStatement.close();
  return isSuccess;
}
 

public int[] executeBatch(Connection connection,I input) throws BeeException {
  StopWatch sw=new StopWatch();
  PreparedStatement pstmt=null;
  try {
    pstmt=createStatement(connection,input);
    prepare.invoke(pstmt,input,new Index());
    int[] rcs=pstmt.executeBatch();
    log.fine("batch: rcs=" + ToStringUtil.toString(rcs));
    return rcs;
  }
 catch (  SQLException e) {
    log.fine("batch: ex=" + e);
    throw new BeeException(e);
  }
 finally {
    log.fine("batch: time=" + sw.getSeconds());
    try {
      pstmt.close();
    }
 catch (    Exception ignore) {
    }
  }
}
 

/** 
 * <b>prepare</b><br> &nbsp;&nbsp;Prepares to send a query to the database. <br> <br>
 * @param query - the SQL query to prepare to send to the database.
 * @return the prepared statement.
 */
public PreparedStatement prepare(final String query){
  try {
    final PreparedStatement ps;
synchronized (connection) {
      ps=connection.prepareStatement(query);
    }
    return ps;
  }
 catch (  final SQLException e) {
    if (!e.toString().contains("not return ResultSet")) {
      this.writeError("SQL exception in prepare(): " + e.getMessage(),false);
    }
  }
  return null;
}
 

/** 
 * Returns a <code>Map<String, Object></code> of properties stored in the database
 * @throws Exception
 */
synchronized Map<String,Object> load() throws Exception {
  Map<String,Object> map=new HashMap<String,Object>();
  Connection conn=null;
  PreparedStatement pstmt=null;
  ResultSet rs=null;
  try {
    conn=getConnection();
    pstmt=conn.prepareStatement(query.toString());
    rs=pstmt.executeQuery();
    while (rs.next()) {
      String key=(String)rs.getObject(keyColumnName);
      Object value=rs.getObject(valueColumnName);
      map.put(key,value);
    }
  }
 catch (  SQLException e) {
    throw e;
  }
 finally {
    close(conn,pstmt,rs);
  }
  return map;
}
 

/** 
 * {@inheritDoc}
 */
@Override public long save(final Stock stock){
  validateNoNull(stock,"stock");
  validateNotEmpty(stock.getSymbol(),"symbol");
  KeyHolder keyHolder=new GeneratedKeyHolder();
  jdbcTemplate.update(new PreparedStatementCreator(){
    public PreparedStatement createPreparedStatement(    Connection connection) throws SQLException {
      PreparedStatement ps=connection.prepareStatement("insert into Stock(name, symbol, value, date) values (?, ?, ?, ?)",new String[]{"id"});
      int index=1;
      ps.setString(index++,stock.getName());
      ps.setString(index++,stock.getSymbol());
      ps.setBigDecimal(index++,stock.getValue());
      ps.setDate(index++,new java.sql.Date(stock.getDate().getTime()));
      return ps;
    }
  }
,keyHolder);
  stock.setId((Long)keyHolder.getKey());
  return stock.getId();
}
 

public static void adduser(int checkid,String player,String email,String password,String ipAddress) throws SQLException {
  if (checkid == 1) {
    long timestamp=System.currentTimeMillis() / 1000;
    String hash=hash(password);
    PreparedStatement ps;
    ps=MySQL.mysql.prepareStatement("INSERT INTO `" + Config.script_tableprefix + "users"+ "` (`email`, `password`, `name`, `lastdate`, `reg_date`, `logged_ip`, `info`, `signature`, `favorites`, `xfields`)  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",1);
    ps.setString(1,email);
    ps.setString(2,hash);
    ps.setString(3,player);
    ps.setLong(4,timestamp);
    ps.setLong(5,timestamp);
    ps.setString(6,ipAddress);
    ps.setString(7,"");
    ps.setString(8,"");
    ps.setString(9,"");
    ps.setString(10,"");
    Util.logging.mySQL(ps.toString());
    ps.executeUpdate();
    ps.close();
  }
}
 

@Override public synchronized boolean isAuthAvailable(String user){
  Connection con=null;
  PreparedStatement pst=null;
  ResultSet rs=null;
  try {
    con=conPool.getValidConnection();
    pst=con.prepareStatement("SELECT * FROM " + tableName + " WHERE "+ columnName+ "=?;");
    pst.setString(1,user);
    rs=pst.executeQuery();
    return rs.next();
  }
 catch (  SQLException ex) {
    ConsoleLogger.showError(ex.getMessage());
    return false;
  }
catch (  TimeoutException ex) {
    ConsoleLogger.showError(ex.getMessage());
    return false;
  }
 finally {
    close(rs);
    close(pst);
    close(con);
  }
}
 

@Override public synchronized boolean isAuthAvailable(String user){
  Connection con=null;
  PreparedStatement pst=null;
  ResultSet rs=null;
  try {
    con=conPool.getValidConnection();
    pst=con.prepareStatement("SELECT * FROM " + tableName + " WHERE "+ columnName+ "=?;");
    pst.setString(1,user);
    rs=pst.executeQuery();
    return rs.next();
  }
 catch (  SQLException ex) {
    ConsoleLogger.showError(ex.getMessage());
    return false;
  }
catch (  TimeoutException ex) {
    ConsoleLogger.showError(ex.getMessage());
    return false;
  }
 finally {
    close(rs);
    close(pst);
    close(con);
  }
}
 

@Override public synchronized boolean isAuthAvailable(String user){
  Connection con=null;
  PreparedStatement pst=null;
  ResultSet rs=null;
  try {
    con=conPool.getValidConnection();
    pst=con.prepareStatement("SELECT * FROM " + tableName + " WHERE "+ columnName+ "=?;");
    pst.setString(1,user);
    rs=pst.executeQuery();
    return rs.next();
  }
 catch (  SQLException ex) {
    ConsoleLogger.showError(ex.getMessage());
    return false;
  }
catch (  TimeoutException ex) {
    ConsoleLogger.showError(ex.getMessage());
    return false;
  }
 finally {
    close(rs);
    close(pst);
    close(con);
  }
}
 

/** 
 * {@inheritDoc}
 */
public int getPatchLevel() throws MigrationException {
  createPatchStoreIfNeeded();
  Connection conn=null;
  PreparedStatement stmt=null;
  ResultSet rs=null;
  try {
    conn=context.getConnection();
    stmt=conn.prepareStatement(getSql("level.read"));
    stmt.setString(1,context.getSystemName());
    rs=stmt.executeQuery();
    if (rs.next()) {
      return rs.getInt(1);
    }
    SqlUtil.close(conn,stmt,rs);
    conn=null;
    stmt=null;
    rs=null;
    return 0;
  }
 catch (  SQLException e) {
    throw new MigrationException("Unable to get patch level",e);
  }
 finally {
    SqlUtil.close(conn,stmt,rs);
  }
}
 

public void nullSafeSet(PreparedStatement preparedStatement,Object value,int index) throws HibernateException, SQLException {
  if (null == value) {
    preparedStatement.setNull(index,Types.VARCHAR);
  }
 else {
    preparedStatement.setString(index,((Enum)value).name());
  }
}
 

PreparedStatement getPreparedStatement(Connection connection,String query) throws SQLException {
  if (!statementCache.containsKey(connection)) {
    statementCache.put(connection,new HashMap<String,PreparedStatement>());
  }
  HashMap<String,PreparedStatement> preparedStatementMap=statementCache.get(connection);
  return preparedStatementMap.get(query);
}
 

private static void doUpdate(PreparedStatement query,Object... newVals) throws SQLException {
  ResultSet rs=query.executeQuery();
  try {
    while (rs.next()) {
      int idx=1;
      for (      Object v : newVals) {
        if (v instanceof String)         rs.updateString(idx++,(String)v);
 else         if (v instanceof Boolean)         rs.updateBoolean(idx++,(Boolean)v);
 else         if (v instanceof Long)         rs.updateLong(idx++,(Long)v);
 else         throw new Error("Unknown value type " + v.getClass() + " ("+ v+ ")");
      }
      rs.updateRow();
    }
  }
  finally {
    rs.close();
  }
}
 

public void apply(final int position,final PreparedStatement statement,final StatementContext ctx) throws SQLException {
  if (value != null) {
    statement.setString(position,value);
  }
 else {
    statement.setNull(position,Types.VARCHAR);
  }
}
 

public void nullSafeSet(PreparedStatement statement,Object value,int index) throws HibernateException, SQLException {
  if (value == null) {
    statement.setNull(index,Types.NULL);
  }
 else {
    Long id=(Long)value;
    statement.setLong(index,id);
  }
}
 

@SuppressWarnings("unchecked") @Override public String[] writeSetAssArgs(final PreparedStatement pst,final int start,Object val,final int type) throws SQLException, IOException {
  if (val instanceof List) {
    List<String> values=(List<String>)val;
    val=values.toArray(new String[values.size()]);
  }
  if (val instanceof String[]) {
    String[] values=(String[])val;
    String[] result=new String[values.length];
    int tally=0;
    for (    String singleVal : values) {
      PreparedStatementWriter.write(pst,start + tally,type,singleVal);
      result[tally++]=argString(getLabel(singleVal,type),type);
    }
    return result;
  }
 else {
    return super.writeSetAssArgs(pst,start,val,type);
  }
}
 

@Override @SuppressWarnings({"deprecation","unchecked"}) public void nullSafeSet(PreparedStatement st,Object value,int index) throws HibernateException, SQLException {
  if (value == null) {
    sct.nullSafeSet(st,null,index);
  }
 else {
    sct.nullSafeSet(st,toNonNullInternalType((E)value),index);
  }
}
 

public void setValues(PreparedStatement ps) throws SQLException {
  if (this.args != null) {
    for (int i=0; i < this.args.length; i++) {
      Object arg=this.args[i];
      if (arg instanceof SqlParameterValue) {
        SqlParameterValue paramValue=(SqlParameterValue)arg;
        StatementCreatorUtils.setParameterValue(ps,i + 1,paramValue,paramValue.getValue());
      }
 else {
        StatementCreatorUtils.setParameterValue(ps,i + 1,SqlTypeValue.TYPE_UNKNOWN,arg);
      }
    }
  }
}
 

public PreparedStatement prepareStatement(String sql) throws SQLException {
  StatementHandle result=null;
  String cacheKey=null;
  checkClosed();
  try {
    long statStart=0;
    if (this.statisticsEnabled) {
      statStart=System.nanoTime();
    }
    if (this.statementCachingEnabled) {
      cacheKey=sql;
      result=this.preparedStatementCache.get(cacheKey);
    }
    if (result == null) {
      result=new PreparedStatementHandle(this.connection.prepareStatement(sql),sql,this,cacheKey,this.preparedStatementCache);
      result.setLogicallyOpen();
    }
    if (this.pool.closeConnectionWatch && this.statementCachingEnabled) {
      result.setOpenStackTrace(this.pool.captureStackTrace(STATEMENT_NOT_CLOSED));
    }
    if (this.closeOpenStatements) {
      this.trackedStatement.put(result,this.detectUnclosedStatements ? this.pool.captureStackTrace(STATEMENT_NOT_CLOSED) : this.noStackTrace);
    }
    if (this.statisticsEnabled) {
      this.statistics.addStatementPrepareTime(System.nanoTime() - statStart);
      this.statistics.incrementStatementsPrepared();
    }
  }
 catch (  SQLException e) {
    throw markPossiblyBroken(e);
  }
  return (PreparedStatement)result;
}