public void testBoolean() throws Throwable { // String crtab = "create table #testBigInt (a bigint)"; String crtab = "create table #testBit (a BIT NULL)"; dropTable("#testBit"); Statement stmt = con.createStatement(); stmt.executeUpdate(crtab); stmt.executeUpdate("insert into #testBit values (NULL)"); stmt.executeUpdate("insert into #testBit values (0)"); stmt.executeUpdate("insert into #testBit values (1)"); ResultSet rs = stmt.executeQuery("select * from #testBit where a is NULL"); rs.next(); rs.getBoolean(1); rs = stmt.executeQuery("select * from #testBit where a = 0"); rs.next(); rs.getBoolean(1); rs = stmt.executeQuery("select * from #testBit where a = 1"); rs.next(); rs.getBoolean(1); stmt.close(); PreparedStatement pstmt = con.prepareStatement("insert into #testBit values (?)"); pstmt.setBoolean(1, true); assertTrue(!pstmt.execute()); assertTrue(pstmt.getUpdateCount() == 1); pstmt.setBoolean(1, false); assertTrue(!pstmt.execute()); assertTrue(pstmt.getUpdateCount() == 1); pstmt.setNull(1, java.sql.Types.BIT); assertTrue(!pstmt.execute()); assertTrue(pstmt.getUpdateCount() == 1); pstmt.close(); }
private void setParameter(PreparedStatement stmt, List<Object> paramList) throws SQLException { for (int i = 0; i < paramList.size(); i++) { Object param = paramList.get(i); if (param instanceof Arrays) { List<Object> list = Arrays.asList(param); for (Object obj : list) { if (obj instanceof String) { stmt.setString(i + 1, (String) paramList.get(i)); } else if (obj instanceof Integer) { stmt.setInt(i + 1, (Integer) paramList.get(i)); } else if (obj instanceof Double) { stmt.setDouble(i + 1, (Double) paramList.get(i)); } else if (param instanceof Long) { stmt.setLong(i + 1, (Long) paramList.get(i)); } else if (param instanceof Float) { stmt.setFloat(i + 1, (Float) paramList.get(i)); } else if (param instanceof Short) { stmt.setShort(i + 1, (Short) paramList.get(i)); } else if (param instanceof Byte) { stmt.setByte(i + 1, (Byte) paramList.get(i)); } else if (param instanceof Boolean) { stmt.setBoolean(i + 1, (Boolean) paramList.get(i)); } else if (param instanceof Date) { stmt.setDate(i + 1, (Date) paramList.get(i)); } else if (param instanceof Timestamp) { stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i)); } else if (param instanceof Object) { stmt.setObject(i + 1, (Object) paramList.get(i)); } else if (param instanceof Arrays) { stmt.setObject(i + 1, (Object) paramList.get(i)); } } } if (param instanceof String) { stmt.setString(i + 1, (String) paramList.get(i)); } else if (param instanceof Integer) { stmt.setInt(i + 1, (Integer) paramList.get(i)); } else if (param instanceof Double) { stmt.setDouble(i + 1, (Double) paramList.get(i)); } else if (param instanceof Long) { stmt.setLong(i + 1, (Long) paramList.get(i)); } else if (param instanceof Float) { stmt.setFloat(i + 1, (Float) paramList.get(i)); } else if (param instanceof Short) { stmt.setShort(i + 1, (Short) paramList.get(i)); } else if (param instanceof Byte) { stmt.setByte(i + 1, (Byte) paramList.get(i)); } else if (param instanceof Boolean) { stmt.setBoolean(i + 1, (Boolean) paramList.get(i)); } else if (param instanceof Date) { stmt.setDate(i + 1, (Date) paramList.get(i)); } else if (param instanceof Timestamp) { stmt.setTimestamp(i + 1, (Timestamp) paramList.get(i)); } else if (param instanceof Object) { stmt.setObject(i + 1, (Object) paramList.get(i)); } else if (param instanceof Arrays) { stmt.setObject(i + 1, (Object) paramList.get(i)); } } }
/* atualizaao no BD */ @Override public void update(Object object) throws SQLException { Artefato a = (Artefato) object; String sql = "UPDATE Artefato SET conteudo=?, data_aprovacao=?, data_criacao=?, tags=?, titulo=?, idAprovador=?, idArtefato=?, idAutor=?, idCategoria=?, tipo=?, versao=?, isAprovado=?, isBloqueado=?" + " where titulo=?"; PreparedStatement stm = dataSource.getConnection().prepareStatement(sql); stm.setString(1, a.getConteudo()); stm.setString(2, a.getData_aprovacao()); stm.setString(3, a.getData_criacao()); stm.setString(4, a.getTags()); stm.setString(5, a.getTitulo()); stm.setInt(6, a.getIdAprovador()); stm.setInt(7, a.getIdArtefato()); stm.setInt(8, a.getIdAutor()); stm.setInt(9, a.getIdCategoria()); stm.setInt(10, a.getTipo()); stm.setFloat(11, a.getVersao()); stm.setBoolean(12, a.isAprovado()); stm.setBoolean(13, a.isBloqueado()); stm.setString(14, a.getTitulo()); stm.executeUpdate(); }
private int insertJobDetail(Connection conn, JobExecutionContext context) throws IOException, SQLException, SchedulerException { JobDetail job = context.getJobDetail(); JobDataMap jobDataMap = job.getJobDataMap(); Object oResult = context.getResult(); ByteArrayOutputStream baos = serializeJobData(jobDataMap); ByteArrayOutputStream baosResult = serializeObject(oResult); PreparedStatement ps = null; int insertResult = 0; try { ps = conn.prepareStatement(rtp(INSERT_JOB_DETAIL_TRACK, scheduler.getSchedulerName())); ps.setString(1, job.getKey().getName()); ps.setString(2, job.getKey().getGroup()); String instanceId = context.getFireInstanceId(); ps.setString(3, instanceId); ps.setString(4, job.getDescription()); ps.setString(5, job.getJobClass().getName()); // TODO verify it works with DB2v8 // DB2v8 : ps.setInt(6, ((job.isDurable()) ? 1 : 0)); ps.setBoolean(6, job.isDurable()); // TODO verify it works with DB2v8 // DB2v8 : ps.setInt(7, ((job.isConcurrentExectionDisallowed()) ? 1 : 0)); ps.setBoolean(7, job.isConcurrentExectionDisallowed()); // TODO verify it works with DB2v8 // DB2v8 : ps.setInt(8, ((job.isPersistJobDataAfterExecution()) ? 1 : 0)); ps.setBoolean(8, job.isPersistJobDataAfterExecution()); // TODO verify it works with DB2v8 // DB2v8 : ps.setInt(9, ((job.requestsRecovery()) ? 1 : 0)); ps.setBoolean(9, job.requestsRecovery()); // TODO verify it works with Sybase // Sybase : ps.setBytes(10, (baos == null) ? null: baos.toByteArray()); ps.setBytes(10, (baos == null) ? new byte[0] : baos.toByteArray()); ps.setBytes(11, (baosResult == null) ? new byte[0] : baosResult.toByteArray()); String restartedInstanceId = jobDataMap.getString(RESTARTED_FIRE_INSTANCE_ID); ps.setString(12, restartedInstanceId); String returnCode = null; if (oResult instanceof JobDataMap) { returnCode = ((JobDataMap) oResult).getString(QuartzContextAdapter.RETURN_CODE); } else { returnCode = String.valueOf(oResult); } ps.setString(13, returnCode); insertResult = ps.executeUpdate(); } finally { closeStatement(ps); cleanupConnection(conn); } return insertResult; }
/** * Description of the Method * * @param db Description of the Parameter * @throws SQLException Description of the Exception */ public void insert(Connection db) throws SQLException { id = DatabaseUtils.getNextSeq(db, "report_queue_queue_id_seq"); PreparedStatement pst = db.prepareStatement( "INSERT INTO report_queue " + "(" + (id > -1 ? "queue_id, " : "") + "report_id, entered, enteredby, processed, " + "status, filename, filesize, enabled, output_type, email) " + "VALUES (" + (id > -1 ? "?, " : "") + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "); int i = 0; if (id > -1) { pst.setInt(++i, id); } pst.setInt(++i, reportId); pst.setTimestamp(++i, entered); pst.setInt(++i, enteredBy); pst.setTimestamp(++i, processed); DatabaseUtils.setInt(pst, ++i, status); pst.setString(++i, filename); DatabaseUtils.setLong(pst, ++i, size); pst.setBoolean(++i, enabled); DatabaseUtils.setInt(pst, ++i, outputType); pst.setBoolean(++i, email); pst.execute(); pst.close(); id = DatabaseUtils.getCurrVal(db, "report_queue_queue_id_seq", id); }
public boolean updateContractList(Contract con) { boolean check = true; PreparedStatement ps; if (dbc.connect()) { try { String sqlQuery = "INSERT INTO " + getCONTRACT_CB() + " VALUES(?,?,?,?,?,?)"; ps = dbc.getConnection().prepareStatement(sqlQuery); ps.setNString(1, con.getName()); ps.setShort(2, con.getMonth()); if (con.isBHXH()) { ps.setBoolean(3, con.isBHXH()); ps.setFloat(4, con.getPercent()); } else { ps.setBoolean(3, con.isBHXH()); ps.setNull(4, java.sql.Types.FLOAT); } ps.setBytes(5, con.getTemplate()); ps.setString(6, con.getExtension()); if (ps.executeUpdate() < 1) { check = false; } } catch (SQLException ex) { Logger.getLogger(PersonnelDAO.class.getName()).log(Level.SEVERE, null, ex); } } return check; }
private void putUpdateStoredBlock(StoredBlock storedBlock, boolean wasUndoable) throws SQLException { try { PreparedStatement s = conn.get() .prepareStatement( "INSERT INTO headers(hash, chainWork, height, header, wasUndoable)" + " VALUES(?, ?, ?, ?, ?)"); // We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes byte[] hashBytes = new byte[28]; System.arraycopy(storedBlock.getHeader().getHash().getBytes(), 3, hashBytes, 0, 28); s.setBytes(1, hashBytes); s.setBytes(2, storedBlock.getChainWork().toByteArray()); s.setInt(3, storedBlock.getHeight()); s.setBytes(4, storedBlock.getHeader().unsafeRimbitSerialize()); s.setBoolean(5, wasUndoable); s.executeUpdate(); s.close(); } catch (SQLException e) { // It is possible we try to add a duplicate StoredBlock if we upgraded // In that case, we just update the entry to mark it wasUndoable if (!(e.getSQLState().equals(POSTGRES_DUPLICATE_KEY_ERROR_CODE)) || !wasUndoable) throw e; PreparedStatement s = conn.get().prepareStatement("UPDATE headers SET wasUndoable=? WHERE hash=?"); s.setBoolean(1, true); // We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes byte[] hashBytes = new byte[28]; System.arraycopy(storedBlock.getHeader().getHash().getBytes(), 3, hashBytes, 0, 28); s.setBytes(2, hashBytes); s.executeUpdate(); s.close(); } }
public void registrar(partners pa) throws Exception { try { this.Conectar(); String query = "insert into partner (name,lastname,street,noExt,noInt,colony,zip,locality,phone,mobile,email,customer,supplier,image,city_id,state_id,country_id,active)" + "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); "; PreparedStatement st = this.getCon().prepareStatement(query); st.setString(1, pa.getName()); st.setString(2, pa.getLastName()); st.setString(3, pa.getStreet()); st.setString(4, pa.getNoExt()); st.setString(5, pa.getNoInt()); st.setString(6, pa.getColony()); st.setString(7, pa.getZip()); st.setString(8, pa.getLocality()); st.setString(9, pa.getPhone()); st.setString(10, pa.getMobile()); st.setString(11, pa.getEmail()); st.setBoolean(12, pa.getCustomer()); st.setBoolean(13, pa.isSupplier()); st.setBytes(14, pa.getImage()); st.setInt(15, pa.getCity_id()); st.setInt(16, pa.getState_id()); st.setInt(17, pa.getCity_id()); st.setInt(18, pa.getActive()); st.executeUpdate(); } catch (Exception e) { throw e; } finally { this.Cerrar(); } }
public void actualizar(partners pa) throws Exception { try { this.Conectar(); System.out.println("Aqui vamos"); String query = "update partner set name=?, lastname=?, street=?, noExt=?, noInt=?, colony=?, zip=?, locality=?, phone=?, mobile=?, email=?, customer=? ,supplier=?, image=?,city_id=?,state_id=?,country_id=?,active=? where id=?"; PreparedStatement st = this.getCon().prepareStatement(query); st.setString(1, pa.getName()); st.setString(2, pa.getLastName()); st.setString(3, pa.getStreet()); st.setString(4, pa.getNoExt()); st.setString(5, pa.getNoInt()); st.setString(6, pa.getColony()); st.setString(7, pa.getZip()); st.setString(8, pa.getLocality()); st.setString(9, pa.getPhone()); st.setString(10, pa.getMobile()); st.setString(11, pa.getEmail()); st.setBoolean(12, pa.getCustomer()); st.setBoolean(13, pa.isSupplier()); st.setBytes(14, pa.getImage()); st.setInt(15, pa.getCity_id()); st.setInt(16, pa.getState_id()); st.setInt(17, pa.getCity_id()); st.setInt(18, pa.getActive()); st.setInt(19, pa.getIdPartner()); st.executeUpdate(); } catch (Exception e) { throw e; } finally { this.Cerrar(); } }
protected void updateLayout(long oldGroupId, long newGroupId, boolean privateLayout) throws Exception { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { con = DataAccess.getConnection(); ps = con.prepareStatement( "update Layout set groupId = ?, privateLayout = ? where " + "(groupId = ?) and (privateLayout = ?)"); ps.setLong(1, newGroupId); ps.setBoolean(2, true); ps.setLong(3, oldGroupId); ps.setBoolean(4, privateLayout); ps.executeUpdate(); } finally { DataAccess.cleanUp(con, ps, rs); } }
public void alterarCampanha(Campanha campanha) throws SQLException { PreparedStatement stmtAlterar = null; try { try { connection = ConnectionFactory.getConnection(); } catch (ClassNotFoundException ex) { } stmtAlterar = connection.prepareStatement( "update campanha set titulo = ?, dtInicio = ?, dtFim = ?, " + "aPositivo = ?, aNegativo = ?, bPositivo = ?, bNegativo = ?, oPositivo = ?, oNegativo = ?, " + "abPositivo = ?, abNegativo = ?, tipo = ?, outros = ?, descricao = ?, sexo = ? , nomeImagem = ?, " + " idAtivo = ?, caminhoImagem = ?, legendaImagem = ? where idCampanha = ?"); stmtAlterar.setString(1, campanha.getTitulo()); stmtAlterar.setTimestamp(2, campanha.getDtInicio()); stmtAlterar.setTimestamp(3, campanha.getDtFim()); stmtAlterar.setBoolean(4, campanha.isaPositivo()); stmtAlterar.setBoolean(5, campanha.isaNegativo()); stmtAlterar.setBoolean(6, campanha.isbPositivo()); stmtAlterar.setBoolean(7, campanha.isbNegativo()); stmtAlterar.setBoolean(8, campanha.isoPositivo()); stmtAlterar.setBoolean(9, campanha.isoNegativo()); stmtAlterar.setBoolean(10, campanha.isAbPositivo()); stmtAlterar.setBoolean(11, campanha.isAbNegativo()); stmtAlterar.setInt(12, campanha.getTipo()); stmtAlterar.setString(13, campanha.getOutros()); stmtAlterar.setString(14, campanha.getDescricao()); stmtAlterar.setString(15, campanha.getSexo()); stmtAlterar.setString(16, campanha.getNomeImagem()); stmtAlterar.setBoolean(17, campanha.isAtivo()); stmtAlterar.setString(18, campanha.getCaminhoImagem()); stmtAlterar.setString(19, campanha.getLegendaImagem()); // stmtAlterar.setInt(20, campanha.getUsuario().getId()); stmtAlterar.setInt(20, campanha.getId()); stmtAlterar.executeUpdate(); } catch (SQLException e) { throw new RuntimeException("Erro ao alterar campanha. Origem: " + e.getMessage()); } finally { try { stmtAlterar.close(); } catch (SQLException ex) { System.out.println("Erro ao fechar stmt. Ex:" + ex.getMessage()); } ; try { connection.close(); } catch (SQLException ex) { System.out.println("Erro ao fechar conexão. Ex:" + ex.getMessage()); } ; } }
/** * Creates the entity in the database if it does not exist in the database (this is the case if it was constructed using {@link RelationManager#newCourseElement} rather than {@link RelationManager#createCourseElement). */ @Override public CourseElement create() throws de.fu.weave.orm.DatabaseException { String query = "INSERT INTO " + "\"scetris\".\"CourseElement\"" + " (\"name\", \"part_of\", \"duration\", \"type\", \"required\", \"timekey\")" + " VALUES (?, ?, ?, ?, ?, ?) RETURNING id;"; timekey(true); try { java.sql.PreparedStatement stmt = manager.connectionManager().getConnection().prepareStatement(query); int i = 1; if (_name != null) { stmt.setString(i++, _name); } else { stmt.setNull(i++, java.sql.Types.VARCHAR); } stmt.setInt(i++, ref_partOf); stmt.setInt(i++, _duration); if (ref_type != null) { stmt.setInt(i++, ref_type); } else { stmt.setNull(i++, java.sql.Types.INTEGER); } if (manager.isNull(_required)) { stmt.setBoolean(i++, _required = true); } else { stmt.setBoolean(i++, _required); } stmt.setTimestamp(i++, _timekey); java.sql.ResultSet keys = manager.executeQuery(stmt); if (keys.next()) { _id = keys.getInt(1); } else { throw new de.fu.weave.orm.DatabaseException("no key was generated. phail."); } changed_name = false; changed_partOf = false; changed_duration = false; changed_type = false; changed_required = false; } catch (java.sql.SQLException e) { throw new de.fu.weave.orm.DatabaseException(query, e); } exists = true; return this; }
protected long addGroup( long companyId, long creatorUserId, String groupName, long layoutSetPrototypeId) throws Exception { long layoutSetPrototypeClassNameId = getClassNameId(LayoutSetPrototype.class.getName()); long groupId = getGroupId(companyId, layoutSetPrototypeClassNameId, layoutSetPrototypeId); if (groupId > 0) { return groupId; } Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { con = DataAccess.getConnection(); StringBundler sb = new StringBundler(5); sb.append("insert into Group_ (groupId, companyId, "); sb.append("creatorUserId, classNameId, classPK, parentGroupId, "); sb.append("liveGroupId, name, description, type_, typeSettings, "); sb.append("friendlyURL, site, active_) values (?, ?, ?, ?, ?, 0, "); sb.append("0, ?, ?, 0, ?, ?, ?, ?)"); String sql = sb.toString(); ps = con.prepareStatement(sql); groupId = increment(); ps.setLong(1, groupId); ps.setLong(2, companyId); ps.setLong(3, creatorUserId); ps.setLong(4, layoutSetPrototypeClassNameId); ps.setLong(5, layoutSetPrototypeId); ps.setString(6, groupId + StringPool.MINUS + groupName); ps.setString(7, StringPool.BLANK); ps.setString(8, StringPool.BLANK); ps.setString(9, "/template-" + layoutSetPrototypeId); ps.setBoolean(10, false); ps.setBoolean(11, true); ps.execute(); return groupId; } finally { DataAccess.cleanUp(con, ps, rs); } }
public void storeRuleSet(Map<String, RuleSet> map, DBUtils db) { DBUtils dbs; dbs = db; Connection cons; Statement stms; PreparedStatement ps; RuleSet rs; try { System.out.println("----> Storing rulesets....."); dbs.dropTable("RuleSet"); cons = dbs.getConnection(); stms = cons.createStatement(); String sql = "CREATE CACHED TABLE RULESET(KEY VARCHAR(80), INCHUNK BOOLEAN, DIST1 INT, DIST2 INT, PREP VARCHAR(100), " + "PREP2 VARCHAR(300), T_ORDER BOOLEAN, PCOUNT INT, ECOUNT INT, T2COUNT INT, PCAUSE INT, ECAUSE INT, " + "INPREP VARCHAR(100), in_front int, prep_1 varchar(100), prep_2 varchar(100), detected int, inchunk_count int, apply int)"; stms.executeUpdate(sql); ps = cons.prepareStatement( "INSERT INTO RULESET(key,inchunk,dist1,dist2,prep,prep2,t_order,pcount,ecount,t2count,pcause," + "ecause,INPREP, in_front, prep_1,prep_2, detected, inchunk_count,apply) " + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); for (String s : map.keySet()) { rs = map.get(s); ps.setString(1, s); ps.setBoolean(2, rs.in_chunk); ps.setInt(3, rs.dist1); ps.setInt(4, rs.dist2); ps.setString(5, setToStr(rs.prep)); ps.setString(6, mapToStr(rs.prep2)); ps.setBoolean(7, rs.order); ps.setInt(8, rs.pcount); ps.setInt(9, rs.ecount); ps.setInt(10, rs.t2count); ps.setInt(11, rs.pcause); ps.setInt(12, rs.ecause); ps.setString(13, setToStr(rs.inchunk_prep)); ps.setInt(14, rs.in_front); ps.setString(15, setToStr(rs.prep_1)); ps.setString(16, setToStr(rs.prep_2)); ps.setInt(17, rs.detected); ps.setInt(18, rs.inchunk_count); ps.setInt(19, rs.apply); ps.executeUpdate(); } ps.close(); System.out.println("---DONE---> Saving patterns"); } catch (Exception e) { System.out.println("ERORR here "); System.out.println(e.getLocalizedMessage()); } }
public void insertNewData(CommonDataBean bean) throws Exception { Connection conn = null; PreparedStatement pstmt = null; try { // prepare sql script StringBuffer sql = new StringBuffer(); sql.append( "insert into digital_converage (serialno, id, lang_id, title, subtitle, description, author, linkA, ") .append( " linkB, linkC, imgPathA, imgPathB, imgPathC, breadcrumbA, breadcrumbB, breadcrumbC, ") .append( " filePathA, filePathB, filePathC, createDate, top, enabled, isDelete, displayOrder, content_short, content_long ) ") .append(" values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,now(), ?,?,?,1,?,?) "); conn = DriverManager.getConnection("proxool.digital"); pstmt = conn.prepareStatement(sql.toString()); pstmt.setNull(1, java.sql.Types.INTEGER); pstmt.setInt(2, bean.getId()); pstmt.setInt(3, bean.getLang_id()); pstmt.setNString(4, bean.getTitle()); pstmt.setString(5, bean.getSubtitle()); pstmt.setString(6, bean.getDescription()); pstmt.setString(7, bean.getAuthor()); pstmt.setString(8, bean.getLinkA()); pstmt.setString(9, bean.getLinkB()); pstmt.setString(10, bean.getLinkC()); pstmt.setString(11, bean.getImageApath()); pstmt.setString(12, bean.getImageBpath()); pstmt.setString(13, bean.getImageCpath()); pstmt.setInt(14, bean.getBreadcrumbA()); pstmt.setInt(15, bean.getBreadcrumbB()); pstmt.setInt(16, bean.getBreadcrumbC()); pstmt.setString(17, bean.getFileApath()); pstmt.setString(18, bean.getFileBpath()); pstmt.setString(19, bean.getFileCpath()); // pstmt.setNull(20, java.sql.Types.DATE);//default is now() pstmt.setBoolean(20, bean.getTop() == 1 ? true : false); pstmt.setBoolean(21, bean.getEnabled() == 1 ? true : false); pstmt.setBoolean(22, false); pstmt.setString(23, bean.getContent_short()); pstmt.setString(24, bean.getContent_long()); pstmt.execute(); } catch (Exception e) { e.printStackTrace(); } finally { pstmt.close(); conn.close(); } }
private void setCarDescription(PreparedStatement statement, CarDescription carDescription) throws SQLException { int i = 0; statement.setLong(++i, carDescription.getModel().getId()); statement.setInt(++i, carDescription.getPrice()); statement.setInt(++i, carDescription.getDoors()); statement.setInt(++i, carDescription.getSeats()); statement.setInt(++i, carDescription.getConsumption()); statement.setBoolean(++i, carDescription.isAirCondition()); statement.setBoolean(++i, carDescription.isAirBags()); statement.setBoolean(++i, carDescription.isAutomatic()); statement.setString(++i, carDescription.getDescription()); statement.setString(++i, carDescription.getImgUrl()); }
@Override public void saveOrUpdate(Movie movie) { Connection conn = null; try { conn = ds.getConnection(); PreparedStatement pst; if (movie.getId() == null) { // insert long id = 0; Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("select max(MOVIE_ID) from MOVIES"); if (rs.next()) { id = rs.getLong(1) + 1; } movie.setId(id); pst = conn.prepareStatement( "INSERT INTO MOVIES (MOVIE_ID, MOVIE_TITLE, MOVIE_RELEASEDATE, MOVIE_RENTED, PRICECATEGORY_FK) VALUES (?,?,?,?,?)"); pst.setLong(1, id); pst.setString(2, movie.getTitle()); pst.setDate(3, new Date(movie.getReleaseDate().getTime())); pst.setBoolean(4, movie.isRented()); pst.setLong(5, movie.getPriceCategory().getId()); pst.execute(); } else { // update pst = conn.prepareStatement( "UPDATE MOVIES SET MOVIE_TITLE=?, MOVIE_RELEASEDATE=?, MOVIE_RENTED=?, PRICECATEGORY_FK=? where MOVIE_ID=?"); pst.setLong(5, movie.getId()); pst.setString(1, movie.getTitle()); pst.setDate(2, new Date(movie.getReleaseDate().getTime())); pst.setBoolean(3, movie.isRented()); pst.setLong(4, movie.getPriceCategory().getId()); pst.execute(); } } catch (SQLException e) { throw new RuntimeException(e); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } }
public boolean ModificarUsuario() { boolean resp = false; try { // Realizar consulta INSERT String sql = "UPDATE usuarios SET nombres=?,apellidos=?,nacimiento=?, identificador=?, tipo=?, correo=?, telefono=?, estado_usuario=?, nick=?, contraseña=?, materia=? WHERE id_usuario=?"; PreparedStatement cmd = cn.prepareStatement(sql); // Llenar los parámetros cmd.setString(1, nombres); cmd.setString(2, apellidos); cmd.setString(3, nacimiento); cmd.setString(4, identificador); cmd.setString(5, tipo); cmd.setString(6, correo); cmd.setString(7, telefono); cmd.setBoolean(8, estado); cmd.setString(9, usuario); cmd.setString(10, contraseña); cmd.setString(11, materia); cmd.setInt(12, id); // Si da error devuelve 1, caso contrario 0 // Tomar en cuenta el "!" de negación if (!cmd.execute()) { resp = true; } // cmd.close(); // cn.close(); } catch (Exception e) { System.out.println(e.toString()); } return resp; }
/** * 设置预编译参数 * * @param ps 预编译 * @param index 序号 * @param t vo模型 * @param f 字段 * @throws IllegalArgumentException * @throws SQLException * @throws IllegalAccessException */ private void setParamter(PreparedStatement ps, int index, T t, Field f) throws IllegalArgumentException, SQLException, IllegalAccessException { if (!f.isAccessible()) { f.setAccessible(true); } if (isBoolean(f)) { ps.setBoolean(index, f.getBoolean(t)); } else if (isInt(f)) { ps.setInt(index, f.getInt(t)); } else if (isLong(f)) { ps.setLong(index, f.getLong(t)); } else if (isString(f)) { ps.setString(index, (String) f.get(t)); } else if (isDate(f)) { Object o = f.get(t); if (o == null) { ps.setDate(index, null); } else { ps.setTimestamp(index, new java.sql.Timestamp(((Date) o).getTime())); } } else if (isByte(f)) { ps.setByte(index, f.getByte(t)); } else if (isChar(f)) { ps.setInt(index, f.getChar(t)); } else if (isDouble(f)) { ps.setDouble(index, f.getDouble(t)); } else if (isFloat(f)) { ps.setFloat(index, f.getFloat(t)); } else { ps.setObject(index, f.get(t)); } }
private void setParam(PreparedStatement pst, Object param, int i) throws SQLException { if (param instanceof Integer) { int value = ((Integer) param).intValue(); pst.setInt(i + 1, value); } else if (param instanceof String) { String s = (String) param; pst.setString(i + 1, s); } else if (param instanceof Double) { double d = ((Double) param).doubleValue(); pst.setDouble(i + 1, d); } else if (param instanceof Float) { float f = ((Float) param).floatValue(); pst.setFloat(i + 1, f); } else if (param instanceof Long) { long l = ((Long) param).longValue(); pst.setLong(i + 1, l); } else if (param instanceof Boolean) { boolean b = ((Boolean) param).booleanValue(); pst.setBoolean(i + 1, b); } else if (param instanceof Date) { Date d = (Date) param; pst.setDate(i + 1, d); } else if (param instanceof Timestamp) { Timestamp d = (Timestamp) param; pst.setTimestamp(i + 1, d); } else if (param instanceof Object) { pst.setString(i + 1, param.toString()); } }
public boolean guardarUsuario() { boolean resp = false; try { // Realizar consulta INSERT String sql = "INSERT INTO usuarios(nombres,apellidos, nacimiento,identificador, tipo, correo, telefono, estado_usuario, nick, contraseña, materia) " + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?,?,?)"; PreparedStatement cmd = cn.prepareStatement(sql); // Llenar los parámetros cmd.setString(1, nombres); cmd.setString(2, apellidos); cmd.setString(3, nacimiento); cmd.setString(4, identificador); cmd.setString(5, tipo); cmd.setString(6, correo); cmd.setString(7, telefono); cmd.setBoolean(8, estado); cmd.setString(9, usuario); cmd.setString(10, contraseña); cmd.setString(11, materia); // Si da error devuelve 1, caso contrario 0 // Tomar en cuenta el "!" de negación if (!cmd.execute()) { resp = true; } // cmd.close(); // cn.close(); } catch (Exception e) { System.out.println(e.toString()); } return resp; }
/** * Description of the Method * * @param db Description of the Parameter * @return Description of the Return Value * @throws SQLException Description of the Exception */ public int update(Connection db) throws SQLException { int resultCount = 0; PreparedStatement pst = null; StringBuffer sql = new StringBuffer(); sql.append( " UPDATE customer_product " + " SET description = ?, " + " status_id = ?, " + " status_date = ?, " + " modified = " + DatabaseUtils.getCurrentTimestamp(db) + ", " + " modifiedby = ?, " + " enabled = ? "); sql.append("WHERE order_id = ? "); sql.append("AND modified " + ((this.getModified() == null) ? "IS NULL " : "= ? ")); int i = 0; pst = db.prepareStatement(sql.toString()); pst.setString(++i, this.getDescription()); DatabaseUtils.setInt(pst, ++i, this.getStatusId()); pst.setTimestamp(++i, this.getStatusDate()); pst.setInt(++i, this.getModifiedBy()); pst.setInt(++i, this.getId()); if (this.getModified() != null) { pst.setTimestamp(++i, this.getModified()); } pst.setBoolean(++i, this.getEnabled()); resultCount = pst.executeUpdate(); pst.close(); return resultCount; }
/** * Create the cookies enabled CharacteristicBean. * * @param conn A connection to the database. * @param num_samples The number of samples in the database. * @param value The value of this sample. * @return * @throws SQLException */ private static CharacteristicBean getCharacteristicBean( Connection conn, int num_samples, String dbname, Boolean value) throws SQLException { CharacteristicBean chrbean = new CharacteristicBean(); PreparedStatement getCount; String querystr = "SELECT COUNT(*) FROM `Samples` WHERE `" + dbname + "`"; if (value != null) { if (value) { chrbean.setValue("Yes"); } else { chrbean.setValue("No"); } querystr += " = ?;"; getCount = conn.prepareStatement(querystr); getCount.setBoolean(1, value); } else { chrbean.setValue(NO_JAVASCRIPT); querystr += " IS NULL;"; getCount = conn.prepareStatement(querystr); } ResultSet rs = getCount.executeQuery(); rs.next(); int count = rs.getInt(1); rs.close(); chrbean.setInX(((double) num_samples) / ((double) count)); chrbean.setBits(Math.abs(Math.log(chrbean.getInX()) / Math.log(2))); return chrbean; }
@Override public boolean blockSub(final boolean blocked, final int subId) throws SQLException { Connection connection = null; String sql = "update subscribers " + "set blocked = ? where id = ?"; try { connection = MysqlDaoFactory.getConnection(); connection.setAutoCommit(false); PreparedStatement stmt = connection.prepareStatement(sql); stmt.setBoolean(1, blocked); stmt.setInt(2, subId); int res = stmt.executeUpdate(); if (res > 0) { connection.commit(); return true; } } catch (Exception e) { try { if (connection != null) { connection.rollback(); throw new EJBException("Transaction failed: " + e.getMessage()); } else { throw new EJBException( "Transaction failed due " + "connection problem: " + e.getMessage()); } } catch (SQLException ex) { throw new EJBException("Rollback failed due SQLException " + ex.getMessage()); } } finally { if (connection != null) { connection.setAutoCommit(true); MysqlDaoFactory.putBackConnection(connection); } } return false; }
/** * disables the current active id, enables the new one selected * * @param profileId * @param clientUUID * @param active * @throws Exception */ public void updateActive(int profileId, String clientUUID, Boolean active) throws Exception { Connection sqlConnection = null; PreparedStatement statement = null; try { sqlConnection = sqlService.getConnection(); statement = sqlConnection.prepareStatement( "UPDATE " + Constants.DB_TABLE_CLIENT + " SET " + Constants.CLIENT_IS_ACTIVE + "= ?" + " WHERE " + Constants.GENERIC_CLIENT_UUID + "= ? " + " AND " + Constants.GENERIC_PROFILE_ID + "= ?"); statement.setBoolean(1, active); statement.setString(2, clientUUID); statement.setInt(3, profileId); statement.executeUpdate(); } catch (Exception e) { // ok to swallow this.. just means there wasn't any } finally { try { if (statement != null) statement.close(); } catch (Exception e) { } } }
public void addKhachHang( String maKH, String tenKH, String diaChi, String email, String soDienThoai, boolean tinhTrang) throws Exception { // TODO Auto-generated method stub con.ConnectionData(); PreparedStatement pst; String smtpServer = "smtp.aol.com"; String to = email; String from = "*****@*****.**"; String subject = "Thu Xac Nhan"; String body = "Chao " + tenKH + ". Chung toi da nhan duoc yeu cau dat hang cua ban./r/n" + " Chung toi se lien lac voi ban qua thong tin ban de lai. Cam on"; String password = "******"; // send(smtpServer, to, from, password, subject, body); try { pst = con.cn.prepareStatement( "insert into KhachHang(MaKH,TenKH,DiaChi,Email,SoDienThoai,TinhTrang) values(?,?,?,?,?,?)"); pst.setString(1, maKH); pst.setString(2, tenKH); pst.setString(3, diaChi); pst.setString(4, email); pst.setString(5, soDienThoai); pst.setBoolean(6, tinhTrang); pst.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally { con.cn.close(); } }
/** * Updating a given appointment with new values in the persistence. * * @param appointment which shall be updated must not be null, id must not be null and must not be * changed * @return given appointment with updated values * @throws PersistenceException if there are complications with the persitance layer */ @Override public Appointment update(Appointment appointment) throws PersistenceException { LOGGER.info("Updating record in appointment table.."); try { if (appointment == null) { LOGGER.error("Update parameter (appointment) was null."); throw new PersistenceException("Appointment to be updated must not be null"); } updateStm.setDate(1, new java.sql.Date(appointment.getDatum().getTime())); updateStm.setInt(2, appointment.getSession_id()); updateStm.setInt(3, appointment.getUser_id()); updateStm.setBoolean(4, appointment.getIsTrained()); updateStm.setBoolean(5, appointment.getIsDeleted()); updateStm.setInt(6, appointment.getId()); updateStm.executeUpdate(); } catch (SQLException e) { LOGGER.error("Failed to update record in appointment table. - " + e.getMessage()); throw new PersistenceException("Failed to update record in appointment table.", e); } LOGGER.info("Record successfully updated in appointment table. " + appointment); return appointment; }
/** * Endrer en entry(kø-element) til aktiv tilstand(får hjelp av en lærer). * * @param teacher * @param user * @param active */ public static void setActive(String teacher, String user, boolean active) { Database db = new Database(); try { db.openConnection(); } catch (Exception ex) { System.out.println("(QueueDB.java) Error: Kunne ikke koble til database -> " + ex); return; } PreparedStatement ps = null; try { ps = db.connection.prepareStatement( "UPDATE queue_list SET status = ?,teacher_username = ? WHERE person_username = ?"); ps.setBoolean(1, active); ps.setString(2, teacher); ps.setString(3, user); ps.executeUpdate(); } catch (Exception e) { System.out.println(e); } finally { Cleanup.closeSentence(ps); Cleanup.closeConnection(db.connection); } }
/** * Creates a new appointment. * * @param appointment which shall be inserted into the underlying persistance layer. must not be * null, id must be null * @return the given appointment for further usage * @throws PersistenceException if there are complications with the persitance layer */ @Override public Appointment create(Appointment appointment) throws PersistenceException { LOGGER.info("Creating a new appointment in db.. " + appointment); try { if (appointment == null) { LOGGER.error("Create parameter (appointment) was null."); throw new PersistenceException("Appointment to be create must not be null"); } Statement appointmentNextValStm = connection.createStatement(); ResultSet rs_appointmentNextVal = appointmentNextValStm.executeQuery("SELECT NEXTVAL('appointment_seq')"); rs_appointmentNextVal.next(); appointment.setId(rs_appointmentNextVal.getInt(1)); createStm.setInt(1, appointment.getId()); createStm.setDate(2, new java.sql.Date(appointment.getDatum().getTime())); createStm.setInt(3, appointment.getSession_id()); createStm.setInt(4, appointment.getUser_id()); createStm.setBoolean(5, appointment.getIsTrained()); createStm.setBoolean(6, appointment.getIsDeleted()); createStm.execute(); } catch (SQLException e) { LOGGER.error("Failed to create record into appointment table. - " + e.getMessage()); throw new PersistenceException("Failed to create record into appointment table.", e); } LOGGER.info("Record successfully created in appointment table."); return appointment; }
public static void editBBSThread( MapleClient client, String title, String text, int icon, int localthreadid) { MapleCharacter c = client.getPlayer(); if (c.getGuildId() <= 0) { return; // expelled while viewing? } try { Connection con = DatabaseConnection.getConnection(); PreparedStatement ps = con.prepareStatement( "UPDATE bbs_threads SET " + "`name` = ?, " + "`timestamp` = ?, " + "`icon` = ?, " + "`startpost` = ? " + "WHERE guildid = ? AND localthreadid = ? AND (postercid = ? OR ?)"); ps.setString(1, title); ps.setLong(2, System.currentTimeMillis()); ps.setInt(3, icon); ps.setString(4, text); ps.setInt(5, c.getGuildId()); ps.setInt(6, localthreadid); ps.setInt(7, c.getId()); ps.setBoolean(8, c.getGuildRank() <= 2); ps.execute(); ps.close(); displayThread(client, localthreadid); } catch (SQLException se) { log.error("SQLException: " + se.getLocalizedMessage(), se); } }