public List<Funcionario> list() {
String sql = "SELECT * FROM FUNCIONARIO";
Funcionario funcionario = null;
try {
PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
List<Funcionario> funcionarios = new ArrayList<Funcionario>();
while (rs.next()) {
funcionario = new Funcionario();
funcionario.setNumFuncionario(rs.getInt("id_funcionario"));
funcionario.setNomeFuncionario(rs.getString("nome_funcionario"));
funcionario.setComissao(rs.getDouble("comissao"));
funcionario.setEspecialidade(rs.getString("especialidade"));
funcionarios.add(funcionario);
}
stmt.close();
rs.close();
return funcionarios;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* Cancel a reservation
*
* @param email
* @param d
* @return
*/
public boolean cancelReservation(String email, Date d) {
boolean success = false;
int cid = getCID(email);
if (cid != -1) {
String queryCancelReservation = "DELETE FROM Reservation WHERE cID=? AND reservationDate=?";
try {
PreparedStatement statement =
(PreparedStatement) connection.prepareStatement(queryCancelReservation);
statement.setInt(1, cid);
statement.setDate(2, d);
statement.execute();
statement.close();
success = true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
success = false;
System.out.println("Failed: " + e.getMessage());
} finally {
return success;
}
} else {
return false;
}
}
// to implement
public void add(Funcionario funcionario) {
String sql =
"INSERT INTO Funcionario (nome_funcionario, especialidade, comissao) VALUES (?, ?, ?)";
// String sqlNumFunc = "SELECT id_funcionario FROM Funcionario WHERE nome_funcionario = '?'";
try {
PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
stmt.setString(1, funcionario.getNomeFuncionario());
stmt.setString(2, funcionario.getEspecialidade());
stmt.setDouble(3, funcionario.getComissao());
stmt.execute();
// stmt = (PreparedStatement) connection.prepareStatement(sqlNumFunc);
// stmt.execute();
// ResultSet rs = stmt.executeQuery();
// funcionario.setNumFuncionario(rs.);
stmt.close();
System.out.println("Funcionario Inserted Successfully");
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
/**
* Reserve a table
*
* @param partySize number of people in a party
* @param d reservation date
* @param tID table id
* @param c a customer
* @return true if succeed, false otherwise
*/
public boolean reserveTable(int partySize, Date d, int tID, Customer c) {
PreparedStatement statement = null;
int customerid = getCID(c.getEmail());
String sql_reserve =
"INSERT INTO Restaurant.Reservation (reservationDate,partySize,cID,tID) values(?, ?, ?,?)";
try {
connection.setAutoCommit(false);
statement = (PreparedStatement) connection.prepareStatement(sql_reserve);
statement.setDate(1, d);
statement.setInt(2, partySize);
statement.setInt(3, customerid);
statement.setInt(4, tID);
statement.executeUpdate();
connection.commit();
if (statement != null) {
statement.close();
}
connection.setAutoCommit(true);
return true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
System.out.println("Failed: " + e.getMessage());
return false;
}
}
/**
* Change a reservation
*
* @param email current user's email
* @param date date to change
* @param partySize number of guests to change
* @return true if success, false otherwise
* @throws SQLException
*/
public boolean changeReservation(String email, Date date, int partySize) throws SQLException {
boolean success = false;
PreparedStatement updateReservation = null;
int customerID = getCID(email);
String query = "update reservation set reservationDate = ?, partysize = ? where cid = ?";
try {
connection.setAutoCommit(false);
updateReservation = (PreparedStatement) connection.prepareStatement(query);
updateReservation.setDate(1, date);
updateReservation.setInt(2, partySize);
updateReservation.setInt(3, customerID);
updateReservation.execute();
connection.commit();
success = true;
} catch (SQLException e) {
e.printStackTrace();
success = false;
} finally {
if (updateReservation != null) {
updateReservation.close();
}
connection.setAutoCommit(true);
return success;
}
}
/**
* Get a customer by id
*
* @param id customer id
* @return a customer
*/
public Customer getACustomer(int id) {
String sql = "Select * From Customer where cid = ?";
try {
PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
statement.setInt(1, id);
ResultSet rs = statement.executeQuery();
if (rs.next()) {
Customer customer = new Customer();
customer.setEmail(rs.getString("email"));
customer.setFirstName(rs.getString("firstName"));
customer.setLastName(rs.getString("lastName"));
customer.setId(rs.getInt("cid"));
if (statement != null) statement.close();
return customer;
} else {
return null;
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println("Error at get a customer");
return null;
}
}
/**
* Rate a restaurant and give a feedback Rate uses SQL INSERT INTO
*
* @param stars number of stars
* @param feedback user's feedback
* @param customer a customer
* @return true if rate works fine, false otherwise
* @throws SQLException
*/
public boolean rate(int stars, String feedback, Customer customer) throws SQLException {
boolean success = false;
PreparedStatement insertStatement = null;
int customerID = getCID(customer.getEmail());
String query =
"INSERT INTO Rating (cid, stars, feedback) VALUES (?,?,?) "
+ "ON DUPLICATE KEY UPDATE stars=?, feedback=?";
try {
connection.setAutoCommit(false);
insertStatement = (PreparedStatement) connection.prepareStatement(query);
insertStatement.setInt(1, customerID);
insertStatement.setInt(2, stars);
insertStatement.setString(3, feedback);
insertStatement.setInt(4, stars);
insertStatement.setString(5, feedback);
insertStatement.execute();
connection.commit();
success = true;
} catch (SQLException e) {
e.printStackTrace();
success = false;
} finally {
if (insertStatement != null) {
insertStatement.close();
}
connection.setAutoCommit(true);
return success;
}
}
/**
* Checks if the databasePassword is the same as the provided password.
*
* @param login
* @param password
* @return true / false
* @throws InstantiationException
* @throws IllegalAccessException
* @throws ClassNotFoundException
* @throws SQLException
*/
public static boolean checkPassword(String login, String password)
throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
String sql = "SELECT password FROM users WHERE login = ? ";
String serverPassword = "";
Boolean isValid = false;
Connection connection = DataBaseUtils.getMySQLConnection();
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);
ps.setString(1, login);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
serverPassword = rs.getString("password");
if (password.equals(serverPassword)) {
isValid = true;
}
}
ps.close();
connection.close();
return isValid;
}
/**
* Inserts a new entry in the `session` table.
*
* @param id I
* @param root
* @return A unique key associated with the user
* @throws InstantiationException
* @throws IllegalAccessException
* @throws ClassNotFoundException
* @throws SQLException
*/
public static String insertSession(int id, boolean root)
throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
/* SESSION_HOUR_DURATIONS starting from now */
Timestamp expires =
new Timestamp(System.currentTimeMillis() + SESSION_HOUR_DURATION * 60 * 60 * 1000);
String key = generateKey();
String sql =
"INSERT INTO `session` " + "(`key`, `user_id`, `expires`, `root`)" + " VALUE(?,?,?,?)";
Connection connection = DataBaseUtils.getMySQLConnection();
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);
ps.setString(1, key);
ps.setInt(2, id);
ps.setTimestamp(3, expires);
ps.setBoolean(4, root);
ps.executeUpdate();
System.out.println("Session inserted for id : " + id);
ps.close();
connection.close();
return key;
}
public JasperPrint relatorioModalidade(String modalidade) throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioModalidade.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
modalidade = (new StringBuilder()).append("= '").append(modalidade).append("'").toString();
String query =
(new StringBuilder())
.append(
"SELECT p.nome,a.matricula,m.nome "
+ "AS modalidade FROM pessoas p INNER JOIN alunos a ON p.idPessoa = a.idPessoa"
+ " INNER JOIN adesoes ad ON a.matricula = ad.matricula INNER JOIN planos pl on "
+ "ad.codPlano = pl.`codPlano` INNER JOIN modalidades m ON pl.codModalidade = "
+ "m.codModalidade WHERE m.nome ")
.append(modalidade)
.append(" ")
.append(" ORDER BY p.nome")
.toString();
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
public static void dbUpdate(Creative creative) throws Exception {
Connection conn = null;
PreparedStatement statement = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
conn = DriverManager.getConnection("database_url", "username", "password");
String query =
"update creative set creativeID = ? , "
+ "creativeTimeStamp = ? "
+ "where creativeName = ?";
statement = (PreparedStatement) conn.prepareStatement(query);
statement.setString(1, creative.getCreativeID());
statement.setString(2, creative.getCreativeTimestamp());
statement.setString(3, creative.getName());
statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
statement.close();
conn.close();
}
}
@Override
public boolean guardar() {
boolean ret = false;
try {
String sql = "UPDATE expulsiones SET ano=?,alumno_id=?,fecha=?,dias=? WHERE id=?";
if (getId() == null) {
sql = "INSERT INTO expulsiones (ano,alumno_id,fecha,dias,id) VALUES(?,?,?,?,?)";
}
PreparedStatement st =
(PreparedStatement)
MaimonidesApp.getApplication()
.getConector()
.getConexion()
.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
st.setInt(1, getAnoEscolar().getId());
st.setInt(2, getAlumno().getId());
st.setDate(3, new java.sql.Date(getFecha().getTimeInMillis()));
st.setInt(4, getDias());
st.setObject(5, getId());
ret = st.executeUpdate() > 0;
if (ret && getId() == null) {
setId((int) st.getLastInsertID());
}
st.close();
ret = true;
} catch (SQLException ex) {
Logger.getLogger(Conducta.class.getName())
.log(Level.SEVERE, "Error guardando datos de expulsion: " + this, ex);
}
return ret;
}
/**
* retrieve food items from database
*
* @param type beverage or food , or null for combination
* @return menu - list of items
*/
public ArrayList<Item> getMenu(String type) {
String sql = "SELECT itemName, price, description FROM Menu";
if (type != null) {
sql += " WHERE type=?";
}
try {
PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
if (type != null) {
statement.setString(0, type);
}
ResultSet rs = statement.executeQuery();
ArrayList<Item> menu = new ArrayList<Item>(); // maybe food menu if there is beverage menu
while (rs.next()) {
String itemName = rs.getString("itemName");
double price = rs.getDouble("price");
String desc = rs.getString("description");
Item i = new Item(itemName, price, desc);
menu.add(i);
}
rs.close();
statement.close();
return menu;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
return null;
}
}
public void deleteDokter(String idDokter) throws SQLException {
try {
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(deleteDokter);
ps.setString(1, idDokter);
ps.executeUpdate();
ps.close();
// JOptionPane.showMessageDialog(null, "Data dokter berhasil dihapus!");
} catch (SQLException se) {
// JOptionPane.showMessageDialog(null, se.getMessage(),"Delete Dokter
// Gagal!",JOptionPane.ERROR_MESSAGE);
}
}
public JasperPrint relatorioPlanosVencidos() throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioPlanosVencidos.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
String query = "";
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
public void updateDokter(Dokter d, String idDokter) throws SQLException {
try {
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(updateDokter);
ps.setString(1, d.getNmDokter());
ps.setString(2, d.getIdSpesialis());
ps.setString(3, idDokter);
ps.executeUpdate();
ps.close();
// JOptionPane.showMessageDialog(null, "Data dokter berhasil diubah!");
} catch (SQLException se) {
// JOptionPane.showMessageDialog(null, se.getMessage(),"Update Dokter
// Gagal!",JOptionPane.ERROR_MESSAGE);
}
}
public JasperPrint relatorioAniversariantes(int mes) throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioAniversariantes.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
String query =
"SELECT a.matricula,p.nome,p.nascimento,p.celular,(year(curdate()) - year(nascimento)) as anos FROM pessoas p inner join alunos a on p.idPessoa = a.idPessoa WHERE month(p.nascimento) = "
+ mes;
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
/**
* Deletes the user from the data-base.
*
* @param id user id
* @return true | false
* @throws SQLException
* @throws ClassNotFoundException
* @throws IllegalAccessException
* @throws InstantiationException
*/
public static void removeUserFromDataBase(int id)
throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
String sql = "DELETE FROM users WHERE `id`=?";
Connection connection = DataBaseUtils.getMySQLConnection();
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);
ps.setInt(1, id);
ps.executeUpdate();
System.out.println("User removed for id : " + id);
ps.close();
connection.close();
}
/**
* Removes the entry associated with id, from the `sessions` table.
*
* @param id user id
* @throws InstantiationException
* @throws IllegalAccessException
* @throws ClassNotFoundException
* @throws SQLException
*/
public static void removeSession(String key)
throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
String sql = "DELETE FROM session WHERE `key`=?";
Connection connection = DataBaseUtils.getMySQLConnection();
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);
ps.setString(1, key);
ps.executeUpdate();
System.out.println("Session removed for key : " + key);
ps.close();
connection.close();
}
public void freeConnection(Connection con, PreparedStatement pstmt) {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* Add a customer
*
* @param ln last name
* @param fn fist name
* @param email email
* @return true if succeed, false otherwise
*/
public boolean addCustomer(String ln, String fn, String email) {
String sql_addcustomer = "INSERT INTO CUSTOMER (email,lastname,firstname) VALUES (?,?,?)";
try {
PreparedStatement addCustomer =
(PreparedStatement) connection.prepareStatement(sql_addcustomer);
addCustomer.setString(1, email);
addCustomer.setString(2, ln);
addCustomer.setString(3, fn);
addCustomer.execute();
addCustomer.close();
return true;
} catch (SQLException e) {
e.printStackTrace();
System.out.println(e.getMessage());
return false;
}
}
public void remove(Funcionario funcionario) {
String sql = "DELETE FROM Funcionario WHERE id_funcionario = '?'";
// String sql = "DELETE FROM Funcionario WHERE nome_funcionario = ?";
try {
PreparedStatement stmt = (PreparedStatement) connection.prepareStatement(sql);
stmt.setString(1, funcionario.getNomeFuncionario());
stmt.execute();
stmt.close();
System.out.println(
"Funcionario: " + funcionario.getNomeFuncionario() + " deleted successfully");
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public JasperPrint relatorioIdade(String idadeInicial, String idadeFinal) throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioFaixaEtaria.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
String query =
"SELECT p.nome,a.matricula,curdate()as data_atual,p.nascimento,(year(curdate()) - year(p.nascimento)) idade FROM pessoas p INNER JOIN alunos a ON p.idPessoa = a.idPessoa WHERE (year(curdate()) - year(p.nascimento)) >="
+ idadeInicial
+ " AND (year(curdate()) - year(p.nascimento)) <= "
+ idadeFinal;
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
public String tampilIdByNama(String nama) throws SQLException {
try {
String id = null;
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(tampilIdByNama);
ps.setString(1, nama);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
id = rs.getString("id_dokter");
}
rs.close();
ps.close();
return id;
} catch (SQLException se) {
JOptionPane.showMessageDialog(
null, se.getMessage(), "Tampil ID By Nama Dokter Gagal!", JOptionPane.ERROR_MESSAGE);
return null;
}
}
/**
* Archive customers with a given date
*
* @param date
* @return true if succeed, false otherwise
*/
public boolean archive(String date) {
String sql = "call archiveCustomers(?)";
try {
PreparedStatement statement = (PreparedStatement) connection.prepareStatement(sql);
statement.setString(1, date);
statement.execute();
if (statement != null) {
statement.close();
}
return true;
} catch (SQLException e) {
e.printStackTrace();
System.out.println(e.getMessage());
return false;
}
}
public String[] NamaDokter(String spesialis, int total) throws SQLException {
try {
String[] nama = new String[total];
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(ambilNamaDokter);
ps.setString(1, spesialis);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
nama[rs.getRow() - 1] = rs.getString("nm_dokter");
}
rs.close();
ps.close();
return nama;
} catch (SQLException se) {
JOptionPane.showMessageDialog(
null, se.getMessage(), "String[] Nama Dokter Gagal!", JOptionPane.ERROR_MESSAGE);
return null;
}
}
public void run() {
// this.saveTime = System.currentTimeMillis();
while (true) {
IBLoaderInputStream is = null;
// IBLoaderInputStreamNew is = null;
com.mysql.jdbc.PreparedStatement mysqlStatement = null;
if (finished == 1 && queue.size() == 0) {
stop();
break;
}
try {
is = new IBLoaderInputStream(queue);
// is = new IBLoaderInputStreamNew(queue);
is.init();
// obtain PreparedStatement for load data into infobright.
mysqlStatement = obtainLoadMysqlStatement(is.getActualTableName());
DButil.loadData(mysqlStatement, is);
// if(queue.size()>=IBLConfig.loadQueueSize/5)
// logger.info("current Queue size: " + queue.size());
} catch (InterruptedException e) {
logger.info("ibloader stop when take task from queue", e);
finished = 1;
} catch (SQLException e) {
logger.error("load failed!!!", e);
} catch (Exception e) {
logger.error("load failed!!!", e);
} finally {
try {
if (null != is) is.close();
if (null != mysqlStatement) mysqlStatement.close();
} catch (IOException e) {
logger.info("close inputstream error!!!", e);
} catch (SQLException e) {
logger.info("close mysqlStatement error!!!", e);
} catch (Exception e) {
logger.error("close inputstream or mysqlStatement error!!!", e);
}
}
}
}
public void fromExcelToDB() {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con =
(Connection)
DriverManager.getConnection("jdbc:mysql://localhost/excel", "root", "21092012artem");
con.setAutoCommit(false);
PreparedStatement pstm = null;
File file = new File("D://file.xlsx");
if (!file.exists()) {}
InputStream input = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(input);
XSSFSheet sheet = wb.getSheetAt(0);
Row row;
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
int id = (int) row.getCell(0).getNumericCellValue();
String name = row.getCell(1).getStringCellValue();
String address = row.getCell(2).getStringCellValue();
String sql = "INSERT INTO excel VALUES('" + id + "','" + name + "','" + address + "')";
pstm = (PreparedStatement) con.prepareStatement(sql);
pstm.execute();
System.out.println("Import rows " + i);
}
con.commit();
pstm.close();
con.close();
input.close();
System.out.println("Success import excel to mysql table");
} catch (ClassNotFoundException e) {
System.out.println(e);
} catch (SQLException ex) {
System.out.println(ex);
} catch (IOException ioe) {
System.out.println(ioe);
}
}
public JasperPrint relatorioHorario(String horaInicio) throws Exception {
java.sql.Connection con = ConexaoDB.getInstance().getCon();
String nomeRelatorio = "br/sistcomp/sar/impressao/relatorioHorario.jasper";
URL urlFile = getClass().getClassLoader().getResource(nomeRelatorio);
horaInicio = (new StringBuilder()).append("= '").append(horaInicio).append("'))").toString();
String query =
(new StringBuilder())
.append(
"SELECT a.matricula, p.nome, t.horaInicio, t.horaFinal FROM alunos a, pessoas p, turmas t WHERE a.idPessoa = p.idPessoa AND a.matricula IN (SELECT matricula FROM ADESOES WHERE status=true AND codTurma IN (SELECT codTurma FROM TURMAS where horaInicio ")
.append(horaInicio)
.toString();
PreparedStatement stmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
JRResultSetDataSource jrRS = new JRResultSetDataSource(rs);
java.util.Map parameters = new HashMap();
JasperPrint rel = JasperFillManager.fillReport(urlFile.openStream(), parameters, jrRS);
rs.close();
stmt.close();
return rel;
}
/**
* Returns the session-key associated with the user.
*
* @param id
* @return session-key
* @throws InstantiationException
* @throws IllegalAccessException
* @throws ClassNotFoundException
* @throws SQLException
*/
public static String getUserKey(int id)
throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
String sql = "SELECT `key` FROM session WHERE user_id = ?";
String key = null;
Connection connection = DataBaseUtils.getMySQLConnection();
PreparedStatement ps = (PreparedStatement) connection.prepareStatement(sql);
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
key = rs.getString("key");
}
ps.close();
connection.close();
return key;
}