Simple SQL Generator

Get your database integration up and running in minutes.

Simple SQL Generator is a Java annotation processor that automatically generates type-safe SQL execution code at compile time. Write clean, maintainable data access code without runtime reflection or complex ORM overhead.

✨ Why Simple SQL Generator?

• ⚡ Rapid Prototyping - Get database queries working in minutes with just annotations, perfect for MVPs and proof-of-concepts
• 🚀 Zero Runtime Cost - All SQL generated at compile time, no reflection overhead
• 🛡️ Type Safe - Compilation errors catch SQL parameter mismatches early
• 🗃️ Multi-Database Support - MySQL, PostgreSQL, Oracle, H2, and more
• ☕ JDK 11-21+ Compatible - Wide compatibility range for modern and legacy projects
• 📦 Minimal Dependencies - Only requires Google Auto Service for annotation processing
• 🔧 IDE Friendly - Full IntelliJ IDEA and Eclipse support with code completion

🚀 Quick Start

Prerequisites

Your project must include Spring JDBC dependencies:

Gradle:

dependencies {
    implementation 'org.springframework:spring-jdbc:6.0.0+'
    implementation 'org.springframework:spring-tx:6.0.0+'
    // Or use Spring Boot starter
    // implementation 'org.springframework.boot:spring-boot-starter-jdbc'

    // Database driver (choose one)
    runtimeOnly 'com.h2database:h2'              // H2
    runtimeOnly 'org.postgresql:postgresql'       // PostgreSQL
    runtimeOnly 'com.mysql:mysql-connector-j'    // MySQL
}

Maven:

<dependencies>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>6.0.0</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>6.0.0</version>
    </dependency>

    <!-- Database driver (choose one) -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

Installation

Add Simple SQL Generator to your build tool:

Maven:

<dependency>
    <groupId>io.github.simplesqlgen</groupId>
    <artifactId>simple-sql-generator</artifactId>
    <version>1.0.2</version>
    <scope>provided</scope>
</dependency>

Gradle:

compileOnly 'io.github.simplesqlgen:simple-sql-generator:1.0.2'
annotationProcessor 'io.github.simplesqlgen:simple-sql-generator:1.0.2'

Basic Usage

1. Define your entity:

public class User {
    private Long id;
    private String name;
    private String email;
    private boolean active;
    
    // constructors, getters, setters...
}

2. Create a repository class:

@Component
@SqlGenerator(entity = User.class, tableName = "users")
public class UserRepository {
    
    // These fields will be automatically injected by the processor
    // @Autowired private JdbcTemplate jdbcTemplate;
    // @Autowired private NamedParameterJdbcTemplate namedJdbcTemplate;
    
    // Native SQL queries
    @NativeQuery("SELECT * FROM users WHERE id = ?")
    public User findById(Long id) {
        // Implementation will be generated at compile time
        return null;
    }
    
    @NativeQuery("UPDATE users SET active = ? WHERE id = ?")
    public int updateActiveStatus(boolean active, Long id) {
        // Implementation will be generated at compile time
        return 0;
    }
    
    // Auto-generated queries from method names
    public List<User> findByName(String name) {
        // Implementation will be generated at compile time
        return null;
    }
    
    public List<User> findByEmailAndActive(String email, boolean active) {
        // Implementation will be generated at compile time
        return null;
    }
    
    public User findByEmail(String email) {
        // Implementation will be generated at compile time
        return null;
    }
    
    // CRUD operations
    public User save(User user) {
        // Implementation will be generated at compile time
        return null;
    }
    
    public int deleteById(Long id) {
        // Implementation will be generated at compile time
        return 0;
    }
    
    public boolean existsByEmail(String email) {
        // Implementation will be generated at compile time
        return false;
    }
    
    public long countByActive(boolean active) {
        // Implementation will be generated at compile time
        return 0;
    }
}

3. Use in your application:

@Component
public class UserService {
    
    private final UserRepository userRepository;
    
    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }
    
    public User createUser(String name, String email) {
        User user = new User(name, email, true);
        return userRepository.save(user);
    }
    
    public List<User> findActiveUsersByName(String name) {
        return userRepository.findByNameAndActive(name, true);
    }
}

