Package org.tmatesoft.sqljet.core.table

Examples of org.tmatesoft.sqljet.core.table.ISqlJetTable


                    // open db and read schema.
                    progress.start("Loading Data...", myPageSize);
                    SqlJetDb db = null;
                    try {
                        db = SqlJetDb.open(dbFile, true);
                        ISqlJetTable table = db.getTable(tableName);
                        model = DataTableModel.createInstance(table, row, myPageSize, progress);
                    } catch (final Throwable th) {
                        model = new DefaultTableModel();
                        SwingUtilities.invokeLater(new Runnable() {
                            public void run() {
View Full Code Here


       
        // insert rows:
        db.beginTransaction(SqlJetTransactionMode.WRITE);
        try {
            Calendar calendar = Calendar.getInstance();
            ISqlJetTable table = db.getTable(TABLE_NAME);
            calendar.clear();
            calendar.set(1981, 4, 19);
            table.insert("Prochaskova", "Elena", calendar.getTimeInMillis());
            calendar.set(1967, 5, 19);
            table.insert("Scherbina", "Sergei", calendar.getTimeInMillis());
            calendar.set(1987, 6, 19);
            table.insert("Vadishev", "Semen", calendar.getTimeInMillis());
            calendar.set(1982, 7, 19);
            table.insert("Sinjushkin", "Alexander", calendar.getTimeInMillis());
            calendar.set(1979, 8, 19);
            table.insert("Stadnik", "Dmitry", calendar.getTimeInMillis());
            calendar.set(1977, 9, 19);
            table.insert("Kitaev", "Alexander", calendar.getTimeInMillis());
        } finally {
            db.commit();
        }

        ISqlJetTable table = db.getTable(TABLE_NAME);

        // getting all rows in table, sorted by PK.       
        System.out.println();
        System.out.println(">All employees in order defined by PK (" + table.getPrimaryKeyIndexName() + "):");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.order(table.getPrimaryKeyIndexName()));
        } finally {
            db.commit();
        }

        // getting all rows in table, sorted by PK.       
        System.out.println();
        System.out.println(">All employees in order defined by " + DOB_INDEX + ", reversed:");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.order(table.getPrimaryKeyIndexName()).reverse());
        } finally {
            db.commit();
        }
       
        // getting all rows in table, sorted by index.       
        System.out.println();
        System.out.println(">All employees in order defined by " + FULL_NAME_INDEX + " :");
        System.out.println();

        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.order(FULL_NAME_INDEX));
        } finally {
            db.commit();
        }

        // getting rows in table with exact indexed field value.
        System.out.println();
        System.out.println(">Alexanders:");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.lookup(FULL_NAME_INDEX, "Alexander"));
        } finally {
            db.commit();
        }
       
        // getting rows in table with indexed field value in certain scope.       
        System.out.println();
        System.out.println(">Employees with full name in scope [B, I]:");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.scope(FULL_NAME_INDEX, new Object[] {"B"}, new Object[] {"I"}));
        } finally {
            db.commit();
        }

        Calendar calendar = Calendar.getInstance();
        calendar.setTime(new Date(System.currentTimeMillis()));
        calendar.add(Calendar.YEAR, -30);

        System.out.println();
        System.out.println(">Deleting rows of employees older than 30 years old.");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.WRITE);
        try {
            ISqlJetCursor deleteCursor = table.scope(DOB_INDEX,
                     new Object[] {Long.MIN_VALUE},
                     new Object[] {calendar.getTimeInMillis()});
            while (!deleteCursor.eof()) {
                System.out.println("Deleting: " +
                        deleteCursor.getRowId() + " : " +
                        deleteCursor.getString(FIRST_NAME_FIELD) + " " +
                        deleteCursor.getString(SECOND_NAME_FIELD) + " was born on " +
                        formatDate(deleteCursor.getInteger(DOB_FIELD)));
                deleteCursor.delete();
            }
            deleteCursor.close();
        } finally {
            db.commit();
        }

        System.out.println();
        System.out.println(">After deletion in row id order:");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.open());
        } finally {
            db.commit();
        }

        db.beginTransaction(SqlJetTransactionMode.WRITE);
        ISqlJetCursor updateCursor = null;
        try {
            table.insert("Smith", "John", 0);
            calendar.setTime(new Date(System.currentTimeMillis()));
            updateCursor = table.open();
            do {
                updateCursor.update(updateCursor.getValue(SECOND_NAME_FIELD), updateCursor.getValue(FIRST_NAME_FIELD), calendar.getTimeInMillis());
            } while(updateCursor.next());
        } finally {
            updateCursor.close();
            db.commit();
        }

        System.out.println();
        System.out.println(">After insertion of a new record and updating dates (by PK):");
        System.out.println();
        db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
        try {
            printRecords(table.order(table.getPrimaryKeyIndexName()));
            System.out.println();
            System.out.println(">Same in order defined by " + FULL_NAME_INDEX + " :");
            System.out.println();
            printRecords(table.order(FULL_NAME_INDEX));
        } finally {
            db.commit();
        }
      
        System.out.println();
View Full Code Here

public class StringLengthTest extends AbstractNewDbTest {

