Click here to Skip to main content
15,507,388 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Am new for sqlite then when am trying to execute the the following code. then i need to connect and display some result





Java
package sqlwrapper;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class SqLiteSQLWrapper implements SQLWrapper {

private final String driver = "SQLite.JDBCDriver";
private final String protocol = "jdbc:sqlite:";
private final String dbName = "toolbox.dat";
private String dbPath = null;

private Connection conn = null;

/**
 * Single instance created upon class loading.
 */

private static final SqLiteSQLWrapper sqLiteInstance = new SqLiteSQLWrapper();

private SqLiteSQLWrapper() {
}

/**
 * Returns the singleton sqLite instance
 * @return sqLiteInstance
 */
public static SqLiteSQLWrapper getInstance() {

    try{
        if(sqLiteInstance.conn == null || sqLiteInstance.conn.isClosed())
            sqLiteInstance.conn = sqLiteInstance.getConnection();

    }catch(Exception ex){
        ex.printStackTrace();
        return null;
    }

    return sqLiteInstance;
 }

/**
 * Execute SQL statement for data definition and manipulation
 * @param sql Sql operation
 * @return success of executed operation
 */

public boolean execute(String sql) {
    Statement st = null;
    try {
        st =sqLiteInstance.conn.createStatement();
        boolean successful = st.execute(sql);
        st.close();
        return successful;
    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    } finally {
        try {
            st.close();
        } catch(Exception e) {}
    }
}

/**
 * Execute SQL statement for data query
 * @param sql Sql operation
 * @return queryResult
 */

public QueryResult executeQuery(String sql) {

    QueryResult result = null;

    try {
        Statement st =sqLiteInstance.conn.createStatement();
        result = new QueryResult(st, sql);
    } catch (Exception e) {
        e.printStackTrace();
    }

    return result;
 }


 /**
 * Commit transaction
 */

public void commit() {
    try {
        conn.commit();
    } catch(SQLException e) {
        e.printStackTrace();
    }
}


/**
 * Commit transaction, delete temporary data
 * @return success of the operation
 */

public boolean flush() {
    try {
        conn.commit();
        conn.close();

        String dbPath = sqLiteInstance.dbPath +"/" +sqLiteInstance.dbName;
        File file = new File(dbPath);
        boolean succ = file.delete();

        if(succ){
            File dir = new File(sqLiteInstance.dbPath);
            dir.delete();
        }

    } catch (SQLException e) {
        e.printStackTrace();
        return false;
    }
    return true;
}

private Connection getConnection() throws SQLException {

    loadDriver();

    //if(sqLiteInstance.dbPath == null){

        sqLiteInstance.dbPath= System.getProperty("user.home") + "/.Toolbox" ;

        if (!(new File(sqLiteInstance.dbPath)).exists()){
                boolean success = (new File(sqLiteInstance.dbPath)).mkdir();
                if(!success)
                    return null;
        }

    //}

    conn = DriverManager.getConnection( sqLiteInstance.protocol + "/" + sqLiteInstance.dbPath +"/" +sqLiteInstance.dbName , "", "" );
    conn.setAutoCommit(false);

    return conn;
}

private void loadDriver() throws SQLException {
    try {
        System.loadLibrary("sqlite_jni");
       //Class.forName(sqLiteInstance.driver);
       Class.forName("org.sqlite.JDBC");

    } catch (ClassNotFoundException cnfe) {
        System.err.println("\nUnable to load the JDBC driver ");
        System.err.println("Please check your CLASSPATH.");
        cnfe.printStackTrace(System.err);
    }
}
}


**then It display an error message**





org.sqlite.SQLiteException: [SQLITE_LOCKED]  A table in the database is 
    locked (database table is locked)
	    at org.sqlite.core.DB.newSQLException(DB.java:909)
	    at org.sqlite.core.DB.newSQLException(DB.java:921)
	    at org.sqlite.core.DB.execute(DB.java:822)
	    at org.sqlite.core.CoreStatement.exec(CoreStatement.java:75)
	    at org.sqlite.jdbc3.JDBC3Statement.execute(JDBC3Statement.java:61)
	    at sqlwrapper.SqLiteSQLWrapper.execute(SqLiteSQLWrapper.java:60)



then how can i fix these problem ? the sqlite DB is locked

What I have tried:

am trying to display the result
Posted
Updated 13-Mar-18 0:38am
v2
Comments
Richard MacCutchan 13-Mar-18 5:38am    
Which is line 60 and 61?
amanuelhaft 13-Mar-18 6:02am    
Line 60 is part of this code

56 public boolean execute(String sql) {
57 Statement st = null;
58 try {
59 st =sqLiteInstance.conn.createStatement();
60 boolean successful = st.execute(sql);
61 st.close();
62 return successful;
63 } catch (SQLException e) {
64 e.printStackTrace();
65 return false;
66 } finally {
67 try {
68 st.close();
69 } catch(Exception e) {}
70 }


But Line 61 is part of the Sqlite package
Richard MacCutchan 13-Mar-18 6:06am    
Then you need to trace back from the last call to find out where the table has been locked.
amanuelhaft 13-Mar-18 6:10am    
ok Sir, Thank you for your response !
Richard Deeming 13-Mar-18 9:54am    
public boolean execute(String sql)
public QueryResult executeQuery(String sql)

Those methods will force you to write code which is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900