📚 Features

Native SQL Queries

Write raw SQL with type-safe parameter binding:

@NativeQuery(value = "SELECT u.*, p.title as profile_title FROM users u LEFT JOIN profiles p ON u.id = p.user_id WHERE u.created_at > ?", 
            resultType = UserProfile.class)
public List<UserProfile> findUsersWithProfilesAfter(LocalDateTime date) {
    // Implementation generated at compile time
    return null;
}

// Named parameters
@NativeQuery("SELECT * FROM users WHERE name = :name AND age > :minAge")
public List<User> findByNameAndAgeGreaterThan(@Param("name") String name, @Param("minAge") int minAge) {
    // Implementation generated at compile time
    return null;
}

Auto-Generated Queries

Method names are parsed to generate SQL automatically:

Basic Operations

// Generates: SELECT * FROM users WHERE name = ?
public List<User> findByName(String name) {
    return null; // Implementation generated at compile time
}

// Generates: SELECT * FROM users WHERE email = ? AND active = ?
public List<User> findByEmailAndActive(String email, boolean active) {
    return null; // Implementation generated at compile time
}

// Generates: SELECT COUNT(*) FROM users WHERE active = ?
public long countByActive(boolean active) {
    return 0; // Implementation generated at compile time
}

// Generates: DELETE FROM users WHERE id = ?
public int deleteById(Long id) {
    return 0; // Implementation generated at compile time
}

// Generates: SELECT COUNT(*) FROM users WHERE email = ?
public boolean existsByEmail(String email) {
    return false; // Implementation generated at compile time
}

Advanced Query Patterns

// LIKE operations
// Generates: SELECT * FROM users WHERE name LIKE ?
public List<User> findByNameContaining(String namePart) {
    return null; // Implementation generated at compile time
}

// Generates: SELECT * FROM users WHERE UPPER(email) LIKE UPPER(?)
public List<User> findByEmailStartingWithIgnoreCase(String emailPrefix) {
    return null; // Implementation generated at compile time
}

// Boolean literals
// Generates: SELECT * FROM users WHERE active = ?
public List<User> findByActiveTrue() {
    return null; // Implementation generated at compile time
}

// OR operations
// Generates: SELECT * FROM users WHERE name = ? OR email = ?
public List<User> findByNameOrEmail(String name, String email) {
    return null; // Implementation generated at compile time
}

// Aggregation functions
// Generates: SELECT SUM(age) FROM users WHERE active = ?
public Integer sumAgeByActiveTrue() {
    return 0; // Implementation generated at compile time
}

// Complex combinations
// Generates: SELECT DISTINCT email FROM users WHERE UPPER(name) LIKE UPPER(?) AND age BETWEEN ? AND ? ORDER BY email ASC
public List<String> findDistinctEmailByNameContainingIgnoreCaseAndAgeBetweenOrderByEmailAsc(String namePart, int minAge, int maxAge) {
    return null; // Implementation generated at compile time
}

Result Mapping

Flexible result mapping strategies:

@NativeQuery(value = "SELECT name, email FROM users", 
            mappingType = ResultMappingType.CONSTRUCTOR,
            resultType = UserSummary.class)
public List<UserSummary> getUserSummaries() {
    return null; // Implementation generated at compile time
}

@NativeQuery(value = "SELECT id, name, email, created_at FROM users",
            mappingType = ResultMappingType.FIELD_MAPPING,
            columnMapping = {"id", "name", "email", "createdAt"})
public List<User> getAllUsers() {
    return null; // Implementation generated at compile time
}

🗃️ Database Support

Simple SQL Generator supports multiple databases with standard SQL generation:

Supported Databases:

• MySQL 5.7+
• PostgreSQL 10+
• Oracle 12c+
• H2 Database
• SQL Server 2017+

⚙️ Configuration

Naming Strategies

Control how entity names map to table/column names:

@Component
@SqlGenerator(entity = User.class, 
              namingStrategy = NamingStrategy.SNAKE_CASE) // user_profile -> USER_PROFILE
