Scribd
Upload
6 views

SQLite Tutorial

Uploaded by

Gomathy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
6 views

SQLite Tutorial

Uploaded by

Gomathy
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 8

SQLIte

Storage Description
Class

NULL It specifies that the value is a null value.

INTEGER It specifies the value is a signed integer, stored in 1, 2, 3,


4, 6, or 8 bytes depending on the magnitude of the value.

REAL It specifies the value is a floating point value, stored as an


8-byte IEEE floating point number.

text It specifies the value is a text string, stored using the


database encoding (utf-8, utf-16be or utf-16le)

BLOB It specifies the value is a blob of data, stored exactly as it


was input.

SQLite Create Database


In SQLite, the sqlite3 command is used to create a new database.

Syntax:

1. sqlite3 DatabaseName.db

SQLite Create Table


In SQLite, CREATE TABLE statement is used to create a new table. While
creating the table, we name that table and define its column and data
types of each column.

Syntax:

1. CREATE TABLE database_name.table_name(


2. column1 datatype PRIMARY KEY(one or more columns),
3. column2 datatype,
4. column3 datatype,
5. .....
6. columnN datatype,
7. );

o Add the downloaded jar file to your class path.


o You can now connect to the SQLite database using java.

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

public class Connect {


/**
* Connect to a sample database
*/
public static void connect() {
Connection conn = null;
try {
// db parameters
String url = "jdbc:sqlite:D:/venkat/student.db";
// create a connection to the database
conn = DriverManager.getConnection(url);

System.out.println("Connection to SQLite has been


established.");

} catch (SQLException e) {
System.out.println(e.getMessage());
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
}
}
/**
* @param args the command line arguments
*/
public static void main(String[] args) {
connect();
}
}

Select records

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SelectRecords {

private Connection connect() {


// SQLite connection string
String url = " jdbc:sqlite:D:/venkat/student.db ";
Connection conn = null;
try {
conn = DriverManager.getConnection(url);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}
public void selectAll(){
String sql = "SELECT * FROM employees";

try {
Connection conn = this.connect();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

// loop through the result set


while (rs.next()) {
System.out.println(rs.getInt("id") + "\t" +
rs.getString("name") + "\t" +
rs.getDouble("capacity"));
}
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
SelectRecords app = new SelectRecords();
app.selectAll();
}

Insert Record in the table


After the creation of the table, use the following code to insert some
records in the table. Create a new class "InsertRecords", having the
following code:

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

public class InsertRecords {

private Connection connect() {


// SQLite connection string
String url = " jdbc:sqlite:D:/venkat/student.db ";
Connection conn = null;
try {
conn = DriverManager.getConnection(url);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}

public void insert(String name, double capacity) {


String sql = "INSERT INTO student(name, capacity)
VALUES(?,?)";

try{
Connection conn = this.connect();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setDouble(2, capacity);
pstmt.executeUpdate();
} catch (SQLException e) {
System.out.println(e.getMessage());
}
}

public static void main(String[] args) {

InsertRecords app = new InsertRecords();


// insert three new rows
app.insert(24999, "Aryan", 20, “West street”, 2000.00);
app.insert(24998, "Bharathi", 20, “East street”, 2000.00);
app.insert(24997, "Christina", 20, “North street”, 2000.00);
}

You might also like