| SSISO Community검색 |
|
|
| SSISO Community메뉴 |
|
|
| SSISO Community카페 |
|
|
| 블로그 카테고리 |
|
|
|
| struts로 검색한 결과 |
 |
등록일:2008-11-10 20:07:48
작성자:
제목:DB를 XML로 변환, DBtoXML.java |
|
[01] DB를 XML로 변환
1. 테이블 구조
-- 사무실 임대료 등록 테이블
DROP TABLE office CASCADE CONSTRAINTS PURGE ;
CREATE TABLE office(
officenum INT NOT NULL, -- 일련 번호
company VARCHAR2(50) NOT NULL, -- 회사명
area NUMBER(3) DEFAULT 0 NOT NULL, -- 사무실 평수
rent NUMBER(10) DEFAULT 0 NOT NULL, -- 임대료
PRIMARY KEY(officenum)
);
CREATE SEQUENCE office_officenum
START WITH 1 --시작번호a
INCREMENT BY 1 --증가값
MAXVALUE 99999 --최대값
CACHE 2 --시쿼스 변경시 자주 update되는 것을 방지하기위한 캐시값
NOCYCLE
INSERT INTO office(officenum, company, area, rent)
VALUES(office_officenum.nextval, '(주)자바', 200, 10000000);
INSERT INTO office(officenum, company, area, rent)
VALUES(office_officenum.nextval, '(주)JSP', 200, 10000000);
INSERT INTO office(officenum, company, area, rent)
VALUES(office_officenum.nextval, '(주)EJB', 200, 10000000);
INSERT INTO office(officenum, company, area, rent)
VALUES(office_officenum.nextval, 'MVC', 100, 500000);
INSERT INTO office(officenum, company, area, rent)
VALUES(office_officenum.nextval, 'struts', 100, 600000);
INSERT INTO office(officenum, company, area, rent)
VALUES(office_officenum.nextval, 'DBCP', 100, 700000);
SELECT officenum, company, area, rent FROM office;
COMMIT;
2. XML 데이터 파일
>>>>> WebContent/xml/office/office.xml
<?xml version="1.0" encoding="euc-kr"?>
<OfficeList>
</OfficeList>
3. DTO
>>>>> OfficeDTO.java
package www.xml;
public class OfficeDTO {
private int officenum;
private String company;
private int area;
private int rent;
public int getArea() {
return area;
}
public void setArea(int area) {
this.area = area;
}
public String getCompany() {
return company;
}
public void setCompany(String company) {
this.company = company;
}
public int getOfficenum() {
return officenum;
}
public void setOfficenum(int officenum) {
this.officenum = officenum;
}
public int getRent() {
return rent;
}
public void setRent(int rent) {
this.rent = rent;
}
}
3. Manager Class
>>>>> DBtoXML.java
package www.xml;
import java.io.File;
import java.io.FileWriter;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.Vector;
import org.jdom.Document;
import org.jdom.Element;
import org.jdom.input.SAXBuilder;
import org.jdom.output.Format;
import org.jdom.output.XMLOutputter;
import www.utility.ConnectionPoolMgr;
import www.utility.DBClose;
// DB --> XML
public class DBtoXML {
/** Connection Pool */
private ConnectionPoolMgr dbconnect=null;
private String xml_dir;
private String xml_name;
SAXBuilder builder;
Document doc;
Element root;
/**
* 생성자, Document 객체 생성
*
*/
public DBtoXML(){
xml_dir ="F:/200708_ojt1330/eclipse/workspace/www_pilot/WebContent/xml/office/";
xml_name= newXML(); // 새로운 XML 파일 생성
try {
builder = new SAXBuilder();
doc = builder.build(new File(xml_dir, xml_name));
dbconnect = new ConnectionPoolMgr(); //접속 개체 생성
}catch(Exception ex) {
ex.printStackTrace();
}
}
public DBtoXML(int DBCPMode){
xml_dir ="F:/200708_ojt1330/eclipse/workspace/www_pilot/WebContent/xml/office/";
xml_name= newXML(); // 새로운 XML 파일 생성
try {
builder = new SAXBuilder();
doc = builder.build(new File(xml_dir, xml_name));
dbconnect = new ConnectionPoolMgr(DBCPMode); //접속 개체 생성
}catch(Exception ex) {
ex.printStackTrace();
}
}
/**
* 새로운 XML파일을 생성합니다.
* @return XML 파일명
*/
private String newXML(){
Calendar cal = Calendar.getInstance();
StringBuffer sb = new StringBuffer();
sb.append("office_");
sb.append("Y"+cal.get(Calendar.YEAR));
sb.append("M"+(cal.get(Calendar.MONTH)+1));
sb.append("D"+cal.get(Calendar.DATE));
sb.append("_");
sb.append("H"+cal.get(Calendar.HOUR));
sb.append("M"+cal.get(Calendar.MINUTE));
sb.append("S"+cal.get(Calendar.SECOND));
sb.append(".xml");
String str = sb.toString();
try{
File file = new File(xml_dir, str);
PrintWriter pw = new PrintWriter(new FileWriter(file));
pw.write("<?xml version=\"1.0\" encoding=\"euc-kr\"?>");
pw.write("<officeList>");
pw.write("");
pw.write("</officeList>");
pw.close();
}catch(Exception e){
e.printStackTrace();
}
return sb.toString();
}
/**
* XML 파일에 기록
* OfficeList 태그의 자식 태그로 저장
* <officeList>
* <office>
* <officenum></officenum>
* <company></company>
* <area></area>
* <rent></rent>
* </office>
* </officeList>
*/
private boolean addElement(Object dto){
// Object 타입을 특정 DTO 타입으로 변환
OfficeDTO officeDTO = (OfficeDTO)dto;
root = doc.getRootElement(); // <officeList>
//새로운 office 태그를 생성합니다.
Element newOffice = new Element("office");
//새로운 태그를 생성합니다.
Element officenum = new Element("officenum");
//값을 저장합니다.
officenum.setText(Integer.toString(officeDTO.getOfficenum()));
//새로 생성된 office태그에 저장합니다.
newOffice.addContent(officenum);
Element company = new Element("company");
company.setText(officeDTO.getCompany());
newOffice.addContent(company);
Element area = new Element("area");
area.setText(Integer.toString(officeDTO.getArea()));
newOffice.addContent(area);
Element rent = new Element("rent");
rent.setText(Integer.toString(officeDTO.getRent()));
newOffice.addContent(rent);
//완성된 Address태그를 root태그에 저장합니다.
root.addContent(newOffice);
//인수1: 하위태그가 있을시 들여쓸 여백을 지정
//인수2: 하나의 태그 출력후 라인 변경여부 true는 라인 변경
//인수3: 문자셋 지정
//XMLOutputter outputter = new XMLOutputter(" ", true, "euc-kr");
XMLOutputter outputter = new XMLOutputter(Format.getPrettyFormat().setEncoding("euc-kr"));
try{
FileWriter writer = new FileWriter(xml_dir + xml_name);
outputter.output(doc, writer);
writer.close();
}catch(Exception ex){
ex.printStackTrace();
return false;
}
return true;
}
/**
* 레코드를 DTO 객체로 변환하여 Vector에 저장하여 리턴
*/
private Vector DBtoObject(){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Vector dataList = new Vector(); //Vector 정의
StringBuffer sql = new StringBuffer();
try{
con = dbconnect.getConnection();
sql.append(" SELECT officenum, company, area, rent");
sql.append(" FROM office ");
sql.append(" ORDER BY company ASC");
// sql.append(" SELECT officenum, company, area, rent");
// sql.append(" FROM office ");
// sql.append(" WHERE wdate LIKE '2007%'");
// sql.append(" ORDER BY company ASC");
// sql.append(" SELECT officenum, company, area, rent");
// sql.append(" FROM office ");
// sql.append(" WHERE wdate LIKE '2007-07%'");
// sql.append(" ORDER BY company ASC");
// sql.append(" SELECT officenum, company, area, rent");
// sql.append(" FROM office ");
// sql.append(" WHERE wdate LIKE '%2007-0722%'");
// sql.append(" ORDER BY company ASC");
pstmt = con.prepareStatement(sql.toString());
rs = pstmt.executeQuery();
while ( rs.next()){
OfficeDTO officeDTO = new OfficeDTO();
officeDTO.setOfficenum(rs.getInt("officenum"));
officeDTO.setCompany(rs.getString("company"));
officeDTO.setArea(rs.getInt("area"));
officeDTO.setRent(rs.getInt("rent"));
dataList.add(officeDTO);
}
}catch(Exception e){
System.out.println(e);
}finally{
DBClose.close(con, pstmt, rs);
}
return dataList;
}
/**
* 레코드를 DTO 객체로 변환하여 Vector에 저장하여 리턴
*/
private Vector DBtoObject(StringBuffer sql){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
Vector dataList = new Vector(); //Vector 정의
try{
con = dbconnect.getConnection();
pstmt = con.prepareStatement(sql.toString());
rs = pstmt.executeQuery();
while ( rs.next()){
OfficeDTO officeDTO = new OfficeDTO();
officeDTO.setOfficenum(rs.getInt("officenum"));
officeDTO.setCompany(rs.getString("company"));
officeDTO.setArea(rs.getInt("area"));
officeDTO.setRent(rs.getInt("rent"));
dataList.add(officeDTO);
}
}catch(Exception e){
System.out.println(e);
}finally{
DBClose.close(con, pstmt, rs);
}
return dataList;
}
// DB를 XML로 변환, DB ---> DTO ---> Vector ---> XML
public void convertDBtoXML(){
// DB에서 레코드 추출,
Vector list = DBtoObject();
//XML 출력
for (int i=0; i<list.size(); i++){
addElement(list.get(i));
}
System.out.println("DB를 XML로 변환 했습니다.");
}
// DB를 XML로 변환, DB ---> DTO ---> Vector ---> XML
public void convertDBtoXML(StringBuffer sql){
// DB에서 레코드 추출,
Vector list = DBtoObject(sql);
//XML 출력
for (int i=0; i<list.size(); i++){
addElement(list.get(i));
}
System.out.println("DB를 XML로 변환 했습니다.");
}
}
4. Test
>>>>> DBtoXML_convertDBtoXML.java
package test.www.xml;
import www.xml.DBtoXML;
public class DBtoXML_Test {
public static void main(String[] args) {
DBtoXML mgr = new DBtoXML(1);
// mgr.convertDBtoXML();
StringBuffer sql = new StringBuffer();
sql.append(" SELECT officenum, company, area, rent");
sql.append(" FROM office ");
sql.append(" WHERE company LIKE '%자바%'");
sql.append(" ORDER BY company ASC");
mgr.convertDBtoXML(sql);
}
}
[출처] [23-D12][XML] DB를 XML로 변환, DBtoXML.java|작성자 푸우하하
http://blog.naver.com/poohhahahaha/10024310959
|
|
|
|
| |
|
