Importance of Java Servlet for the beginner. The JDBC Servlet.

/*

* 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 SQLite 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