Instantly share code, notes, and snippets.

Embed
What would you like to do?
<%@page import="java.sql.*, javax.sql.*, javax.naming.*"%>
<%--
Simple test to check if the connection to a database is working.
Steps:
- Starting with a new Openshift Jboss app, using the Openshift web console, add a DB. For example, PostgreSQL 9.2.
(you can also use command-line. For example: rhc-ctl-app -a <application-name> -l <email-id-we-used-to-register> -e add-mysql-5.1 )
It will not use any extra gears.
- You can check the datasources, already declared for JBoss, in: .openshift/config/standalone.xml (hidden folder).
In this example, we will use the datasource with name "PostgreSQLDS". Openshift has internally enabled it in the previous steps.
- You can also ssh to your app, and check the environment variables (user, pw, datasource url) already declared: env | grep POSTGRESQL
- Place this file in <path-to-your-openshift-app>/src/main/webapp
- In this example, the datasource is "PostgreSQLDS". Change it if yours is different.
- Add, commit and push to Git. Access with a browser to <url-of-your-app>/testDatabaseConnection.jsp, and it's done.
Of course, connecting to DB from a jsp is a bad practice :) This is only an easy example.
--%>
<%
DataSource ds = null;
Connection conn = null;
ResultSet result = null;
Statement stmt = null;
ResultSetMetaData rsmd = null;
try{
Context context = new InitialContext();
Context envCtx = (Context) context.lookup("java:jboss/datasources");
ds = (DataSource)envCtx.lookup("PostgreSQLDS");
if (ds != null) {
conn = ds.getConnection();
stmt = conn.createStatement();
result = stmt.executeQuery("SELECT now()");
}
}
catch (SQLException e) {
System.out.println("Error occurred " + e);
}
int columns=0;
try {
rsmd = result.getMetaData();
columns = rsmd.getColumnCount();
}
catch (SQLException e) {
System.out.println("Error occurred " + e);
}
%>
<html>
<head>
<title>Using a DataSource</title>
</head>
<body>
<h1>Using a DataSource</h1>
<table width="90%" border="1">
<tr>
<% // write out the header cells containing the column labels
try {
for (int i=1; i<=columns; i++) {
out.write("<th>" + rsmd.getColumnLabel(i) + "</th>");
}
%>
</tr>
<% // now write out one row for each entry in the database table
while (result.next()) {
out.write("<tr>");
for (int i=1; i<=columns; i++) {
out.write("<td>" + result.getString(i) + "</td>");
}
out.write("</tr>");
}
// close the connection, resultset, and the statement
result.close();
stmt.close();
conn.close();
} // end of the try block
catch (SQLException e) {
System.out.println("Error " + e);
}
// ensure everything is closed
finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e) {}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {}
}
%>
</table>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment