README.md

Java SQL Mapping

Mapping of SQL statements into it's corresponding object 🤩

---

READ THIS

This package is still in beta, and is not recommended to be used in production.

Expect bugs.

The problem

You are tired of doing your mapping of sql queries yourself, making your repositories huge. You don't want to use an ORM framework, but still want the mapping part of them.

This solution

Java SQL Mapping is a package designed to work on any SQL database (see Supported section).

It allows you to create SQL queries, which will then be run through a jdbc connection, and then return the object corresponding to your query.

Installation

Apache Maven

<dependency>
  <groupId>io.github.tobias-z</groupId>
  <artifactId>java-sql-mapping</artifactId>
  <version>1.0.4</version>
</dependency>

Gradle Groovy DSL

implementation 'io.github.tobias-z:java-sql-mapping:1.0.4'

For other methods see Central Repository

Supported

Currently, Java SQL Mapping has support for 3 databases:

• MySQL
• PostgreSQL
• SQLServer

These three have are listed since they have been thoroughly tested.

It is possible that other SQL databases are supported, but they have not been tested.

Usage

Setup

Java SQL Mapping has a very simple setup. It provides a DBConnection.createDatabase() method, which takes a DBConfig

Example MySQL config

public class MySQLDBConfig implements DBConfig {

    @Override
    public Map<DBSetting, String> getConfiguration() {
        Map<DBSetting, String> config = new HashMap<>();
        config.put(DBSetting.JDBC_DRIVER, "com.mysql.cj.jdbc.Driver");
        config.put(DBSetting.USER, "YOUR_USER");
        config.put(DBSetting.PASSWORD, "SUPER_SECRET_PASSWORD");
        config.put(DBSetting.URL, "jdbc:mysql://localhost:3306/chat_test");
        return config;
    }

}

Example entity

@Table(name = "users")
public class User {

    @AutoIncremented
    @PrimaryKey
    @Column(name = "id")
    private Integer id;

    @Column(name = "username")
    private String username;

    @Column(name = "password")
    private String password;

    public User() {
    }

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

    //...getters and setters

}

Annotations

@Table(name = "table name") //required
@Column(name = "col name") //required
@PrimaryKey //required
@AutoIncremented

Example implementation

Following example can be used for any config. Just change the DBConfig you provide to the DBConnection.createDatabase() method

public class UserRepository {
    private static final Database db = DBConnection.createDatabase(new MySQLDBConfig());

    public User getUserById(int id) throws Exception {
        User user = db.get(id, User.class);
        return user;
    }

    public List<User> getAllUsers() throws Exception {
        List<User> users = db.getAll(User.class);
        return users;
    }

    public List<User> findAllUsersWithRole(Role role) throws Exception {
        List<User> users = db.select(connection -> {
            String sql = "SELECT * FROM users WHERE role = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, role.name());
            return preparedStatement;
        }, User.class);
        return users;
    }

    public User createUser(String username, String password) throws Exception {
        User createdUser = db.insert(connection -> {
            String sql = "INSERT INTO users (username, password) VALUES (?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            return preparedStatement;
        }, User.class);
        return createdUser;
    }

    public void updateUser(int id, String username) throws Exception {
        db.executeQuery(connection -> {
            String sql = "UPDATE users SET username = ? WHERE id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setInt(2, id);
            return preparedStatement;
        });
    }

    public void deleteUser(int id) throws Exception {
        // Can also pass a DBStatement
        db.delete(id, User.class);
    }
}

Issues

Looking to contribute? Please read the CONTRIBUTING.md file, which contains information about making a PR.

Any feedback is very appreciated!

🪲 Bugs

Please file an issue for bugs, missing documentation, unexpected behavior etc.

Create bug report

🕯 Feature Requests

Please file an issue to suggest new features. Vote on feature requests by adding a 👍.

Create Feature Requests

Contributors ✨

Thanks goes to these wonderful people (emoji key):

Tobias Zimmermann 🚇 ⚠️ 💻

This project follows the all-contributors specification. Contributions of any kind welcome!