×

Welcome to TagMyCode

Please login or create account to add a snippet.
 

Friend: Implementation JDBC SQLITE using java.sql.PreparedStatement

0
0
 
0
Language: Java
Posted by: anti laila
Added: May 23, 2022 3:49 PM
Views: 16
Tags: dao friend java.sql.connection java.sql.preparedstatement 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.PreparedStatement;
  6. import java.sql.ResultSet;
  7. import java.sql.SQLException;
  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.PreparedStatement 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.  
  27. public class FriendJDBCSQLiteDemo2 {
  28.     public static void main(String[] args) {
  29.         FriendJDBCSQLitePreparedStatementTemplate db = new FriendJDBCSQLitePreparedStatementTemplate();
  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.  
  73. //the POJO class : Friend
  74. class Friend{
  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. /**
  128. * class to implement SQLite JDBC
  129. * please download sqlite-jdbc
  130. * or add dependencies in maven's pom.xml, see
  131. * https://mvnrepository.com/artifact/org.xerial/sqlite-jdbc/3.36.0.3
  132. *
  133. */
  134.  
  135. //class SQLite JDBC Implementation
  136. class FriendJDBCSQLitePreparedStatementTemplate implements FriendDao{
  137.     private Connection con;
  138.     private final String DRIVER = "org.sqlite.JDBC";
  139.     private final String URL = "jdbc:sqlite:antilaila8.db"; //database is antilaila8.db
  140.  
  141.     //sql to make friend table
  142.     private final String sqlFriendTable = "create table if not exists friend("
  143.             + "id integer primary key autoincrement,"
  144.             + "name text,"
  145.             + "address text,"
  146.             + "phone text)";
  147.    
  148.     public FriendJDBCSQLitePreparedStatementTemplate() {
  149.         try {
  150.             Class.forName(DRIVER);
  151.             con = DriverManager.getConnection(URL);
  152.             createTable(sqlFriendTable);
  153.         } catch (ClassNotFoundException | SQLException ex) {
  154.             Logger.getLogger(FriendJDBCSQLitePreparedStatementTemplate.class.getName()).log(Level.SEVERE, null, ex);
  155.         }
  156.     }
  157.  
  158.     @Override
  159.     public void insert(Friend f) {
  160.         String sql = "insert into friend values(?,?,?,?)";
  161.         try(PreparedStatement pst = con.prepareStatement(sql)){
  162.             if(f.getId()==0){
  163.                 pst.setNull(1, f.getId());
  164.                 pst.setString(2, f.getName());
  165.                 pst.setString(3, f.getAddress());
  166.                 pst.setString(4, f.getPhone());
  167.             }
  168.             else{
  169.                 pst.setInt(1, f.getId());
  170.                 pst.setString(2, f.getName());
  171.                 pst.setString(3, f.getAddress());
  172.                 pst.setString(4, f.getPhone());
  173.             }
  174.             pst.executeUpdate();
  175.         } catch (SQLException ex) {
  176.             Logger.getLogger(FriendJDBCSQLitePreparedStatementTemplate.class.getName()).log(Level.SEVERE, null, ex);
  177.         }
  178.     }
  179.  
  180.     @Override
  181.     public void update(Friend f) {
  182.         String sql = "update friend set name=?, address=?, phone=? where id=?";
  183.         try(PreparedStatement pst = con.prepareStatement(sql)){
  184.             pst.setString(1, f.getName());
  185.             pst.setString(2, f.getAddress());
  186.             pst.setString(3, f.getPhone());
  187.             pst.setInt(4, f.getId());
  188.             pst.executeUpdate();
  189.         } catch (SQLException ex) {
  190.             Logger.getLogger(FriendJDBCSQLitePreparedStatementTemplate.class.getName()).log(Level.SEVERE, null, ex);
  191.         }
  192.     }
  193.  
  194.     @Override
  195.     public void delete(int id) {
  196.         String sql = "delete from friend where id=?";
  197.         try(PreparedStatement pst = con.prepareStatement(sql)){
  198.             pst.setInt(1, id);
  199.             pst.executeUpdate();
  200.         } catch (SQLException ex) {
  201.             Logger.getLogger(FriendJDBCSQLitePreparedStatementTemplate.class.getName()).log(Level.SEVERE, null, ex.getMessage());
  202.         }
  203.     }
  204.  
  205.     @Override
  206.     public void deleteAll() {
  207.         try(PreparedStatement pst = con.prepareStatement("delete from friend")){
  208.             pst.executeUpdate();
  209.         } catch (SQLException ex) {
  210.             Logger.getLogger(FriendJDBCSQLitePreparedStatementTemplate.class.getName()).log(Level.SEVERE, null, ex);
  211.         }
  212.        
  213.         //reset autoincrement to 0 from friend table
  214.         try(PreparedStatement pst = con.prepareStatement("update sqlite_sequence set seq = 0 where name = 'friend'")){
  215.             pst.executeUpdate();
  216.         } catch (SQLException ex) {
  217.             Logger.getLogger(FriendJDBCSQLitePreparedStatementTemplate.class.getName()).log(Level.SEVERE, null, ex);
  218.         }
  219.     }
  220.  
  221.     @Override
  222.     public void createTable(String sql) {
  223.         try(PreparedStatement pst = con.prepareStatement(sql)){
  224.             pst.executeUpdate();
  225.         } catch (SQLException ex) {
  226.             Logger.getLogger(FriendJDBCSQLitePreparedStatementTemplate.class.getName()).log(Level.SEVERE, null, ex);
  227.         }
  228.     }
  229.  
  230.     @Override
  231.     public Friend getAFriendById(int id) {
  232.         Friend result = null;
  233.         String sql = "select * from friend where id=?";
  234.         try(PreparedStatement pst = con.prepareStatement(sql)){
  235.             pst.setInt(1, id);
  236.             try(ResultSet rs = pst.executeQuery()){
  237.                 if(rs.first())
  238.                     result = new Friend(
  239.                             rs.getInt("id"),
  240.                             rs.getString("name"),
  241.                             rs.getString("address"),
  242.                             rs.getString("phone")
  243.                     );
  244.             }
  245.         } catch (SQLException ex) {
  246.             Logger.getLogger(FriendJDBCSQLitePreparedStatementTemplate.class.getName()).log(Level.SEVERE, null, ex);
  247.         }
  248.         return result;
  249.     }
  250.  
  251.     @Override
  252.     public List<Friend> getAllFriends() {
  253.         List<Friend> result = new ArrayList<>();
  254.         try(PreparedStatement pst = con.prepareStatement("select * from friend");
  255.                 ResultSet rs = pst.executeQuery()){
  256.             while(rs.next())
  257.                 result.add(new Friend(
  258.                         rs.getInt("id"),
  259.                         rs.getString("name"),
  260.                         rs.getString("address"),
  261.                         rs.getString("phone")
  262.                 ));
  263.            
  264.         } catch (SQLException ex) {
  265.             Logger.getLogger(FriendJDBCSQLitePreparedStatementTemplate.class.getName()).log(Level.SEVERE, null, ex);
  266.         }
  267.         return result;
  268.     }
  269. }

Write a comment

To post comments, please log in or register.