✔ JDBC-Problem: Vertauschung in den abgefragten Tabellen

[hubertb]

Hallo!

Ich habe ein Servlet und ein JSP, mit welchen ich auf eine PostgreSQL zugreife. Ich habe mit

create table fuckyou (person_id int, name varchar(10), prename varchar(10))

eine Tabelle angelegt und mit

insert into fuckyou (person_id, name, prename) values (666, 'Simpson', 'Homer')

einen Datensatz eingefügt. Wenn ich mir jetzt über

select * from fuckyou

den Inhalt ausgeben lasse, dann sieht das aber so aus:

person_id  	name  	prename
-------------------------------
Simpson 	666 	Homer

Ich vermute den Fehler irgendwo in der JSP oder im Servlet.

Das Servlet sieht so aus:

package jdbcservlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.jsp.jstl.sql.Result;
import javax.servlet.jsp.jstl.sql.ResultSupport;

/**
 * Servlet implementation class for Servlet: JDBCServlet
 * 
 */
public class JDBCServlet extends javax.servlet.http.HttpServlet implements
		javax.servlet.Servlet {
	// Hält die Datenbankverbindung
	protected Connection myConnection = null;

	// Datenbank-Metadaten
	protected DatabaseMetaData myDataBaseMetaData = null;

	@Override
	public void destroy() {
		// TODO Auto-generated method stub
		try {
			if (myConnection != null) {
				myConnection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			log("Datenbankverbindung '" + myConnection.toString()
					+ "' geschlossen");
		}
		super.destroy();
	}

	@Override
	public void init() throws ServletException {
		// TODO Auto-generated method stub
		super.init();
		String url = getInitParameter("url");
		String driverClass = getInitParameter("driverClass");
		String user = getInitParameter("user");
		String password = getInitParameter("password");
		if (url == null || driverClass == null || user == null
				|| password == null) {
			throw new ServletException("Fehlende Parameter\nURL: " + url
					+ "\nDriverClass: " + driverClass + "\nUser: " + user
					+ "\nPassword: " + password);
		}
		try {
			// Treiber laden
			Class.forName(driverClass);
			// Verbindung aufbauen
			myConnection = DriverManager.getConnection(url, user, password);
			// Metadaten holen
			myDataBaseMetaData = myConnection.getMetaData();
		} catch (Exception e) {
			// TODO: handle exception
			throw new ServletException(e.toString());
		}
	}

	/*
	 * (non-Java-doc)
	 * 
	 * @see javax.servlet.http.HttpServlet#HttpServlet()
	 */
	public JDBCServlet() {
		super();
	}

	/*
	 * (non-Java-doc)
	 * 
	 * @see javax.servlet.http.HttpServlet#doGet(HttpServletRequest request,
	 *      HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		getServletContext().getRequestDispatcher("/JDBC.jsp").forward(request,
				response);
	}

	/*
	 * (non-Java-doc)
	 * 
	 * @see javax.servlet.http.HttpServlet#doPost(HttpServletRequest request,
	 *      HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		String query = request.getParameter("query");

		Result myResult;
		try {
			myResult = executeSQL(query);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new ServletException(e.toString());
		}

		if (myResult != null) {
			// Result myResult = (Result) request.getSession().getAttribute(
			// "queryResult");

			// Ergebnis der Anfrage an die Sessionen Context binden...
			request.getSession().setAttribute("queryResult", myResult);
		} else {
			// myResult aus dem Context nehmen
			request.getSession().removeAttribute("queryResult");
		}

		// ...und zur Formatierung und Darstellung an das JSP weiterleiten
		getServletContext().getRequestDispatcher("/JDBC.jsp").forward(request,
				response);
	}

	protected Result executeSQL(String query) throws SQLException {
		Statement myStatement = null;
		ResultSet myResultSet = null;
		Result myResult = null;

		// Führende und anhängende Leerzeichen entfernen
		query = query.trim();

		// Anfragestring zu kleinbuchstaben konvertieren (erleichtert vergleich)
		String lowerQuery = query.toLowerCase();

		myStatement = myConnection.createStatement();

		if (lowerQuery.startsWith("select")) {
			myResultSet = myStatement.executeQuery(query);
			
			// Before reading any values, you must call next(). This returns
			// true if there is a result, but more importantly, it prepares the
			// row for processing.
			myResultSet.next();
		} else if (lowerQuery.startsWith("insert")
				|| lowerQuery.startsWith("delete")
				|| lowerQuery.startsWith("update")) {
			myStatement.executeUpdate(query);
		} else {
			myStatement.execute(query);
		}

		if (myResultSet != null) {
			myResult = ResultSupport.toResult(myResultSet);
			myResultSet.close();
		}

		return myResult;
	}
}

Das JSP:

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JDBC-JSP/Servlet</title>
</head>
<body>
<form method="post" action="/JDBCServlet/JDBCServlet">Query: <input
	type=text size=30 name="query"> <input type=submit value="Go"></form>
<br>
<c:if test="${queryResult!=null}">
	<%-- Tabelle mit Hilfe der JSTL formatieren und ausgeben --%>
	<table border=1 cellspacing=0 cellpadding=3>
		<c:forEach var="columnName" items="${queryResult.columnNames}">
			<th><c:out value="${columnName}" /></th>
		</c:forEach>
		<c:forEach var="row" items="${queryResult.rows}">
			<tr>
				<c:forEach var="column" items="${row}">
					<td><c:out value="${column.value}" /></td>
				</c:forEach>
			</tr>
		</c:forEach>
	</table>
</c:if>
</body>
</html>

Was mache ich nur falsch? Ich hab deswegen schon kaum noch Haare aufm Kopf :-((

 

[hubertb]

Es lag am JSP. So funktionierts:

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JDBC-JSP/Servlet</title>
</head>
<body>
<h2>JDBC-JSP/Servlet</h2>
<form method="post" action="/JDBCServlet/JDBCServlet">Query: <input
	type=text size=30 name="query"> <input type=submit value="Go"></form>
<br>
<c:if test="${queryResult!=null}">
	<hr>
	<br>
	<%-- Tabelle mit Hilfe der JSTL formatieren und ausgeben --%>
	<table border=1 cellspacing=0 cellpadding=3>
		<c:forEach var="columnName" items="${queryResult.columnNames}">
			<th><c:out value="${columnName}" /></th>
		</c:forEach>
		<c:forEach var="row" items="${queryResult.rowsByIndex}">
			<tr>
				<c:forEach var="column" items="${row}">
					<td><c:out value="${column}" /></td>
				</c:forEach>
			</tr>
		</c:forEach>
	</table>
</c:if>
</body>
</html>