📄 deptinfodao.java
字号:
package com.galaxy.dao;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.galaxy.base.DaoInterface;
import com.galaxy.db.ConnectDB;
import com.galaxy.vo.DeptInfoVO;
import com.galaxy.vo.LevelInfoVO;
import com.galaxy.vo.UserInfoVO;
import com.galaxy.util.PageHelp;
public class DeptInfoDAO extends ConnectDB implements DaoInterface{
//参数为部门VO对象，即DeptInfoVO对象，其中各个字段(除了ID)都应该为有效值
public int addObject(Object ob) {
int result = 0;
DeptInfoVO dptvo = new DeptInfoVO();
dptvo = (DeptInfoVO)ob;
String psql = "insert into dept_info values (seq.nextval,?,?,?,?,?,?,?)";
super.openDBConnection();
try {
PreparedStatement pst = dbConnection.prepareStatement(psql);
pst.setLong(1, dptvo.getLevelInfo().getLiId());
pst.setString(2, dptvo.getDiName());
pst.setString(3, dptvo.getDiState());
pst.setLong(4, dptvo.getDiUp());
pst.setString(5, dptvo.getDiManager());
pst.setLong(6, dptvo.getDiNumber());
pst.setString(7, dptvo.getDiExtend());
result = pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
super.closeDBConnection();
return result;
}
//c参数应该为删除对象的ID值，可以是多个ID串(以逗号相隔)
public int deleteObject(Object cond) {
int result = 0;
String sql = "delete from dept_info where di_id in("+cond+")";
super.openDBConnection();
try {
result = super.dbStatement.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
super.closeDBConnection();
return result;
}
//参数应该数据查询的条件
public List queryByCondition(Object cond)
{
List deps=new ArrayList();
super.openDBConnection();
String sql = "select * from dept_info d join level_info l on d.li_id=l.li_id where 1=1 "
+ cond + " order by di_id";
try
{
super.dbResultSet = super.dbStatement.executeQuery(sql);
while(super.dbResultSet.next())
{
DeptInfoVO depvo = new DeptInfoVO();
depvo.setDiId(dbResultSet.getLong("di_id"));
depvo.setDiName(dbResultSet.getString("di_name"));
depvo.setDiState(dbResultSet.getString("di_state"));
depvo.setDiUp(dbResultSet.getLong("di_up"));
depvo.setDiManager(dbResultSet.getString("di_manager"));
depvo.setDiNumber(dbResultSet.getLong("di_number"));
depvo.setDiExtend(dbResultSet.getString("di_extend"));
LevelInfoVO levelinfovo = new LevelInfoVO();//根据部门表中的外键查询相应的级别表的信息
levelinfovo.setLiId(dbResultSet.getLong("li_id"));
levelinfovo.setLiName(dbResultSet.getString("li_name"));
levelinfovo.setLiState(dbResultSet.getString("li_state"));
levelinfovo.setLiTag(dbResultSet.getString("li_tag"));
depvo.setLevelInfo(levelinfovo);
deps.add(depvo);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
super.closeDBConnection();
return deps;
}
//参数应该数据查询的条件
public List queryByDepid(Object cond)
{
List deps=new ArrayList();
super.openDBConnection();
String sql = "select * from dept_info where 1=1"
+ cond + "order by di_id";
try
{
super.dbResultSet = super.dbStatement.executeQuery(sql);
while(super.dbResultSet.next())
{
DeptInfoVO depvo = new DeptInfoVO();
depvo.setDiId(dbResultSet.getLong("di_id"));
depvo.setDiName(dbResultSet.getString("di_name"));
depvo.setDiState(dbResultSet.getString("di_state"));
depvo.setDiUp(dbResultSet.getLong("di_up"));
depvo.setDiManager(dbResultSet.getString("di_manager"));
depvo.setDiNumber(dbResultSet.getLong("di_number"));
depvo.setDiExtend(dbResultSet.getString("di_extend"));
deps.add(depvo);
}
}
catch(SQLException e)
{
e.printStackTrace();
}
super.closeDBConnection();
return deps;
}
//参数为部门VO对象，即DeptInfoVO对象，要求ID有效
public Object readObject(Object cond)
{
DeptInfoVO depvo = (DeptInfoVO)cond;
super.openDBConnection();
String sql = "select * from dept_info where di_id = ?";
try
{
PreparedStatement pst = super.dbConnection.prepareStatement(sql);
pst.setLong(1, depvo.getDiId());
super.dbResultSet = pst.executeQuery();
while(super.dbResultSet.next())
{
depvo.setDiName(dbResultSet.getString("di_name"));
depvo.setDiState(dbResultSet.getString("di_state"));
depvo.setDiUp(dbResultSet.getLong("di_up"));
depvo.setDiManager(dbResultSet.getString("di_manager"));
depvo.setDiNumber(dbResultSet.getLong("di_number"));
depvo.setDiExtend(dbResultSet.getString("di_extend"));
}
}
catch(SQLException e)
{
e.printStackTrace();
}
super.closeDBConnection();
return depvo;
}
//参数为部门VO对象，即DeptInfoVO对象，其中各个字段都应该为有效值
public int updateObject(Object ob) {
int result = 0;
DeptInfoVO dptvo = new DeptInfoVO();
dptvo = (DeptInfoVO)ob;
String psql = "update dept_info set li_id=?, di_name=?, di_state=?, " +
"di_up=?, di_manager=?, di_number=?, di_extend=? where di_id=?";
super.openDBConnection();
try {
PreparedStatement pst = dbConnection.prepareStatement(psql);
pst.setLong(1, dptvo.getLevelInfo().getLiId());
pst.setString(2, dptvo.getDiName());
pst.setString(3, dptvo.getDiState());
pst.setLong(4, dptvo.getDiUp());
pst.setString(5, dptvo.getDiManager());
pst.setLong(6, dptvo.getDiNumber());
pst.setString(7, dptvo.getDiExtend());
pst.setLong(8, dptvo.getDiId());
result = pst.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
super.closeDBConnection();
return result;
}
public int combine(String cond, Object ob)
{
int i = 0;
DeptInfoVO deptvo = (DeptInfoVO)ob;
String sql1 = "update dept_info set di_state='被合并' where di_id in (" + cond + ")";
String sql2 = "select sum(di_number) pop from dept_info where di_id in (" + cond + ")";
super.openDBConnection();
try {
super.dbResultSet = super.dbStatement.executeQuery(sql2);
while(super.dbResultSet.next()){
deptvo.setDiNumber(super.dbResultSet.getLong("pop"));
}
i = this.addObject(deptvo);//添加新合并的部门
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
super.closeDBConnection();
if(i != 0)
{
super.openDBConnection();
try {
super.dbStatement.executeUpdate(sql1);//被合并的部门设状态为被合并
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
super.closeDBConnection();
//将被合并部门旗下的人员转移到新部门下
String cond1 = " and d.di_name='"+deptvo.getDiName()+"'";
List list = this.queryByCondition(cond1);
deptvo = (DeptInfoVO)list.get(0);
UserInfoDAO userdao = new UserInfoDAO();
UserInfoVO uservo = new UserInfoVO();
String deptid[] = cond.split(",");
for(int j = 0; j < deptid.length; j++)
{
cond1 = " and u.di_id="+deptid[j];
List userlist = userdao.queryByCondition(cond1);
for(int n = 0; n < userlist.size(); n++)
{
uservo = (UserInfoVO)userlist.get(n);
uservo.getDeptInfo().setDiId(deptvo.getDiId());
userdao.updateObject(uservo);
}
}
}
return i;
}
public PageHelp getList(String condition, int pageSize, int currentPage) {
int listSize=0;
List userlist=new ArrayList();//存放要显示到页面上的部分结果
PageHelp pageHelp=new PageHelp();
String sql ="";
try { //统计记录总数
super.openDBConnection();
ResultSet rs = null;
sql="select count(*) listSize from dept_info d join level_info t on d.li_id=t.li_id ";
if(!"".equals(condition)&&condition!=null){
sql = "select count(*) listSize from dept_info d join level_info t on d.li_id=t.li_id where 1=1 "+condition;
}
sql=sql+" order by d.di_id";
System.out.println("sqlcount------"+sql);
pageHelp.setSqlstr(sql);
rs = super.dbStatement.executeQuery(sql); //执行sql语句
// 执行sql语句
while (rs.next()) {
listSize=rs.getInt("listSize");
}
}
catch (SQLException SqlE) {
SqlE.printStackTrace();
}
catch (Exception E) {
E.printStackTrace();
}
finally {
// 关闭连接，释放数据库资源：
super.closeDBConnection();
}
try { //找到要显示的记录
super.openDBConnection();
int startNum=(currentPage - 1) * pageSize+1;//由于数据库中没有第0条记录所以要进行+1修正
int endNum= currentPage* pageSize+1;
sql = "select * from (select a.* ,rownum rc from(select * from dept_info d join level_info t on d.li_id=t.li_id ) a where rownum<"+endNum+") b where rc >="+startNum+"";
if(!"".equals(condition)&&condition!=null)
sql = "select * from (select a.* ,rownum rc from(select * from dept_info d join level_info t on d.li_id=t.li_id where 1=1 "+condition+" ) a where rownum<"+endNum+") b where rc >="+startNum+"";
sql=sql+" order by di_id";
System.out.println("sqllist------"+sql);
pageHelp.setSqlstr(sql);
super.dbResultSet = super.dbStatement.executeQuery(sql); //执行sql语句
System.out.println("list------");
// 执行sql语句
while(super.dbResultSet.next()){
DeptInfoVO depvo = new DeptInfoVO();
depvo.setDiId(dbResultSet.getLong("di_id"));
depvo.setDiName(dbResultSet.getString("di_name"));
depvo.setDiState(dbResultSet.getString("di_state"));
depvo.setDiUp(dbResultSet.getLong("di_up"));
depvo.setDiManager(dbResultSet.getString("di_manager"));
depvo.setDiNumber(dbResultSet.getLong("di_number"));
depvo.setDiExtend(dbResultSet.getString("di_extend"));
LevelInfoVO levelinfovo = new LevelInfoVO();//根据部门表中的外键查询相应的级别表的信息
levelinfovo.setLiId(dbResultSet.getLong("qcsj_c005001"));//li_id
levelinfovo.setLiName(dbResultSet.getString("li_name"));
levelinfovo.setLiState(dbResultSet.getString("li_state"));
levelinfovo.setLiTag(dbResultSet.getString("li_tag"));
depvo.setLevelInfo(levelinfovo);
userlist.add(depvo);
}
System.out.println("list------"+userlist.size());
}
catch (SQLException SqlE) {
SqlE.printStackTrace();
}
catch (Exception E) {
E.printStackTrace();
}
finally {
// 关闭连接，释放数据库资源：
super.closeDBConnection();
}
// 设置页面有关分页的显示信息
pageHelp.setCondition(condition);
pageHelp.setCurrentpage(currentPage); //要显示的是第几页
pageHelp.setPagesize(pageSize); //每页显示几条记录
pageHelp.setRecordcount(listSize); //按当前条件查询结果的全部记录数（总条数）
pageHelp.getPagecount(); //按照“页数=记录总数/每页显示条数”得到显示页数
pageHelp.setSqlstr(sql); //将当前的查询条件装入gageHelp对象中
pageHelp.setPagebar("DeptInfoServlet");//设置上一页，下一页，首页，末页的显示条
pageHelp.setObjectlist(userlist);//将list对象存储起来
return pageHelp;
}
public static void main(String [] Args)
{
/**
* 测试使用代码
DeptInfoDAO dptDao = new DeptInfoDAO();
DeptInfoVO dptvo = new DeptInfoVO();
LevelInfoVO levelInfo = new LevelInfoVO();
levelInfo.setLiId(1L);
dptvo.setLevelInfo(levelInfo);
dptvo.setDiName("乱七八糟部");
dptvo.setDiState("不可用");
dptvo.setDiUp(0L);
dptvo.setDiManager("阿不知");
dptvo.setDiNumber(0L);
*/
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -