Using SQLite with JSP

During my study at the university, my group got an assignment in a Web engineering course where we were asked to develop a couple of mini-webshops, one in PHP and one in JSP. To centralize the data storage, we decided to use a single SQLite database. I got the assignment to develop the webshop in JSP, and I started looking at how to get SQLite working with JSP. I spend a couple of hours on it, and was surprised how difficult it was to find something about this on the Internet. So, I thought it could be nice to share my solution 🙂

In this post, I’ll show you:

  • how to get SQLite 3 up and running with JSP
  • how to select data from an SQLite database in JSP

To get SQLite 3 up and running, download the SQLiteJDBC here: http://www.zentus.com/sqlitejdbc/

Place the .jar file in C:\…\webapps\MyWebShop\WEB-INF\lib (if the WEB-INF and lib dirs does not exist, you should create them)

To load the library, run services.msc and restart the tomcat service.

TIP! To make the library accessible from other java projects, you can place it in: C:\Program Files\Java\jre7\lib (or wherever your JRE is installed)

In the root of your webshop solution (e.g. C:\…\webapps\MyWebShop)  create an sqlitedemo.jsp file, and insert the code shown below:

<%@ page contentType="text/html" %>
<%@ page import="java.sql.*" %>
<%@ page import="org.sqlite.*" %>

<!DOCTYPE html>
<html lang="en">
    <head>
        <title>SQLite Demo</title>
    </head>
    <body>
        <table>
            <thead>
                <tr>
                    <th>Item No.</th>
                    <th>Name</th>
                    <th>Price</th>
                    <th>Description</th>
                </tr>
            </thead>
            <tbody>
            <%
                Class.forName("org.sqlite.JDBC");
                Connection conn =
                     DriverManager.getConnection("jdbc:sqlite:c:\\...\\webshop.db");
                Statement stat = conn.createStatement();

                ResultSet rs = stat.executeQuery("select * from products;");

                while (rs.next()) {
                    out.println("<tr>");
                    out.println("<td>" + rs.getString("id") + "</td>");
                    out.println("<td>" + rs.getString("title") + "</td>");
                    out.println("<td>" + rs.getString("price") + " kr.</td>");
                    out.println("<td>" + rs.getString("description") + "</td>");
                    out.println("</tr>");
                }

                rs.close();
                conn.close();
            %>
            </tbody>
        </table>
    </body>
</html>

As seen in the code, I first create the connection to my SQLite db in line 23 and 24. In line 27 I execute a query to get all the products, and from line 29 I start to iterate through the items until the result set is empty.

As a last remark: remember to close the cursors and connection objects 😉

Tags: ,

5 Responses to “Using SQLite with JSP”

  1. Lars Ditlev October 6, 2011 at 8:20 pm #

    Ahhh, so nice, it helped me alot during my coding!

  2. Jorge Borrás April 11, 2012 at 11:44 am #

    Thaks a lot for the posting, it saved me a time and aspirins 😉 will spread the word around me

  3. Harish m May 5, 2012 at 9:10 am #

    Beautiful! Thank you so much for sharing. Saved me many hours and got right information with perfect sample code.

    • Lasse Christiansen May 7, 2012 at 11:43 pm #

      You’re welcome – glad I could help 🙂

  4. Rhys February 7, 2013 at 8:26 am #

    So simple. Cheers mate and thanks for sharing.

Leave a Reply to Lasse Christiansen