Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * To change this template, choose Tools | Templates
- * and open the template in the editor.
- */
- import java.sql.Connection;
- import java.sql.PreparedStatement;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.util.ArrayList;
- import java.util.List;
- import java.util.UUID;
- import javax.naming.Context;
- import javax.naming.InitialContext;
- import javax.naming.NamingException;
- import javax.sql.DataSource;
- /**
- *
- * @author marc
- */
- public class Services {
- private Connection getConnection() throws NamingException, SQLException
- {
- Context context = new InitialContext();
- DataSource ds = (DataSource)context.lookup("java:comp/env/jdbc/test");
- return ds.getConnection();
- }
- public boolean isUsernameAvailable(String username)
- {
- boolean retour = false;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "SELECT 1 FROM users WHERE username = ?"
- );
- st.setString(1, username);
- ResultSet rs = st.executeQuery();
- if (!rs.next()) retour = true;
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- public boolean isAccessGranted(String username, String password, String uuid, String usbid)
- {
- boolean retour = false;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "SELECT 1 FROM users WHERE username = ? AND password = ? AND uuid = ? AND usbid = ?"
- );
- st.setString(1, username);
- st.setString(2, password);
- st.setString(3, uuid);
- st.setString(4, usbid);
- ResultSet rs = st.executeQuery();
- if (rs.next()) retour = true;
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- public String createNewAccount(String username, String password, String firstname, String lastname, String email, String usbid)
- {
- String retour = null;
- try
- {
- Connection cn = this.getConnection();
- retour = UUID.randomUUID().toString().toUpperCase().replaceAll("-", "");
- PreparedStatement st = cn.prepareStatement(
- "INSERT INTO users VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, 0)"
- );
- st.setString(1, retour);
- st.setString(2, usbid);
- st.setString(3, username);
- st.setString(4, password);
- st.setString(5, firstname);
- st.setString(6, lastname);
- st.setString(7, email);
- st.executeUpdate();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- public boolean blacklistAccount(String username, String password)
- {
- boolean retour = false;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "UPDATE users SET blacklist = 1 WHERE username = ? AND password = ?"
- );
- st.setString(1, username);
- st.setString(2, password);
- st.executeUpdate();
- retour = true;
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- public int addNewForm(String url, String action, String method, String name, int number)
- {
- int retour = 0;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "INSERT INTO forms VALUES (NULL, ?, ?, ?, ?, ?, NOW(), 0)",
- PreparedStatement.RETURN_GENERATED_KEYS
- );
- st.setString(1, url);
- st.setString(2, action);
- st.setString(3, method);
- st.setString(4, name);
- st.setInt(5, number);
- st.executeUpdate();
- ResultSet rs = st.getGeneratedKeys();
- if (rs.next()) retour = rs.getInt(1);
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- public int addNewField(int formid, String name, String type, String value, int rank)
- {
- int retour = 0;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "INSERT INTO fields VALUES (NULL, ?, ?, ?, ?, ?)",
- PreparedStatement.RETURN_GENERATED_KEYS
- );
- st.setInt(1, formid);
- st.setString(2, name);
- st.setString(3, type);
- st.setString(4, value);
- st.setInt(5, rank);
- st.executeUpdate();
- ResultSet rs = st.getGeneratedKeys();
- if (rs.next()) retour = rs.getInt(1);
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- /*
- public Global getGlobalFormById(int id)
- {
- Global form = null;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st1 = cn.prepareStatement(
- "SELECT id, url, action, method, name, number FROM forms WHERE id = ?"
- );
- st1.setInt(1, id);
- ResultSet rs1 = st1.executeQuery();
- if (rs1.next()) {
- Global tmp = new Global (
- rs1.getInt("id"), rs1.getString("url"), rs1.getString("action"),
- rs1.getString("method"), rs1.getString("name"), rs1.getInt("number")
- );
- List<Field> fields = new ArrayList<Field>();
- PreparedStatement st2 = cn.prepareStatement(
- "SELECT id, idform, name, type, value, rank FROM fields WHERE idform = ?"
- );
- st2.setInt(1, rs1.getInt("id"));
- ResultSet rs2 = st2.executeQuery();
- while (rs2.next()) {
- Field field = new Field (
- rs2.getInt("id"), rs2.getInt("idform"), rs2.getString("name"),
- rs2.getString("type"), rs2.getString("value"), rs2.getInt("rank")
- );
- fields.add(field);
- }
- tmp.setFields((Field[]) fields.toArray(new Field[0]));
- rs2.close();
- st2.close();
- form = tmp;
- }
- rs1.close();
- st1.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return form;
- }
- public Form getFormById(int id)
- {
- Form form = null;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "SELECT id, url, action, method, name, number FROM forms WHERE id = ?"
- );
- st.setInt(1, id);
- ResultSet rs = st.executeQuery();
- if (rs.next()) {
- Form tmp = new Form (
- rs.getInt("id"), rs.getString("url"), rs.getString("action"),
- rs.getString("method"), rs.getString("name"), rs.getInt("number")
- );
- form = tmp;
- }
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return form;
- }
- public Field getFieldById(int id)
- {
- Field field = null;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "SELECT id, idform, name, type, value, rank FROM fields WHERE id = ?"
- );
- st.setInt(1, id);
- ResultSet rs = st.executeQuery();
- if (rs.next()) {
- Field tmp = new Field (
- rs.getInt("id"), rs.getInt("idform"), rs.getString("name"),
- rs.getString("type"), rs.getString("value"), rs.getInt("rank")
- );
- field = tmp;
- }
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return field;
- }
- */
- /*
- public Global[] getGlobalForms(boolean valid)
- {
- List<Global> forms = new ArrayList<Global>();
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st1;
- if (valid) {
- st1 = cn.prepareStatement(
- "SELECT id, url, action, method, name, number FROM forms WHERE valid = 1"
- );
- } else {
- st1 = cn.prepareStatement(
- "SELECT id, url, action, method, name, number FROM forms"
- );
- }
- ResultSet rs1 = st1.executeQuery();
- while (rs1.next()) {
- Global tmp = new Global (
- rs1.getInt("id"), rs1.getString("url"), rs1.getString("action"),
- rs1.getString("method"), rs1.getString("name"), rs1.getInt("number")
- );
- List<Field> fields = new ArrayList<Field>();
- PreparedStatement st2 = cn.prepareStatement(
- "SELECT id, idform, name, type, value, rank FROM fields WHERE idform = ?"
- );
- st2.setInt(1, rs1.getInt("id"));
- ResultSet rs2 = st2.executeQuery();
- while (rs2.next()) {
- Field field = new Field (
- rs2.getInt("id"), rs2.getInt("idform"), rs2.getString("name"),
- rs2.getString("type"), rs2.getString("value"), rs2.getInt("rank")
- );
- fields.add(field);
- }
- tmp.setFields((Field[]) fields.toArray(new Field[0]));
- rs2.close();
- st2.close();
- forms.add(tmp);
- }
- rs1.close();
- st1.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- //return forms;
- return (Global[]) forms.toArray(new Global[0]);
- }
- */
- public Form[] getForms(boolean valid)
- {
- List<Form> forms = new ArrayList<Form>();
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st;
- if (valid) {
- st = cn.prepareStatement(
- "SELECT id, url, action, method, name, number FROM forms WHERE valid = 1"
- );
- } else {
- st = cn.prepareStatement(
- "SELECT id, url, action, method, name, number FROM forms"
- );
- }
- ResultSet rs = st.executeQuery();
- while (rs.next()) {
- Form tmp = new Form (
- rs.getInt("id"), rs.getString("url"), rs.getString("action"),
- rs.getString("method"), rs.getString("name"), rs.getInt("number")
- );
- forms.add(tmp);
- }
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- //return forms;
- return (Form[]) forms.toArray(new Form[0]);
- }
- public Field[] getFieldsByForm(int id)
- {
- List<Field> fields = new ArrayList<Field>();
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "SELECT id, idform, name, type, value, rank FROM fields WHERE idform = ?"
- );
- st.setInt(1, id);
- ResultSet rs = st.executeQuery();
- while (rs.next()) {
- Field tmp = new Field (
- rs.getInt("id"), rs.getInt("idform"), rs.getString("name"),
- rs.getString("type"), rs.getString("value"), rs.getInt("rank")
- );
- fields.add(tmp);
- }
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- //return fields;
- return (Field[]) fields.toArray(new Field[0]);
- }
- /*
- public String[] getFormsToString(boolean valid)
- {
- String[] retour = new String[0];
- try
- {
- Form[] forms = this.getForms(valid);
- retour = new String[forms.length];
- for(int i = 0; i < forms.length; i++) {
- retour[i] = forms[i].getId() + "|";
- retour[i] += forms[i].getUrl() + "|";
- retour[i] += forms[i].getAction() + "|";
- retour[i] += forms[i].getMethod() + "|";
- retour[i] += forms[i].getName() + "|";
- retour[i] += forms[i].getNumber();
- }
- List<Form> forms = this.getForms(valid);
- retour = new String[forms.size()];
- int i = 0;
- for (Form form : forms) {
- retour[i] = form.getId() + "|";
- retour[i] += form.getUrl() + "|";
- retour[i] += form.getAction() + "|";
- retour[i] += form.getMethod() + "|";
- retour[i] += form.getName() + "|";
- retour[i] += form.getNumber();
- i++;
- }
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- public String[] getFieldsByFormToString(int id)
- {
- String[] retour = new String[0];
- try
- {
- Field[] fields = this.getFieldsByForm(id);
- retour = new String[fields.length];
- for(int i = 0; i < fields.length; i++) {
- retour[i] = fields[i].getId() + "|";
- retour[i] += fields[i].getIdform() + "|";
- retour[i] += fields[i].getName() + "|";
- retour[i] += fields[i].getType() + "|";
- retour[i] += fields[i].getValue() + "|";
- retour[i] += fields[i].getRank();
- }
- List<Field> fields = this.getFieldsByForm(id);
- retour = new String[fields.size()];
- int i = 0;
- for (Field field : fields) {
- retour[i] = field.getId() + "|";
- retour[i] += field.getIdform() + "|";
- retour[i] += field.getName() + "|";
- retour[i] += field.getType() + "|";
- retour[i] += field.getValue() + "|";
- retour[i] += field.getRank();
- i++;
- }
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- */
- public int isPortalAccessGranted(String username, String password)
- {
- int retour = 0;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "SELECT id FROM users WHERE username = ? AND password = ?"
- );
- st.setString(1, username);
- st.setString(2, password);
- ResultSet rs = st.executeQuery();
- if (rs.next()) retour = rs.getInt("id");
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- public int addNewGadget(int idg, int ido, String title, int col, int row, int idu, Pref[] prefs)
- {
- int retour = 0;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st1 = cn.prepareStatement(
- "INSERT INTO gadgets VALUES (NULL, ?, ?, ?, ?, ?, ?)",
- PreparedStatement.RETURN_GENERATED_KEYS
- );
- st1.setInt(1, idg);
- st1.setInt(2, ido);
- st1.setString(3, title);
- st1.setInt(4, col);
- st1.setInt(5, row);
- st1.setInt(6, idu);
- st1.executeUpdate();
- ResultSet rs1 = st1.getGeneratedKeys();
- if (rs1.next()) {
- retour = rs1.getInt(1);
- for (Pref pref : prefs)
- {
- PreparedStatement st2 = cn.prepareCall(
- "INSERT INTO prefs VALUES (?, ?, ?)"
- );
- st2.setInt(1, retour);
- st2.setString(2, pref.getKey());
- st2.setString(3, pref.getValue());
- st2.executeUpdate();
- st2.close();
- }
- }
- rs1.close();
- st1.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour;
- }
- public boolean removeGadget(int id)
- {
- int retour = 0;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "DELETE FROM gadgets WHERE id = ?"
- );
- st.setInt(1, id);
- retour = st.executeUpdate();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour > 0;
- }
- public boolean setGadgetPosition(int id, int col, int row)
- {
- int retour = 0;
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "UPDATE gadgets SET col = ?, row = ? WHERE id = ?"
- );
- st.setInt(1, col);
- st.setInt(2, row);
- st.setInt(3, id);
- retour = st.executeUpdate();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour > 0;
- }
- public boolean setGadgetPrefs(int id, Pref[] prefs)
- {
- int retour = 0;
- try
- {
- Connection cn = this.getConnection();
- for (Pref pref : prefs)
- {
- PreparedStatement st = cn.prepareStatement(
- "INSERT INTO prefs (idg, key, value) VALUES (?, ?, ?) " +
- "ON DUPLICATE KEY UPDATE value = ?"
- );
- st.setInt(1, id);
- st.setString(2, pref.getKey());
- st.setString(3, pref.getValue());
- st.setString(4, pref.getValue());
- retour = st.executeUpdate();
- st.close();
- }
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- return retour > 0;
- }
- public Gadget[] getGadgetsByUser(int id)
- {
- List<Gadget> gadgets = new ArrayList<Gadget>();
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "SELECT id, idg, ido, title, col, row, idu FROM gadgets WHERE idu = ? ORDER BY row ASC, col ASC"
- );
- st.setInt(1, id);
- ResultSet rs = st.executeQuery();
- while (rs.next()) {
- Gadget g = new Gadget(
- rs.getInt("id"), rs.getInt("idg"), rs.getInt("ido"), rs.getString("title"),
- rs.getInt("col"), rs.getInt("row"), rs.getInt("idu")
- );
- gadgets.add(g);
- }
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- //return gadgets;
- return (Gadget[]) gadgets.toArray(new Gadget[0]);
- }
- public Pref[] getPrefsByGadget(int id)
- {
- List<Pref> prefs = new ArrayList<Pref>();
- try
- {
- Connection cn = this.getConnection();
- PreparedStatement st = cn.prepareStatement(
- "SELECT 'key', 'value' FROM prefs WHERE idg = ?"
- );
- st.setInt(1, id);
- ResultSet rs = st.executeQuery();
- while (rs.next()) {
- Pref pref = new Pref(rs.getString("key"), rs.getString("value"));
- prefs.add(pref);
- }
- rs.close();
- st.close();
- cn.close();
- }
- catch (Exception e) {
- e.printStackTrace();
- }
- //return prefs;
- return (Pref[]) prefs.toArray(new Pref[0]);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement