Importance of JDBC and Servlet to the beginner

Copy and paste the below JDBC Servlet class into Spring STS and run it.

We should learn about Java Servlets just to be prepared, if for nothing else. Why? Suppose you are looking for a job. No doubt many large corporations will deploy a variety of web technology. There will be a sophisticated frontend in Javascript and most likely enterprise level applications on the backend containing the logic needed for all the programs to interrelate. Most notably, these enterprise applications will connect to data so basic knowledge of SQL is also desirable.

Without knowledge of Java, or any other programming language, u have limited opportunities and a lower salary level. But why Java and why Servlet? If we concentrate on just a small segment of Java then we can get our foot in the door. We can, at minimum, say we have familiarity with Java and this goes a long way in an interview by creating the perception that we are well rounded.

So an easy way to do Java is to start with a Servlet and connect to some data (using sql). We are also opening opportunities up into the Data Science area by proceeding this way (a bonus). The JDBC and Servlet APis are where we will begin, along with an SQLite library (the database) and the REST api too. Keep it simple and master only this small segment of Java and u will never regret it. Also it hardly takes any time to learn so ur small effort is well worth the reward. Then u can move onto other programming languages if desired, but don't spread yourself too thin and don't run in 20 different directions.

In my previous tutorial click here I presented an embedded database and a connection object. We inserted some data and viewed it with an old java method. But now we want a Web representation of this program. By web enabling our JDBC we can then host our Web application and refer to it from any javascript frontend using Ajax, Fetch, Axios or ur favorite http client. Seperately refer to my blog for AWS hosting instructions here.

A Java Servlet also needs to be in a Tomcat Container on AWS EC2 or just use Spring STS which embeds a Pivotal Tomcat Server alternative so u don't need a separate Tomcat install like we do in Eclipse. Spring STS with embedded Tomcat is a welcome shortcut because installing Tomcat can be a very big challenge at times.

Http methods, get and post are most popularly used in request and response objects (classes) created in Java. These concepts are very important to grasp because they will popup in all programming languages. Http protocol is literally the backbone of the web and we use some of it in this exercise. It is the REST API.

Here is a small sample, below, of a Java JDBC Servlet. Just drop it right into ur environment and run on the server.

I am using Spring STS with built in Tomcat Server and Servlet API. U can also use Eclipse 2021 EE version for this exercise. Be sure it is the EE version because u will need a dynamic web project for Servlet.

See below for links to our tools. The reason I am using Spring STS is so we can graduate to the next level after learning Servlet and not have to use a different tool.

Here is the code, copy and paste it into a dynamic web project in STS. Please note that after dropping into STS everything is double spaced so u can edit accordingly in ur STS environment for improved readability

PS. u need to import An SQLite Driver/Connector jar file into ur Java webapp. See run instructions in code section.

/*

* Run instructions...we are using Spring STS 3.9.17 jdk11 version

* drop sqlite jar file into webapp path lib as follows

* in STS project explorer, navigate to src > main > webapp > WEB-INF > lib > jar file

* right click on java file and then run as, then run on server

* default path for servlet is localhost:8080 which will appear after run

* after running on server, go to

* C:\Users\rickd\Downloads\spring-tool-suite-3.9.17.RELEASE-e4.20.0-win32-x86_64\sts-bundle\sts-3.9.17.RELEASE (ur path will differ)

* and the test4.db will appear embedded in ur app

* for manual manipulation of sqlite data find my tutorial 4 which will show u

* how to do sql commands from a command line prompt click here for tutorial 4

* I highly recommend this part for learning basics of sql

* a good first exercise would be to manually delete the data we just created from cmd prompt

Also, while servlet is still running, be sure to open a local browser and type in http://localhost:8080/Servlet_Test/Transform_Main_to_Servlet to view ur servlet output on a localhost. (note Servlet_Test was my project name in Spring sts so urs may be different) If u want to u can run an instance of the Pivotal Tomcat server in AWS and host ur own website publically. Or better yet, if u want to go serverless, instead of running a server you can dump java servlets altogether and run ur logic in AWS Lambda. I wrote a short piece on this topic at my main website.

*/

package test2;

import java.sql.*; //this import contains the jdbc classes and methods

//servlet imports

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

// IO imports

import java.io.PrintWriter;

import java.io.IOException;

/* JDBC micro tutorial for beginners...connect to data using SQLite

* this program will create, embed and display sqlite table

* be sure u add sqlite driver to ur build path..see separate instructions for this

*

* jdbc objects used

* connection object = cn

* driver manager object....get connection is the driver manager method

* prepared statement - select, create, insert, update (not covered here)

* result set object

* print writer object

* for loop array

*

* note, I transformed my original plain java class to a servlet (previous jdbc tutorial)

* by getting rid of main method and including the service method

*

*/

//this was the original Main Class

//public class JDBC {

//this was the original Main Method

//public static void main(String[] args) {

//instead of initializing Main method, lets initialize a servlet

@WebServlet("/Transform_Main_to_Servlet") //need to register servlet, ps old way to do this was in web.xml

