Java by examples

Now a days all major applications in Java using Hibernate to support multiple dialect. However Today I'm going to post an article "Connecting Microsoft SQL Server using Java code" by using SQL Server library.

Change credential in below code and test your connection to Microsoft SQL Server

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @author javaQuery
 * This program demonstrate connection between Java and Microsoft SQL Server.
 */
public class MSSQLConnect {

    public static void main(String[] args) {
        /**
         * Driver to connect database.
         * Database credentials.
         */
        String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String url = "jdbc:sqlserver://127.0.0.1:1433;DatabaseName=javaQuery";
        String username = "sa";
        String password = "airborne";
        try {
            /* Load database driver */
            Class.forName(driver);
            /* Establish database connection */
            Connection con = DriverManager.getConnection(url, username, password);
            /* Run query */
            PreparedStatement stmt = con.prepareStatement("select * from user_master");
            /* Get return result */
            ResultSet resultset = stmt.executeQuery();
            /* Loop through every row  */
            while (resultset.next()) {
                System.out.println(resultset.getString("Firstname")+" "+resultset.getString("Lastname"));
            }
            /* Close result set */
            resultset.close();
            /* Close database connection */
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Download Microsoft SQL Server API from here http://goo.gl/HT6HA6

Scenario: 
We were calling MSSQL Stored Procedure using hibernate. My colleague called me to check out error and even I was clueless. It was working fine at database level. And while checking out calling code it seems alright but it thrown an error as follow.

org.hibernate.exception.SQLGrammarException: could not execute query
  at Incorrect syntax near '@P1'..()
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
  at could not execute query.(Native Method)
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
  at org.hibernate.loader.Loader.doList(Loader.java:2223)
  at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
  at org.hibernate.loader.Loader.list(Loader.java:2099)
  at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289)
  at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695)
  at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
  at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.doExecute(AbstractDispatchImpl.java:153)
  at com.gwtplatform.dispatch.server.AbstractDispatchImpl.execute(AbstractDispatchImpl.java:111)
  at com.gwtplatform.dispatch.server.AbstractDispatchServiceImpl.execute(AbstractDispatchServiceImpl.java:80)
  at sun.reflect.GeneratedMethodAccessor1479.invoke(Unknown Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
  at java.lang.reflect.Method.invoke(Unknown Source)
  at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
  at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
  at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
  at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
  at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:263)
  at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:178)
  at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91)
  at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:62)
  at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:118)
  at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:113)
  at org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1088)
  at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:360)
  at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
  at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
  at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:729)
  at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:405)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.handler.RequestLogHandler.handle(RequestLogHandler.java:49)
  at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
  at org.mortbay.jetty.Server.handle(Server.java:324)
  at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:505)
  at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:843)
  at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:647)
  at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:205)
  at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:380)
  at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:395)
  at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:488)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
  ... 53 more
Caused by: java.sql.SQLException: Incorrect syntax near '@P1'.
  at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
  at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2820)
  at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2258)
  at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:632)
  at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:477)
  at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:778)
  at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:144)
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
  at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
  at org.hibernate.loader.Loader.doQuery(Loader.java:674)
  at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
  at org.hibernate.loader.Loader.doList(Loader.java:2220)
  ... 50 more

Solution:
My colleague forgot to put comma , after each parameter. Follow below queries one with error and other without.

EXEC MY_STORED_PROC :PARAM1 :PARAM2 /* Query that throwing error because of missing comma. */
EXEC MY_STORED_PROC :PARAM1, :PARAM2 /* Query that works fine. */