Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to process SQL statements with JDBC explain with an example?
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("");
}
4K+ Views
