Home Tutorial Java Core JDBC: WHERE Clause Example

 
 

Share on Google+Share on Google+
JDBC: WHERE Clause Example
Posted on: September 26, 2012 at 12:00 AM
Advertisement
In this section, we will discuss how to use WHERE Clause for putting condition in selection of table records using JDBC API.

JDBC: WHERE Clause Example

In this section, we will discuss how to use WHERE Clause for putting condition in selection of table records using JDBC API.

WHERE Clause :

MySql Where Clause is used to write some condition and data is retrieved under that specific conditions. This is used to filter result according to specified conditions. It searches the result by the SELECT statement.

Some terms which is used in creation of database connection -

Connection: This  interface specifies connection with specific databases like: MySQL, Ms-Access, Oracle etc and java files. The SQL statements are executed within the context of this interface.

Class.forName(String driver): It loads the driver.

DriverManager  :  The DriverManager class will attempt to load the driver classes referenced in the "jdbc.drivers" system property

getConnection(String url+dbName, String userName, String password): This method establishes a connection to specified database url.

he MySQL connection URL has the following format:
jdbc:mysql://[host][:port]/[database][?property1][=value1]...

  • host :The host name where MySQL server is running. Default is 127.0.0.1 - the IP address of localhost.
  • port : The port number where MySQL is listening for connection. Default is 3306.
  • dbName : The name of an existing database on MySQL server. If not specified, the connection starts no current database.
  • Property : The name of a supported connection properties. "userName" and "password" are 2 most important properties.

Statement: This interface executes the SQL statement and returns the result it produces.

createStatement(): It is a method of Connection interface which returns Statement object.

executeQuery(String sql): Executes the given SQL statement, which returns a single ResultSet object.

Example :

package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class WhereClause{
	public static void main(String[] args) {
		System.out.println("Use of WHERE Clause in JDBC...");
		Connection con = null;
		Statement statement = null;
		ResultSet rs = null;
		String url = "jdbc:mysql://localhost:3306/";
		String dbName = "students";
		String driverName = "com.mysql.jdbc.Driver";
		String userName = "root";
		String password = "root";
		try {
			Class.forName(driverName);

			// Connecting to the database
			con = DriverManager.getConnection(url + dbName, userName, password);
			try {
				statement = con.createStatement();

				// Selecting records under Where Clause condition
				String sql = "SELECT * FROM student WHERE roll_no=1";
				rs = statement.executeQuery(sql);
				System.out.println("RollNo\tName\tCourse");
				System.out.println("----------------------");
				while (rs.next()) {
					int roll = rs.getInt("roll_no");
					String name = rs.getString("name");
					String course = rs.getString("course");
					System.out.println(roll + "\t" + name + "\t" + course);

				}

			} catch (SQLException e) {
				System.out.println(e);
			}
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Output :

Use of WHERE Clause in JDBC...
RollNo	Name	Course
----------------------
1	Ron	MCA

Advertisement

Related Tags for JDBC: WHERE Clause Example:


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: September 26, 2012

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com