How to Connect SQLite with Java

How to connect SQLite with Java?

You need to have a SQLite JDBC driver in your classpath.

Taro L. Saito (xerial) forked the Zentus project and now maintains it under the name sqlite-jdbc. It bundles the native drivers for major platforms so you don't need to configure them separately.

What is a proper way to connect to a sqlite database, execute a prepared Statement and close the connection with java?

The problem is that you are closing the result set, statement and connection before you do things with the result set. This is not allowed: a result set needs to be open when you retrieve values using getString (not shown in your code, but present in your stack trace).

As you are using try-with-resource which will close the resource on exit, it is even unnecessary to explicitly close. It also looks like the SQLite driver does not meet the JDBC requirements that closing an already closed resource should be a no-op, as it is throwing an exception that the connection is closed when closing the statement (likely by the try-with-resources).

You need to change your code to

public boolean checkForUser(String username){
    try(Connection con = this.connect();
            PreparedStatement pstmt = createPreparedStatementRegistrate(conn, username);
            ResultSet rs= pstmt.executeQuery();)
            {
            if (rueck.next()){
                //do some stuff
            }
    }catch(SQLException e){
        e.printStackTrace();
    }
    return null;
}

That is, remove the lines

pstmt.close();
rs.close();
con.close();

how to connect sqLite database to netbeans

Here is example http://www.tutorialspoint.com/sqlite/sqlite_java.htm

Most important part is jdbc url to create connection:

jdbc:sqlite:mydb.sqlite

This url assumes that test.db located in same directory, u application starts from. But u can put path to certain db.

For example:

jdbc:sqlite:c:/temp/sqlite/mydb.sqlite

If u distribute u application when there u manage some initialization. Actually u have to set environment for u application before start. Let's say u have good working application in netbeans and u copy application to another machine.
Configure environment for application to make it same as actual working application instance in netbeans. Question u should think about

• What path to database to use relative or absolute?
• Where database location on filesystem (absolute path to database)?
• Where directory from which application starts (relative path to database)?

Useful information:

• jdbc driver will create database mysql.sqlite if it doesn't exists (path to directory "c:/temp/sqlite" should be real) and after u can recreate necessary structure of database.
• u can use System Properties https://docs.oracle.com/javase/tutorial/essential/environment/sysprop.html especially user.dir and user.home to set predefined location of u database (create database in there or copy existing database file)

How to connect to sqlite database on environmental path

You can expand the Windows environment with String.format() and System.getenv():

String url = String.format("jdbc:sqlite:%s\\sqlite.db", System.getenv("APPDATA"));
connection = DriverManager.getConnection(url );

Java JDBC on SQLite

The classpath separator is the colon (":"), not the semicolon. It's only a semicolon on windows, which is probably why the tutorial uses that. So, just update the classpath param to: -cp .:sqlite-jdbc-3.27.2.1jar :)

NB: sidenote, but, sqlite is a weird fit for java. something like h2 makes a lot more sense. SQLite gives virtually no benefits: You still need to ship an SQLite engine and launch it from within the process if you want it to feel lightweight, OR you need to explicitly ask the user to install the thing, at which point you might as well ask them to install a full featured DB like postgres. H2 is in-process, does not require you to ship one executable for each and every os/architecture combination you intend to support, and does not require messing about with trying to make the host OS launch native apps. It 'just works'. You should use sqlite only if you have a specific need for precisely sqlite and nothing else will do (for example, you're on android, which provides sqlite for you, or, you're trying to inspect or change firefox data storage, which are sqlite dbs).

---

Related Topics