public boolean checkChartUpdateRequired(String doi, Connection con) throws SQLException {
PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY);
getUpdateStmt.setString(1, doi);
ResultSet rs1 = getUpdateStmt.executeQuery();
int lastNcites = 0;
if (rs1.first()) {
lastNcites = rs1.getInt("lastNcites");
} else {
rs1.close();
getUpdateStmt.close();
return true;
}
rs1.close();
getUpdateStmt.close();
PreparedStatement getNcitesStmt = con.prepareStatement(DEF_GET_NCITES_QUERY);
getNcitesStmt.setString(1, doi);
int ncites = 0;
ResultSet rs2 = getNcitesStmt.executeQuery();
if (rs2.first()) {
ncites = rs2.getInt("ncites");
}
rs2.close();
getNcitesStmt.close();
if (ncites != lastNcites) {
return true;
} else {
return false;
}
} // - checkChartUpdateRequired
/**
* Inserts a new non-existing row into the DailyPrices table
*
* @param symbolId ID of entry in symbol table.
* @param dt Date of the price information
* @param open Opening price on the day
* @param close Closing price on the day
* @param high high price of the day
* @param low low price of the day
* @throws SQLException
*/
public void insertDailyPrice(
int symbolId, Date dt, double open, double close, double high, double low)
throws SQLException {
// ensure price doesn't exist already.
PreparedStatement psCheck =
MyConnection.prepareStatement(
"select count(*) rowcount from DailyPrices where Date=? and SymbolId=?;");
psCheck.setString(1, dt.toString());
psCheck.setInt(2, symbolId);
ResultSet rsCheck = psCheck.executeQuery();
if (rsCheck.getInt("rowcount") > 0) {
return;
}
PreparedStatement ps =
MyConnection.prepareStatement(
"insert into DailyPrices(SymbolId, Date, Open, Close, High, Low) values(?,?,?,?,?,?);");
ps.setInt(1, symbolId);
ps.setString(2, dt.toString());
ps.setDouble(3, open);
ps.setDouble(4, close);
ps.setDouble(5, high);
ps.setDouble(6, low);
ps.executeUpdate();
}
public boolean set(int sequence, String message) throws IOException {
Connection connection = null;
PreparedStatement insert = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
insert = connection.prepareStatement(SQL_INSERT_MESSAGE);
int offset = setSessionIdParameters(insert, 1);
insert.setInt(offset++, sequence);
insert.setString(offset, message);
insert.execute();
} catch (SQLException ex) {
if (connection != null) {
PreparedStatement update = null;
try {
update = connection.prepareStatement(SQL_UPDATE_MESSAGE);
update.setString(1, message);
int offset = setSessionIdParameters(update, 2);
update.setInt(offset, sequence);
boolean status = update.execute();
return !status ? update.getUpdateCount() > 0 : false;
} catch (SQLException e) {
throw (IOException) new IOException(e.getMessage()).initCause(e);
} finally {
JdbcUtil.close(sessionID, update);
}
}
} finally {
JdbcUtil.close(sessionID, rs);
JdbcUtil.close(sessionID, insert);
JdbcUtil.close(sessionID, connection);
}
return true;
}
public void reset() throws IOException {
cache.reset();
Connection connection = null;
PreparedStatement deleteMessages = null;
PreparedStatement updateTime = null;
try {
connection = dataSource.getConnection();
deleteMessages = connection.prepareStatement(SQL_DELETE_MESSAGES);
setSessionIdParameters(deleteMessages, 1);
deleteMessages.execute();
updateTime = connection.prepareStatement(SQL_UPDATE_SESSION);
updateTime.setTimestamp(
1, new Timestamp(Calendar.getInstance(TimeZone.getTimeZone("UTC")).getTimeInMillis()));
updateTime.setInt(2, getNextTargetMsgSeqNum());
updateTime.setInt(3, getNextSenderMsgSeqNum());
setSessionIdParameters(updateTime, 4);
updateTime.execute();
} catch (SQLException e) {
throw (IOException) new IOException(e.getMessage()).initCause(e);
} catch (IOException e) {
throw e;
} finally {
JdbcUtil.close(sessionID, deleteMessages);
JdbcUtil.close(sessionID, updateTime);
JdbcUtil.close(sessionID, connection);
}
}
public void insertChartUpdate(String doi, int lastNcites, Connection con) throws SQLException {
PreparedStatement getUpdateStmt = con.prepareStatement(DEF_GET_UPDATE_QUERY);
getUpdateStmt.setString(1, doi);
ResultSet rs = getUpdateStmt.executeQuery();
boolean isNew = true;
if (rs.first()) {
isNew = false;
}
rs.close();
getUpdateStmt.close();
PreparedStatement stmt = null;
if (isNew) {
stmt = con.prepareStatement(DEF_INS_UPDATE_STMT);
stmt.setString(1, doi);
stmt.setInt(2, lastNcites);
} else {
stmt = con.prepareStatement(DEF_UPDATE_UPDATE_STMT);
stmt.setInt(1, lastNcites);
stmt.setString(2, doi);
}
stmt.executeUpdate();
stmt.close();
} // - insertChartUpdate
public void insert(Connection con, JUser user) throws Exception {
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
sql = "update t_card set valid=1 where member_id=?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, this.member_id);
pstmt.executeUpdate();
pstmt.close();
this.card_id = com.gemway.util.ID.getIDObject("default").create();
sql =
"insert into t_card "
+ " (card_id, regdate, card_no, member_id, card_class, op_type, moneyPaid, "
+ " paidOfPro, con_no, class_name, markup, created, createrName,valid)"
+ " values(?,?,?,?,?,?,?, ?,?,?,?,?,?,0)";
pstmt = con.prepareStatement(sql);
int colIndex = 1;
pstmt.setString(colIndex++, this.card_id);
pstmt.setDate(colIndex++, this.regdate);
pstmt.setString(colIndex++, this.card_no);
pstmt.setString(colIndex++, this.member_id);
pstmt.setString(colIndex++, this.card_class);
pstmt.setInt(colIndex++, this.op_type);
pstmt.setString(colIndex++, this.moneypaid);
pstmt.setString(colIndex++, this.paidofpro);
pstmt.setString(colIndex++, this.con_no);
pstmt.setString(colIndex++, this.class_name);
pstmt.setString(colIndex++, this.markup);
pstmt.setTimestamp(colIndex++, new java.sql.Timestamp(System.currentTimeMillis()));
pstmt.setString(colIndex++, user.getUserName());
pstmt.executeUpdate();
pstmt.close();
}
public static int delTeam(Connection con, String team_code) throws java.sql.SQLException {
PreparedStatement pstmt = null;
java.sql.ResultSet rs = null;
String sql = null;
int delCounts = 0;
int teamMember = 0;
if (team_code == null) return delCounts;
sql = " select count(*) as teamMembers from t_teammember " + " where tm_team_id = ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, Team.getTeamIdbyCode(con, team_code));
rs = pstmt.executeQuery();
if (rs.next()) {
teamMember = rs.getInt("teamMembers");
}
rs.close();
if (teamMember == 0) {
sql = " delete from t_team " + " where team_code = ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, team_code);
pstmt.executeUpdate();
delCounts++;
}
pstmt.close();
return delCounts;
}
private static boolean connectToDatabase() {
String databaseConnectionInfo =
"jdbc:mysql://localhost/authorities?user=authorities&password=authorities&useUnicode=yes&characterEncoding=UTF-8";
if (authoritiesConn == null) {
try {
authoritiesConn = DriverManager.getConnection(databaseConnectionInfo);
getPreferredAuthorByOriginalNameStmt =
authoritiesConn.prepareStatement(
"SELECT viafId, normalizedName from preferred_authors where originalName = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
getPreferredAuthorByNormalizedNameStmt =
authoritiesConn.prepareStatement(
"SELECT viafId, normalizedName from preferred_authors where originalName = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
getPreferredAuthorByAlternateNameStmt =
authoritiesConn.prepareStatement(
"SELECT preferred_authors.viafId, normalizedName FROM `preferred_authors` inner join alternate_authors on preferred_authors.viafId = alternate_authors.viafId where alternateName = ?",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY);
return true;
} catch (Exception e) {
logger.error("Error connecting to authorities database");
return false;
}
}
return true;
}
/**
* fügt einen neuen Kontakt zur Kontaktliste hinzu
*
* @param besitzer Der Besitzer der Kontaktliste
* @param kontakt Der Kontakt, der hinzugefügt werden soll
*/
public void kontaktZurKontaktListeHinzufuegen(String besitzer, String kontakt) {
int kNr = bestimmeBNrBenutzer(kontakt);
int bNr = bestimmeBNrBenutzer(besitzer);
try {
// überprüft, ob der Konktakt noch nicht in der Kontaktliste ist
boolean gefunden = false;
ResultSet rueckgabewert = null;
String query1 = "select count(*) from kontaktliste where besitzer = ? and kontakt = ?";
PreparedStatement anweisung1 = con.prepareStatement(query1);
anweisung1.setString(1, besitzer);
anweisung1.setString(1, kontakt);
rueckgabewert = anweisung1.executeQuery();
// werdet den Rückgabewert aus
while (rueckgabewert.next()) {
gefunden = rueckgabewert.getBoolean(1);
}
if (!gefunden) {
// fügt den Kontakt zur Kontakliste hinzu
String query = "insert into kontaktliste (besitzer, kontakt) values(?, ?);";
PreparedStatement anweisung = con.prepareStatement(query);
anweisung.setInt(1, bNr);
anweisung.setInt(2, kNr);
anweisung.executeUpdate();
}
} catch (SQLException e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(1);
}
}
public void deleteUser(String username) {
if (isReadOnly()) {
// Reject the operation since the provider is read-only
throw new UnsupportedOperationException();
}
Connection con = null;
PreparedStatement pstmt = null;
boolean abortTransaction = false;
try {
// Delete all of the users's extended properties
con = DbConnectionManager.getTransactionConnection();
pstmt = con.prepareStatement(DELETE_USER_PROPS);
pstmt.setString(1, username);
pstmt.execute();
pstmt.close();
// Delete the actual user entry
pstmt = con.prepareStatement(DELETE_USER);
pstmt.setString(1, username);
pstmt.execute();
} catch (Exception e) {
Log.error(e);
abortTransaction = true;
} finally {
DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction);
}
}
@Test
public void testUpsertValuesWithExpression() throws Exception {
long ts = nextTimestamp();
ensureTableCreated(getUrl(), "IntKeyTest", null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String upsert = "UPSERT INTO IntKeyTest VALUES(-1)";
PreparedStatement upsertStmt = conn.prepareStatement(upsert);
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsert = "UPSERT INTO IntKeyTest VALUES(1+2)";
upsertStmt = conn.prepareStatement(upsert);
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
conn.close();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT i FROM IntKeyTest";
ResultSet rs = conn.createStatement().executeQuery(select);
assertTrue(rs.next());
assertEquals(-1, rs.getInt(1));
assertTrue(rs.next());
assertEquals(3, rs.getInt(1));
assertFalse(rs.next());
}
public ArrayList<passport> getPassport() {
connect();
ArrayList<passport> list = new ArrayList<passport>();
try {
if (this.getOrd().equals("select")) {
pstmt = con.prepareStatement(SQL);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
passport p = new passport();
p.setName(rs.getString("name"));
p.setEng_name(rs.getString("eng_name"));
p.setBirth(rs.getString("birth"));
p.setExdate(rs.getString("exdate"));
list.add(p);
result++;
}
this.setResult(result);
rs.close();
} else {
pstmt = con.prepareStatement(SQL);
result = pstmt.executeUpdate();
this.setResult(result);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
disconnect();
}
return list;
}
@Override
public void delete(Integer deptno) {
int updateCount_EMPs = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
// 1●設定於 pstm.executeUpdate()之前
con.setAutoCommit(false);
// 先刪除員工
pstmt = con.prepareStatement(DELETE_EMPs);
pstmt.setInt(1, deptno);
updateCount_EMPs = pstmt.executeUpdate();
// 再刪除部門
pstmt = con.prepareStatement(DELETE_DEPT);
pstmt.setInt(1, deptno);
pstmt.executeUpdate();
// 2●設定於 pstm.executeUpdate()之後
con.commit();
con.setAutoCommit(true);
System.out.println("刪除部門編號" + deptno + "時,共有員工" + updateCount_EMPs + "人同時被刪除");
// Handle any driver errors
} catch (ClassNotFoundException e) {
throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
// Handle any SQL errors
} catch (SQLException se) {
if (con != null) {
try {
// 3●設定於當有exception發生時之catch區塊內
con.rollback();
} catch (SQLException excep) {
throw new RuntimeException("rollback error occured. " + excep.getMessage());
}
}
throw new RuntimeException("A database error occured. " + se.getMessage());
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
}
public void insertVisitant(VisitantBean visitant) {
String insertSql = query.insertVisitant_InsertQuery;
String selectSql = query.insertVisitant_SelectSql;
try {
connection = DBManager.getConnection();
preparedStatement = connection.prepareStatement(selectSql);
preparedStatement.setInt(1, visitant.getPassNum());
resultSet = preparedStatement.executeQuery();
if (!resultSet.next()) {
preparedStatement = connection.prepareStatement(insertSql);
preparedStatement.setTimestamp(1, visitant.getInTime());
preparedStatement.setInt(2, visitant.getPassNum());
preparedStatement.setString(3, visitant.getVisitantName());
preparedStatement.setString(4, visitant.getCompany());
preparedStatement.setString(5, visitant.getPhone());
preparedStatement.setInt(6, 1);
preparedStatement.executeUpdate();
}
} catch (SQLException e) {
System.out.println(query.insertVisitant_Exception);
e.printStackTrace();
} finally {
DBManager.close(resultSet, connection, preparedStatement);
}
} // end insertVisitant
private void loadCache() throws SQLException, IOException {
Connection connection = null;
PreparedStatement query = null;
PreparedStatement insert = null;
ResultSet rs = null;
try {
connection = dataSource.getConnection();
query = connection.prepareStatement(SQL_GET_SEQNUMS);
setSessionIdParameters(query, 1);
rs = query.executeQuery();
if (rs.next()) {
cache.setCreationTime(SystemTime.getUtcCalendar(rs.getTimestamp(1)));
cache.setNextTargetMsgSeqNum(rs.getInt(2));
cache.setNextSenderMsgSeqNum(rs.getInt(3));
} else {
insert = connection.prepareStatement(SQL_INSERT_SESSION);
int offset = setSessionIdParameters(insert, 1);
insert.setTimestamp(offset++, new Timestamp(cache.getCreationTime().getTime()));
insert.setInt(offset++, cache.getNextTargetMsgSeqNum());
insert.setInt(offset, cache.getNextSenderMsgSeqNum());
insert.execute();
}
} finally {
JdbcUtil.close(sessionID, rs);
JdbcUtil.close(sessionID, query);
JdbcUtil.close(sessionID, insert);
JdbcUtil.close(sessionID, connection);
}
}
@Override
public Grupo crear_grupo(String nombre) throws SQLException, GrupoAlreadyExistException {
Connection connection = null;
PreparedStatement stmt = null;
String id = null;
try {
Grupo grupo = obtener_ID_grupo_por_NOMBRE(nombre);
if (grupo != null) throw new GrupoAlreadyExistException();
connection = Database.getConnection();
stmt = connection.prepareStatement(GrupoDAOQuery.UUID);
ResultSet rs = stmt.executeQuery();
if (rs.next()) id = rs.getString(1);
else throw new SQLException();
connection.setAutoCommit(false);
stmt.close();
stmt = connection.prepareStatement(GrupoDAOQuery.CREAR_GRUPO);
stmt.setString(1, id);
stmt.setString(2, nombre);
stmt.executeUpdate();
} catch (SQLException e) {
throw e;
} finally {
if (stmt != null) stmt.close();
if (connection != null) {
connection.setAutoCommit(true);
connection.close();
}
}
return obtener_NOMBRE_por_ID(id);
}
// public static void main(String[] args)
// {
void query() {
try {
// System.out.println("hello ");
// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// System.out.println("heloo2");
System.out.println("Driver Loading Succesful");
Connection conn =
DriverManager.getConnection(
"jdbc:ucanaccess://C:\\Users\\naman\\Desktop\\Database1.accdb");
System.out.println("connecttion with Database Succesful");
Statement stmt = conn.createStatement();
// access obj()
PreparedStatement ps =
conn.prepareStatement(
"select customer_id,customer_name,phone,address,email from customer where customer_id = ?");
v = Integer.parseInt(st);
ps.setInt(1, v);
PreparedStatement ps1 =
conn.prepareStatement(
"select m.medicine_name,o.quantity from medicine m,order o,new n where n.customer_id=? and n.order_id=o.order_id and o.medicine_id=m.medicine_id");
ps1.setInt(1, v);
// ps.setString(2, "*");
rs1 = ps1.executeQuery();
rs = ps.executeQuery();
// ResultSetMetaData rsmd = rs.getMetaData();
// System.out.println("Result set Created");
// int id;
// int salary;
// System.out.println("id ");
// String s;
// int col=rsmd.getColumnCount();
// System.out.println(col);
// id=rsmd.getColumnCount();
// int i=0;
/* while(rs.next())
{
for ( i = 1; i <= col; i++)
{
s=rs.getString(i);
System.out.println(s);
}
//salary=rs.getInt("salary");
// System.out.println(s+" " );
// s=rs.getString("Medicine_Name");
//System.out.println(s);
//i++;
}
*/
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
public static UserBean isRegister(UserBean bean) {
con = Connector.getConnection();
String createTable =
"create table if not exists users("
+ "id int not null auto_increment primary key,"
+ "firstName varchar(100),"
+ "lastName varchar(100),"
+ "email varchar(100),"
+ "password varchar(50),"
+ "gender varchar(7)"
+ ");";
String sql = "insert into users(firstName,lastName,email,password,gender) values(?,?,?,?,?);";
String sqlId = "select * from users where email = ? and password = ?;";
String sql3 = "insert into isRead(id) values(?);";
try {
PreparedStatement st = con.prepareStatement(createTable);
st.executeUpdate();
st.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
PreparedStatement st = con.prepareStatement(sql);
st.setString(1, bean.getFirstName());
st.setString(2, bean.getLastName());
st.setString(3, bean.getEmail());
st.setString(4, bean.getPassword());
st.setString(5, bean.getGender());
int i = st.executeUpdate();
PreparedStatement st2 = con.prepareStatement(sqlId);
st2.setString(1, bean.getEmail());
st2.setString(2, bean.getPassword());
if (i != 0) {
ResultSet rs = st2.executeQuery();
boolean more = rs.next();
if (more) {
bean.setFirstName(rs.getString("firstName"));
bean.setLastName(rs.getString("lastName"));
bean.setId(rs.getString("id"));
bean.setIsValid(true);
PreparedStatement st3 = con.prepareStatement(sql3);
st3.setString(1, bean.getId());
st3.executeUpdate();
}
bean.setIsValid(true);
}
st.close();
st2.close();
con.close();
} catch (Exception e) {
e.printStackTrace();
}
return bean;
}
protected void setUp() throws Exception {
Class.forName("org.hsqldb.jdbcDriver");
conn = DriverManager.getConnection("jdbc:hsqldb:mem:jxls", "sa", "");
Statement stmt = conn.createStatement();
stmt.executeUpdate(CREATE_DEPARTMENT_TABLE);
stmt.executeUpdate(CREATE_EMPLOYEE_TABLE);
PreparedStatement insertDep = conn.prepareStatement(INSERT_DEPARTMENT);
PreparedStatement insertStmt = conn.prepareStatement(INSERT_EMPLOYEE);
int k = 1;
int n = 1;
for (int i = 0; i < depNames.length; i++) {
String depName = depNames[i];
insertDep.setString(1, depName);
insertDep.setInt(2, n++);
insertDep.executeUpdate();
for (int j = 0; j < employeeNames[i].length; j++) {
insertStmt.setString(1, employeeNames[i][j]);
insertStmt.setInt(2, employeeAges[i][j]);
insertStmt.setDouble(3, employeePayments[i][j]);
insertStmt.setDouble(4, employeeBonuses[i][j]);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-mm-dd");
insertStmt.setDate(5, new Date(sdf.parse(employeeBirthDates[i][j]).getTime()));
SimpleDateFormat tdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss.SSS");
insertStmt.setTimestamp(6, new Timestamp(tdf.parse(birthTimestamps[i][j]).getTime()));
insertStmt.setInt(7, n - 1);
insertStmt.setInt(8, k++);
insertStmt.executeUpdate();
}
}
stmt.close();
insertStmt.close();
}
/**
* Method called by the Form panel to delete existing data.
*
* @param persistentObject value object to delete
* @return an ErrorResponse value object in case of errors, VOResponse if the operation is
* successfully completed
*/
public Response deleteRecord(ValueObject persistentObject) throws Exception {
PreparedStatement stmt = null;
try {
EmpVO vo = (EmpVO) persistentObject;
// delete from WORKING_DAYS...
stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
stmt.close();
// delete from EMP...
stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
gridFrame.reloadData();
frame.getGrid().clearData();
return new VOResponse(vo);
} catch (SQLException ex) {
ex.printStackTrace();
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
conn.commit();
} catch (SQLException ex1) {
}
}
}
public static boolean saveDetalle(Detalle a) {
try {
conn = ds.getConnection();
stmt = conn.prepareStatement("call savedetalle(?,?,?,?,?)");
Date fech = new Date(a.getFecha().getTime());
stmt.setInt(1, a.getFormaDePago().getId());
stmt.setInt(2, a.getUsuario().getIdUsuario());
stmt.setDate(3, fech);
stmt.setDouble(4, a.getTotal());
stmt.setTime(5, new java.sql.Time(a.getHora().toDateTimeToday().getMillis()));
rs = stmt.executeQuery();
while (rs.next()) {
a.setId(rs.getInt(1));
}
stmt = conn.prepareStatement("call saveproductosventa(?,?)");
for (Producto x : a.getProductos()) {
stmt.setInt(1, a.getId());
stmt.setInt(2, x.getIdProducto());
stmt.execute();
}
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
@Test
public void testUpsertDateValues() throws Exception {
long ts = nextTimestamp();
Date now = new Date(System.currentTimeMillis());
ensureTableCreated(getUrl(), TestUtil.PTSDB_NAME, null, ts - 2);
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1)); // Execute at timestamp 1
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String dateString = "1999-01-01 02:00:00";
PreparedStatement upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('aaa','bbb',to_date('" + dateString + "'))");
int rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
upsertStmt =
conn.prepareStatement(
"upsert into ptsdb(inst,host,date) values('ccc','ddd',current_date())");
rowsInserted = upsertStmt.executeUpdate();
assertEquals(1, rowsInserted);
conn.commit();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 1
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String select = "SELECT date,current_date() FROM ptsdb";
ResultSet rs = conn.createStatement().executeQuery(select);
Date then = new Date(System.currentTimeMillis());
assertTrue(rs.next());
Date date = DateUtil.parseDate(dateString);
assertEquals(date, rs.getDate(1));
assertTrue(rs.next());
assertTrue(rs.getDate(1).after(now) && rs.getDate(1).before(then));
assertFalse(rs.next());
}
public void save(Connection con, JUser juser) throws Exception {
PreparedStatement pstmt = null;
java.sql.Timestamp now = new java.sql.Timestamp(System.currentTimeMillis());
String sql = null;
int idx = 1;
if (JUtil.convertNull(this.getGroup_id()).equals("")) {
sql =
" insert into t_ym_book(group_id, group_name, inquire_date, group_no, contact,"
+ " contact_phone, contact_handset, contact_fax, countries, countries_name, group_type, "
+ " memo, company_id, zxgw_id, zxgw_name, take_out, creater, createrName, created) "
+ " values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
pstmt = con.prepareStatement(sql);
this.setGroup_id(JUtil.createUNID());
pstmt.setString(idx++, this.getGroup_id());
pstmt.setString(idx++, this.getGroup_name());
pstmt.setDate(idx++, this.getInquire_date());
pstmt.setString(idx++, this.getGroup_no());
pstmt.setString(idx++, this.getContact());
pstmt.setString(idx++, this.getContact_phone());
pstmt.setString(idx++, this.getContact_handset());
pstmt.setString(idx++, this.getContact_fax());
pstmt.setString(idx++, this.getCountries());
pstmt.setString(idx++, this.getCountries_name());
pstmt.setInt(idx++, this.getGroup_type());
pstmt.setString(idx++, this.getMemo());
pstmt.setString(idx++, this.getCompany_id());
pstmt.setString(idx++, this.getZxgw_id());
pstmt.setString(idx++, this.getZxgw_name());
pstmt.setString(idx++, this.getTake_out());
pstmt.setString(idx++, juser.getUserId());
pstmt.setString(idx++, juser.getUserName());
pstmt.setTimestamp(idx++, now);
pstmt.executeUpdate();
pstmt.close();
} else {
sql =
" update t_ym_book set group_name = ?, inquire_date = ?, memo = ?, "
+ " group_no = ?, contact = ?, contact_phone = ?, contact_handset = ?, contact_fax = ?,"
+ " countries = ?, countries_name = ?, zxgw_id = ?, zxgw_name = ?, take_out = ? "
+ " where group_id = ? ";
pstmt = con.prepareStatement(sql);
pstmt.setString(idx++, this.getGroup_name());
pstmt.setDate(idx++, this.getInquire_date());
pstmt.setString(idx++, this.getMemo());
pstmt.setString(idx++, this.getGroup_no());
pstmt.setString(idx++, this.getContact());
pstmt.setString(idx++, this.getContact_phone());
pstmt.setString(idx++, this.getContact_handset());
pstmt.setString(idx++, this.getContact_fax());
pstmt.setString(idx++, this.getCountries());
pstmt.setString(idx++, this.getCountries_name());
pstmt.setString(idx++, this.getZxgw_id());
pstmt.setString(idx++, this.getZxgw_name());
pstmt.setString(idx++, this.getTake_out());
pstmt.setString(idx++, this.getGroup_id());
pstmt.executeUpdate();
pstmt.close();
}
}
public void init(int startIndex, int endIndex, String updateDate) throws SQLException {
if (connection == null || connection.isClosed()) {
connection = getConnection();
}
if (startIndex != 0 && endIndex != 0) {
bibQuery =
"SELECT * FROM OLE_DS_BIB_T WHERE BIB_ID BETWEEN "
+ startIndex
+ " AND "
+ endIndex
+ " ORDER BY BIB_ID";
} else if (StringUtils.isNotEmpty(updateDate)) {
updateDate = getDateStringForOracle(updateDate);
bibQuery = "SELECT * FROM OLE_DS_BIB_T where DATE_UPDATED > '" + updateDate + "'";
} else {
bibQuery = "SELECT * FROM OLE_DS_BIB_T ORDER BY BIB_ID";
}
if (!isStaffOnly) {
bibQuery = bibStaffOnly;
holdingsQuery = holdingsQuery + staffOnlyHoldings;
itemQuery = itemQuery + staffOnlyItem;
}
fetchCallNumberType();
fetchReceiptStatus();
fetchAuthenticationType();
fetchItemType();
fetchItemStatus();
fetchStatisticalSearchCode();
fetchExtentOfOwnershipType();
bibConnection = getConnection();
holdingsConnection = getConnection();
itemConnection = getConnection();
bibConnection.setAutoCommit(false);
bibStatement =
bibConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
if (dbVendor.equalsIgnoreCase("oracle")) {
bibStatement.setFetchSize(1);
} else if (dbVendor.equalsIgnoreCase("mysql")) {
bibStatement.setFetchSize(Integer.MIN_VALUE);
}
bibResultSet = bibStatement.executeQuery(bibQuery);
holdingsPreparedStatement = holdingsConnection.prepareStatement(holdingsQuery);
itemPreparedStatement = itemConnection.prepareStatement(itemQuery);
String insertQuery =
"INSERT INTO OLE_DS_BIB_INFO_T(BIB_ID, BIB_ID_STR, TITLE, AUTHOR, PUBLISHER, ISXN) VALUES (?,?,?,?,?,?)";
bibInsertPreparedStatement = connection.prepareStatement(insertQuery);
String updateQuery =
"UPDATE OLE_DS_BIB_INFO_T SET TITLE=?, AUTHOR=?, PUBLISHER=?, ISXN=?, BIB_ID_STR=? WHERE BIB_ID=?";
bibUpdatePreparedStatement = connection.prepareStatement(updateQuery);
}
@Test
public void dbclose() throws SQLException {
conn.prepareStatement("select ?;").setString(1, "Hello World");
conn.prepareStatement("select null;").close();
conn.prepareStatement("select null;").executeQuery().close();
conn.prepareStatement("create table t (c);").executeUpdate();
conn.prepareStatement("select null;");
}
private void prepareStatements(Connection connection) throws SQLException {
createAccountStatement =
connection.prepareStatement("INSERT INTO " + TABLE_NAME + " VALUES (?, 0)");
findAccountStatement =
connection.prepareStatement("SELECT * from " + TABLE_NAME + " WHERE NAME = ?");
deleteAccountStatement =
connection.prepareStatement("DELETE FROM " + TABLE_NAME + " WHERE name = ?");
}
@WebMethod(operationName = "update")
public int update(
@WebParam(name = "id") int id,
@WebParam(name = "token") String token,
@WebParam(name = "user-agent") String ua,
@WebParam(name = "ip") String ip,
@WebParam(name = "topic") String topic,
@WebParam(name = "content") String content) {
Connection conn = null;
PreparedStatement ps = null;
int res = -1;
InformationToken it = new InformationToken();
String email = it.getEmail(token, ua, ip);
if (it.getStatus() != 200) {
return -1 * it.getStatus();
} else {
try {
// new com.mysql.jdbc.Driver();
Class.forName("com.mysql.jdbc.Driver").newInstance();
// conn =
// DriverManager.getConnection("jdbc:mysql://localhost:3306/databasename?user=username&password=password");
String connectionUrl = "jdbc:mysql://localhost:3306/stackexchange";
String connectionUser = "******";
String connectionPassword = "";
conn = DriverManager.getConnection(connectionUrl, connectionUser, connectionPassword);
ps = conn.prepareStatement("select name from user where email = ?");
ps.setString(1, email);
ResultSet rs = ps.executeQuery();
rs.next();
String name = rs.getString("name");
ps =
conn.prepareStatement(
"update question set name = ?, email = ?, topic = ?, content = ? where id = ?;");
ps.setString(1, name);
ps.setString(2, email);
ps.setString(3, topic);
ps.setString(4, content);
ps.setInt(5, id);
res = ps.executeUpdate();
} catch (ClassNotFoundException
| InstantiationException
| IllegalAccessException
| SQLException e) {
} finally {
try {
if (ps != null) ps.close();
} catch (SQLException e) {
}
try {
if (conn != null) conn.close();
} catch (SQLException e) {
}
}
return res;
}
}
/**
* Description of the Method
*
* @param db Description of the Parameter
* @return Description of the Return Value
* @throws SQLException Description of the Exception
*/
public DependencyList processDependencies(Connection db) throws SQLException {
DependencyList dependencyList = new DependencyList();
// Check for orders associated with this customer product
try {
PreparedStatement pst =
db.prepareStatement(
"SELECT COUNT(DISTINCT(order_id)) as orderscount "
+ "FROM customer_product_history "
+ "WHERE customer_product_id = ? ");
pst.setInt(1, this.getId());
ResultSet rs = pst.executeQuery();
if (rs.next()) {
int orderscount = rs.getInt("orderscount");
if (orderscount != 0) {
Dependency thisDependency = new Dependency();
thisDependency.setName("orders");
thisDependency.setCount(orderscount);
thisDependency.setCanDelete(true);
dependencyList.add(thisDependency);
}
}
rs.close();
pst.close();
} catch (SQLException e) {
throw new SQLException(e.getMessage());
}
// check for quotes associated with this customer product
try {
PreparedStatement pst =
db.prepareStatement(
"SELECT COUNT(*) as quotescount FROM order_entry "
+ "WHERE quote_id > -1 AND order_id IN "
+ "(SELECT DISTINCT(order_id) "
+ " FROM customer_product_history "
+ " WHERE customer_product_id = ? )");
pst.setInt(1, this.getId());
ResultSet rs = pst.executeQuery();
if (rs.next()) {
int quotescount = rs.getInt("quotescount");
if (quotescount != 0) {
Dependency thisDependency = new Dependency();
thisDependency.setName("quotes");
thisDependency.setCount(quotescount);
thisDependency.setCanDelete(true);
dependencyList.add(thisDependency);
}
}
rs.close();
pst.close();
} catch (SQLException e) {
throw new SQLException(e.getMessage());
}
return dependencyList;
}
/**
* Consulta as glosas do prestador passado em um determinado periodo
*
* @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as glosas
* @param strNumPeriodo - o periodo de referencia do qual se deseja obter os glosas
* @return um array de glosas do prestador fornecido como paramentro
*/
public static final GlosaPrestador[] buscaGlosaPrest(String strCdContrato, String strNumPeriodo)
throws Exception {
Connection con = ConnectionPool.getConnection();
GlosaPrestador[] glosas = null;
PreparedStatement pst;
ResultSet rset;
int qtdeGlosas = 0;
try {
pst = con.prepareStatement(CONSULTA_GLOSA);
pst.setString(1, strCdContrato);
pst.setString(2, strNumPeriodo);
rset = pst.executeQuery();
if (!rset.next()) {
return null;
} // if ( ! rset.next() )
do {
qtdeGlosas += 1;
} while (rset.next());
System.out.println("qtdeGlosas -> " + qtdeGlosas);
glosas = new GlosaPrestador[qtdeGlosas];
pst = con.prepareStatement(CONSULTA_GLOSA);
pst.setString(1, strCdContrato);
pst.setString(2, strNumPeriodo);
rset = pst.executeQuery();
qtdeGlosas = 0;
while (rset.next()) {
glosas[qtdeGlosas] = montaGlosaPrestador(rset);
qtdeGlosas++;
} // while
} catch (SQLException sqle) {
sqle.printStackTrace();
throw new Exception(
"Não foi possivel estabelecer conexão com a base de "
+ "dados.Erro:\n"
+ sqle.getMessage());
} finally { // catch()
if (con != null) {
con.close();
System.out.println("Fechou a conexao");
} // if
}
return glosas;
} // consultaGlosaPrest()
/** Return the user's secure_hash_key. If one wasn't found create one on the fly. */
private SecureHashKeyResult getSecureHashKey(UserAccount user, Connection c) throws SQLException {
PreparedStatement select = null;
// Create lazily.
PreparedStatement update = null;
int userId = user.getUserID();
boolean justCreated = false;
byte[] key = null;
try {
// TODO: consider having UserManager returning secure_hash_key.
// TODO: We have similar logic in several places for creating secure_hash_key just-in-time.
// D.R.Y. this out. Sorry I couldn't resist using this cliche :)
select = c.prepareStatement("SELECT secure_hash_key FROM dat_user_account WHERE object_id=?");
select.setInt(1, userId);
ResultSet rs = select.executeQuery();
if (!rs.next()) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("dat_user_account row not found");
lmg.param(LoggingConsts.USER_ID, userId);
// possible that the user simply disappeared by the time we got here.
m_logCategory.warn(lmg.toString());
} else {
key = rs.getBytes(1);
if (key == null || key.length == 0) {
// hash key not found; create one on the fly.
update =
c.prepareStatement("UPDATE dat_user_account SET secure_hash_key=? WHERE object_id=?");
key = createNewRandomKey();
update.setBytes(1, key);
update.setInt(2, userId);
int ct = update.executeUpdate();
if (ct != 1) {
LogMessageGen lmg = new LogMessageGen();
lmg.setSubject("Unable to update dat_user_account.secure_hash_key");
lmg.param(LoggingConsts.USER_ID, userId);
m_logCategory.error(lmg.toString());
} else {
justCreated = true;
}
} // needed to set key.
} // user found
} finally {
DbUtils.safeClose(select);
DbUtils.safeClose(update);
}
return new SecureHashKeyResult(key, justCreated);
}