Accessing sqlite database from java

Saved searches

Use saved searches to filter your results more quickly

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session.

License

Apache-2.0, BSD-2-Clause licenses found

Licenses found

xerial/sqlite-jdbc

This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository.

Name already in use

A tag already exists with the provided branch name. Many Git commands accept both tag and branch names, so creating this branch may cause unexpected behavior. Are you sure you want to create this branch?

Sign In Required

Please sign in to use Codespaces.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching GitHub Desktop

If nothing happens, download GitHub Desktop and try again.

Launching Xcode

If nothing happens, download Xcode and try again.

Launching Visual Studio Code

Your codespace will open once ready.

There was a problem preparing your codespace, please try again.

Latest commit

Git stats

Files

Failed to load latest commit information.

README.adoc

SQLite JDBC is a library for accessing and creating SQLite database files in Java.

Our SQLiteJDBC library requires no configuration since native libraries for major OSs, including Windows, macOS, Linux etc., are assembled into a single JAR (Java Archive) file.

➡️ More usage examples and configuration are available in USAGE.md

SQLite JDBC is a library for accessing SQLite databases through the JDBC API. For the general usage of JDBC, see JDBC Tutorial or Oracle JDBC Documentation.

1. Download sqlite-jdbc-3.42.0.0.jar then append this jar file into your classpath.
2. Open a SQLite database connection from your code. (see the example below)

Assuming sqlite-jdbc-3.42.0.0.jar is placed in the current directory.

> javac Sample.java > java -classpath ".;sqlite-jdbc-3.42.0.0.jar" Sample # in Windows or > java -classpath ".:sqlite-jdbc-3.42.0.0.jar" Sample # in macOS or Linux name = leo id = 1 name = yui id = 2

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Sample < public static void main(String[] args) < Connection connection = null; try < // create a database connection connection = DriverManager.getConnection("jdbc:sqlite:sample.db"); Statement statement = connection.createStatement(); statement.setQueryTimeout(30); // set timeout to 30 sec. statement.executeUpdate("drop table if exists person"); statement.executeUpdate("create table person (id integer, name string)"); statement.executeUpdate("insert into person values(1, 'leo')"); statement.executeUpdate("insert into person values(2, 'yui')"); ResultSet rs = statement.executeQuery("select * from person"); while(rs.next()) < // read the result set System.out.println("name pl-s1">rs.getString("name")); System.out.println("id pl-s1">rs.getInt("id")); > > catch(SQLException e) < // if the error message is "out of memory", // it probably means no database file is found System.err.println(e.getMessage()); > finally < try < if(connection != null) connection.close(); > catch(SQLException e) < // connection close failed. System.err.println(e.getMessage()); > > > >

Our SQLite JDBC driver package (i.e., sqlite-jdbc-3.42.0.0.jar ) contains three types of native SQLite libraries ( sqlitejdbc.dll , sqlitejdbc.jnilib , sqlitejdbc.so ), each of them is compiled for Windows, macOS and Linux. An appropriate native library file is automatically extracted into your OS’s temporary folder, when your program loads org.sqlite.JDBC driver.

Supported Operating Systems

Since sqlite-jdbc-3.6.19, the natively compiled SQLite engines will be used for the following operating systems:

Источник

Accessing sqlite database from java

Learn Latest Tutorials

Preparation

Trending Technologies

B.Tech / MCA

Javatpoint Services

JavaTpoint offers too many high quality services. Mail us on h[email protected], to get more information about given services.

• Website Designing
• Website Development
• Java Development
• PHP Development
• WordPress
• Graphic Designing
• Logo
• Digital Marketing
• On Page and Off Page SEO
• PPC
• Content Development
• Corporate Training
• Classroom and Online Training
• Data Entry

Training For College Campus

JavaTpoint offers college campus training on Core Java, Advance Java, .Net, Android, Hadoop, PHP, Web Technology and Python. Please mail your requirement at [email protected].
Duration: 1 week to 2 week

Like/Subscribe us for latest updates or newsletter

Источник

Java Connect to SQLite with JDBC Example

SQLite is a simple, small, fast, reliable, server-less, zero-configuration and no-installation SQL database library which is running in-process with the client application. Although there is no official JDBC driver library from www.sqlite.org, there is one provided by www.xerial.org – an XML Database Management System project.

1. Download SQLite JDBC driver

You can download the latest version of JDBC driver for SQLite here. The download is categorized by versions, so browse a directory for a specific version you want: 3.5.9, 3.6.16, 3.7.2, etc. As of this writing, the latest version is 3.7.2 which corresponds to the jar file sqlite-jdbc-3.7.2.jar .

