sqlQuery = "SELECT * FROM " + TABLE_CALLS + " where " + CALLER_NAME + " like ? COLLATE NOCASE or " + CALLER_NBR + " like ? or " + CALLER_EXT + " like ?" + " or " + IS_OUTGOING + " like ?  COLLATE NOCASE or " + TYPE + " like ? COLLATE NOCASE";

Cursor cursor = database.rawQuery(sqlQuery,new String[]{"%" + criterion + "%","%" + criterion + "%","%" + criterion + "%"});

解决方法

if (name.length() != 0) {

        name = "%" + name + "%";
    }
    if (email.length() != 0) {
        email = "%" + email + "%";
    }
    if (Phone.length() != 0) {
        Phone = "%" + Phone + "%";
    }
    String selectQuery = " select * from tbl_Customer where Customer_Name like  '"
            + name
            + "' or Customer_Email like '"
            + email
            + "' or Customer_Phone like '"
            + Phone
            + "' ORDER BY Customer_Id DESC";

    Cursor cursor = mDb.rawQuery(selectQuery,null);`

Summary: in this tutorial, you will learn how to rename tables using MySQL RENAME TABLE statement and ALTER TABLE statement.

Introduction to MySQL RENAME TABLE statement

Because business requirements change, we need to rename the current table to a new one to better reflect the new situation. MySQL provides us with a very useful statement that changes the name of one or more tables.

To change one or more tables, we use the RENAME TABLE statement as follows:

The old table ( old_table_name) must exist, and the new table ( new_table_name) must not. If the new table new_table_name does exist, the statement will fail.

In addition to the tables, we can use the RENAME TABLE statement to rename views.

Before we execute the RENAME TABLE statement, we must ensure that there is no active transactions or locked tables.

Note that you cannot use the RENAME TABLE statement to rename a temporary table, but you can use the ALTER TABLE statement to rename a temporary table.

In terms of security, any existing privileges that we granted to the old table must be manually migrated to the new table.

Before renaming a table, you should evaluate the impact thoroughly. For example, you should investigate which applications are using the table. If the name of the table changes, so the application code that refers to the table name needs to be changed as well. In addition, you must manually adjust other database objects such as views, stored procedures, triggers, foreign key constraints, etc., that reference to the table. We will discuss this in more detail in the following examples.

MySQL RENAME TABLE examples

First, we create a new database named hr that consists of two tables: employees and departments for the demonstration.

Second, we insert sample data into both employees and departments tables:

Third, we review our data in the departments and employees tables:

Renaming a table referenced by a view

If the table that you are going to rename is referenced by a view, the view will become invalid if you rename the table, and you have to adjust the view manually.

For example, we create a view named v_employee_info based on the employees and departments tables as follows:

The views use the inner join clause to join departments and employees tables.

The following SELECT statement returns all data from the v_employee_info view.

Now we rename the employees to people table and query data from the v_employee_info view again.

MySQL returns the following error message:

We can use the CHECK TABLE statement to check the status of the v_employee_info view as follows:

We need to manually change the v_employee_info view so that it refers to the people table instead of the employees table.

Renaming a table that referenced by a stored procedure

In case the table that you are going to rename is referenced by a stored procedure, you have to manually adjust it like you did with the view.

First, rename the people table back to the employees table.

Then, create a new stored procedure named get_employee that refers to the employees table.

Next, we execute the get_employee table to get the data of the employee with id 1 as follows:

After that, we rename the employees to the people table again.

Finally, we call the get_employee stored procedure to get the information of employee with id 2:

MySQL returns the following error message:

To fix this, we must manually change the employees table in the stored procedure to people table.

Renaming a table that has foreign keys referenced to

The departments table links to the employees table using the department_id column. The department_id column in the employees table is the foreign key that references to the departments table.

If we rename the departments table, all the foreign keys that point to the departments table will not be automatically updated. In such cases, we must drop and recreate the foreign keys manually.

We delete a department with id 1, because of the foreign key constraint, all rows in the people table should be also deleted. However, we renamed the departments table to the depts table without updating the foreign key manually, MySQL returns an error as illustrated below:

Renaming multiple tables

We can also use the RENAME TABLE statement to rename multiple tables at a time. See the following statement:

The following statement renames the people and depts tables to employees and departments tables:

Note the RENAME TABLE statement is not atomic. It means that if any errors occurred, MySQL does a rollback all renamed tables to their old names.

Renaming tables using ALTER TABLE statement

We can rename a table using the ALTER TABLE statement as follows:

The ALTER TABLE statement can rename a temporary table while the RENAME TABLE statement cannot.

Renaming temporary table example

First, we create a temporary table that contains all unique last names which come from the last_namecolumn of the employees table:

Second, we use the RENAME TABLE to rename the lastnames table:

MySQL returns the following error message:

Third, we use the ALTER TABLE statement to rename the lastnames table.

Fourth, we query data from the unique_lastnames temporary table:

In this tutorial, we have shown you how to rename tables using MySQL RENAME TABLE and ALTER TABLE statements.

解决方法

public class User {
    private int univ_id;

    private String univ_name,univ_abbr;

    public User(int univ_id,String univ_name,String univ_abbr) {

        this.univ_id = univ_id;
        this.univ_name = univ_name;
        this.univ_abbr = univ_abbr;
    }

    public User(String univ_name,String univ_abbr) {

        this.univ_name = univ_name;
        this.univ_abbr = univ_abbr;
    }

    public User() {
        // Todo Auto-generated constructor stub
    }

    public int getUniv_id() {
        return univ_id;
    }

    public void setUniv_id(int univ_id) {
        this.univ_id = univ_id;
    }

    public String getUniv_name() {
        return univ_name;
    }

    public void setUniv_name(String univ_name) {
        this.univ_name = univ_name;
    }

    public String getUniv_abbr() {
        return univ_abbr;
    }

    public void setUniv_abbr(String univ_abbr) {
        this.univ_abbr = univ_abbr;
    }
}

public class DBStorageFirst extends sqliteOpenHelper {

    public static final String DATABASE_NAME = "STUPIDSID_OFFLINE_DB";

    public static final String TABLE_NAME_UNIVERSITIES = "universities";


    public static final String COLUMN_UNIV_ID = "univ_id";
    public static final String COLUMN_UNIV_NAME = "univ_name";
    public static final String COLUMN_UNIV_ABBR = "univ_abbr";

    public DBStorageFirst(Context context) {
        super(context,DATABASE_NAME,null,1);
    }

    @Override
    public void onCreate(sqliteDatabase db) {
        // Todo Auto-generated method stub
        db.execsql("create table " + TABLE_NAME_UNIVERSITIES
                + "(" + COLUMN_UNIV_ID + " integer RIMARY KEY," + COLUMN_UNIV_NAME
                + " text," + COLUMN_UNIV_ABBR + " text )");
        Log.e("Success","Universities Table Created");

    }

    @Override
    public void onUpgrade(sqliteDatabase db,int oldVersion,int newVersion) {
        // Todo Auto-generated method stub
        db.execsql("DROP TABLE IF EXISTS " + TABLE_NAME_UNIVERSITIES);

        onCreate(db);
    }

    public void addUniversities(int univ_id,String univ_abbr) {
         sqliteDatabase db = this.getWritableDatabase();
        String sql1 = "insert into " + DBStorageFirst.TABLE_NAME_UNIVERSITIES
                + " (" + DBStorageFirst.COLUMN_UNIV_ID + ","
                + DBStorageFirst.COLUMN_UNIV_NAME + ","
                + DBStorageFirst.COLUMN_UNIV_ABBR + ")" + " values(" + univ_id
                + ",''" + univ_name + " '',''" + univ_abbr + "'')";
        db.execsql(sql1);
        // database.execsql(sql2);
        Log.e("Success","Data inserted Successfully into Universities Table");
    }

    public List<User> getAllContacts() {
        List<User> contactList = new ArrayList<User>();

        String selectQuery = "SELECT * FROM "+TABLE_NAME_UNIVERSITIES;
        sqliteDatabase db1 = this.getWritableDatabase();
        Cursor cursor = db1.rawQuery(selectQuery,null);
        if (cursor.movetoFirst()) {
            do {
                User contact = new User();
                contact.setUniv_id(Integer.parseInt(cursor.getString(0)));
                contact.setUniv_name(cursor.getString(1));
                contact.setUniv_abbr(cursor.getString(2));
                contactList.add(contact);
            } while (cursor.movetoNext());
        }
        return contactList;
    }
}

DBStorageFirst db = new DBStorageFirst(this);

// Inserting Contacts
Log.d("Insert: ","Inserting ..");
db.addUniversities(25,"ABC","UA");
db.addUniversities(26,"DEF","UB");
List<User> list = new ArrayList<User>();
list = db.getAllContacts();

for (User cn : list) {
    String log = "Id: " + cn.getUniv_id() + ",Name: "
            + cn.getUniv_name() + ",Abbr: " + cn.getUniv_abbr();
    // Writing Contacts to log
    Log.d("Name: ",log);
}

解决方法