Home » Developer & Programmer » JDeveloper, Java & XML » JSP database connectivity using SQL
|
|
| Re: JSP database connectivity using SQL [message #91012 is a reply to message #91009] |
Tue, 12 February 2002 04:49   |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
here is an example, change it according to your requirement
<%@ page import="java.sql.*" %>
<HTML>
<HEAD>
<TITLE>
Hello User
</TITLE>
</HEAD>
<BODY>
<%
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.100.100.34:1521:tst2","camhealth", "coshh");
stmt = conn.createStatement();
rset = stmt.executeQuery ("SELECT item_name FROM webform_module_items where module_id = 8");
while (rset.next())
{
out.println (rset.getString("item_name")+"
");
}
}
catch (SQLException e)
{
out.println ("
SQL error:
n");
}
finally
{
if (rset!= null) rset.close();
if (stmt!= null) stmt.close();
if (conn!= null) conn.close();
}
%>
</BODY>
</HTML>
|
|
|
|
|
|
|
|
|
|
|
|
| Re: JSP database connectivity using SQL [message #92122 is a reply to message #92113] |
Wed, 17 March 2004 04:00 |
costas
Messages: 1
Registered: March 2004
|
Junior Member |
|
|
this code will connect jsp with Oracle database.
[<]%@ page import="java.sql.*" %[>]
<html>
<head> <Title> The JSP JdBCQuery JSP </title></head>
<body bgcolor="white" style="font-family: Tahoma; font-size: 10pt">
[<]%
String SQL = request.getParameter("txtSQL");
String DRV = request.getParameter("txtDriver");
String DB = request.getParameter("txtDb");
String SRV = request.getParameter("txtServer");
String SID = request.getParameter("txtSID");
String UID = request.getParameter("txtUserID");
String PASS = request.getParameter("txtPass");
String PRT = request.getParameter("txtPort");
if (UID == null) UID = "scott";
if (PASS == null) PASS = "tiger";
if (DRV == null) DRV = "oci8";
if (DB == null) DB = "oracle";
if (SRV == null) SRV = "";
if (SID == null) SID = "";
if (PRT == null) PRT = "";
if (SQL == null) SQL = "";
%[>]
FORM TAG REMOVED method='post'>
- Driver -
- <input type='text' name='txtDriver' size='20' value='[<]%= DRV %[>]'>
i.e: thin, oci8 -
- Database -
- <input type='text' name='txtDb' size='20' value='[<]%= DB %[>]'>
i.e.: oracle, odbc -
- Server -
- <input type='text' name='txtServer' size='20' value='[<]%= SRV %[>]'>
server host/IP -
- SID -
- <input type='text' name='txtSID' size='10' value='[<]%= SID %[>]'>
Oracle service ID -
- -
- -
- Port -
- <input type="text" name="txtPort" size="10" value="[<]%= PRT %[>]">
Oracle server port (i.e. 1521) -
- UserID -
- <input type='text' name='txtUserID' size='20' value='[<]%= UID %[>]'>
Oracle server username -
- Password -
- <input type='password' name='txtPass' size='20' value='[<]%= PASS %[>]'>
Oracle server password -
- Query Text -
- <textarea rows='4' name='txtSQL' cols="67">[<]%= SQL %[>]</textarea>
Supported commands : CREATE, ALTER, SELECT, INSERT, UPDATE, DELETE, DESC, DROP -
- -
- <input type='submit' value='Query >>' name='cmdSubmit'> -
FORM TAG REMOVED >
</body>
</html>
[<]%
String url, cmd;
if (SRV.equals(""))
url = "jdbc:" + DB + ":" + DRV + ":@";
else
url = "jdbc:" + DB + ":" + DRV + ":@" + SRV + ":" + PRT + ":" + SID;
if (!SQL.equals(""))
{
int pos = SQL.indexOf(" ");
if (pos == -1)
cmd = SQL.toUpperCase();
else
cmd = SQL.substring(0,pos).toUpperCase();
if (cmd.equals("SELECT"))
{
%[>]
[<]%= runQuery(SQL, url, UID, PASS) %[>]
[<]%
}
else if (cmd.equals("UPDATE") || (cmd.equals("DELETE")) || (cmd.equals("INSERT")) || cmd.equals("CREATE") || (cmd.equals("ALTER")) || (cmd.equals("DROP")) || (cmd.equals("ROLLBACK")) )
{
%[>]
[<]%= runUpdate(SQL, url, UID, PASS) %[>]
[<]%
}
else if (cmd.equals("ROLLBACK"))
{
%[>]
[<]%= Rollback(SQL, url, UID, PASS) %[>]
[<]%
}
else if (cmd.equals("DESC") || (cmd.equals("DESCRIBE"))) //|| (cmd.equals("DROP")))
{
String tbl = SQL.substring(pos+1, SQL.length());
%[>]
[<]%= descTable(url, UID, PASS, tbl) %[>]
[<]%
}
else
{
%[>]
[<]%= "Invalid SQL syntax catched" %[>]
[<]%
}
}
%[>]
[<]%!
private String commit(String cond, String url, String user, String pass) throws SQLException
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(url, user, pass);
// conn.setAutoCommit(false);
conn.commit();
//stmt = conn.createStatement();
//rset = stmt.executeQuery(cond);
return ("Done!");
} catch (SQLException e) {
return ("
SQL Error :
n");
} finally {
if (rset!=null) rset.close();
if (stmt!=null) stmt.close();
if (conn!=null) conn.close();
}
}
private String Rollback(String cond, String url, String user, String pass) throws SQLException
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(url, user, pass);
//conn.setAutoCommit(false);
conn.rollback();
//stmt = conn.createStatement();
//rset = stmt.executeQuery(cond);
return ("Done!");
} catch (SQLException e) {
return ("
SQL Error :
n");
} finally {
if (rset!=null) rset.close();
if (stmt!=null) stmt.close();
if (conn!=null) conn.close();
}
}
private String descTable(String url, String user, String pass, String tbl) throws SQLException
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
ResultSetMetaData rsmd = null;
int cols=0;
StringBuffer dbuff = new StringBuffer();
//int ps = sql.indexOf(" ");
//String Tbl = sql.substring(ps, sql.length()).toUpperCase();
String sQL = "SELECT * FROM " + tbl;
try
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(url, user, pass);
//conn.setAutoCommit(false);
stmt = conn.createStatement();
rset = stmt.executeQuery(sQL);
rsmd = rset.getMetaData();
cols = rsmd.getColumnCount();
dbuff.append("
");
dbuff.append("
- Name - - Null? - - Type -
");
String nl;
int cnt=0;
String c1, c2, c;
c1 = "#CCCCFF";
c2 = "#FFFFCC";
for (int x=1; x<=cols; x++)
{
if (x%2 == 0)
c = c1;
else
c = c2;
if (rsmd.isNullable(x) != 0)
nl = "";
else
nl = "NOT NULL";
dbuff.append("
- " + rsmd.getColumnName(x) + " - - " + nl + " - - " + rsmd.getColumnTypeName(x) + " -
");
}
dbuff.append("
");
return dbuff.toString();
}
catch (SQLException e)
{
return ("
SQL Error :
n");
}
finally
{
if (rset!=null) rset.close();
if (stmt!=null) stmt.close();
if (conn!=null) conn.close();
}
}
// ------------------------------------------------------------------------------------------------------------------------
private String runQuery(String cond, String url, String user, String pass) throws SQLException
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(url, user, pass);
//conn.setAutoCommit(false);
stmt = conn.createStatement();
rset = stmt.executeQuery(cond);
return (formatResult(rset));
} catch (SQLException e) {
return ("
SQL Error :
n");
} finally {
//conn.setAutoCommit(true);
if (rset!=null) rset.close();
if (stmt!=null) stmt.close();
if (conn!=null) conn.close();
}
}
// ------------------------------------------------------------------------------------------------------------------------
private String formatResult(ResultSet rset) throws SQLException
{
StringBuffer sb = new StringBuffer();
ResultSetMetaData rsmd = rset.getMetaData();
int cols;
cols = rsmd.getColumnCount();
if (!rset.next())
sb.append("
No matching rows.n
");
else
{
int cnt=0;
String c1, c2, c;
c1 = "#CCCCFF";
c2 = "#FFFFCC";
sb.append("
");
sb.append("
");
for (int j=1; j<=cols; j++)
{
sb.append(" - [b] " + rsmd.getColumnName (j) + " [b] - ");
}
sb.append("
");
do {
if (cnt%2 == 0)
c = c1;
else
c = c2;
sb.append("
");
for (int i=1; i<=cols; i++)
{
sb.append(" - " + rset.getString(i) + " - ");
}
cnt++;
sb.append("
");
} while (rset.next());
sb.append("
Number of rows : " + cnt + "</br>");
}
return sb.toString();
}
// ------------------------------------------------------------------------------------------------------------------------
private String runUpdate(String cond, String url, String user, String pass) throws SQLException
{
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
PreparedStatement ps = null;
try {
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(url, user, pass);
//conn.setAutoCommit(false);
ps = conn.prepareStatement(cond);
stmt = conn.createStatement();
ps.executeUpdate();
return "Done!";
} catch (SQLException e) {
return ("
SQL Error :
n");
} finally {
if (rset!=null) rset.close();
if (stmt!=null) stmt.close();
if (conn!=null) conn.close();
}
}
%[>]
With other databases is easier to connect but you have to have the suitable driver for it.
For example with Microsoft Access , the following code is fine but there is a bug, somewhere. That is what I am trying to find out now.
<html>
<head><title>Retriveing employee records</title></head>
<body>
FIND EMPLOYEE RECORD
FORM TAG REMOVED ACTION="employee.jsp" METHOD="POST">
GIVE ID HERE:<INPUT TYPE=TEXT NAME="id1">
<INPUT TYPE=SUBMIT VALUE="SUBMIT ID ">
FORM TAG REMOVED >
[<]%int a;%[>]
[<]%Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
java.sql.Connection connection=java.sql.DriverManager.getConnection("jdbc:odbc:employee");
java.sql.Statement statement=connection.createStatement();
String ide=request.getParameter("id1");
a=Integer.parseInt(ide);
%[>]
[<]%=ide%[>]
[<]%
java.sql.ResultSet columns=statement.executeQuery("SELECT name,ammount "+"FROM employee WHERE id=a");
while(columns.next())
{String name=columns.getString("name");
String amount=columns.getString("ammount");
%[>]
- [<]%=name%[>] -
- [<]%=amount%[>] -
[<]%}%[>]
</body>
</html>
|
|
|
|
| Easy way to connec the JSP with ORACLE [message #92123 is a reply to message #92122] |
Wed, 17 March 2004 20:18 |
abdul
Messages: 18
Registered: May 2001
|
Junior Member |
|
|
[<]%@ page language="java" import="java.sql.* , javax.sql.*, oracle.jdbc.pool.* " %[>]
[<]%
session=request.getSession(true);
String test_id=null;
String uname=null;
String ga=null,c_id=null,testid=null,course_id=null,material_id=null,scale=null,test1=null,test2=null,test3=null,test4=null,test5=null,avg=null,rang=null,sd=null;
Connection con=null;
Statement stat=null;
ResultSet rs=null;
int c=1,b=0;
try
{
test_id=(String)session.getValue("user_Id");
uname=(String)session.getValue("userId");
}
catch(Exception e)
{
out.println(e.toString());
}
if (!uname.equals(null))
{
%[>]
<html>
<head>
<title>Wellcome to main</title>
META TAG REMOVED http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
[<]%
try
{
DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
con = DriverManager.getConnection("jdbc:oracle:thin:@aqsa:1521:oracle", "digital", "matl#1");
stat=con.createStatement();
rs=stat.executeQuery("select * from RESULTS where TESTER_ID='"+test_id+"'");
while(rs.next())
{
testid=rs.getString("TEST_ID");
course_id=rs.getString("COURSE_ID");
material_id=rs.getString("MATERIAL_ID");
scale=rs.getString("SCALE");
test1=rs.getString("TEST1");
test2=rs.getString("TEST2");
test3=rs.getString("TEST3");
test4=rs.getString("TEST4");
test5=rs.getString("TEST5");
avg=rs.getString("AVERAGE");
rang=rs.getString("RANGE");
sd=rs.getString("STD_DEV");
if (test_id.equals(null))
{
out.println("no resluts");
}
if (!test_id.equals(null))
{
out.println(testid);
out.println(course_id);
out.println(material_id);
out.println(scale);
out.println(test1);
out.println(test2);
out.println(test3);
out.println(test4);
out.println(test5);
out.println(avg);
out.println(rang);
out.println(sd);
}
} }
catch(Exception e)
{
out.println(e.toString());
return;
}
%[>]
Welcoem to DHT Mr.[<]%out.println(uname);%[>]and ure test id is[<]%out.println(test_id);%[>]
- You been awarded grade -
- -
</body>
</html>
[<]%
}
else
{
out.println("Plz Log on first");
}
%[>]
<html>
<head>
<title>View Grades</title>
META TAG REMOVED http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
</body>
</html>
***********************************
for the JSP connectivity Driver see the Directory of ORA HOMeJDBCLIB
u will find the Classes.jar and ZIp put any one on the CLASS PATH of ur system but make sure the on CMD mode
javac and java Comands are runing
thats all
|
|
|
|
|
|
|
|
| Re: JSP database connectivity using SQL [message #92253 is a reply to message #92122] |
Wed, 05 May 2004 22:46 |
Hiren
Messages: 29
Registered: September 2002
|
Junior Member |
|
|
when i execute my page in tomcat for database connection it send error like
1)"ClassNotfound"
2)"20"
3) driver notregister
here may database is oracle 8i ,stand alone pc
so there is no host string,front end tool- JSP
From :
Hiren
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Nov 29 08:27:53 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
