Category: sqlite

SQLite – AND & OR Operators ”; Previous Next SQLite AND & OR operators are used to compile multiple conditions to narrow down the selected data in an SQLite statement. These two operators are called conjunctive operators. These operators provide a means to make multiple comparisons with different operators in the same SQLite statement. The AND Operator The AND operator allows the existence of multiple conditions in a SQLite statement”s WHERE clause. While using AND operator, complete condition will be assumed true when all the conditions are true. For example, [condition1] AND [condition2] will be true only when both condition1 and condition2 are true. Syntax Following is the basic syntax of AND operator with WHERE clause. SELECT column1, column2, columnN FROM table_name WHERE [condition1] AND [condition2]…AND [conditionN]; You can combine N number of conditions using AND operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, all conditions separated by the AND must be TRUE. Example Consider COMPANY table with the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records where AGE is greater than or equal to 25 AND salary is greater than or equal to 65000.00. sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000; ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 The OR Operator The OR operator is also used to combine multiple conditions in a SQLite statement”s WHERE clause. While using OR operator, complete condition will be assumed true when at least any of the conditions is true. For example, [condition1] OR [condition2] will be true if either condition1 or condition2 is true. Syntax Following is the basic syntax of OR operator with WHERE clause. SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]…OR [conditionN] You can combine N number of conditions using OR operator. For an action to be taken by the SQLite statement, whether it be a transaction or query, only any ONE of the conditions separated by the OR must be TRUE. Example Consider COMPANY table with the following records. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following SELECT statement lists down all the records where AGE is greater than or equal to 25 OR salary is greater than or equal to 65000.00. sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 OR SALARY >= 65000; ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Print Page Previous Next Advertisements ”;

SQLite – ORDER BY Clause ”; Previous Next SQLite ORDER BY clause is used to sort the data in an ascending or descending order, based on one or more columns. Syntax Following is the basic syntax of ORDER BY clause. SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort, that column should be available in the column-list. Example Consider COMPANY table with the following records. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example, which will sort the result in descending order by SALARY. sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 7 James 24 Houston 10000.0 2 Allen 25 Texas 15000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 Following is an example, which will sort the result in descending order by NAME and SALARY. sqlite> SELECT * FROM COMPANY ORDER BY NAME, SALARY ASC; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 2 Allen 25 Texas 15000.0 5 David 27 Texas 85000.0 7 James 24 Houston 10000.0 6 Kim 22 South-Hall 45000.0 4 Mark 25 Rich-Mond 65000.0 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 Following is an example, which will sort the result in descending order by NAME. sqlite> SELECT * FROM COMPANY ORDER BY NAME DESC; This will produce the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 3 Teddy 23 Norway 20000.0 1 Paul 32 California 20000.0 4 Mark 25 Rich-Mond 65000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 5 David 27 Texas 85000.0 2 Allen 25 Texas 15000.0 Print Page Previous Next Advertisements ”;

SQLite – SELECT Query ”; Previous Next SQLite SELECT statement is used to fetch the data from a SQLite database table which returns data in the form of a result table. These result tables are also called result sets. Syntax Following is the basic syntax of SQLite SELECT statement. SELECT column1, column2, columnN FROM table_name; Here, column1, column2 … are the fields of a table, whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax − SELECT * FROM table_name; Example Consider COMPANY table with the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is an example to fetch and display all these records using SELECT statement. Here, the first three commands have been used to set a properly formatted output. sqlite>.header on sqlite>.mode column sqlite> SELECT * FROM COMPANY; Finally, you will get the following result. ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 If you want to fetch only selected fields of COMPANY table, then use the following query − sqlite> SELECT ID, NAME, SALARY FROM COMPANY; The above query will produce the following result. ID NAME SALARY ———- ———- ———- 1 Paul 20000.0 2 Allen 15000.0 3 Teddy 20000.0 4 Mark 65000.0 5 David 85000.0 6 Kim 45000.0 7 James 10000.0 Setting Output Column Width Sometimes, you will face a problem related to the truncated output in case of .mode column which happens because of default width of the column to be displayed. What you can do is, you can set column displayable column width using .width num, num…. command as follows − sqlite>.width 10, 20, 10 sqlite>SELECT * FROM COMPANY; The above .width command sets the first column width to 10, the second column width to 20 and the third column width to 10. Finally, the above SELECT statement will give the following result. ID NAME AGE ADDRESS SALARY ———- ——————– ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Schema Information As all the dot commands are available at SQLite prompt, hence while programming with SQLite, you will use the following SELECT statement with sqlite_master table to list down all the tables created in your database. sqlite> SELECT tbl_name FROM sqlite_master WHERE type = ”table”; Assuming you have only COMPANY table in your testDB.db, this will produce the following result. tbl_name ———- COMPANY You can list down complete information about COMPANY table as follows − sqlite> SELECT sql FROM sqlite_master WHERE type = ”table” AND tbl_name = ”COMPANY”; Assuming you have only COMPANY table in your testDB.db, this will produce the following result. CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL ) Print Page Previous Next Advertisements ”;

