package ru.ts.ehabarov.test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.naming.InitialContext;
import javax.servlet.Servlet;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

/**
 * Servlet implementation class UploadInsert
 */
public class SelectXML extends HttpServlet 
{
	private static final long serialVersionUID = 1L;
	private static final String dsName = "java:comp/env/jdbc/SPRAVKIN";
	private static final String sqlSelXml = "SELECT XML_DATA FROM SPRAVKIN.DOC_ARH WHERE ID_SP_DOC=?";
	private DataSource ds = null;
	       
	/**
	 * @see Servlet#init(ServletConfig)
	 */
	public void init(ServletConfig config) throws ServletException 
	{
		super.init(config);
		// Инициализация ссылки на пул соединений
		try
		{
			InitialContext ic = new InitialContext();
			ds = (DataSource) ic.lookup(dsName);
			ic.close();

		}
		catch (Exception e) 
		{
			throw new ServletException("Error during resource lookup", e);
		}
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String xmlString = null;
		try
		{
			con = ds.getConnection();
			pstmt = con.prepareStatement(sqlSelXml);
			pstmt.setInt( 1 , 123 );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				xmlString = rs.getString( 1 );
			}
			pstmt.close();
			con.commit();
			con.close();
		} 
		catch (Exception e) 
		{
			try { pstmt.close(); } catch (Exception e1) {}
			try { con.rollback(); } catch (Exception e1) {}
			try { con.close(); } catch (Exception e1) {}
			throw new ServletException(e);
		}
		if (xmlString == null) throw new ServletException("XML Content is NULL");
		response.setContentType("text/xml");
		response.setCharacterEncoding("utf-8");
		response.getWriter().write(xmlString);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{
		doGet(request, response);
	}
	
	@Override
	public void destroy() 
	{
		super.destroy();
	}

	
}

create table spravkin.doc_arh(id_sp_doc integer, XML_DATA CLOB(1M))