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 😉
Ahhh, so nice, it helped me alot during my coding!
Thaks a lot for the posting, it saved me a time and aspirins 😉 will spread the word around me
Beautiful! Thank you so much for sharing. Saved me many hours and got right information with perfect sample code.
You’re welcome – glad I could help 🙂
So simple. Cheers mate and thanks for sharing.