[JDBC] I tried to access the SQLite3 database from Java.

This time, I was studying database access from Java in the process of software development, and I succeeded in reading the database, so I wrote an article. Since this is almost my first experience with database access using JDBC, I would like to do it step by step as a review of SQLite3.

For how to use SQLite3, please refer to the article Qiita: SQLite3 operation command related summary.


The development environment this time is as follows.

Create a database

Create a database in advance to read elements from an existing database.

Create database

sqlite3 test.db

Define table1


At this point, the database files will be created in the directory. testJDBC_Qiita.png

I put some data in the INSERT statement, so let's take a look at it in the SELECT statement.

SELECT * FROM table1;
id          name      
----------  ----------
1           satou     
2           suzuki    
3           tanaka    
4           katou     
5           takahashi 

I was able to confirm the database.

JDBC download

A JDBC driver is required to access SQLite3 from Java. Download JDBC from the JDBC Repository (https://bitbucket.org/xerial/sqlite-jdbc/downloads/). I think the version can be any one, but this time I will download the latest sqlite-jdbc-3.30.1.jar at the time of writing.

Once you've downloaded JDBC, copy it to the directory that contains the database you just created.

Java programming creation

This time, I created a java file by making appropriate changes based on the code of the site that I referred to.


import java.sql.*;

public class TestDatabaseDriver {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        try {

            //Specify the database PATH. It seems that you can go with either a relative path or an absolute path
            connection = DriverManager.getConnection("jdbc:sqlite:test.db");
            statement = connection.createStatement();
            String sql = "select * from table1";
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
        } catch (ClassNotFoundException e) {
        } catch (SQLException e) {
        } finally {
            try {
                if (statement != null) {
            } catch (SQLException e) {
            try {
                if (connection != null) {
            } catch (SQLException e) {

Compile and run

javac *.java && java -classpath .:sqlite-jdbc-3.30.1.jar TestDatabaseDriver

Execution result


From this result, it seems that the part to be read is determined by the System.out.println (rs.getString (1)); part of TestDAtabaseDriver.java, so let's make some changes.

Change before


After change

System.out.println(rs.getString(1) + "|" + rs.getString(2));

I will try it.

Compile and run

javac *.java && java -classpath .:sqlite-jdbc-3.30.1.jar TestDatabaseDriver

Execution result



I was able to read the database safely. This time I tried JDBC for the first time, so most of it was copied and pasted, but I would like to expand the range based on this and make it highly reusable.

This article table of contents page

[Database] SQLite3 / JDBC Summary

Reference site

--JDBC repository for SQLite -[TECHSCORE BROG / 3 minutes to build! Try running SQLite in various environments](https://www.techscore.com/blog/2015/03/27/3%E5%88%86%E3%81%A7%E6%A7%8B%E7 % AF% 89% EF% BC% 81sqlite% E3% 82% 92% E8% 89% B2% E3% 80% 85% E3% 81% AA% E7% 92% B0% E5% A2% 83% E3% 81 % A7% E5% 8B% 95% E3% 81% 8B% E3% 81% 97% E3% 81% A6% E3% 81% BF% E3% 82% 8B /)

Recommended Posts

[JDBC] I tried to access the SQLite3 database from Java.
[JDBC] I tried to make SQLite3 database access from Java into a method for each SQL statement.
[Rails] I tried to raise the Rails version from 5.0 to 5.2
I want to write quickly from java to sqlite
[Java] I want to calculate the difference from the date
I tried to implement the Euclidean algorithm in Java
[JDBC ③] I tried to input from the main method using placeholders and arguments.
I tried to summarize the basics of kotlin and java
I tried to interact with Java
I tried to explain the method
I tried the Java framework "Quarkus"
I tried to summarize Java learning (1)
The road from JavaScript to Java
I tried to summarize Java 8 now
I tried upgrading from CentOS 6.5 to CentOS 7 with the upgrade tool
I tried connecting to Oracle Autonomous Database 21c with JDBC Thin
I tried to translate the error message when executing Eclipse (Java)
I tried to summarize the methods of Java String and StringBuilder
[Java] I tried to make a maze by the digging method ♪
I tried to move the Java compatible FaaS form "Fn Project"
I tried to solve the paiza campaign problem "Challenge from Kaito 813"
I tried to display the calendar on the Eclipse console using Java.
I tried to summarize the methods used
I tried to summarize Java lambda expressions
I tried the new era in Java
I tried to implement the Iterator pattern
I tried to summarize the Stream API
I tried to build Ruby 3.0.0 from source
I went to the Java Women's Club # 1
[Introduction to Java] I tried to summarize the knowledge that I think is essential
I tried to visualize the access of Lambda → Athena with AWS X-Ray
I translated the grammar of R and Java [Updated from time to time]
Run R from Java I want to run rJava
I tried to make Basic authentication with Java
I tried to organize the session in Rails
java I tried to break a simple block
I tried hitting a Java method from ABCL
I tried to implement deep learning in Java
I tried to output multiplication table in Java
I tried to set tomcat to run the Servlet.
[Java / PostgreSQL] Connect the WEB application to the database
I tried to create Alexa skill in Java
I tried to break a block with java (1)
Try connecting to the Autonomous Database with JDK6 (Java) + JDBC OCI Driver (type 2).
I tried to get the distance from the address string to the nearest station with ruby
Try to access the on-premise system from SAP Cloud Platform --Java App Edition
[Small story] I tried to make the java ArrayList a little more convenient
Changes from Java 8 to Java 11
Sum from Java_1 to 100
Access API.AI from Java
From Java to Ruby !!
I tried to make a program that searches for the target class from the process that is overloaded with Java
I tried to organize the cases used in programming
Tokoro I rewrote in the migration from Wicket 7 to 8
I tried to implement TCP / IP + BIO with JAVA
How to write Scala from the perspective of Java
[Java 11] I tried to execute Java without compiling with javac
I tried to summarize the state transition of docker
[Java] I tried to solve Paiza's B rank problem
I tried to decorate the simple calendar a little
I tried to operate SQS using AWS Java SDK