Master SQL Joins: Top 4 Types Explained

How SQL might lie to you. The short version: SQL equality checks ignore trailing spaces. Java doesn't. Your DB client hides them. If you don't sanitize at system boundaries, dirty data will silently break your pipeline while tests pass. The investigation trail: Steps 1-2 - No errors, data "looked" perfect. A batch service produced zero output with no exceptions, just a log: 0 records processed. The natural assumption was an empty input. But a direct query WHERE CATEGORY = 'Active' returned thousands of rows. Even a DISTINCT query check looked clean. The database said: the data is here and it's fine. Steps 3-4 - The code was fine. The test data wasn't. Since data existed, I suspected the Java code. But the logic was a simple "Active".equals(j.getCategory()). So I seeded the integration tests, copying rows straight from the DB client's UI grid. Every test passed. What I only realized later: that was the contamination point. DB clients silently strip trailing spaces on display, so I had handed the test suite pre-sanitized strings. Step 5 - Inspecting the raw payload. If the DB had data and the code worked, something in between was wrong. Checking the raw JSON payload revealed it: the value wasn't "Active" it was "Active ". Every field had trailing spaces. This was a VARCHAR column, VARCHAR doesn't pad, so the spaces had been physically inserted by an upstream ETL. Step 6 - Why did the DB hide this? Per the ANSI SQL standard, when comparing strings of unequal length with =, the shorter string is space-padded to match. So 'Active ' = 'Active' evaluates to TRUE. The database wasn't lying exactly it was following spec. But the spec is surprising. Step 7 - The full delivery chain: DB stores dirty VARCHAR strings JDBC extracts the exact dirty string Spring JdbcTemplate maps it directly to the DTO Jackson serializes it verbatim No layer sanitizes. But "Active".equals("Active ") returns FALSE. Every record was silently filtered out. Key takeaways: 1️⃣ SQL and Java equals() speak different languages. SQL space-pads for comparison; Java is strictly character-by-character. 2️⃣ Standard SQL diagnostics lie. DISTINCT, =, and GROUP BY are all space-insensitive. Detect dirty data with LENGTH(col) != LENGTH(RTRIM(col)). 3️⃣ DB clients sanitize display output. UI-copied test data will pass against clean strings while prod fails against dirty ones. 4️⃣ Trim at the boundary, not in the logic. Configure Jackson or your RowMapper to trim at deserialization don't scatter .trim() calls across business logic. Over to you: What is the most frustrating "silent bug" you've chased down? Where do you enforce data sanitization the DB, the API boundary, or business logic? #Java #DB2 #SQL #JDBC #Spring #JdbcTemplate #H2 #BDD #Debugging #DataEngineering #BackendEngineering

🚀 Day 3 of My Advanced Java Journey – Mastering CRUD Operations in JDBC Today, I implemented one of the most important concepts in backend development — CRUD operations using JDBC. 🔹 What is CRUD? CRUD stands for: Create → Insert data Read → Fetch data Update → Modify existing data Delete → Remove data 🔹 1. Create (INSERT) Used to add records into the database. ✔️ Key concept: Using PreparedStatement for inserting values safely. String sql = "INSERT INTO employees(name, designation, salary) VALUES (?, ?, ?)"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, "Vamsi"); ps.setString(2, "Software Engineer"); ps.setDouble(3, 60000); ps.executeUpdate(); 🔹 2. Read (SELECT) Used to retrieve and display data. ✔️ Key concept: Using ResultSet to iterate through records. Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM employees"); while(rs.next()){ int id = rs.getInt("id"); String name = rs.getString("name"); String designation = rs.getString("designation"); double salary = rs.getDouble("salary"); } 🔹 3. Update (UPDATE) Used to modify existing records. String sql = "UPDATE employees SET salary = ? WHERE id = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setDouble(1, 65000); ps.setInt(2, 1); ps.executeUpdate(); 🔹 4. Delete (DELETE) Used to remove records from the database. String sql = "DELETE FROM employees WHERE id = ?"; PreparedStatement ps = conn.prepareStatement(sql); ps.setInt(1, 1); ps.executeUpdate(); 🔍 What I explored beyond the session Why PreparedStatement is preferred over Statement (prevents SQL Injection 🔐) Difference between executeQuery() and executeUpdate() Importance of handling exceptions (SQLException) Closing resources (Connection, Statement, ResultSet) to avoid memory leaks 💡 CRUD operations form the core of any real-world application, from simple apps to enterprise systems. 🙌 Special thanks to the amazing trainers at TAP Academy: kshitij kenganavar Sharath R MD SADIQUE Bibek Singh Hemanth Reddy Vamsi yadav Harshit T Ravi Magadum Somanna M G Rohit Ravinder TAP Academy 📌 Learning in public. Building consistency every day. #Java #AdvancedJava #JDBC #BackendDevelopment #LearningInPublic #VamsiLearns

