DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. A Serverless, Zero-Configuration Database Solution : SQLite

A Serverless, Zero-Configuration Database Solution : SQLite

Cagdas Basaraner user avatar by
Cagdas Basaraner
·
Jan. 17, 13 · Interview
Like (0)
Save
Tweet
Share
5.80K Views

Join the DZone community and get the full member experience.

Join For Free
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 (on http://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
    con.close();
     
    // 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.setString(1, "1");
    prep.setString(2, "andy brown");
    prep.execute();
    // 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.setInt(1, "andy black");
    prep.setString(2, 1);
    prep.execute();

For more detailed examples about SQL syntax, please take a look at:
http://docs.oracle.com/javase/tutorial/jdbc/index.html


 

Database SQLite

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

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • An Introduction to Data Mesh
  • Quick Pattern-Matching Queries in PostgreSQL and YugabyteDB
  • Agile Transformation With ChatGPT or McBoston?
  • What Is Policy-as-Code? An Introduction to Open Policy Agent

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: