JSP SQL Adhoc DataGrid
Quick and Dirty
November 12, 2018One of my "Hello World" web pages I always do when beginning a project is a quick and dirty datagrid. Today's language of choice is JSP (Java Server Pages) and the back-end database is MySQL.
Here's what the final output looks like:
There are lot's of things we could add like clickable headers that sort the grid, edit links on each row, drill downs, row numbering and counts, etc. But I called this Quick and Dirty for a reason! Sheesh give a guy a break. I'll share one of my "fully functional" data grids later.
Code Please:
<%@page import= "java.sql.Connection"%>
<%@page import= "java.sql.Statement"%>
<%@page import= "java.sql.ResultSet"%>
<%@page import= "java.sql.ResultSetMetaData"%>
<%@page import= "java.sql.DriverManager"%>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title></title>
<link rel="stylesheet" href="normalize.css">
<link rel="stylesheet" href="skeleton.css">
<style>
#SQLAdhocBox {width: 75%; height: 300px; margin-left: 12.5%; margin-top: 2%; margin-bottom: 2%;}
#SQLAdhocText {display: block; width: 100%; height: 100%; }
</style>
</head>
<body>
<%
String query=request.getParameter("SQLAdhocText");
if (query=="") {query = "select * from users";}
%>
<div id="SQLAdhocBox">
<form id="SQLAdhocForm" action="#">
SQL Statement: <br />
<textarea id="SQLAdhocText" name="SQLAdhocText"><%=query %></textarea>
<input type="submit" />
</form>
</div>
<div id="container">
<table id="SQLAdhocTable">
<thead><tr>
<%
try
{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://1.22.333.4444:3306/testdb";
String username="tester";
String password="retset";
Connection conn=DriverManager.getConnection(url,username,password);
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery(query);
ResultSetMetaData metadata = rs.getMetaData();
int columnCount = metadata.getColumnCount();
// Loop for all the columns and create column headers
for (int i = 1; i <= columnCount; i++) {
%>
<th><%=metadata.getColumnName(i)%></th>
<%} %>
</tr></thead>
<tbody>
<%
// Loop for all the rows
while(rs.next())
{%>
<tr>
<%
// Loop for all the columns and get the data
for (int i = 1; i <= columnCount; i++) { %>
<td><%=rs.getString(i) %></td>
<%}%>
</tr>
<%}
rs.close(); stmt.close(); conn.close();
}
catch(Exception e) { e.printStackTrace();}
%>
</tbody>
</table>
</div>
</body>
</html>
I know, I know, I know... You shouldn't put code in the .jsp file, it should be in a .java or class file. You should have safety code, you should, "blah blah blah." I told you this was my *Quick and Dirty version!
Like this article?
1
