totalcross.db.sqlite

Class SQLiteUtil

java.lang.Object

totalcross.db.sqlite.SQLiteUtil

public class SQLiteUtil
extends java.lang.Object

Utility class to make convertion from Litebase to SQLite easier. Important rules about date and time in SQLite.

• DATE type: must be in the form: YYYY-MM-DD
• TIME type: must be in the form: YYYY-MM-DD HH:MM:SS.MMM

When using 'between' or any other date/time comparation, the arguments MUST MATCH the type form. So, in "select borndate from test where borndate between '2014-12-10' and '2014-12-14'":

• If borndate is a DATE, the comparison will succeed.
• If borndate is a TIME, the comparison will fail. To make it work, use "select borndate from test where borndate between '2014-12-10 00:00:00.000' and '2014-12-14 00:00:00.000'".

See Also:

Date.getSQLString(), Time.getSQLString()

Field Summary

Fields

Modifier and Type	Field and Description
java.lang.String	fullPath
int	vectorInitialSize

Constructor Summary

Constructors

Constructor and Description
SQLiteUtil() Open a connection at memory
SQLiteUtil(Connection con) Use the given connection in all operations
SQLiteUtil(java.lang.String table) Open a connection at the given table
SQLiteUtil(java.lang.String path, java.lang.String table) Open a connection at the given path and table

Method Summary

Modifier and Type	Method and Description
void	close()
void	close(ResultSet rs)
Connection	con() Returns the connecton with the parameters passed in the constructor
ResultSet	executeQuery(java.lang.String s)
static boolean	existsIndex(java.lang.String tableName, java.lang.String indexName, Connection connection) Check if the index with the name exists in this table
void	finishTransaction()
static java.lang.String	fixQuote(java.lang.String s, boolean toSqlite) Handles single quote when inserting or retrieving data from Sqlite.
static Date	fromSqlDate(java.lang.String sqldate) Changes a date in format 2014-02-19 00:00:00:000 to a totalcross.util.Date.
int	getColCount(ResultSet rs)
int	getColumnType(ResultSetMetaData md, int col) SQLite has a problem (not sure if its a bug) where it returns DATE for both DATE and DATETIME types, so this method returns the correct correspondence: DATE for date and TIME for DATETIME (remember that in TotalCross, a Time object also contains the date).
int	getInt(ResultSet rs)
int	getInt(java.lang.String sql)
int	getRowCount(java.lang.String table)
int	getShort(ResultSet rs)
int	getShort(java.lang.String sql)
java.lang.String	getString(java.lang.String sql)
java.lang.String[][]	getStrings(ResultSet rs)
java.lang.String[][]	getStrings(ResultSet rs, Vector v)
java.lang.String[][]	getStrings(ResultSet rs, Vector v, int[] decimalPlaces)
java.lang.String[][]	getStrings(java.lang.String sql)
java.lang.String[]	getStrings1(ResultSet rs)
java.lang.String[]	getStrings1(java.lang.String sql)
boolean	isNotEmpty(ResultSet rs)
boolean	isNotEmpty(java.lang.String sql)
java.lang.String[]	listAllTables()
PreparedStatement	prepareStatement(java.lang.String sql)
void	rollback()
void	shrinkDB() Rebuild and shrink the entire database, like the old Litebase's purge method, but in this case it applies to all tables.
void	startTransaction()
boolean	tableExists(java.lang.String tab)

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

vectorInitialSize

public int vectorInitialSize

fullPath

public java.lang.String fullPath

Constructor Detail

SQLiteUtil

public SQLiteUtil(java.lang.String table)
           throws java.sql.SQLException

Open a connection at the given table

Throws:

java.sql.SQLException

SQLiteUtil

public SQLiteUtil(java.lang.String path,
                  java.lang.String table)
           throws java.sql.SQLException

Open a connection at the given path and table

Throws:

java.sql.SQLException

SQLiteUtil

public SQLiteUtil()
           throws java.sql.SQLException

Open a connection at memory

Throws:

java.sql.SQLException

SQLiteUtil

public SQLiteUtil(Connection con)

Use the given connection in all operations

Method Detail

con

public Connection con()
               throws java.sql.SQLException

Returns the connecton with the parameters passed in the constructor

Throws:

java.sql.SQLException

