Connecting Java EE RESTful web services, hosted on GlassFish, to Microsoft SQL Server – a high level overview. Demonstrate the creation of a Web Application project in NetBeans, including a SQL Server data source, entity classes from a SQL database, and RESTful web services. Show how to test and deploy the project to GlassFish.
Introduction
In a previous post, Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0, I demonstrated how Microsoft’s JDBC Driver 4.0 can connect Java-based RESTful web services to Microsoft SQL Server. In a more recent post, Calling Microsoft SQL Server Stored Procedures from a Java Application Using JDBC, I demonstrated the use of JDBC to call stored procedures from a Java application. In this post, I am going to offer a high-level, end-to-end overview on how to create and connect Java EE RESTful web services, hosted on GlassFish, to SQL Server. The goals of post are:
- Demonstrate the use of Microsoft’s JDBC Driver 4.0 to connect a Java-based application to SQL Server 2008 R2;
- Demonstrate the use of NetBeans to:
- Create a SQL Server Data Source;
- Create entity classes from the SQL Server database using the SQL Server Data Source;
- Create RESTful web services using JAX-RS, which communicate with database, through the entity classes;
- Compile and deploy the data source, entities, and services to GlassFish;
- Test the RESTful Web Services locally in NetBeans, and once deployed, in GlassFish.
Setting up the Post’s Example
To demonstrate the example in this post, I have the follow applications installed, configured, and running in my development environment:
- Microsoft SQL Server 2008 R2 (www.microsoft.com/sqlserver)
- Microsoft’s Adventure Works 2008 R2 Sample Database (msftdbprodsamples.codeplex.com)
- Microsoft JDBC Driver 4.0 for SQL Server (msdn.microsoft.com/en-us/sqlserver/aa937724.aspx)
- NetBeans 7.2 Open Source IDE (netbeans.org)
- Apache Any 1.82 (installed with NetBeans or downloaded separately from ant.apache.org)
- GlassFish 3.2.2.2 Open Source Edition Application Server (installed with NetBeans or downloaded separately from glassfish.java.net)
If you have any questions about installing and configuring the Adventure Works database, please refer to my post, Convert VS 2010 Database Project to SSDT and Automate Publishing with Jenkins – Part 1/3. The post takes you through creating a SQL Server 2008 R2 instance (entitled ‘Development’), installing the Adventure Works database, and creating a database user (‘aw-dev’). Also, refer to my earlier post, Connecting Java EE to SQL Server with Microsoft’s JDBC Driver 4.0, for directions on installing the Microsoft JDBC driver in the lib directory of GlassFish. This is necessary before following along with this demonstration.
Note, since I have NetBeans, SQL Server, and GlassFish all installed on a single computer, the URLs in several of the screen-grabs switch between ‘localhost’ and my computer’s actual name. Both references are interchangeable.
The Demonstration
Here are the high-level steps I will walk-through in this post:
- Confirm the SQL Server instance, database, and user are functioning properly;
- Create a new Web Application project in NetBeans;
- Create the SQL Server data source in the project;
- Create entity classes from the SQL Server database;
- Create RESTful web services using the entity classes;
- Test the web services locally in NetBeans;
- Build and deploy the project to GlassFish;
- Test the web services on GlassFish.
SQL Server
Using Microsoft’s SQL Server Management Studio, Quest’s Toad for SQL, or similar IDE, confirm the ‘Development’ instance (or whatever you have named your instance) of SQL Server 2008 R2 is running. Confirm the Adventure Works database is installed in that instance. Lastly, confirm the ‘aw_dev’ user can connect to the Adventure Works database and view and interact with all the database objects. Confirming these items will elevate many problems you might otherwise encounter when creating the data source, next.
View of the SQL Server Instance from SSMS. Note the : 1) Server, Instance, User, 2) Database, and 3) Schema
Create New Web Application Project
Create a new Java Web Application project in NetBeans. Name the project whatever you would like; I named mine ‘JdbcSqlWebSrvTest’. Select the correct GlassFish server instance and GlassFish domain where the project will be deployed. I am deploying my project to the default ‘domain1’ domain.
Choose the Web Application Project-Type in NetBeans
Provide a Project Name and Location
Select the Target GlassFish Server and Domain Where the Project Will be Installed
Include any Frameworks You Will Use with the Project
View of the New Web Application Project in NetBeans
Create SQL Server Data Source and Entity Classes from the Database
Right-click on the project again and select ‘New’ -> ‘Other…’. From the list of Categories, select ‘Persistence’. From the list of Persistence choices, choose ‘Entity Classes from Database’. Click Next.
Choose ‘Entity Classes from Database’
Before we can choose which database tables we want from the Adventure Works database to create entity classes, we must create a connection to the database – a SQL Server Data Source. Click on the Data Source drop down and select ‘New Data Source…’. Give a Java Naming and Directory Interface (JNDI) name for the data source. I called mine ‘AdventureWorks’. Click on the ‘Database Connection’ dropdown, select ‘New Database Connection…’.
Database Tables View Before Data Source is Created
Name the Data Source
This starts the ‘New Connection Wizard’. The first screen, ‘Locate Driver’, is where we point the will instruct NetBeans to use the Microsoft JDBC Driver 4.0 for SQL Server Driver. Locate the sqljdbc4.jar file.
Locate the Microsoft JDBC Driver 4.0 for SQL Server Driver .jar File
On the next screen, ‘Customize the Connection’, input the required SQL Server information. The host is the machine your instance of SQL Server is installed on, such as ‘localhost’. The instance is the name of the SQL Server instance in which the Adventure Works database is installed, such as ‘Development’. Once you complete the form, click ‘Test Connection’. If it doesn’t succeed, check your settings, again.
Provide the SQL Server Adventure Works Database Connection Information
As I mentioned in an earlier post, the SQL Server Data Source forces you to select a single database schema. On the ‘Choose Database Schema’ screen, select the ‘HumanResources’ schema. The database tables you will be able to reference from you entity classes are limited to just this schema, when using this data source. To reference other schemas, you will need to create more data sources.
Select the ‘HumanResources’ Database Schema
New Data Source Complete with JNDI Name and Database Connection Created
Back in the ‘New Entity Classes from Database’ window, you will now have the ‘AdventureWorks’ data source selected as the Data Source. After a few seconds of processing, all ‘Available Tables’ within the ‘HumanResources’ schema are displayed. Choose the four tables shown in the screen-grab, below. Actually, two are database tables and two are virtual tables, called database ‘views’. We will need to do an extra step later on, to use the two entity classes built from the database views.
Retrieving all the ‘HumanResources’ Schema Database Tables and Views
All the ‘HumanResources’ Schema Database Tables and Views Available
Selecting the Database Views Throws a Warning Regarding no Entity IDs (Primary Keys)
On the next screen, ‘Entity Classes’, in the ‘New Entity Classes from Database’ window, give a package name to place the individual entity classes into. I chose to call mine ‘entityclasses’.
Provide the Name of the Package Where the Entity Classes Will be Created
On the next screen, ‘Mapping Options’, choose ‘Fully Qualified Database Table Names’. Without this option selected, I have had problems trying to make the RESTful web services function properly. This is also the reason I chose to create the entity classes first, and then create the RESTful web services, separately. NetBeans has an option that combines these two tasks into a single step, by choosing ‘RESTful Web Services from Database’. However, the ‘Fully Qualified Database Table Names’ option is not available on the equivalent screen, using that process (at least in my version of NetBeans 7.2). I prefer the two-step approach.
Select the ‘Fully Qualified Database Table Names’ Option
Click finished. You have successfully created the SQL Server data source and entity classes.
The data source we created, which will be deployed to GlassFish, is referred to as a JDBC Resource and a JDBC Connection Pool. This JDBC information is stored in the ‘glassfish-resources.xml’ file by NetBeans.
New GlassFish JDBC Connection Pool and JDBC Resource Contained in the glassfish-resources.xml File
The JPA Persistence Unit is found in the ‘persistence.xml’ file in the ‘Configuration Files’ folder. This file describes the Persistence Unit (PU). The PU serves to register the project’s four persistable entity classes, which are referred to by JPA as managed classes.
persistence.xml Design View
persistence.xml Source View
RESTful Web Services from Entity Classes
Now that have a SQL Server Data Source and our entity classes, we will create the RESTful web services. Right-click on the project and select ‘New’ -> ‘Other…’ -> ‘Persistence’ -> ‘RESTful Web Services from ‘Entity Classes’. You will see a list of four entity classes we just created, from which to choose. Add all four entity classes.
Creating the New RESTful Web Service from Four Entity Classes
Add the Four Entity Classes
On the next screen, give a name for Resource Package to store the service classes in; I called mine ‘service’. That’s it; you now have four RESTful web services and the corresponding Enterprise Beans and Façade service classes. The service class sits between the RESTful web service and the entity class.
Provide a Package Location to Place the RESTful Web Service Classes Into
Click finished. You have successfully created the RESTful web services.
View of Web Application Project with Entity Classes and RESTful Web Services Added
View of Web Application Project with Entity Classes and RESTful Web Services Added
Adding a Primary Key to Entity Classes
If you recall, I mentioned a problem with the two entity classes we created from the database views. To avoid an error when you build and deploy your project to GlassFish, we need to make a small change to the VEmployee.java and VEmployeeDepartment.java entity classes. Entity classes need a unique identifier, a primary key (or, Entity ID) identified. Since these two entity classes are built from database views, as opposed to database tables, they lack a primary key. To fix this, annotate the ‘businessEntityID’ field with ‘@Id’ in each class. This indicates that ‘businessEntityID’ is the primary key (Entity ID) for this class. The field, ‘businessEntityID’, must contain unique values, for this to work properly. NetBeans will make the suggested correction for you, if you allow it.
NetBeans Highlights the Entity Id Error in Two View-Based Entity Classes
Select the ‘businessEntityID’ Field as the Primary Key
View of Entity Class with ‘@Id’ Annotation Added
Test RESTful Web Services Locally in NetBeans
NetBeans provides an easy way to test the RESTful web services, locally. Right-click on the ‘RESTful Web Services’ project folder within the main project, and select ‘Test RESTful Web Services’. Select the first option, ‘Locally Generated Test Client’, in the ‘Configure REST Test Client’ pop-up window.
Choose the Local Test Client Option
NetBeans will open a web browser window and displays the RESTful URI (Universal Resource Identifier) for the services in a tree structure. There are four primary URIs, corresponding to the four services. Each primary URI has additional child URIs grouped under them. Are they URIs or URLs? I found this excellent post that does a very good job explaining the difference between the URL (how to get there) and the URI (the resource), which is part of the URL.
You May Get an ActiveX Warning When Using IE to Test the RESTful Web Services
View of all the RESTful Web Services Universal Resource Identifiers (URIs)
Click on the ‘entityclasses.employee’ URI. Choose the HTTP ‘GET()’ request method from the drop-down and click the ‘Test’ button. The service should return a status of ‘200 (OK)’, along with xml output containing information on all the Adventure Works employees. Congratulation, the RESTful web services have just returned data to your browser from the SQL Server Adventure Works database, using the entity classes and data source you created.
All Employees Being Successfully Retrieved from the Adventure Works Database
Click on the other URIs to familiarize yourself with the various default resources. Test the employee ‘from/to’ URI by inputting two parameters, test the ‘count’ URI, and try changing the MIME type where applicable from XML to JSON and observe the results.
A Single Employee Being Successfully Retrieved from the Adventure Works Database Using Input Parameter
Count of All Employees Being Successfully Retrieved from the Adventure Works Database
WADL
Note the link in the upper right corner of the above screens, labeled WADL: ‘http://[your_server_path]/JdbcSqlWebSrvTest/webresources/application.wadl’
The WADL (Web Application Description Language) file is the machine-readable XML description of the RESTful web service(s). The WADL file is to RESTful web services, as the WSDL (Web Service Definition Language) file is to non-RESTful, SOA-/SOAP-oriented web services. The WADL provides all the information you need to understand to the various RESTful web service’s resources, and how to call them using their URIs. According to Wikipedia, in the WADL file, ‘the service is described using a set of resource elements. Each resource has param elements to describe the inputs, and method elements which describe the request and response of a resource. The request element specifies how to represent the input, what types are required and any specific HTTP headers that are required. The response describes the representation of the service’s response, as well as any fault information, to deal with errors.’ You can download the WADL file (application.wadl), and review it in an XML-friendly viewer such as Notepad++.
View of the RESTful Web Services’ WADL File – application.wadl
Deploy Project to GlassFish
Now that the RESTful web services are working properly from within NetBeans, we can deploy them to GlassFish. To deploy the project to GlassFish, right-click on the main project icon in the Projects tab and select ‘Clean and Build’. Once the project builds successfully, right-click again and select ‘Deploy’. This will instruct Apache Ant to deploy the project as a .war file to GlassFish, using the project’s default Ant deploy task. The SQL Server data source will also be installed into GlassFish.
Once the deployment is complete, switch to GlassFish and refresh the home page if necessary. Under the ‘Applications’ item on the left-hand navigation menu, you should see a new application with the same name as your project, ‘JdbcSqlWebSrvTest’.
Default View of GlassFish Domain Prior to Deploying the New Web Application
New Web Application and JDBC Resource and Pool Deployed Successfully to GlassFish
Also, under the ‘JDBC’ -> ‘JDBC Resources’ item, you should see a resource with the same name as the data source you created in NetBeans, ‘AdventureWorks’. Under the ‘JDBC’ -> ‘JDBC Connection Pools’, you should see a pool entitled ‘microsoft_sql_AdventureWorks_aw_devPool’. The JDBC Resource, ‘AdventureWorks’, is linked to this pool. The pool is a ‘javax.sql.DataSource’ resource type, which references the ‘com.microsoft.sqlserver.jdbc.SQLServerDataSource’. This data source is identical to the data source you built in NetBeans.
New JDBC Resource Successfully Deployed to GlassFish
New JDBC Connection Pool Successfully Deployed to GlassFish
Test Web Services on GlassFish
To test the RESTful web services from GlassFish, begin by clicking on the ‘JdbcSqlWebSrvTest’ application, under ‘Applications’ menu item. On the Applications page, click on the ‘Launch’ action link. GlassFish open a new web browser window, and presents you with two ‘Web Application Links’, one link is HTTP and the other, HTTPS. Click on the HTTP link. This should display the default index.jsp page’s ‘Hello World!’ message.
Default Response from Application
To call the service, append the current URL to match the resource URIs you used when testing the services in NetBeans. For example, to display all the employees again like you did in NetBeans, append the current URL, http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/, to include the following:
http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/webresources/entityclasses.employee
This URI should return the same xml content you observed when testing this same URI locally in NetBeans.
All Employees Being Successfully Retrieved from the Adventure Works Database
As another test, append the URI to also include the Id of a single employee, as follows:
http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/webresources/entityclasses.employee/2
This should cut the amount of data returned from the Adventure Works database to a single employee record.
A Single Employee Being Successfully Retrieved from the Adventure Works Database Using a Parameter
One last test, remove the number two from the URI and add the word ‘count’, as follows:
http://%5Byour_server_name%5D:%5Bport%5D/JdbcSqlWebSrvTest/webresources/entityclasses.employee/count
This time, you should see a single integer returned to the browser, representing the count of all employees in the database’s employee table.
Count of All Employees Being Successfully Retrieved from the Adventure Works Database
Conclusion
Congratulations, the Java EE RESTful web services have been successfully deployed to GlassFish. The services are connecting to Adventure Works SQL Server database, through the entity classes and data source, and returning data to your web browser! Next step is to create a RESTful web services client application, to display the data returned by the services and/or to perform CRUD operations on the database.
Programmatic Ponderings 
#1 by Bartholomew on October 26, 2012 - 8:20 pm
Hello Mr. Gary A. Stafford , i am glad if there is most glad i am for your intense resource you shared.Before i begin my name is Omile Bartholomew from Nigeria.i am new to web service as a topic.
Please i would like to ask you two questions
1. i created a database called ‘example’, with schema called ‘exam’
then i created my table like this
create table exam.student
(
firstName varchar(20) not null,
lastName varchar(150) not null,
student_id int not null constraint PKstudent primary key
)
i created my entity class from database “example ” database i created in my Sq l server 2008
But when i try to create Restfull web service from already created entity class ,i did not see any table or view .
please i need your help sir
2.how can i also create web service using “soap” to refer to external database like Sq l server 2008 apart from Restful method of approach.
well i tried to use class.forName method to create a connection for my Sql server access in my soap web service i receive these error message when trying to deploy it after cleaning and building
Error ::WARNING: Deployment failed
com.sun.xml.ws.spi.db.DatabindingException: com.sun.xml.bind.v2.runtime.IllegalAnnotationsException: 1 counts of IllegalAnnotationExceptions
java.sql.Connection is an interface, and JAXB can’t handle interfaces.
this problem is related to the following location:
at java.sql.Connection
at public java.sql.Connection SERV.SERVICES.jaxws.Query.arg0
at SERV.SERVICES.jaxws.Query
at com.sun.xml.ws.db.glassfish.JAXBRIContextFactory.newContext(JAXBRIContextFactory.java:101)
at com.sun.xml.ws.spi.db.BindingContextFactory.create(BindingContextFactory.java:182)
at com.sun.xml.ws.model.AbstractSEIModelImpl$1.run(AbstractSEIModelImpl.java:213)
at com.sun.xml.ws.model.AbstractSEIModelImpl$1.run(AbstractSEIModelImpl.java:186)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.xml.ws.model.AbstractSEIModelImpl.createJAXBContext(AbstractSEIModelImpl.java:186)
at com.sun.xml.ws.model.AbstractSEIModelImpl.postProcess(AbstractSEIModelImpl.java:111)
at com.sun.xml.ws.model.RuntimeModeler.buildRuntimeModel(RuntimeModeler.java:318)
at com.sun.xml.ws.db.DatabindingImpl.(DatabindingImpl.java:99)
at com.sun.xml.ws.db.DatabindingProviderImpl.create(DatabindingProviderImpl.java:74)
at com.sun.xml.ws.db.DatabindingProviderImpl.create(DatabindingProviderImpl.java:58)
at com.sun.xml.ws.db.DatabindingFactoryImpl.createRuntime(DatabindingFactoryImpl.java:130)
at com.sun.xml.ws.server.EndpointFactory.createSEIModel(EndpointFactory.java:433)
at com.sun.xml.ws.server.EndpointFactory.create(EndpointFactory.java:268)
at com.sun.xml.ws.server.EndpointFactory.createEndpoint(EndpointFactory.java:145)
at com.sun.xml.ws.api.server.WSEndpoint.create(WSEndpoint.java:569)
at com.sun.xml.ws.api.server.WSEndpoint.create(WSEndpoint.java:552)
at com.sun.xml.ws.api.server.WSEndpoint.create(WSEndpoint.java:623)
at org.glassfish.webservices.WSServletContextListener.registerEndpoint(WSServletContextListener.java:282)
at org.glassfish.webservices.WSServletContextListener.contextInitialized(WSServletContextListener.java:102)
at org.apache.catalina.core.StandardContext.contextListenerStart(StandardContext.java:4750)
at com.sun.enterprise.web.WebModule.contextListenerStart(WebModule.java:550)
at org.apache.catalina.core.StandardContext.start(StandardContext.java:5366)
at com.sun.enterprise.web.WebModule.start(WebModule.java:498)
WARNING: java.lang.IllegalStateException: ContainerBase.addChild: start: org.apache.catalina.LifecycleException: java.lang.RuntimeException: Servlet web service endpoint ” failure.
Sir i thank you for your help in Resful web service ,i am looking forth for your help in my case.
#2 by Gary A. Stafford on October 30, 2012 - 4:03 pm
1. “But when i try to create Restfull web service from already created entity class ,i did not see any table or view .” – I didn’t follow this part. If you have already created the entity classes from the tables (or views), you wouldn’t see them when you create the RESTful web service classes. You should see the entity classes you created.
2. “how can i also create web service using “soap” to refer to external database like Sq l server 2008 apart from Restful method of approach.” – The process of creating the database connection and entity classes would be the same, but you would create a new web service class (not RESTful web service class) based on a WSDL or from an existing session bean (like the bean created for your RESTful web service).
#3 by Thanasis Zeliotis on November 22, 2012 - 6:40 pm
My name is Thanasis Zeliotis and I greet you. I read carefully your example and I found it very helpful. Still I would take the courage and ask you if you can help me on a certain issue.
I am using also SQL SERVER database and Netbeans java platform to create rest web services.
still i have a small problem which i dont know yet how to solve. I was hoping for a hint or so.
i have created a table in database which is called lets say “TABLE”. Now in this table there are records which can be viewed only from certain users. To do that i used VIEWS and I set different rights for the users. The views where named “TABLE_1”, “TABLE_2” etc..
I could create seperate web services from those views using your example but i dont want to do that because the number of the views is not standard. As users appended in the futire, the relevant views will be appended also. So I cant create new java classes and web services everytime a user is added. it is not efficient, even if i use inheritence.
So Is there any way to use one java entity class as a pattern for many similar structured tables?
I would appreciate your answer. how easy is to accomplish my goal?
#4 by Gary A. Stafford on November 26, 2012 - 9:54 pm
Thanasis – Thank you for your comment. It’s hard to understand all the details of your application through email. However, sounds like one possible solution would be to a stored procedures with input parameters to return a result set instead of views. You could pass the necessary credentials you are currently using (user id, role id, or group id) to the stored procedure, which in turn would query the table and return a subset of records, similar to the view, based on the user’s level of access to the records in that table(s).
You might also pass a parameter(s) to the stored procedure that represents different view of the result set (similar to having multiple views).
You would have one web service with a single operation which calls the stored procedure, passing in the input parameter(s). Alternatively, one service with multiple operations, each which calls the same procedure, passing a different parameter for the ‘view’ of the result set you want. Just some quick mock-code. Hope this might help:
USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetUserView]
@personId int= 0,
@viewId int = 1
AS
IF (@viewId = 1)
BEGIN
SELECT Field1
FROM [dbo].[TableName]
WHERE PersonId = @personId
END
IF (@viewId = 2)
BEGIN
SELECT PersonId, Field2, Field3
FROM [dbo].[TableName]
WHERE PersonId = @personId
AND Field2 > 100
END
IF (@viewId = 3)
BEGIN
SELECT PersonId, Field1, Field2, Field3
FROM [dbo].[TableName]
WHERE PersonId = @personId
ORDER BY Field1
END
RETURN 0
GO
Here is a post I wrote on using stored procedures with JPA and Web Services:
https://programmaticponderings.wordpress.com/2012/08/24/calling-sql-server-stored-procedures-with-java-using-jdbc
#5 by Thanasis Zeliotis on November 27, 2012 - 8:50 am
Thank you for your reply Mr.Stafford. I am relative new to the JPA and REST technology so I will find very very useful all of your posts, since I am also using SQL Server.
In this point I have to make a small reference to what I am trying to achieve again.
A. There is a table called USERS which connects every user to a certain number. e.g. user1 corresponds to number 1, user2 to number 2… etc. .
B. There is also a large table called TABLE which contains a field called USER_ID. This field is filled by a number which connects each record to a specific user according to the USERS table.
C. In order to secure each user’s records from others I created Views called TABLE_n where n = the id of the user. So the view with name TABLE_1 contains all records of user1 filtered on USER_ID=1. TABLE_2 contains all records of user2 filtered on USER_ID=2 etc…
For any new user an appended record is created in the USERS table with n as ID and a relevant VIEW called TABLE_n is created automatically filtering the USER_ID field of table TABLE to value n. So the number of views in increased with every user addition.
The question is… Can I use one dynamic java entity class ass pattern, in which I will give the view name as well as a parameter? Is there any way to dynamically map these views using variable @table annotation or something relevant? Is there any overriding method to do that. Does JPA people thought of this specific problem so far?
I am not trying to build Eiffel tower here. I am asking for something very simple actually. I find the approach of views in these kind of Real World Database problems very common. Almost classic. (if you want to break a large table into smaller filtered parts and give different access rights to each one. Another approach was to create many tables with the same stucture but this gives less flexibility in filtering).
I guess there must be a simple solution cause honestly, I would be very surprised if JPA people didnt think about this until now.
I dont want to dig deep to change java classes persistence behavior to do that and so on. (In the end if I have to I will.. I am just asking now if there is a simple way)
The use of store procedure adds a new perspective for me, which I havent thought of until now. However there are a few drawbacks. There is a problem of access rights in the store procedure. Should not be used by all users. This can be solved by using many store procedures each for every user, like views. But this means that we come to the same problem we faced before. A variable name for views or store procedure. Another problem is that you can not make CRUD operations in a single store procedure, so you have to create specific store procedures for this as well.
I was hoping if you Mr Stafford have any more ideas.
#6 by Bahar on April 12, 2013 - 8:27 pm
Hi Gary,
Thanks for your helpful post.
Would you give me some hint or link for make a web service with sql server which is going to implement in Eclipse and Apache ?!
I have tried but not success. Simple web service works fine but I have problem with database connection and read data.
#7 by Gary A. Stafford on April 15, 2013 - 9:19 pm
I have several detailed posts on working wit SQL Server from Java. Please read https://programmaticponderings.wordpress.com/2012/08/24/calling-sql-server-stored-procedures-with-java-using-jdbc
#8 by omisayeinfotech on June 20, 2013 - 10:23 am
Hello Mr. Gary A, i’m omile Bartholonew from Nigeria.currently i’m trying to develop an jsp enterprise web site. Resfull service is what i’m using, this( Restfull web service) will be consumed by java-script as a client ..if you wouldn’t mind , please could you be of help by giving me some reading resources that will enhance my me ?? i use Netbean 7.3.1 as my IDE. thank you Mr. Gary for your coperation.
#9 by omisayeinfotech on June 20, 2013 - 10:29 am
Please Mr.Gary , i use mysql server as my database.
#10 by Gary on September 3, 2013 - 6:37 pm
Gary,
I followed your instructions to the letter with the exception of creating an datasource to an external SQl database. I am able to get teh service to perform the get fine on the test web service page. it returns both json and XML perfect. However, I get an error when trying to use POST or PUT. I get a response can not deserialise. Using fiddler, see the same thing only it is a response 415 “unsupported media type”. Any assitance you can provide is greatl appreciated. I work for RGE in rochester. Please reply back there also gary_wiest@rge.com. Thank you in advance for your assistance. The following is the response I am getting back in the test view:
javax.servlet.ServletException: org.glassfish.jersey.server.ContainerException: org.codehaus.jackson.map.JsonMappingException: Can not deserialize instance of entityclasses.OpCo out of START_ARRAY token
at [Source: org.glassfish.jersey.message.internal.EntityInputStream@cf5635; line: 1, column: 1]
root cause
org.glassfish.jersey.server.ContainerException: org.codehaus.jackson.map.JsonMappingException: Can not deserialize instance of entityclasses.OpCo out of START_ARRAY token
at [Source: org.glassfish.jersey.message.internal.EntityInputStream@cf5635; line: 1, column: 1]
root cause
org.codehaus.jackson.map.JsonMappingException: Can not deserialize instance of entityclasses.OpCo out of START_ARRAY token
at [Source: org.glassfish.jersey.message.internal.EntityInputStream@cf5635; line: 1, column: 1]
#11 by azhar on August 25, 2014 - 6:34 am
Hello Sir,
This is good tutorial and it help me a lot. But still i m stuck in how to insert a record into mysql using webservices.
#12 by sousou on July 20, 2015 - 3:12 pm
Hello Sir,
My name is FEKIER Ahmed and I greet you. I read carefully This tutorial and it help me a lot ,
my development environment:
• Microsoft SQL Server 2008
• Microsoft’s 2008 Database name : BDD_HABITAT_RURAL
• Microsoft JDBC Driver 4.0 for SQL Server
• NetBeans 8
• GlassFish 4.1
I am using your example steps by steps :
Connecting Java EE RESTful Web Services to Microsoft SQL Server Using NetBeans and GlassFish
still i have a small problem which i dont know yet how to solve :
when click on the ‘entity_classes.decisions’ URI. Choose the HTTP ‘GET()’ request method from the drop-down and click the ‘Test’ button. The service should return a status of :
HTTP Status 500 – Internal Server Error
——————————————————————————–
type Exception report
messageInternal Server Error
descriptionThe server encountered an internal error that prevented it from fulfilling this request.
exception
javax.servlet.ServletException: javax.ejb.EJBException
root cause
javax.ejb.EJBException
root cause
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services – 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Syntaxe incorrecte vers ‘de’.
Error Code: 102
Call: SELECT Adresse, Au profit de, Code bénéficiaire, Code dernier utilisateur, Code produit, Code projet, Code promoteur, Code type construction, Code type travaux, Code utilisateur création, CodifN, Commune, Commune naissance, Daira, date_dec_mhu, date_envo_bordau_cnl, Date PV, date_reception_niveau_cnl, Date création, Date dernière utilisation, Date décision, dateenvoi, Date naissance, Etat décision, Fils de, Montant aide, N° Ordre sur le PV, Nature date naissance, N° etat civil, Nom, Nom de jeune fille, Nom mère, Nom_structure_envoi, N° tranche, numinstruction, observation, Programme, Prénom, Prénom mère, Recours crédit bancaire, ref_dec_mhu_ins, ref_pv_envoi_bordau, Revenu duménage, Revenue mensuel, Revenu mensuel conjoint, Réference Décision notification, Référence PV, Référence liste CNL, Wilaya naissance, Code agence, Code DR, Code wilaya, N° décision FROM BDD_HABITAT_RURAL.dbo.Decisions
Query: ReadAllQuery(referenceClass=Decisions sql=”SELECT Adresse, Au profit de, Code bénéficiaire, Code dernier utilisateur, Code produit, Code projet, Code promoteur, Code type construction, Code type travaux, Code utilisateur création, CodifN, Commune, Commune naissance, Daira, date_dec_mhu, date_envo_bordau_cnl, Date PV, date_reception_niveau_cnl, Date création, Date dernière utilisation, Date décision, dateenvoi, Date naissance, Etat décision, Fils de, Montant aide, N° Ordre sur le PV, Nature date naissance, N° etat civil, Nom, Nom de jeune fille, Nom mère, Nom_structure_envoi, N° tranche, numinstruction, observation, Programme, Prénom, Prénom mère, Recours crédit bancaire, ref_dec_mhu_ins, ref_pv_envoi_bordau, Revenu duménage, Revenue mensuel, Revenu mensuel conjoint, Réference Décision notification, Référence PV, Référence liste CNL, Wilaya naissance, Code agence, Code DR, Code wilaya, N° décision FROM BDD_HABITAT_RURAL.dbo.Decisions”)
root cause
Exception [EclipseLink-4002] (Eclipse Persistence Services – 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.microsoft.sqlserver.jdbc.SQLServerException: Syntaxe incorrecte vers ‘de’.
Error Code: 102
Call: SELECT Adresse, Au profit de, Code bénéficiaire, Code dernier utilisateur, Code produit, Code projet, Code promoteur, Code type construction, Code type travaux, Code utilisateur création, CodifN, Commune, Commune naissance, Daira, date_dec_mhu, date_envo_bordau_cnl, Date PV, date_reception_niveau_cnl, Date création, Date dernière utilisation, Date décision, dateenvoi, Date naissance, Etat décision, Fils de, Montant aide, N° Ordre sur le PV, Nature date naissance, N° etat civil, Nom, Nom de jeune fille, Nom mère, Nom_structure_envoi, N° tranche, numinstruction, observation, Programme, Prénom, Prénom mère, Recours crédit bancaire, ref_dec_mhu_ins, ref_pv_envoi_bordau, Revenu duménage, Revenue mensuel, Revenu mensuel conjoint, Réference Décision notification, Référence PV, Référence liste CNL, Wilaya naissance, Code agence, Code DR, Code wilaya, N° décision FROM BDD_HABITAT_RURAL.dbo.Decisions
Query: ReadAllQuery(referenceClass=Decisions sql=”SELECT Adresse, Au profit de, Code bénéficiaire, Code dernier utilisateur, Code produit, Code projet, Code promoteur, Code type construction, Code type travaux, Code utilisateur création, CodifN, Commune, Commune naissance, Daira, date_dec_mhu, date_envo_bordau_cnl, Date PV, date_reception_niveau_cnl, Date création, Date dernière utilisation, Date décision, dateenvoi, Date naissance, Etat décision, Fils de, Montant aide, N° Ordre sur le PV, Nature date naissance, N° etat civil, Nom, Nom de jeune fille, Nom mère, Nom_structure_envoi, N° tranche, numinstruction, observation, Programme, Prénom, Prénom mère, Recours crédit bancaire, ref_dec_mhu_ins, ref_pv_envoi_bordau, Revenu duménage, Revenue mensuel, Revenu mensuel conjoint, Réference Décision notification, Référence PV, Référence liste CNL, Wilaya naissance, Code agence, Code DR, Code wilaya, N° décision FROM BDD_HABITAT_RURAL.dbo.Decisions”)
root cause
com.microsoft.sqlserver.jdbc.SQLServerException: Syntaxe incorrecte vers ‘de’.
note The full stack traces of the exception and its root causes are available in the GlassFish Server Open Source Edition 4.1 logs.
——————————————————————————–
GlassFish Server Open Source Edition 4.1
Waiting for your help , sir