public static List<PriceBar> getPriceDatas(
String sym,
java.util.Date beginDT,
java.util.Date endDT,
int priceMagnifier,
int multiplier) {
List<PriceBar> priceDatas = new ArrayList<PriceBar>();
try {
PreparedStatement datedRangeBySymbol =
DBopsMySql.datedRangeBySymbol(
sym, new Timestamp(beginDT.getTime()), new Timestamp(endDT.getTime()));
ResultSet res = datedRangeBySymbol.executeQuery();
while (res.next()) {
PriceBar priceBar =
new PriceBar(
res.getTimestamp("datetime").getTime(),
res.getDouble("open") / priceMagnifier * multiplier,
res.getDouble("high") / priceMagnifier * multiplier,
res.getDouble("low") / priceMagnifier * multiplier,
res.getDouble("close") / priceMagnifier * multiplier,
res.getLong("volume"));
priceDatas.add(priceBar);
}
// int i = 1;
} catch (SQLException ex) {
MsgBox.err2(ex);
} catch (Exception ex) {
MsgBox.err2(ex);
} finally {
return priceDatas;
}
}
public ArrayList<SmallCard> getCardsFromPlayerID(long id) {
ArrayList<SmallCard> ds = new ArrayList<SmallCard>();
String retval = "";
Statement stmt;
try {
stmt = connection.createStatement();
String sql = "SELECT rowid,* FROM cards Where owner= " + id + ";";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
SmallCard d = new SmallCard();
// ( typeId int, owner int, tradeable int, level int)
d.cardid = rs.getLong(1);
d.typeid = rs.getInt(2);
d.owner = rs.getLong(3);
d.tradeable = rs.getInt(4);
d.level = rs.getInt(5);
ds.add(d);
}
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ds;
}
public ArrayList<Deck> getDecksFromPlayerID(long id) {
ArrayList<Deck> ds = new ArrayList<Deck>();
String retval = "";
Statement stmt;
try {
stmt = connection.createStatement();
String sql = "SELECT rowid,* FROM decks Where owner= " + id + ";";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
Deck d = new Deck();
d.deckname = rs.getString(2);
d.playerowner = rs.getLong(3);
d.ressis = rs.getString(4);
d.valid = rs.getInt(5);
d.timestamp = rs.getLong(6);
String cs = rs.getString(7);
for (String c : cs.split(",")) {
try {
d.cardIds.add(Integer.parseInt(c));
} catch (NumberFormatException nfe) {
}
}
ds.add(d);
}
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return ds;
}
protected CFAccFeeDetailBuff unpackFeeDetailResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackFeeDetailResultSetToBuff";
int idxcol = 1;
CFAccFeeDetailBuff buff = schema.getFactoryFeeDetail().newBuff();
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedByUserId(null);
} else {
buff.setCreatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedAt(null);
} else {
buff.setCreatedAt(CFAccSybaseSchema.convertTimestampString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedByUserId(null);
} else {
buff.setUpdatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedAt(null);
} else {
buff.setUpdatedAt(CFAccSybaseSchema.convertTimestampString(colString));
}
idxcol++;
}
buff.setRequiredTenantId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredFeeId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredFeeDetailId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredDescription(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredAmountCharged(resultSet.getBigDecimal(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
protected CFSecurityHostNodeBuff unpackHostNodeResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackHostNodeResultSetToBuff";
int idxcol = 1;
CFSecurityHostNodeBuff buff = schema.getFactoryHostNode().newBuff();
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedByUserId(null);
} else {
buff.setCreatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedAt(null);
} else {
buff.setCreatedAt(CFSecurityPgSqlSchema.convertTimestampString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedByUserId(null);
} else {
buff.setUpdatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedAt(null);
} else {
buff.setUpdatedAt(CFSecurityPgSqlSchema.convertTimestampString(colString));
}
idxcol++;
}
buff.setRequiredClusterId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredHostNodeId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredDescription(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredHostName(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
@SuppressWarnings("unchecked")
public static <T> T getValueFromResultSet(
int index, final ResultSet resultSet, final Class<T> type) {
try {
if (java.sql.Date.class.isAssignableFrom(type)) {
final long time = resultSet.getLong(index);
return (T) new java.sql.Date(time);
} else if (Time.class.isAssignableFrom(type)) {
final long time = resultSet.getLong(index);
return (T) new java.sql.Time(time);
} else if (Timestamp.class.isAssignableFrom(type)) {
final long time = resultSet.getLong(index);
return (T) new java.sql.Timestamp(time);
} else if (Date.class.isAssignableFrom(type)) {
final long time = resultSet.getLong(index);
return (T) new Date(time);
} else if (Long.class.isAssignableFrom(type)) {
return (T) Long.valueOf(resultSet.getLong(index));
} else if (Integer.class.isAssignableFrom(type)) {
return (T) Integer.valueOf(resultSet.getInt(index));
} else if (Short.class.isAssignableFrom(type)) {
return (T) Short.valueOf(resultSet.getShort(index));
} else if (Float.class.isAssignableFrom(type)) {
return (T) Float.valueOf(resultSet.getFloat(index));
} else if (Double.class.isAssignableFrom(type)) {
return (T) Double.valueOf(resultSet.getDouble(index));
} else if (Boolean.class.isAssignableFrom(type)) {
return (T) Boolean.valueOf(resultSet.getBoolean(index));
} else if (BigDecimal.class.isAssignableFrom(type)) {
return (T) resultSet.getBigDecimal(index);
} else if (CharSequence.class.isAssignableFrom(type)) {
return (T) resultSet.getString(index);
} else if (byte[].class.isAssignableFrom(type)) {
return (T) resultSet.getBytes(index);
} else {
throw new IllegalStateException("Type " + type + " not supported.");
}
} catch (Exception e) {
throw new IllegalStateException(
"Unable to read the value from the resultSet. Index:" + index + ", type: " + type, e);
}
}
protected CFCrmMemoDataBuff unpackMemoDataResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackMemoDataResultSetToBuff";
int idxcol = 1;
CFCrmMemoDataBuff buff = schema.getFactoryMemoData().newBuff();
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedByUserId(null);
} else {
buff.setCreatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedAt(null);
} else {
buff.setCreatedAt(CFCrmMSSqlSchema.convertTimestampString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedByUserId(null);
} else {
buff.setUpdatedByUserId(UUID.fromString(colString));
}
idxcol++;
colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedAt(null);
} else {
buff.setUpdatedAt(CFCrmMSSqlSchema.convertTimestampString(colString));
}
idxcol++;
}
buff.setRequiredTenantId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredMemoId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredContactId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredMemo(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
@Override
protected Vote loadObject(ResultSet resultSet) throws SQLException, MapperException {
Vote vote =
new Vote(
Mappers.getForClass(User.class).loadById(resultSet.getLong("user_id")),
Mappers.getForClass(Answer.class).loadById(resultSet.getLong("answer_id")),
resultSet.getTimestamp("creation_datetime"));
vote.setId(resultSet.getLong("id"));
return vote;
}
protected LumberLog fromResultSet(ResultSet rs) throws SQLException {
LumberLog lumberLog = new LumberLog();
lumberLog.setId(rs.getLong("id"));
lumberLog.setLength(rs.getDouble("length"));
lumberLog.setRealLength(rs.getLong("reallength"));
lumberLog.setVolume(rs.getDouble("volume"));
lumberLog.setRealVolume(rs.getDouble("realvolume"));
lumberLog.setSmallRadius(rs.getDouble("small_diameter"));
lumberLog.setBigRadius(rs.getDouble("big_diameter"));
lumberLog.setLumberType(rs.getLong("lumbertype"));
lumberLog.setLumberClass(rs.getLong("lumberclass"));
lumberLog.setCutPlanId(rs.getLong("planId"));
LumberStack stack = new LumberStack();
stack.setName(rs.getString("stackName"));
stack.setId(rs.getLong("stack"));
lumberLog.setStack(stack);
IDPlate plate = new IDPlate();
plate.setId(rs.getLong("idplate"));
plate.setLabel(rs.getString("plateName"));
lumberLog.setPlate(plate);
lumberLog.setSupplierId(rs.getLong("SupplierId"));
lumberLog.setTransportCertifiateId(rs.getLong("TransportCertificateId"));
if (rs.wasNull()) {
lumberLog.setTransportCertifiateId(null);
}
lumberLog.setMarginPercent(rs.getInt("Margin"));
lumberLog.setMarginVolume(rs.getDouble("MarginVolume"));
lumberLog.setMarginRealVolume(rs.getDouble("RealMarginVolume"));
return lumberLog;
}
public ArrayList<Minion> getDeckFromPlayer(String deckname, long playerid, boolean iswhite) {
ArrayList<Minion> deck = new ArrayList<Minion>();
Statement stmt;
ArrayList<SmallCard> cards = new ArrayList<SmallCard>();
try {
stmt = connection.createStatement();
String sql =
"SELECT * FROM decks Where owner= " + playerid + " AND name = '" + deckname + "';";
ResultSet rs = stmt.executeQuery(sql);
String cs = "";
if (rs.next()) {
cs = rs.getString(6);
}
String inlist = "(";
boolean added = false;
for (String c : cs.split(",")) {
if (added) inlist += ",";
inlist += c;
added = true;
}
inlist += ")";
sql = "SELECT rowid,* FROM cards Where owner= " + playerid + " AND rowid IN " + inlist + ";";
rs = stmt.executeQuery(sql);
while (rs.next()) {
SmallCard d = new SmallCard();
// ( typeId int, owner int, tradeable int, level int)
d.cardid = rs.getLong(1);
d.typeid = rs.getInt(2);
d.owner = rs.getLong(3);
d.tradeable = rs.getInt(4);
d.level = rs.getInt(5);
cards.add(d);
}
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
CardDB cdb = CardDB.getInstance();
UColor colw = UColor.black;
if (iswhite) colw = UColor.white;
for (SmallCard sc : cards) {
Minion m = new Minion(cdb.cardId2Card.get(sc.typeid), sc.cardid, colw);
m.lvl = sc.level;
deck.add(m);
}
return deck;
}
@Override
public Collection<Long> extractData(ResultSet rs) throws SQLException, DataAccessException {
List<Long> goodIds = new ArrayList<>(rs.getFetchSize());
while (rs.next()) {
incGoodCount(/* article id */ rs.getLong(2), /* timestamp */ rs.getTimestamp(3).getTime());
goodIds.add(/* good id*/ rs.getLong(1));
}
return goodIds;
}
ConcurrentHashMap<String, WordWar> loadWars() {
Connection con = null;
Channel channel;
String user;
ConcurrentHashMap<String, WordWar> wars = new ConcurrentHashMap<>(32);
try {
con = pool.getConnection(timeout);
Statement s = con.createStatement();
ResultSet rs = s.executeQuery("SELECT * FROM `wars`");
while (rs.next()) {
channel = Tim.channelStorage.channelList.get(rs.getString("channel"));
user = rs.getString("starter");
WordWar war =
new WordWar(
rs.getLong("base_duration"),
rs.getLong("duration"),
rs.getLong("remaining"),
rs.getLong("time_to_start"),
rs.getInt("total_chains"),
rs.getInt("current_chain"),
rs.getInt("delay"),
rs.getBoolean("randomness"),
rs.getString("name"),
user,
channel,
rs.getInt("id"));
wars.put(war.getName(false).toLowerCase(), war);
}
rs.close();
s.close();
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
}
}
return wars;
}
public Player getPlayerbyName(String name) {
Player p = new Player();
Statement stmt;
boolean added = false;
try {
stmt = connection.createStatement();
String sql = "SELECT rowid,* FROM players Where name= '" + name + "' ;";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println("in db" + rs.getLong(1) + " " + rs.getString(2));
added = true;
p.profileId = rs.getLong(1);
p.name = rs.getString(2);
p.gold = rs.getInt(3);
p.shards = rs.getInt(4);
p.admin = rs.getInt(5);
p.featureType = rs.getInt(6);
p.spectatePermission = rs.getInt(7);
p.acceptTrades = rs.getInt(8);
p.acceptChallenges = rs.getInt(9);
p.rating = rs.getInt(10);
p.head = rs.getInt(11);
p.body = rs.getInt(12);
p.leg = rs.getInt(13);
p.armBack = rs.getInt(14);
p.armFront = rs.getInt(15);
p.idolType = rs.getString(16);
p.idol1 = rs.getInt(17);
p.idol2 = rs.getInt(18);
p.idol3 = rs.getInt(19);
p.idol4 = rs.getInt(20);
p.idol5 = rs.getInt(21);
break;
}
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (added == false) {
long id = insertPlayer(name);
p.name = name;
p.profileId = id;
System.out.println("new player was added: " + id);
}
return p;
}
/**
* This method queries the database to get the Invoice Number associated with the passes Invid
*
* @exception SQLException, if query fails
* @author
*/
public long getInvID(String inv_no) {
String query;
long invID = 0;
Statement stmt = null;
ResultSet rs = null;
query = "select inv_id from inv where inv_no=" + inv_no;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(query);
if (rs.next()) {
invID = rs.getLong(1);
}
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvview: The Invoice ID not retreived", this, ex);
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
}
}
return (invID);
}
private Operator fromResultSet(ResultSet rs) throws SQLException {
long id = rs.getLong(1);
if (rs.wasNull()) id = -1;
String name = rs.getString(2);
if (rs.wasNull()) name = "";
int binding = rs.getInt(3);
if (rs.wasNull()) binding = -1;
String notation = rs.getString(4);
if (rs.wasNull()) notation = "";
String symbol = rs.getString(5);
if (rs.wasNull()) symbol = "";
String symbol_intern = rs.getString(6);
if (rs.wasNull()) symbol_intern = "";
int arity = rs.getInt(7);
if (rs.wasNull()) arity = -1;
String description = rs.getString(8);
if (rs.wasNull()) description = "";
String comment = rs.getString(9);
if (rs.wasNull()) comment = "";
Operator operator =
new Operator(
id, name, binding, notation, symbol, symbol_intern, arity, description, comment);
return operator;
}
/**
* Calculate the balance for a coinbase, to-address, or p2sh address.
*
* @param address The address to calculate the balance of
* @return The balance of the address supplied. If the address has not been seen, or there are no
* outputs open for this address, the return value is 0
* @throws BlockStoreException
*/
public BigInteger calculateBalanceForAddress(Address address) throws BlockStoreException {
maybeConnect();
PreparedStatement s = null;
try {
s =
conn.get()
.prepareStatement(
"select sum(('x'||lpad(substr(value::text, 3, 50),16,'0'))::bit(64)::bigint) "
+ "from openoutputs where toaddress = ?");
s.setString(1, address.toString());
ResultSet rs = s.executeQuery();
if (rs.next()) {
return BigInteger.valueOf(rs.getLong(1));
} else {
throw new BlockStoreException("Failed to execute balance lookup");
}
} catch (SQLException ex) {
throw new BlockStoreException(ex);
} finally {
if (s != null)
try {
s.close();
} catch (SQLException e) {
throw new BlockStoreException("Could not close statement");
}
}
}
/**
* Helper function intended to be overwritten by subclasses. Thsi is where the real requiest for
* IDs happens
*/
protected void performIDRequest() throws Exception {
Connection dbConnection = null;
try {
try {
dbConnection = dataSource.getConnection();
Statement stmt =
dbConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet set = null;
set =
stmt.executeQuery(
"SELECT id, " + dbColumn + " FROM " + dbTable); // $NON-NLS-1$ //$NON-NLS-2$
if (!set.next()) {
set.moveToInsertRow();
set.insertRow();
set.updateLong(dbColumn, NUM_IDS_GRABBED);
set.moveToCurrentRow();
set.next();
}
long nextID = set.getLong(dbColumn);
long upTo = nextID + mCacheQuantity;
set.updateLong(dbColumn, upTo);
set.updateRow();
stmt.close();
setMaxAllowedID(upTo);
setNextID(nextID);
} finally {
if (dbConnection != null) {
dbConnection.close();
}
}
} catch (SQLException e) {
throw new NoMoreIDsException(e);
}
}
@Override
public long insert(ManagerPanel panel, int userId) throws DbException {
PreparedStatement ps = null;
long result;
try {
prepareConnection();
ps = connection.prepareStatement(MANAGER_INSERT, Statement.RETURN_GENERATED_KEYS);
ps.setInt(1, userId);
ps.setDouble(2, panel.getSummaryFinRes().getAmount());
ps.executeUpdate();
try (ResultSet rs = ps.getGeneratedKeys()) {
if (rs.next()) {
result = rs.getLong(1);
} else {
throw new DbException(
"Inserting failed on query: '" + MANAGER_INSERT + "' no ID obtained.");
}
}
} catch (SQLException e) {
throw new DbException("Can't execute SQL = '" + MANAGER_INSERT + "'", e);
} finally {
daoHelper.closeDataBaseEntities(ps, null, connection);
}
return result;
}
@Test
public void testInsertLongRecord() {
Connection db = getConnection();
long app = getAppId();
try {
Record record;
record = new Record();
record.setString("Single_line_text", "foo");
record.setLong("Number", 999);
record.setString("Number_0", "999.99");
db.insert(app, record);
ResultSet rs = db.select(app, "");
if (rs.size() != 1) {
fail("invalid count");
}
rs.next();
assert (rs.getString("Single_line_text") == "foo");
assert (rs.getLong("Number") == 999);
assert (rs.getString("Number_0") == "999.99");
} catch (Exception e) {
fail("db exception:" + e.getMessage());
}
}
// metoda za pretragu po broju stanovnika
public ArrayList<Country> SearchCountryPopulation(long Population) {
ArrayList<Country> countries = new ArrayList<Country>();
try {
Connection connection = getConnected("world");
PreparedStatement statement =
connection.prepareStatement(
"SELECT * FROM country WHERE Population <= " + Population + ";");
ResultSet result = statement.executeQuery();
while (result.next()) {
countries.add(
new Country(
result.getString("Code"),
result.getString("Name"),
result.getString("Continent"),
result.getString("Region"),
result.getDouble("SurfaceArea"),
result.getInt("IndepYear"),
result.getLong("Population"),
result.getDouble("LifeExpectancy"),
result.getDouble("GNP"),
result.getDouble("GNPOld"),
result.getString("LocalName"),
result.getString("GovernmentForm"),
result.getString("HeadOfState"),
result.getInt("Capital"),
result.getString("Code2")));
}
connection.close();
} catch (Exception e) {
System.out.println(e.toString());
return null;
}
return countries;
}
public long insertPlayer(String name) {
long id = -1;
String sql =
"INSERT INTO players ( name, gold, shards, admin , featuretype , spectate , trade , challenge , rating , head, body, leg, armback, armfront, idoltype, idol1, idol2, idol3, idol4, idol5) "
+ "VALUES ( '"
+ name
+ "', 2000, 0, 0, 0, 1, 1, 1, 1000, 37, 11, 40, 1, 17, 'DEFAULT', 2, 2, 2, 2, 2);";
try {
connection.setAutoCommit(false); // Starts transaction.
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()");
rs.next();
id = rs.getLong(1);
stmt.close();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
if (id != -1) {
addDefaultDecks(id);
}
return id;
}
/**
* Execute an insert statement
*
* @param query
*/
public long insert(String query) {
if (PreciousStones.getInstance().getSettingsManager().isDebugsql()) {
PreciousStones.getLog().info(query);
}
try {
Statement statement = getConnection().createStatement();
ResultSet keys = null;
try {
statement.executeUpdate(query);
keys = statement.executeQuery("SELECT last_insert_rowid()");
} finally {
if (keys != null) {
if (keys.next()) {
return keys.getLong(1);
}
}
statement.close();
return 0;
}
} catch (SQLException ex) {
if (!ex.toString().contains("not return ResultSet")) {
log.severe("Error at SQL INSERT Query: " + ex);
log.severe("Query: " + query);
}
}
return 0;
}
public long addNewCard(int typeid, int owner) {
long id = -1;
String sql =
"INSERT INTO cards ( typeId , owner , tradeable , level ) "
+ "VALUES ("
+ typeid
+ ", "
+ owner
+ ", 0, 0);";
try {
/*Statement stmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
stmt.executeUpdate(sql);
ResultSet rs = stmt.getGeneratedKeys();
rs.next();
id = rs.getInt(1);
stmt.close();*/
connection.setAutoCommit(false); // Starts transaction.
Statement stmt = connection.createStatement();
stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery("SELECT last_insert_rowid()");
rs.next();
id = rs.getLong(1);
stmt.close();
connection.commit();
} catch (SQLException e) {
e.printStackTrace();
}
return id;
}
private Vector getNextRow(ResultSet rs, ResultSetMetaData rsmd) throws SQLException {
Vector currentRow = new Vector();
for (int i = 1; i <= rsmd.getColumnCount(); ++i)
switch (rsmd.getColumnType(i)) {
case Types.VARCHAR:
currentRow.addElement(rs.getString(i));
break;
case Types.INTEGER:
currentRow.addElement(new Long(rs.getLong(i)));
break;
case Types.DATE:
currentRow.addElement(rs.getDate(i));
break;
case Types.FLOAT:
currentRow.addElement(rs.getFloat(i));
break;
case Types.CHAR:
currentRow.addElement(rs.getString(i));
break;
case Types.REAL:
currentRow.addElement(rs.getFloat(i));
break;
default:
System.out.println("Type was: " + rsmd.getColumnTypeName(i));
}
return currentRow;
}
@Test
public void testCompareLongGTEDecimal() throws Exception {
long ts = nextTimestamp();
initTableValues(null, ts);
String query = "SELECT l FROM LongInKeyTest where l >= 1.5";
Properties props = new Properties();
props.setProperty(
PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 2)); // Execute at timestamp 2
Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
try {
PreparedStatement statement = conn.prepareStatement(query);
ResultSet rs = statement.executeQuery();
/*
* Failing because we're not converting the constant to the type of the RHS
* when forming the start/stop key.
* For this case, 1.5 -> 1L
* if where l < 1.5 then 1.5 -> 1L and then to 2L because it's not inclusive
*
*/
assertTrue(rs.next());
assertEquals(2, rs.getLong(1));
assertFalse(rs.next());
} finally {
conn.close();
}
}
private boolean validate(int type, String columnName, ResultSet resultSet0, ResultSet resultSet1)
throws SQLException {
switch (type) {
case Types.DATE:
return resultSet0.getDate(columnName).equals(resultSet1.getDate(columnName));
case Types.BOOLEAN:
return resultSet0.getBoolean(columnName) == resultSet1.getBoolean(columnName);
case Types.BIGINT:
return resultSet0.getLong(columnName) == resultSet1.getLong(columnName);
case Types.INTEGER:
return resultSet0.getInt(columnName) == resultSet1.getInt(columnName);
case Types.VARCHAR:
default:
return resultSet0.getString(columnName).equals(resultSet1.getString(columnName));
}
}
public synchronized Disciplina inclui(String nome, String descricao) throws Exception {
// ------- Testa consist�ncia dos dados -------
String testeCons = testaConsistencia(null, nome, descricao);
if (testeCons != null)
throw new Exception("não foi possível inserir devido ao campo " + testeCons + "");
// ------- Insere na base de dados -------
// Inicia a conexão com a base de dados
Connection dbCon = BancoDados.abreConexao();
Statement dbStmt = dbCon.createStatement();
ResultSet dbRs;
String str;
// Pega Id maximo
long maxId = 1;
str = "SELECT Max(cod) AS maxId From Disciplina";
BancoDadosLog.log(str);
dbRs = dbStmt.executeQuery(str);
if (dbRs.next()) {
maxId = dbRs.getLong("maxId");
maxId++;
}
String id = Long.toString(maxId);
nome = StringConverter.toDataBaseNotation(nome);
// Insere o elemento na base de dados
str = "INSERT INTO Disciplina (cod, nome, descricao, desativada)";
str +=
" VALUES ("
+ id
+ ",'"
+ nome
+ "'"
+ ",'"
+ StringConverter.toDataBaseNotation(descricao)
+ "',0)";
BancoDadosLog.log(str);
dbStmt.executeUpdate(str);
// Finaliza conexao
dbStmt.close();
dbCon.close();
// ------- Insere na mem�ria -------
// Cria um novo objeto
Disciplina obj = new Disciplina(id, nome, descricao, false);
// Insere o objeto na lista do gerente
this.listaObj.addElement(obj);
// ---- Cria uma nova turma ----
Turma_ger turmager = new Turma_ger();
turmager.inclui("Turma 1", "", obj);
return obj;
}
protected CFSecurityTSecGroupBuff unpackTSecGroupResultSetToBuff(ResultSet resultSet)
throws SQLException {
final String S_ProcName = "unpackTSecGroupResultSetToBuff";
int idxcol = 1;
CFSecurityTSecGroupBuff buff = schema.getFactoryTSecGroup().newBuff();
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedAt(null);
} else {
buff.setCreatedAt(CFSecurityMySqlSchema.convertTimestampString(colString));
}
}
idxcol++;
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setCreatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setCreatedByUserId(null);
} else {
buff.setCreatedByUserId(UUID.fromString(colString));
}
}
idxcol++;
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedAt(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedAt(null);
} else {
buff.setUpdatedAt(CFSecurityMySqlSchema.convertTimestampString(colString));
}
}
idxcol++;
{
String colString = resultSet.getString(idxcol);
if (resultSet.wasNull()) {
buff.setUpdatedByUserId(null);
} else if ((colString == null) || (colString.length() <= 0)) {
buff.setUpdatedByUserId(null);
} else {
buff.setUpdatedByUserId(UUID.fromString(colString));
}
}
idxcol++;
buff.setRequiredTenantId(resultSet.getLong(idxcol));
idxcol++;
buff.setRequiredTSecGroupId(resultSet.getInt(idxcol));
idxcol++;
buff.setRequiredName(resultSet.getString(idxcol));
idxcol++;
buff.setRequiredRevision(resultSet.getInt(idxcol));
return (buff);
}
public void loadPartyResultsOfUser(Profile profile) {
PreparedStatement ps = null;
try {
/*
* SELECT MIN(pt.time) as best, SUM(pt.time) as time, COUNT(pt.time) as count, p.cubeType as type, p.mode as mode
* FROM " + prefix + "party p, " + prefix + "partytime pt, " + prefix + "user u
* WHERE pt.userID = u.id AND u.name = ? AND p.id = pt.partyID AND pt.time > 0
* GROUP BY p.cubeType, p.mode;
*/
ps =
database
.getConnection()
.prepareStatement(
"SELECT MIN(pt.time) as best, SUM(pt.time) as time, COUNT(pt.time) as count, p.cubeType as type, p.mode as mode FROM "
+ prefix
+ "party p, "
+ prefix
+ "partytime pt, "
+ prefix
+ "user u WHERE pt.userID = u.id AND u.name = ? AND p.id = pt.partyID AND pt.time > 0 GROUP BY p.cubeType, p.mode;");
ps.setString(1, profile.getUser().getUsername());
ResultSet result = ps.executeQuery();
while (result.next()) {
String type = result.getString("type");
long time = result.getLong("time");
long count = result.getLong("count");
long mode = result.getLong("mode");
long best = result.getLong("best");
profile.addCubeTimes(type, time, count, best, mode);
}
return;
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeOrFail(ps);
}
return;
}
public long getOID(Connection conn) throws SQLException {
String sql = "SELECT last_insert_id()";
ResultSet rs = executeQuery(sql, conn);
if (rs.isBeforeFirst()) rs.next();
long oid = rs.getLong(1);
rs.close();
return oid;
}
