mysql—如何创建具有动态表名的存储过程,并从另一个具有特定日期范围的表中选择内容?使用java时它不起作用

jtjikinw  于 2021-06-17  发布在  Mysql
关注(0)|答案(1)|浏览(247)

当我尝试用纯sql命令执行时,它就工作了,但当我尝试用java执行时,它就不工作了。只创建一个没有数据的空白表?

DELIMITER $$
        CREATE PROCEDURE `create_tb_tw`(IN tableName varchar(255),IN x varchar(255),IN y varchar(255))
        BEGIN
        SET @sql = CONCAT('CREATE TABLE ',tableName,' SELECT * FROM emp
         WHERE hiredate >= ',x,'
         AND hiredate <= ',y);
                PREPARE s FROM @sql;
                EXECUTE s;
                DEALLOCATE PREPARE s;
        END $$
        DELIMITER;

        call create_tb_tw("x","'1980-12-17'","'1981-02-22'");//working

    public class emdetails extends HttpServlet {

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            response.setContentType("text/html;charset=UTF-8");
            try (PrintWriter out = response.getWriter()) {

                //db connection

                Connection con = DbConnection.getcon();
                String Sdate = request.getParameter("Sdate");
                String Edate = request.getParameter("Edate");
                String TName = request.getParameter("TName");
                try {
                    CallableStatement cStmt = con.prepareCall("{call create_tb_tw(?, ?,?)}");
                    cStmt.setString(1, TName);
                    cStmt.setString(2, Sdate);  
                    cStmt.setString(3, Edate);
                    cStmt.executeQuery();

                    }
                } catch (SQLException ex) {
    //                System.out.println(emdetails.class.getName()).log(Level.SEVERE, null, ex);
                    Logger.getLogger(emdetails.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
        }

        // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
        /**
         * Handles the HTTP <code>GET</code> method.
         *
         * @param request servlet request
         * @param response servlet response
         * @throws ServletException if a servlet-specific error occurs
         * @throws IOException if an I/O error occurs
         */
        @Override
        protected void doGet(HttpServletReq`enter code heenter code herere`uest request, HttpServletResponse response)
                throws ServletException, IOException {
            processRequest(request, response);
        }

        /**
         * Handles the HTTP <code>POST</code> method.
         *
         * @param request servlet request
         * @param response servlet response
         * @throws ServletException if a servlet-specific error occurs
         * @throws IOException if an I/O error occurs
         */
        @Override
        protected void doPost(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            processRequest(request, response);
        }

        /**
         * Returns a short description of the servlet.
         *
         * @return a String containing servlet description
         */
        @Override
        public String getServletInfo() {
            return "Short description";
        }// </editor-fold>

    }

当我尝试用纯sql命令执行时,它就工作了,但当我尝试用java执行时,它就不工作了。只创建一个没有数据的空白表?

vwoqyblh

vwoqyblh1#

你需要引用日期

call create_tb_tw("t","'2014-01-01'","'2017-12-31'");

相关问题