Over a million developers have joined DZone.

A Serverless, Zero-Configuration Database Solution: SQLite

· DevOps Zone

The DevOps Zone is brought to you in partnership with Sonatype Nexus. The Nexus Suite helps scale your DevOps delivery with continuous component intelligence integrated into development tools, including Eclipse, IntelliJ, Jenkins, Bamboo, SonarQube and more. Schedule a demo today

Most software need saving data. Sometimes that data is predicted to be small and hundreds or thousands of transactions on it will not be needed at the same time. But you will need some SQL-like operations on that data, because some modifications can be difficult and time consuming with regular file operations. At that time, SQLite becomes a very practical solution to this situation.
It started as a C/C++ library (on http://www.sqlite.org/) but it also has Xerial jdbc project for Java (onhttp://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC and https://bitbucket.org/xerial/sqlite-jdbc lastly). We will tell some details of Java JDBC project here. Some critical properties are:
  • You need only one jar file and adding it to the classpath.
Download latest jar (from here: https://bitbucket.org/xerial/sqlite-jdbc/downloads) and add to the classpath.
  • Needs one line of code to start using.
Class.forName("org.sqlite.JDBC"); line is enough for activating driver.
  • It creates one database file per schema at the place which you will determine.
Connection con = DriverManager.getConnection("jdbc:sqlite:mydb.db"); line creates "mydb.db" file as database file on the root of your project and creates a connection for DB operations.
  • Supports a general formed JDBC SQL syntax with a useful JDBC API.
Some code examples are shown below (connection opening/closing statements are not included each time for simplifying statements):

// opening connection
Connection con = DriverManager.getConnection("jdbc:sqlite:person.db");
Statement stat = con.createStatement();

// closing connection
// creating table
stat.executeUpdate("create table person(id INT, name varchar(30));");
// dropping table
stat.executeUpdate("drop table if exists person");
// inserting data
PreparedStatement prep = con.prepareStatement("insert into person values(?,?);");
prep.setInt(1, 1);
prep.setString(2, "andy brown");
// selecting data
ResultSet res = stat.executeQuery("select * from person");
while (res.next()) {
     System.out.println(res.getString("id") + " " +res.getString("name"));
// updating data
PreparedStatement prep = con.prepareStatement("update person set name = ? where id = ?;");
prep.setString(1, "andy black");
prep.setInt(2, 1);

For more detailed examples about SQL syntax, please take a look at: 

The DevOps Zone is brought to you in partnership with Sonatype Nexus. Use the Nexus Suite to automate your software supply chain and ensure you're using the highest quality open source components at every step of the development lifecycle. Get Nexus today


Published at DZone with permission of Cagdas Basaraner, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}