ofrendo
4/17/2018 - 9:14 AM
SQLite Java DB class
package com.sap.charging.playground.util.sqlite;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface SQLiteAttributeNotNull {
}
package com.sap.charging.playground.util.sqlite;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface SQLiteAttributeKey {
}
package com.sap.charging.playground.util.sqlite;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface SQLiteAttributeIgnore {
}
package com.sap.charging.playground.util.sqlite;
public class SQLiteAttribute {
private final Object value;
public final String name;
public final String dataType;
public final boolean isKey;
public final boolean isNotNull;
public SQLiteAttribute(Object value, String name, String dataType, boolean isKey, boolean isNotNull) {
this.value = value;
this.name = name;
this.dataType = dataType;
this.isKey = isKey;
this.isNotNull = isNotNull;
}
public String getValueString() {
if (dataType.equals("TEXT"))
return "'" + value.toString() + "'";
else
return value.toString();
}
public Object getValue() {
return value;
}
@Override
public String toString() {
return "Name=" + name + ", value=" + value + ", dataType=" + dataType + ", isKey=" + isKey + ", isNotNull=" + isNotNull;
}
}
package com.sap.charging.playground.util.sqlite;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public abstract class SQLiteTableRow {
@SQLiteAttributeIgnore
private ArrayList<SQLiteAttribute> attributes;
public SQLiteTableRow() {
}
public abstract String getTableName();
private ArrayList<SQLiteAttribute> buildAttributes() throws IllegalArgumentException, IllegalAccessException {
ArrayList<SQLiteAttribute> result = new ArrayList<>();
Field[] fields = this.getClass().getFields();
for (Field field : fields) {
boolean ignoreThis = field.isAnnotationPresent(SQLiteAttributeIgnore.class);
if (ignoreThis == true)
continue;
boolean isKey = field.isAnnotationPresent(SQLiteAttributeKey.class);
boolean isNotNull = field.isAnnotationPresent(SQLiteAttributeNotNull.class);
Class<?> attributeClass = field.getType();
String name = field.getName();
SQLiteAttribute attribute = new SQLiteAttribute(
field.get(this),
name,
this.getSQLiteDataType(attributeClass),
isKey,
isNotNull);
result.add(attribute);
}
return result;
}
private ArrayList<SQLiteAttribute> getAttributes() {
if (this.attributes == null) {
try {
this.attributes = buildAttributes();
} catch (IllegalArgumentException | IllegalAccessException e) {
e.printStackTrace();
System.exit(1);
}
}
return this.attributes;
}
protected String getSQLiteDataType(Class<?> attributeClass) {
if (attributeClass == Integer.class || attributeClass == int.class) {
return "INTEGER";
}
if (attributeClass == Double.class || attributeClass == double.class) {
return "REAL";
}
if (attributeClass == String.class) {
return "TEXT";
}
return null;
}
public String getCreateTableStatement() {
ArrayList<SQLiteAttribute> attributes = this.getAttributes();
String result = "CREATE TABLE " + getTableName() + "(\n";
// Column definitions
for (int i=0;i<attributes.size();i++) {
SQLiteAttribute attribute = attributes.get(i);
result += "\t" + attribute.name + " " + attribute.dataType;
if (attribute.isNotNull)
result += " NOT NULL";
if (i != attributes.size()-1)
result += ", \n";
}
// Primary key
boolean isFirst = true;
List<SQLiteAttribute> primaryKeyAttributes = attributes.stream().filter(a -> a.isKey).collect(Collectors.toList());
for (int i=0;i<primaryKeyAttributes.size();i++) {
SQLiteAttribute attribute = primaryKeyAttributes.get(i);
if (isFirst == true) {
result += ", \n" + "\t" + "PRIMARY KEY (";
isFirst = false;
}
result += attribute.name;
if (i != primaryKeyAttributes.size()-1)
result += ", ";
}
if (isFirst == false) { // Cheeck if any were added
result += ")";
}
result += "\n);";
return result;
}
public String getInsertStatement() {
ArrayList<SQLiteAttribute> attributes = this.getAttributes();
String result = "INSERT INTO " + getTableName() + " (";
// Add list of columns
for (int i=0;i<attributes.size();i++) {
SQLiteAttribute attribute = attributes.get(i);
result += attribute.name;
if (i != attributes.size()-1)
result += ", ";
}
result += "\nVALUES (";
for (int i=0;i<attributes.size();i++) {
SQLiteAttribute attribute = attributes.get(i);
result += attribute.getValueString();
if (i != attributes.size()-1)
result += ", ";
}
result += ");";
return result;
}
public String getSelectRowsStatement() {
List<SQLiteAttribute> attributes = this.getAttributes();
attributes = attributes.stream().filter(a -> a.isKey).collect(Collectors.toList());
String result = "SELECT * FROM " + getTableName() + " WHERE \n";
for (int i=0;i<attributes.size();i++) {
SQLiteAttribute attribute = attributes.get(i);
if (attribute.getValue() != null) {
result += "\t" + attribute.name + "=" + attribute.getValueString();
}
if (i != attributes.size()-1)
result += " AND\n";
}
return result;
}
}
package com.sap.charging.playground.util.sqlite;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLiteDB {
private String dbUrl;
private final Connection connection;
public SQLiteDB() {
this(null);
}
public SQLiteDB(String dbUrl) {
this.dbUrl = dbUrl;
this.connection = getDBConnection();
}
protected Connection getDBConnection() {
if (connection != null)
return connection;
try {
Connection connection = DriverManager.getConnection(getDBUrl());
return connection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return null;
}
protected String getDBUrl() {
return dbUrl;
}
protected synchronized ResultSet executeQuery(String sql) {
try {
Statement stmt = getDBConnection().createStatement();
ResultSet rs = stmt.executeQuery(sql);
return rs;
} catch (SQLException e) {
System.out.println("Executed statement:");
System.out.println(sql);
e.printStackTrace();
}
return null;
}
protected synchronized void executeStatement(String sql) {
try {
Statement stmt = getDBConnection().createStatement();
stmt.execute(sql);
} catch (SQLException e) {
System.out.println("Executed statement:");
System.out.println(sql);
e.printStackTrace();
}
}
protected synchronized boolean tableExists(String tableName) {
String sql = "SELECT name FROM sqlite_master "
+ "WHERE type='table'"
+ " AND name='" + tableName + "'";
try {
while (executeQuery(sql).next()) {
return true;
}
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("SQLiteDB::tableExists Table '" + tableName + "' does not exist yet!");
return false;
}
public synchronized boolean tableExists(SQLiteTableRow tableRow) {
return tableExists(tableRow.getTableName());
}
public synchronized void createTable(SQLiteTableRow tableRow) {
String sql = tableRow.getCreateTableStatement();
executeStatement(sql);
}
public synchronized void insert(SQLiteTableRow tableRow, boolean forceTableCreate) {
if (forceTableCreate == true && tableExists(tableRow) == false)
createTable(tableRow);
String sql = tableRow.getInsertStatement();
executeStatement(sql);
}
public synchronized void insert(SQLiteTableRow tableRow) {
insert(tableRow, true);
}
public synchronized boolean rowExists(SQLiteTableRow tableRow, boolean forceTableCreate) {
if (forceTableCreate == true && tableExists(tableRow) == false)
createTable(tableRow);
String sql = tableRow.getSelectRowsStatement();
ResultSet resultSet = executeQuery(sql);
try {
while (resultSet.next())
return true;
} catch (SQLException e) {
System.out.println("Executed statement:");
System.out.println(sql);
e.printStackTrace();
}
return false;
}
public synchronized boolean rowExists(SQLiteTableRow tableRow) {
return rowExists(tableRow, true);
}
}