SQLite – DETACH Database ”; Previous Next SQLite DETACH DATABASE statement is used to detach and dissociate a named database from a database connection which was previously attached using ATTACH statement. If the same database file has been attached with multiple aliases, then DETACH command will disconnect only the given name and rest of the attachment will still continue. You cannot detach the main or temp databases. If the database is an in-memory or temporary database, the database will be destroyed and the contents will be lost. Syntax Following is the basic syntax of SQLite DETACH DATABASE ”Alias-Name” statement. DETACH DATABASE ”Alias-Name”; Here, ”Alias-Name” is the same alias, which you had used while attaching the database using ATTACH statement. Example Consider you have a database, which you created in the previous chapter and attached it with ”test” and ”currentDB” as we can see using .database command. sqlite>.databases seq name file — ————— ———————- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db 3 currentDB /home/sqlite/testDB.db Let”s try to detach ”currentDB” from testDB.db using the following command. sqlite> DETACH DATABASE ”currentDB”; Now, if you will check the current attachment, you will find that testDB.db is still connected with ”test” and ”main”. sqlite>.databases seq name file — ————— ———————- 0 main /home/sqlite/testDB.db 2 test /home/sqlite/testDB.db Print Page Previous Next Advertisements ”;

SQLite – Installation ”; Previous Next SQLite is famous for its great feature zero-configuration, which means no complex setup or administration is needed. This chapter will take you through the process of setting up SQLite on Windows, Linux and Mac OS X. Install SQLite on Windows Step 1 − Go to SQLite download page, and download precompiled binaries from Windows section. Step 2 − Download sqlite-shell-win32-*.zip and sqlite-dll-win32-*.zip zipped files. Step 3 − Create a folder C:>sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files. Step 4 − Add C:>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display the following result. C:>sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter “.help” for instructions Enter SQL statements terminated with a “;” sqlite> Install SQLite on Linux Today, almost all the flavours of Linux OS are being shipped with SQLite. So you just issue the following command to check if you already have SQLite installed on your machine. $sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter “.help” for instructions Enter SQL statements terminated with a “;” sqlite> If you do not see the above result, then it means you do not have SQLite installed on your Linux machine. Following are the following steps to install SQLite − Step 1 − Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section. Step 2 − Run the following command − $tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure –prefix=/usr/local $make $make install The above command will end with SQLite installation on your Linux machine. Which you can verify as explained above. Install SQLite on Mac OS X Though the latest version of Mac OS X comes pre-installed with SQLite but if you do not have installation available then just follow these following steps − Step 1 − Go to SQLite download page, and download sqlite-autoconf-*.tar.gz from source code section. Step 2 − Run the following command − $tar xvfz sqlite-autoconf-3071502.tar.gz $cd sqlite-autoconf-3071502 $./configure –prefix=/usr/local $make $make install The above procedure will end with SQLite installation on your Mac OS X machine. Which you can verify by issuing the following command − $sqlite3 SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter “.help” for instructions Enter SQL statements terminated with a “;” sqlite> Finally, you have SQLite command prompt where you can issue SQLite commands for your exercises. Print Page Previous Next Advertisements ”;

