• JDBC Prepared Statement

    JDBC Prepared Statement

    JDBC prepared statement address the following requirements:

    1. Creating parametrised statements such that data for parameters can be substituted dynamically.
    2. Creating statements involving data values that cannot always be represented as character strings.
    3. Pre-compiling SQL statements to avoid repeated compiling of the same SQL statements.

    In most cases, you may not have the complete information to construct a WHERE clause in SQL. For instance, to write a SQL SELECT statement to select the data of a user, while writing the JDBC code, you need to know the primary key value to construct the SQL. In most cases, such information is available only at runtime. Prepared statements address this problem by providing for parameters in SQL.

    Instead of using values, you may use “?” qualifiers in SQL. So instead of creating a statement with the SQL string:

    SELECT <select_fields> FROM <table_name> WHERE USER_ID = <value>

    You can use a prepared statement with the SQL string:

    SELECT <select_fields> FROM <table_name> WHERE USER_ID = ?

    You can substitute values using methods on the java.sql.PreparedStatement interface.

    The same applies to SQL involving complex data types such as long text data, binary data, or even timestamp data. Such data types cannot be expressed as such plain string. For instance, how would we create a SQL statement to update image data? Since prepared statements are parameterized, instead of expressing such data type sin the SQL statement directly, you can set the data using various methods on the java.sql.PreparedStatement interface.

    In addition, the same SQL statement can be executed many times with different parameters and the database can compile such statements just once, this improving performance. A PreparedStatement object can hold precompiled SQL statements. The following methods on the java.sql.Connection interface let us create PreparedStatement objects:

    PreparedStatement prepareStatement(String sql) throws SQLExceptionPreparedStatement prepareStatement(String sql, int resultSetType,int resultSetConcurrency) throws SQLException

    JDBC PreparedStatement – Example

    Here I have created simple Java DataBase Connectivity (JDBC) PreparedStatement example that show you the use of PreparedStatement class and prepareStatement() method with several DataBase operations like, Insert, Save.

    JDBC PreparedStatement Program Code

    Example:

    1. To start a practical, we need to first create ODBC connection. If you are using Windows OS then go to Control Panel à Administrative Tools à ODBC Data Source and create desired DSN (Data Source Name). Here I have created “EmpDSN”.
    1. Now Start NetBeans IDE. Create new java application and draw the frame as displayed below figure.
    JDBC PreparedStatement Example

    JDBC PreparedStatement Example

    1. After completing the design, click on source tab and start to declare variable in general declaration under class area.
    String dburl = “jdbc:odbc:EmpDSN”;String user = “”;

    String pass = “”;

    Connection c;

    PreparedStatement ps;

    Statement s;

    ResultSet r;

    1. Now write down necessary code into class constructor which are need to initialize once like, calling Class.forName() method, initialize connection, creating ResultSet.
    public Practical02DBPreparedStatement() {initComponents();

    try{

    c = DriverManager.getConnection(dburl, user, pass);

    s = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

    r = s.executeQuery(“Select * From Employee”);

    }catch(Exception e){}

    }

    1. Write down the operation code on ActionPerformed event of appropriate button Like, Insert Button, and Save Button.
    private void btnInsertActionPerformed(java.awt.event.ActionEvent evt) {// TODO add your handling code here:

    tfENo.setText(“”);

    tfEName.setText(“”);

    tfEDesignation.setText(“”);

    tfECity.setText(“”);

    }

    private void btnSaveActionPerformed(java.awt.event.ActionEvent evt) {

    // TODO add your handling code here:

    String ENo = tfENo.getText();

    String EName = tfEName.getText();

    String EDesig = tfEDesignation.getText();

    String ECity = tfECity.getText();

    String insEmp = “Insert Into Employee Values(?,?,?,?)”;

    try{

    ps = c.prepareStatement(insEmp);

    ps.clearParameters();

    ps.setString(1, ENo);

    ps.setString(2, EName);

    ps.setString(3, EDesig);

    ps.setString(4, ECity);

    ps.executeUpdate();

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    1. Write down the navigation code on ActionPerformed event of appropriate button Like, Move First, Move Previous, Move Next, and Move Last.
    private void btnFirstActionPerformed(java.awt.event.ActionEvent evt) {// TODO add your handling code here:

    try{

    r = s.executeQuery(“Select * From Employee”);

    r.first();

    tfENo.setText(r.getString(“ENo”));

    tfEName.setText(r.getString(“EName”));

    tfEDesignation.setText(r.getString(“EDesignation”));

    tfECity.setText(r.getString(“ECity”));

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    private void btnPreviousActionPerformed(java.awt.event.ActionEvent evt) {

    // TODO add your handling code here:

    try{

    r.previous();

    if(r.isBeforeFirst()){

    r.first();

    }

    tfENo.setText(r.getString(“ENo”));

    tfEName.setText(r.getString(“EName”));

    tfEDesignation.setText(r.getString(“EDesignation”));

    tfECity.setText(r.getString(“ECity”));

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    private void btnNextActionPerformed(java.awt.event.ActionEvent evt) {

    // TODO add your handling code here:

    try{

    r.next();

    if(r.isAfterLast()){

    r.last();

    }

    tfENo.setText(r.getString(“ENo”));

    tfEName.setText(r.getString(“EName”));

    tfEDesignation.setText(r.getString(“EDesignation”));

    tfECity.setText(r.getString(“ECity”));

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    private void btnLastActionPerformed(java.awt.event.ActionEvent evt) {

    // TODO add your handling code here:

    try{

    r.last();

    tfENo.setText(r.getString(“ENo”));

    tfEName.setText(r.getString(“EName”));

    tfEDesignation.setText(r.getString(“EDesignation”));

    tfECity.setText(r.getString(“ECity”));

    }catch(Exception e){

    System.out.println(e.toString());

    }

    }

    1. Now Save and Run Application.
    Appreciate my work :Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestShare on RedditShare on StumbleUponShare on TumblrDigg thisShare on YummlyShare on VKFlattr the authorBuffer this page

Leave a Reply

Your email address will not be published. Required fields are marked *