Sqlite dan Java

Sebelum kita menggunakan sqlite pada program java, kita harus mempunyai SQlite JDBC driver yang bisa didownload bitbucket.org/xerial/sqlite-jdbc/downloads. Setelah kita download SQLite JDBC, kita import file .jar ke folder library project.

Kalo sudah langsung kita mulai dengan membuat koneksi ke database sqlite. Buat class java dengan nama “TestSqlite” atau apa aja terserah.

1. Koneksi Database.

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

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

/**
 *
 * @author fadlur
 */
public class TestSqlite {
    static Connection c = null;
    static Statement stmt = null;
    public static void main(String[] args){
        try{
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
        }catch(SQLException e){
            System.err.println(e.getClass().getName() + " : " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Openned database successfully");
    }
}

2. Create Table

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

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

/**
 *
 * @author fadlur
 */
public class TestSqlite {
    static Connection c = null;
    static Statement stmt = null;
    public static void main(String[] args){

        try{
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
            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(SQLException e){
            System.err.println(e.getClass().getName() + " : " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Table created successfully");
    }
}

3. Insert Data

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

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

/**
 *
 * @author fadlur
 */
public class TestSqlite {
    static Connection c = null;
    static Statement stmt = null;
    public static void main(String[] args){

        try{
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
            c.setAutoCommit(false);
            stmt = c.createStatement();
            String sql = "INSERT into company values (1,'fadlur',20,'kudus',200000);";
            sql = "INSERT into company values (2,'rohman',22,'jepara',200000);";
            sql = "INSERT into company values (3,'rohim',24,'pati',200000);";
            stmt.executeUpdate(sql);
            c.commit();
            stmt.close();
            c.close();
        }catch(SQLException e){
            System.err.println(e.getClass().getName() + " : " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Data berhasil disimpan");
    }
}

4. Select Data

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

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

/**
 *
 * @author fadlur
 */
public class TestSqlite {
    static Connection c = null;
    static Statement stmt = null;
    public static void main(String[] args){

        try{
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
            c.setAutoCommit(false);
            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("Nama = "+name);
                System.out.println("Age = "+age);
                System.out.println("Address = "+address);
                System.out.println("Salary = "+salary);
            }
            rs.close();
            stmt.close();
            c.close();
        }catch(SQLException e){
            System.err.println(e.getClass().getName() + " : " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Select Data berhasil");
    }
}

5. Update Data

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

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

/**
 *
 * @author fadlur
 */
public class TestSqlite {
    static Connection c = null;
    static Statement stmt = null;
    public static void main(String[] args){

        try{
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
            c.setAutoCommit(false);
            stmt = c.createStatement();
            String sql = "UPDATE company set name = 'abdurrohman' where id=3;";
            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("Nama = "+name);
                System.out.println("Age = "+age);
                System.out.println("Address = "+address);
                System.out.println("Salary = "+salary);
            }
            rs.close();
            stmt.close();
            c.close();
        }catch(SQLException e){
            System.err.println(e.getClass().getName() + " : " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Update Data berhasil");
    }
}

6. Delete Data

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

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

/**
 *
 * @author fadlur
 */
public class TestSqlite {
    static Connection c = null;
    static Statement stmt = null;
    public static void main(String[] args){

        try{
            c = DriverManager.getConnection("jdbc:sqlite:test.db");
            c.setAutoCommit(false);
            stmt = c.createStatement();
            String sql = "DELETE from company 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("Nama = "+name);
                System.out.println("Age = "+age);
                System.out.println("Address = "+address);
                System.out.println("Salary = "+salary);
            }
            rs.close();
            stmt.close();
            c.close();
        }catch(SQLException e){
            System.err.println(e.getClass().getName() + " : " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Delete Data berhasil");
    }
}

Selamat mencoba semoga manfaat. ๐Ÿ˜€

Tinggalkan Balasan

This site uses Akismet to reduce spam. Learn how your comment data is processed.