JavaBean and PreparedStatement Usage

佚名 6年前 (2019-05-16) 随笔 1228人围观抢沙发百度已收录

Based on previous example, I have made a few enhancements.

1. I have used class actor (JavaBean) as parameter for search, delete and insert method.

SRE实战互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

　As could see written below, I have used class actor as parameter,

　this is called as JavaBean. which contains our data information.

 public void SelectActor(actor a)

   public int DeleteActor(actor a)

  public int addActor(actor a)

Javabean actor code as below

package com.yang.Bean;

import java.util.Date;

public class actor {
    
    private int id;
    private String firstName;
    private String LastName;
    private Date date;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return LastName;
    }
    public void setLastName(String lastName) {
        LastName = lastName;
    }
    public Date getDate() {
        return date;
    }
    public void setDate(Date date) {
        this.date = date;
    }
    
    
}

2. I have used preparedStatement instead of Statement.

　　The advantage of preparedStatement is that I can use ? to denote the value that I hope to use in SQL.

　　And then set these values afterwards.

　　this will make the SQL much more easy to read and understand.

　　namely as below.

 String SQL = "insert into actor (actor_id, first_name, last_name, last_update) values(?,?,?,?)";

stmt = conn.prepareStatement(SQL);    
            stmt.setInt(1, a.getId());
            stmt.setString(2, a.getFirstName());
            stmt.setString(3, a.getLastName());
            stmt.setTimestamp(4, new Timestamp(a.getDate().getTime()));

Test Case 1. SelectActor(a)

Test Case 2. DeleteActor(a)

As could see, there is record id 202 in DB before running the code

Code Example

package com.yang.dao;
//Step 1, import the needed packages
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Date;
import java.util.Properties;

import com.yang.Bean.actor;

import src.util.ConfigManager;
/*USE mysql;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '831015';
FLUSH PRIVILEGES;

*/

public class NewsDao {
    
 // JDBC driver name and database URL
      String JDBC_Driver = null;
      String url = null;
      String UserName = null;
      String Password = null;
      Connection conn = null;
      PreparedStatement stmt = null;
      
    public int addActor(actor a) {
        
         String SQL = "insert into actor (actor_id, first_name, last_name, last_update) values(?,?,?,?)";

        this.getConnection();
         
        try {
            
            stmt = conn.prepareStatement(SQL);    
            stmt.setInt(1, a.getId());
            stmt.setString(2, a.getFirstName());
            stmt.setString(3, a.getLastName());
            stmt.setTimestamp(4, new Timestamp(a.getDate().getTime()));
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        System.out.println("Executing the Update Query...");
        
        int i = 0;
        try {
             i = stmt.executeUpdate();
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        if(i>0) {
            System.out.println("Successfully Inserted the data");
        }
        return i;
        
    }

    public int DeleteActor(actor a) {
         String SQL = "delete from actor where actor_id =?";

         this.getConnection();
          
         try {
             
             stmt = conn.prepareStatement(SQL);    
             stmt.setInt(1, a.getId());
              
             
         } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
         
         System.out.println("Executing the Update Query...");
         
         int i = 0;
         try {
              i = stmt.executeUpdate();
             
         } catch (SQLException e) {
             // TODO Auto-generated catch block
             e.printStackTrace();
         }
         
         if(i>0) {
             System.out.println("Successfully Deleted record");
         }
         return i;
        
    }
    
    public void SelectActor(actor a) {
        
        String SQL = "select * from actor where first_name =? and last_name = ?";
        
        this.getConnection();
        
        try {
            
            stmt = conn.prepareStatement(SQL);
            stmt.setString(1,a.getFirstName() );
            stmt.setString(2, a.getLastName());
            
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        System.out.println("Executing the Query...");
        
        
        ResultSet rs = null;
        try {
            rs = stmt.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
        System.out.println("fetching the result...");
        
        try {
            while(rs.next()) {
                
                int id = rs.getInt("actor_id");
                
                String name = rs.getString("first_name")+ "  "+ rs.getString("last_name");
                
                System.out.println(id +  "  "+ name);        
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        
    }
    
    public void getConnection()  {
        
        ConfigManager m = new ConfigManager();
        JDBC_Driver = m.getString("jdbc.driver");
        url = m.getString("jdbc.connection.url");
        UserName = m.getString("jdbc.connection.username");
        Password = m.getString("jdbc.connection.password");
    
        try {
            
            //System.out.println("Connecting to Database...");
            Class.forName(JDBC_Driver);
            
            conn = DriverManager.getConnection(url, UserName, Password);
            
            //System.out.println("Connected to Database...");
        }
        catch(SQLException se){
              //Handle errors for JDBC
              se.printStackTrace();
           }catch(Exception e){
              //Handle errors for Class.forName
              e.printStackTrace();
           }    
     }
    
    public static void main(String[] args) {
        
        NewsDao my = new NewsDao ();
        /*actor a = new actor();
        a.setFirstName("NICK");
        a.setLastName("WAHLBERG");
        my.SelectActor(a);
         
        actor b = new actor();
        b.setDate(new Date());
        b.setFirstName("YaJing");
        b.setLastName("Hong");
        b.setId(202);
         
        my.addActor(b);*/
        
        //my.DeleteActor(202);
    }
    
}