JDBC Related

Configure Tomcat 6 DataSource using Sql Server 2005

This is a step-by-step instructions on how to configure Tomcat 6 DataSource using Sql Server 2005. The installation of Tomcat and SqlServer is not covered.
1. Verify that you can login to the SQL Server using 'SQL Server Authentication'. You may wish to change the 'Server Authentication mode' to 'SQL Server and Windows Authentication mode'. You may also wish to check that the particular user's status of Login is 'Enabled'.
2. Verify that 'Local and remote connections' is enabled.
Go to Microsoft SQL Server 2005>Configuration Tools>SQL Server Surface Area Configuration>Remote Connections: Enable TCP/IP
3. Restart the database
4. Download and drop the JDBC driver to tomcat_home/lib. You may find tomcat-dbcp.jar in the directory. This will make the driver available to both the Tomcat internal classes and the web application.
5. Change the context.xml, for example:






6. Change the web.xml, for example:




DB Connection
jdbc/TestDB
javax.sql.DataSource
Container



7. Write a testing JSP page like this:
<%@ page contentType="text/html;charset=UTF-8" %>
<%@ page import="java.sql.*" %>
<%@ page import="javax.sql.*" %>
<%@ page import="javax.naming.*" %>

Hello,test JNDI !

<% Context ctx = new InitialContext(); Context envctx = (Context) ctx.lookup("java:comp/env"); DataSource ds = (DataSource) envctx.lookup("jdbc/TestDB"); Connection conn=ds.getConnection(); Statement st=conn.createStatement(); String sql="select * from status"; ResultSet rs=st.executeQuery(sql); while(rs.next()) { %>
ID:<%=rs.getInt(1) %>
Value:<%=rs.getString(2) %>


<% } %>
Here is just JNDI datasource SQL Server 2005 + tomcat example
<% rs.close(); st.close(); conn.close(); %>




That's it!

Posted by Denis Wang at 12:11 PM 0 comments

Friday, May 9, 2008

JDBC Performance Tuning

When a JDBC call is slow, you can do a performance tuning on both the Java code side and the database server side. The following is a list of tips.

Java side performance tuning:

1. Time your database access to have an idea whether the Java code or the database is the culprit.
2. Make sure you are using the right JDBC driver. There are four types of JDBC drivers. Some types, like JDBC-ODBC bridge, are doomed to be slow.
3. Be sure to use database connection pool unless your application is entirely single threaded.
4. Be sure to close database resources, such as Connection, Statement, ResultSet even in situations when Exception's are thrown.
5. Use PreparedStatement correctly.

Database side performance tuning:

1. Determine whether indexes are necessary to create.
2. Update database statistics.
3. Run Explain Plan or similar tools on expensive queries to avoid full table scan.

These tips sound like vanilla. However they are proven effective ways to kill 80% of your JDBC performance issues.

Posted by Denis Wang at 11:28 AM 0 comments

Thursday, May 8, 2008

How to log JDBC Calls to a Stored Procedure

When debugging nasty problems, JDBC developers want to see logs. Of course we can log on the Java code side. A lot of times it is still not good enough. What if the problem is from the JDBC driver? We want to see what is really going on inside a stored procedure and what exactly do the input parameters or the complicated query statement look like.

One simple approach is to insert the statement as a string into a log table. The following is an example in the syntax of SQL Server:
declare @stmt1 nvarchar(2000)
declare @stmt2 nvarchar(2000)

select @stmt1 = '...'
select @stmt2 = '...'

if ( @input is not null ) begin
select @stmt1 = @stmt1 + '...'
select @stmt2 = @stmt2 + '...';
end

select @stmt1 = @stmt1 + @stmt2
exec sp_executesql @stmt1
insert into LogTable values (@stmt1, CURRENT_TIMESTAMP)

In a conclusion, the trick is to write the statement as a string, insert it into a table before execution.

Posted by Denis Wang at 8:28 AM 0 comments

Monday, April 21, 2008

java.lang.NullPointerException when accessing ResultSet from SQL Server

I used Java JDBC to call a SQL Server Stored Procedure and got a NullPointerException from the last line of the following innocent code below:
stmt.execute();
rs = stmt.getResultSet();
while (rs.next()) ...

The stored procedure looks like:
operation 1
operation 2
select ... from ...

It turns out that 'SET NOCOUNT ON' should be added into the stored procedure before the 'operation 1'.