×

Welcome to TagMyCode

Please login or create account to add a snippet.
 

Friend: Implementation JDBC MySQL using java.sql.Statement

0
0
 
0
Language: Java
Posted by: anti laila
Added: May 25, 2022 5:38 AM
Views: 13
Tags: dao friend java.sql.connection java.sql.statement jdbc mysql pojo
  1. package id.ac.poliban.roman.jdbc.preparedstatement;
  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.  
  11. /**
  12.  * This code is used to demonstrate the use of JDBC class for mysql(mysql-connector-java)
  13.  * in manipulating sqlite tables. This code uses the POJO Friend
  14.  * class, Interface Friend and a class that implements JDBC to manipulate the
  15.  * the mysql database. In this code we only use the java.sql.Statement class to
  16.  * manipulate the data.
  17.  *
  18.  * remember this code using jdk 8 and above
  19.  *
  20.  * @author antilaila8
  21.  *
  22.  */
  23.  
  24. /**
  25.  * Notes :
  26.  * This tutorial using mysql database with a database that called romandb
  27.  * (if you don't have that database  please create it) and
  28.  * a table that called friend. The structure friend table is :
  29.  *  
  30.  *  +---------+--------------+------+-----+---------+----------------+
  31.  * | Field   | Type         | Null | Key | Default | Extra          |
  32.  * +---------+--------------+------+-----+---------+----------------+
  33.  * | id      | int(11)      | NO   | PRI | NULL    | auto_increment |
  34.  * | name    | varchar(50)  | NO   |     |         |                |
  35.  * | address | varchar(100) | NO   |     |         |                |
  36.  * | phone   | varchar(14)  | YES  |     | NULL    |                |
  37.  * +---------+--------------+------+-----+---------+----------------+
  38.  *
  39.  * if you don't have a table, please execute script sql below to create friend table :
  40.  *
  41.  *      create table if not exists friend(
  42.  *          id int not null auto_increment primary key,
  43.  *          name varchar(20) not null,
  44.  *          address varchar(30) not null,
  45.  *          phone varchar(14) null)
  46.  *
  47.  *      using method execSQL(sql) from object of FriendJDBCMySQLPreparedStatementTemplate
  48.  *
  49.  */
  50.  
  51.  
  52. public class FriendJDBCMySQLUsingStatementDemo {
  53.     public static void main(String[] args) {
  54.         String url = "jdbc:mysql://localhost:3306/romandb?user=root&password=1234";
  55.         FriendJDBCMySQLStatementTemplate db = new FriendJDBCMySQLStatementTemplate(url);
  56.        
  57.         //delete all record &
  58.         //set auto_increment to 0
  59.         db.deleteAll();
  60.        
  61.         //create list of Friend for insert
  62.         List<Friend> lsfriend1 = new ArrayList<>();
  63.         lsfriend1.add(new Friend("Ullah", "BJM", "0822511215"));
  64.         lsfriend1.add(new Friend("Eson", "BJM", "0822511216"));
  65.         lsfriend1.add(new Friend("Hing", "BJM", "0822511219"));
  66.         lsfriend1.add(new Friend("Johny Fren", "BJM", "0822511210"));
  67.         lsfriend1.add(new Friend("Acuna", "BJM", "0822511217"));
  68.         lsfriend1.add(new Friend("Fahmi Malaya", "BJM", "0822511213"));
  69.        
  70.         //insert list to table
  71.         lsfriend1.forEach(f->db.insert(f));
  72.        
  73.         //read data
  74.         System.out.println("read data after insert:");
  75.         db.getAllFriends().forEach(System.out::println);
  76.        
  77.         //create list of Friend for update
  78.         List<Friend> lsFriend2 = new ArrayList<>();
  79.         lsFriend2.add(new Friend(3, "Ullah", "BJM", "0822511215"));
  80.         lsFriend2.add(new Friend(6,"Eson", "BJM", "0822511216"));
  81.         lsFriend2.add(new Friend(1, "Hing", "BJM", "0822511219"));
  82.         lsFriend2.add(new Friend(2, "Johny Fren", "BJM", "0822511210"));
  83.         lsFriend2.add(new Friend(5,"Acuna", "BJM", "0822511217"));
  84.         lsFriend2.add(new Friend(4, "Fahmi Malaya", "BJM", "0822511213"));
  85.        
  86.         //update list to table
  87.         lsFriend2.forEach(f->db.update(f));
  88.        
  89.         System.out.println("\n\nread data after update");
  90.         db.getAllFriends().forEach(System.out::println);
  91.        
  92.         db.delete(3);
  93.         System.out.println("\n\nread data after delete id:3");
  94.         db.getAllFriends().forEach(System.out::println);
  95.     }
  96.    
  97. }
  98.  
  99. //the POJO class : Friend
  100. class Friend {
  101.  
  102.     private int id;
  103.     private String name;
  104.     private String address;
  105.     private String phone;
  106.  
  107.     public Friend(int id, String name, String address, String phone) {
  108.         this.id = id;
  109.         this.name = name;
  110.         this.address = address;
  111.         this.phone = phone;
  112.     }
  113.  
  114.     public Friend(String name, String address, String phone) {
  115.         this.name = name;
  116.         this.address = address;
  117.         this.phone = phone;
  118.     }
  119.  
  120.     public int getId() {
  121.         return id;
  122.     }
  123.  
  124.     public String getName() {
  125.         return name;
  126.     }
  127.  
  128.     public String getAddress() {
  129.         return address;
  130.     }
  131.  
  132.     public String getPhone() {
  133.         return phone;
  134.     }
  135.  
  136.     @Override
  137.     public String toString() {
  138.         return String.format("%-3d %-20s %-30s %-14s", getId(), getName(), getAddress(), getPhone());
  139.     }
  140. }
  141.  
  142. //FriendDao interface
  143. interface FriendDao {
  144.     void insert(Friend f);
  145.     void update(Friend f);
  146.     void delete(int id);
  147.     void deleteAll();
  148.     void execSQL(String sql);
  149.     Friend getAFriendById(int id);
  150.     List<Friend> getAllFriends();
  151. }
  152.  
  153. /**
  154. * class to implement JDBC class
  155. * please download mysql-connector-java
  156. * or add dependencies in maven's pom.xml, see
  157. * https://mvnrepository.com/artifact/mysql/mysql-connector-java
  158. *
  159. */
  160.  
  161. class FriendJDBCMySQLStatementTemplate implements FriendDao {
  162.    
  163.     private Connection con;
  164.  
  165.     //constructor
  166.  
  167.     public FriendJDBCMySQLStatementTemplate(String driver, String url) {
  168.         try {
  169.             Class.forName(driver);
  170.             con = DriverManager.getConnection(url);
  171.         } catch (ClassNotFoundException | SQLException ex) {
  172.             System.out.println("Error: " + ex.getMessage());
  173.         }
  174.     }
  175.  
  176.     public FriendJDBCMySQLStatementTemplate(String driver, String url, String user, String password) {
  177.         try {
  178.             Class.forName(driver);
  179.             con = DriverManager.getConnection(url, user, password);
  180.         } catch (ClassNotFoundException | SQLException ex) {
  181.             System.out.println("Error: " + ex.getMessage());
  182.         }
  183.     }
  184.  
  185.     public FriendJDBCMySQLStatementTemplate(String url) {
  186.         try {
  187.             con = DriverManager.getConnection(url);
  188.         } catch (SQLException ex) {
  189.             System.out.println("Error: " + ex.getMessage());
  190.         }
  191.     }
  192.  
  193.    
  194.     @Override
  195.     public void execSQL(String sql) {
  196.         try ( Statement st = con.createStatement()) {
  197.             st.executeUpdate(sql);
  198.         } catch (SQLException ex) {
  199.             System.out.println("Error : " + ex.getMessage());
  200.         }
  201.     }
  202.  
  203.     @Override
  204.     public void insert(Friend f) {
  205.         String sql;
  206.         if (f.getId() == 0) {//when id is null
  207.             sql = String.format("insert into friend values(null, '%s', '%s', '%s')", f.getName(), f.getAddress(), f.getPhone());
  208.         } else {
  209.             sql = String.format("insert into friend values('%d', '%s', '%s', '%s')", f.getId(), f.getName(), f.getAddress(), f.getPhone());
  210.         }
  211.  
  212.         try ( Statement st = con.createStatement()) {
  213.             st.executeUpdate(sql);
  214.         } catch (SQLException ex) {
  215.             System.out.println("Error : " + ex.getMessage());
  216.         }
  217.     }
  218.  
  219.     @Override
  220.     public void update(Friend f) {
  221.         String sql = String.format("update friend set name='%s', address='%s', phone = '%s' where id = '%d'",
  222.                 f.getName(), f.getAddress(), f.getPhone(), f.getId());
  223.  
  224.         try ( Statement st = con.createStatement()) {
  225.             st.executeUpdate(sql);
  226.         } catch (SQLException ex) {
  227.             System.out.println("Error : " + ex.getMessage());
  228.         }
  229.     }
  230.  
  231.     @Override
  232.     public void delete(int id) {
  233.         String sql = String.format("delete from friend where id = '%d'", id);
  234.         try ( Statement st = con.createStatement()) {
  235.             st.executeUpdate(sql);
  236.         } catch (SQLException ex) {
  237.             System.out.println("Error : " + ex.getMessage());
  238.         }
  239.     }
  240.  
  241.     @Override
  242.     public void deleteAll() {
  243.         try(Statement st = con.createStatement()){
  244.             st.executeUpdate("delete from friend");
  245.         } catch (SQLException ex) {
  246.             System.out.println("Error : " + ex.getMessage());
  247.         }
  248.        
  249.         try(Statement st = con.createStatement()){
  250.             st.executeUpdate("alter table friend auto_increment = 0");
  251.         } catch (SQLException ex) {
  252.             System.out.println("Error : " + ex.getMessage());
  253.         }
  254.     }
  255.  
  256.     @Override
  257.     public Friend getAFriendById(int id) {
  258.         Friend result = null;
  259.         String sql = String.format("select * from friend where id = '%d'", id);
  260.         try ( Statement st = con.createStatement();  ResultSet rs = st.executeQuery(sql)) {
  261.             if (rs.first()) {
  262.                 result = new Friend(
  263.                         rs.getInt("id"),
  264.                         rs.getString("name"),
  265.                         rs.getString("address"),
  266.                         rs.getString("phone")
  267.                 );
  268.             }
  269.  
  270.         } catch (SQLException ex) {
  271.             System.out.println("Error : " + ex.getMessage());
  272.         }
  273.  
  274.         return result;
  275.     }
  276.  
  277.     @Override
  278.     public List<Friend> getAllFriends() {
  279.         List<Friend> result = new ArrayList<>();
  280.         String sql = "select * from friend";
  281.         try ( Statement st = con.createStatement(); ResultSet rs = st.executeQuery(sql)) {
  282.             while(rs.next())
  283.                 result.add(new Friend(
  284.                         rs.getInt("id"),
  285.                         rs.getString("name"),
  286.                         rs.getString("address"),
  287.                         rs.getString("phone")
  288.                 ));
  289.         } catch (SQLException ex) {
  290.             System.out.println("Error : " + ex.getMessage());
  291.         }
  292.         return result;
  293.     }
  294. }

Write a comment

To post comments, please log in or register.