🚀 **JDBC-Based Database Operations Project – My Learning Experience** I recently worked on a **Java JDBC project** where I implemented end-to-end database operations for managing course details. This project gave me a strong foundation in understanding how Java applications directly interact with databases. 🔹 Implemented complete **CRUD operations**: * Create table dynamically * Insert multiple records using **batch processing** * Retrieve and display records * Update course details * Delete records based on user input 🔹 Key Highlights: * Used **PreparedStatement** for secure and efficient queries * Implemented **batch execution** to optimize multiple insert operations * Handled database connectivity using **DriverManager** * Built a **menu-driven console application** for user interaction * Practiced proper **resource management** (closing connections, statements, result sets) 💡 What I learned: * Core concepts of **JDBC architecture** * Difference between **Statement vs PreparedStatement** * Importance of preventing SQL Injection * How real-time database operations are performed in Java * Writing cleaner and more structured database code ⚙️ Tech Stack: * Java * JDBC * MySQL This project helped me understand the fundamentals behind database connectivity before moving to advanced frameworks like **Spring JDBC** and **Hibernate ORM**. You can connect with me through my GitHub link 🔗https://lnkd.in/gFhdXmZs Looking forward to building more efficient and scalable backend systems 🚀 #Java #JDBC #MySQL #BackendDevelopment #Database #LearningJourney #Freshers #SoftwareDevelopment #GlobalQuestTechnologies

🚀 Day 4 of My Advanced Java Journey – PreparedStatement in JDBC Today, I learned one of the most important concepts in JDBC — PreparedStatement, which makes database operations more secure and efficient. 🔹 What is PreparedStatement? A PreparedStatement is used to execute SQL queries with dynamic values using placeholders (?). It helps in writing cleaner, reusable, and secure database code. 🔹 Steps to Use PreparedStatement 1️⃣ Load the Driver Load the JDBC driver class. 2️⃣ Establish Connection Connect to the database using URL, username, and password. 3️⃣ Create PreparedStatement Write SQL query with placeholders (?): String query = "INSERT INTO employee (id, name, desig, salary) VALUES (?, ?, ?, ?)"; PreparedStatement pstmt = con.prepareStatement(query); 4️⃣ Set Parameter Values Assign values using setter methods: pstmt.setInt(1, id); pstmt.setString(2, name); pstmt.setString(3, desig); pstmt.setInt(4, salary); 5️⃣ Execute Query int rows = pstmt.executeUpdate(); 🔹 Batch Processing (Multiple Inserts) Used to insert multiple records efficiently in one go. do { pstmt.setInt(1, scan.nextInt()); pstmt.setString(2, scan.next()); pstmt.setString(3, scan.next()); pstmt.setInt(4, scan.nextInt()); pstmt.addBatch(); System.out.println("Add more? (yes/no)"); s = scan.next(); } while(s.equalsIgnoreCase("yes")); int[] result = pstmt.executeBatch(); 🔹 Important Methods setInt(), setString(), setFloat() → Set values executeUpdate() → Insert/Update/Delete addBatch() → Add queries to batch executeBatch() → Execute all at once 🔍 What I explored beyond the session PreparedStatement prevents SQL Injection attacks 🔐 Precompiled queries improve performance Difference between Statement and PreparedStatement Importance of closing resources (Connection, PreparedStatement) Using try-with-resources for better memory management 💡 PreparedStatement is a must-know concept for writing secure and optimized database applications in Java. 🙌 Special thanks to the amazing trainers at TAP Academy: kshitij kenganavar Sharath R MD SADIQUE Bibek Singh Vamsi yadav Hemanth Reddy Harshit T Ravi Magadum Somanna M G Rohit Ravinder TAP Academy 📌 Learning in public. Improving every single day. #Java #AdvancedJava #JDBC #PreparedStatement #BackendDevelopment #LearningInPublic #VamsiLearns