close

public void close()

tableExists

public boolean tableExists(java.lang.String tab)
                    throws java.sql.SQLException

Throws:

java.sql.SQLException

isNotEmpty

public boolean isNotEmpty(java.lang.String sql)

isNotEmpty

public boolean isNotEmpty(ResultSet rs)

getColCount

public int getColCount(ResultSet rs)
                throws java.sql.SQLException

Throws:

java.sql.SQLException

executeQuery

public ResultSet executeQuery(java.lang.String s)
                       throws java.sql.SQLException

Throws:

java.sql.SQLException

close

public void close(ResultSet rs)

getStrings1

public java.lang.String[] getStrings1(java.lang.String sql)

getStrings1

public java.lang.String[] getStrings1(ResultSet rs)

getColumnType

public int getColumnType(ResultSetMetaData md,
                         int col)
                  throws java.sql.SQLException

SQLite has a problem (not sure if its a bug) where it returns DATE for both DATE and DATETIME types, so this method returns the correct correspondence: DATE for date and TIME for DATETIME (remember that in TotalCross, a Time object also contains the date).

Parameters:

md - The ResultSetMetaData obtained with ResultSet.getMetaData.

col - The column, starting from 1.

Throws:

java.sql.SQLException

getStrings

public java.lang.String[][] getStrings(ResultSet rs,
                                       Vector v)
                                throws java.sql.SQLException

Throws:

java.sql.SQLException

getStrings

public java.lang.String[][] getStrings(ResultSet rs,
                                       Vector v,
                                       int[] decimalPlaces)
                                throws java.sql.SQLException

Throws:

java.sql.SQLException

getStrings

public java.lang.String[][] getStrings(ResultSet rs)
                                throws java.sql.SQLException

Throws:

java.sql.SQLException

getStrings

public java.lang.String[][] getStrings(java.lang.String sql)
                                throws java.sql.SQLException

Throws:

java.sql.SQLException

getString

public java.lang.String getString(java.lang.String sql)
                           throws java.sql.SQLException

Throws:

java.sql.SQLException

getInt

public int getInt(java.lang.String sql)
           throws java.sql.SQLException

Throws:

java.sql.SQLException

getInt

public int getInt(ResultSet rs)
           throws java.sql.SQLException

Throws:

java.sql.SQLException

getShort

public int getShort(java.lang.String sql)
             throws java.sql.SQLException

Throws:

java.sql.SQLException

getShort

public int getShort(ResultSet rs)
             throws java.sql.SQLException

Throws:

java.sql.SQLException

getRowCount

public int getRowCount(java.lang.String table)

startTransaction

public void startTransaction()
                      throws java.sql.SQLException

Throws:

java.sql.SQLException

finishTransaction

public void finishTransaction()
                       throws java.sql.SQLException

Throws:

java.sql.SQLException

rollback

public void rollback()
              throws java.sql.SQLException

Throws:

java.sql.SQLException

prepareStatement

public PreparedStatement prepareStatement(java.lang.String sql)
                                   throws java.sql.SQLException

Throws:

java.sql.SQLException

listAllTables

public java.lang.String[] listAllTables()

fixQuote

public static java.lang.String fixQuote(java.lang.String s,
                                        boolean toSqlite)

Handles single quote when inserting or retrieving data from Sqlite. Example:

 String s = SQLiteUtil.fixQuote("'",true); // returns ''
 String s = SQLiteUtil.fixQuote("''",false); // returns '
 

fromSqlDate

public static Date fromSqlDate(java.lang.String sqldate)
                        throws InvalidDateException

Changes a date in format 2014-02-19 00:00:00:000 to a totalcross.util.Date.

Throws:

InvalidDateException

shrinkDB

public void shrinkDB()
              throws java.sql.SQLException

Rebuild and shrink the entire database, like the old Litebase's purge method, but in this case it applies to all tables.

Throws:

java.sql.SQLException

existsIndex

public static final boolean existsIndex(java.lang.String tableName,
                                        java.lang.String indexName,
                                        Connection connection)
                                 throws java.sql.SQLException

Check if the index with the name exists in this table

Parameters:

tableName - to check

indexName - to check

connection - with the database

Returns:

true if the index exists, otherwise false;

Throws:

java.sql.SQLException