5. Android Guide - reubenjohn/aceqlandroid GitHub Wiki
This page will go a little more into the details of how to use the SDK.
Disclaimer
Code and functionality described by this Wiki is subject to change. Code presented here may not be up to date.
Let's start by demonstrating how a list of Questions will be queried from our database.
So, here's the class that acts as a wrapper for the information that I am expecting about each question from my database:
public static class PointItem {
//Here are all the fields that I want to retrieve for each question
public final String PID;
public final String poster;
public final String title;
public final short upVotesPercentage;
public final String tag1, tag2, tag3, tag4;
public String views;
//Yes, this is a weird constructor, but the important parameter is the `ResultSet`
//The 'ResultSet' will represent a row from the result of the query I am about to make.
//From the 'rs' object, I will be able to access all of the fields of the row by name as shown.
//Exactly what the name of the particular column is will depend on my SQL expression.
//Don't worry this will all make more sense after we actually make the query and fetch the list.
public PointItem(ResultSet rs, Context context) throws SQLException {
PID = String.valueOf(rs.getInt("PID"));
poster = rs.getString("poster");
title = rs.getString("title");
views = rs.getString("views") + " " + context.getString(R.string.views);
upVotesPercentage = rs.getShort("upVotesPercentage");
tag1 = rs.getString("tag1");
tag2 = rs.getString("tag2");
tag3 = rs.getString("tag3");
tag4 = rs.getString("tag4");
}
}
And here's how I fetch a list of such a class from my database:
//First create our SQL query as a string (Yours doesn't have to be so complex :p)
String sql = "SELECT PID, " +
"username as poster, " +
"title, " +
"(select count(*) from Question where PID = Question.PID) as views, " +
"tag1, tag2, tag3, tag4 " +
"FROM Question";
//Now we specify what to do when the query is complete
OnQueryComplete<Question> onQueryCompleteListener= new OnQueryComplete<Question>() {
@Override
public void onQueryComplete(List<QuestionItem> list, SQLException e1) {
if (e1 != null) {
//If there's an error, e1 will not be null, and will contain the specifics of the error
Toast.makeText(getActivity(),"Something's not right",Toast.LENGTH_SHORT).show();
} else {
//We got a list of lists, but we just need the first list, since we only
questionItems = list;
Toast.makeText(getActivity(),"Retrieved: "+questionItems.size()+" items!",Toast.LENGTH_SHORT).show();
}
}
};
//We also have to let the SDK know how to convert the rows that SQL fetches to the class that represents your row.
//In my case, I have a class called QuestionItem that stores the data about each row
ItemBuilder<QuestionItem> itemBuilder = new ItemBuilder<QuestionItem>() {
@Override
public QuestionItem buildItem(ResultSet rs) {
try {
return new QuestionItem(rs, getContext());
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
};
//Finally, I put it all together with this statement
//It translates to:
//"Hey AceQL, Perform the select statement that is specified in the 'sql' string,
//and when you're done making the List from all the rows that the query returned,
//then perform the actions I specified in the `onQueryCompleteListener`."
//"Oh! And by the way, if you want to know how to convert those rows to a list of my custom class,
//I've explained that in the 'itemBuilder'"
AceQLDBManager.getSelectedLists(sql, itemBuilder, onQueryCompleteListener);
Although, you can directly execute SQL insertion and deletion commands with AceQLDBManager.executeUpdate(), we've provided a more convenient approach to insert a list into the database.
Basically, if you create a class that implements the SQLEntity interface, then all you have to do is give us the list of such objects, and specify what should happen when the query is complete, and we'll handle everything else!
Here's what such a class may look like:
public class Question implements SQLEntity {
public String title;
public String description;
public String username;
@Override
public String getEntityName() {
return "Question";
}
@Override
public String[] getAttributeNames() {
return new String[]{"username", "title", "description"};
}
@Override
public int onPrepareStatement(PreparedStatement preparedStatement, int i) throws SQLException {
preparedStatement.setString(i++, username);
preparedStatement.setString(i++, title);
preparedStatement.setString(i++, description);
return i;
}
}
And here's how to insert a list of such objects
//Create the list you want to insert
List<Question> list = new ArrayList<>();
list.add(question); //Let's assume you've already instantiated this object that implements SQLEntity and you want to add it to the list
//Specify what you want to do when the list is inserted:
OnUpdateCompleteListener onUpdateCompleteListener = new OnUpdateCompleteListener() {
@Override
public void onUpdateComplete(int result, SQLException e) {
if (e != null) {
Toast.makeText(getActivity(),"Something's gone wrong",Toast.LENGTH_SHORT).show();
e.printStackTrace();
} else {
Toast.makeText(getActivity(),"Yay! Inserted: "+result+" items",Toast.LENGTH_SHORT).show();
}
}
};
//Now simply call the function and pass the parameters
AceQLDBManager.insertSQLEntityList(list, onUpdateCompleteListener);
This option is for those who are already familiar with the standard AceQL library. It allows you to manually prepare the select statement and specify how to retrieve data from the result. You should find a fair amount of documentation about what you can do with PreparedStatement and ResultSet it in the official documentation. You can also follow a similar approach for updation(update and delete).
OnGetPrepareStatement onGetPrepareStatement = new OnGetPrepareStatement() {
@Override
public PreparedStatement onGetPreparedStatement(BackendConnection remoteConnection) {
try {
return remoteConnection.prepareStatement("select username from User");
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
};
OnGetResultSetListener onGetResultSetListener = new OnGetResultSetListener() {
@Override
public void onGetResultSet(ResultSet rs, SQLException e) {
if(e!=null){
//Something went wrong
}else{
try {
while(rs.next()){
Log.d("Yay!", rs.getString("username"));
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
};
AceQLDBManager.executeQuery(onGetPrepareStatement, onGetResultSetListener);