Package totalcross.db.sqlite

Class SQLiteUtil

  • public class SQLiteUtil
extends java.lang.Object
    Utility class to make convertion 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 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 '

      • shrinkDB

        public void shrinkDB()
              throws java.sql.SQLException
        Rebuild and shrink the entire database.
        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