Unit VI: Interacting with Database
Q. Questions: 2 Marks each
1.List JDBC Drivers.
Ans:
1. Type1:JDBC/ODBC bridge
2. Type2:Native API drivers
3. Type3:Network protocol, pure Java driver
4. Type4:Native protocol, pure Java driver
2.Write syntax of "insert" query.
Ans:
Datatype variable_name=Statement_object.executeUpdate(“insert into
table_name values(value1, value2, value3)”);
3.Enlist navigation methods of ResultSet.
Ans:
1. public boolean first ( ) : moves virtual curosr to the first row of ResultSet.
2. public boolean last ( ) : moves virtual cursor to the last row.
3. public boolean next ( ) : moves virtual cursor to the next row.
4. public boolean previous ( ) : moves virtual cursor to the previous row
5. public boolean absolute (int position ) : positions virtual cursor at the
specified row no.
6. public boolean relative ( int row ) : moves the virtual cursor the specified no.
of rows . If this parameter is positive , virtual cursor moves forward by that no
.of rows. If it is negative , the cursor moves backward direction by that no .of
rows.
4.Write use of PreparedStatement.
Ans:
When we want to run same query multiple times, using Statement object
,we have to write query all the time, it will be compiled all times. So there
will be overhead.
PreparedStatement is used to write query once ,compile once and run query
multiple times.
Query is formed with question marks and before running value is set in place of
question mark.
5.Write syntax to retrieve Connection object.
Ans:
Connection Connection_object=DriverManager.getConnection("jdbc:odbc:
Connectivity_name");
Q. Questions: 4 Marks each
1. Explain two tier architecture of JDBC.
Ans:
1. This is the first generation of client-server architectures.
2. It contains two active components: Client:-which requests data server:-which
delivers data
3.Basically , the application processing is done separately for Database queries
& updates and for users interface presentations.
4. Usually the networks binds the backend to the front end , although both tiers
could be present on the same hardware. e.g. Hundreds/thousands of airline seat
reservation application can connect to control to a central DBMS to request,
insert or modify data.
5. Actually, it is inadvisable to overload the database engine with data
processing i.e. irrelevant to the server, thus some processing usually also
happens on the clients.
6. Two tiers often called as:
1. Application layer: includes JDBC drivers , business logic & user interfaces.
2. Database layer: consists of RDBMS server.
7. Advantages:
1. It is simple in design
2. Client-side scripting offloads work onto the client.
8.Drawbacks:
1. Fat client
2. It is inflexible
2. Write JDBC code to establish a connection with Database.
Ans:
import java.io.*;
import java.sql.*;
class db2
{
public static void main(String ar[])
{
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ;
Connection con=DriverManager.getConnection("jdbc:odbc:studdb");
Statement st=con.createStatement();
int r =st.executeUpdate("delete from syco where RollNo='12'");
System.out.println("row deleted");
ResultSet rs=st.executeQuery("select * from syco");
while(rs.next ( ))
System.out.println(rs.getInt(1)+"\t"+rs.getString(2)); con.close();
} catch(Exception e)
{
}
}}
3. Differentiate between Statement and PreparedStatement
Ans:
Statement PreparedStatement
Compiled every time executed Compiled once and reused
Slower, especially for repeated Faster for repeated queries
queries
Prone to SQL Injection attacks Prevents SQL Injection with
parameters
No support for parameters Supports parameters using
placeholders
Supports parameters using Best for repeated queries with
placeholders parameters
SELECT * FROM users WHERE id SELECT * FROM users WHERE id
=1 =?
4. Write a program to insert a record in "SYCO" table of "Student"
database.
Ans:
import java.io.*;
import java.sql.*;
class db4
{
public static void main(String ar[])
{
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") ;
Connection con=DriverManager.getConnection("jdbc:odbc:studdb");
Statement st=con.createStatement();
int r =st.executeUpdate("insert into syco values(45,35,'ABC')");
System.out.println("row inserted");
ResultSet rs=st.executeQuery("select * from syco");
while(rs.next( ))
System.out.println(rs.getInt(2)+"\t"+rs.getString(3));
con.close();
} catch(Exception e)
{
}
}}
5. Write a program to delete record from "emp" table having id=9.
Ans:
import java.io.*;
import java.sql.*;
class sql1
{
public static void main(String arr[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver ");
Connection con=DriverManager.getConnection("jdbc:odbc:s1");
Statement st=con.createStatement();
int r=st.executeUpdate("delete from emp where id=9");
System.out.println("Record Deleted");
con.close();
}
catch(Exception e)
{
System.out.println("Exception occur");
}
}
}
6. Write a program to update record from "emp" table, change name to
"XYZ" of employee having id=12.
Ans:
import java.io.*;
import java.sql.*;
class sql2
{
public static void main(String arr[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver ");
Connection con=DriverManager.getConnection("jdbc:odbc:ggsp");
Statement st=con.createStatement();
int r=st.executeUpdate("update emp set Name=xyz where id=12");
System.out.println("Record Deleted");
con.close();
}
catch(Exception e)
{
System.out.println("Exception occur");
}
}
}
