sqlserverdatabasedao.java

本人写的另一个XML实例化小程序

package advdb.dao;

import java.sql.*;
import advdb.*;
import java.util.*;

public class SQLServerDatabaseDAO 
{
	private final static String SQL_CREATE_TABLE =
		"CREATE TABLE {$tname} ("
				+ "[Id] [int] NOT NULL ,"
				+ "[ParentId] [int] NOT NULL ,"
				+ "[Order] [int] NULL ,"
				+ "[Name] [varchar] (50) NOT NULL ,"
				+ "[Value] [varchar] (1024) NULL ,"
				+ "[Type] [int] NOT NULL" 
			+ ")";
	private final static String SQL_INSERT_NODE =
		"insert {$tname} ("
				+ "[id], " 
				+ "parentid, "
				+ "[order], " 
				+ "[name], " 
				+ "value, "
				+ "type"
				+") " 
		+ "values (?, ?, ?, ?, ?, ?)";
	private final static String SQL_LOAD_NODES =
		"select "
				+ "[id],"
				+ "[name],"
				+ "parentid,"
				+ "[order],"
				+ "[value],"
				+ "[type] "
		+ "from {$tname}";

	private Configurator cfg;
	private Connection conn;
	
	public SQLServerDatabaseDAO() throws Exception
	{
		cfg = Configurator.getInstance();
		conn = cfg.getConnection();
	}
	
	public void buildTable(String name) throws Exception 
	{
		String sql = SQL_CREATE_TABLE.replace("{$tname}", name);
		Statement stmt = null;
		
		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
		}catch(Exception ex) {
			throw ex;
		}finally{
			if (stmt!=null)try{stmt.close();}catch(Exception e){};
		}
	}

	public void saveXMLNode(String tname, Node node) throws Exception
	{
		PreparedStatement ps = null;
		String sql = SQL_INSERT_NODE.replace("{$tname}", tname);;
		
		try {
			ps = conn.prepareStatement(sql);
			
			ps.setInt(1, node.getId());
			ps.setInt(2, node.getParentId());
			ps.setInt(3, node.getOrder());
			ps.setString(4, node.getName());
			ps.setString(5, node.getValue());
			ps.setInt(6, node.getType());
			
			ps.execute();
		}catch(Exception ex) {
			throw ex;
		}finally{
			if (ps!=null)try{ps.close();}catch(Exception e){};
		}
	}
	
	public HashMap<Integer, Node> loadXMLNodes(String tname) throws Exception
	{
		String sql = SQL_LOAD_NODES.replace("{$tname}", tname);;
		HashMap<Integer, Node> hash = new HashMap<Integer, Node>();
		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);

			while(rs.next()) {
				int id = rs.getInt(1);
				String name = rs.getString(2);
				int pid = rs.getInt(3);
				int order = rs.getInt(4);
				String value = rs.getString(5);
				int type = rs.getInt(6);
				
				Node n = (id==Node.NodeTypeElement
						?new XMLNode()
						:new XMLAttribute());
				n.setId(id);
				n.setName(name);
				n.setParentId(pid);
				n.setOrder(order);
				n.setValue(value);
				n.setType(type);

				hash.put(new Integer(id), n);
			}
		}catch(Exception ex) {
			throw ex;
		}finally{
			if (rs!=null)try{rs.close();}catch(Exception e){};
			if (stmt!=null)try{stmt.close();}catch(Exception e){};
		}
		
		return hash;
	}
}