Database Code Generator Tool

Hi there!! You all possibly have come across designing database and creating DAO for the database. Isn't it little painful or may be boring. Yes that's affirmative for me! I feel bore creating DAO; copying entity name from the database and writing the logic for database operation and all.

Isn't it would be awesome if there exist some tool which can automate the process and give you the generated code? Sounds good right? Yes, we have developed one such tool which can save your few days of work in a matter of just few minutes. Let's have some few minutes understanding the tool.

You can download copy of this user guide/article from here.

Introduction

The Database Code Generator Tool is a software tool, which can generate DAO (Data Access Object) based on OODBMS for Java-Mysql and Android from MySql database. The tool is a bundle of a PC application and an Android application. This document explains the use of the tool and related technical details.

Problem statement-

Database management system is a vital module for any complex software design. After designing the database schema with any database tool like Mysql, the developers need to write the code for database interaction. However, this traditional method has some shortcomings-

• Time Constraint: The developers spend ample amount of time writing the Database model classes, database adapters and query logic which might take few days to weeks based on complexity of the database design.
• Human error:  To err is human. The developer might make some silly mistakes while converting the database to code. For e.g. Table or field name mismatch due to typo.
• Individual coding pattern: Everyone has their own coding pattern. In a big project where many developers work, individual can write their own database interaction logic which can be difficult to maintain while going forward.
• Extensive Testing activities: Developers need to spend much time for sanity test.
• Maintenance issue: Due to lack of a standard database framework, it is difficult to maintain the code.

The solution and Advantages-

The DBCodeGenerator is a simple tool which can minimize the above stated problems.

• Less time for code: Once the Mysql database schema is designed, it takes less than 3 minutes to generate the entire DAO code for your project.  
• No Human Error: The tool generates the code based on the data field and type you have designed in your database; which includes getter and setter methods, database cursor to object creation in all model classes.
• Common framework: A common framework which can be used by all the developers working in your project.
• Less Testing activities: Since the framework has been tested for all CRUD (Create Read Update Delete) operations, you need to spend very less time testing your code.
• Easy to maintain: The framework is based on Abstract design pattern. All major database operation related codes are placed at one place. Hence it is easy to maintain.
• Ready to deploy: The generated framework is ready to deploy with no to minimum (if any) change in the code.  Only package name and import statements are need to be taken care. Hereby reducing the database development activity time by more than 95%.
• JSON support: The tool also generates ad-on methods for data object to JSON string conversion and vice versa inside all model classes.

The tool: Architecture

The tool is easy to use. It has following components-

• Desktop PC based application for Windows, MAC and Linux.
• Android based application.

The architecture of the tool can be defined below.

1. The user creates the database schema using Mysql.
2. Using the desktop tool user generates one intermediate .gen file from the selected database. The file is encrypted with a key. Hence ensuring security to your database schema design.
3. User place the .gen file in an Android phone internal SD card.
4. User opens the Android application and select the file from File browser and click generate button.  
5. On successful generation of the code, user would be prompted to share the generated .zip file using email or other means he would like to. Also it can be copy and transfer to desktop from the SD card.

System Requirement

1. Mysql must be installed in your desktop PC and configured properly.
2. JRE (Java Runtime) installed and PATH variable configured properly. Please refer how to configure JAVA environment in your respective OS.
3. An android phone 4.4+ (KitKat) with SD Card mounted.

Supported mysql data type

Most of the major Mysql data types are supported as listed below-

• INT
• VARCHAR
• CHAR
• VARBINARY
• BINARY
• TIMESTAMP
• SMALLINT
• TINYINT
• MEDIUMINT
• BIGINT
• DOUBLE
• BIT
• TEXT
• DATE
• FLOAT
• TIME
• BLOB
• DATETIME

How to use

The windows tool can be downloaded as zip file from this link.

The Android version is a pro version can be downloaded from Google play store link (QR code shown below). In order to support for our development activity you need to contribute a small amount to us. For latest price of the tool please refer the play store page.

The Windows Tool:

The downloaded zip file contains following:

Open the “DbCodeGenIntermediate.jar” file by double clicking or using terminal with command

“java –jar DbCodeGenIntermediate.jar”

• If your java is installed and class path configured properly, it should open the authorization dialog.

• Enter the mysql host, port, username, password and click connect.
• On successful connection to mysql the main window would list all available databases. Select the database you had created. Available tables would be auto populated.

