Avatar of ivoicenetworks
ivoicenetworks
 asked on

Needed A jsp code to pull a value from mysql table

Dear experts can some one help me with the code in jsp page to fetch data from a table coulm in mysql  according to the variable i passed through jsp page

suppose if i browse http://mywesite.com/getvalue?user=test  then
 it will run mysql query  like  SELECT user_data FROM userslist WHERE user_id=test

and dispaly the value in browser .

Waiting for some one reply with the code for jsp and class needed

Thank you
JavaScriptJava EEMySQL Server

Avatar of undefined
Last Comment
ivoicenetworks

8/22/2022 - Mon
Loganathan Natarajan

ivoicenetworks

ASKER
dear logudotcom  i have gone through the same links yesterday ,  . I posted this question expecting some one can reply with exact code . Those links explain how to connect mysqldb to java and making logging page etc.,  but i need to pass a variable through URL and fetch the data from the table . there is no authentication needed . But to run the query backend there must be servlet to connect to DB in which mysql root password is hardcoded ( sorry as i am  completely new to java world , i dont know what do you call it) ..

Hope some one can reply with the jsp page i need to put in webserver .
SANDY_SK

If it is complete jsp code, it is one thing, if you are looking at jsp and servlet and all that, it is a web-application which has to be deployed as a war file(or folder) on the server,

Are you aware on how to build a war file and what are the components of the same?

Because even if we give you a jsp and a servlet which does this, do you know how to build it and deploy it?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ivoicenetworks

ASKER
Dear sandy_sk yeah please give me that file . I have installed mysql and apache-tomcat , I will copy to webapps directory and check .
SANDY_SK

Check this out, save this as Sample.jsp and put it in the webapps folder and access it as follows http://mywesite.com/Sample.jsp?user=test 

PS : I have not executed this code, so check to see if there are any syntax errors and fix them

<%@ page import="import java.sql.Connection" %>
<%@ page import="import java.sql.PreparedStatement" %>
<%@ page import="import java.sql.ResultSet" %>
<%@ page import="import java.sql.DriverManager" %>
<%@ page import="import java.sql.SQLException" %>


<%

String strServer = ""; //Give your database server name here
String strPort = ""; //Give your database port here
String strDBName = ""; //Give your database name here
String strUser = ""; //Give your database user name here
String strPassword = ""; //Give your database password here
String strUser = null;
String strSQL = "SELECT user_data FROM userslist WHERE user_id=? ";

Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;

try{

	strUser = (String) request.getParameter("user"); //Getting the value of user from the request

	Class.forName("com.mysql.jdbc.Driver");
	con = DriverManager.getConnection("jdbc:mysql://"+strServer+":"+strPort+"/"+strDBName, strUser, strPassword);



	ps = con.prepareStatement(strSQL);
	ps.setString(1, strUser);

	rs = ps.executeQuery();

	if(rs.next()){

		out.println("<H1>User Data : "+rs.getString("user_data")+"</H1>");

	} else {
		out.println("<H1>User Data : No data found for user"+strUser+"</H1>");
        }
} catch(Exception ex){
	out.println("Exception occurred while retrieving data");
	ex.printStacktrace();
} finally {
	con.close();
}

%>

Open in new window

ivoicenetworks

ASKER
Dear SANDY_SK  is it ok to put db password  and user name credentials in JSP file ?

I am going to test this . Thank you
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ivoicenetworks

ASKER
Hi SANDY_SK , it doesn't work . I passed user_id  value to url http://localhost/test.jsp?user=test

It supposed to show up test user_data .

It says In the code , strUser string is duplicated ( mentioned in 2 places).

Even i changed the second string to strUser2 but still it doesn't work .

Please help
ASKER CERTIFIED SOLUTION
SANDY_SK

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ivoicenetworks

ASKER
Thank you SANDY_SK