2.16 Connecting with Databases
1) Connecting with Access Database :
-
To
connect java application to access database we must have at least one database
created in access.
-
Steps to create a database in MS-Access:
1) Open Microsoft
Office Access.
2) Click on Blank Database.
3) Type an
appropriate name of database in File Name: box for example, HOD_DATA and click
on Create Button.
4) Create
appropriate field name in table and value as per the field.
1) Right click on
Table1 and select Save.
2) Type the name of
Table for example, DATA.
3) And click on OK
button.
4) Close the Table
by right clicking on DATA
5) And select
Close.
6) And Exit from
Database.
7) Move this
database to the appropriate drive where we want.
-
Now
lets create TYPE 1 driver program for JDBC with access.
-
Example:
-
import java.sql.Connection;
-
import java.sql.DriverManager;
-
import java.sql.ResultSet;
-
import java.sql.Statement;
-
-
public class Type_One
-
{
-
public static void main(String[] args)
-
{
-
try
-
{
-
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//Load Driver
-
-
Connection con =
DriverManager.getConnection("jdbc:odbc:HOD_DATA"); //Create
Connection with Data Source Name : HOD_DATA
-
Statement stmt =
con.createStatement(); // Create Statement
-
String query = "select * from
Data"; // Create Query
-
stmt.execute(query); // Execute
Query
-
ResultSet rs = stmt.getResultSet();
//return the data from Statement into ResultSet
-
while(rs.next()) // Retrieve
data from ResultSet
-
{
-
System.out.print("Serial
number : "+rs.getString(1)); //1st column of Table from database
-
System.out.print(" , Name
: "+rs.getString(2)); //2nd column of Table
-
System.out.print(" , City :
"+rs.getString(3)); //3rd column of Table
-
System.out.println(" and
Age : "+rs.getString(4)); //4th column of Table
-
}
-
stmt.close();
-
con.close();
-
}
-
catch (Exception e)
-
{
-
System.out.println("Exception
: "+e);
-
}
-
}
-
}
-
Output:
-
Serial number : 1 , Name : Ashutosh Abhangi , City :
Dhoraji and Age : 27
-
Serial number : 2 , Name : Kamal Kotecha , City :
Junagadh and Age : 24
-
Key
point:
-
String
which we are writing in Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
to load the driver.
-
String
which we are writing in Connection con =
DriverManager.getConnection("jdbc:odbc:HOD_DATA") to
create connection with particular database.
-
Here
HOD_DATA is our DSN (Data Source Name).
-
Steps for creating DSN for access :
-
Go
to Control Panel.
1) Click
on Administrative Tools(Window XP)
2) For (Window 7)
System and Security then Administrative Tools.
3) Click on Data
Sources (ODBC).
4) Select MS Access
Database and Click on Add button.
-
Here
in Windows XP we can easily add new DSN
-
But
if we are getting an error or not able to add new DSN in Window 7.
-
Go
to C:\Windows\SysWOW64
-
And
then open odbcad32.exe
-
And
repeat step 4.
5)
Select
Microsoft Access Driver (*.mdb,*.accdb)
6)
And
Click on Finish button.
7)
If
we cant find the below driver then we should download JDBC ODBC Driver for ms
access.
8)
Type
Data Source Name.
9)
For
example HOD_DATA
10)
Then
click on Select button in the Database frame.
11)
Select
particular database.
12)
Which
we saved on particular drive.
13)
And
created at beginning of this page (HOD_DATA).
14)
And
click on OK button.
5)
Click
on OK button and Check out the textarea of Data Sources Administrator.
6)
Now
it contains a new DSN as a HOD_DATA.
7)
Click
on OK button
8)
And
close the Administrative Tools (Control Panel).
-
Do
not confuse wer self due to Database Name and Data Source Name.
-
Here
Both are same HOD_DATA but we can take different name too.
-
One
more thing there may be a 32 bit or 64 bit issue like architecture mismatch.
-
Recommend
we that please make them all same.
-
Wer
java IDE tool, Microsoft Aceess and JVM or JDK all must be the same bit
(32/64) version.
-
Now
run the above program and check out the output.
PreparedStatement in access:
-
Lets
now move to PreparedStatement example for access.
-
First
of all lets assume that we have table named PA in access.
-
And
our DSN is DATA.
-
Example:
-
import java.sql.Connection;
-
import java.sql.DriverManager;
-
import java.sql.PreparedStatement;
-
-
public class Prepare_Demo
-
{
-
public static void main(String[] args)
-
{
-
try
-
{
-
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
-
Connection
con=DriverManager.getConnection("jdbc:odbc:DATA");
-
-
PreparedStatement ps = con.prepareStatement("insert
into PA (ID,Name,CITY,AGE)values(?,?,?,?)");
-
-
ps.setInt(1,200);
-
ps.setString(2, "hello");
-
ps.setInt(4,101);
-
ps.setString(3, "brd");
-
-
ps.executeUpdate();
-
-
System.out.println("inserted");
-
-
con.close();
-
}
-
catch (Exception e)
-
{
-
System.out.println(e);
-
}
-
}
-
}
-
Output:
-
inserted
-
First
run the above program with suitable table and DSN
-
After
running it refreshes wer access database.
-
And
we can see one record inserted as per our program.
-
Example:- 2
-
We
can run PreparedStatement program for JSP too with dynamic data.
-
For
this we will cretae two JSP file one for inserting data (simple form with text
box as per our table).
-
Second
JSP file contains logic for connecting data base as well as PreparedStatement
logic for inserting data.
-
Insert_data.jsp
-
<html>
-
<head>
-
<meta http-equiv="Content-Type"
content="text/html; charset=ISO-8859-1">
-
<title>Insert
DATA</title>
-
</head>
-
<body>
-
<form
action="datams.jsp">
-
ID: <input
type="text" name="ID"/>
-
NAME : <input
type="text" name="NAME"/>
-
AGE : <input
type="text" name="AGE"/>
-
CITY : <input
type="text" name="CITY"/>
-
-
<input
type="submit" value ="INSERT">
-
</form>
-
</body>
-
</html>
-
Now
insert value in text box as we want to insert in database as shown below.
-
Remember
here too our DSN and Table are same as above program.
-
We
can see the data here which i want to insert in our database.
-
Now
we create our second jsp page.
-
datams.jsp
-
<%@ page
language="java" contentType="text/html; charset=ISO-8859-1"
-
pageEncoding="ISO-8859-1"
import="java.sql.*"%>
-
<!DOCTYPE
html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
-
<html>
-
<head>
-
<meta
http-equiv="Content-Type" content="text/html;
charset=ISO-8859-1">
-
<title>Insert title
here</title>
-
</head>
-
-
<body>
-
<%
-
int id = Integer.parseInt(request.getParameter("ID"));
-
int age =
Integer.parseInt(request.getParameter("AGE"));
-
String nm =
request.getParameter("NAME");
-
String ct =
request.getParameter("CITY");
-
try
-
{
-
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
-
Connection
con=DriverManager.getConnection("jdbc:odbc:DATA");
-
PreparedStatement ps =
con.prepareStatement("insert into PA
(ID,Name,CITY,AGE)values(?,?,?,?)");
-
-
ps.setInt(1,id);
-
ps.setString(2,nm);
-
ps.setInt(4,age);
-
ps.setString(3,ct);
-
-
ps.executeUpdate();
-
System.out.println("inserted");
-
con.close();
-
}
-
catch (Exception e)
-
{
-
System.out.println(e);
-
}
-
%>
-
</body>
-
</html>
-
Output:
-
Inserted
-
Now
again refresh wer table data
-
And
we can see one more new record which we inserted dynamically.
No comments:
Post a Comment