Java and Database Class


Course Outline

 

Course Objective:
Learn the fundamental core Java coding concepts with relational database fundamentals and hands-on programming to build data-driven Java applications.

Main topics:
Java review, SQL, relational design, JDBC, transactions, basic ORM.

Target audience
-Undergraduate CS / IT majors in their first or second year who have completed an introductory Java course and are beginning to work with real-world data.
-Students in related programs (information systems, software development, data analytics) who want to build data-driven applications in Java for web, desktop, or backend services.
-Working professionals or career changers with some Java exposure who need to integrate Java applications with relational databases for enterprise or small business projects.

Tools Involved:
JDK, an IDE (IntelliJ/Eclipse), MySQL Database, and JDBC driver.

Prerequisites
Be familiar with the what is the Java Programming Language.
Have an interest in Java and Programming concepts like: variables, data types, operators, control flow, arrays, methods, and basic classes/objects.
-We will explain these concepts during the class.
Be familiar with the idea of using an IDE (e.g., IntelliJ, Eclipse, or NetBeans) to write, run, and debug Java programs.
-We will be doing hands-one coding practice with real-world examples during this course.

 Learning outcomes
By the end of the course, students will be able to:
Explain relational database concepts and design simple schemas using primary/foreign keys and normalization.
Write SQL code, to create tables and perform CRUD (create, read, update, delete) queries and operations.
Connect Java applications to a relational DB using JDBC drivers and the java SQL API.
Use SQL Statements and PreparedStatements safely, including protection against SQL injection.
Manage transactions (commit/rollback) and handle JDBC errors and exceptions.
Build a small multi-class Java application that persists and retrieves data from a database.

 

Weekly outline (14 weeks)
Introduction & setup
Course goals, grading, and project overview.
Install JDK, IDE, database server (e.g., MySQL), and client tools; test environment with a simple Java “Hello DB” plan.
 
 
Java review for data apps
Quick review of Java syntax, methods, classes, collections (ArrayList, HashMap), and exceptions handling.
Relational database fundamentals: Tables, rows, columns, keys, relationships; relational data model.
Conceptual and logical design: ER diagrams, mapping to tables, basic normalization up to 3NF.
 
Introduction to JDBC
What JDBC is, driver types, and how JDBC abstracts database access in Java.
Steps to connect: loading driver (if needed), DriverManager, Connection, URL, username/password.
 
Executing queries with JDBC
-Using Statement and ResultSet to execute SQL and read results.
-Mapping rows to Java objects; closing resources (try-with-resources) and basic exception handling.
 
 
Core SQL and CRUD
-Simple SELECT queries with WHERE, ORDER BY, LIMIT; lab exercises against a sample schema.
-DDL: CREATE, ALTER, DROP; DML: INSERT, SELECT, UPDATE, DELETE.
-Primary/foreign keys, NOT NULL, UNIQUE, CHECK constraints; basics of indexes.
 
Week 8: Prepared statements and security
PreparedStatement for parameterized queries; benefits for performance and security.
SQL injection examples and how PreparedStatement and validation mitigate them.
 
Updates, transactions, and batch
-INSERT, UPDATE, DELETE via JDBC; getGeneratedKeys for auto IDs.
-Transactions: auto-commit, commit, rollback, isolation concept; simple batch operations.
 
Stored procedures and metadata
Calling stored procedures and functions via CallableStatement.
Database and driver metadata (DatabaseMetaData, ResultSetMetaData) for introspection.
 
 
Designing a simple DAO (Data Access Object) 
layer to separate persistence from business logic.
Connection pooling concepts and configuration using a common pool (e.g., HikariCP or app-server pool).
 
Intro to ORM and JPA (optional light coverage)
-Motivation for ORM; basic concepts of entities, mappings, and repositories.
-Simple example using JPA/Hibernate annotations for one or two entities if time/tools permit.
 
Optional topics: views, triggers, simple NoSQL overview, or REST API exposing database-backed endpoints.
 
Presentations and review
-Student project demos and code walkthroughs.
-Review key concepts; comprehensive interview questions, exercise with Java, SQL, and JDBC together.
 
Assessments
Weekly labs: SQL practice and small JDBC programs.
Midterm exam: relational model, SQL, basic JDBC steps.
 
Final project: 
multi-class Java application with a relational schema, SQL scripts, and JDBC-based data layer (plus short written design description).