Laravel+MySQL网站对接Android应用：Ionic/Java双方案及离线SQLite同步咨询

Got it, let’s walk through how to sync your Laravel/MySQL backend with an Android app using SQLite—including handling offline data persistence and sync when connectivity returns. I’ll cover both Ionic (cross-platform) and native Java approaches.

Core Sync Architecture (Shared for Both Approaches) #

First, let’s lay out the foundational pieces that work regardless of your Android implementation:

1. Laravel Backend Setup #

• Build RESTful APIs using Laravel’s resource controllers to handle CRUD operations for your data models.
• Add a sync endpoint (e.g., POST /api/sync) that accepts batch changes from the app. Include logic to:
  • Validate incoming data (use Laravel’s request validation).
  • Handle conflicts (use optimistic locking with a version field on your MySQL tables—compare the app’s submitted version with the backend’s current version; if they mismatch, return a conflict error).
  • Return a confirmation response with updated data or conflict details.
• For incremental updates, add an endpoint like GET /api/data/updated-since that accepts a timestamp or sync token, returning only data changed after that point.

2. Offline Sync Strategy #

• Local Storage: Use SQLite (via Capacitor for Ionic, Room for native Java) to mirror your backend data and track pending changes.
• Sync Queue: Create a dedicated table (e.g., sync_queue) in SQLite to store offline operations. It should include fields like:
  • id: Primary key
  • operation: Type of change (CREATE, UPDATE, DELETE)
  • table_name: Which data table the change affects
  • data: JSON string of the modified record
  • created_at: Timestamp of the offline operation
  • synced: Boolean flag to track if the change was successfully sent to the backend
• Conflict Resolution: Decide on a strategy—either auto-merge (prioritize newer data) or prompt the user to resolve conflicts manually when syncing.

Ionic (Cross-Platform) Implementation #

Ionic uses Capacitor to access native device features like SQLite and network status, paired with Angular/React for the UI layer.

Step 1: Install Dependencies #

# Capacitor SQLite for local storage
npm install @capacitor/sqlite
npx cap sync

# Network status plugin
npm install @capacitor/network
npx cap sync

# HTTP client (for Angular; use fetch/axios for React)
npm install @angular/common/http

Step 2: Configure SQLite Database #

Initialize your database and create tables (including the sync queue) on app launch:

import { CapacitorSQLite } from '@capacitor/sqlite';

async function initDatabase() {
  const db = await CapacitorSQLite.createConnection('app_db', false, 'no-encryption', 1);
  await db.open();

  // Create business table (example: products)
  await db.execute(`
    CREATE TABLE IF NOT EXISTS products (
      id INTEGER PRIMARY KEY,
      name TEXT,
      price REAL,
      version INTEGER DEFAULT 1,
      updated_at TIMESTAMP
    )
  `);

  // Create sync queue table
  await db.execute(`
    CREATE TABLE IF NOT EXISTS sync_queue (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      operation TEXT NOT NULL,
      table_name TEXT NOT NULL,
      data TEXT NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      synced INTEGER DEFAULT 0
    )
  `);
}

Step 3: Handle Network Status & Sync #

Listen for network changes and trigger sync when connectivity returns:

import { Network } from '@capacitor/network';

// Monitor network status
Network.addListener('networkStatusChange', async (status) => {
  if (status.connected) {
    await syncPendingChanges();
    await fetchLatestData();
  }
});

// Sync pending offline changes
async function syncPendingChanges() {
  const db = await CapacitorSQLite.getConnection('app_db');
  const pendingChanges = await db.query('SELECT * FROM sync_queue WHERE synced = 0 ORDER BY created_at ASC');

  for (const change of pendingChanges.values) {
    try {
      const data = JSON.parse(change.data);
      let response;

      switch (change.operation) {
        case 'CREATE':
          response = await fetch('https://your-laravel-api.com/api/products', {
            method: 'POST',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify(data)
          });
          break;
        case 'UPDATE':
          response = await fetch(`https://your-laravel-api.com/api/products/${data.id}`, {
            method: 'PUT',
            headers: { 'Content-Type': 'application/json' },
            body: JSON.stringify(data)
          });
          break;
        case 'DELETE':
          response = await fetch(`https://your-laravel-api.com/api/products/${data.id}`, { method: 'DELETE' });
          break;
      }

      if (response.ok) {
        // Mark change as synced
        await db.execute(`UPDATE sync_queue SET synced = 1 WHERE id = ?`, [change.id]);
      } else {
        // Handle error (e.g., retry later)
        console.error('Sync failed for change:', change.id);
      }
    } catch (err) {
      console.error('Error syncing change:', err);
    }
  }
}

// Fetch latest data from backend
async function fetchLatestData() {
  const lastSyncTime = await getLastSyncTimestamp(); // Store this in local storage
  const response = await fetch(`https://your-laravel-api.com/api/products/updated-since?timestamp=${lastSyncTime}`);
  const updatedData = await response.json();

  const db = await CapacitorSQLite.getConnection('app_db');
  for (const item of updatedData) {
    await db.execute(`
      INSERT OR REPLACE INTO products (id, name, price, version, updated_at)
      VALUES (?, ?, ?, ?, ?)
    `, [item.id, item.name, item.price, item.version, item.updated_at]);
  }

  await saveLastSyncTimestamp(new Date().toISOString()); // Update local storage
}

Native Java Implementation #

For native Android, use Room (SQLite ORM) for local storage, Retrofit for API calls, and WorkManager for background sync.

Step 1: Add Dependencies to build.gradle #

// Room
implementation "androidx.room:room-runtime:2.5.0"
annotationProcessor "androidx.room:room-compiler:2.5.0"