• Select the tables (using ctrl + click) you would like to generate codes for.
• Enter optional target project package name and output file location.
• Click generate.

• Copy the .gen file from the output directory to your phone internal SD card.
• Open the installed DbCodeGen App in your app.

• Select your desired target code base. As of today only Java and Android is supported. Kotlin, PHP support would be added in upcoming versions.
• Select the .gen file from phone SD Card.

• Enter target package name if you want to change. Based on the conventional package structure we will name the generated class package as .model, .dbAdapter and .util if not specified.
• Click generate.

• The generated code base (.zip file) can be shared by the user’s intended share mode available in his phone (e.g. Email, ShareIt etc.). Also it can be copied from the root folder of the phone internal SD card.
• Copy the generated model, util and dbAdapter classes into their respective package folders in your project IDE.
• Rename package declaration if needed.
• Use proper import statement as required.
• Fix any build error shown by your IDE and compile successfully.
• Refer Sample Code section below to refer on how to use the framework.
• Congratulations ☺ your ODBMS code is ready for testing.

Sample code

Samples to use the generated code can be found inside the downloaded zip file along with windows tool.

You can access your table by creating the dbAdapter object.

For Example, as in our sample code we have one table called “employee” and we want to insert and employee data.

AbstractSqliteDbAdapter.open(context);// for Android // AbstractDbAdapter.open()// for Java-Mysql EmployeeDbAdapter employeeDbAdapter = new EmployeeDbAdapter(); // Create employee record Employee employee = new Employee(); // set all the properties of the employee. employee.setEmpName("Test"); // insert the object using int ret = employeeDbAdapter.insert(employee); // On success, the ret would be ErrorCodes.ERROR_CODE_SUCCESS // perform other operations like delete update as required. // Once done close the db Connection using AbstractSqliteDbAdapter.close();// for Android //AbstractDbAdapter.close()// for java-mysql

Please refer the sample code, FAQ section and API document for detail explanations on CRUD operations.

License

There is no license of the generated code. Once you generate the code, it all belongs to you. You will maintain the code base for lifetime. You are free to use it anywhere you want. For any technical support please reach us at blogtech90@gmail.com or support.dbcodegen@mobilelabops.space. And of course you can leave your comment below.

Happy Coding :). 

FAQ

Q: - How do I use the framework for database access?

Ans: The use of the framework is pretty easy. You just need to create the dbAdapter object of the table, you want to use for db operations. Below are the steps-

1. Open the database connection before doing your first db interaction.

AbstractSqliteDbAdapter.open(context);// for Android

AbstractDbAdapter.open()// for Java-Mysql

2. Create dbAdapter object of the table. For e.g. we are trying to perform some operation on Employee table of our sample database design.

EmployeeDbAdapter empDbAdapter = new EmployeeDbAdapter();

3. Please look for methods available for your intended database operation.
4. Once you are done with database operations (or on application exit). Call the close() method.

AbstractSqliteDbAdapter.close(); // for Android

AbstractDbAdapter.close()// for java-mysql

Q. I want to insert some data to the table.

Ans: - Create the dbAdapter object and call the insert() method with the model object set with data you want to fill in. Refer Javadoc for details.

Q. Do I need to call open and close every time I do any db operation?

Ans:- Both open and close methods are static and singleton by design. You need to call open only once until next close method invocation. Your database connection would be alive during this time. If you don’t have frequent database interaction, you can close the connection for time being and open it again for the next database operation.

Q. I want to change mysql server location address and user credentials.

Ans: - All these parameters are defined inside MysqlDbManager class. Change Host, Username and password as required.

Q. I am getting SQLException. What I am doing wrong?

Ans: - Make sure your database connection is alive. Also double check your SQL query statement.

Q. How do I write where condition for update and delete method?

Ans: - For e.g. I want to update employee name based on employee ID. The update query should be-

Employee.KEY_NAME = ‘XYZ’ WHERE Employee.KEY_ID = 1

You no need send any “UPDATE” SQL command.

Again foe e.g. I want to delete an employee based on employee id. The delete statement should be-

Employee.KEY_ID = 1

You no need send where clause here. Same case applies for getData and getList method as well.

Q. Do I need to install any additional library for the framework?

Ans: - For Java-Mysql framework yes. You should latest mysql-jdbc-connector jar file for it work with Mysql database.