How to connect SQLite with Java?

Question

I am using one simple code to access the SQLite database from Java application . My code is

 import java.sql.Connection;  
 import java.sql.DriverManager;  
 import java.sql.ResultSet;  
 import java.sql.Statement;  
 public class ConnectSQLite 
 {  
  public static void main(String[] args) 
  {  
     Connection connection = null;  
     ResultSet resultSet = null;  
     Statement statement = null;  

     try 
     {  
         Class.forName("org.sqlite.JDBC");  
         connection = DriverManager.getConnection("jdbc:sqlite:D:\\testdb.db");  
         statement = connection.createStatement();  
         resultSet = statement  
                 .executeQuery("SELECT EMPNAME FROM EMPLOYEEDETAILS");  
         while (resultSet.next()) 
         {  
             System.out.println("EMPLOYEE NAME:"  
                     + resultSet.getString("EMPNAME"));  
         }  
     } 
     catch (Exception e) 
     {  
         e.printStackTrace();  
     }
     finally 
     {  
         try 
         {  
             resultSet.close();  
             statement.close();  
             connection.close();  
         } 
         catch (Exception e) 
         {  
             e.printStackTrace();  
         }  
     }  
 }  
}  

But this code gives one exception like

java.lang.ClassNotFoundException: org.sqlite.JDBC

How can I slove this,please help me.

Solution 1

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.

Solution 2

If you are using netbeans Download the sqlitejdbc driver Right click the Libraries folder from the Project window and select Add Library , then click on the Create button enter the Library name (SQLite) and hit OK

You have to add the sqlitejdbc driver to the class path , click on the Add Jar/Folder.. button and select the sqlitejdbc file you've downloaded previously Hit OK and you are ready to go !

Solution 3

If you are using Netbeans using Maven to add library is easier. I have tried using above solutions but it didn't work.

<dependencies>
    <dependency>
      <groupId>org.xerial</groupId>
      <artifactId>sqlite-jdbc</artifactId>
      <version>3.7.2</version>
    </dependency>
</dependencies>

I have added Maven dependency and java.lang.ClassNotFoundException: org.sqlite.JDBC error gone.

Solution 4

I'm using Eclipse and I copied your code and got the same error. I then opened up the project properties->Java Build Path -> Libraries->Add External JARs... c:\jrun4\lib\sqlitejdbc-v056.jar Worked like a charm. You may need to restart your web server if you've just copied the .jar file.

Solution 5

    import java.sql.ResultSet;
    import java.sql.SQLException;
    import javax.swing.JOptionPane;
    import org.sqlite.SQLiteDataSource;
    import org.sqlite.SQLiteJDBCLoader;

    public class Test {

        public static final boolean Connected() {
            boolean initialize = SQLiteJDBCLoader.initialize();

            SQLiteDataSource dataSource = new SQLiteDataSource();
            dataSource.setUrl("jdbc:sqlite:/home/users.sqlite");
            int i=0;
            try {
                ResultSet executeQuery = dataSource.getConnection()
                        .createStatement().executeQuery("select * from \"Table\"");
                while (executeQuery.next()) {
i++;
                    System.out.println("out: "+executeQuery.getMetaData().getColumnLabel(i));

                }



            } catch (SQLException ex) {
                JOptionPane.showMessageDialog(null, ex);
            }

            return initialize;

        }

View more solutions

Comments

[Dyapa Srikanth]

i am using this driver, and works fine. But why it is not compacting database while creating.With FireFox sqlite plugin Database compacted from 11.5MB to 11.3MB. how to do that with java.

[Donal Fellows]

@Dyapa You need to periodically run VACUUM; since JDBC doesn't actually interpret the SQL (for very good reasons) just send it over. Or use a pragma per connection to run in auto-vacuum mode.

[Dyapa Srikanth]

@DonalFellows can u explain how to run VACUUM through java.

[Roy Hinkley]

For those experiencing this issue with Tomcat this jar needs to be put into the lib folder for respective Tomcat version. Tomcat will also need to be restarted. In the web application, the jar needs to be placed into the WEB-INF lib folder as well. You don't need to add it to the build path because putting it in the WEB-INF lib folder places it there automatically.

[tonytony]

@AndroidAddict Thank you bro you saved hours of my time :)

[Indigo]

I struggled a lot because of this problem. I am a Java newbie and using Netbeans. It's been a frustration. Finally, your answer saved my day.

[Davoud Taghawi-Nejad]

Its a lot of irrelevant code to learn about a relatively specific problem. Not saying its a bad video/code, but not specific for this problem.

[kmort]

I was using dataSource.setDatabaseName(...) and it wasn't working. Now I see I needed to use dataSource.setUrl(...). Thanks. :-)

[berione]

@AndroidAddict thank you so much ıhave been struggling with this issue over an hour... thank you dude thank you :)

[kevinarpe]

@DyapaSrikanth: Execute a query (or an update, not sure which) with the text VACUUM. Example: statement.executeQuery("VACUUM");