SQLite – Expressions ”; Previous Next An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. SQL expressions are like formulas and they are written in query language. You can also use to query the database for a specific set of data. Syntax Consider the basic syntax of the SELECT statement as follows − SELECT column1, column2, columnN FROM table_name WHERE [CONDITION | EXPRESSION]; Following are the different types of SQLite expressions. SQLite – Boolean Expressions SQLite Boolean Expressions fetch the data on the basis of matching single value. Following is the syntax − SELECT column1, column2, columnN FROM table_name WHERE SINGLE VALUE MATCHTING EXPRESSION; Consider COMPANY table with the following records − ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0 Following is a simple examples showing the usage of SQLite Boolean Expressions − sqlite> SELECT * FROM COMPANY WHERE SALARY = 10000; ID NAME AGE ADDRESS SALARY ———- ———- ———- ———- ———- 4 James 24 Houston 10000.0 SQLite – Numeric Expression These expressions are used to perform any mathematical operation in any query. Following is the syntax − SELECT numerical_expression as OPERATION_NAME [FROM table_name WHERE CONDITION] ; Here, numerical_expression is used for mathematical expression or any formula. Following is a simple example showing the usage of SQLite Numeric Expressions. sqlite> SELECT (15 + 6) AS ADDITION ADDITION = 21 There are several built-in functions such as avg(), sum(), count(), etc., to perform what is known as aggregate data calculations against a table or a specific table column. sqlite> SELECT COUNT(*) AS “RECORDS” FROM COMPANY; RECORDS = 7 SQLite – Date Expressions Date Expressions returns the current system date and time values. These expressions are used in various data manipulations. sqlite> SELECT CURRENT_TIMESTAMP; CURRENT_TIMESTAMP = 2013-03-17 10:43:35 Print Page Previous Next Advertisements ”;

SQLite – CREATE Database ”; Previous Next In SQLite, sqlite3 command is used to create a new SQLite database. You do not need to have any special privilege to create a database. Syntax Following is the basic syntax of sqlite3 command to create a database: − $sqlite3 DatabaseName.db Always, database name should be unique within the RDBMS. Example If you want to create a new database <testDB.db>, then SQLITE3 statement would be as follows − $sqlite3 testDB.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter “.help” for instructions Enter SQL statements terminated with a “;” sqlite> The above command will create a file testDB.db in the current directory. This file will be used as database by SQLite engine. If you have noticed while creating database, sqlite3 command will provide a sqlite> prompt after creating a database file successfully. Once a database is created, you can verify it in the list of databases using the following SQLite .databases command. sqlite>.databases seq name file — ————— ———————- 0 main /home/sqlite/testDB.db You will use SQLite .quit command to come out of the sqlite prompt as follows − sqlite>.quit $ The .dump Command You can use .dump dot command to export complete database in a text file using the following SQLite command at the command prompt. $sqlite3 testDB.db .dump > testDB.sql The above command will convert the entire contents of testDB.db database into SQLite statements and dump it into ASCII text file testDB.sql. You can perform restoration from the generated testDB.sql in a simple way as follows − $sqlite3 testDB.db < testDB.sql At this moment your database is empty, so you can try above two procedures once you have few tables and data in your database. For now, let”s proceed to the next chapter. Print Page Previous Next Advertisements ”;

