Welcome!

Java IoT Authors: Elizabeth White, Pat Romanski, Liz McMillan, William Schmarzo, Stackify Blog

Related Topics: Java IoT

Java IoT: Article

Delivering JDBC-Based Applications

Delivering JDBC-Based Applications

Introduction
In this article, we take a look at using JDBC to build applications. Internet technology is changing so rapidly that some of the methods used today will become obsolete tomorrow. We are taking an approach that combines Java network programming and JDBC programming. Programming in JDBC is taking a step backward when you have mature 4GL tools like Sybase Powerbuilder. Programming in JDBC is similar to programming in any low level database library like ODBC or Sybase CT-LIB.

There is more than one way to deploy applications and applets using JDBC. The first approach is by using the JDBC-ODBC bridge that is part of JDK 1.1. You have to install ODBC Drivers and set the ODBC datasource in each client. If you are willing to do this existing client server tools will do a better job than Java. The second approach is to use a JDBC driver that is 100 Percent Java compliant. For writing Java applications, this method will work fine, but not for Java applets. Applets can not make arbitrary connection to other servers. You have to run your database server in the same machine where the Web server is running. This article will help you write Java applets or applications using JDBC without any of the above mentioned restrictions.

What are the Advantages?
This implementation follows a 3-tier client-server model. JDBC Client sends a request to JDBC server which processes the request, communicates to the database server and sends back the reply. Compatible with all Java enabled browsers. It's secure because the database server is not exposed. You can configure number of concurrent users allowed at any point of time. With little modification you can dynamically increase and decrease number of JDBC server listening for request or totally redirect the request to a different web server. You can also attach your own encryption and decryption routine before communicating with a remote client. JDBC server runs on the server side so you have total freedom to use any JDK, any ODBC driver and Java native methods. You can batch your request or can have real-time connection like any other client-server application.

There are some limitations in this implementation. If you want to have multiple connection you have to initialize multiple driver's in the client side. It will not handle blob or image data type even though it's very easy to implement one, by saving the image in the server and sending the filename back as URL. You can use the same method to transfer the whole ResultSet.

Architecture
There are three different functional pieces. Port allocator (PA) is a Java based server that listens on a predefined port. JDBC server listens for connection from client. JDBC client which makes the request. When client makes a request to Port Allocator it will send back a JDBC server port that is currently free. From that point onwards, JDBC client talks only to the JDBC server. Client sends a single byte command to server and waits for reply. The server sends back a status code and byte or string value. Commands can have arguments usually a byte or a string. While processing clients request, if server encounters an exception it converts the exception to a string and sends back an error code and the string to the client.

Let us look into the details. There are four client side classes RDriver, RConnection, Rstatement, RResultSet for connecting and fetching data. These are very similar to JDBC interfaces Driver, Connection, Statement, ResultSet. It does not implement these interfaces. JDBC client initializes the RDriver then creates a TCP socket to port allocator (PA) and gets a port to communicate to a JDBC server. It also sends command to initialize DriverManager in the server if everything is successful. This enables the client to connect directly to the JDBC server using RDriver.connect(username, password) and create a database connection. You only need username and password to create a database connection hiding all server details in the JDBC server code. You can call createStatement() method to create a RStatement() after successfully establishing a database connection. Send the SQL string to server using executeQuery function. It will create a ResultSet in the server and RResultSet in the client. Use executeUpdate() function for update and insert SQL statements. Access the ResultSet values using next() and getString() or getInt().

One of the advantages of this architecture is that all conversation between client and server happens as bytes and strings. If the client dies for some reason Driver.detach() will cleanup the connection, but if an exception occurs you have to take care of it. Rdriver has an overloaded finalize method which calls Driver.detach().

The example here shows how to connect to a Sybase SQL server using beta version of Jconnect. Before running the examples you may need to change hostname, port and any other parameters it may need in your environment.

Table 1 shows the commands, arguments and return code.

JDBC Protocol Implementation
JDBC Protocol is a layer above the Java socket layer. It hides the Java socket layer, but you have to still catch exceptions from this layer. Protocol structure follows the same structure as any other standard protocols (FTP, TELNET). JDBC protocol does session control by sending acknowledgment or negative acknowledgment after each request from client. Java takes care of presentation of data. This protocol fits in the top layers of OSI model. Like most other protocols this protocol has series of commands and expected return values. Most importantly these commands are not part of any standards. One other difference is it has only one path to the server, unlike FTP which uses a command port (21) and a data port (20).

All commands are single byte followed by a byte or string. In special cases it is more than one string. For each command sent from client JDBC server replies with a return status of SUCCESS or FAIL. If the return status from the server is SUCCESS it returns either string, byte or no value depending on type of command. If an error occurs in the server the return status is FAIL server then sends a SQLExeption string to the client.

Table 2 explains the protocol implementation.

More Stories By Paul Ananth

Paul Ananth works with Barclays Global Investors, San Francisco, as a software engineer. He is a senior member in the client/server development team, specializing in building object-oriented software using C++, Java, PowerBuilder 5.0 and PFC.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.


