What Will I Learn?
- What is Database in Supbot API?
- Why are we using Database of Supbot?
- How can we use it?
- Java Development Kit, IDEA intellij
- Google Chrome, Selenium Chrome Webdrivers
- Supbot API
- DB Browser for SQlite
- JDBC and SQLite Query Language
- Commands and Feature of Supbot API
Resources for this Tutorial:
Outline and Overview
In this Supbot tutorial, we will take a look on how database works in Supbot API. We will see why should we use database of Supbot API instead of coding it manually, then we will create Pin Feature inside Whatsapp using which clients will be able to pin messages, delete the pinned messages and show pins when needed.
In simple terms, database is a way by which we can store, access, manage and update data easily. Supbot API uses JDBC and SQlite to create database. Database class of Supbot API implements some methods which simplifies creating new tables and versioning them.
How to create Database?
In order to create a new database table in Supbot you will have to,
- First create a class and extend it with Database.
- Then create a constructor.
- Then add the Database to the bot by writing Bot.getDatabaseManager().add() method in addThingsInBot method in Main class.
Database Constructor Definition
Constructor of Client Database
For the Bot to use the correct version of the database table you want, you will have to specify which version you want along with the database name in the Constructor. Name is used as a reference to log in console and to save the version in config.json file.
When the bot is executed, the bot will try to upgrade/dowgrade to the specified version from the current version using VersionChangers. New VersionChangers can be added by using
addVersionChanger() method. The version of newly created Database is 0, so to use the database you will have to use addVersionChanger to upgrade it to version 1.
addVersionChanger definition in Database class
addVersionChanger of ChatgroupDatabase
Console output when Database upgrades
addVersionChanger method helps us to call specific query according to which version it currently is, so if the current version is 2 and you tell it to use version 3, it will use the addVersionChanger with version 3 to upgrade the database.
VersionChanger contains 2 methods that have to be implemented,
upTo method will upgrade the table to specified version,
downFrom method will downgrade from specified version to previous one. In the
downFrom methods jdbc connection is passed using the parameter which can be used to create statements to execute query in the sqlite database.
saveClient in ClientDatabase
For the bot to comunicate with the database, to insert/select/delete from database static methods are using in Supbot API, in the method a connection has to be created with the Supbot SQlite database file using the
connect() method (connect method is a method of Supbot not JDBC that is defined inside Database class). Then by using
prepareStatement() queries can be created and
executeQuery() to executue them.
After we learn about Database in Supbot API, we will create a Pin feature. The Pin feature will have 3 commands.
- pin, which will save the message.
- show-pins, which shows all the saved messages in the group.
- delete-pin, which delete the saved pin.
Creating the pin command you will learn how you could save a row into the database along with the client information to remember the author and the group in which it was pinned.
Creating the show pin command you will learn how you could select rows from database and display it with format as a whatsapp message.
Creating the delete-pin command you will learn how to delete targeted rows from the database and how can you add logic like, to check if the client has enough permission to perform an action.