CODEWITHSUNDEEP
javamysqljsp
USERRR5
USERRR5

Reputation: 430

write a sql query in one jsp page and execute it using another jsp page

I have two jsp pages one is connector.jsp and another is select.jsp.i have created database connection and tried to make an environment to run any query in my connector.jsp. I have written the main query in select.jsp .I want to pass the query toconnector.jsp as a string .i want to execute it only in connector.jsp .but i am not getting any idea how should i solve it.Please help me to solve my problem here is my code
connector.jsp

<%@page import="java.util.HashMap"%>
<%@page import="java.sql.ResultSetMetaData"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.util.Map"%>
<%@page import="java.util.ArrayList"%>
<%@page import="java.util.List"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%!
Connection connection = null;
Statement statement = null;
%>
<% Class.forName("org.postgresql.Driver"); %>

<%
    Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres", "root");
statement = connection.createStatement();
out.println("Database Connected Successfully.");

%>

<%!
    List select(String query) {
    List rows = new ArrayList();
    Map row = null;

    try {
        ResultSet resultSet = statement.executeQuery(query);
        ResultSetMetaData metaData = resultSet.getMetaData();
        int numColumns = metaData.getColumnCount();

        while (resultSet.next()) {
            row = new HashMap();
            for (int i = 1; i < numColumns + 1; i++) {
                row.put(metaData.getColumnName(i), resultSet.getObject(i));
            }
            rows.add(row);
        }

        resultSet.close();
    } catch (Exception e) {

    }

    return rows;
}

%>​

here is my another page where i wrote my sql main query

selectquery.jsp

    <%@page import="java.util.List"%>
     <%@page import=" com.google.gson.Gson"%>
    <%@page contentType="text/html" pageEncoding="UTF-8"%>
     <jsp:include page="connector.jsp" />
    <%
        String query =("select distinct eid,ename,esalary from testemployee order by eid");

        Gson gson = new Gson();
        String json = gson.toJson(rows);
        out.print(json);
    %>

but it is showing error.

Upvotes: 1

Views: 9454

Answers (3)

muasif80
muasif80

Reputation: 6006

You must not write this database related code in the JSP. Its bad practice. Rather it should be in a managed bean or some other class that you use as a bean in the JSP page.

Upvotes: 1

Nisheeth Shah
Nisheeth Shah

Reputation: 608

When you use <%! .. %>, it indicates that you are writing your code out of the service method. You should include your entire code into the method as below.

    <%!
        public List select(String query) throws Exception{
            Connection connection = DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "postgres", "root");
            Statement statement = connection.createStatement();
            out.println("Database Connected Successfully.");
            List rows = new ArrayList();
            Map row = null;
            ResultSet resultSet = statement.executeQuery(query);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int numColumns = metaData.getColumnCount();
            while (resultSet.next()) {
                row = new HashMap();
                for (int i = 1; i < numColumns + 1; i++) {
                    row.put(metaData.getColumnName(i), resultSet.getObject(i));
                }
                rows.add(row);
            }
            resultSet.close();
            connection.close();
        }
    %>

Include this jsp using <@include file="connector.jsp">.

Well, it is not appropriate to use JSP for connection. You should create a class and declare connection utility methods init.

Upvotes: 0

rahimv
rahimv

Reputation: 581

You need to use <@include....> if you want to access variables defined in the parent page from included page instead of jsp:include.

Define your variable (i.e. query in this case) BEFORE you include the page.

Upvotes: 0

Related Questions