// Retrofit
implementation 'com.squareup.retrofit2:retrofit:2.9.0'
implementation 'com.squareup.retrofit2:converter-gson:2.9.0'

// WorkManager
implementation "androidx.work:work-runtime:2.8.1"

Step 2: Define Room Entities & DAO #

Product Entity (Business Table)

@Entity(tableName = "products")
public class Product {
    @PrimaryKey
    public int id;
    public String name;
    public double price;
    public int version;
    public String updated_at;
}

Sync Queue Entity

@Entity(tableName = "sync_queue")
public class SyncQueueItem {
    @PrimaryKey(autoGenerate = true)
    public int id;
    public String operation;
    public String table_name;
    public String data;
    public String created_at;
    public boolean synced;
}

DAO Interface

@Dao
public interface AppDao {
    // Product CRUD
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    void insertProduct(Product product);

    @Update
    void updateProduct(Product product);

    @Delete
    void deleteProduct(Product product);

    // Sync Queue
    @Insert
    void addSyncItem(SyncQueueItem item);

    @Query("SELECT * FROM sync_queue WHERE synced = 0 ORDER BY created_at ASC")
    List<SyncQueueItem> getPendingSyncItems();

    @Query("UPDATE sync_queue SET synced = 1 WHERE id = :itemId")
    void markItemAsSynced(int itemId);
}

Room Database Class

@Database(entities = {Product.class, SyncQueueItem.class}, version = 1)
public abstract class AppDatabase extends RoomDatabase {
    public abstract AppDao appDao();

    private static volatile AppDatabase INSTANCE;

    public static AppDatabase getInstance(Context context) {
        if (INSTANCE == null) {
            synchronized (AppDatabase.class) {
                INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                                AppDatabase.class, "app_db")
                        .build();
            }
        }
        return INSTANCE;
    }
}

Step 3: Retrofit API Interface #

public interface ApiService {
    @GET("api/products/updated-since")
    Call<List<Product>> getUpdatedProducts(@Query("timestamp") String timestamp);

    @POST("api/products")
    Call<Product> createProduct(@Body Product product);

    @PUT("api/products/{id}")
    Call<Product> updateProduct(@Path("id") int id, @Body Product product);

    @DELETE("api/products/{id}")
    Call<Void> deleteProduct(@Path("id") int id);
}

Step 4: Background Sync with WorkManager #

Create a Worker class to handle syncing when network is available:

public class SyncWorker extends Worker {
    private AppDatabase db;
    private ApiService apiService;

    public SyncWorker(@NonNull Context context, @NonNull WorkerParameters params) {
        super(context, params);
        db = AppDatabase.getInstance(context);
        apiService = new Retrofit.Builder()
                .baseUrl("https://your-laravel-api.com/")
                .addConverterFactory(GsonConverterFactory.create())
                .build()
                .create(ApiService.class);
    }

    @NonNull
    @Override
    public Result doWork() {
        List<SyncQueueItem> pendingItems = db.appDao().getPendingSyncItems();

        for (SyncQueueItem item : pendingItems) {
            try {
                Gson gson = new Gson();
                Product product = gson.fromJson(item.data, Product.class);
                Response<?> response;

                switch (item.operation) {
                    case "CREATE":
                        response = apiService.createProduct(product).execute();
                        break;
                    case "UPDATE":
                        response = apiService.updateProduct(product.id, product).execute();
                        break;
                    case "DELETE":
                        response = apiService.deleteProduct(product.id).execute();
                        break;
                    default:
                        response = null;
                }

                if (response != null && response.isSuccessful()) {
                    db.appDao().markItemAsSynced(item.id);
                } else {
                    // Retry later
                    return Result.retry();
                }
            } catch (IOException e) {
                e.printStackTrace();
                return Result.retry();
            }
        }

        // Fetch latest data after syncing pending changes
        try {
            String lastSyncTime = getLastSyncTimestamp(getApplicationContext());
            Response<List<Product>> response = apiService.getUpdatedProducts(lastSyncTime).execute();
            if (response.isSuccessful()) {
                List<Product> updatedProducts = response.body();
                if (updatedProducts != null) {
                    for (Product p : updatedProducts) {
                        db.appDao().insertProduct(p);
                    }
                }
                saveLastSyncTimestamp(getApplicationContext(), new Date().toString());
            }
        } catch (IOException e) {
            e.printStackTrace();
            return Result.retry();
        }

        return Result.success();
    }
}

Step 5: Trigger Sync on Network Change #

Use a BroadcastReceiver to listen for network connectivity changes and enqueue the SyncWorker:

public class NetworkChangeReceiver extends BroadcastReceiver {
    @Override
    public void onReceive(Context context, Intent intent) {
        ConnectivityManager cm = (ConnectivityManager) context.getSystemService(Context.CONNECTIVITY_SERVICE);
        NetworkInfo activeNetwork = cm.getActiveNetworkInfo();
        boolean isConnected = activeNetwork != null && activeNetwork.isConnectedOrConnecting();

        if (isConnected) {
            // Enqueue sync work
            Constraints constraints = new Constraints.Builder()
                    .setRequiredNetworkType(NetworkType.CONNECTED)
                    .build();

            OneTimeWorkRequest syncRequest = new OneTimeWorkRequest.Builder(SyncWorker.class)
                    .setConstraints(constraints)
                    .build();

            WorkManager.getInstance(context).enqueue(syncRequest);
        }
    }
}

Don’t forget to register the receiver in your AndroidManifest.xml:

<receiver android:name=".NetworkChangeReceiver">
    <intent-filter>
        <action android:name="android.net.conn.CONNECTIVITY_CHANGE" />
    </intent-filter>
</receiver>

内容的提问来源于stack exchange，提问作者IhAb MouDhich