Beside Java class files, the jar file includes SQLite binaries for Windows, Linux and Mac (for both 32-bit and 64-bit).

Place the sqlite-jdbc-VERSION.jar into your classpath.

2. SQLite JDBC database connection URL

Here is the syntax of database connection URL for file system database:

jdbc:sqlite:database_file_path

Where database_file_path can be either relative or absolute path. For example:

jdbc:sqlite:C:/work/product.db

And here is the syntax of database connection URL for memory database:

3. Loading SQLite JDBC driver

Class.forName("org.sqlite.JDBC");

DriverManager.registerDriver(new org.sqlite.JDBC());

4. Making SQLite JDBC connection

The following example program creates a connection to a SQLite database file product.db which is in the same directory as the program, prints some database metadata information, and closes the connection:

package net.codejava.jdbc; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.SQLException; /** * This program demonstrates making JDBC connection to a SQLite database. * @author www.codejava.net * */ public class JdbcSQLiteConnection < public static void main(String[] args) < try < Class.forName("org.sqlite.JDBC"); String dbURL = "jdbc:sqlite:product.db"; Connection conn = DriverManager.getConnection(dbURL); if (conn != null) < System.out.println("Connected to the database"); DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData(); System.out.println("Driver name: " + dm.getDriverName()); System.out.println("Driver version: " + dm.getDriverVersion()); System.out.println("Product name: " + dm.getDatabaseProductName()); System.out.println("Product version: " + dm.getDatabaseProductVersion()); conn.close(); >> catch (ClassNotFoundException ex) < ex.printStackTrace(); >catch (SQLException ex) < ex.printStackTrace(); >> >

To see the coding in action, I recommend you to watch the video below:

JDBC API References:

Related JDBC Tutorials:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He started programming with Java in the time of Java 1.4 and has been falling in love with Java since then. Make friend with him on Facebook and watch his Java videos you YouTube.

Источник

SQLite Java

This SQLite Java section teaches you step by step how to interact with SQLite using Java JDBC API.

There are some interfaces that you can use to interact with SQLite using the Java language. Some of these are the native C API wrapper while the other implement the standardized Java Database Connectivity (JDBC) API.

In this section, we will introduce you to a modern JDBC driver which is called SQLiteJDBC package. The SQLiteJDBC package contains both Java classes, as well as native SQLite libraries for Windows, Mac OS X, and Linux.

• Connecting to an SQLite database: this tutorial shows you how to download SQLiteJDBC driver and connect to an existing SQLite database using JDBC.
• Creating a new SQLite database – in this tutorial, we will show you how to create a new SQLite database from a Java program using SQLiteJDBC driver.
• Creating a new table using JDBC – before working with data, you need to create a table. This tutorial shows you how to create a new table in an SQLite database from a Java program.
• Inserting data into a table from a Java program – this tutorial walks you through the steps for inserting data into a table from a Java program
• Querying data from a table with or without parameters – after having the data in the table, we show you how to query data using a SELECT statement. You will learn how to issue a simple SELECT statement to query all rows from a table, as well as use a query with parameters to select data based on user’s input.
• Updating existing data using PreparedStatement object – this tutorial guides you how to update existing data in a table.
• Deleting data from a table – this tutorial provides the steps for deleting existing data in a table.
• Managing transaction – this tutorial shows you how to manage SQLite transaction using Java JDBC API such as setAutoCommit , commit , and rollback .
• Writing and Reading SQLite BLOB – we will show you how to update the SQLite BLOB data into a table and query BLOB data for displaying.

Getting Started

Источник

Работа с SQLite в Java

Всем доброго времени суток. На связи Алексей Гулынин. В данной статье я бы хотел рассказать, как работать с SQLite в Java. При работе с базами данных на Java будем использовать технологию JDBC (Java DataBase Connectivity)) . JDBC — это API для работы с базами данных на Java. Компания Oracle разработала универсальный интерфейс для работы с драйверами JDBC. Допустим, вы написали какую-то СУБД. Для того, чтобы программа на Java могла работать с вашей СУБД вам необходимо реализовать данный интерфейс.

В данном примере мы будем работать с SQLite . Для этого скачаем драйвер JDBC для SQLite (в поисковике можете прямо так и указать).

Для того, чтобы подключить данный драйвер БД нужно зайти в File — Project Structure (Ctrl + Alt + Shift + S) — Libraries и указываем путь до драйвера, который скачали.