SQLite – Data Type ”; Previous Next SQLite data type is an attribute that specifies the type of data of any object. Each column, variable and expression has related data type in SQLite. You would use these data types while creating your tables. SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. SQLite Storage Classes Each value stored in an SQLite database has one of the following storage classes − Sr.No. Storage Class & Description 1 NULL The value is a NULL value. 2 INTEGER The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. 3 REAL The value is a floating point value, stored as an 8-byte IEEE floating point number. 4 TEXT The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE) 5 BLOB The value is a blob of data, stored exactly as it was input. SQLite storage class is slightly more general than a datatype. The INTEGER storage class, for example, includes 6 different integer datatypes of different lengths. SQLite Affinity Type SQLite supports the concept of type affinity on columns. Any column can still store any type of data but the preferred storage class for a column is called its affinity. Each table column in an SQLite3 database is assigned one of the following type affinities − Sr.No. Affinity & Description 1 TEXT This column stores all data using storage classes NULL, TEXT or BLOB. 2 NUMERIC This column may contain values using all five storage classes. 3 INTEGER Behaves the same as a column with NUMERIC affinity, with an exception in a CAST expression. 4 REAL Behaves like a column with NUMERIC affinity except that it forces integer values into floating point representation. 5 NONE A column with affinity NONE does not prefer one storage class over another and no attempt is made to coerce data from one storage class into another. SQLite Affinity and Type Names Following table lists down various data type names which can be used while creating SQLite3 tables with the corresponding applied affinity. Data Type Affinity INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 INTEGER CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB TEXT BLOB no datatype specified NONE REAL DOUBLE DOUBLE PRECISION FLOAT REAL NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME NUMERIC Boolean Datatype SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true). Date and Time Datatype SQLite does not have a separate storage class for storing dates and/or times, but SQLite is capable of storing dates and times as TEXT, REAL or INTEGER values. Sr.No. Storage Class & Date Formate 1 TEXT A date in a format like “YYYY-MM-DD HH:MM:SS.SSS” 2 REAL The number of days since noon in Greenwich on November 24, 4714 B.C. 3 INTEGER The number of seconds since 1970-01-01 00:00:00 UTC You can choose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions. Print Page Previous Next Advertisements ”;

SQLite Tutorial PDF Version Quick Guide Resources Job Search Discussion SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain. This tutorial will give you a quick start with SQLite and make you comfortable with SQLite programming. Audience This tutorial has been prepared for beginners to help them understand the basic-to-advanced concepts related to SQLite Database Engine. Prerequisites Before you start practicing various types of examples given in this reference, we assume that you are already aware about what is a database, especially RDBMS and what is a computer programming language. Frequently Asked Questions about SQLite There are some very Frequently Asked Questions(FAQ) about SQLite, this section tries to answer them briefly. What is SQLite used for? SQLite is a database management system used to store and manage data in a structured format. It is often used in applications that need to save data locally on a device or computer, such as mobile apps, desktop software, and embedded systems. SQLite allows developers to create databases, organize data into tables, and perform operations like inserting, updating, and querying data. Why you should use SQLite? You should use SQLite because it is easy to use and does not require a separate server to run. It is perfect for applications that need to store data locally on a device, like mobile apps or desktop software. SQLite is also fast and reliable, making it a good choice for projects where simplicity and efficiency are important. Plus, since it is self-contained, you can easily distribute SQLite databases with your application without worrying about additional setup for users. What maintenance does SQLite require? SQLite requires minimal maintenance compared to other database management systems. Since it is self-contained and doesn”t require a separate server, you don”t need to worry about installing updates or managing server configurations. However, regular backups of your SQLite databases are recommended to prevent data loss in case of accidents or errors. Additionally, you may need to periodically optimize your databases to improve performance, especially if they become large or complex. Why is SQLite database locked? SQLite databases can become locked to prevent multiple processes from simultaneously making changes that could conflict with each other. When a process accesses an SQLite database, it acquires a lock to ensure exclusive access to the database while performing operations like writing data or executing transactions. This locking mechanism helps maintain data integrity and prevents data corruption by ensuring that only one process can modify the database at a time. Once the process completes its operations, it releases the lock, allowing other processes to access the database. What type of database is SQLite? SQLite is a type of relational database management system (RDBMS). It allows users to store, manage, and retrieve data in a structured format organized into tables with rows and columns. Users can define relationships between different tables, perform queries to retrieve specific data, and execute transactions to ensure data integrity. What language does SQLite use? SQLite is written in the C programming language. This means that the core functionality of SQLite, including its engine for managing databases, is implemented using C code. However, SQLite provides interfaces for many programming languages, allowing developers to interact with SQLite databases using languages like Python, Java, C++, and more. Is SQLite open source? Yes, SQLite is open source, which means its source code is freely available for anyone to view, modify, and distribute. This openness allows developers to inspect SQLite”s code, contribute improvements, and use it in their projects without restrictions. How fast is SQLite? SQLite is known for its speed and efficiency in handling database operations. It is designed to be lightweight and optimized for quick access to data, making it fast for reading and writing data. Because SQLite is self-contained and doesn”t require a separate server, there is minimal overhead in communication, resulting in faster response times for database queries and transactions. Who invented SQLite? SQLite was invented by D. Richard Hipp. He created SQLite to provide a simple, lightweight, and efficient database solution that could be embedded into software applications without requiring a separate server. Hipp designed SQLite to be fast, reliable, and easy to use, making it suitable for a wide range of projects and environments. How long does it take to learn Sqlite? The time it takes to learn SQLite can vary depending on your familiarity with databases and SQL (Structured Query Language). If you are completely new to databases, it might take a few days to understand the basics of SQLite and how to perform common operations like creating tables, inserting data, and querying information. With consistent practice and experimentation, you can become proficient in SQLite within a few weeks or months. However, mastering more advanced features and optimizing database performance could take longer and might require additional learning and experience. Where can I practice SQLite? The latest version of SQLite is 3.36.0. However, it is important to note that new versions may have been released. SQLite developers regularly release updates to improve performance, fix bugs, and add new features. To find the most current version of SQLite, you can visit the official SQLite website or check the release notes on their GitHub repository. Can I use SQLite in browser? Yes, you can use SQLite in a browser environment. There are various ways to do this, but one common method is to use JavaScript libraries or frameworks that provide SQLite functionality directly within a web browser. These libraries include a JavaScript implementation of SQLite or provide wrappers around SQLite that allow you to execute SQL queries and interact with SQLite databases directly from your web browser. This enables you to create web applications that use SQLite databases without needing a server-side database management system. However, it is important to note that browser-based SQLite implementations may have limitations compared to using SQLite in

