颜色代码 #000~#FFF & DML案例设计

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(" -------违反完整约束条件-------请删除子表后再行操作------- ");
// 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(" " + sql + " ");

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

}