Prev
Home
Next
Menu

Some Important JDBC Methods

In this tutorial, we will discuss some of the important methods of JDBC. Eventually, all these methods are from Statement interface which provides necessary support to execute queries with the database. They are namely —

1) public ResultSet executeQuery (String sql) :  It is used to execute “select” query and returns the object of ResultSet.

2) public int executeUpdate (String sql) :  It is used to execute specified SQL query, it may be create, drop, insert, update, delete etc. and returns an integer value representing the number of rows affected by the SQL statement.

3) public boolean execute (String sql) :  It can be used with any type of SQL statements and it returns a boolean value. If we don’t know which method to use for SQL statements, then this method can be the best option. A ‘true‘ value indicates that this method returns a result set object which can be retrieved using getResultSet() method;  while a ‘false‘ value indicates that statement has returned an int value or returned nothing.

The executeQuery() method has already been discussed in the preceding sections. So, we will not describe this method here. We provide coding examples for second and third methods only.

 

Example of executeUpdate() method

input.html

<html>
<head>
    <title>Insert Students Data</title>
</head>
<body>
    <h3>Insert Data for Students</h3><br>
    <form action="insert.jsp" method="post">   
        <b>Roll: </b><input type="text" name="first" ><br><br> 
        <b>Name: </b><input type="text" name="second" ><br><br> 
        <input type="submit" value="Submit">  
    </form> 
</body>
</html>

 

insert.jsp

<%@ page import="java.sql.*" %>
<%!
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    String url = "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true";
    String user = "root";
    String pass = "system";
    String str, name, sql;
    int roll, count;
%>
<%
    try {
        //initializing necessary variables
        str = request.getParameter("first"); 
        name = request.getParameter("second"); 
        roll = Integer.parseInt(str);
        sql = "insert into students values(" + roll + ", '" + name + "')";

        //loading the database driver
        Class.forName("com.mysql.cj.jdbc.Driver").newInstance();

        //creating the connection object for MySQL
        con = DriverManager.getConnection(url, user, pass);
        if (con != null) {
             out.println("Successfully connected to " + "MySQL server using TCP/IP..." + "<br>");
        }

        //creating the statement object
        stmt = con.createStatement();

        //returns the number of data rows affected after executing SQL statement
        count = stmt.executeUpdate(sql);

        if(count==1)
            out.println("Successfully added one record" + "<br>");
        else
            out.println("Test unsuccessful!" + "<br>");
    }
    catch (Exception e) {
        out.println("Exception: " + e.getMessage());
    }
    finally {
        try {
            if (con != null) {
                con.close();
            }
        } 
        catch (SQLException e) {   }
    }
%>

 

Output:

 

 

 

 

 

 

 

 

 

Now, we press the ‘Submit’ button to get the following response from Tomcat server:

 

 

 

 

 

 

 

 

 

Then, we perform another insert operation. See it below.

 

 

 

 

 

 

 

 

 

This insertion is also successful. See the response below.

 

 

 

Example of executeUpdate() method

test-execute.jsp

<%@ page import="java.sql.*" %>
<html>
<head>
    <title>JDBC Method Example</title>
</head>
<body>
<%!
    Connection con = null;
    Statement stmt = null;
    ResultSet rs = null;
    String url = "jdbc:mysql://localhost:3306/test?useSSL=false&allowPublicKeyRetrieval=true";
    String user = "root";
    String pass = "system";
    String sql = "select * from students";
    Boolean val;
%>
<%
    try {
        Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        con = DriverManager.getConnection(url, user, pass);
        if (con != null) {
            out.println("Successfully connected to " + "MySQL server using TCP/IP..." + "<br>");
        }        
        stmt = con.createStatement();
        val = stmt.execute(sql);

        if(val==true)
            rs = stmt.getResultSet();
        else
            out.println("Test unsuccessful!" + "<br>");

        while (rs.next()) {
            out.println("Roll = " + rs.getInt(1) + " Name = " + rs.getString(2) + "<br>");
        }
    } 
    catch (Exception e) {
        out.println("Exception: " + e.getMessage());
    } 
    finally {
        try {
            if (con != null) {
                con.close();
            }
        } 
        catch (SQLException e) {  }
    }
%>
</body>
</html>

 

Output:

 

 

 

 

 

 

 

 

 

In the next section, we will discuss about PreparedStatement interface with coding examples.

Prev
Home
Next