Создадим простую базу данных по учету расходов. Будет всего 1 таблица: Products . Таблица Products будет состоять из следующих столбцов: id , good (наименование товара), category_name (наименование категории, к которой относится товар), price (цена товара).

Файл базы данных вместе с проектом можно скачать по ссылке.

Создадим класс Product , в котором будет находиться информация по нашему продукту:

public class Product < // Поля класса public int id; public String good; public double price; public String category_name; // Конструктор public Product(int id, String good, double price, String category_name) < this.id = id; this.good = good; this.price = price; this.category_name = category_name; >// Выводим информацию по продукту @Override public String toString() < return String.format("ID: %s | Товар: %s | Цена: %s | Категория: %s", this.id, this.good, this.price, this.category_name); >>

Создадим класс DbHandler , в котором будет находиться вся логика работы с базой данных:

import org.sqlite.JDBC; import java.sql.*; import java.util.*; public class DbHandler < // Константа, в которой хранится адрес подключения private static final String CON_STR = "jdbc:sqlite:D:/myfin.db"; // Используем шаблон одиночка, чтобы не плодить множество // экземпляров класса DbHandler private static DbHandler instance = null; public static synchronized DbHandler getInstance() throws SQLException < if (instance == null) instance = new DbHandler(); return instance; >// Объект, в котором будет храниться соединение с БД private Connection connection; private DbHandler() throws SQLException < // Регистрируем драйвер, с которым будем работать // в нашем случае Sqlite DriverManager.registerDriver(new JDBC()); // Выполняем подключение к базе данных this.connection = DriverManager.getConnection(CON_STR); >public List getAllProducts() < // Statement используется для того, чтобы выполнить sql-запрос try (Statement statement = this.connection.createStatement()) < // В данный список будем загружать наши продукты, полученные из БД Listproducts = new ArrayList(); // В resultSet будет храниться результат нашего запроса, // который выполняется командой statement.executeQuery() ResultSet resultSet = statement.executeQuery("SELECT id, good, price, category_name FROM products"); // Проходимся по нашему resultSet и заносим данные в products while (resultSet.next()) < products.add(new Product(resultSet.getInt("id"), resultSet.getString("good"), resultSet.getDouble("price"), resultSet.getString("category_name"))); >// Возвращаем наш список return products; > catch (SQLException e) < e.printStackTrace(); // Если произошла ошибка - возвращаем пустую коллекцию return Collections.emptyList(); >> // Добавление продукта в БД public void addProduct(Product product) < // Создадим подготовленное выражение, чтобы избежать SQL-инъекций try (PreparedStatement statement = this.connection.prepareStatement( "INSERT INTO Products(`good`, `price`, `category_name`) " + "VALUES(?, ?, ?)")) < statement.setObject(1, product.good); statement.setObject(2, product.price); statement.setObject(3, product.category_name); // Выполняем запрос statement.execute(); >catch (SQLException e) < e.printStackTrace(); >> // Удаление продукта по id public void deleteProduct(int id) < try (PreparedStatement statement = this.connection.prepareStatement( "DELETE FROM Products WHERE < statement.setObject(1, id); // Выполняем запрос statement.execute(); >catch (SQLException e) < e.printStackTrace(); >> >

Напишем основной класс Main , в котором будут вызываться все наши методы:

import java.sql.SQLException; import java.util.List; public class Main < public static void main(String[] args) < try < // Создаем экземпляр по работе с БД DbHandler dbHandler = DbHandler.getInstance(); // Добавляем запись //dbHandler.addProduct(new Product("Музей", 200, "Развлечения")); // Получаем все записи и выводим их на консоль Listproducts = dbHandler.getAllProducts(); for (Product product : products) < System.out.println(product.toString()); >// Удаление записи с //dbHandler.deleteProduct(8); > catch (SQLException e) < e.printStackTrace(); >> >

В данном примере мы рассмотрели как работать с базой данных в Java на примере SQLite. Рассмотрели операции: выборка данных, добавление и удаление данных. Операцию по обновлению данных оставляю в качестве домашнего задания. В следующих статьях я покажу вам, как работать с графическим интерфейсом (используя Swing ) на примере создания игры крестики-нолики. Консольный вариант игры мы уже сделали, теперь сделаем — графический. Также вы можете выполнить все эти манипуляции с базой данных, используя графический интерфейс.

На связи был Алексей Гулынин, оставляйте свои комментарии, увидимся в следующих статьях.

Источник