An introduction to JDBC, JNDI and Spring JDBC with Tomcat

More about myself and what the course is about

• JDBC use is widespread in large companies worldwide

• Actively maintained for 20+ years and counting

• Spring JDBC gives you a “quick win” when dealing with legacy JDBC code

• As an expatriate living abroad, I want access to an online database of all charities (non-profits) that operate in my “home country”.

  • I want to be able to find Charities by their Tax Id

  • I want to be able to search by Charity Categories such as Environment or Education

  • I want to be able to search by Charity Program Types

• OpenJDK 11

• Git & Github

• IntelliJ

  
  

• MariaDB

• Maven 3.6

• Tomcat 8.5

1. Download OpenJDK 11 zip

2. Unzip Archive

3. Setup JAVA_HOME environment variable

4. Add /bin directory to PATH

5. Confirm Java setup in command prompt

1. Download IntelliJ Ultimate Trial

2. Run the .exe

3. Confirm IntelliJ setup with OpenJDK 11

1. Go to Git Downloads page - https://git-scm.com/downloads

2. Select and download Git for Windows 10

3. Run the executable (Setup)

4. Verify Git Bash in Windows 10

1. Prerequisite is that you have a Github account : https://github.com/join

2. Create a Git Repo in Github https://github.com/new

3. Import the Github Repository into IntelliJ

We will select the latest version of Maven: https://maven.apache.org/index.html

• Download the zip file

  • https://maven.apache.org/download.cgi

• Unzip it to our C: drive

• Update our Windows Environment variables

  • https://maven.apache.org/install.html

• Confirm Maven Setup through Command Line

Using Maven with IntelliJ

• Starting with our Github created project

• We will create a Maven POM Root Project Structure in IntelliJ

• Look at Maven Settings in IntelliJ (.m2 & settings.xml)

• We will match IntelliJ Maven with Command Line Maven

• Confirm Maven Setup through Command Line

https://downloads.mariadb.org/mariadb/+releases/

We will select the latest stable MariaDB release:

• We will choose Windows 10

• We will choose .MSI file download

• We will follow the instruction for using MSI

• Run MSI in Windows Explorer

• Confirm setup through command line

1. Create Charity DB using MariaDB command line client

2. Create App User with full privileges on Charity DB

3. Use our POM Root from Github project in IntelliJ

4. Set up DB Connection in IntelliJ with App User

5. Create new maven module named MariaDBConnect

We will select the Tomcat 8 release downloads page:

• We will choose the 64 bit zip core download

• Unzip win64 zip file

• Run Tomcat 8.5 from the command line

• Confirm setup in the Tomcat Admin Web Page

  
  

• Prerequisite is Java 7 or higher (JAVA_HOME)

• Set CATALINA_HOME environment variable

• Scripts: startup.bat and shutdown.bat

• Set up a Tomcat Admin User through the web page Admin Install

In IntelliJ

• From the main menu, select Run | Edit Configurations

• Click + and select Tomcat Configuration type

• We will be using the local Tomcat run configuration

Walkthrough of running the SQL Scripts to setup Maria DB Charity FB, using the SQL Script documentation examples and IntelliJ Data Source Explorer:

Slide A

1. Recreate application user and Charity DB

2. Create Charity Table

3. Create Charity Category Table and relationships

4. Create Charity Program Table and relationships

5. Create Charity Associate Table and relationships

6. Populate Categories and Programs with scripts

Slide B

1. Create Charity Associate Address Table

2. Create Charity Associate Email Table

3. Create Charity Associate Phone Table

4. Create Charity Associate Type Table

Connecting to the MariaDB with JDBC

Establishing a Connection

• Using the DriverManager Class

• Specify the database Connection URLs

1. Create a new Connection Module (JDBC1)

2. Use Maven in IntelliJ

3. Write DriverManager App that gets JDBC Connection

4. Use Junit 5 Test Driver

Use Spring Managed Driver Manager & Connection URLs

1. Create JDBC2 Spring DataSource Module (maven)

2. Define DataSource Class and its Properties in application.xml

3. Retrieve DataSource through Spring Classpath Context in Java Code 4. Use DataSource Object to get a Connection in the Main method.

   
   

Connecting to the MariaDB :

Using JNDI DataSource Objects to Get a Connection

• Registering DataSource Object with Naming Service That Uses JNDI API

• Creating Instance of DataSource Class and Setting its Properties

• Using Deployed DataSource Object

How to set up and configure an embedded Tomcat Server to connect to the MariaDB Charity DB via JNDI that we configured in our Main method Java Code

Using JNDI to Get a JDBC Connection in Embedded Tomcat

• Java Main Method to launch Embedded Tomcat

• Registering DataSource Object with JNDI API

• Using the DataSource Object

Using Spring App Context to Get a JDBC Connection Tomcat

• Java Main Method to launch Embedded Tomcat

• Defining the DataSource in Spring application.xml

• Adding a context listener to web.xml

• Retrieve the Spring Data Source bean in the Servlet

Connecting to the MariaDB :

Using Spring Annotation Config in Embedded Tomcat

• Java Main Method to launch Embedded Tomcat

• Defining the DataSource in Spring Config Class

• Retrieve the Spring DataSource bean in the Servlet

Learn about problems with using Direct JDBC:

• Setting up the Data Source

• Using the DAO Pattern

• Implementing the DAO with direct JDBC

1. Using Spring App Context to Get a JDBC Connection

2. Tomcat Java Main Method to launch Embedded Tomcat

3. Defining the DataSource in Spring application.xml Adding a context listener to web.xml

4. Retrieve the Spring Data Source bean in the Servlet

• Setting up the Connection Pool Data Source

• Using the DAO Pattern

• Using DAO and DataSource as Spring Beans

• Implementing the DAO with direct transactional JDBC

Update the DAO with JDBC for full CRUD capability, using Connection Pooling to get the Data Source and expanding the Charity Object Model to include its Category Types

Using a DAO with Spring JdbcDaoSupport to insert a new Charity and retrieve the Charity from the database

Using Spring JdbcDaoSupport abstract class : (JDBCTemplate)

• Setting up the Data Source

• Extending the DAO Pattern

• Using DAO and DataSource as Spring Beans

• Using DAO with JdbcDaoSupport

Using Spring JdbcDaoSupport with Transactions :

• Setting up the Data Source

• Extending the DAO Pattern with JdbcDaoSupport

• Using DAO and DataSource as Spring Beans

• Using DAO with JdbcDaoSupport

• Setting up the Data Source

• Extending the DAO Pattern

• Using DAO and DataSource as Spring Beans

• Using DAO with JdbcDaoSupport

Using Spring Spring Boot :

• All components come prepackaged

• Using DataSource as Spring Bean

• Using the DAO Implementation as Spring Components

• Using the Service Layer as Components

• Adding a Controller and Template Pages