JSP and MySQL and issues

[jill Na]

Hi

I have started using JSP and mysql for a website. These are my questions-

1. Please advice me with some nice tutorial for JSP and (using mySQL with JSP).
2. For creating connection to database -

Connection conn = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "Database";
String driver = "com.mysql.jdbc.Driver";
String username_mysql = "uname";
String password_mysql = "pswd";
Statement statement = null;
ResultSet rs = null;

a. Is there a better way, so that we dont have to specify the username and password like this in the script itself. It looks shady for security.
b. what does the url - jdbc:mysql://localhost:3306/ represent and how do ppl come up with this URL. I know in some cases jdbc:mysql://localhost:3307 works rather that 3306. Is there any way I could check it via browser or mySQL if this url works?
c. Isnt there a jar called smthing like mySQLConnector.jar - how does that work? Can I just copy that jar from some safe site add it to the project? How to update the username and password in that case?


3. Most of the time, does it suffice to import java.sql? If not do I have to add it to the list of jars?
<%@ page import="java.sql.*" %>

Thanks

Hoping to hear from you

[Bear Bibeault]

If you're putting this code in a JSP, your first step is to take some time to learn proper web app structure and stop putting code in JSPs. That's a poor practice from over 8 years ago.

As this really has nothing to do with JSP, it's been moved to the JDBC forum.

[jill Na]

Hi,

Thanks for your reply.
As I pointed out, I just started using JSP And MySQL and found this snippet from most of the websites, that's why I posted the question - if there's a better way to do this.

Is there any links that you would know of for what you suggested? (It was actually my first question in the post too ... )
How about the rest of the questions ? 2a. , b. , c., ? 3 ?

Any advice

-Jill

[Stefan Evans]

Yes there is a better way.
The standard approach to database connections now is to use a JNDI datasource.

Basically your code looks up the Datasource from JNDI, and then invokes the 'getConnection()' method on it.
Your code need know nothing about where the database is, or what the username/password connection details are.

Seeing as you are using JSP, a link to the Tomcat JNDI Datasources may be appropriate.

b. what does the url - jdbc:mysql://localhost:3306/ represent and how do ppl come up with this URL.

The first bit is protocol: jdbc
The second bit tells it what database/driver: mysql
The next bit is the address of the server (in this case localhost, meaning the current computer)
The final bit is the port number to talk to the database on. This is a configuration feature on your database.
The URL is obtained from looking at your documentation for your database :-)

c. Isnt there a jar called smthing like mySQLConnector.jar - how does that work? Can I just copy that jar from some safe site add it to the project? How to update the username and password in that case?

Yes there is such a jar.
You get it from the MySQL site and add it to your project.
For a web application, you normally install it on your application server /lib directory so you can use it with JNDI.

[Ram Narayan.M]

Seconding Bear's comments...

Database accessing should not be in JSPs... Its for presentation logic... DB accessing code should be in separate layer called "DAO(Data Access Objects)"...

And for checking JDBC URL Connectivity, you can go for "Eclipse's Database Development" Embedded Plugin... JDBC url is not HTTP url to be tested in browsers... it is of different protocol...

If you want to cover the username and password from the code, you can use connection pools (DataSource) referring by JNDI names...

[jill Na]

Thnks for your reply Stefan! Its awsm, it covers evthing I needed to know to get started. Thanks Ram.
I will get started with your suggestions ...

[Bear Bibeault]

"jIll lllij", please check your private messages for an important administrative matter. Thanks.