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);
	}
	
}