@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; }