JDO SQL + Escape + DeleteAll + Buffering

  1. Limit
    1. PMF.flush
    2. Drop table
  2. ESCAPE '
  3. Buffering

Limit

SQL limit 20
JDO setRange(0,19)
 final PersistenceManager pm = PMF.get().getPersistenceManager();
 final String query = "select from " ENLF.class.getName() " where " in "=='" q2 "'";
 final javax.jdo.Query que = pm.newQuery(query); que.setRange(0, 23);
 System.out.println(que.toString());
 //System out //SELECT FROM com.lfn.autocomplete.entity.ENLF WHERE e4=='supre' RANGE 0,23 

PMF.flush

 for (final Tidder t : lt) {
  pm.makePersistent(t); pm.flush();
 }
 pm.close(); 

or

 pm.makePersistentAll(lt);
 pm.flush(); 
pm.flush(); is critical. DON'T forget

Drop table

SQL drop `table`
JDO .deletePersistentAll();

it goes super fast.

 final PersistenceManager pm = PMF.get().getPersistenceManager();
 final javax.jdo.Query que = pm.newQuery(ENLF.class);
 que.deletePersistentAll(); 

ESCAPE

select from Dictionary.class where fr='aujourd'hui'; problem
 final javax.jdo.Query que = pm.newQuery("SELECT FROM " + ENLF.class.getName() + " where " + in + "=='" q2.replace("'", "\\'") + "'");

it works.
this is completed query

 SELECT FROM com.lfn.autocomplete.entity.ENLF WHERE e2=='i\'m' 
OTHER WAYS DOESN'T WORK

que.setFilter(in + "==" + q2.replace("'", "\'"));
//javax.jdo.JDOUserException: Portion of expression could not be parsed: 'm que.setFilter(in + "==" + q2.replace("'", "''"));
//Portion of expression could not be parsed: ''m que.setFilter(in + "==" + q2.replace("'", "'"));
//javax.jdo.JDOUserException: Portion of expression could not be parsed: ;m
pm.newQuery("Select " ENLF.class " " in "==" q2.replace("'", "''"));
pm.newQuery("SELECT " ENLF.class.getSimpleName() " `" in "`=='" q2.replace("'", "''") "'");
SELECT class com.lfn.autocomplete.entity.ENLF `e2`=='i''m'
//java.lang.NullPointerException at org.datanucleus.query.compiler.JavaQueryCompiler.getType(JavaQueryCompiler.java:561)
SELECT FROM com.lfn.autocomplete.entity.ENLF WHERE e2=='i''m'
//javax.jdo.JDOUserException: Portion of expression could not be parsed: 'm'
pm.newQuery("SELECT FROM " ENLF.class.getName() " where " in "=='" q2.replace("'", "''") "'");
//javax.jdo.JDOUserException: Portion of expression could not be parsed: 'm'

Buffering

uploading DB file to google directory is not possible.
to move database, use post method @ client and create doPost() for recieve and processing at server.
it easily exceed CPU process limitation. buffering data and keep post number small is slightly helpful, can be relatively very faster.

for example, I had to upload sqlite3 records of 23000. so, I buffered every 500 records as a unit and pushed.

Client SQLite2GDS.java
Host InsertDB.java
SQLite2GDS
List + 500 records
    private void up()
    {
        try
        {
            Class.forName("org.sqlite.JDBC");
        }
        catch (final ClassNotFoundException e1)
        {
            e1.printStackTrace();
        }
        Connection con = null;
        try
        {
            final SQLiteConfig config = new SQLiteConfig();
            con = DriverManager.getConnection("jdbc:sqlite:" + "lfn.db");
            final Statement stat = con.createStatement();
            final ResultSet res = stat.executeQuery("select lfn,english from enlf;");

            final List lt = new LinkedList<String[]>(); // List as Buffer
            while (res.next())
            {
                lt.add(new String[] { res.getString("english"), res.getString("lfn") });
                if (lt.size() == 500)
                {
                    post(lt); // Post when get exceeding 500 records
                    lt.clear();
                }
            }
            post(lt);
        }
        catch (final SQLException e)
        {
            System.err.println(e.getMessage());
        }
        finally
        {
            try
            {
                if (con != null)
                {
                    con.close();
                }
            }
            catch (final SQLException e)
            {
                System.err.println(e);
            }
        }

    }
create Post url query, long for 500 records.
    private void post(final List<String[]> lt)
    {
        try
        {
            final StringBuffer sb = new StringBuffer();
            for (int i = 0; i < lt.size(); i++)
            {
                sb.append("&en" + Integer.toString(i) + "=" + URLEncoder.encode(lt.get(i)[0], "UTF-8"));
                sb.append("&fn" + Integer.toString(i) + "=" + URLEncoder.encode(lt.get(i)[1], "UTF-8"));
            }
            sb.deleteCharAt(0); //Don't forget THIS :)
            

            
            final URL url = new URL("http://lfnsea.appspot.com/insertdb");
            final URLConnection conn = url.openConnection();
            conn.setDoOutput(true);
            final OutputStreamWriter wr = new OutputStreamWriter(conn.getOutputStream());
            wr.write(sb.toString());
            wr.flush();


            final BufferedReader rd = new BufferedReader(new InputStreamReader(conn.getInputStream()));
            String line;
            while ((line = rd.readLine()) != null)
            {
                System.out.println(line);
            }
            wr.close();
            rd.close();
        }
        catch (final Exception e)
        {
        }
    }
Server
InsertDB
List as Buffer, and because it is Collection, is able to be given GDataStore PersistenceManager directly.
    @Override
    public void doPost(final HttpServletRequest req, final HttpServletResponse res)
    {
        final List lt = new LinkedList<ENLF>();
        for (int i = 0; i < 500; i++)
        {
            final String en = req.getParameter("en" + Integer.toString(i));
            final String fn = req.getParameter("fn" + Integer.toString(i));
            if ((en != null) && (fn != null))
            {
                lt.add(new ENLF(en, fn));
            }
        }
        PMF.get().getPersistenceManager().makePersistentAll(lt);
    }

I guess post a data something csv or somehow, and process it via stream open would be better?

See also Data Restore @ GAE - JSON
    last modified: 11 July 2011 [ View wiki source Close ]
     
SEARCH DIFFERENCE ATTACHMENT RECENT POST