Java program to connect and insert data into a SQLite database

Aim

Write a java program to show connectivity with database using JDBC driver and perform different operations on the data in a database.

 

Theory

Database and DBMS

A database is a collection of related data. For example, a student database contains details about all the students. An employee database contains data about all the employees in a company and so on. Different types of databases are relational database, network database, hierarchical database, file-based database, object database, etc. In a relational database, data is organized in the form of rows and columns.

 

A DataBase Management System (DBMS) is a software which contains different tools for reading, updating, and deleting the data in the database and also supports other operations like backing up the data, assigning security roles, etc. Examples of a DBMS are Oracle, MYSQL, Microsoft SQL Server, Microsoft Access, Sybase, PostgreSQL, SQLite, etc.

 

SQLite

SQLite is an embedded relational database management system. It is self-contained, serverless, zero configuration and transactional SQL database engine.

 

SQLite is different from other SQL databases because unlike most other SQL databases, SQLite does not have a separate server process. It reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

 

SQLite commands are similar to SQL commands. There are three types of SQLite commands:

• DDL: Data Definition Language (Create, Alter, Drop)
• DML: Data Manipulation Language (Insert, Update, Delete)
• DQL: Data Query Language (Select)

 

We can create a database in SQLite with the command given below:

sqlite3 <database_name.db>

 

To verify whether the database was created or not use the following command:

.databases

 

The above command will display a list of available databases.

 

In case of multiple databases, we can select a certain database to execute our SQL commands. We select a database by attaching it using the following command:

attach database <database_name> as <alias_name>;

 

We can detach a database by using the following command:

detach database <alias_name>;

 

We can create a table in a database by using create command as shown below:

create table table_name( 
   column1 datatype PRIMARY KEY,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
); 

 

We can use data types like int, char, real, text, etc. in the above command.

 

To see the structure or description of a table (column names, data types, and other information), we can use the following commands:

.header on

.mode column

pragma table_info(<table_name>);

 

To display the list of tables in a database, we can use the following command:

.tables

 

We can drop a table by using the following command:

drop table <table_name>;

 

To select and display the data for only some of the columns in a table, use the following command:

select column1, column2, …, columnN from <table_name>;

 

To select data for all columns in a table, use the following command:

select *from <table_name>;

 

To insert a row into a table, we can use the following command:

insert into <table_name> [(column1, column2, column3,...columnN)] values (value1, value2, value3,...valueN);

 

The column names in the above command are optional if you specify the values for all the columns in a table.

 

To update values in one or more columns based on a condition, we can use the following command:

update <table_name>
set column1 = value1, column2 = value2...., columnN = valueN
where [condition];

 

To delete one or more rows from a table, we can use the following command:

delete from <table_name>
where [condition];

 

JDBC

JDBC stands for Java Database Connectivity. JDBC is a Java API to connect and execute queries on a database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to connect with the database. There are four types of JDBC drivers:

1. JDBC-ODBC Bridge Driver
2. Native Driver
3. Network Protocol Driver
4. Thin Driver

 

The java.sql package contains classes and interfaces for JDBC API. A list of popular interfaces of JDBC API are given below:

• Driver
• Connection
• Statement
• PreparedStatement
• CallableStatement
• ResultSet
• ResultSetMetaData
• DatabaseMetaData
• RowSet

 

A list of popular classes of JDBC API are given below:

• DriverManager
• Blob
• Clob
• Types

 

The thin driver converts JDBC calls directly into the vendor-specific database protocol. That is why it is known as thin driver. It is fully written in Java language.

 

Advantages:

• Better performance than all other drivers.
• No software is required at client side or server side.

 

Disadvantage:

• Drivers depend on the Database.

 

Program

Java program to connect and insert data into a SQLite database using a JDBC driver is given below:

