Java WebService connected to Database

By: Emiley J  

This tutorial takes one step forward in building Java Web Services by connecting your web service to a database and returning a value from a MySQL database.

If you have completed the previous tutorial on creating your first java web service then you will already have met the minimum requirements and created the required folders.

If you already have the javasamples folder then goto that folder or open the command prompt and create a folder named "javasamples" ( md javasamples )
Next cd javasamples to goto that folder. Now create another folder named "two" ( md two )
Next create a file named Users.java ( notepad Users.java )
Copy the below code in that file and save it.

package javasamples.two;  

import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.soap.SOAPBinding;
import javax.jws.soap.SOAPBinding.Style;

@WebService
@SOAPBinding(style = Style.RPC) 
public interface Users {

    @WebMethod int getUserCount();
}

Next create a file named UsersImpl.java ( notepad UsersImpl.java )
Copy the below code in that file and save it. Remember to change the dbname in the dbUrl and the username and password based on your db username and password. This file just connects to the database and counts the number of records in a table named Users. You can change the SQL query as you wish.

package javasamples.two; 
import java.util.Date;
import javax.jws.WebService;
import java.sql.*;
import javax.sql.*;

@WebService(endpointInterface = "javasamples.two.Users")

public class UsersImpl implements Users {

    public int getUserCount() { 
		int numusers = 0;
		String dbUrl = "jdbc:mysql://localhost/yourdbname";
		String dbClass = "com.mysql.jdbc.Driver";
		String query = "Select count(*) FROM users";
		String userName = "root", password = "yourdbpassword";
		try {

		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection (dbUrl, userName, password);
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(query);

		while (rs.next()) {
			numusers = rs.getInt(1);
			} //end while
			con.close();
		} //end try

		catch(ClassNotFoundException e) {
			e.printStackTrace();
		}

		catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			
			return numusers;
		}

	}
}

Now you can check whether everything is fine by compiling.
First go to the folder above javasamples
Next compile the two files by executing this command ( javac javasamples/two/*.java )
If there are no errors then congratulate yourself. You have just developed a webservice that returns the system time. Its a time server.

If you get a compile error, then check this solution for package javax.jws does not exist
If you getting java.lang.ClassNotfoundException:com.mysql.jdbc.driver error then check the suggestions in this answer.

Normally, in a production system, you will publish the web service in a server such as GlassFish, Jboss, Websphere etc.. But while testing and in development, we will just create a simple java application that will publish this web service in localhost.

Go to the javasamples/two folder.
Now create a new java file named UsersPublisher.java ( notepad UsersPublisher.java )
And copy the below code into it.

package javasamples.two;

import javax.xml.ws.Endpoint;

public class UsersPublisher {

    public static void main(String[ ] args) {

      // 1st argument is the publication URL
      // 2nd argument is an SIB instance

      Endpoint.publish("http://127.0.0.1:9876/two", new UsersImpl());

    }

}

Now you can check whether everything is fine by compiling.
First go to the folder above javasamples
Next compile the two files by executing this command ( javac javasamples/two/*.java )
If there are no errors then congratulate yourself. You have just developed a webservice that connects to MySQL database server and retrieves a value from it.

Finally, you need to start your UsersPublisher class. You can do this like ( java javamples.two.UsersPublisher ). That't It. You have created your first java web service and publish it. You can double check whether it works by opening your brower and going to http://127.0.0.1:9876/two

You can now create a java client to connect to your new web service and get the values from this web service.

Go to the javasamples/two folder.
Now create a new java file named UsersClient.java ( notepad UsersClient.java )
And copy the below code into it.

package javasamples.two;

import javax.xml.namespace.QName;
import javax.xml.ws.Service;
import java.net.URL;

class UsersClient {

    public static void main(String args[ ]) throws Exception {

        URL url = new URL("http://localhost:9876/two?wsdl");


        QName qname = new QName("http://two.javasamples/", "UsersImplService");

        // Create, in effect, a factory for the service.
        Service service = Service.create(url, qname);
        // Extract the endpoint interface, the service "port".

        Users eif = service.getPort(Users.class);
        System.out.println(eif.getUserCount());
  
   }

}

Now compile and run this UsersClient class in a new command prompt window as follows: (Make sure the UsersPublisher is running in another command prompt) javac javasamples/two/UsersClient.java
java javasamples.two.UsersClient




Archived Comments

1. I love your blog.. very nice colors & theme. Did you create this
website yourself or did

View Tutorial          By: alquiler de embarcaciones en menorca Sin titulacion at 2017-07-08 09:16:15

2. how can we change this for oracle 11g
View Tutorial          By: Naveen at 2017-06-12 11:54:58

3. Thanks for your nice sharing. If possible, can you advice me how I can host the web service on Amazo
View Tutorial          By: William at 2016-03-21 08:34:09

4. I am using OracleDriver and am facing this error on the publisher prompt while running my client.
View Tutorial          By: Jajoko at 2015-12-01 11:54:31

5. this tutorial is awesome.. solved my doubt in seconds.. :) thank u so much..
View Tutorial          By: Natasha at 2015-07-29 09:03:42

6. Hi, i tried this tutorial with my own database and when i like to put some data into a table with in
View Tutorial          By: Dejan at 2015-03-18 13:06:27

7. hi
Instead of sending a int type pass an array or object or a vector to use all the values re

View Tutorial          By: Sanju at 2014-12-04 08:51:18

8. How to return values when there may be muliple rows returned by select * query of database
View Tutorial          By: asd at 2013-11-22 01:24:47

9. Hi ngocanh, You are right. The client has to be in different package. It is placed in the same packa
View Tutorial          By: Emiley J at 2013-07-14 05:51:35

10. This is a cool tutorial ! :) But I think in this sample client consume and service are in same packa
View Tutorial          By: ngocanh at 2013-07-11 02:33:06


Most Viewed Articles (in WebServices )

Latest Articles (in WebServices)

Comment on this tutorial