public class Transform_Main_to_Servlet extends HttpServlet {

Connection cn = null;

String col1,col2,col3;

Statement stmt;

PreparedStatement stmt2;

ResultSet rs;

PrintWriter out;

//this is the servlet service method..we use request and response objects

public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

//create a jdbc driver instance..be sure sqlite jar file is in WEB-INF lib

loadDriver(); //this refers to the load driver method below...ie, run method below then proceed with try block

//now create a table in test4.db

try {

stmt = cn.createStatement();

String sql = "CREATE TABLE IF NOT EXISTS test_table3"

+ "("

+ " column1 TEXT," // in other dbs we use column, datatype, length, null or not null, primary key

+ " column2 TEXT,"

+ " column3 INTEGER" //i left this as integer to show below that it also takes a string

+ ")";

//note that for sqlite, data types are really not needed (sqlite assumes txt is used)

//but for other sql dbs like MySQL or PostgreSQL we need to use type syntax

stmt.executeUpdate(sql);

stmt.close();

//now insert some data

//declare more variables

String sql2;

int i; //each of 5 rows

int j=1;

String value;

//for loop to insert 5 rows of data

for(i = 0; i < 5; i ++){

//code to insert 1 row of data to sqlite database..for each pass insert a row....do 5 passes then loop stops

stmt2 = cn.prepareStatement("insert into test_table3 (column1,column2,column3) values (?,?,?)");

stmt2.setInt(1,j); //note that we setInt instead of set string, but as i said above sqlite assumes txt

//I am setting int here just as a reminder if u decide to use mysql or other dbs

stmt2.setString(2,"val2");

stmt2.setString(3,"value"+ String.valueOf(j)); //concatenation of of a string +string val of an integer

int result3 = stmt2.executeUpdate();

j = j+1; //increment integer by 1 for each pass

//note: for other insertions of data we can also read from an existing file with double for loop

//not covered here

} //end of for loop

System.out.println("table created and data inserted");

//create pw to write result to html

//this was the old way

//pw = new PrintWriter(new FileWriter("C:/Users/rickd/Desktop/files/html_table.html")); //my local path to html file

//this is the servlet way of initializing PrintWriter

out = new PrintWriter(response.getOutputStream());

//add html to print writer

//start html

out.println("<html><head><title>HTML table Example</title></head><body text='black' > ");

out.println("<table width='450'>");

out.println("<TH>ID</TH><TH>value</TH><TH>some other value</TH>"); //table header

// then get results

rs = stmt.executeQuery("Select * from test_table3");

//while loop....while we have records, pass thru each one

while(rs.next()) {

//assign 'getString from db' to local strings col1, col2 and col3

col1 = rs.getString("column1");

col2 = rs.getString("column2");

col3 = rs.getString("column3");

//for each pass append each line to html table

out.println("<tr>");

out.println (

"<td width='100'>"+"<font color='blue'>"+col1+"</font>"+"</td >"

+"<td width='100'>"+col2+"</td >"

+"<td width='100'>"+col3+"</td >"

);

out.println("</tr>");

System.out.println(col1 +col2 +col3); //display result in console

} //end of result set while loop

out.println("</table>");

out.println("</body></html>");

stmt2.close();

out.close();

} //end of try block

// catch any exceptions occurring from sql query

catch (SQLException e) {

System.out.println(" Exceptions : " + e.toString());

} //end catch block

//also need to catch io exception because pw is in io package

catch (IOException e) {

System.out.println("An I/O error occurs: " + e.getMessage());

} //end catch block

//disconnect from the database

try {

if(cn != null) {

cn.close();

cn = null;

}

}

catch(SQLException e) { }

} //end service method

//load driver method

//no need to use static reference in a servlet, this is only used in main method

//public static void loadDriver() {

public void loadDriver() {

try {

Class.forName("org.sqlite.JDBC");

System.out.println("The Driver has been loaded successfully!");

} catch (Exception E1) {

System.out.println("Unable to load the Driver!");

System.out.println("Exceptions:" + E1.toString());

System.exit(1);

}

System.out.println("Establishing connection to Server");

//Establishing the connection with the test4.db database

try {

cn = DriverManager.getConnection("jdbc:sqlite:test4.db");

System.out.println("Connection to Server was Established");

} catch (Exception E2) {

System.out.println(" Exceptions : " + E2.toString());

}

} //end load driver method

} //end of class


LINKS TO TOOLS

for jdk11 if u don't already have it installed..I am using Open JDK not oracle JDK (u can use either)

https://adoptopenjdk.net/installation.html#windows-msi

Download the .msi file for the Windows MSI Installer Package (JDK version not JRE)

Double click the installer package in downloads section

files will be in C:\Program Files\Eclipse Adoptium\jdk-11.0.14.9-hotspot

Go to windows environment variables and set JAVA_HOME to above path, then reboot computer

For Spring STS go to this link and download the 3.9.17 version for windows (uses jdk11)

https://github.com/spring-projects/toolsuite-distribution/wiki/Spring-Tool-Suite-3

Extract all from zip archive and note that warning 'file name is to long' occasionally appears ....just skip over all occurrences of this....the install will still work

Then open STS and create dynamic web project with the above servlet example


Download the sqlite jar file from this link click here


Best of Luck and contact me if any questions


go to howtolearnJava Homepage for more