Programming Tutorials

Java WebService connected to Database

By: Emiley J in WebServices Tutorials on 2013-07-10  

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 whether you have properly added the mysql driver to your classpath

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