Using WHERE Clause in JDBC
Where clause is used to retrieve the data from a table based on some specific conditions. It allows to filter the data from a table, a WHERE clause can be added to the SELECT statement. The WHERE clause searches the specific results by the SELECT statement.
Example of WHERE Clause
|
SELECT Name from Employee where Employee_ID=1; |
Where clause Using LIKE
1.The
following SQL statement will return employees with names that start
with 'a':
|
SELECT * FROM Employee WHERE Name LIKE 'a%'; |
2.The following SQL statement will return employees with names that end with 'y':
|
SELECT * FROM Employee WHERE Name LIKE '%y'; |
3.The following SQL statement will return employees with names that contain the pattern 'as':
|
SELECT * FROM Employee WHERE Name LIKE '%as%'; |
A "%" and â??_â?? sign is known as wildcards, both before and after the pattern.
Operators Used With WHERE Clause
|
= Equal <> Not Equal > Greater than < Less than >= Greater than or Equal <= Less than or Equal BETWEEN Between an inclusive range LIKE Search for pattern |
This program is concern with, how to get selected data from a table. Like- If any specific data desired then it would be easier as compare to search in whole table. So according to the Query the data would retrieve. In this program we used employee table which has Employee_ID field. So it will search in table and then give the result of an employee whose Employee_ID is 1.
WhereClause.java
import java.sql.*;
public class WhereClause {
public static void main(String a[])
{
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "vineej";
String driver = "com.mysql.jdbc.Driver";
String userName = "vineej";
String password = "no";
try {
Class.forName(driver).newInstance();
con = DriverManager.getConnection(url+dbName,userName,password);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select Name from Employee where Employee_ID=1");
System.out.println("Results");
while( rs.next() ) {
String data = rs.getString(1);
System.out.println( data );
}
st.close();
}
catch( Exception e ) {
System.out.println(e.getMessage());
e.printStackTrace();
}
}
}
Description of program
This
program making the connection between MySQL database and java with
the help of many types of APIs interfaces.
When it will be execute then it shows "Results" .The
Employee is the table name and Employee_ID is the column name.
Description of code
1.Connection
An
interface in java.sql package that provides connection with the
database like- MySQL and java files. The SQL statements are executed
within the context of the Connection interface.
2.Class.forName(String
driver)
Class.forName method is static. This driver load the
class and returns class instance and takes string type value and
after that it will match the class with string.
3.DriverManager
It
is a class of java.sql package that controls the JDBC drivers. Each
driver has to be register with this class.
4.getConnection(String
url, String userName, String password)
This method establishes
a connection to specified database url. It takes three string types
of arguments like:
|
url - Database url where stored or
created your database |
5.con.close()
This
method is used for disconnecting the connection. It frees all the
resources occupied by the database.
6.printStackTrace()
The
method is used to show error messages. If the connection is not
connected then it throws the exception and print the message.