servlet连接数据库并查询/返回json格式数据

好久没这么写查询，写了几行忘词了,so再记录一下
doget,dopost别反了

基本查询

package tt.webhomework;

import javax.servlet.*;


import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

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 java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

@WebServlet(name = "Servlet2", value = "/Servlet2")
public class Servlet2 extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

       // response.getWriter().write("aaa");
       // response.getWriter().write("hello");
        // 设置相应内容类型
        response.setContentType("text/json;charset=utf-8");
        // 实例化输出流对象
        PrintWriter out = response.getWriter();
        //连接数据库，获取数据
        try {

            Class.forName("com.mysql.jdbc.Driver"); //(加载)获取jdbc驱动jar包里的类
            Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/web?serverTimezone=UTC","root","root");
            // 创建 Statement对象
            Statement stmt = connect.createStatement();
            String sql;
            sql  = "select * from college"; // 设置sql语句
            // 执行sql语句,获取查询结果集,
            ResultSet rs = stmt.executeQuery(sql);
            // 这里需要导入 json jar 包
          //  JSONArray jsonArray = new JSONArray();
         //   JSONObject jsonObj = new JSONObject();
            // 展开结果集
            while (rs.next()){
                //通过字段检索
                // 设置key 和 value put到json对象
                out.println(rs.getString("major"));
             //   jsonObj.put("major",rs.getString("major"));
                //       jsonObj.put("url",rs.getString("url"));

              //  jsonArray.add(jsonObj); // 把对象放到数组里，容易遍历
            }
           // out.println(jsonArray);
            //关闭
            rs.close();//结果集大门关闭
            stmt.close(); // 对话关闭
            connect.close(); //连接关闭
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }
    }

返回json数据

注意：需要引入如下依赖，并且必须手动复制到tomcat的lib文件夹下

package tt.webhomework;

import javax.servlet.*;


import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

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 java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;

@WebServlet(name = "Servlet2", value = "/Servlet2")
public class Servlet2 extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

       // response.getWriter().write("aaa");
       // response.getWriter().write("hello");
        // 设置相应内容类型
        response.setContentType("text/json;charset=utf-8");
        // 实例化输出流对象
        PrintWriter out = response.getWriter();
        //连接数据库，获取数据
        try {

            Class.forName("com.mysql.jdbc.Driver"); //(加载)获取jdbc驱动jar包里的类
            Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/web?serverTimezone=UTC","root","root");
            // 创建 Statement对象
            Statement stmt = connect.createStatement();
            String sql;
            sql  = "select * from college"; // 设置sql语句
            // 执行sql语句,获取查询结果集,
            ResultSet rs = stmt.executeQuery(sql);
            // 这里需要导入 json jar 包
           JSONArray jsonArray = new JSONArray();
           JSONObject jsonObj = new JSONObject();
            // 展开结果集
            while (rs.next()){
                //通过字段检索
             //   设置key 和 value put到json对象
               // out.println(rs.getString("major"));
              jsonObj.put("major",rs.getString("major"));
                      jsonObj.put("colle",rs.getString("colle"));

               jsonArray.add(jsonObj); // 把对象放到数组里，容易遍历
            }
            out.println(jsonArray);
            //关闭
            rs.close();//结果集大门关闭
            stmt.close(); // 对话关闭
            connect.close(); //连接关闭
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }
    }