How to process SQL statements with JDBC explain with an example?

JDBCJava 8ProgrammingObject Oriented Programming

To process an SQL statement, you need to follow the steps given below:

  • Establish the connection.

  • Create a statement.

  • Execute the statement/query.

  • Process the result.

  • Close the connection.

Establishing a Connection

To process SQL statements first of all you need to establish connection with the desired DBMS or, file System or, other data sources.

  • To do so, Register the JDBC driver class, corresponding to the DataSource you need to the DriverManager using the registerDriver() method.

Driver myDriver = new com.mysql.jdbc.Driver();
DriverManager.registerDriver(myDriver);

This method accepts an object of the Driver class; it registers the specified Driver with the DriverManager.

You can also register the driver using the forName() method. This method loads the specified class in to the memory and it automatically gets registered.

Class.forName("com.mysql.jdbc.Driver");
  • After registering the Driver class, get the Connection object using the getConnection() method.

    This method accepts a database URL (an address that points to your database), Username and, password and, returns a connection object.

String url = "jdbc:mysql://localhost/";
String user = "user_name";
String passwd = "password";
Connection conn = DriverManager.getConnection(url, user_name, password);

Creating a Statement

The Statement interface represents an SQL statement and JDBC provides 3 kinds of Statements

  • Statement: A general purpose statement which does not accept any parameters.

  • PreparedStatement: A precompiled SQL statement which accepts input parameters.

  • Callable Statement: This is used to call the stored procedures.

The Connection interface provides methods named createStatement(), prepareStatement() and, prepareCall() to create Statement, prepared statement and, CallableStatement respectively. You need to create any of these statements using the respective method.

conn.createStatement();
conn.prepareStatement(query);
conn.prepareCall(query);

Executing the Statements

After creating the statement objects, you need to execute them. To execute the statements, the Statement interface provides three methods namely, execute(), executeUpdate() and, executeQuery().

  • execute(): Used to execute SQL DDL statements, it returns a boolean value specifying whether the ResultSet object can be retrieved.

  • executeUpdate(): Used to execute statements such as insert, update, delete. It returns an integer value representing the number of rows affected.

  • executeQuery(): Used to execute statements that returns tabular data (example select). It returns an object of the class ResultSet.

Execute the created statement using one of these methods.

stmt.execute(query);
stmt.executeQuery(query);
stmt.execute(query);

Processing the Result

Once you execute the statements/queries you will get the result of the respective query as a return value from execute() (boolean value) or, executeQuery() (ResultSet) or, executeUpdate() (integer value) methods.

The ResultSet object holds tabular values and it has a pointer that points to the rows of the table, initially the pointer/cursor will be positioned before the first row. Once you get a ResultSet object you need to retrieve required values from it and process.

You can move the pointer to the next row using the next() method. This returns a boolean value specifying whether the ResultSet contains next row.

Therefore, using this method in the while loop you can iterate the contents of the result set and get the contents of the columns of that row using the getter methods of the Statement interface.

while(rs.next()) {
   System.out.print("Brand: "+rs.getString("Mobile_Brand")+", ");
   System.out.print("Sale: "+rs.getString("Unit_Sale"));
   System.out.println("");
}
raja
Published on 25-Mar-2019 11:15:01
Advertisements