package helloworld;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;

import org.json.JSONException;

import org.json.JSONObject;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

/**

 */

@WebServlet("/HelloWorld")

public class HelloWorld extends HttpServlet {

    private static final long serialVersionUID = 1L;

    private String diverClass;

    private String userName;

    private String password;

    private String url;

    /**

     * Default constructor.

     */

    public HelloWorld() {

        // TODO Auto-generated constructor stub

    }

    public String resultSetToJson(ResultSet rs) throws SQLException,JSONException

    {

       // json数组

       JSONArray array = new JSONArray();  

       // 获取列数

       ResultSetMetaData metaData = rs.getMetaData();

       int columnCount = metaData.getColumnCount();  

       // 遍历ResultSet中的每条数据

        while (rs.next()) {

            JSONObject jsonObj = new JSONObject();  

            // 遍历每一列

            for (int i = 1; i <= columnCount; i++) {

                String columnName =metaData.getColumnLabel(i);

                String value = rs.getString(columnName);

                jsonObj.put(columnName, value);

            }

            array.put(jsonObj);

        }  

       return array.toString();

    }  

    /**

     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

     */

    @Override

    protected void doGet(HttpServletRequest request,

            HttpServletResponse response) throws ServletException, IOException {

        Connection conn = null;

        Statement stmt = null;

        ResultSet rs = null;

        response.setContentType("text/html");

        response.setCharacterEncoding("gb2312");

        PrintWriter out = response.getWriter();

        diverClass = /* getServletConfig(). */getServletContext().getInitParameter("driver");

           userName = /* getServletConfig(). */getServletContext().getInitParameter("username");

           password = /* getServletConfig(). */getServletContext().getInitParameter("password");

           url = /* getServletConfig(). */getServletContext().getInitParameter("url");

        String sql = request.getParameter("sql");

        if (sql==null){

            sql="SELECT * FROM ANIMALS";

        }

        try {

            Class.forName(diverClass);

            //conn = DriverManager.getConnection("jdbc:mysql://localhost/bbs?user=root&password=12345678");

            conn = DriverManager.getConnection(url, userName,password);

            stmt = conn.createStatement();

            rs = stmt.executeQuery(sql); 

            HelloWorld hello = new HelloWorld();

            try {

                String data = hello.resultSetToJson(rs);

                out.println(data);

            } catch (JSONException e) {

                // TODO Auto-generated catch block

                e.printStackTrace();

            }

        } catch (ClassNotFoundException e) {

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            try {

                if(rs != null) {

                    rs.close();

                    rs = null;

                }

                if(stmt != null) {

                    stmt.close();

                    stmt= null;

                }

                if(conn != null) {

                    conn.close();

                    conn = null;

                }

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

    /**

     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

     */

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

         PrintWriter out = response.getWriter();

         out.println("<div style='width:100px;height:100px;background-color:red'>123123</div>");

    }

}