static void attributeMean() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
Statement meanQuery = con.createStatement();
ResultSet mean = meanQuery.executeQuery("select AVG(sal) from employ where sal IS NOT NULL");
if (mean.next()) {
fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + mean.getInt(1) + ")");
}
/*fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = COALESCE(sal,AVG(sal))");*/
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println("Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
static void probableValue() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
Statement freqQuery = con.createStatement();
ResultSet freq =
freqQuery.executeQuery(
"select sal,count(*) as total from employ group by sal order by total desc");
if (freq.next()) {
fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + freq.getInt(1) + ")");
}
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public void addOrder(ShoppingCart cart, String userName, Order order) throws SQLException {
Connection c = null;
PreparedStatement ps = null;
Statement s = null;
ResultSet rs = null;
boolean transactionState = false;
try {
s = c.createStatement();
transactionState = c.getAutoCommit();
int userKey = getUserKey(userName, c, ps, rs);
c.setAutoCommit(false);
addSingleOrder(order, c, ps, userKey);
int orderKey = getOrderKey(s, rs);
addLineItems(cart, c, orderKey);
c.commit();
order.setOrderKeyFrom(orderKey);
} catch (SQLException sqlx) {
s = c.createStatement();
c.rollback();
throw sqlx;
} finally {
try {
c.setAutoCommit(transactionState);
dbPool.release(c);
if (s != null) s.close();
if (ps != null) ps.close();
if (rs != null) rs.close();
} catch (SQLException ignored) {
}
}
}
@Override
public void callInsert(String statementString) throws SQLException {
/* String sqlString = statementString;
CallableStatement statement = sqlConn.prepareCall(sqlString);
statement.execute();
int iStatus = statement.getInt(1);
sqlConn.commit();*/
Statement statement = sqlConn.createStatement();
// statement.executeU(statementString);
Statement s = null;
try {
s = sqlConn.createStatement();
} catch (SQLException se) {
System.out.println(
"We got an exception while creating a statement:"
+ "that probably means we're no longer connected.");
se.printStackTrace();
System.exit(1);
}
int m = 0;
try {
m = s.executeUpdate(statementString);
} catch (SQLException se) {
System.out.println(
"We got an exception while executing our query:"
+ "that probably means our SQL is invalid");
se.printStackTrace();
System.exit(1);
}
System.out.println("Successfully modified " + m + " rows.\n");
}
static void globalConstant() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
fill = con.createStatement();
fill.executeUpdate("UPDATE employ set sal = " + GlobalConstant + " where sal IS NULL");
// fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + GlobalConstant + ")");
// System.out.println("Entered..!");
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println("Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
public Nutzer insert(Nutzer a) {
Connection con = DBConnection.connection();
try {
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT MAX(NutzerId) AS maxid " + "FROM nutzer");
if (rs.next()) {
a.setId(rs.getInt("maxid") + 1);
stmt = con.createStatement();
stmt.executeUpdate(
"INSERT INTO nutzer (NutzerId, Email, Vorname, Nachname, Passwort)"
+ "VALUES ("
+ a.getId()
+ ",'"
+ a.getEmail()
+ "','"
+ a.getVorname()
+ "','"
+ a.getNachname()
+ "','"
+ a.getPassword()
+ "')");
}
} catch (SQLException e) {
e.printStackTrace();
}
return a;
}
public static void main(String args[])
throws TwitterException, SQLException, LangDetectException, ParseException {
DetectorFactory.loadProfile("/Users/Andrey/Downloads/langdetect-09-13-2011/profiles/");
Connection conn =
DriverManager.getConnection(
"jdbc:mysql://localhost/grupa2?useUnicode=yes&characterEncoding=utf8",
"group2",
"PU7CKJc8M7LwGzUc");
int n = 0;
Statement selectDate = conn.createStatement();
ResultSet rs = selectDate.executeQuery("SELECT MAX(date) FROM tweet");
rs.next();
String getDate = rs.getString("MAX(date)");
Statement select = conn.createStatement();
ResultSet select_result =
select.executeQuery("SELECT id, text FROM brand WHERE parent_id!='NULL' AND parent_id!=0");
while (select_result.next()) {
int id = select_result.getInt(1);
String text = '"' + select_result.getString(2) + '"';
n = searchForTwits(id, text, conn, getDate);
System.out.println(n);
}
conn.close();
}
@Test
public void testUpsertValuesWithDate() throws Exception {
long ts = nextTimestamp();
Properties props = new Properties();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts));
Connection conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute("create table UpsertDateTest (k VARCHAR not null primary key,date DATE)");
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
conn = DriverManager.getConnection(getUrl(), props);
conn.createStatement()
.execute("upsert into UpsertDateTest values ('a',to_date('2013-06-08 00:00:00'))");
conn.commit();
conn.close();
props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
conn = DriverManager.getConnection(getUrl(), props);
ResultSet rs =
conn.createStatement().executeQuery("select k,to_char(date) from UpsertDateTest");
assertTrue(rs.next());
assertEquals("a", rs.getString(1));
assertEquals("2013-06-08 00:00:00", rs.getString(2));
}
private void okActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_okActionPerformed
try {
if (txtuser.getText().equals("") || txtpassword.getText().equals("")) {
JOptionPane.showMessageDialog(this, "Please Enter Username & Password", "Message", WIDTH);
} else { // connect
Class.forName("com.mysql.jdbc.Driver");
Connection conn =
DriverManager.getConnection(
"jdbc:mysql://localhost:3306/student_information", "root", "123");
Statement stm = conn.createStatement();
String qry = "select password from login where username = '******' ;";
ResultSet rst = stm.executeQuery(qry);
Statement stm2 = conn.createStatement();
String qry2 = "select state from login where username = '******'; ";
ResultSet rst2 = stm2.executeQuery(qry2);
if (!rst.next()) { // validate username
JOptionPane.showMessageDialog(this, "Invalid Username", "Error", WIDTH);
} else if (rst.getString("password").equals(txtpassword.getText())) { // check password
MainWindow m = new MainWindow(); // open main
m.setVisible(true);
m.lbluser.setText(txtuser.getText());
if (rst2.next() && rst2.getInt("State") == 1) { // block lecturer
m.lblState.setText("Admin");
} else {
m.lblState.setText("Lecturer");
/* MainWindow mw1 = new MainWindow();
mw1.btnStudentm.setVisible(false);
mw1.btnCoursem.setVisible(false);
mw1.btnLecturerm.setVisible(false);
mw1.btnAdmin.setVisible(false);
mw1.lbllec.setVisible(false);*/
}
this.setVisible(false);
txtuser.setText("");
txtpassword.setText("");
} else {
JOptionPane.showMessageDialog(this, "Invalid Password", "Error", WIDTH);
}
}
} catch (Exception e) {
JOptionPane.showMessageDialog(this, "Error in Excecution " + e, "Error", WIDTH);
}
} // GEN-LAST:event_okActionPerformed
static void specificAttributeMean() {
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
fill = con.createStatement();
fill.executeUpdate(
"UPDATE employ a set sal = (select AVG(sal) from employ where sal IS NOT NULL and grade = (select grade from employ b where sal IS NULL and a.grade=b.grade and ROWNUM <= 1)) where sal IS NULL");
st = con.createStatement();
rs = st.executeQuery("select * from employ");
System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
while (rs.next()) {
System.out.println(
">>"
+ rs.getString(1)
+ "\t"
+ rs.getString(2)
+ "\t"
+ rs.getString(3)
+ "\t"
+ rs.getString(4)
+ "\t"
+ rs.getString(5));
}
} catch (Exception e) {
System.out.println("Error: " + e);
}
}
@EventHandler(priority = EventPriority.NORMAL)
public void onDeath(final EntityDeathEvent event) throws SQLException {
if (event.getEntity() instanceof Player) {
Player player = (Player) event.getEntity();
List<ItemStack> list = event.getDrops();
int size = list.size();
Connection conn =
DriverManager.getConnection("jdbc:sqlite:plugins/KeepShit/" + player.getName() + ".db");
conn.createStatement()
.execute(
"Create Table " + player.getName() + "(id int primary key, TypeID int, Amount int)");
for (int temp = 0; temp < size; temp++) {
conn.createStatement()
.execute(
"Insert into "
+ player.getName()
+ " Values("
+ (temp + 1)
+ ","
+ list.get(temp).getTypeId()
+ ","
+ list.get(temp).getAmount()
+ ")");
}
event.getDrops().clear();
}
}
/** erstellt die nötigen Tabellen in der Datenbank, falls sie noch nicht exisiteren */
private void erstelleTabellen() {
try {
// erstellt die Benutzer-Tabelle
String dbQuery =
"CREATE TABLE IF NOT EXISTS benutzer ("
+ "bNr INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "benutzername VARCHAR(45) NOT NULL,"
+ "statusnachricht text,"
+ "statussymbol VARCHAR(4),"
+ "status_aktuell_seit INTEGER,"
+ "passwort VARCHAR(50) NOT NULL,"
+ "online TINYINT(1));";
con.createStatement().executeUpdate(dbQuery);
// erstellt die Kontaklisten-Tabelle
dbQuery =
"CREATE TABLE IF NOT EXISTS kontaktliste ("
+ "kId INTEGER PRIMARY KEY AUTOINCREMENT,"
+ "besitzer INTEGER,"
+ "kontakt INTEGER,"
+ "foreign key (besitzer) references benutzer(bNr),"
+ "foreign key (kontakt) references benutzer(bNr))";
con.createStatement().executeUpdate(dbQuery);
} catch (SQLException e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
System.exit(1);
}
}
private synchronized void init() throws SQLException {
if (isClosed) return;
// do tables exists?
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(TABLE_NAMES_SELECT_STMT);
ArrayList<String> missingTables = new ArrayList(TABLES.keySet());
while (rs.next()) {
String tableName = rs.getString("name");
missingTables.remove(tableName);
}
for (String missingTable : missingTables) {
try {
Statement createStmt = conn.createStatement();
// System.out.println("Adding table "+ missingTable);
createStmt.executeUpdate(TABLES.get(missingTable));
createStmt.close();
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
}
}
}
private void revokeDelegatedPrivilege(Connection dConn, Connection gConn, String tableName) {
int num = SQLTest.random.nextInt(SQLTest.numOfWorkers) + 1;
ArrayList<SQLException> exceptionList = new ArrayList<SQLException>();
String grantees = getGrantees(num);
StringBuffer sql = new StringBuffer();
int whichPriv = SQLTest.random.nextInt(tablePriv.length);
sql.append("revoke " + tablePriv[whichPriv] + " on " + tableName + " from " + grantees);
Log.getLogWriter().info("security statement is " + sql.toString());
try {
Statement stmt = dConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
dConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleDerbySQLException(se, exceptionList);
}
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
gConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleGFGFXDException(se, exceptionList);
}
}
private static void addCourse(Connection conn, String stu_no) throws SQLException {
String cno = readEntry("Course number : ");
String sno = readEntry("Section number : ");
String course_query =
"SELECT Section_identifier FROM SECTION WHERE Section_identifier="
+ sno
+ " AND Course_number='"
+ cno
+ "' AND Semester='Spring' AND YEAR=TO_DATE('2007','YYYY')";
// System.out.println(course_query);
Statement s1 = conn.createStatement();
ResultSet r1 = s1.executeQuery(course_query);
if (r1.next()) {
String addc =
"INSERT INTO GRADE_REPORT (Student_number, Section_identifier, Grade) VALUES ("
+ stu_no
+ ", "
+ sno
+ ", 'NULL')";
Statement s2 = conn.createStatement();
System.out.println(addc);
try {
s2.executeUpdate(addc);
System.out.println("Enrollment successfull");
} catch (SQLException e) {
System.out.println("Cannot add course into table");
}
} else {
System.out.println("Course not found");
}
s1.close();
}
public static boolean deleteUser(String user) {
Connection con = null;
CallableStatement cs = null;
try {
con = LoginBean.getConnection();
Statement st = null;
Statement st2 = null;
Statement st4 = null;
st2 = con.createStatement();
String sql4 = ("delete from items where seller = '" + user + "';");
st2.execute(sql4);
st4 = con.createStatement();
String sql5 = ("delete from bids where bidder = '" + user + "';");
st4.execute(sql5);
st = con.createStatement();
String sql3 = ("delete from users where username = '******';");
st.execute(sql3);
return true;
} catch (Exception ex) {
System.out.println(ex.getMessage());
return false;
} finally {
try {
cs.close();
con.close();
} catch (Exception ex) {
}
}
}
public void provideAllPrivToAll(Connection dConn, Connection gConn) {
ArrayList<SQLException> exList = new ArrayList<SQLException>();
for (int i = 0; i < tableNames.length; i++) {
StringBuffer sql = new StringBuffer();
sql.append("grant all privileges on " + tableNames[i] + " to " + getAllGrantees());
Log.getLogWriter().info("security statement is " + sql.toString());
if (dConn != null) {
try {
Statement stmt = dConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
dConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleDerbySQLException(se, exList);
}
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
gConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleGFGFXDException(se, exList);
}
} else {
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
gConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
}
}
}
/**
* *************************************************** Utility method to remove the tables and
* allow the user to reset the database for a new season
*
* @param conn Connection to the database **************************************************
*/
public static void beginNewSeason(Connection conn) {
try {
Statement stmt = conn.createStatement();
// remove tables if database tables have been created
// this will throw an exception if the tables do not exist
stmt.execute("DROP TABLE Games");
System.out.println("DROP TABLE Games");
} catch (Exception ex) {
// call the method to create tables for the database
System.out.println("DROP TABLE Games failed");
}
try {
Statement stmt = conn.createStatement();
// remove tables if database tables have been created
// this will throw an exception if the tables do not exist
stmt.execute("DROP TABLE Teams");
System.out.println("DROP TABLE Teams");
} catch (Exception ex) {
// call the method to create tables for the database
System.out.println("DROP TABLE Teams failed");
}
createTeamDB(conn);
}
/*
* Delete item
*/
public static boolean delete(int itemid) {
Connection con = null;
CallableStatement cs = null;
try {
con = LoginBean.getConnection();
Statement st = null;
st = con.createStatement();
String sql3 = ("delete from items where itemid = " + itemid + ";");
st.execute(sql3);
Statement st2 = null;
st2 = con.createStatement();
String sqll3 = ("delete from bids where itemid = " + itemid + ";");
st2.execute(sqll3);
return true;
} catch (Exception ex) {
System.out.println(ex.getMessage());
return false;
} finally {
try {
cs.close();
con.close();
} catch (Exception ex) {
}
}
}
/**
* Get how often a message has been displayed
*
* @param node to get the count for
* @param playerId id of the player to get the count for
* @return how often this message has been displayed
*/
private int getCountFor(MessageNode node, int playerId) {
Connection conn = null;
Statement statement = null;
ResultSet result = null;
int value = 0;
try {
conn = retrieveConnection();
statement = conn.createStatement();
String select = String.format("SELECT * FROM %s WHERE %s = %s", msgTable, "id", playerId);
result = statement.executeQuery(select);
if (result.next()) value = result.getInt(node.getColumnName());
else // create the missing row
{
String newPlayerDataQuery =
String.format( // empty row in messages
"INSERT INTO %s (%s) VALUES (%s)", msgTable, "id", playerId);
conn.createStatement().executeUpdate(newPlayerDataQuery);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (statement != null) statement.close();
if (result != null) result.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return value;
}
// metode deleteType nodrošina tipa dzēšanu no datubāzes. Tipu "Cits" nevar dzēst
// typeName - tipa nosaukums
public static void deleteType(String typeName) {
try {
Statement stmt = connect.createStatement();
ResultSet rs =
stmt.executeQuery(
"SELECT * FROM PROCESS INNER JOIN PROCESS_TYPE WHERE PROCESS.TYPE_ID = PROCESS_TYPE.PT_ID ORDER BY PROCESS.Count_Day DESC;");
while (rs.next()) {
String name = rs.getString("NAME");
String type = rs.getString("Type_Name");
if (type.equals(typeName)) {
Statement stmt2 = connect.createStatement();
stmt2.executeUpdate("UPDATE PROCESS SET Type_ID=1 where NAME='" + name + "';");
stmt2.close();
}
}
if (!typeName.equals("Cits")) {
stmt.executeUpdate("DELETE from PROCESS_TYPE where Type_Name='" + typeName + "';");
}
stmt.close();
// System.out.println("Type deleted successfully");
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, e.getClass().getName() + ": " + e.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
// System.exit(0);
}
}
private static void dropCourse(Connection conn, String stu_no) throws SQLException {
String cno = readEntry("Course number: ");
String sno = readEntry("Section number : ");
String chks =
"SELECT g.Section_identifier FROM SECTION s, GRADE_REPORT g where s.Section_identifier=g.Section_identifier AND s.Course_number='"
+ cno
+ "' AND g.Section_identifier="
+ sno
+ " AND s.Semester='Spring' AND s.Year=TO_DATE('2007','YYYY') AND g.Student_number="
+ stu_no;
Statement s3 = conn.createStatement();
ResultSet r3 = s3.executeQuery(chks);
if (r3.next()) {
String del =
"DELETE FROM GRADE_REPORT WHERE Student_number="
+ stu_no
+ " AND Section_identifier="
+ sno;
Statement s4 = conn.createStatement();
try {
s4.executeUpdate(del);
System.out.println("Course dropped");
} catch (SQLException e) {
System.out.println("Cannot drop a course from table");
}
} else {
System.out.println("Enrollment data not found. Check the input and try again.");
}
}
@Test
public void testDropIndexOnTable() throws SQLException {
Connection connection = null;
Statement statement = null;
try {
ConnectionManager connectionManager = temporaryFileDatabase.getConnectionManager(true);
connection = spy(connectionManager.getConnection(null));
statement = spy(connection.createStatement());
when(connection.createStatement()).thenReturn(statement);
DBQueries.createIndexTable(NAME, Integer.class, String.class, connection);
DBQueries.createIndexOnTable(NAME, connection);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", true, connectionManager);
DBQueries.dropIndexOnTable(NAME, connection);
assertObjectExistenceInSQLIteMasterTable(TABLE_NAME, "table", true, connectionManager);
assertObjectExistenceInSQLIteMasterTable(INDEX_NAME, "index", false, connectionManager);
verify(statement, times(3)).close();
} finally {
DBUtils.closeQuietly(connection);
DBUtils.closeQuietly(statement);
}
}
/**
* Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
*
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
*/
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
String username = request.getParameter("username");
String password = request.getParameter("password");
Statement stmt;
ResultSet rs;
Connection con = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String connectionUrl = "jdbc:mysql://localhost/myflickr?" + "user=root&password=123456";
con = DriverManager.getConnection(connectionUrl);
if (con != null) {
System.out.println("connected to mysql");
}
} catch (SQLException e) {
System.out.println("SQL Exception: " + e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: " + cE.toString());
}
try {
stmt = con.createStatement();
System.out.println("SELECT * FROM flickrusers WHERE name='" + username + "'");
rs = stmt.executeQuery("SELECT * FROM flickrusers WHERE name='" + username + "'");
while (rs.next()) {
if (rs.getObject(1).toString().equals(username)) {
out.println("<h1>To username pou epileksate uparxei hdh</h1>");
out.println("<a href=\"project3.html\">parakalw dokimaste kapoio allo.</a>");
stmt.close();
rs.close();
return;
}
}
stmt.close();
rs.close();
stmt = con.createStatement();
if (!stmt.execute("INSERT INTO flickrusers VALUES('" + username + "', '" + password + "')")) {
out.println("<h1>Your registration is completed " + username + "</h1>");
out.println("<a href=\"index.jsp\">go to the login menu</a>");
registerListener.Register(username);
} else {
out.println("<h1>To username pou epileksate uparxei hdh</h1>");
out.println("<a href=\"project3.html\">Register</a>");
}
} catch (SQLException e) {
throw new ServletException("Servlet Could not display records.", e);
}
}
private void submitButtonActionPerformed(
java.awt.event.ActionEvent evt) { // GEN-FIRST:event_submitButtonActionPerformed
// TODO add your handling code here:
Connection con = FrameLogin.getConnect();
String SQLInsert =
"INSERT INTO `2761DB`.`Persons` (`FirstName`, `LastName`, `CellPhoneNo`, `HomePhoneNo`, "
+ "`SchoolId`, `Graduation Year`, `Gender`) VALUES ('"
+ firstName.getText()
+ "', '"
+ lastName.getText()
+ "', '"
+ cellPhone.getText()
+ "', '"
+ homePhone.getText()
+ "', '"
+ studentId.getText()
+ "', '"
+ gradYear.getText()
+ "', '"
+ (String) (gender.getSelectedItem())
+ "');";
String SQLUpdate =
"UPDATE `2761DB`.`Persons` SET `FirstName` = '"
+ firstName.getText()
+ "', `LastName` = '"
+ lastName.getText()
+ "', `CellPhoneNo` = ' "
+ cellPhone.getText()
+ "', `HomePhoneNo` = '"
+ homePhone.getText()
+ "', `Graduation Year` = '"
+ gradYear.getText()
+ "', `Gender` = '"
+ (String) (gender.getSelectedItem())
+ "' WHERE `PersonId` = '"
+ Id
+ "';";
// UPDATE `2761DB`.`Persons` SET `FirstName`='Robbie', `LastName`='Tacescu', `CellPhoneNo`='',
// `HomePhoneNo`='559300', `SchoolId`='15648916', `Graduation Year`='6545', `Gender`='males'
// WHERE
// `PersonId`='42';
try {
if (isEdit) {
Statement stmt = con.createStatement();
// System.out.println(SQLUpdate);
stmt.executeUpdate(SQLUpdate);
} else {
Statement stmt = con.createStatement();
// System.out.println(SQLInsert);
stmt.executeUpdate(SQLInsert);
}
} catch (SQLException err) {
MessageBox.infoBox(err.toString(), "Error in AddUserForm submitButton");
}
FrameLogin.closeConnect();
this.dispose();
} // GEN-LAST:event_submitButtonActionPerformed
public static boolean updateUser(
String user, String fname, String lname, String address, String phone) {
Connection con = null;
CallableStatement cs = null;
try {
con = LoginBean.getConnection();
Statement st = null;
st = con.createStatement();
Statement st2 = null;
st2 = con.createStatement();
Statement st3 = null;
st3 = con.createStatement();
Statement st4 = null;
st4 = con.createStatement();
System.out.println(fname);
System.out.println(lname);
System.out.println(address);
System.out.println(phone);
if (fname != null) {
String sql3 = ("update users set fname= '" + fname + "' where username = '******';");
st.execute(sql3);
}
if (lname != null) {
String sql4 = ("update users set lname= '" + lname + "' where username = '******';");
st2.execute(sql4);
}
if (address != null) {
String sql5 =
("update users set address= '" + address + "' where username = '******';");
st3.execute(sql5);
}
if (phone != null) {
String sql6 = ("update users set phone= '" + phone + "' where username = '******';");
st4.execute(sql6);
}
return true;
} catch (Exception ex) {
System.out.println(ex.getMessage());
return false;
} finally {
try {
cs.close();
con.close();
} catch (Exception ex) {
}
}
}
public static void setupDb() {
loadDriver();
Connection conn = null;
ArrayList statements = new ArrayList();
Statement s = null;
ResultSet rs = null;
try {
// database name
String dbName = "demoDB";
conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
System.out.println("Creating database " + dbName);
boolean createTable = false;
s = conn.createStatement();
try {
s.executeQuery("SELECT count(*) FROM rssFeed");
} catch (Exception e) {
createTable = true;
}
if (createTable) {
// handle transaction
conn.setAutoCommit(false);
s = conn.createStatement();
statements.add(s);
// Create a contact table...
s.execute("create table rssFeed(id int, title varchar(255), url varchar(600))");
System.out.println("Created table rssFeed ");
conn.commit();
}
shutdown();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
close(rs);
// Statements and PreparedStatements
int i = 0;
while (!statements.isEmpty()) {
// PreparedStatement extend Statement
Statement st = (Statement) statements.remove(i);
close(st);
}
close(conn);
}
}
/**
* Remove a symbol from the listofsymbols table and drop the table for the symbol.
*
* @param symbol - symbol to delete from database
*/
static void deleteSymbol(String symbol) throws Exception {
Connection conn = initialize();
Statement removeSymbol = conn.createStatement();
removeSymbol.executeUpdate(
"DELETE FROM listofsymbols WHERE symbolname = " + "'" + symbol + "'" + ";");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists " + symbol + ";");
conn.close();
return;
}
@EventHandler(priority = EventPriority.NORMAL)
public void whenSpawned(final PlayerRespawnEvent event) throws SQLException {
Player player = event.getPlayer();
Connection conn =
DriverManager.getConnection("jdbc:sqlite:plugins/KeepShit/" + player.getName() + ".db");
ResultSet data = conn.createStatement().executeQuery("Select * From " + player.getName());
while (data.next()) {
int TypeID = data.getInt("TypeID");
int Amount = data.getInt("Amount");
player.getInventory().addItem(new ItemStack(TypeID, Amount));
}
conn.createStatement().execute("Drop Table " + player.getName());
}
public static void createTables() throws SQLException {
// Champion table
setupConnection();
conn.createStatement()
.execute(
"CREATE TABLE IF NOT EXISTS champions "
+ "(id int PRIMARY KEY,"
+ " name varchar(15))");
// Summoner Table id (primary key) Region name division-points division ladder-rank
conn.createStatement()
.execute(
"CREATE TABLE IF NOT EXISTS Summoners "
+ "(id int PRIMARY KEY, "
+ "region VARCHAR(6), "
+ "name varchar(30),"
+ "points int,"
+ "division VARCHAR(2))");
// Matches Table id (primary key) winner (RED or BLUE) duration .. other info?
// TODO
conn.createStatement()
.execute(
"CREATE TABLE IF NOT EXISTS Matches "
+ "(id int PRIMARY KEY, "
+ "datePlayed date, "
+ "region VARCHAR(3),"
+ "winningTeam varchar(4), "
+ "length int)");
// Build summoner-id(foreign key) match-id(foreign key) items1-6
// spell-progression"qwwqwerrqwwe..."
conn.createStatement()
.execute(
"CREATE TABLE IF NOT EXISTS Builds "
+ "(summonerId int REFERENCES champions(id),"
+ "matchId int REFERENCES matches(id), "
+ "kills int,"
+ "deaths int,"
+ "assists int,"
+ "team varchar(6), "
+ "item0 int,"
+ "item1 int,"
+ "item2 int,"
+ "item3 int,"
+ "item4 int,"
+ "item5 int,"
+ "item6 int,"
+ "spell1 int, "
+ "spell2 int)");
}