×

Welcome to TagMyCode

Please login or create account to add a snippet.
 

Friend: Implementation JDBC SQLITE using java.sql.Statement

0
0
 
0
Language: Java
Posted by: anti laila
Added: May 23, 2022 2:56 PM
Modified: May 23, 2022 3:48 PM
Views: 13
Tags: dao friend java.sql.connection java.sql.statement jdbc model pojo sqlite
  1. package id.ac.poliban.roman.jdbc.sqlite;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.ResultSet;
  6. import java.sql.SQLException;
  7. import java.sql.Statement;
  8. import java.util.ArrayList;
  9. import java.util.List;
  10. import java.util.logging.Level;
  11. import java.util.logging.Logger;
  12.  
  13. /**
  14.  * This code is used to demonstrate the use of JDBC SQLite (sqlite-jdbc version
  15.  * 3.36.0.3) in manipulating sqlite tables. This code uses the POJO Friend
  16.  * class, Interface Friend and a class that implements JDBC to manipulate the
  17.  * the sqlite database. In this code we only use the java.sql.Statement class to
  18.  * manipulate the data. See the next tutorial which will use the
  19.  * java.sql.PreparedStatement class
  20.  *
  21.  * remember this code using jdk 8 and above
  22.  *
  23.  * @author antilaila8
  24.  *
  25.  */
  26. public class FriendJDBCSQLiteDemo {
  27.  
  28.     public static void main(String[] args) {
  29.         FriendJDBCSQLiteStatementTemplate db = new FriendJDBCSQLiteStatementTemplate();
  30.        
  31.         //delete all record
  32.         //and reset autoincrement of friend table to 0
  33.         db.deleteAll();
  34.        
  35.         //create list of Friend for insert
  36.         List<Friend> lsfriend1 = new ArrayList<>();
  37.         lsfriend1.add(new Friend("Ullah", "BJM", "0822511215"));
  38.         lsfriend1.add(new Friend("Eson", "BJM", "0822511216"));
  39.         lsfriend1.add(new Friend("Hing", "BJM", "0822511219"));
  40.         lsfriend1.add(new Friend("Johny Fren", "BJM", "0822511210"));
  41.         lsfriend1.add(new Friend("Acuna", "BJM", "0822511217"));
  42.         lsfriend1.add(new Friend("Fahmi Malaya", "BJM", "0822511213"));
  43.        
  44.         //insert list to table
  45.         lsfriend1.forEach(f->db.insert(f));
  46.        
  47.         //read data
  48.         System.out.println("read data after insert:");
  49.         db.getAllFriends().forEach(System.out::println);
  50.        
  51.         //create list of Friend for update
  52.         List<Friend> lsFriend2 = new ArrayList<>();
  53.         lsFriend2.add(new Friend(3, "Ullah", "BJM", "0822511215"));
  54.         lsFriend2.add(new Friend(6,"Eson", "BJM", "0822511216"));
  55.         lsFriend2.add(new Friend(1, "Hing", "BJM", "0822511219"));
  56.         lsFriend2.add(new Friend(2, "Johny Fren", "BJM", "0822511210"));
  57.         lsFriend2.add(new Friend(5,"Acuna", "BJM", "0822511217"));
  58.         lsFriend2.add(new Friend(4, "Fahmi Malaya", "BJM", "0822511213"));
  59.        
  60.         //update list to table
  61.         lsFriend2.forEach(f->db.update(f));
  62.        
  63.         System.out.println("\n\nread data after update");
  64.         db.getAllFriends().forEach(System.out::println);
  65.        
  66.         db.delete(3);
  67.         System.out.println("\n\nread data after delete id:3");
  68.         db.getAllFriends().forEach(System.out::println);
  69.     }
  70. }
  71.  
  72. //the POJO class : Friend
  73. class Friend {
  74.  
  75.     private int id;
  76.     private String name;
  77.     private String address;
  78.     private String phone;
  79.  
  80.     public Friend(int id, String name, String address, String phone) {
  81.         this.id = id;
  82.         this.name = name;
  83.         this.address = address;
  84.         this.phone = phone;
  85.     }
  86.  
  87.     public Friend(String name, String address, String phone) {
  88.         this.name = name;
  89.         this.address = address;
  90.         this.phone = phone;
  91.     }
  92.  
  93.     public int getId() {
  94.         return id;
  95.     }
  96.  
  97.     public String getName() {
  98.         return name;
  99.     }
  100.  
  101.     public String getAddress() {
  102.         return address;
  103.     }
  104.  
  105.     public String getPhone() {
  106.         return phone;
  107.     }
  108.  
  109.     @Override
  110.     public String toString() {
  111.         return String.format("%-3d %-20s %-30s %-14s", getId(), getName(), getAddress(), getPhone());
  112.     }
  113. }
  114.  
  115. //FriendDao interface
  116. interface FriendDao {
  117.     void insert(Friend f);
  118.     void update(Friend f);
  119.     void delete(int id);
  120.     void deleteAll();
  121.     void createTable(String sql);
  122.     Friend getAFriendById(int id);
  123.     List<Friend> getAllFriends();
  124. }
  125.  
  126. /**
  127. * class to implement SQLite JDBC
  128. * please download sqlite-jdbc
  129. * or add dependencies in maven's pom.xml, see
  130. * https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc/3.36.0.3
  131. *
  132. */
  133.  
  134. class FriendJDBCSQLiteStatementTemplate implements FriendDao {
  135.    
  136.     private Connection con;
  137.     private final String DRIVER = "org.sqlite.JDBC";
  138.     private final String URL = "jdbc:sqlite:antilaila8.db"; //database is antilaila8.db
  139.    
  140.     //sql to make friend table
  141.     private final String sqlFriendTable = "create table if not exists friend("
  142.             + "id integer primary key autoincrement,"
  143.             + "name text,"
  144.             + "address text,"
  145.             + "phone text)";
  146.  
  147.  
  148.     //constructor
  149.     public FriendJDBCSQLiteStatementTemplate() {
  150.         try {
  151.             Class.forName(DRIVER);
  152.             con = DriverManager.getConnection(URL);
  153.            
  154.             //make a table friend
  155.             createTable(sqlFriendTable);
  156.            
  157.         } catch (ClassNotFoundException | SQLException ex) {
  158.             Logger.getLogger(FriendJDBCSQLiteStatementTemplate.class.getName()).log(Level.SEVERE, null, ex.getMessage());
  159.         }
  160.     }
  161.  
  162.     @Override
  163.     public void createTable(String sql) {
  164.         try ( Statement st = con.createStatement()) {
  165.             st.executeUpdate(sql);
  166.         } catch (SQLException ex) {
  167.             Logger.getLogger(FriendJDBCSQLiteStatementTemplate.class.getName()).log(Level.SEVERE, null, ex.getMessage());
  168.         }
  169.     }
  170.  
  171.     @Override
  172.     public void insert(Friend f) {
  173.         String sql;
  174.         if (f.getId() == 0) {//when id is null
  175.             sql = String.format("insert into friend values(null, '%s', '%s', '%s')", f.getName(), f.getAddress(), f.getPhone());
  176.         } else {
  177.             sql = String.format("insert into friend values('%d', '%s', '%s', '%s')", f.getId(), f.getName(), f.getAddress(), f.getPhone());
  178.         }
  179.  
  180.         try ( Statement st = con.createStatement()) {
  181.             st.executeUpdate(sql);
  182.         } catch (SQLException ex) {
  183.             Logger.getLogger(FriendJDBCSQLiteStatementTemplate.class.getName()).log(Level.SEVERE, null, ex.getMessage());
  184.         }
  185.     }
  186.  
  187.     @Override
  188.     public void update(Friend f) {
  189.         String sql = String.format("update friend set name='%s', address='%s', phone = '%s' where id = '%d'",
  190.                 f.getName(), f.getAddress(), f.getPhone(), f.getId());
  191.  
  192.         try ( Statement st = con.createStatement()) {
  193.             st.executeUpdate(sql);
  194.         } catch (SQLException ex) {
  195.             Logger.getLogger(FriendJDBCSQLiteStatementTemplate.class.getName()).log(Level.SEVERE, null, ex.getMessage());
  196.         }
  197.     }
  198.  
  199.     @Override
  200.     public void delete(int id) {
  201.         String sql = String.format("delete from friend where id = '%d'", id);
  202.         try ( Statement st = con.createStatement()) {
  203.             st.executeUpdate(sql);
  204.         } catch (SQLException ex) {
  205.             Logger.getLogger(FriendJDBCSQLiteStatementTemplate.class.getName()).log(Level.SEVERE, null, ex.getMessage());
  206.         }
  207.     }
  208.  
  209.     @Override
  210.     public void deleteAll() {
  211.         try ( Statement st = con.createStatement()) {
  212.             st.executeUpdate("delete from friend");
  213.             //reset autoincrement of friend table to 0
  214.             st.executeUpdate("update sqlite_sequence set seq = 0 where name = 'friend'");
  215.         } catch (SQLException ex) {
  216.             Logger.getLogger(FriendJDBCSQLiteStatementTemplate.class.getName()).log(Level.SEVERE, null, ex.getMessage());
  217.         }
  218.     }
  219.  
  220.     @Override
  221.     public Friend getAFriendById(int id) {
  222.         Friend result = null;
  223.         String sql = String.format("select * from friend where id = '%d'", id);
  224.         try ( Statement st = con.createStatement();  ResultSet rs = st.executeQuery(sql)) {
  225.             if (rs.first()) {
  226.                 result = new Friend(
  227.                         rs.getInt("id"),
  228.                         rs.getString("name"),
  229.                         rs.getString("address"),
  230.                         rs.getString("phone")
  231.                 );
  232.             }
  233.  
  234.         } catch (SQLException ex) {
  235.             Logger.getLogger(FriendJDBCSQLiteStatementTemplate.class.getName()).log(Level.SEVERE, null, ex.getMessage());
  236.         }
  237.  
  238.         return result;
  239.     }
  240.  
  241.     @Override
  242.     public List<Friend> getAllFriends() {
  243.         List<Friend> result = new ArrayList<>();
  244.         String sql = "select * from friend";
  245.         try ( Statement st = con.createStatement(); ResultSet rs = st.executeQuery(sql)) {
  246.             while(rs.next())
  247.                 result.add(new Friend(
  248.                         rs.getInt("id"),
  249.                         rs.getString("name"),
  250.                         rs.getString("address"),
  251.                         rs.getString("phone")
  252.                 ));
  253.         } catch (SQLException ex) {
  254.             Logger.getLogger(FriendJDBCSQLiteStatementTemplate.class.getName()).log(Level.SEVERE, null, ex.getMessage());
  255.         }
  256.         return result;
  257.     }
  258. }

Write a comment

To post comments, please log in or register.