我大概看了下。你的意思。这是一个树状结构的字段更新问题。插入节点或删除节点,需要更新父节点的字段值。首先要放在一个事务里处理。 对于havechild:的更新比较简单,如果是在某个节点下插入节点,只要更新其父节点就可以了。 对于grouplevel: ORACLE里面有树状查询的语法。可以将某个节点的,往上所有节点都查出来。 举个例子: ORACLE 有对LEVEL的直接支持。update app_group set grouplevel = grouplevel+1 where id in( select id from app_group start with id=当前插入节点fatherid connect by prior fatherid=id )删除节点比较麻烦。 对于节点层次的重新计算,Oracle有对LEVEL的直接支持。 如果是其他数据库,可能要用存储过程来处理了。
String sql = "insert into app_group(id, name, CREATEDDATE, fatherid, havechild, grouplevel, dept_id, ismaporganization)" + " values(?,?,sysdate,?,?,?,?,1)"; String sq3=" update app_group set grouplevel=grouplevel+1 where id in (select id from app_group start with id=fatherid connect by prior fatherid=id)"; String sql2 = "insert into app_applicationgroup values(1, ?)";
public static boolean importOrg(String file) { //从xml中获取组织机构信息 List data = readXML(file);
//过滤掉在现有系统中已有的组织机构 data = filter(data);
//将新的组织机构导入到系统中 boolean result = updateOrg(data);
return result; }
/** * 读取xml信息 * * @param file * @return */ public static List readXML(String file) { SAXReader reader = new SAXReader(); List data = new ArrayList(); try { InputStreamReader in = new InputStreamReader(new FileInputStream(file), "UTF-8"); Document document = reader.read(in); Element root = document.getRootElement();
//获取父信息 List list = root.selectNodes("//Organization"); String[] parent = new String[5]; Element node = (Element)list.get(0); parent[0] = node.attribute("id").getText(); parent[1] = node.attribute("name").getText(); parent[2] = node.attribute("fid").getText(); parent[3] = node.attribute("havechild").getText(); parent[4] = node.attribute("grouplevel").getText(); data.add(parent);
//获取子信息 list = root.selectNodes("//Organization/child"); for(int i = 0; i < list.size(); i++){ node = (Element) list.get(i); String[] child = new String[5]; child[0] = node.attribute("id").getText(); child[1] = node.attribute("name").getText(); child[2] = parent[0]; child[1] = node.attribute("havechild").getText(); child[1] = node.attribute("grouplevel").getText(); data.add(child); }
} catch (Exception e) { e.printStackTrace(); }
return data; }
/** * 批量更新数据库 * * @param data */ public static boolean updateOrg(List data) { Connection conn = null;
String sql = "insert into app_group(id, name, CREATEDDATE, fatherid, havechild, grouplevel, dept_id, ismaporganization)" + " values(?,?,sysdate,?,?,?,?,1)"; String sq3=" update app_group set grouplevel=grouplevel+1 where id in (select id from app_group start with id=fatherid connect by prior fatherid=id)"; String sql2 = "insert into app_applicationgroup values(1, ?)";
ORACLE 有对LEVEL的直接支持。update app_group set grouplevel = grouplevel+1 where id in(
select id from app_group start with id=当前插入节点fatherid connect by prior fatherid=id
)删除节点比较麻烦。
对于节点层次的重新计算,Oracle有对LEVEL的直接支持。
如果是其他数据库,可能要用存储过程来处理了。
Connection conn = null;
PreparedStatement ps = null;
PreparedStatement ps2 = null;
PreparedStatement ps3 = null;
boolean result = true;
try {
conn = DBConnection.getInstance().getConnection();
String sql = "insert into app_group(id, name, CREATEDDATE, fatherid, havechild, grouplevel, dept_id, ismaporganization)" +
" values(?,?,sysdate,?,?,?,?,1)";
String sq3=" update app_group set grouplevel=grouplevel+1 where id in (select id from app_group start with id=fatherid connect by prior fatherid=id)";
String sql2 = "insert into app_applicationgroup values(1, ?)";
ps = conn.prepareStatement(sql);
ps3 = conn.prepareStatement(sq3);
ps2 = conn.prepareStatement(sql2);
conn.setAutoCommit(false);
ps.clearBatch();
ps2.clearBatch();
for(int i = 0; i < data.size(); i++) {
String[] as = (String[])data.get(i);
ps.setInt(1, Integer.parseInt(as[0]));
ps.setString(2, as[1]);
ps.setInt(3, Integer.parseInt(as[2]));
ps.setInt(4, Integer.parseInt(as[3]));
ps.setInt(5, Integer.parseInt(as[4]));
ps.setInt(6, Integer.parseInt(as[0]));
ps.addBatch();
ps2.setInt(1, Integer.parseInt(as[0]));
ps2.addBatch();
}
ps.executeBatch();
ps2.executeBatch();
conn.commit();
} catch (Exception ex) {
result = false;
ex.printStackTrace();
} finally {
DBUtil.closeStatement(ps);
DBUtil.closeStatement(ps2);
DBUtil.closeConnection(conn);
}
return result;
}
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;import com.founder.fdam.database.DBConnection;
import com.founder.fdam.database.DBUtil;/**
*
* @author caixiaojin
*
*/
public class ImportOrgUtil {
public static boolean importOrg(String file) {
//从xml中获取组织机构信息
List data = readXML(file);
//过滤掉在现有系统中已有的组织机构
data = filter(data);
//将新的组织机构导入到系统中
boolean result = updateOrg(data);
return result;
}
/**
* 读取xml信息
*
* @param file
* @return
*/
public static List readXML(String file) {
SAXReader reader = new SAXReader();
List data = new ArrayList();
try {
InputStreamReader in = new InputStreamReader(new FileInputStream(file), "UTF-8");
Document document = reader.read(in);
Element root = document.getRootElement();
//获取父信息
List list = root.selectNodes("//Organization");
String[] parent = new String[5];
Element node = (Element)list.get(0);
parent[0] = node.attribute("id").getText();
parent[1] = node.attribute("name").getText();
parent[2] = node.attribute("fid").getText();
parent[3] = node.attribute("havechild").getText();
parent[4] = node.attribute("grouplevel").getText();
data.add(parent);
//获取子信息
list = root.selectNodes("//Organization/child");
for(int i = 0; i < list.size(); i++){
node = (Element) list.get(i);
String[] child = new String[5];
child[0] = node.attribute("id").getText();
child[1] = node.attribute("name").getText();
child[2] = parent[0];
child[1] = node.attribute("havechild").getText();
child[1] = node.attribute("grouplevel").getText();
data.add(child);
}
} catch (Exception e) {
e.printStackTrace();
}
return data;
}
/**
* 批量更新数据库
*
* @param data
*/
public static boolean updateOrg(List data) {
Connection conn = null;
PreparedStatement ps = null;
PreparedStatement ps2 = null;
PreparedStatement ps3 = null;
boolean result = true;
try {
conn = DBConnection.getInstance().getConnection();
String sql = "insert into app_group(id, name, CREATEDDATE, fatherid, havechild, grouplevel, dept_id, ismaporganization)" +
" values(?,?,sysdate,?,?,?,?,1)";
String sq3=" update app_group set grouplevel=grouplevel+1 where id in (select id from app_group start with id=fatherid connect by prior fatherid=id)";
String sql2 = "insert into app_applicationgroup values(1, ?)";
ps = conn.prepareStatement(sql);
ps3 = conn.prepareStatement(sq3);
ps2 = conn.prepareStatement(sql2);
conn.setAutoCommit(false);
ps.clearBatch();
ps2.clearBatch();
for(int i = 0; i < data.size(); i++) {
String[] as = (String[])data.get(i);
ps.setInt(1, Integer.parseInt(as[0]));
ps.setString(2, as[1]);
ps.setInt(3, Integer.parseInt(as[2]));
ps.setInt(4, Integer.parseInt(as[3]));
ps.setInt(5, Integer.parseInt(as[4]));
ps.setInt(6, Integer.parseInt(as[0]));
ps.addBatch();
ps2.setInt(1, Integer.parseInt(as[0]));
ps2.addBatch();
}
ps.executeBatch();
ps2.executeBatch();
conn.commit();
} catch (Exception ex) {
result = false;
ex.printStackTrace();
} finally {
DBUtil.closeStatement(ps);
DBUtil.closeStatement(ps2);
DBUtil.closeConnection(conn);
}
return result;
}
/**
* 过滤掉已经存在的组织机构
*
* @param data
* @return
*/
public static List filter(List data) {
Connection conn = DBConnection.getInstance().getConnection();;
PreparedStatement ps = null;
ResultSet rs = null;
String strSQL = "select * from app_group where id = ?";
List newData = new ArrayList();
for(int i = 0; i < data.size(); i++) {
try {
String[] as = (String[])data.get(i);
ps = conn.prepareStatement(strSQL);
ps.setInt(1, Integer.parseInt(as[0]));
rs = ps.executeQuery();
if(!rs.next()) {//如果没有找到该组织,就返回List中
newData.add(as);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(ps);
}
}
DBUtil.closeConnection(conn);
return newData;
}
public static String getAppid(String username) {
Connection conn = DBConnection.getInstance().getConnection();;
PreparedStatement ps = null;
ResultSet rs = null;
String strSQL = "select a.appid from app_user u inner join app_applicationuser a on u.id = a.userid where u.loginname = ?";
String appids = "";
try {
ps = conn.prepareStatement(strSQL);
ps.setString(1, username);
rs = ps.executeQuery();
while(rs.next()){
appids += "["+rs.getString("appid")+"]";
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(ps);
}
DBUtil.closeConnection(conn);
return appids;
}
}