@ThingsExpo Stories
SYS-CON Events announced today that N3N will exhibit at SYS-CON's @ThingsExpo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. N3N’s solutions increase the effectiveness of operations and control centers, increase the value of IoT investments, and facilitate real-time operational decision making. N3N enables operations teams with a four dimensional digital “big board” that consolidates real-time live video feeds alongside IoT sensor data a...
Mobile device usage has increased exponentially during the past several years, as consumers rely on handhelds for everything from news and weather to banking and purchases. What can we expect in the next few years? The way in which we interact with our devices will fundamentally change, as businesses leverage Artificial Intelligence. We already see this taking shape as businesses leverage AI for cost savings and customer responsiveness. This trend will continue, as AI is used for more sophistica...
Real IoT production deployments running at scale are collecting sensor data from hundreds / thousands / millions of devices. The goal is to take business-critical actions on the real-time data and find insights from stored datasets. In his session at @ThingsExpo, John Walicki, Watson IoT Developer Advocate at IBM Cloud, will provide a fast-paced developer journey that follows the IoT sensor data from generation, to edge gateway, to edge analytics, to encryption, to the IBM Bluemix cloud, to Wa...
What is the best strategy for selecting the right offshore company for your business? In his session at 21st Cloud Expo, Alan Winters, U.S. Head of Business Development at MobiDev, will discuss the things to look for - positive and negative - in evaluating your options. He will also discuss how to maximize productivity with your offshore developers. Before you start your search, clearly understand your business needs and how that impacts software choices.
SYS-CON Events announced today that Massive Networks, that helps your business operate seamlessly with fast, reliable, and secure internet and network solutions, has been named "Exhibitor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. As a premier telecommunications provider, Massive Networks is headquartered out of Louisville, Colorado. With years of experience under their belt, their team of...
SYS-CON Events announced today that Fusic will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Fusic Co. provides mocks as virtual IoT devices. You can customize mocks, and get any amount of data at any time in your test. For more information, visit https://fusic.co.jp/english/.
SYS-CON Events announced today that Enroute Lab will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Enroute Lab is an industrial design, research and development company of unmanned robotic vehicle system. For more information, please visit http://elab.co.jp/.
SYS-CON Events announced today that MIRAI Inc. will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. MIRAI Inc. are IT consultants from the public sector whose mission is to solve social issues by technology and innovation and to create a meaningful future for people.
SYS-CON Events announced today that Mobile Create USA will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Mobile Create USA Inc. is an MVNO-based business model that uses portable communication devices and cellular-based infrastructure in the development, sales, operation and mobile communications systems incorporating GPS capabi...
There is huge complexity in implementing a successful digital business that requires efficient on-premise and cloud back-end infrastructure, IT and Internet of Things (IoT) data, analytics, Machine Learning, Artificial Intelligence (AI) and Digital Applications. In the data center alone, there are physical and virtual infrastructures, multiple operating systems, multiple applications and new and emerging business and technological paradigms such as cloud computing and XaaS. And then there are pe...
SYS-CON Events announced today that Interface Corporation will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Interface Corporation is a company developing, manufacturing and marketing high quality and wide variety of industrial computers and interface modules such as PCIs and PCI express. For more information, visit http://www.i...
SYS-CON Events announced today that Keisoku Research Consultant Co. will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Keisoku Research Consultant, Co. offers research and consulting in a wide range of civil engineering-related fields from information construction to preservation of cultural properties. For more information, vi...
SYS-CON Events announced today that SIGMA Corporation will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. uLaser flow inspection device from the Japanese top share to Global Standard! Then, make the best use of data to flip to next page. For more information, visit http://www.sigma-k.co.jp/en/.
SYS-CON Events announced today that B2Cloud will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. B2Cloud specializes in IoT devices for preventive and predictive maintenance in any kind of equipment retrieving data like Energy consumption, working time, temperature, humidity, pressure, etc.
Agile has finally jumped the technology shark, expanding outside the software world. Enterprises are now increasingly adopting Agile practices across their organizations in order to successfully navigate the disruptive waters that threaten to drown them. In our quest for establishing change as a core competency in our organizations, this business-centric notion of Agile is an essential component of Agile Digital Transformation. In the years since the publication of the Agile Manifesto, the conn...
While some developers care passionately about how data centers and clouds are architected, for most, it is only the end result that matters. To the majority of companies, technology exists to solve a business problem, and only delivers value when it is solving that problem. 2017 brings the mainstream adoption of containers for production workloads. In his session at 21st Cloud Expo, Ben McCormack, VP of Operations at Evernote, will discuss how data centers of the future will be managed, how th...
SYS-CON Events announced today that NetApp has been named “Bronze Sponsor” of SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. NetApp is the data authority for hybrid cloud. NetApp provides a full range of hybrid cloud data services that simplify management of applications and data across cloud and on-premises environments to accelerate digital transformation. Together with their partners, NetApp em...
SYS-CON Events announced today that Nihon Micron will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Nihon Micron Co., Ltd. strives for technological innovation to establish high-density, high-precision processing technology for providing printed circuit board and metal mount RFID tags used for communication devices. For more inf...
SYS-CON Events announced today that Suzuki Inc. will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Suzuki Inc. is a semiconductor-related business, including sales of consuming parts, parts repair, and maintenance for semiconductor manufacturing machines, etc. It is also a health care business providing experimental research for...
SYS-CON Events announced today that Ryobi Systems will exhibit at the Japan External Trade Organization (JETRO) Pavilion at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Ryobi Systems Co., Ltd., as an information service company, specialized in business support for local governments and medical industry. We are challenging to achive the precision farming with AI. For more information, visit http:...