package com.qingruan.dao;
import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;
import com.qingruan.pojo.Email;import com.qingruan.pojo.EmpInfo;
public class EmpDao extends Dao {
/* * * lastResult最后一条数据的位置 maxResult 最大返回数量 */ @SuppressWarnings({ "rawtypes", "unchecked" }) public List queryEmployeeList(int lastResult, int maxResult) {
open();
String sql = "select employee_id,first_name,last_name,salary,phone_number from " + "(select employee_id,first_name,last_name,salary,phone_number from " + "(select employee_id,first_name,last_name,salary,phone_number " + "from employees order by employee_id) " + "where rownum <=" + (lastResult) + " order by employee_id desc)" + "where rownum <=" + maxResult + "order by employee_id";
// System.out.println(sql);
List list = new ArrayList<EmpInfo>(); EmpInfo empInfo = null;
int empId = 0; double salary = 0; String firstName = null; String lastName = null; String phoneNumber = null;
try { ResultSet rs = stat.executeQuery(sql);
while (rs.next()) { empId = rs.getInt(1); firstName = rs.getString(2); lastName = rs.getString(3); phoneNumber = rs.getString(5); salary = rs.getDouble(4);
empInfo = new EmpInfo(empId, firstName, lastName, phoneNumber, salary); list.add(empInfo); }
} catch (SQLException e) { e.printStackTrace(); } finally { close(); }
return list; }
public int queryEmployeeCount() { open(); int count = 0; try { ResultSet rs = stat.executeQuery("select count(*) from employees"); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } finally { close(); }
return count; }
// 删除employees表中的数据 public void deleteEmployee(String id) {
open();
try { // 更新，将用户表和部门表中的管理ID为要删除这个人得数据进行置空 stat.executeUpdate("update employees set manager_id = '' where manager_id=" + id); stat.executeUpdate("update departments set manager_id = '' where manager_id=" + id); stat.executeUpdate("delete employees where employee_id=" + id);
conn.commit();
} catch (Exception e) { try { conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } e.printStackTrace(); } finally { close(); }
}
// 通过empId查询出一个emp对象 public EmpInfo queryEmployeeById(String id) { open(); EmpInfo empInfom = null; try { ResultSet rs = stat .executeQuery("select employee_id,first_name,last_name,salary,phone_number from employees where employee_id =" + id);
if (rs.next()) { int empId = rs.getInt(1); String firstName = rs.getString(2); String lastName = rs.getString(3); double salary = rs.getDouble(4); String phoneNumber = rs.getString(5); empInfom = new EmpInfo(empId, firstName, lastName, phoneNumber, salary); }
} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }
return empInfom; }
public void updateEmployee(EmpInfo empInfo) {
String sql = "update employees set first_name = '" + empInfo.getFirstName() + "'," + "last_name='" + empInfo.getLastName() + "',salary=" + empInfo.getSalary() + ",phone_number='" + empInfo.getPhoneNumber() + "' " + "where employee_id = " + empInfo.getEmpid(); open(); try { stat.executeUpdate(sql); conn.commit(); } catch (SQLException e) { e.printStackTrace(); try { conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } } finally { close(); }
} @SuppressWarnings({ "rawtypes", "unchecked" }) public List getEmpName(String empName,String limit){ open(); List list = new ArrayList<String>(); try { ResultSet rs = stat.executeQuery("select first_name from employees where lower(first_name) like '"+empName+"%' and rownum<="+limit); //System.out.println("select first_name from employees where first_name like '"+empName+"%' and rownum<="+limit); while (rs.next()) { list.add(rs.getString(1)); } } catch (SQLException e) { e.printStackTrace(); }finally{ close(); } return list; } @SuppressWarnings({ "rawtypes", "unchecked" }) public List getEmpEmail(String empName,String limit){ open(); List list = new ArrayList<Email>(); try { ResultSet rs = stat.executeQuery("select first_name,email from employees where lower(first_name) like '"+empName+"%' and rownum<="+limit); System.out.println("select first_name,email from employees where lower(first_name) like '"+empName+"%' and rownum<="+limit); while(rs.next()){ Email e = new Email(rs.getString(1),rs.getString(2)); list.add(e); } } catch (SQLException e) { e.printStackTrace(); }finally{ close(); } return list; }
/** * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * -------------------------------------非系统员工表数据---------------------------- * */ @SuppressWarnings({ "rawtypes", "unchecked" }) public List queryMyEmpList(int lastResult, int maxResult) {
open();
String sqlUsers = "select emp_id,first_name,last_name,salary from " + "(select emp_id,first_name,last_name,salary from " + "(select emp_id,first_name,last_name,salary " + "from emp order by emp_id) " + "where rownum <=" + (lastResult) + " order by emp_id desc)" + "where rownum <=" + maxResult + "order by emp_id"; // System.out.println(sqlUsers);
List list = new ArrayList<EmpInfo>(); EmpInfo empInfo = null;
int empId = 0; double salary = 0; String firstName = null; String lastName = null;
try { ResultSet rs = stat.executeQuery(sqlUsers);
while (rs.next()) { empId = rs.getInt(1); firstName = rs.getString(2); lastName = rs.getString(3); salary = rs.getDouble(4);
empInfo = new EmpInfo(empId, firstName, lastName, salary); list.add(empInfo); }
} catch (SQLException e) { e.printStackTrace(); } finally { close(); }
return list; }
public int queryMyEmpCount() { open(); int count = 0; try { ResultSet rs = stat.executeQuery("select count(*) from emp"); if (rs.next()) { count = rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } finally { close(); }
return count; }
public void deleteMyEmp(String empId) {
open();
try { stat.executeQuery("delete from emp where emp_id ='" + empId + "'"); } catch (SQLException e) { System.out.println("\n-------违反完整约束条件-------请删除子表后再行操作-------\n"); // e.printStackTrace(); } finally { close(); }
}
public EmpInfo queryMyEmpById(String id) { open(); EmpInfo empInfom = null; try { ResultSet rs = stat .executeQuery("select emp_id,first_name,last_name,salary from emp where emp_id =" + id);
if (rs.next()) { int empId = rs.getInt(1); String firstName = rs.getString(2); String lastName = rs.getString(3); double salary = rs.getDouble(4); empInfom = new EmpInfo(empId, firstName, lastName, salary); }
} catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }
return empInfom; }
public boolean updateMyEmp(EmpInfo empInfo) { // 返回值 boolean b = false;
// 插入之前，先获取用户id open();
try { // 取消事物的自动提交 conn.setAutoCommit(false);
String sql = "update emp set first_name = '" + empInfo.getFirstName() + "'," + "last_name='" + empInfo.getLastName() + "',salary=" + empInfo.getSalary() + " where emp_id = " + empInfo.getEmpid();
System.out.println("\n" + sql + "\n");
if (stat.executeUpdate(sql) > 0) {
System.out .println("--------------------修改完成-------------------");
}
} catch (SQLException e) { try { conn.rollback(); } catch (SQLException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } e.printStackTrace(); } finally { close(); }
return b; }
}
转载于:https://www.cnblogs.com/Dream-Lasting/p/4185519.html
相关资源：JAVA上百实例源码以及开源项目