public class UserRepository { }

@Component
@SqlGenerator(entity = User.class,
              namingStrategy = NamingStrategy.CAMEL_CASE) // userProfile -> userProfile  
public class UserRepository { }

Native Query Only Mode

Use only @NativeQuery annotations without entity-based generation:

@Component
@SqlGenerator(nativeQueryOnly = true)
public class CustomQueryRepository {
    @NativeQuery("SELECT custom_function(?) as result")
    public String executeCustomFunction(String input) {
        return null; // Implementation generated at compile time
    }
}

🔧 Integration

Spring Boot

Simple SQL Generator integrates seamlessly with Spring Boot:

@Repository
@SqlGenerator(entity = User.class)
public class UserRepository {
    // Repository methods with empty bodies that will be filled at compile time
    public List<User> findAll() {
        return null; // Implementation generated
    }
}

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository; // Auto-injected
}

Testing

Write clean tests for your repositories:

@Test
void testUserRepository() {
    User user = new User("John Doe", "john@example.com", true);
    User saved = userRepository.save(user);
    
    assertThat(saved.getId()).isNotNull();
    assertThat(userRepository.existsByEmail("john@example.com")).isTrue();
}

📖 Comparison

Feature	Simple SQL Generator	MyBatis	JPA/Hibernate	JOOQ
Compile-time generation	✅	❌	❌	✅
Zero runtime overhead	✅	❌	❌	❌
Type safety	✅	⚠️	✅	✅
Native SQL support	✅	✅	⚠️	✅
Learning curve	Low	Medium	High	High
Annotation-based	✅	⚠️	✅	❌

🔍 Verifying Generated Code

After building your project, you can inspect the generated SQL queries to ensure they match your expectations.

Build Your Project

# Gradle
./gradlew build

# Maven
mvn compile

Locate Generated Classes

build/classes/java/main/your/package/YourRepository.class

Inspect Generated Code in IDE

Open the generated .class file in your IDE to see the decompiled implementation:

@Component
public class UserRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public List<User> findByName(String name) {
        return this.jdbcTemplate.query(
            "SELECT * FROM users WHERE name = ?",
            new BeanPropertyRowMapper(User.class),
            name
        );
    }

    public List<User> findByAgeGreaterThan(int age) {
        return this.jdbcTemplate.query(
            "SELECT * FROM users WHERE age > ?",
            new BeanPropertyRowMapper(User.class),
            age
        );
    }

    public long countByActive(boolean active) {
        return (Long) this.jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM users WHERE active = ?",
            Long.class,
            active
        );
    }
}

Pre-deployment Verification Checklist

• ✅ SQL Syntax: Are the generated queries syntactically correct?
• ✅ Parameter Binding: Are parameters properly bound with ? placeholders?
• ✅ Return Types: Do return types match your method signatures?
• ✅ Field Injection: Are @Autowired fields properly injected?
• ✅ Naming Convention: Are column names correctly mapped (snake_case ↔ camelCase)?

Debugging Tips

• Query Issues: Check the generated SQL against your database schema
• Type Mismatches: Verify return types match your entity fields
• Missing Fields: Ensure entity fields are accessible (have getters/setters)
• Naming Problems: Check your @SqlGenerator tableName and field mappings

🎯 Requirements

• JDK: 11, 17, 21+ (tested and verified)
• Build Tools: Maven 3.6+, Gradle 6.0+
• Databases: MySQL, PostgreSQL, Oracle, H2, SQL Server
• IDE: IntelliJ IDEA, Eclipse, VS Code

🤝 Contributing

We welcome contributions! Please see our Contributing Guide for details.

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

📄 License

This project is licensed under the Apache License 2.0 - see the LICENSE file for details.

🙏 Acknowledgments

• Inspired by the simplicity of Lombok and the power of JOOQ
• Built on top of Google's Auto Service annotation processor framework
• Thanks to the Java community for annotation processing standards (JSR 269)

---

Made with ❤️ by the Simple SQL Generator team
Star ⭐ this repo if you find it useful!