×

Welcome to TagMyCode

Please login or create account to add a snippet.
 

Friend: Implementation JDBC MySQL using java.sql.PreparedStatement

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

Write a comment

To post comments, please log in or register.