SQLiTable2Java Programming Examples

These SQLiTable2Java programming examples are based on the following SQLite database table:

CREATE TABLE user_table (
user_id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
purchase_limit REAL,
email TEXT
);

sqlite> PRAGMA table_info(user_table);
0|user_id|INTEGER|0||1
1|first_name|TEXT|0||0
2|last_name|TEXT|0||0
3|purchase_limit|REAL|0||0
4|email|TEXT|0||0

You need an SQL connection if you want try out the examples on this page. That could be something like this:

import org.sqlite.SQLiteConfig;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
// Import here the generated class

public class Test {

    public static void main(String[] args)  {
        java.sql.Connection sqlConnection = null;
        try {
            // get a SQL connection
            SQLiteConfig sqLiteConfig = new SQLiteConfig();
            sqLiteConfig.setBusyTimeout("30000"); // 30 seconds for waiting to locks (this is not a query timeout)
            sqLiteConfig.setSynchronous(SQLiteConfig.SynchronousMode.FULL);

            String jdbcUrl = "jdbc:sqlite:C:\\Scratch3\\test.db";
            sqlConnection = DriverManager.getConnection(jdbcUrl, sqLiteConfig.toProperties());

            // Enter here the examples below
            UserTable userTable = new UserTable(sqlConnection);
            // ...
        }
        catch (Exception ex) {
            ex.printStackTrace();
        }
        finally {
            if (sqlConnection != null) {
                try {
                    sqlConnection.close();
                }
                catch (SQLException se) {
                    se.printStackTrace();
                }
            }
        }
    }
}

Insert Data:

UserTable userTable = new UserTable(sqlConnection);

UserTable.Data data = userTable.getNewDataInstance();
data.userId = 1L;
data.firstName = "Karl";
data.lastName = "Miller";
data.purchaseLimit = 100.0D;
data.email = "karl.miller@domain";
userTable.insertData(data);

data = userTable.getNewDataInstance();
data.userId = 2L;
data.firstName = "Max";
data.lastName = "Keller";
data.purchaseLimit = 50.0D;
data.email = "max.keller@domain";
userTable.insertData(data);

data = userTable.getNewDataInstance();
data.userId = 3L;
data.firstName = "Alex (incomplete)";
userTable.insertData(data);

The data type conversion from SQLite to Java is: TEXT = String, INTEGER = Long, REAL = Double

Select All Data:

ArrayList<UserTable.Data> dataList = userTable.selectAll();
for (UserTable.Data data : dataList) {
    System.out.println(data.toString());
}

Output:
1|Karl|Miller|100.0|karl.miller@domain
2|Max|Keller|50.0|max.keller@domain
3|Alex (incomplete)||-1.0|

Note: The generated code replaces Java null values by default values when reading data. The default values are String = "", Long = -1L, Double = -1.0D

Select Row by Primary Key:

UserTable.Data data = userTable.selectByUserId(2L);
System.out.println(data.toString());

Output:
2|Max|Keller|50.0|max.keller@domain

Select Rows by Column Value:

ArrayList<UserTable.Data> dataList = userTable.selectByPurchaseLimit(-1.0D);
for (UserTable.Data data : dataList) {
    System.out.println(data.toString());
}

Output:
3|Alex (incomplete)||-1.0|

Update Column Value by Primary Key:

userTable.updatePurchaseLimit(2L, 500.0D);

Read and Update All Column Values by Primary Key:

UserTable.Data data = userTable.selectByUserId(3L);
data.firstName = "Alex";
data.lastName = "Vogel";
data.purchaseLimit = 200.0D;
data.email = "alex.vogel@domain";
userTable.updateData(data);

Delete Row by Primary Key:

userTable.deleteData(2L);

Get Min and Max Values for INTEGER and REAL data types:

Double minPurchaseLimit = userTable.getMinPurchaseLimit();
Double maxPurchaseLimit = userTable.getMaxPurchaseLimit();
System.out.println("minPurchaseLimit = " + minPurchaseLimit);
System.out.println("maxPurchaseLimit = " + maxPurchaseLimit);

Output:
minPurchaseLimit = 100.0
maxPurchaseLimit = 200.0

Did you found a bug or have a suggestion? Please use the contact form.