🔹 What is @Transactional? @Transactional ensures that a set of database operations are executed as a single unit of work. 👉 Either: ✅ All operations succeed (COMMIT) ❌ If any fails → everything is rolled back (ROLLBACK) 💡 Scenario: Transfer money from one account to another. ❌ Issue: Money deducted Deposit failed → data inconsistency ✅ With @Transactional (Solution) Java @Service public class BankService { @Autowired private AccountRepository accountRepository; @Transactional public void transfer(Long fromId, Long toId, double amount) { Account from = accountRepository.findById(fromId).get(); Account to=accountRepository.findById(toId).get();from.setBalance(from.getBalance() - amount); if (amount > 10000) { throw new RuntimeException("Limit exceeded"); // simulate failure } to.setBalance(to.getBalance() + amount) accountRepository.save(from); accountRepository.save(to); }} 👉 If exception occurs: ❌ Both updates are rolled back ✅ No partial update 🔹 Where to Use @Transactional ✅ 1. Service Layer (Best Practice) ✔ Always use in Service class, not Controller Java @Service public class OrderService { @Transactional public void placeOrder(Order order) { // multiple DB operations } } ✅ 2. Multiple DB Operations When you perform: Insert + Update Update + Delete Multiple table operations ✅ 3. Financial / Critical Operations Examples: Banking transactions Payment processing Order processing ✅ 4. Batch Processing Bulk inserts/updates Data migration 🔹 Important Attributes 1️⃣ Propagation Defines how transaction behaves if already exists Java @Transactional(propagation = Propagation.REQUIRED) Common types: REQUIRED (default) → join existing or create new REQUIRES_NEW → always create new SUPPORTS → run with/without transaction 2️⃣ Isolation Controls data consistency level Java @Transactional(isolation = Isolation.READ_COMMITTED) Levels: READ_UNCOMMITTED READ_COMMITTED REPEATABLE_READ SERIALIZABLE 3️⃣ Rollback Rules Java @Transactional(rollbackFor = Exception.class) 👉 By default: Rollback only on RuntimeException Not on checked exception 4️⃣ Read Only Java @Transactional(readOnly = true) ✔ Optimized for read operations ✔ Improves performance 🔹 Internal Working (Important Interview Point) Spring uses: 👉 AOP (Proxy-based) Flow: Method call intercepted Transaction started Method executed Commit or rollback based on result 🔹 Common Mistakes ❌ ❌ 1. Using in Controller Java @RestController @Transactional // ❌ avoid ❌ 2. Private Method Java @Transactional private void method() {} // ❌ won't work ❌ 3. Self Invocation Java this.otherMethod(); // ❌ transaction ignored 🔹 Real-Life Example Order Service Java @Transactional public void createOrder(Order order) { orderRepository.save(order); paymentService.processPayment(order); inventoryService.updateStock(order); } 👉 If payment fails: Order not saved Stock not reduced 🔹 Summary @Transactional = Data consistency + Atomicity

SQL INNER JOIN vs LEFT JOIN – Real Examples from My 2.5 Years as a Java Backend Developer 📊 After working with databases in almost every project, one concept I wish I had understood earlier is the real practical difference between INNER JOIN and LEFT JOIN. As a Java Backend Developer with 2.5 years of experience, here’s how I decide which one to use in production: INNER JOIN → Returns only matching rows from both tables (intersection) LEFT JOIN → Returns all rows from the left table + matching rows from the right table (NULLs where no match) Real Project Examples: 1. INNER JOIN – Get only users who have placed at least one order SQL → Only users with orders will appear in the result. 2. LEFT JOIN – Get ALL users and their latest order (even if they never ordered anything) → Every user shows up. Users without orders will have NULL in order columns. My Rule of Thumb after 2.5 years: Use INNER JOIN when you only care about matching data (e.g., active relationships). Use LEFT JOIN when the main table is your source of truth (very common in reports, dashboards, and user lists). In Spring Boot + JPA projects, I see most developers default to LEFT JOIN FETCH in @Query when they want to avoid N+1 problems while still getting complete data. Which one do you use more in your day-to-day work — INNER or LEFT? Drop your real use case below 👇 Let’s learn from each other! #SQL #Database #INNERJOIN #LEFTJOIN #PostgreSQL #SpringBoot #JPA #JavaDeveloper #BackendDevelopment #BackendEngineer

“I wasted hours writing JDBC code before I understood this.” If you’ve used JDBC, you know the drill: Open connection Create statement Execute query Loop through ResultSet Manually map data Close everything (and hope nothing breaks) Now imagine doing this for every table in a real project. 👉 That’s not scalable. 👉 That’s exactly why Hibernate exists. 💡 𝗧𝗵𝗲 𝗥𝗲𝗮𝗹 𝗣𝗿𝗼𝗯𝗹𝗲𝗺  JDBC is not “bad” — it’s just too low-level. 𝗬𝗼𝘂 𝗮𝗿𝗲 𝗳𝗼𝗿𝗰𝗲𝗱 𝘁𝗼: - Write repetitive boilerplate code - Manually convert table data → Java objects - Handle exceptions and resource management yourself 👉 This slows you down and increases bugs. 🔥 𝗪𝗵𝗮𝘁 𝗛𝗶𝗯𝗲𝗿𝗻𝗮𝘁𝗲 𝗔𝗰𝘁𝘂𝗮𝗹𝗹𝘆 𝗦𝗼𝗹𝘃𝗲𝘀 Hibernate is an ORM (Object Relational Mapping) tool. 𝘐𝘯𝘴𝘵𝘦𝘢𝘥 𝘰𝘧 𝘵𝘩𝘪𝘯𝘬𝘪𝘯𝘨 𝘪𝘯 𝘵𝘢𝘣𝘭𝘦𝘴 𝘢𝘯𝘥 𝘳𝘰𝘸𝘴: 👉 𝘠𝘰𝘶 𝘸𝘰𝘳𝘬 𝘸𝘪𝘵𝘩 𝘑𝘢𝘷𝘢 𝘰𝘣𝘫𝘦𝘤𝘵𝘴 𝗛𝗶𝗯𝗲𝗿𝗻𝗮𝘁𝗲 𝗵𝗮𝗻𝗱𝗹𝗲𝘀: * Mapping objects ↔ database tables * Generating SQL queries * Managing connections and transactions 🧠 𝗧𝘄𝗼 𝗖𝗼𝗻𝗰𝗿𝗲𝘁𝗲 𝗦𝗶𝘁𝘂𝗮𝘁𝗶𝗼𝗻𝘀 👉 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝟭: You fetch 100 employees 𝗝𝗗𝗕𝗖: Loop + manually map each column 𝗛𝗶𝗯𝗲𝗿𝗻𝗮𝘁𝗲: List<Employee> directly 👉 𝗘𝘅𝗮𝗺𝗽𝗹𝗲 𝟮: You insert a record 𝗝𝗗𝗕𝗖: Write INSERT query + set parameters 𝗛𝗶𝗯𝗲𝗿𝗻𝗮𝘁𝗲: Save/persist the object 🎯 𝗧𝗮𝗸𝗲𝗮𝘄𝗮𝘆 Hibernate is not about “less code.” 👉 It’s about focusing on business logic instead of database plumbing If you don’t understand why Hibernate exists, You’ll misuse it and create worse performance than JDBC. Day 2 → I’ll break down ORM deeply (and where it fails in real projects) #Java #Hibernate #SpringBoot #OpenToWork #BackendDevelopment #ImmediateJoiner

Deep Dive into Spring Data JPA :- As part of my backend learning journey, I recently worked on a Spring Data JPA project to understand how modern Java applications handle database operations efficiently. Traditionally, interacting with databases required writing complex SQL queries and handling JDBC code. But with Spring Data JPA, the process becomes much cleaner, faster, and developer-friendly. Project Overview:- In this project, I built a simple application that: 👉 Stores employee details like name and salary 👉 Connects seamlessly with a MySQL database 👉 Uses Spring Data JPA to perform operations with minimal code The goal was to understand how abstraction and ORM simplify backend development. Step-by-Step Working:- 🔹 1. Main Application Class The application starts by loading the Spring configuration using ApplicationContext. Once the context is initialized: ->The repository bean is retrieved ->An Employee object is created ->The save() method is called to persist data 🔹 2. Entity Class (Employee.java) The Employee class acts as a bridge between Java objects and database tables. Key annotations used: ->@Entity → Marks the class as a database table ->@Id → Defines the primary key ->@GeneratedValue → Automatically generates ID values 🔹 3. Repository Layer Instead of writing DAO classes manually, I used: public interface EmployeeRepository extends JpaRepository<Employee, Integer> { } By extending JpaRepository, I automatically get: ->save() → Insert/update data ->findAll() → Fetch records ->delete() → Remove records 🔹 4. Spring Configuration The configuration file sets up the backbone of the application: ->Database connection (MySQL URL, username, password) ->Hibernate as the JPA provider ->Entity scanning ->Transaction management 🔹 5. Maven Dependencies To make everything work, I added essential dependencies: ->Spring Core & Context ->Spring Data JPA ->Hibernate ->MySQL Connector 🎯 Key Advantages I Observed ✔ Eliminates boilerplate JDBC code ✔ No need to manually write SQL queries ✔ Faster development and cleaner structure ✔ Easy to scale and maintain ✔ Built-in powerful CRUD operations  Output When the application runs: 👉 A new employee record is automatically inserted into the database table This confirms that the entire flow — from object creation to database persistence — works seamlessly. #SpringDataJPA #SpringBoot #JavaDeveloper #BackendDevelopment #Hibernate #MySQL #Java #SoftwareDevelopment #Coding #Programming #TechLearning #ORM #Developers #LearningJourney #100DaysOfCode #CleanCode #Codegnan Anand Kumar Buddarapu Uppugundla Sairam Saketh Kallepu

🚀 Java Streams in Action: Partitioning Data ! 👉 Partition employees into two groups - one earning above ₹50,000 and the others. i. Have you ever needed to split data into two groups based on a condition? ii. Here’s a simple example using Java Streams to partition employees based on salary. 🔍 Approach 👉 stream() Converts the list into a stream to perform functional-style operations. 👉 filter(Objects::nonNull) Removes any null objects from the list to avoid NullPointerException. 👉 collect(...) Terminal operation that transforms the stream into a collection (in this case, a Map). 👉 Collectors.partitioningBy(...) This is the key part 🔥 It splits the data into two groups based on a condition: true -> Employees earning more than ₹50,000 false -> Employees earning ₹50,000 or less ✔ Automatically groups into two categories ✔ Ideal for binary conditions (true/false) 📊 Output Structure true -> List of high-salary employees false -> List of other employees - Use partitioningBy when your condition results in only two groups. - If you need multiple groups (like department-wise), go for groupingBy. 💻 I’ve added my Java solution in the comments below. Please let me know if there are any other approaches I could try. #Java #JavaStreams #CodingInterview #BackendDevelopment #SpringBoot #Developers

Spring Boot DAY 26 – JPQL vs Native Query When working with Spring Data JPA, you often need custom queries. That’s where JPQL and Native Queries come into play 👇 🔹 JPQL (Java Persistence Query Language) JPQL is an object-oriented query language defined by Java Persistence API (JPA). It works with: ✔ Entity class names ✔ Java field names ✔ Object relationships 👉 It does NOT use table names or column names directly. ✅ Example: @Query("SELECT e FROM Employee e WHERE e.salary > :salary") List<Employee> findHighSalaryEmployees(@Param("salary") double salary); Here: Employee= Entity class salary= Java field 💡 JPQL works on the entity model, not the database schema. 🎯 Advantages: ✔ Database independent ✔ Cleaner & object-oriented ✔ Easy to maintain ✔ Portable across databases 🔹 Native Query Native Query uses pure SQL. It works with: ✔ Table names ✔ Column names ✔ Database-specific functions ✅ Example: @Query(value = "SELECT * FROM employee WHERE salary > :salary", nativeQuery = true) List<Employee> findHighSalaryEmployees(@Param("salary") double salary); Here: employee = Table name salary = Column name 💡 You are directly interacting with the database. 🎯 Advantages: ✔ Full database control ✔ Use complex joins ✔ Use DB-specific features (e.g., LIMIT, stored procedures) ✔ Better for performance tuning in complex cases 🆚 JPQL vs Native Query – Key Differences FeatureJPQLNative QueryQuery TypeObject-basedSQL-basedUsesEntity namesTable namesDB DependencyIndependentDB-specificPortabilityHighLowAdvanced SQLLimitedFull support🧠 When Should You Use What? ✅ Use JPQL when: You want database independence Your query is simple to moderate You prefer object-oriented coding ✅ Use Native Query when: You need complex joins You want database-specific optimization You are using stored procedures or advanced SQL 🎯 Simple Rule 👉 JPQL = Work with Objects 👉 Native Query = Work with Database Both are powerful. The smart developer chooses based on project requirements 💡 If you're learning Spring Boot, understanding this difference is crucial for writing optimized and maintainable applications 🚀