@Override
public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
Set<EmpVO> set = new LinkedHashSet<EmpVO>();
EmpVO empVO = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();
while (rs.next()) {
empVO = new EmpVO();
empVO.setEmpno(rs.getInt("empno"));
empVO.setEname(rs.getString("ename"));
empVO.setJob(rs.getString("job"));
empVO.setHiredate(rs.getDate("hiredate"));
empVO.setSal(rs.getDouble("sal"));
empVO.setComm(rs.getDouble("comm"));
empVO.setDeptno(rs.getInt("deptno"));
set.add(empVO); // Store the row in the vector
}
// Handle any driver errors
} catch (ClassNotFoundException e) {
throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
// Handle any SQL errors
} catch (SQLException se) {
throw new RuntimeException("A database error occured. " + se.getMessage());
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
return set;
}
public static String getCurrentProfile() {
String url = "jdbc:mysql://" + var.db_host + "/" + var.db_name;
String login = var.db_username;
String passwd = var.db_psswd;
Connection cn = null;
Statement stmt = null;
String query = "SELECT Name FROM profile WHERE ID='0';";
try {
Class.forName("com.mysql.jdbc.Driver");
cn = DriverManager.getConnection(url, login, passwd);
stmt = cn.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
return rs.getString("Name");
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return "";
}
public static void main(String[] args) {
Connection conn = null;
try {
// Step 1: connect to database server
Driver d = new SimpleDriver();
conn = d.connect("jdbc:simpledb://localhost", null);
// Step 2: execute the query
Statement stmt = conn.createStatement();
String qry = "select SName, DName " + "from DEPT, STUDENT " + "where MajorId = DId";
ResultSet rs = stmt.executeQuery(qry);
// Step 3: loop through the result set
System.out.println("Name\tMajor");
while (rs.next()) {
String sname = rs.getString("SName");
String dname = rs.getString("DName");
System.out.println(sname + "\t" + dname);
}
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// Step 4: close the connection
try {
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/** update a project */
public void update(Project project) {
PreparedStatement stmt = null;
try {
String sql =
"update Projects "
+ "set Title = ?, RecordSperImage = ?, FirstYCoord = ?, RecordHeight = ? "
+ "where ProjectID = ?";
stmt = db.getConnection().prepareStatement(sql);
stmt.setString(1, project.getTitle());
stmt.setInt(2, project.getRecordsPerImage());
stmt.setInt(3, project.getFirstYCoord());
stmt.setInt(4, project.getRecordHeight());
stmt.setInt(5, project.getProjectID());
if (stmt.executeUpdate() == 1) {
System.out.println("Update success");
} else {
System.out.println("Update fail");
}
} catch (SQLException e) {
System.out.println("Can't execute update");
e.printStackTrace();
} finally {
try {
if (stmt != null) stmt.close();
} catch (SQLException e) {
System.out.println("Can't execute connect");
e.printStackTrace();
}
}
}
public boolean findUser(String un, String pwd) {
Connection con = null;
try {
con = DBConnectionFactory.getConnection();
String sql =
"select count(uname) from user_details"
+ " where uname='"
+ un
+ "' and pass='******'";
PreparedStatement st = con.prepareStatement(sql);
ResultSet rs = st.executeQuery();
rs.next();
int count = rs.getInt(1);
return (count == 1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
} // finally
return false;
}
public DBConn(Properties p) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("No Oracle JDBC Driver found.");
e.printStackTrace();
return;
}
try {
connection =
DriverManager.getConnection(
p.getProperty("db_adr"), p.getProperty("db_usr"), p.getProperty("db_pwd"));
} catch (SQLException e) {
e.printStackTrace();
}
if (connection == null) {
System.out.println("Failed to make connection!");
} else {
try {
connection.setAutoCommit(false);
System.out.println("Connection established and ready to work.");
} catch (SQLException e) {
System.out.println("Can't turn off autocommit mode.");
e.printStackTrace();
}
}
}
@Override
protected String getParam(String name) {
/* function get_setting (
p_key in dxdy_spider_setting.key%type) return varchar2;
*/
String proc = "{? = call dxdy_executable.get_setting (?)}";
CallableStatement st = null;
String result = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.VARCHAR);
st.setString(2, name);
st.execute();
result = st.getString(1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
@Override
public int getLastTopicPage(int tID) {
String proc = "{? = call dxdy_executable.get_topic_last_page(?)}";
CallableStatement st = null;
int res = 0;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.INTEGER);
st.setInt(2, tID);
st.execute();
res = st.getInt(1);
} catch (SQLException e) {
System.out.println("Error while querying last page of topic ID = " + tID);
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return res;
}
private Doctor update(Doctor object) {
Connection connection = connectionManager.getConnection();
PreparedStatement ps = null;
try {
ps =
connection.prepareStatement(
"update DRUG d set d.NAME = ?, d.SURNAME = ?, d.EXPERTISE = ? " + " where d.ID = ?");
ps.setString(1, object.getName());
ps.setString(2, object.getSurname());
ps.setString(3, object.getExpertise());
ps.setLong(4, object.getId());
ps.executeUpdate();
return this.find(object.getId());
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
throw new DrugStoreRuntimeException(e);
}
}
}
return null;
}
@Override
public Doctor find(Long key) {
Connection connection = connectionManager.getConnection();
PreparedStatement ps = null;
ResultSet result = null;
Doctor doctor = null;
try {
ps = connection.prepareStatement("select * from Doctor d where d.id = ?");
ps.setLong(1, key);
result = ps.executeQuery();
while (result.next()) {
doctor = DoctorRepositoryJDBCImpl.buildObjectFromResultSet(result, "d");
}
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
if (result != null) {
try {
result.close();
ps.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
throw new DrugStoreRuntimeException(e);
}
}
}
return doctor;
}
/**
* 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;
}
/**
* Set the count of a certain message to a certain value
*
* @param node node to set the count for
* @param playerId player for whom we are tracking the count
* @param value value to set
*/
private void set(MessageNode node, int playerId, int value) {
Validate.isTrue(value >= 0, "Count has to be positive");
Connection conn = null;
Statement statement = null;
try {
conn = retrieveConnection();
statement = conn.createStatement();
// Set the count to the provided value
String setQuery =
String.format(
"UPDATE %s SET %s = %s WHERE id = %s",
msgTable, node.getColumnName(), value, playerId);
statement.execute(setQuery);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (conn != null) conn.close();
if (statement != null) statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public boolean TableExists(String TableName) {
String Query = "SHOW TABLES LIKE '" + TableName + "' ";
Statement stmt = null;
ResultSet rs = null;
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(Query);
return rs.next();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (stmt != null) stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (rs != null) rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
/** update a field */
public void update(Field field) {
PreparedStatement stmt = null;
try {
String sql =
"update Fields "
+ "set XCoord = ?, Width = ?, HelpHTML = ?, "
+ "KnownData = ?, ProjectID = ?, Title = ? "
+ "where FieldID = ?";
stmt = db.getConnection().prepareStatement(sql);
stmt.setInt(1, field.getxCoord());
stmt.setInt(2, field.getWidth());
stmt.setString(3, field.getHelpHTML());
stmt.setString(4, field.getKnownData());
stmt.setInt(5, field.getProjectID());
stmt.setInt(7, field.getFieldID());
stmt.setString(6, field.getTitle());
if (stmt.executeUpdate() == 1) {
System.out.println("Update success");
} else {
System.out.println("Update fail");
}
} catch (SQLException e) {
System.out.println("Can't execute update");
e.printStackTrace();
} finally {
try {
if (stmt != null) stmt.close();
} catch (SQLException e) {
System.out.println("Can't execute connect");
e.printStackTrace();
}
}
}
@Override
public void actionPerformed(ActionEvent axnEve) {
Object obj = axnEve.getSource();
if (obj == replyBut) {
try {
if (Home.composeDial != null && Home.composeDial.isShowing()) {
} else {
Home.composeDial =
new ComposeMailDialog(
msgID, workingSet.getString("mail_addresses"), workingSet.getString("subject"));
}
} catch (SQLException sqlExc) {
sqlExc.printStackTrace();
}
} else if (obj == forwardBut) {
try {
if (Home.composeDial != null && Home.composeDial.isShowing()) {
} else {
Home.composeDial =
new ComposeMailDialog(
msgID,
workingSet.getString("subject") + "\n\n" + workingSet.getString("content"));
}
} catch (SQLException sqlExc) {
sqlExc.printStackTrace();
}
}
}
@Override
protected void saveModer(ModeratorMessage mm, int mID) {
/* function add_moderation (
p_message_id in dxdy_moderation.message_id%type,
p_move_from in dxdy_forum.forum_name%type,
p_move_to in dxdy_forum.forum_name%type,
p_user in dxdy_user.username%type,
p_message_type in dxdy_notice_type.notice_name%type) return varchar2; */
String proc = "{? = call dxdy_executable.add_moderation(?, ?, ?, ?, ?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, java.sql.Types.VARCHAR);
st.setInt(2, mID);
st.setString(3, mm.moveFrom);
st.setString(4, mm.moveTo);
st.setString(5, mm.moderator);
st.setString(6, mm.messageType);
st.execute();
// System.out.println("Link ID = " + st.getString(1));
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
@Override
protected void saveUser(User u) {
/*procedure add_user(
p_id in dxdy_user.id%type,
p_username in dxdy_user.username%type,
p_status in dxdy_status.status_name%type,
p_reg_date in varchar2);
*/
super.saveUser(u);
String procedure = "{call dxdy_executable.add_user (?, ?, ?, ?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(procedure);
st.setInt(1, u.ID);
st.setString(2, u.name);
st.setString(3, u.status);
st.setString(4, u.regDate);
st.execute();
// System.out.println("User ID = " + u.ID + ": OK");
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public String getUserPasswordByEmail(String email) {
connect();
ResultSet resultSet = null;
try {
resultSet =
stmt.executeQuery(
String.format(
"SELECT password FROM edrc_therapist WHERE email='%s'",
email) /*"SELECT password FROM edrc_therapist WHERE email='"+email+"'"*/);
} catch (SQLException e) {
e.printStackTrace();
}
assert resultSet != null;
String password = null;
try {
while (resultSet.next()) {
password = resultSet.getString("password");
System.out.println(password + "this is the pass");
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
stmt.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
return decriptionMD5(password);
}
@Override
public String getLastTopicDate(int tID) {
/* function get_topic_last_update (p_topic_id in dxdy_topic.id%type) return varchar2; */
String proc = "{? = call dxdy_executable.get_topic_last_update(?)}";
CallableStatement st = null;
String res = "";
try {
// System.out.println("Request date for topic ID = " + tID);
st = connection.prepareCall(proc);
st.registerOutParameter(1, Types.VARCHAR);
st.setInt(2, tID);
st.execute();
res = st.getString(1);
// System.out.println("Requested date = " + res);
} catch (SQLException e) {
System.out.println("Error while querying last message date of topic ID = " + tID);
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return res;
}
@Override
public Student findByStudentId(int studentId) {
// TODO Auto-generated method stub
String sql = "SELECT * FROM student WHERE ID=?";
Connection connection = null;
Student student = null;
try {
connection = dataSource.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, studentId);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
student =
new Student(
resultSet.getString("NAME"),
resultSet.getString("CLASSNAME"),
resultSet.getString("STATUS"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return student;
}
@Override
public boolean getForumFullScan(String forum) {
/* function get_forum_scan (p_forum in varchar2) return number; */
boolean result = true;
String proc = "{? = call dxdy_executable.get_forum_scan (?)}";
CallableStatement st = null;
try {
st = connection.prepareCall(proc);
st.registerOutParameter(1, Types.INTEGER);
st.setString(2, forum);
st.execute();
result = (st.getInt(1) == 1);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return result;
}
// Returns a string array containing all results. Only use this when your SQL query will return
// one column.
public String[] sendSelectQueryArr(String query) {
List<String> results = new ArrayList<String>();
Statement statement = null;
try {
statement = dbConnection.createStatement();
ResultSet result = statement.executeQuery(query);
while (result.next()) {
results.add(result.getString(1));
}
} catch (SQLException e) {
registerException(e);
e.printStackTrace();
} finally {
try {
statement.close();
} catch (SQLException e) {
registerException(e);
e.printStackTrace();
}
}
// Turn the list into an array
return results.toArray(new String[results.size()]);
}
public MetadataBlob[] select(String selectString) {
PreparedStatement stmt = null;
try {
stmt =
conn.prepareStatement(
"select writingKey, mapkey, blobdata from metadatablobs " + selectString + ";");
ResultSet rs = stmt.executeQuery();
List<MetadataBlob> list = new ArrayList<MetadataBlob>();
while (rs.next()) {
MetadataBlob f =
new MetadataBlob(
rs.getString("writingkey"), rs.getString("mapkey"), rs.getString("blobdata"));
list.add(f);
}
return list.toArray(new MetadataBlob[0]);
} catch (SQLException sqe) {
System.err.println("Error selecting: " + selectString);
sqe.printStackTrace();
return null;
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException sqe2) {
sqe2.printStackTrace();
}
}
}
public static Map<String, Text> getAllTexts() {
Map<String, Text> l = new TreeMap<String, Text>();
Connection conn = null;
Statement stmt = null;
try {
// STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");
// STEP 3: Open a connection
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
// STEP 4: Execute a query
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql_user;
sql_user = "******" + "FROM text " + ";";
ResultSet rs = stmt.executeQuery(sql_user);
// STEP 5: Extract data from result set
while (rs.next()) {
// Retrieve by column name
Text t =
new Text(
rs.getString("textID"),
rs.getString("content"),
rs.getString("rdf"),
rs.getString("source"),
rs.getString("author"),
rs.getString("dateRef"));
l.put(rs.getString("textID"), t);
}
stmt.close();
conn.close();
} catch (SQLException se) {
// Handle errors for JDBC
se.printStackTrace();
} catch (Exception e) {
// Handle errors for Class.forName
e.printStackTrace();
} finally {
// finally block used to close resources
try {
if (stmt != null) {
stmt.close();
}
} catch (SQLException se2) {
} // nothing we can do
try {
if (conn != null) {
conn.close();
}
} catch (SQLException se) {
se.printStackTrace();
} // end finally try
} // end try
System.out.println("Goodbye!");
return l;
}
@Override
public User getUserDetails(String uname) {
Connection con = null;
try {
con = DBConnectionFactory.getConnection();
Statement st = con.createStatement();
String sql =
"select fname, lname, email, mobile, usertype "
+ "from user_details "
+ "where uname='"
+ uname
+ "'";
ResultSet rs = st.executeQuery(sql);
if (rs.next()) {
User u = new User();
u.setUname(uname);
u.setFname(rs.getString(1));
u.setLname(rs.getString(2));
u.setEmail(rs.getString(3));
u.setMobile(rs.getString(4));
u.setUsertype(rs.getString(5));
return u;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
@Override
public Customer getCurrentCustomer(String login, String password) {
ResultSet res =
requests.select(
"select customer_id from users where login = '******' AND password = '******'");
try {
if (res.next()) {
int id = res.getInt(1);
String name = getStringToId("customers", "name", id, "customer_id");
System.out.println(name);
Customer customer = new Customer(name, id);
return customer;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (res != null) res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
/**
* get a project by projectID
*
* @return a project
*/
public Project getProject(int ID) {
PreparedStatement stmt = null;
ResultSet rs = null;
Project project = null;
try {
String sql =
"select ProjectID, Title, RecordSperImage, "
+ "FirstYCoord, RecordHeight from Projects where ProjectID = ?";
stmt = db.getConnection().prepareStatement(sql);
stmt.setInt(1, ID);
rs = stmt.executeQuery();
while (rs.next()) {
Project newProject =
new Project(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getInt(5));
project = newProject;
}
} catch (SQLException e) {
System.out.println("Can't execute query");
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
} catch (SQLException e) {
System.out.println("Can't execute connect");
e.printStackTrace();
}
}
return project;
}
public static void main(String[] args) {
Connection conn = null;
try {
String connUrl = "jdbc:sqlserver://localhost:1433;databaseName=jdbc";
conn = DriverManager.getConnection(connUrl, "sa", "passw0rd");
String qryStmt = "SELECT ename, salary FROM employee";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(qryStmt);
while (rs.next()) {
System.out.print("name = " + rs.getString("ename") + ", ");
System.out.println("salary = " + rs.getDouble("salary"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
} // end of main()
/**
* excluir
*
* @param VO Usuario
* @return boolean
*/
public static boolean excluir(Usuario usuario) {
SigeDataBase db = new SigeDataBase();
try {
db.getConn().setAutoCommit(false);
db.prepareStatement("DELETE FROM permissao_usuario WHERE cd_usuario=?");
db.setInt(1, usuario.getCodigo());
db.executeUpdate();
db.getPreparedStatement().close();
db.prepareStatement("DELETE FROM usuarios WHERE cd_usuario=?");
db.setInt(1, usuario.getCodigo());
db.executeUpdate();
db.getConn().commit();
db.closeConnection();
return true;
} catch (SQLException ex) {
ex.printStackTrace();
try {
db.getConn().rollback();
db.closeConnection();
} catch (SQLException ex1) {
ex1.printStackTrace();
db.closeConnection();
}
return false;
}
}
public boolean execute(String update) {
// System.out.println( "Database.execute: "+ update );
Statement stmt = null;
ResultSet results = null;
try {
assureConnected();
stmt = connection.createStatement();
stmt.executeUpdate(update);
} catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
return false;
} finally {
if (results != null) {
try {
results.close();
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}
results = null;
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException sqlEx) {
sqlEx.printStackTrace();
}
stmt = null;
}
}
return true;
}
