Test connecting PostgreSQL DB from tomcat8

This is an continued article of PostgreSQL JDBC Driver on tomcat8 - Shammerism. There is a JSP tag library to connect Database and showing the search result. There are a lot of links, one of them is https://www.tutorialspoint.com/jsp/jsp_standard_tag_library.htm.

I tested this tag library with using my tomcat8 and postgresql. Here is a procedures.

Create DB on PostgreSQL
1. Login DB Server
2. su - postgres
3. createdb TestDB
4. psql TestDB
5. CREATE ROLE test PASSWORD 'Password123';
6. ALTER ROLE test LOGIN;
7. ALTER ROLE test CREATEDB;
8. GRANT SELECT ON TABLE testdata TO test;
9. CREATE TABLE testdata ( id int, foo varchar(80), bar varchar(80));
10. INSERT INTO testdata VALUES(10,'ABCDEFG','01234567890');
11. \q
Configure Datasource JNDI object on tomcat8
1. open /etc/tomcat8/context.xml and add following Resource element
2. copy correct JDBC Driver, more detail in my previous article
Application implementation

web.xml should include the pointer of Datasource JNDI object
include jsp using sql tag library

The datasource JNDI object definition in context.xml should be below.

<Resource name="jdbc/TestDB" auth="Container"
	      type="javax.sql.DataSource"
	      driverClassName="org.postgresql.Driver"
	      url="jdbc:postgresql://127.0.0.1:5432/TestDB"
	      username="test" password="Password123" />

The resource-ref definition in web.xml which should be included war file is below.

    <resource-ref>
      <res-ref-name>jdbc/TestDB</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
    </resource-ref>

And sample JSP is below.

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<sql:query var="rs" dataSource="jdbc/TestDB">
select id, foo, bar from testdata
</sql:query>

<html>
  <head>
    <title>DB Test</title>
  </head>
  
  <body>

  <h2>Results</h2>

  <table border="1">
    <tbody>
      <tr><th>ID</th><th>Foo</th><th>Bar</th></tr>
<c:forEach var="row" items="${rs.rows}">
<tr><td>${row.id}</td><td>${row.foo}</td><td>${row.bar}</td></tr>
</c:forEach>
    </tbody>
  </table>

  </body>
</html>

Shammer's Philosophy

My private adversaria

Test connecting PostgreSQL DB from tomcat8