  @Test
  public void stringLengthTest() throws SqlJetException {
    db.createTable("CREATE TABLE record (a NONE NOT NULL)");
    ISqlJetTable table = db.getTable("record");
    table.insert("10");
    db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
    try {
      ISqlJetCursor cursor = table.open();
      boolean more = !cursor.eof();
      while (more) {
        String s = cursor.getString(0);
        Assert.assertEquals(2, s.length());
        more = cursor.next();
View Full Code Here

    @Test
    public void testInsertOrUpdate1() throws SqlJetException {
        final ISqlJetTableDef createTable = db.createTable("CREATE TABLE IF NOT EXISTS files ("
                + " name TEXT NOT NULL PRIMARY KEY, time INTEGER)");
        final ISqlJetTable table = db.getTable(createTable.getName());
        for (long actual = 1; actual < 10; actual++) {
            table.insertOr(SqlJetConflictAction.REPLACE, "test", new Long(actual));
            assertInsertedOrReplaced(table, actual, "test");
        }
        assertCount(table, 1);
    }
View Full Code Here

    db.beginTransaction(SqlJetTransactionMode.EXCLUSIVE);

    try {

      // Create X rows
      final ISqlJetTable testTable = db.getTable("test");
      for (int i = 0; i < rowCount; i++) {
        final Map<String, Object> values = new HashMap<String, Object>();
        values.put("preview", createByte());

        final long pkTestTable = testTable.insertByFieldNames(values);
        System.out.println("PK : " + pkTestTable);
      }

      // Commit & Close
      db.commit();
View Full Code Here

    try {

      // Remove all rows
      ISqlJetCursor curseur = null;
      final ISqlJetTable tableDossiers = db.getTable("test");
      curseur = tableDossiers.open();
      while (!curseur.eof()) {
        curseur.delete();
      }
      curseur.close();
View Full Code Here

    @Test
    public void testInsertOrUpdate2() throws SqlJetException {
        final ISqlJetTableDef createTable = db.createTable("CREATE TABLE IF NOT EXISTS files ("
                + " id INTEGER NOT NULL PRIMARY KEY, time INTEGER)");
        final ISqlJetTable table = db.getTable(createTable.getName());
        for (long actual = 1; actual < 10; actual++) {
            table.insertOr(SqlJetConflictAction.REPLACE, 1, new Long(actual));
            assertInsertedOrReplaced(table, actual, 1);
        }
        assertCount(table, 1);
    }
View Full Code Here

        db.createTable("CREATE TABLE IF NOT EXISTS places (place_id INTEGER PRIMARY KEY AUTOINCREMENT,geoid INTEGER NOT NULL,class_id INTEGER NOT NULL,country_id INTEGER NOT NULL,name TEXT NOT NULL,user_defined INTEGER NOT NULL,location_lon INTEGER,location_lat INTEGER)");
    }

    @Test
    public void testTransaction() throws Exception {
        ISqlJetTable table = db.getTable("places");
        db.beginTransaction(SqlJetTransactionMode.WRITE);

        for (int i = 0; i < 2000; ++i) {
            table.insertOr(SqlJetConflictAction.REPLACE, null, i, i % 2, i % 3, 4, "hhhh", 6, 7);
        }
        db.rollback();
        db.beginTransaction(SqlJetTransactionMode.WRITE);

        for (int i = 0; i < 2000; ++i) {
            table.insertOr(SqlJetConflictAction.REPLACE, null, i, i % 2, i % 3, 4, "kkk", 6, 7);
        }
        db.commit();
    }
View Full Code Here

                }
            }, SqlJetTransactionMode.WRITE);
        } catch (Exception e) {
            e.printStackTrace();
        }
        final ISqlJetTable table = db.getTable("contacts");
        final String sql = table.getDefinition().toSQL();
        //Logger.getAnonymousLogger().info(sql);

        Assert.assertTrue(sql.matches("(?i).*LENGTH\\s*\\(\\s*phone\\s*\\).*"));
        Assert.assertFalse(sql.matches("(?i).*LENGTH\\s*\\(\\s*\\).*"));

 
View Full Code Here

    @Test
    public void testUniqueIndex() throws Exception {

        db.runTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb arg0) throws SqlJetException {
                ISqlJetTable table = db.getTable("names");
                table.insertOr(SqlJetConflictAction.REPLACE, null, "same name");
                table.insertOr(SqlJetConflictAction.REPLACE, null, "same name");

                return null;
            }
        }, SqlJetTransactionMode.WRITE); // I would expect transaction fails or
                                         // one record in table. But in table
                                         // are two records!

        Integer count = (Integer) db.runTransaction(new ISqlJetTransaction() {
            public Object run(SqlJetDb arg0) throws SqlJetException {
                ISqlJetTable table = db.getTable("names");
                ISqlJetCursor cur = table.order(null);
                Integer counter = 0;
                if (!cur.eof()) {
                    do {
                        ++counter;
                    } while (cur.next());
View Full Code Here

TOP

Related Classes of org.tmatesoft.sqljet.core.table.ISqlJetTable

Copyright © 2018 www.massapicom. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.