Android Java中SQLite与SQL Server类连接空指针异常问题

已实现本地SQLite的增、读、改操作，但在SQL Server通信测试类中读取SQLite存储的通信配置数据时触发空指针异常，硬编码配置信息则可正常运行。

报错信息 #

java.lang.NullPointerException: Attempt to invoke virtual method 'java.io.File android.content.Context.getDatabasePath(java.lang.String)' on a null object reference
2024-12-05 21:36:00.969 13284-13337 System.err              com.example.midasitemcatalog         W      at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:352)
2024-12-05 21:36:00.969 13284-13337 System.err              com.example.midasitemcatalog         W      at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:322)
2024-12-05 21:36:00.969 13284-13337 System.err              com.example.midasitemcatalog         W      at Queries.SQLite.readConnetion(SQLite.java:67)
2024-12-05 21:36:00.969 13284-13337 System.err              com.example.midasitemcatalog         W      at MidasConnetion.MSSQLConnection.connectionclass(MSSQLConnection.java:28)
2024-12-05 21:36:00.970 13284-13337 System.err              com.example.midasitemcatalog         W      at Queries.SQLQueries.getItemsFragments(SQLQueries.java:287)
2024-12-05 21:36:00.970 13284-13337 System.err              com.example.midasitemcatalog         W      at fragments.ItemListFragment$RetrieveDataAsyncTask.doInBackground(ItemListFragment.java:353)
2024-12-05 21:36:00.970 13284-13337 System.err              com.example.midasitemcatalog         W      at fragments.ItemListFragment$RetrieveDataAsyncTask.doInBackground(ItemListFragment.java:326)
2024-12-05 21:36:00.970 13284-13337 System.err              com.example.midasitemcatalog         W      at android.os.AsyncTask$2.call(AsyncTask.java:333)
2024-12-05 21:36:00.970 13284-13337 System.err              com.example.midasitemcatalog         W      at java.util.concurrent.FutureTask.run(FutureTask.java:266)
2024-12-05 21:36:00.971 13284-13337 System.err              com.example.midasitemcatalog         W      at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:245)
2024-12-05 21:36:00.971 13284-13337 System.err              com.example.midasitemcatalog         W      at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
2024-12-05 21:36:00.971 13284-13337 System.err              com.example.midasitemcatalog         W      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
2024-12-05 21:36:00.971 13284-13337 System.err              com.example.midasitemcatalog         W      at java.lang.Thread.run(Thread.java:764)

相关代码 #

SQLite类 #

package Queries;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;

public class SQLite extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "midasandroid.db";
    private static final int DB_Version = 1;
    private static final String local_Data = "Configure";
    private static final String id = "id";
    private static final String dbDomainName = "dbDomainName";
    private static final String dbname = "dbname";
    private static final String dbPort  = "dbPort";
    private static final String dbUser = "dbUser";
    private static final String dbPassword = "dbPassword";
    private static final String currentMetPrice = "currentMetPrice";
    private static final String apikey = "apikey";
    private Context context;

    public SQLite(Context context) {
        super(context, DATABASE_NAME, null, DB_Version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE " + local_Data + "("
                + id + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + dbDomainName + " TEXT,"
                + dbPort + " TEXT,"
                + dbname + " TEXT,"
                + dbUser + " TEXT,"
                + dbPassword + " TEXT,"
                + apikey + " TEXT,"
                + currentMetPrice + " REAL)";
        db.execSQL(sql);

    }

    public void addValuesCon(String dbDomainName, String dbname, String dbPort, String dbUser, String dbPassword) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(this.id, 1);
        values.put(this.dbDomainName, dbDomainName);
        values.put(this.dbPort, dbPort);
        values.put(this.dbname, dbname);
        values.put(this.dbUser, dbUser);
        values.put(this.dbPassword, dbPassword);
        values.put(this.apikey, "");
        values.put(this.currentMetPrice, 0);
        db.insert(local_Data, null, values);
        db.close();

    }

    public ArrayList<LocalData> readConnetion() {
        ArrayList<LocalData> localData = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + local_Data, null);
        if (cursor.moveToFirst()) {
            do {
                localData.add(new LocalData(cursor.getString(0),
                        cursor.getString(1),
                        cursor.getString(2),
                        cursor.getString(3),
                        cursor.getString(4),
                        cursor.getString(5),
                        cursor.getString(6),
                        cursor.getFloat(7)));
            } while (cursor.moveToNext());
        }
        cursor.close();
        return  localData;
    }

    public ArrayList<LocalData> readMetPrice() {
        ArrayList<LocalData> localData = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + local_Data, null);
        if (cursor!=null) {
            while (cursor.moveToNext()) {
                localData.add(new LocalData(cursor.getString(1),
                        cursor.getString(2),
                        cursor.getString(3),
                        cursor.getString(4),
                        cursor.getString(5),
                        cursor.getString(6),
                        cursor.getString(7),
                        cursor.getFloat(8)));
            }
        }
        cursor.close();
        return  localData;
    }

    public void updateDataConnection(int id, String dbDomainName, String dbname, String dbPort, String dbUser, String dbPassword){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(this.dbDomainName, dbDomainName);
        values.put(this.dbname, dbname);
        values.put(this.dbPort, dbPort);
        values.put(this.dbUser, dbUser);
        values.put(this.dbPassword, dbPassword);

        db.update(local_Data, values, "id = ?", new String[]{String.valueOf(id)});
        db.close();
    }

    public void updateDataMetPrice(int id,String apikey, float currentMetPrice){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        values.put(this.apikey, apikey);
        values.put(this.currentMetPrice, currentMetPrice);

        db.update(local_Data, values, "id = ?", new String[]{String.valueOf(id)});
        db.close();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + local_Data);
        onCreate(db);
    }
}

