private void getPokemon() {
Connection con = null;
try {
con = pool.getConnection(timeout);
try (Statement s = con.createStatement()) {
ResultSet rs;
s.executeQuery("SELECT `name` FROM `pokemon`");
rs = s.getResultSet();
this.pokemon.clear();
while (rs.next()) {
this.pokemon.add(rs.getString("name"));
}
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);
}
}
}
private void getIgnoreList() {
Connection con = null;
try {
con = pool.getConnection(timeout);
Statement s = con.createStatement();
s.executeQuery("SELECT `name`, `type` FROM `ignores`");
ResultSet rs = s.getResultSet();
this.ignore_list.clear();
this.soft_ignore_list.clear();
while (rs.next()) {
if (rs.getString("type").equals("hard")) {
this.ignore_list.add(rs.getString("name").toLowerCase());
} else {
this.soft_ignore_list.add(rs.getString("name").toLowerCase());
}
}
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);
}
}
}
// execute and get results
private void execute(Connection conn, String text, Writer writer, boolean commaSeparator)
throws SQLException {
BufferedWriter buffer = new BufferedWriter(writer);
Statement stmt = conn.createStatement();
stmt.execute(text);
ResultSet rs = stmt.getResultSet();
ResultSetMetaData metadata = rs.getMetaData();
int nbCols = metadata.getColumnCount();
String[] labels = new String[nbCols];
int[] colwidths = new int[nbCols];
int[] colpos = new int[nbCols];
int linewidth = 1;
// read each occurrence
try {
while (rs.next()) {
for (int i = 0; i < nbCols; i++) {
Object value = rs.getObject(i + 1);
if (value != null) {
buffer.write(value.toString());
if (commaSeparator) buffer.write(",");
}
}
}
buffer.flush();
rs.close();
} catch (IOException ex) {
if (Debug.isDebug()) ex.printStackTrace();
// ok, exit from the loop
} catch (SQLException ex) {
if (Debug.isDebug()) ex.printStackTrace();
}
}
/* return status codes for account/device */
public static int[] getStatusCodes(String accountID, String deviceID) throws DBException {
/* account-id specified? */
if (StringTools.isBlank(accountID)) {
return new int[0];
}
/* device-id specified? */
if (StringTools.isBlank(deviceID)) {
deviceID = ALL_DEVICES;
}
/* select */
// DBSelect: SELECT statucCode FROM StatusCode WHERE (accountID='acct') AND (deviceID='*') ORDER
// BY statucCode
DBSelect<StatusCode> dsel = new DBSelect<StatusCode>(StatusCode.getFactory());
dsel.setSelectedFields(StatusCode.FLD_statusCode);
DBWhere dwh = dsel.createDBWhere();
dsel.setWhere(
dwh.WHERE_(
dwh.AND(
dwh.EQ(StatusCode.FLD_accountID, accountID),
dwh.EQ(StatusCode.FLD_deviceID, deviceID))));
dsel.setOrderByFields(StatusCode.FLD_statusCode);
/* get list */
java.util.List<Integer> codeList = new Vector<Integer>();
Statement stmt = null;
ResultSet rs = null;
try {
stmt = DBConnection.getDefaultConnection().execute(dsel.toString());
rs = stmt.getResultSet();
while (rs.next()) {
int code = rs.getInt(StatusCode.FLD_statusCode);
codeList.add(new Integer(code));
}
} catch (SQLException sqe) {
throw new DBException("Getting StatusCode List", sqe);
} finally {
if (rs != null) {
try {
rs.close();
} catch (Throwable t) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Throwable t) {
}
}
}
/* return array of status codes */
int codeListInt[] = new int[codeList.size()];
for (int i = 0; i < codeListInt.length; i++) {
codeListInt[i] = codeList.get(i).intValue();
}
return codeListInt;
}
/** Method declaration Adjust this method for large strings...ie multi megabtypes. */
void execute() {
String sCmd = null;
if (4096 <= ifHuge.length()) {
sCmd = ifHuge;
} else {
sCmd = txtCommand.getText();
}
if (sCmd.startsWith("-->>>TEST<<<--")) {
testPerformance();
return;
}
String g[] = new String[1];
lTime = System.currentTimeMillis();
try {
sStatement.execute(sCmd);
lTime = System.currentTimeMillis() - lTime;
int r = sStatement.getUpdateCount();
if (r == -1) {
formatResultSet(sStatement.getResultSet());
} else {
g[0] = "update count";
gResult.setHead(g);
g[0] = String.valueOf(r);
gResult.addRow(g);
}
addToRecent(txtCommand.getText());
} catch (SQLException e) {
lTime = System.currentTimeMillis() - lTime;
g[0] = "SQL Error";
gResult.setHead(g);
String s = e.getMessage();
s += " / Error Code: " + e.getErrorCode();
s += " / State: " + e.getSQLState();
g[0] = s;
gResult.addRow(g);
}
updateResult();
System.gc();
}
/* return list of all Devices within the specified DeviceGroup (NOT SCALABLE BEYOND A FEW HUNDRED GROUPS) */
public static java.util.List<String> getUsersForGroup(String acctId, String groupId)
throws DBException {
/* valid account/groupId? */
if (StringTools.isBlank(acctId)) {
return null;
} else if (StringTools.isBlank(groupId)) {
return null;
}
/* get db selector */
DBSelect dsel = GroupList._getUserListSelect(acctId, groupId);
if (dsel == null) {
return null;
}
/* read users for group */
java.util.List<String> usrList = new Vector<String>();
DBConnection dbc = null;
Statement stmt = null;
ResultSet rs = null;
try {
dbc = DBConnection.getDefaultConnection();
stmt = dbc.execute(dsel.toString());
rs = stmt.getResultSet();
while (rs.next()) {
String usrId = rs.getString(GroupList.FLD_userID);
usrList.add(usrId);
}
} catch (SQLException sqe) {
throw new DBException("Get Group GroupeList", sqe);
} finally {
if (rs != null) {
try {
rs.close();
} catch (Throwable t) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Throwable t) {
}
}
DBConnection.release(dbc);
}
/* return list */
return usrList;
}
public final LentilCursor read(final String sql) {
final ResultSet results;
try {
final Statement stmt = getConnection().createStatement();
stmt.execute(sql);
results = stmt.getResultSet();
} catch (SQLException e) {
throw new IllegalStateException(e);
}
return new LentilCursor(results);
}
private void checkDatabaseContents(String query, String[][] correct) throws Exception {
Connection con2 = TestUtil.openDB();
Statement s = con2.createStatement();
assertFalse(s.execute("set time zone 'UTC'"));
assertTrue(s.execute(query));
ResultSet rs = s.getResultSet();
for (int j = 0; j < correct.length; ++j) {
assertTrue(rs.next());
for (int i = 0; i < correct[j].length; ++i) {
assertEquals("On row " + (j + 1), correct[j][i], rs.getString(i + 1));
}
}
assertFalse(rs.next());
rs.close();
s.close();
con2.close();
}
public static void main(String args[]) throws IOException {
try {
Scanner in = args.length == 0 ? new Scanner(System.in) : new Scanner(Paths.get(args[0]));
try (Connection conn = getConnection()) {
Statement stat = conn.createStatement();
while (true) {
if (args.length == 0) System.out.println("Enter command or EXIT to exit:");
if (!in.hasNextLine()) return;
String line = in.nextLine();
if (line.equalsIgnoreCase("EXIT")) return;
if (line.trim().endsWith(";")) // remove trailing semicolon
{
line = line.trim();
line = line.substring(0, line.length() - 1);
}
try {
boolean isResult = stat.execute(line);
if (isResult) {
ResultSet rs = stat.getResultSet();
showResultSet(rs);
} else {
int updateCount = stat.getUpdateCount();
System.out.println(updateCount + " rows updated");
}
} catch (SQLException ex) {
for (Throwable e : ex) e.printStackTrace();
}
}
}
} catch (SQLException e) {
for (Throwable t : e) t.printStackTrace();
}
}
private void getChannelGroups() {
Connection con = null;
try {
con = pool.getConnection(timeout);
Statement s = con.createStatement();
s.executeQuery(
"SELECT `name`, `channels`.`channel` FROM `channel_groups` INNER JOIN `channels` ON (`channel_groups`.`channel_id` = `channels`.`id`)");
ResultSet rs = s.getResultSet();
this.channel_groups.clear();
while (rs.next()) {
if (!this.channel_groups.containsKey(rs.getString("name").toLowerCase())) {
this.channel_groups.put(rs.getString("name").toLowerCase(), new HashSet<>());
}
this.channel_groups
.get(rs.getString("name").toLowerCase())
.add(this.channel_data.get(rs.getString("channel")));
}
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);
}
}
}
/**
* ** Returns true if the specified key attribute exists in the table ** @param altIndexName The
* alternate index name, or null to use the primary index ** @param whereKeyType The partial key
* match type ** @return True if the specified key attribute exists in the table, false otherwise
*/
protected boolean _exists(String altIndexName, int whereKeyType)
throws SQLException, DBException {
/* key fields */
boolean usePrimaryKey = StringTools.isBlank(altIndexName);
DBField kfld[] = usePrimaryKey ? this.getKeyFields() : this.getAltKeyFields(altIndexName);
if (ListTools.isEmpty(kfld)) {
throw new DBException("No keys found!");
}
/* check last key for "auto_increment" */
if (whereKeyType == DBWhere.KEY_FULL) {
DBField lastField = kfld[kfld.length - 1];
if (lastField.isAutoIncrement()
&& !this.getFieldValues().hasFieldValue(lastField.getName())) {
// full key requested and last key is auto_increment, which is missing
return false;
}
}
// DBSelect: SELECT <Keys> FROM <TableName> <KeyWhere>
String firstKey = kfld[0].getName();
DBSelect<gDBR> dsel = new DBSelect<gDBR>(this.getFactory());
dsel.setSelectedFields(firstKey);
dsel.setWhere(this._getWhereClause(altIndexName, whereKeyType));
/* get keyed record */
DBConnection dbc = null;
Statement stmt = null;
ResultSet rs = null;
boolean exists = false;
try {
dbc = DBConnection.getDefaultConnection();
stmt = dbc.execute(dsel.toString()); // may throw DBException
rs = stmt.getResultSet();
exists = rs.next();
} catch (SQLException sqe) {
if (sqe.getErrorCode() == DBFactory.SQLERR_TABLE_NOTLOCKED) {
// MySQL: This case has been seen on rare occasions. Not sure what causes it.
Print.logError("SQL Lock Error: " + sqe);
Print.logError("Hackery! Forcing lock on table: " + this.getTableName());
if (DBProvider.lockTableForRead(this.getTableName(), true)) { // may throw DBException
stmt = dbc.execute(dsel.toString()); // may throw SQLException, DBException
rs = stmt.getResultSet(); // SQLException
exists = rs.next(); // SQLException
DBProvider.unlockTables(); // DBException
}
} else {
throw sqe;
}
} finally {
if (rs != null) {
try {
rs.close();
} catch (Throwable t) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (Throwable t) {
}
}
DBConnection.release(dbc);
}
return exists;
}
protected ResultSet executeStatement(Statement statement, String query) throws SQLException {
if (statement.execute(query)) {
return statement.getResultSet();
}
return null;
}
public static void main(String args[]) {
Connection con = null;
Statement stm = null;
try {
int count;
System.out.println("1...");
Class.forName("com.timesten.jdbc.TimesTenDriver").newInstance();
System.out.println("2...");
con = DriverManager.getConnection("jdbc:timesten:direct:WebNmsDB ", "root", "");
System.out.println("3...");
stm = con.createStatement();
System.out.println("connection established");
for (int j = 1; j <= 10; j++) {
System.out.println("enter 1 to add record");
System.out.println("enter 2 to rem record");
System.out.println("enter 3 to view all the records");
System.out.println("enter 4 to view particular record");
System.out.println("enter 5 to exit");
InputStreamReader reader = new InputStreamReader(System.in);
BufferedReader br = new BufferedReader(reader);
String str = br.readLine();
int i = Integer.parseInt(str);
String quote = "'";
ResultSet rs = null;
switch (i) {
case 1:
System.out.println("Enter the name ");
InputStreamReader reader1 = new InputStreamReader(System.in);
BufferedReader br1 = new BufferedReader(reader1);
String str1 = br1.readLine();
System.out.println("Enter the DOB as YYYY-MM-DD");
InputStreamReader reader2 = new InputStreamReader(System.in);
BufferedReader br2 = new BufferedReader(reader2);
String str2 = br2.readLine();
System.out.println("Enter the DOJ as YYYY-MM-DD");
InputStreamReader reader3 = new InputStreamReader(System.in);
BufferedReader br3 = new BufferedReader(reader3);
String str3 = br3.readLine();
System.out.println("Enter the class");
InputStreamReader reader4 = new InputStreamReader(System.in);
BufferedReader br4 = new BufferedReader(reader4);
String str4 = br4.readLine();
stm.executeUpdate(
"Insert into student (Name,DOB,DOJ,Class) values("
+ quote
+ str1
+ quote
+ ","
+ quote
+ str2
+ quote
+ ","
+ quote
+ str2
+ quote
+ ","
+ quote
+ str4
+ quote
+ ");");
// stm.close();
break;
case 2:
System.out.println("Enter the name whose record to be deleted");
InputStreamReader reader5 = new InputStreamReader(System.in);
BufferedReader br5 = new BufferedReader(reader5);
String str5 = br5.readLine();
stm.executeUpdate("delete from student where Name=" + quote + str5 + quote + ";");
break;
case 3:
stm.executeQuery("select * from Node;");
rs = stm.getResultSet();
System.out.println("Name DOB DOJ CLASS");
while (rs.next() != false) {
String name = rs.getString("NAME");
System.out.print(name + " ");
String dob = rs.getString("ISROUTER");
System.out.print(dob + " ");
String doj = rs.getString("OWNERNAME");
/*System.out.print(doj+" ");
int Class=rs.getInt("class");
System.out.println(Class+" ");*/
}
break;
case 4:
System.out.println("Enter the name whose record to be viewed");
InputStreamReader reader6 = new InputStreamReader(System.in);
BufferedReader br6 = new BufferedReader(reader6);
String str6 = br6.readLine();
stm.executeQuery("select * from student where Name=" + quote + str6 + quote + ";");
rs = stm.getResultSet();
System.out.println("Name DOB DOJ CLASS");
while (rs.next() != false) {
String name = rs.getString("Name");
System.out.print(name + " ");
String dob = rs.getString("DOB");
System.out.print(dob + " ");
String doj = rs.getString("DOJ");
System.out.print(doj + " ");
int Class = rs.getInt("class");
System.out.println(Class + " ");
}
break;
case 5:
System.out.println("Logging out");
System.exit(0);
}
}
} catch (Exception e) {
System.out.println(e);
}
}
