/**
* Get ratings and feedbacks
*
* @return list of ratings and feedbacks in Rating objects
* @throws SQLException
*/
public ArrayList<Rating> getRatingsAndFeedbacks() throws SQLException {
String sql = "SELECT * FROM Rating";
try {
Statement statement = (Statement) connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
ArrayList<Rating> list = new ArrayList<>();
while (rs.next()) {
Rating rating = new Rating();
rating.setCID(rs.getInt("cID"));
rating.setFeedback(rs.getString("feedback"));
rating.setStars(rs.getInt("stars"));
list.add(rating);
}
if (statement != null) {
statement.close();
}
return list;
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Error! Cannot get ratings and feedbacks");
return null;
}
}
public void datatable() {
String KJ = jTextField1.getText();
DefaultTableModel tbl = new DefaultTableModel();
tbl.addColumn("No. Plat Polisi");
tbl.addColumn("Jam Masuk");
tbl.addColumn("Jam Kelaur");
tbl.addColumn("Biaya");
tbl.addColumn("ID Petugas");
tbl.addColumn("Kode Kendaraan");
jTable1.setModel(tbl);
try {
Statement statement = (Statement) conek.GetConnection().createStatement();
ResultSet res = statement.executeQuery("select * from kkeluar where nplat LIKE'" + KJ + "%'");
while (res.next()) {
tbl.addRow(
new Object[] {
res.getString("nplat"),
res.getString("jmask"),
res.getString("jkluar"),
res.getString("biaya"),
res.getString("idpetugas"),
res.getString("kodejenis")
});
jTable1.setModel(tbl);
}
} catch (Exception e) {
JOptionPane.showMessageDialog(rootPane, "Salah");
}
}
/**
* Get All Employees
*
* @return a list of all employees
*/
public ArrayList<Employee> getAllEmployees() {
String sql = "SELECT * FROM Employee";
try {
Statement statement = (Statement) connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
ArrayList<Employee> list = new ArrayList<>();
while (rs.next()) {
int eid = rs.getInt("eid");
String fn = rs.getString("firstName");
String ln = rs.getString("lastName");
String position = rs.getString("position");
String email = rs.getString("email");
String lastworked = rs.getDate("lastworked").toString();
Employee em = new Employee(fn, ln, email, position, lastworked);
// em.setId(eid);
list.add(em);
}
if (statement != null) statement.close();
return list;
} catch (SQLException e) {
e.printStackTrace();
System.out.println(e.getMessage());
return null;
}
}
/**
* Get all archived Customers
*
* @return a list of all archived customers
*/
public ArrayList<Customer> getArchivedCustomers() {
String sql = "SELECT * From arc_customer";
try {
Statement statement = (Statement) connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
ArrayList<Customer> list = new ArrayList<>();
while (rs.next()) {
int id = rs.getInt("cid");
String firstname = rs.getString("firstName");
String lastname = rs.getString("lastName");
String email = rs.getString("email");
Timestamp time = rs.getTimestamp("updatedAt");
String updatedAt = "null";
if (time != null) updatedAt = time.toString();
int discount = rs.getInt("discount");
Customer customer = new Customer(firstname, lastname, email, updatedAt, discount);
// customer.setId(id);
list.add(customer);
}
if (statement != null) statement.close();
return list;
} catch (SQLException e) {
e.printStackTrace();
System.out.println(e.getMessage());
return null;
}
}
/**
* Get all customers
*
* @return a list of all customers
*/
public ArrayList<Customer> getAllCustomers() {
String sql = "SELECT * FROM Customer";
try {
Statement statement = (Statement) connection.createStatement();
ArrayList<Customer> list = new ArrayList<>();
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
int cid = rs.getInt("cid");
String fn = rs.getString("firstName");
String ln = rs.getString("lastName");
String email = rs.getString("email");
String time = rs.getString("updatedAt");
String updatedAt = "null";
if (time != null) updatedAt = time.toString();
int discount = rs.getInt("discount");
Customer customer = new Customer(fn, ln, email, updatedAt, discount);
list.add(customer);
}
if (statement != null) {
statement.close();
}
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void removeItem(String email, String appendNo, String product_id) {
// ResultSet rs;
String query =
"delete from cart where email='"
+ email
+ "' and cartAppendNo='"
+ appendNo
+ "' and productId='"
+ product_id
+ "'";
System.out.println("********" + query);
Statement stmt = null;
Connection con = DB.getConnection();
// rs=DB.readFromDB(query, connection);
try {
stmt = (Statement) con.createStatement();
stmt.executeUpdate(query);
} catch (Exception e) {
System.out.println("In Cart.java");
}
// DB.close(rs);
try {
stmt.close();
DB.close(con);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/** Test of serviceGetAllJaminan method, of class JaminanService. */
@Test
public void d_getAllJaminan() {
System.out.println("4. serviceGetAllJaminan");
tabelModelJaminan.setData(instance.serviceGetAllJaminan());
try {
Statement stt = (Statement) connection.createStatement();
ResultSet rs = stt.executeQuery(getAllJaminan);
while (rs.next()) {
j.setIdJaminan(rs.getString("id_jaminan"));
j.setNmJaminan(rs.getString("nm_jaminan"));
j.setKetJaminan(rs.getString("ket_jaminan"));
list.add(j);
}
rs.close();
stt.close();
} catch (Throwable t) {
// Sengaja dikosongin
}
for (int i = 0; i < list.size(); i++) {
assertEquals(list.get(i).getIdJaminan(), tabelModelJaminan.getValueAt(i, 0));
assertEquals(list.get(i).getNmJaminan(), tabelModelJaminan.getValueAt(i, 1));
assertEquals(list.get(i).getKetJaminan(), tabelModelJaminan.getValueAt(i, 2));
}
}
public static void updateQuantity(String email, String appendNo, String product_id, int qty) {
// ResultSet rs;
String query =
"update cart set quantity='"
+ qty
+ "'"
+ " where email='"
+ email
+ "' and cartAppendNo='"
+ appendNo
+ "' and productId='"
+ product_id
+ "'";
System.out.println("********" + query);
Connection con = DB.getConnection();
// rs=DB.readFromDB(query, connection);
try {
Statement stmt = (Statement) con.createStatement();
stmt.executeUpdate(query);
} catch (Exception e) {
System.out.println("In Cart.java");
}
// DB.close(rs);
DB.close(con);
}
/**
* Get all customers and all employees
*
* @return a list of all people
*/
public ArrayList<Person> getAllCustomersAndEmployees() {
String sql =
"SELECT firstname, lastname, email, updatedAt, discount, lastWorked\n"
+ "FROM customer LEFT JOIN employee using(firstname, lastname, email)\n"
+ "UNION\n"
+ "SELECT firstname, lastname, email, updatedAt, discount, lastWorked\n"
+ "FROM customer RIGHT JOIN employee using(firstname, lastname, email)";
try {
Statement statement = (Statement) connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
ArrayList<Person> list = new ArrayList<>();
while (rs.next()) {
String fn = rs.getString("firstName");
String ln = rs.getString("lastName");
String email = rs.getString("email");
Person person = new Person(fn, ln, email);
list.add(person);
}
if (statement != null) statement.close();
return list;
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
/**
* Get all reservations
*
* @return a list of reservations
*/
public ArrayList<ReservationInfo> getAllReservations() {
String sql =
"SELECT firstName, lastName, tID, partysize, seats, reservationDate\n"
+ "FROM Reservation NATURAL JOIN Customer NATURAL JOIN aTable";
try {
Statement statement = (Statement) connection.createStatement();
ResultSet rs = statement.executeQuery(sql);
ArrayList<ReservationInfo> list = new ArrayList<>();
while (rs.next()) {
String firstName = rs.getString("firstName");
String lastName = rs.getString("lastName");
int tid = rs.getInt("tID");
int partySize = rs.getInt("partySize");
int seats = rs.getInt("seats");
String date = rs.getDate("reservationDate").toString();
ReservationInfo reservation =
new ReservationInfo(tid, firstName, lastName, partySize, seats, date);
list.add(reservation);
}
if (statement != null) statement.close();
return list;
} catch (SQLException e) {
e.printStackTrace();
System.out.println(e.getMessage());
return null;
}
}
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
String uname = (String) request.getSession().getAttribute("username");
String url = "jdbc:mysql://localhost:3306/progin_405_13510032";
String username = "******";
String password = "******";
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = (Connection) DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
String idtask = request.getParameter("q");
query = "SELECT status FROM task WHERE id_task = '" + idtask + "'";
try {
stmt = (Statement) conn.createStatement();
rs = stmt.executeQuery(query);
String status = "";
while (rs.next()) {
status = rs.getString(1);
}
if (status.equals("0")) {
String query2 = "UPDATE task SET status='1' WHERE id_task = '" + idtask + "'";
stmt.executeUpdate(query2);
out.print("tugas done");
} else if (status.equals("1")) {
String query2 = "UPDATE task SET status='0' WHERE id_task = '" + idtask + "'";
stmt.executeUpdate(query2);
out.print("tugas undone");
}
} catch (SQLException ex) {
Logger.getLogger(autosearch.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
}
}
}
public static void tablolariHazirla() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conTest =
DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "etmen123");
Statement komut = (Statement) conTest.createStatement();
komut.executeUpdate("DROP TABLE IF EXISTS personel");
komut.executeUpdate("DROP TABLE IF EXISTS musteriler");
komut.executeUpdate("DROP TABLE IF EXISTS dvdler");
komut.executeUpdate(
"CREATE TABLE personel (adsoyad char(50), username char(50) NOT NULL PRIMARY KEY, password char(50));");
komut.executeUpdate(
"CREATE TABLE musteriler (adsoyad char(50),username char(50) NOT NULL PRIMARY KEY, password char(50), kartNo char(50));");
komut.executeUpdate(
"CREATE TABLE dvdler (filmAdi char(50), yonetmen char(30), fiyat smallint, barkod char(10) NOT NULL PRIMARY KEY, sirket char(30), adet smallint);");
komut.executeUpdate(
"INSERT INTO personel (adsoyad, username, password) VALUES ('xx', 'xx', 'xx');");
komut.close();
} catch (Exception err) {
System.out.println("Driver Error " + err.getMessage());
}
}
public static void runUpdate(String query) {
Connection con = connect();
try {
Statement stmt = (Statement) con.createStatement();
stmt.executeUpdate("USE schedule;");
stmt.executeUpdate(query);
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public User deleteChannel(String Channel) {
String query = "Delete From BOT.RSS Where Email='" + email + "' And Channel='" + Channel + "'";
if (con == null) connect();
try {
Statement stmt = (Statement) con.createStatement();
int i = stmt.executeUpdate(query);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("=====SQL exception found while connection with the driver======");
}
return this;
}
private ResultSet query(Statement s, String query) {
ResultSet rs = null;
try {
s.executeQuery(query);
rs = s.getResultSet();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
public static ResultSet runQuery(String query) {
Connection con = connect();
Statement stmt;
try {
stmt = (Statement) con.createStatement();
stmt.executeUpdate("USE schedule;");
ResultSet rs = stmt.executeQuery(query);
return rs;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public List queryUser() {
// TODO Auto-generated method stub
Conn conn = new Conn();
Connection connection = null;
connection = conn.getConnection(null);
Statement st = null;
try {
st = (Statement) connection.createStatement();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
ResultSet rs = st.executeQuery("select * from finance order by date");
List list = new ArrayList();
while (rs.next()) {
financeUser u = new financeUser();
u.setId(rs.getInt("id"));
u.setDate(rs.getString("date"));
u.setOwn_project(rs.getString("own_project"));
u.setMoney(rs.getString("money"));
u.setBorrow_object(rs.getString("borrow_object"));
u.setPay_way(rs.getString("pay_way"));
u.setGovernment_buystyle(rs.getString("government_buystyle"));
u.setGovernment_buysite(rs.getString("government_buysite"));
u.setProofID(rs.getString("proofID"));
u.setContract_special(rs.getString("contract_special"));
u.setSummary(rs.getString("summary"));
u.setHaber_subject(rs.getString("haber_subject"));
u.setProject_manage(rs.getString("project_manage"));
u.setGovernment_buyway(rs.getString("government_buyway"));
u.setGovernment_buyclassify(rs.getString("government_buyclassify"));
u.setIsSubmit(rs.getString("isSubmit"));
list.add(u);
}
st.close();
return list;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
/* (non-Javadoc)
* @see sb.elpro.dao.InspectionTrackDao#getInspectionTrackLoad(java.lang.String, java.lang.String)
*/
@Override
public ArrayList<InspectionBean> getInspectionTrackLoad() throws SQLException {
ArrayList<InspectionBean> insptracklist = new ArrayList<InspectionBean>();
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = DBConnection.getConnection();
st = (Statement) con.createStatement();
// String sql = "SELECT invno FROM elpro.tbl_invform";
String sql =
"SELECT inspid, inspdate, qualitycontroller, form.contractno, concat_ws('-',art.articlename,sart.articlename) as articlename, grade.color, testid, gradeid, rejectid, rjtotinspected, rej.totpassed, skincount1, skincount2, skincount3, skincount4, skincount5, skincount6, totrejects, form.inspcomments, inspcdn FROM elpro.tbl_inspform form left outer join elpro.tbl_inspgradedetails grade on form.inspid = grade.id and form.articleid = grade.articleid left outer join elpro.tbl_insptestdetails test on form.inspid = test.id and form.articleid = test.articleid left outer join elpro.tbl_insprejectdetails rej on form.inspid = rej.id and form.articleid = rej.articleid left outer join elpro.tbl_prf_article art on art.prfarticleid = grade.articleid left outer join elpro.tbl_srf_article sart on sart.srfarticleid = grade.articleid order by form.inspdate desc, form.contractno, art.articlename desc, color desc";
rs = st.executeQuery(sql);
while (rs.next()) {
InspectionBean insptrackbean = new InspectionBean();
insptrackbean.setInspid(rs.getString("inspid"));
insptrackbean.setInspdate((rs.getString("inspdate")));
insptrackbean.setInspqualityctrlr(rs.getString("qualitycontroller"));
insptrackbean.setInspContractNo(rs.getString("contractno"));
insptrackbean.setArticle(rs.getString("articlename"));
insptrackbean.setColor(rs.getString("color"));
insptrackbean.setTestid(rs.getString("testid"));
insptrackbean.setGradeid(rs.getString("gradeid"));
insptrackbean.setRejectid(rs.getString("rejectid"));
insptrackbean.setRjtotinspected(rs.getString("rjtotinspected"));
insptrackbean.setTotpassed(rs.getString("totpassed"));
insptrackbean.setSkincount1(rs.getString("skincount1"));
insptrackbean.setSkincount2(rs.getString("skincount2"));
insptrackbean.setSkincount3(rs.getString("skincount3"));
insptrackbean.setSkincount4(rs.getString("skincount4"));
insptrackbean.setSkincount5(rs.getString("skincount5"));
insptrackbean.setSkincount6(rs.getString("skincount6"));
insptrackbean.setTotrejects(rs.getString("totrejects"));
insptrackbean.setInspcomments(rs.getString("inspcomments"));
insptrackbean.setInsp_cdn(rs.getString("inspcdn"));
insptracklist.add(insptrackbean);
}
System.out.println("Insp Track loaded Successfully");
} catch (Exception e) {
e.printStackTrace();
System.out.println("Insp Result ERROR RESULT");
} finally {
con.close();
st.close();
rs.close();
}
return insptracklist;
}
@Override
public ArrayList select() {
int a = 1;
Scanner scantool = new Scanner(System.in);
System.out.println("Matrícula de alumno: ");
try {
a = Integer.parseInt(scantool.nextLine());
} catch (NumberFormatException error) {
System.out.println("\n Error: " + error.getMessage());
a = 0;
}
ArrayList mi_lista = new ArrayList();
Alumno alumnoCall = new Alumno();
Connection conexion = DAOFactory.getConexion();
try {
Statement llamada = (Statement) conexion.createStatement();
ResultSet query = llamada.executeQuery(SQL.findAlumno);
int b;
while (query.next()) {
b = query.getInt("Matricula");
if (a == b) {
System.err.println(
"Nombre: "
+ query.getString("Nombre")
+ " "
+ query.getString("Ape_pat")
+ " "
+ query.getString("Ape_mat"));
}
mi_lista.add(
new Alumno(
query.getInt("Matricula"),
query.getString("Nombre"),
query.getString("Ape_pat"),
query.getString("Ape_mat"),
query.getString("Telefono"),
query.getString("Fecha_nac"),
query.getString("Direccion"),
query.getString("Clave")));
}
} catch (SQLException ex) {
System.out.println(ex);
}
return mi_lista;
}
/**
* Metodo para realizar una consulta like
*
* @param tabla
* @param atributo
* @param datoBuscado
* @return
*/
public ResultSet buscarLike(
String tabla, String atributos, String datoBuscado, String buscarpor) {
try {
conn = Enlace(conn);
st = (Statement) conn.createStatement();
// cade = "SELECT "+ atributo+" FROM " + tabla + " WHERE " + atributo+ " like '%"+ datoBuscado
// +"%'";
// System.out.print(cade);
rs =
st.executeQuery(
"SELECT "
+ atributos
+ " FROM "
+ tabla
+ " WHERE "
+ buscarpor
+ " like '%"
+ datoBuscado
+ "%'");
// conn.close();
} catch (SQLException ex) {
Logger.getLogger(conexionBD.class.getName()).log(Level.SEVERE, null, ex);
}
return rs;
}
public static List<GameVo> getGameList(boolean isEn) {
List<GameVo> list = new ArrayList<GameVo>();
Connection conn = DBManager.getConnection();
// 创建SQL语句
Statement st = null;
try {
st = (Statement) conn.createStatement();
String sql = "select * from web_index where isen=false order by sort desc,id";
if (isEn) {
sql = "select * from web_index where isen=true order by sort desc,id";
}
// 执行语句，返回结果
ResultSet rs = (ResultSet) st.executeQuery(sql);
while (rs.next()) {
GameVo vo = new GameVo();
vo.id = rs.getInt("id");
vo.sort = rs.getInt("sort");
vo.name = rs.getString("name");
vo.gamesize = rs.getString("gamesize");
vo.gameos = rs.getString("gameos");
vo.updatetime = rs.getString("updatetime");
vo.version = rs.getString("version");
vo.info = rs.getString("info");
vo.bgcolor = rs.getString("bgcolor");
vo.bgimg = rs.getString("bgimg");
vo.bigicon = rs.getString("bigicon");
vo.icon = rs.getString("icon");
vo.downloadurl = rs.getString("downloadurl");
vo.navicon = rs.getString("navicon");
vo.isen = isEn;
list.add(vo);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
st.close();
st = null;
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
public static boolean login(String rollNo, String password) {
boolean isUserAvailable = false;
Connection connection = null;
try {
try {
connection = createConnection();
} catch (Exception e) {
e.printStackTrace();
}
Statement stmt = (Statement) connection.createStatement();
String query =
"SELECT * FROM user WHERE RollNo = '"
+ rollNo
+ "' AND Password = "******"'"
+ password
+ "'";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
isUserAvailable = true;
}
} catch (SQLException sqlE) {
sqlE.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException sqlE) {
sqlE.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return isUserAvailable;
}
public void UpdateQuery(String query) // Update the database ...
{
try {
stmt.executeUpdate(query);
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", 2);
}
}
@Test
public void testMySQlBasicExportForDelimiter() throws Exception {
final String inputData1 =
"1||Dale Zamora||manager"
+ "\n2||Macaulay Jackson||clerk"
+ "\n3||Charles Wood||supervisior";
createTextFileInHDFS(inputData1, "/input", "testFile1.txt");
String[] args =
new String[] {
"-inputPath",
"/input",
"-url",
"jdbc:mysql://localhost:3306/hiho",
"-userName",
"root",
"-password",
"newpwd",
"-querySuffix",
"employee fields terminated by '||' lines terminated by '\n'"
};
ExportToMySQLDB job = new ExportToMySQLDB();
int res = ToolRunner.run(createJobConf(), job, args);
assertEquals(0, res);
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection conn =
DriverManager.getConnection("jdbc:mysql://localhost:3306/hiho", "root", "newpwd");
com.mysql.jdbc.Statement stmt =
(com.mysql.jdbc.Statement)
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
String query = "Select * from employee";
ResultSet rs = stmt.executeQuery(query);
String output = "";
while (rs.next()) {
output = output + rs.getString(1) + "||" + rs.getString(2) + "||" + rs.getString(3);
output = output + "\n";
}
assertEquals(inputData1 + "\n", output);
stmt.executeUpdate("delete from employee");
rs.close();
stmt.close();
conn.close();
}
public static Boolean clavenueva(Usuario usuario) throws SQLException {
Connection miconexion = null;
Boolean esValido = false;
String result = null;
try {
result = md5(usuario.getClavenueva().trim());
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
System.out.print(result);
try {
Class.forName("com.mysql.jdbc.Driver");
miconexion =
DriverManager.getConnection("jdbc:mysql://localhost:3306/prueba", "root", "mero2511");
Statement insql = (Statement) miconexion.createStatement();
System.out.println("Si he llegado hasta aquí es que se ha producido la conexión");
insql.execute(
"update login set clave='"
+ result.trim()
+ "' where usuario='"
+ usuario.getUsuario().trim()
+ "'");
insql.execute(
"update login set tipo='0' where usuario='" + usuario.getUsuario().trim() + "'");
ResultSet resultado =
insql.executeQuery(
"select * from login where usuario='" + usuario.getUsuario().trim() + "'");
if (resultado.next()) {
usuario.setNombre(resultado.getString("nombre"));
usuario.setCedula(resultado.getString("cedula"));
ResultSet resul =
insql.executeQuery(
"select * from TTDAT where cedula ='" + usuario.getCedula().trim() + "'");
if (resul.next()) {
usuario.setCuenta(resul.getString("cuenta"));
esValido = true;
}
}
} catch (Exception e) {
}
return esValido;
}
public static List<Event> viewAllEvents() {
List<Event> events = new ArrayList<Event>();
Connection connection = null;
try {
try {
connection = createConnection();
} catch (Exception e) {
e.printStackTrace();
}
Statement stmt = (Statement) connection.createStatement();
String query = "SELECT * FROM event";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
Event temp =
new Event(
rs.getLong(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5));
events.add(temp);
}
} catch (SQLException sqlE) {
sqlE.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException sqlE) {
sqlE.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return events;
}
public ResultSetInternalMethods preProcess(String sql, Statement statement, Connection connection)
throws SQLException {
assertReady();
if (logger.isDebugEnabled() && statement != null)
logger.debug(statement.getClass().getName() + ": " + sql);
if (statement instanceof com.mysql.jdbc.PreparedStatement) {
com.mysql.jdbc.PreparedStatement preparedStatement =
(com.mysql.jdbc.PreparedStatement) statement;
// key must be interned because we are using IdentityHashMap
// TODO: in case of DELETE, the SQL has already been rewritten at this point,
// and the original SQL is gone
// so the key in the table is the rewritten DELETE SQL -- not what we want at all
String nonRewrittenSql = preparedStatement.getNonRewrittenSql();
String internedSql = nonRewrittenSql.intern();
// see if we have a parsed version of this query
Executor sQLExecutor = null;
synchronized (parsedSqlMap) {
sQLExecutor = parsedSqlMap.get(internedSql);
}
// if no cached SQLExecutor, create it, which might take some time
if (sQLExecutor == null) {
sQLExecutor = createSQLExecutor(internedSql);
if (sQLExecutor != null) {
// multiple thread might have created a SQLExecutor but it's ok
synchronized (parsedSqlMap) {
parsedSqlMap.put(internedSql, sQLExecutor);
}
}
}
try {
return sQLExecutor.execute(this, preparedStatement);
} catch (Throwable t) {
t.printStackTrace();
return null;
}
} else {
if (logger.isDebugEnabled() && statement != null)
logger.debug(
statement.getClass().getName() + " is not instanceof com.mysql.jdbc.PreparedStatement");
// not a prepared statement; won't execute this
return null;
}
}
public int UpdateQueryReturn(String query) // Update the database ...
{
try {
IsUpdate = stmt.executeUpdate(query);
} catch (SQLException ex) {
JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", 2);
}
return IsUpdate;
}
public static User getUserDetails(String rollNo) {
User user = null;
Connection connection = null;
try {
try {
connection = createConnection();
} catch (Exception e) {
e.printStackTrace();
}
Statement stmt = (Statement) connection.createStatement();
String query = "SELECT * FROM user WHERE RollNo = '" + rollNo + "'";
ResultSet rs = stmt.executeQuery(query);
if (rs.next()) {
User temp = new User(rs.getString(1), rs.getString(2), rs.getBoolean(4), rs.getString(5));
user = temp;
}
} catch (SQLException sqlE) {
sqlE.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException sqlE) {
sqlE.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return user;
}
public static List<User> viewAllUsers() {
List<User> users = new ArrayList<User>();
Connection connection = null;
try {
try {
connection = createConnection();
} catch (Exception e) {
e.printStackTrace();
}
Statement stmt = (Statement) connection.createStatement();
String query = "SELECT * FROM user";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
User temp = new User(rs.getString(1), rs.getString(2), rs.getBoolean(4), rs.getString(5));
users.add(temp);
}
} catch (SQLException sqlE) {
sqlE.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException sqlE) {
sqlE.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
return users;
}