SQL Server连接类 #

package MidasConnetion;

import android.content.Context;
import android.os.StrictMode;
import android.util.Log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;

import Queries.LocalData;
import Queries.SQLite;
import Queries.SQLtoSQLite;

public class MSSQLConnection {
    private Context context;
    public MSSQLConnection(Context context){
        this.context = context;
    }

    public Connection connectionclass() {
        Connection connection = null;
        SQLite sqLite = new SQLite(context);
        ArrayList<LocalData> localData1 = sqLite.readConnetion();
        if (!localData1.isEmpty()){
            LocalData localData = localData1.get(0);
            String classDriver="net.sourceforge.jtds.jdbc.Driver";
            String serverID="jdbc:jtds:sqlserver://"+localData.getDbDomainName()+":"+localData.getDbPort()+"/"+localData.getDbname();
            StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder().permitAll().build();
            StrictMode.setThreadPolicy(policy);
            try {
                Class.forName(classDriver);
                DriverManager.getLoginTimeout();
                connection = DriverManager.getConnection(serverID, localData.getDbUser(), localData.getDbPassword());
            } catch (ClassNotFoundException e) {
                Log.d("Connected ", "Connected "+ e.getMessage());
            }
            catch (Exception e) {
                Log.d("Error ","Error "+ e.getMessage());
            }
        }
        return connection;
    }
}

查询类 #

public class SQLQueries {
    private static Context context;
    private static MSSQLConnection connectionSQL;

    public SQLQueries(Context context){
        this.context = context;
        connectionSQL = new MSSQLConnection(context);
    }

    public static ArrayList<ItemList> getItemsFragments(String searchText ){
        Connection connect;
        int i=0;
        String query = context.getResources().getString(R.string.fragment_select_items) + searchText;
        Log.d("Items query",query);

        ArrayList<ItemList> fetchItems= new ArrayList<>();

        try {
            Connection connec = connectionSQL.connectionclass();
            if (connec != null) {
                Statement st = connec.createStatement();
                ResultSet rs = st.executeQuery(query);
                while (rs.next()) {
                    i++;
                    Integer barcode = rs.getInt(1);
                    String itmSupCode = rs.getString(2);
                    String itmSection = rs. getString(3);
                    String itmType = rs.getString(4);
                    String ItmStonesName = rs.getString(5);
                    String alloycode = rs.getString(6);
                    Float ItmLPrice = rs.getFloat(7);
                    byte[] photo = rs.getBytes(8);
                    fetchItems.add(new ItemList(i,barcode,itmSupCode,itmSection,itmType,ItmStonesName,alloycode,ItmLPrice,photo));
                }
                rs.close();
                st.close();
                connec.close();
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return fetchItems;
    }
}

问题原因 #

1. 静态变量未正确初始化：SQLQueries类中的context和connectionSQL是静态变量，直接调用静态方法getItemsFragments时，若未先实例化SQLQueries对象，这两个变量会保持null值。后续在connectionclass方法中创建SQLite实例时传入null，导致SQLiteOpenHelper无法获取数据库路径，触发空指针。
2. SQLite索引越界：readMetPrice方法中使用cursor.getFloat(8)，但表Configure仅8列（索引0-7），会触发索引越界异常。

修复方案 #

1. 修正SQLQueries类的静态变量问题 #

去掉context和connectionSQL的static修饰符，将getItemsFragments改为非静态方法：

public class SQLQueries {
    private Context context;
    private MSSQLConnection connectionSQL;

    public SQLQueries(Context context){
        this.context = context;
        connectionSQL = new MSSQLConnection(context);
    }

    public ArrayList<ItemList> getItemsFragments(String searchText ){
        Connection connect;
        int i=0;
        String query = context.getResources().getString(R.string.fragment_select_items) + searchText;
        Log.d("Items query",query);

        ArrayList<ItemList> fetchItems= new ArrayList<>();

        try {
            Connection connec = connectionSQL.connectionclass();
            if (connec != null) {
                Statement st = connec.createStatement();
                ResultSet rs = st.executeQuery(query);
                while (rs.next()) {
                    i++;
                    Integer barcode = rs.getInt(1);
                    String itmSupCode = rs.getString(2);
                    String itmSection = rs. getString(3);
                    String itmType = rs.getString(4);
                    String ItmStonesName = rs.getString(5);
                    String alloycode = rs.getString(6);
                    Float ItmLPrice = rs.getFloat(7);
                    byte[] photo = rs.getBytes(8);
                    fetchItems.add(new ItemList(i,barcode,itmSupCode,itmSection,itmType,ItmStonesName,alloycode,ItmLPrice,photo));
                }
                rs.close();
                st.close();
                connec.close();
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return fetchItems;
    }
}

调用处先实例化SQLQueries：

// 在ItemListFragment的RetrieveDataAsyncTask中
SQLQueries queries = new SQLQueries(getContext());
ArrayList<ItemList> items = queries.getItemsFragments(searchText);

2. 修正SQLite类的索引越界问题 #

修改readMetPrice方法中的cursor索引，匹配表的列数：

public ArrayList<LocalData> readMetPrice() {
    ArrayList<LocalData> localData = new ArrayList<>();
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT * FROM " + local_Data, null);
    if (cursor != null && cursor.moveToFirst()) {
        do {
            localData.add(new LocalData(cursor.getString(0),
                    cursor.getString(1),
                    cursor.getString(2),
                    cursor.getString(3),
                    cursor.getString(4),
                    cursor.getString(5),
                    cursor.getString(6),
                    cursor.getFloat(7)));
        } while (cursor.moveToNext());
    }
    if (cursor != null) cursor.close();
    return localData;
}

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