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);
}
}