I have been trying to get my jsp code having some postgresql queries to work. I don't know what seems to be the problem, whether it is a problem with the configuration of the jdbc Driver or there is a problem with my code.
Here is the configuration in context.xml .
<Resource name="jdbc/filedb" auth="Container"
type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
url="jdbc:postgresql://127.0.0.1:5432/testdb"
username="akshay" password="akshay" maxActive="20" maxIdle="10"
maxWait="-1"/>
I also want to know what is resource name. Here I have added the table name whichis filedb.
This is how server.xml looks.
<GlobalNamingResources>
<Resource name="jdbc/filedb" auth="Container"
type="javax.sql.DataSource"
username="akshay"
password="akshay"
driverClassName="org.postgresql.Driver"
description="User database that can be updated and saved"
url="jdbc:postgresql://127.0.0.1:5432/testdb"
maxActive="20"
maxIdle="10"
maxWait="-1"
factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
pathname="conf/tomcat-users.xml" />
</GlobalNamingResources>
I tried to run with and also without the above configuration.
This is what I've added to web.xml
<resource-ref>
<description>postgreSQL Datasource example</description>
<res-ref-name>jdbc/filedb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
This is my code:
<html>
<body>
<form name=f1 method=Post action="http://localhost:8080/filedb1.jsp">
<table>
<td>
Add Header 1<input type=text name=header1 id=header1>
</td><td>
Add Header 2<input type=text name=header2 id=header2>
</td><td>
Add Header 3<input type=text name=header3 id=header3>
</td><td>
Add Header 4<input type=text name=header4 id=header4>
</td>
<tr>
<td>
Upload File 1<input type=file name=path1 id=file1></td><td>
Upload File 2<input type=file name=path2 id=file2></td><td>
Upload File 3<input type=file name=path3 id=file3></td><td>
Upload File 4<input type=file name=path4 id=file4></td>
</tr>
</table>
<div align="center">
<input type=submit value="SUBMIT" onclick="f1.action='http://localhost:8080/filedb1.jsp';return true;">
</div>
</form>
<%@ page import="java.sql.*" %>
<%@ page import="javax.servlet.*" %>
<%@ page import="javax.servlet.http.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.util.*" %>
<%@ page language="java" session="true" %>
<%
String file1 = request.getParameter("file1");
String file2 = request.getParameter("file2");
String file3 = request.getParameter("file3");
String file4 = request.getParameter("file4");
String header1 = request.getParameter("header1");
String header2 = request.getParameter("header2");
String header3 = request.getParameter("header3");
String header4 = request.getParameter("header4");
java.sql.Connection con;
con=null;
PreparedStatement psmt=null;
PreparedStatement psmt1=null;
PreparedStatement psmt2=null;
PreparedStatement psmt3=null;
try{
Class.forName("org.postgresql.Driver");
con = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/testdb","akshay","akshay");
psmt=con.prepareStatement("insert into filedb(id,header,content)"+"values (?,?,?)");
psmt.setInt(1,1);
psmt.setString(2,header1);
psmt.setString(3,file1);
int s = psmt.executeUpdate();
%> <font color="orange">
<%
if(s>0)
{
%><%out.println("File Name :"+file1+" added to database.");%><%
}
else
{
%><%out.println("File Name :"+file1+" unsuccessfull attempt while adding to database.");%><%
}
psmt1=con.prepareStatement("insert into filedb(id,header,content)"+"values (?,?,?)");
psmt1.setInt(1,2);
psmt1.setString(2,header2);
psmt1.setString(3,file2);
int s1 = psmt1.executeUpdate();
if(s1>0)
{
out.println("File Name :"+file2+" added to database.");
}
else
{
out.println("File Name :"+file2+" unsuccessfull attempt while adding to database.");
}
psmt2=con.prepareStatement("insert into filedb(id,header,content)"+"values (?,?,?)");
psmt2.setInt(1,3);
psmt2.setString(2,header3);
psmt2.setString(3,file3);
int s2 = psmt2.executeUpdate();
if(s2>0)
{
out.println("File Name :"+file3+" added to database.");
}
else
{
out.println("File Name :"+file3+" unsuccessfull attempt whileadding to database.");
}
psmt3=con.prepareStatement("insert into filedb(id,header,content)"+"values (?,?,?)");
psmt3.setInt(1,4);
psmt3.setString(2,header4);
psmt3.setString(3,file4);
int s3 = psmt3.executeUpdate();
if(s3>0)
{
out.println("File Name :"+file4+" added to database.");
}
else
{
out.println("File Name :"+file4+" unsuccessfull attempt whileadding to database.");
}
psmt1.close();
psmt2.close();
psmt3.close();
psmt.close();
con.commit();
con.close();
}
catch(Exception ex)
{
ex.printStackTrace();
}
%>
</font>
</body>
</html>
Table filedb
Table "public.filedb"
Column | Type | Modifiers
---------+---------+-----------
id | integer | not null
header | text | not null
content | text | not null
I don't get any errors but there is no update in the table. There are no new records.
I apologize for the bad indentation and the trivial nature of my problem but I have not been able to surpass it. Any help is appreciated.
So I used mysql connector instead.
This is what I added to web.xml
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/test</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
Here is what I added to context.xml
<Resource name="jdbc/filedb" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="akshay" password="akshay" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test?autoReconnect=true"/>
And lastly this is what went inside server.xml
<Resource name="jdbc/filedb" auth="Container"
type="javax.sql.DataSource"
username="akshay"
password="akshay"
driverClassName="com.mysql.jdbc.Driver"
description="User database that can be updated and saved"
url="jdbc:mysql://127.0.0.1:3306/test"
maxActive="20"
maxIdle="10"
maxWait="-1"
factory="org.apache.catalina.users.MemoryUserDatabaseFactory"
pathname="conf/tomcat-users.xml" />
I don't know why its not working with postgresql but the code is actually the same.