SQLite – Java ”; Previous Next In this chapter, you will learn how to use SQLite in Java programs. Installation Before you start using SQLite in our Java programs, you need to make sure that you have SQLite JDBC Driver and Java set up on the machine. You can check Java tutorial for Java installation on your machine. Now, let us check how to set up SQLite JDBC driver. Download latest version of sqlite-jdbc-(VERSION).jar from sqlite-jdbc repository. Add downloaded jar file sqlite-jdbc-(VERSION).jar in your class path, or you can use it along with -classpath option as explained in the following examples. Following section assumes you have little knowledge about Java JDBC concepts. If you don”t, then it is suggested to spent half an hour with JDBC Tutorial to become comfortable with the concepts explained below. Connect to Database Following Java programs shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned. import java.sql.*; public class SQLiteJDBC { public static void main( String args[] ) { Connection c = null; try { Class.forName(“org.sqlite.JDBC”); c = DriverManager.getConnection(“jdbc:sqlite:test.db”); } catch ( Exception e ) { System.err.println( e.getClass().getName() + “: ” + e.getMessage() ); System.exit(0); } System.out.println(“Opened database successfully”); } } Now, let”s compile and run the above program to create our database test.db in the current directory. You can change your path as per your requirement. We are assuming the current version of JDBC driver sqlite-jdbc-3.7.2.jar is available in the current path. $javac SQLiteJDBC.java $java -classpath “.:sqlite-jdbc-3.7.2.jar” SQLiteJDBC Open database successfully If you are going to use Windows machine, then you can compile and run your code as follows − $javac SQLiteJDBC.java $java -classpath “.;sqlite-jdbc-3.7.2.jar” SQLiteJDBC Opened database successfully Create a Table Following Java program will be used to create a table in the previously created database. import java.sql.*; public class SQLiteJDBC { public static void main( String args[] ) { Connection c = null; Statement stmt = null; try { Class.forName(“org.sqlite.JDBC”); c = DriverManager.getConnection(“jdbc:sqlite:test.db”); System.out.println(“Opened database successfully”); stmt = c.createStatement(); String sql = “CREATE TABLE COMPANY ” + “(ID INT PRIMARY KEY NOT NULL,” + ” NAME TEXT NOT NULL, ” + ” AGE INT NOT NULL, ” + ” ADDRESS CHAR(50), ” + ” SALARY REAL)”; stmt.executeUpdate(sql); stmt.close(); c.close(); } catch ( Exception e ) { System.err.println( e.getClass().getName() + “: ” + e.getMessage() ); System.exit(0); } System.out.println(“Table created successfully”); } } When the above program is compiled and executed, it will create COMPANY table in your test.db and final listing of the file will be as follows − -rw-r–r–. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar -rw-r–r–. 1 root root 1506 May 8 05:43 SQLiteJDBC.class -rw-r–r–. 1 root root 832 May 8 05:42 SQLiteJDBC.java -rw-r–r–. 1 root root 3072 May 8 05:43 test.db INSERT Operation Following Java program shows how to create records in the COMPANY table created in above example. import java.sql.*; public class SQLiteJDBC { public static void main( String args[] ) { Connection c = null; Statement stmt = null; try { Class.forName(“org.sqlite.JDBC”); c = DriverManager.getConnection(“jdbc:sqlite:test.db”); c.setAutoCommit(false); System.out.println(“Opened database successfully”); stmt = c.createStatement(); String sql = “INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) ” + “VALUES (1, ”Paul”, 32, ”California”, 20000.00 );”; stmt.executeUpdate(sql); sql = “INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) ” + “VALUES (2, ”Allen”, 25, ”Texas”, 15000.00 );”; stmt.executeUpdate(sql); sql = “INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) ” + “VALUES (3, ”Teddy”, 23, ”Norway”, 20000.00 );”; stmt.executeUpdate(sql); sql = “INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) ” + “VALUES (4, ”Mark”, 25, ”Rich-Mond ”, 65000.00 );”; stmt.executeUpdate(sql); stmt.close(); c.commit(); c.close(); } catch ( Exception e ) { System.err.println( e.getClass().getName() + “: ” + e.getMessage() ); System.exit(0); } System.out.println(“Records created successfully”); } } When above program is compiled and executed, it will create given records in COMPANY table and will display following two line − Opened database successfully Records created successfully SELECT Operation Following Java program shows how to fetch and display records from the COMPANY table created in the above example. import java.sql.*; public class SQLiteJDBC { public static void main( String args[] ) { Connection c = null; Statement stmt = null; try { Class.forName(“org.sqlite.JDBC”); c = DriverManager.getConnection(“jdbc:sqlite:test.db”); c.setAutoCommit(false); System.out.println(“Opened database successfully”); stmt = c.createStatement(); ResultSet rs = stmt.executeQuery( “SELECT * FROM COMPANY;” ); while ( rs.next() ) { int id = rs.getInt(“id”); String name = rs.getString(“name”); int age = rs.getInt(“age”); String address = rs.getString(“address”); float salary = rs.getFloat(“salary”); System.out.println( “ID = ” + id ); System.out.println( “NAME = ” + name ); System.out.println( “AGE = ” + age ); System.out.println( “ADDRESS = ” + address ); System.out.println( “SALARY = ” + salary ); System.out.println(); } rs.close(); stmt.close(); c.close(); } catch ( Exception e ) { System.err.println( e.getClass().getName() + “: ” + e.getMessage() ); System.exit(0); } System.out.println(“Operation done successfully”); } } When the above program is compiled and executed, it will produce the following result. Opened database successfully ID = 1 NAME = Paul AGE = 32 ADDRESS = California SALARY = 20000.0 ID = 2 NAME = Allen AGE = 25 ADDRESS = Texas SALARY = 15000.0 ID = 3 NAME = Teddy AGE = 23 ADDRESS = Norway SALARY = 20000.0 ID = 4 NAME = Mark AGE = 25 ADDRESS = Rich-Mond SALARY = 65000.0 Operation done successfully UPDATE Operation Following Java code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table. import java.sql.*; public class SQLiteJDBC { public static void main( String args[] ) { Connection c = null; Statement stmt = null; try { Class.forName(“org.sqlite.JDBC”); c = DriverManager.getConnection(“jdbc:sqlite:test.db”); c.setAutoCommit(false); System.out.println(“Opened database successfully”); stmt = c.createStatement(); String sql = “UPDATE COMPANY set SALARY = 25000.00 where ID=1;”; stmt.executeUpdate(sql); c.commit(); ResultSet rs = stmt.executeQuery( “SELECT * FROM COMPANY;” ); while ( rs.next() ) { int id = rs.getInt(“id”); String name = rs.getString(“name”); int age = rs.getInt(“age”); String address = rs.getString(“address”); float salary = rs.getFloat(“salary”); System.out.println( “ID = ” + id ); System.out.println( “NAME = ” + name