import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class RegFormDB extends JFrame implements ActionListener {
	JPanel p1;
	JLabel lblStudentRegdno;
	JTextField txtStudentRegdno;
	
	JPanel p2;
	JLabel lblStudentName;
	JTextField txtStudentName;
	
	JPanel p3;
	JLabel lblStudentMobile;
	JTextField txtStudentMobile;
	
	JPanel p4;
	JLabel lblCourse;
	JRadioButton rbBtech;
	JRadioButton rbMtech;
	JRadioButton rbOtherCourse;
	ButtonGroup bg1;
	
	JPanel p5;
	JLabel lblBranch;
	JRadioButton rbECE;
	JRadioButton rbEEE;
	JRadioButton rbCSE;
	JRadioButton rbIT;
	JRadioButton rbOtherBranches;
	ButtonGroup bg2;
	
	JPanel p6;
	JLabel lblSection;
	JComboBox jcb;
	String section[] = {"1","2","3","4","5","6","7","8"};
	
	JPanel p7;
	JButton btnSubmit;
	JButton btnClear;
	
	JPanel p8;
	JLabel lblStatus;
	
	static Connection conn;
	
	RegFormDB(){
		p1 = new JPanel();
		lblStudentRegdno = new JLabel("Student Regd.No.:");
		txtStudentRegdno = new JTextField(30);
		p1.add(lblStudentRegdno);
		p1.add(txtStudentRegdno);
		
		p2 = new JPanel();
		lblStudentName = new JLabel("Student Name:");
		txtStudentName = new JTextField(30);
		p2.add(lblStudentName);
		p2.add(txtStudentName);
		
		p3 = new JPanel();
		lblStudentMobile = new JLabel("Student Mobile No.:");
		txtStudentMobile = new JTextField(30);
		p3.add(lblStudentMobile);
		p3.add(txtStudentMobile);
		
		p4 = new JPanel();
		lblCourse = new JLabel("Course:");
		rbBtech = new JRadioButton("B.Tech.");
		rbMtech = new JRadioButton("M.Tech.");
		rbOtherCourse = new JRadioButton("Others");
		bg1 = new ButtonGroup();
		bg1.add(rbBtech);
		bg1.add(rbMtech);
		bg1.add(rbOtherCourse);
		p4.add(lblCourse);
		p4.add(rbBtech);
		p4.add(rbMtech);
		p4.add(rbOtherCourse);
		
		p5 = new JPanel();
		lblBranch = new JLabel("Branch:");
		rbECE = new JRadioButton("ECE");
		rbEEE = new JRadioButton("EEE");
		rbCSE = new JRadioButton("CSE");
		rbIT = new JRadioButton("IT");
		rbOtherBranches = new JRadioButton("Others");
		bg2 = new ButtonGroup();
		bg2.add(rbECE);
		bg2.add(rbEEE);
		bg2.add(rbCSE);
		bg2.add(rbIT);
		bg2.add(rbOtherBranches);
		p5.add(lblBranch);
		p5.add(rbECE);
		p5.add(rbEEE);
		p5.add(rbCSE);
		p5.add(rbIT);
		p5.add(rbOtherBranches);
		
		p6 = new JPanel();
		lblSection = new JLabel("Section: ");
		jcb = new JComboBox(section);
		p6.add(lblSection);
		p6.add(jcb);
		
		p7 = new JPanel();
		btnSubmit = new JButton("Submit");
		btnClear = new JButton("Clear");
		p7.add(btnSubmit);
		p7.add(btnClear);
		btnSubmit.addActionListener(this);
		btnClear.addActionListener(this);
		
		p8 = new JPanel();
		lblStatus = new JLabel();
		p8.add(lblStatus);
		
		add(p1);
		add(p2);
		add(p3);
		add(p4);
		add(p5);
		add(p6);
		add(p7);
		add(p8);
		setSize(500, 600);
		setTitle("Registration Form");
		setLayout(new GridLayout(10,1));
		setVisible(true);
		setResizable(false);
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	}
	

	public static void main(String[] args){
		RegFormDB f = new RegFormDB();
		connect();
	}

	@Override
	public void actionPerformed(ActionEvent e){
		String course = "";
		String branch = "";
		if(e.getActionCommand() == "Submit") {
			String status = "<html><body>Regd.No. = " + txtStudentRegdno.getText() + 
					" Name = " + txtStudentName.getText() +
					" Mobile No.= " + txtStudentMobile.getText();
			status += " <br>Course = ";
			if(rbBtech.isSelected()) {
				status += rbBtech.getText();
				course = rbBtech.getText();
			}				
			else if(rbMtech.isSelected()) {
				status += rbMtech.getText();
				course = rbMtech.getText();
			}				
			else {
				status += rbOtherCourse.getText();
				course = rbOtherCourse.getText();
			}				
			status += " Branch = ";
			if(rbECE.isSelected()) {
				status += rbECE.getText();
				branch = rbECE.getText();
			}				
			else if(rbEEE.isSelected())	{
				status += rbEEE.getText();
				branch = rbEEE.getText();
			}				
			else if(rbCSE.isSelected()) {
				status += rbCSE.getText();
				branch = rbCSE.getText();
			}				
			else if(rbIT.isSelected()) {
				status += rbIT.getText();
				branch = rbIT.getText();
			}				
			else {
				status += rbOtherBranches.getText();
				branch = rbOtherBranches.getText();
			}				
			status += " Section = " + jcb.getSelectedItem() + "</html></body>";
			lblStatus.setText(status);
			
			try
			{
				String sql = "insert into data values(?,?,?,?,?,?)";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1,Integer.parseInt(txtStudentRegdno.getText()));
				pstmt.setString(2, txtStudentName.getText());
				pstmt.setString(3, txtStudentMobile.getText());
				pstmt.setString(4, course);
				pstmt.setString(5, branch);
				pstmt.setInt(6, Integer.parseInt(jcb.getSelectedItem().toString()));
				pstmt.executeUpdate();
				System.out.println("Data is inserted.");
			}
			catch(SQLException ex)
			{
				ex.printStackTrace();
			}
		}
		else {
			lblStatus.setText("");
		}
		
	}

	public static void connect()
	{
		try {
			conn = null;
			String url = "jdbc:sqlite:D:/Soft/Sqlite/Students.db";
			conn = DriverManager.getConnection(url);
			System.out.println("Connected established to database");
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
	}
}

 

Input and Output

GUI Output

 

Output in SQLite DBMS

 

Related Links

• https://www.sqlite.org/download.html
• https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc