基于JDBC+AJAX前后端分离式架构SQL执行面板的实现，可批量/单条执行在网页上执行SQL语句

AJax版：
前后端分离

SQL连接、配置、执行工具类SQLExecutor详见：https://blog.youkuaiyun.com/qq_56741313/article/details/155247500

ajax servlet:

package com.kk.jdbc;

import jakarta.servlet.ServletContext;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.*;
import java.nio.charset.Charset;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@WebServlet(  name = "SQLServlet",
        value = "/asql",
        loadOnStartup = 1)
public class JDBCAjaxServlet extends HttpServlet {

    private ConnectionManager manager;


    @Override
    public void init() throws ServletException {
        super.init();
        try {
            manager = new ConnectionManager(null);
        }catch(Exception e) {
            e.printStackTrace();
        }
    }

    //service调用doget dopost方法
    @Override
    protected void service(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; charset=UTF-8");

        String sql=request.getParameter("sql");
        String transFlag = request.getParameter("useTrans");
        System.out.println("sql:"+sql);
        if(sql==null) {
            showHomePage(response);
            return;
        }
        sql = sql.trim();
        String lowerCaseSql = sql.toLowerCase();
        System.out.println(sql);
        List<String> sqls = splitSQL(sql);
        System.out.println("---------------------");
        for (String sql2 : sqls) {
            System.out.println("single:["+sql2+"]");
        }

        int len = sqls.size();

        PrintWriter writer = new PrintWriter(response.getOutputStream(),true,Charset.forName("utf-8"));
        //记录异常/正常结果的writer
        try {
            SQLExecutor exe = new SQLExecutor(manager.newConnection());
            JResult rs;
            if(lowerCaseSql.startsWith("select")){
                rs= exe.select(sql);
                System.out.println("查询成功！条数为："+rs.getRowsCount());
                showSelectResult(rs,writer,sql);
            }else {
                if(len>1){
                    int result[]=exe.batchExecution(sqls);
                    System.out.println("批量处理成功！！"+result.length);
                    showBatchResult(result,writer,sql);
                }else {
                    if(transFlag.equals("1")){
                        exe.begin();
                    }
                    int rows = 0;
                    try {
                        exe.update(sql);
                        System.out.println("执行成功！条数为："+rows);
                    }catch (SQLException e){
                        if(transFlag.equals("1")){
                            exe.cancel();
                        }
                        e.printStackTrace(writer);
                    }
                    if(transFlag.equals("1")){
                        exe.end();
                    }
                    showUpdateResult(rows,writer,sql);
                }
            }
        }catch(Exception e) {
            e.printStackTrace(writer);
        }
    }


    //展示批量执行后的结果
    private void showBatchResult(int[] result, PrintWriter out, String sql) {
        String str="";
        for (int i = 0; i < result.length; i++) {
            str=str+"语句"+(i+1)+"执行后受影响条数为："+result[i]+"<br>";
        }
        out.println(str);
        out.close();

    }

    //展示查询后的结果
    private void showSelectResult(JResult rs, PrintWriter out,String sql) throws IOException {
        String str = rs.toTableString();
        str="结果为：<br>"+str;
        out.println(str);
        out.close();
    }

    //展示正删改查后的结果
    private void showUpdateResult(int rows, PrintWriter out,String sql) throws IOException {
        String str = String.valueOf(rows);
        str="受影响的行数为："+str;
//        String msg =  htmlTemplate.replace("@result",str);
//        msg = msg.replace("@SQL",sql);

        out.println(str);
        out.close();
    }


    //展示主页面
    private void showHomePage(HttpServletResponse response) throws IOException {
        response.sendRedirect("sqAjax.html");
    }

    //分隔sql语句
    public static List<String> splitSQL(String sql){
       
}

前端Ajax展示：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>数据库操作管理面板</title>
</head>
<body>

    <!-- SQL输入区域 -->

        <h2>SQL执行</h2>

                <textarea rows="15" cols="75" id="sqlInput" name="sql"  placeholder="请输入SQL语句，例如：SELECT * FROM table1"></textarea>

            <div>
                <label for>是否启动手动事物</label>
                <input type="radio" name="useTrans" value="1" checked>是 &nbsp;&nbsp;&nbsp;&nbsp;
                <input type="radio" name="useTrans" value="0">否 &nbsp;&nbsp;&nbsp;&nbsp;
                <br>
                <button onclick="exeSQL()">执行SQL</button>  &nbsp;&nbsp;&nbsp;&nbsp;
<!--                <button onclick="clearSQL()">清空SQL</button>-->
            </div>

    <p id="demo"> </p>
<!--    <p>执行条数：@rows </p>-->
</body>
<script>
    /**
    * 获取指定名称的 radio 按钮的值
    * @param {string} name - radio 按钮的 name
    * @returns {string|null} 选中的值，未选中返回 null
    */
    function getRadioValue(name) {
        const radio = document.querySelector(`input[name="${name}"]:checked`);
        return radio ? radio.value : null;
    }


    function showResult(xmlhttp) {
        var id = document.getElementById("demo");
        id.innerHTML=xmlhttp.responseText;
    }

    function exeSQL() {
        // debugger
        var sql = document.getElementById("sqlInput").value;
        const trans = getRadioValue("useTrans");
        var data = "sql=" + encodeURIComponent(sql) + "&useTrans=" + trans;
        var xhttp = new XMLHttpRequest();

        xhttp.onreadystatechange = function () {
            if (this.readyState == 4 && this.status == 200) {
                showResult(this);
            }
        }
        xhttp.open("POST", "asql", false);
        xhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
        xhttp.send(data);
    }

</script>
</html>

1、xhttp.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”)——需要加在xhttp.open之后 不加这句话在html页面点击执行sql页面后无响应的BUG
2、 var data = “sql=” + encodeURIComponent(sql) + “&useTrans=” + trans; 参数名sql、useTrans要写对，和后端不一致也会报错，传输不了数据
3、 xhttp.open(“POST”, “asql”, false); 踩坑：(“POST”, “/asql”, false); asql前面加了斜杠/之后会返回到根url中从而从目标:demo/asql转为 /asql

通过js来更改页面的显示内容：更改

中的显示内容==>改为person对象转换之后的字符串：

<p id="obj"> </p>

function testJS(){
			const person = {
  			name: "张三",
 			 age: 28,
 			 isStudent: false
			}; //定义一个person对象
		const jsonString = JSON.stringify(person); //将该对象转换为字符串
		var obj = document.getElementById("obj");//通过getElementById()来获取页面要更改的地方
		obj.innerHTML=jsonString; //改变对应的内容
}