Codename One : Connecting to MySQL database and perfoming basic CRUDs

Hello coders !
Following up with what we started in this tutorial, we’ll add some books to a database, retreive them to show them in a list, and eventually edit their information or entirely delete them. To do so, we need to create a database. So, we need some software to be installed on our machine and few set up steps.

Requirements 

We need to install a webserver stack, which is a set of specific applications dedicated to performing particular tasks . In our case, we’ll download and install WampServer. Wamp is an acronym for ‘Windows Apache MySQL PHP’, so ‘the specific applications for the wamp stack’ are: Apache, MySQL, and phpMyAdmin. Apache is the web server software, MySQL is the database server, phpMyAdmin is a tool for managing the database and php is the scripting language to be used.
Download wamp here, install it following this tutorial and you’re ready to go.

Creating the database

Creating the database is simple, you first need to open the phpMyAdmin tool by running wamp and navigating to http://localhost/phpmyadmin from your web browser. Then you create your database either manually by clicking on new, and/or naming your database and clicking create :

( then adding your tables and populating them with data )

or, by executing the appropriate sql queries for creating and populating your new database :

The video attached to this tutorial might make it clearer for you to create your database with phpMyAdmin . It is in Tunisian dialect, but the code and manipulations are still clear.

CRUD operations

In order to manage our objects and store them in or retrieve them from databases affectively, all in keeping our code as clean as possible, we should at least provide a ‘data persistence strategy‘. We have multiple options going from implementing a design pattern to integrating an API to ‘do the work for us’. For this very basic demo app, we’ll implement a design pattern called ‘Data Access Object‘ or DAO, just for the sake of learning .
As mentioned in the link provided ” the idea is that instead of having the domain logic communicate directly with the database, file system, web service, or whatever persistence mechanism your application uses, the domain logic speaks to a DAO layer instead. This DAO layer then communicates with the underlying persistence system or service. ” We’ll create then a DAO object for each of our business objects (we actually have the one and only Book entity !). This object, or class communicates with the Books table in our database.

Let’s add a BookDAO.java to the daos package, and try to use it in adding a book to the database:

public class BookDAO {
    
    private ConnectionRequest connectionRequest;

    public void addBook(Book book){
        connectionRequest=new ConnectionRequest(){
            @Override
            protected void postResponse() {
                //afficher popup
            Dialog d = new Dialog("Add to my book shelf");
            TextArea popupBody = new TextArea("Book successfully added");
            popupBody.setUIID("PopupBody");
            popupBody.setEditable(false);
            d.setLayout(new BorderLayout());
            d.add(BorderLayout.CENTER, popupBody);
            d.showDialog();
            }
        };
        connectionRequest.setUrl("http://localhost/shelfie/insert.php?title=" + book.getTitle() + "&author=" + book.getAuthor()+"&category="+book.getCategory()+"&isbn="+book.getIsbn());
        NetworkManager.getInstance().addToQueue(connectionRequest);
    }
    public void removeBook(){
        
    }
   
    public void updateBook(){
        
    }

    public void findAllBooks(){
        
    }
    
}

You need to add your php files under wamp’s root directory ( propbably c:\wamp\www if you installed it in c:\wamp) . Here is the script for insert.php :

<?php
require_once('connect.php');

$title=$_GET['title'];
$author=$_GET['author'];
$category=$_GET['category'];
$isbn=$_GET['isbn'];
$sql = "INSERT INTO books (title,author,category,isbn)
VALUES ( '$title','$author','$category','$isbn')";

if (mysqli_query($conn, $sql)) {
 echo "success";
} else {
 echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

You can notice importing a connect.php file, I provided all php scripts and java code at the end of the tutorial, all you need to get by now is ‘ the logic ‘

After preparing the DAO and the php scripts, consider adding this to the addBtn actionListener in the AddBook class :

addBtn.addActionListener((ActionListener) (ActionEvent evt) -> {
            // add a book
            Book typedBook = new Book(authorTf.getText(),titleTf.getText(),categoryTf.getText(),Integer.parseInt(isbnTf.getText()));
            new  BookDAO().addBook(typedBook);
            });

Check your database through the phpMyAdmin tool and you’ll notice your book was added. The removeBook() and updateBook() methods work following this same logic.
This screenshot showcases the results  of the addBook() method :

Now to select all books and display them in a list, we’ll code this in the findAllBooks() method :

public void findAllBooks(){
        connectionRequest = new ConnectionRequest() {
        List books = new ArrayList<>();
            @Override
            protected void readResponse(InputStream in) throws IOException {

                JSONParser json = new JSONParser();
                try {
                    Reader reader = new InputStreamReader(in, "UTF-8");

                    Map<String, Object> data = json.parseJSON(reader);
                    List<Map<String, Object>> content = (List<Map<String, Object>>) data.get("root");
                    books.clear();
                  
                    for (Map<String, Object> obj : content) {
                        books.add(new Book((String) obj.get("title"),(String) obj.get("author"),(String) obj.get("category"),Integer.parseInt((String) obj.get("isbn")))
                        );
                    }
                } catch (IOException err) {
                    Log.e(err);
                }
            }

            @Override
            protected void postResponse() {
                //System.out.println(libs.size());
                listOfBooks = new Form();
                com.codename1.ui.List uiLibsList = new com.codename1.ui.List();
                ArrayList libsNoms = new ArrayList<>();
                for(Book l :books){
                    libsNoms.add(l.getTitle());
                }
                com.codename1.ui.list.DefaultListModel listModel = new com.codename1.ui.list.DefaultListModel<>(libsNoms);
                uiLibsList.setModel(listModel);
                uiLibsList.addActionListener(new ActionListener() {

                    @Override
                    public void actionPerformed(ActionEvent evt) {
                        Book currentBook = books.get(uiLibsList.getCurrentSelected());
                        new Abook(currentBook.getTitle(),currentBook.getAuthor(),currentBook.getCategory(),currentBook.getIsbn()).show();
                    }
                });
                listOfBooks.setLayout(new BorderLayout());
                listOfBooks.add(BorderLayout.NORTH,uiLibsList);
                listOfBooks.add(BorderLayout.SOUTH,Statics.createBackBtn());
                listOfBooks.show();             
            }
        };
        connectionRequest.setUrl("http://localhost/shelfie/getbooks.php");
        NetworkManager.getInstance().addToQueue(connectionRequest);

and add this under the listBookBtn’s actionListener :

listBooksBtn.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent evt) {
                new BookDAO().findAllBooks();
            }
        });

Under the findAllBooks() methods we created a new form, added a com.codename1.ui.List to it excuted the show() method. For the codename1.ui.List, we defined a ListModel with an ArrayList of books’ titles retrieved from the database.

That’s all coders !

You create your DAO layer and use it as a bridge between your domain logic and your database. In other words you implement DAO classes, and call ‘their crud methods’ in your code whenever you need to Create,Read,Update or Delete your database data.

Source code for this project is provided here. You will find the complete code for BookDAO.java class, and all of the php scripts needed. Plus, you might like this video ! Check it out, even if it’s not in english, it helps much.

Happy coding !