// 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;
}
/* 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 int getNext() {
int n = 0;
try {
stmt.executeQuery("SELECT IDEdicion FROM Edicion ORDER BY IDEdicion DESC LIMIT 1");
ResultSet rs = stmt.getResultSet();
rs.next();
n = rs.getInt("IDEdicion");
rs.close();
return n + 1;
} catch (SQLException e) {
System.out.println("Cannot getNext()" + e);
}
return n;
}
public int getNumero(int ID) {
int numero = 0;
try {
stmt.executeQuery("SELECT Numero FROM Edicion WHERE IDEdicion = " + ID);
ResultSet rs = stmt.getResultSet();
rs.next();
numero = rs.getInt("Numero");
rs.close();
return (numero);
} catch (SQLException e) {
System.out.println("Cannot getNumero()" + e);
}
return numero;
}
public boolean validarEdicion(int ID) {
int IDE;
try {
stmt.executeQuery("SELECT IDEdicion FROM Edicion WHERE IDEdicion = " + ID);
ResultSet rs = stmt.getResultSet();
rs.next();
IDE = rs.getInt("IDEdicion");
rs.close();
return IDE == ID;
} catch (SQLException e) {
System.out.println("Cannot getID()" + e);
}
return false;
}
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);
}
public int getCantidad(int ID) {
int cantidad = 0;
;
try {
stmt.executeQuery("SELECT Cantidad FROM Edicion WHERE IDEdicion = " + ID);
ResultSet rs = stmt.getResultSet();
rs.next();
cantidad = rs.getInt("Cantidad");
rs.close();
return (cantidad);
} catch (SQLException e) {
System.out.println("Cannot getCantidad()" + e);
}
return cantidad;
}
public int[] getArticulos(int IDE) {
int count;
int[] a = new int[1];
try {
stmt.executeQuery("SELECT COUNT(*) as cant FROM Articulo WHERE IDEdicion = " + IDE);
ResultSet rs = stmt.getResultSet();
rs.next();
count = rs.getInt("cant");
rs.close();
stmt.executeQuery("Select IDArticulo FROM Articulo Where IDEdicion = " + IDE);
rs = stmt.getResultSet();
rs.next();
int[] anunciosEnc = new int[count];
for (int i = 0; i < count; i++) {
anunciosEnc[i] = rs.getInt("IDArticulo");
rs.next();
}
rs.close();
return anunciosEnc;
} catch (SQLException e) {
System.out.println("Cannot getArticulos()" + e);
}
return a;
}
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();
}
}
/**
* ** 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;
}
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);
}
}
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
out = response.getWriter();
HttpSession session;
String connectionURL = USERS_INFO;
Connection connection = null;
ResultSet rs;
String email = "";
String userName = "";
String passwrd = "";
String remoteAddr = "";
response.setContentType("text/html");
int error = 0;
try {
// Load the database driver
Class.forName("com.mysql.jdbc.Driver");
// Get a Connection to the database
connection = DriverManager.getConnection(connectionURL, USERNAME, PASSWORD);
// Add the data into the database
String sql = "SELECT username, email FROM users";
Statement s = connection.createStatement();
s.executeQuery(sql);
rs = s.getResultSet();
while (rs.next()) {
email = rs.getString("email");
userName = rs.getString("username");
if (email.equals(request.getParameter("email"))) {
String message = "Email" + email + "already exists";
request.setAttribute("RegisterMessage", message);
RequestDispatcher view = request.getRequestDispatcher("signup.jsp");
view.forward(request, response);
error = 1;
}
if (userName.equals(request.getParameter("user"))) {
String message = "Username '" + userName + "' already exists";
request.setAttribute("RegisterMessage", message);
RequestDispatcher view = request.getRequestDispatcher("signup.jsp");
view.forward(request, response);
error = 1;
}
if (error == 1) {
break;
}
}
passwrd = request.getParameter("pass");
if (!passwrd.equalsIgnoreCase(request.getParameter("pass2"))) {
String message = "Passwords don't match";
request.setAttribute("RegisterMessage", message);
RequestDispatcher view = request.getRequestDispatcher("signup.jsp");
view.forward(request, response);
error = 1;
}
remoteAddr = request.getRemoteAddr();
// ReCaptchaImpl reCaptcha = new ReCaptchaImpl();
// reCaptcha.setPrivateKey("6LezstoSAAAAAEE9lfB6TR2kEX81_peDt4n03K4l");
// String challenge = request.getParameter("recaptcha_challenge_field");
// String uresponse = request.getParameter("recaptcha_response_field");
// ReCaptchaResponse reCaptchaResponse = reCaptcha.checkAnswer(remoteAddr, challenge,
// uresponse);
/*if (!reCaptchaResponse.isValid()) {
print_wrong_once(error);
out.print("<h2 align=\"center\">Validation code is wrong.</h2>");
error = 1;
}*/
if (error == 1) {
rs.close();
s.close();
return;
} else {
sql =
"INSERT INTO users_info.users (`username`, `password`, `email`) VALUES ('"
+ request.getParameter("user")
+ "', '"
+ request.getParameter("pass")
+ "', '"
+ request.getParameter("email")
+ "')";
s.executeUpdate(sql);
File dir = new File(mainPath + "/" + request.getParameter("user"));
dir.mkdir();
session = request.getSession(true);
session.setAttribute("username", request.getParameter("user"));
response.sendRedirect(response.encodeRedirectURL("XmlParser"));
}
rs.close();
s.close();
connection.close();
} catch (Exception e) {
System.out.println("Unexpected error: " + e);
}
}