This content originally appeared on DEV Community and was authored by Carlos Estrada
Welcome to another post, today we will be looking about the JDBC, what is, how
can we use one, and most important creating a simple java application abouta todos app
that not only connects to a postgresql database.
But also implementing a really basic ORM similar to the eloquen one from laravel.
Before starting let's look about what is a JDBC.
What is JDBC?
JDBC is the Java Database Connectivity api, that allows us to access virtually
any data source, is comprised of two packages:
- java.sql
- javax.sql
That are installed by default when we get the Java SE 8.
How can we use one?
For use the JDBC api with a particular database management system, we need
a JDBC technology-based driver to mediate between JDBC tehcnology and the
database.
This driver can be written in pure java or in a mixture or java and java native
interface (JNI) native methods.
Today we will be using the Postgresql JDBC driver
for our project.
Requirements
For this project you will be need the next:
- Maven
- Java
- Postgresql (You can use docker)
Creating the project
For creating a new project with maven use the next command
mvn archetype:generate -DgroupId=com.mycompany.app -DartifactId=my-app -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.5 -DinteractiveMode=false
Here is the link of the docs if you want to learn more about this part Maven in 5 minutes
After the commands end, and you enter the folder let's create the database.
Creating the database
Postgresql installed in the OS
If you have installed postgresql in your system you only need to create a new database and
create the next table
create table tbl_jd_todos (
id serial primary key,
name varchar(255) not NULL,
completed smallint default 0 not null,
created_at timestamp default now()
)
After creating the table go to Creating an env file
Using docker
In case you want to use docker here is the docker-compose.yml file that we will
need:
services:
jodos_db:
image: postgres:15
container_name: jodos_db
env_file:
- .env
ports:
- "${DB_PORT}:5432"
volumes:
- jodo-data:/var/lib/postgresql/data
volumes:
jodo-data:
name: jodo-data
We will need to create an .env file to specify the user, password, db and port of our app
so let's create one.
Creating an env file
In this section will be creating the .env file for our project.
For that let's create the file at the root of the project .env with
the next values:
DB_PORT=""
POSTGRES_USER=""
POSTGRES_PASSWORD=""
POSTGRES_DB=""
Fill the values of the env file and let's continue:
In case you are using docker, please run
docker compose upand ensure
that you can connect to the database using a database manager tool likedbeaver
Installing dependencies
Let's continue and install all the dependencies for this project.
We will be using two the postgres-jdbc
and the dotenv java.
Let go to the above links and add the dependencies to your poml file. Your poml file
should be look like these:
<dependencies>
<!-- rest of dependencies -->
<!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.7.5</version>
</dependency>
<!-- https://mvnrepository.com/artifact/io.github.cdimascio/dotenv-java -->
<dependency>
<groupId>io.github.cdimascio</groupId>
<artifactId>dotenv-java</artifactId>
<version>3.1.0</version>
</dependency>
</dependencies>
The java files for database and orm implementation
We will need two important things in this project one file for getting a connection
to our database and the base class for our models.
Let's begin creating the database file.
Create a DB.java file with the next code:
package com.caresle.jodos;
import java.sql.*;
import io.github.cdimascio.dotenv.Dotenv;
/**
* DB
*/
public class DB {
static String url;
static {
Dotenv dotenv = Dotenv.load();
String port = dotenv.get("DB_PORT");
String database = dotenv.get("POSTGRES_DB");
String username = dotenv.get("POSTGRES_USER");
String password = dotenv.get("POSTGRES_PASSWORD");
url = "jdbc:postgresql://localhost:" + port + "/" + database + "?user=" + username + "&password=" + password;
}
private static Connection connection;
private DB() {}
public static Connection getConnection() throws SQLException {
if (connection == null || connection.isClosed()) {
connection = DriverManager.getConnection(url);
}
return connection;
}
}
In this file we are creating the url for connecting to our database, also definning
a method to get the connection of the database to be used by the model.
Next is time to create Model.java file.
This file will be in charge of providing the general methods to interact with the database
for our models. First of all this class will be an abstract class and we are going to define
the next props:
protected static String table;
protected Map<String, Object> attributes = new HashMap<>();
The first prop is the table name, and the second is the attributes of the model.
Let's now create the methods for set and get the attributes.
public void set(String key, Object value) {
attributes.put(key, value);
}
public Object get(String key) {
return attributes.get(key);
}
Remember that Object is the base class of all the java classes, so we can use it
to get the value of the attribute.
The first thing that we will create is the delete method, because is the easiest one.
public boolean delete() throws SQLException {
String sql = "DELETE FROM " + table + " WHERE id = ?";
try (Connection connection = DB.getConnection()) {
PreparedStatement statement = connection.prepareStatement(sql);
statement.setObject(1, attributes.get("id"));
return statement.executeUpdate() > 0;
}
}
Here we are usingt the id attribute of the model to delete the record from the database.
Also we are propagating the exception to the caller.
Now we will be creating a helper method for set the parrams of the model.
private void setParameters(PreparedStatement statement) throws SQLException {
int index = 1;
Set<String> keys = attributes.keySet();
keys.remove("id");
for (String key: keys) {
statement.setObject(index++, attributes.get(key));
}
}
This method will be used in the insert and update methods. So let's create the insert method.
private boolean insert() throws SQLException {
String columns = String.join(", ", attributes.keySet());
String valuesPlaceholder = String.join(", ", Collections.nCopies(attributes.size(), "?"));
String sql = "INSERT INTO " + table + " (" + columns + ") VALUES (" + valuesPlaceholder + ")";
try (Connection connection = DB.getConnection()) {
PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
setParameters(statement);
int affectedRows = statement.executeUpdate();
if (affectedRows > 0) {
ResultSet resultSet = statement.getGeneratedKeys();
if (resultSet.next()) {
attributes.put("id", resultSet.getLong(1));
}
}
return affectedRows > 0;
}
}
And now the update method.
private boolean update() throws SQLException {
int id = (int)attributes.get("id");
Set<String> keys = attributes.keySet();
keys.remove("id");
String setClause = String.join(" = ?, ", keys) + " = ?";
String sql = "UPDATE " + table + " SET " + setClause + " WHERE id = ?";
try ( Connection connection = DB.getConnection()) {
PreparedStatement statement = connection.prepareStatement(sql);
setParameters(statement);
statement.setObject(keys.size() + 1, id);
return statement.executeUpdate() > 0;
}
}
Finally we will create the method for use update and insert, because they are private and the only difference is
the id attribute.
public boolean save() throws SQLException {
if (attributes.containsKey("id")) {
return update();
}
return insert();
}
And here are some static methods for findById and findAll.
FindById
public static <T extends Model> T findById(Class<T> modelClass, long id) {
String sql = "SELECT * FROM " + table + " WHERE id = ?";
try (Connection connection = DB.getConnection()) {
PreparedStatement statement = connection.prepareStatement(sql);
statement.setLong(1, id);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()) {
try {
T instance = modelClass.getDeclaredConstructor().newInstance();
ResultSetMetaData metaData = resultSet.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
instance.set(metaData.getColumnName(i), resultSet.getObject(i));
}
return instance;
} catch (Exception e) {
throw new RuntimeException("Error creating instance of " + modelClass.getName(), e);
}
}
} catch (SQLException e) {
System.err.println("SQL ERROR" + e.getMessage());
e.printStackTrace();
}
return null;
}
FindAll
public static <T extends Model> ArrayList<T> all(Class<T> modelClass) {
ArrayList<T> models = new ArrayList<>();
try (Connection connection = DB.getConnection()) {
String sql = "SELECT * FROM " + table;
PreparedStatement statement = connection.prepareStatement(sql);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
try {
T instance = modelClass.getDeclaredConstructor().newInstance();
ResultSetMetaData metaData = resultSet.getMetaData();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
String columnName = metaData.getColumnName(i);
Object value = resultSet.getObject(i);
instance.set(columnName, value);
}
models.add(instance);
} catch (Exception e) {
throw new RuntimeException("Error creating instance of " + modelClass.getName(), e);
}
}
} catch (SQLException e) {
System.err.println("SQL ERROR" + e.getMessage());
e.printStackTrace();
}
return models;
}
And here is the example of creating a Todo model.
package com.caresle.jodos;
import java.util.ArrayList;
/**
* Todo
*/
public class Todo extends Model {
static {
table = "tbl_jd_todos";
}
public Todo() {}
public static Todo findById(long id) {
return (Todo) Model.findById(Todo.class, id);
}
public static ArrayList<Todo> all() {
return (ArrayList<Todo>) Model.all(Todo.class);
}
}
Creating the menu for the app
Let's create the class UI.java for handle the menu and their actions
package com.caresle.jodos;
import java.util.ArrayList;
import java.util.Scanner;
public class Ui {
static int LIST = 1;
static int CREATE = 2;
static int EDIT = 3;
static int COMPLETE = 4;
static int DELETE = 5;
static int EXIT = 6;
public static void print() {
System.out.println("=\tSELECT THE OPTION\t=");
System.out.println("1) List all todos");
System.out.println("2) Create a new todo");
System.out.println("3) Edit a todo");
System.out.println("4) Mark a todo as completed");
System.out.println("5) Delete a todo");
System.out.println("6) Exit");
}
public static void listTodos() {
ArrayList<Todo> todos = Todo.all();
for (Todo todo: todos) {
System.out.println(todo);
}
}
public static void createTodo(Scanner scanner) {
System.out.println("Enter the todo:");
scanner.nextLine();
String todo = scanner.nextLine();
try {
Todo newTodo = new Todo();
newTodo.set("name", todo);
newTodo.set("completed", 0);
newTodo.save();
} catch (Exception e) {
System.err.println("ERROR: " + e.getMessage());
}
}
public static void editTodo(Scanner scanner) {
System.out.println("Enter the id of the todo to edit:");
long id = scanner.nextLong();
Todo todo = Todo.findById(id);
System.out.println("Enter the new todo:");
scanner.nextLine();
String todoName = scanner.nextLine();
todo.set("name", todoName);
try {
todo.save();
} catch (Exception e) {
System.err.println("ERROR: " + e.getMessage());
}
}
public static void completeTodo(Scanner scanner) {
System.out.println("Enter the id of the todo to complete:");
long id = scanner.nextLong();
Todo todo = Todo.findById(id);
todo.set("completed", 1);
try {
todo.save();
} catch (Exception e) {
System.err.println("ERROR: " + e.getMessage());
}
}
public static void deleteTodo(Scanner scanner) {
System.out.println("Enter the id of the todo to delete:");
long id = scanner.nextLong();
Todo todo = Todo.findById(id);
try {
todo.delete();
} catch (Exception e) {
System.err.println("ERROR: " + e.getMessage());
}
}
}
Joining all the parts
Now in the App.java file we will add all the parts to create the app.
package com.caresle.jodos;
import java.util.Scanner;
public class App {
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
int option = 0;
while (option != Ui.EXIT) {
Ui.print();
option = scanner.nextInt();
if (option == Ui.LIST) {
Ui.listTodos();
continue;
}
if (option == Ui.CREATE) {
Ui.createTodo(scanner);
continue;
}
if (option == Ui.EDIT) {
Ui.editTodo(scanner);
continue;
}
if (option == Ui.COMPLETE) {
Ui.completeTodo(scanner);
continue;
}
if (option == Ui.DELETE) {
Ui.deleteTodo(scanner);
continue;
}
}
scanner.close();
}
}
Conclusion
With this post we have learned how to create a simple java application with jdbc and
how to create a simple orm for our models.
This content originally appeared on DEV Community and was authored by Carlos Estrada
Carlos Estrada | Sciencx (2025-02-08T17:04:21+00:00) Creating a todo app with JDBC. Retrieved from https://www.scien.cx/2025/02/08/creating-a-todo-app-with-jdbc/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.