JSP connection to MySQL and Oracle Database memo (Windows platform)

-"61" gift for everyone

Currently, Tomcat is the most popular JSP environment. Tomcat5.0 contains a Web server. If it is a test, you do not need to integrate Tomcat with IIS or Apache. You can perform JSP testing on the web server that comes with Tomcat.
Install JDK before installing Tomcat 2003 (if it is Windows Server 2003, JDK must be installed, because Windows Server 2003 does not contain JVM-Windows Server is released when the MS and sun is just awkward ). After installation, you need to set several environment variables:

Java_home = E:/j2sdk1.4.2 _ 04
Classpath = E:/j2sdk1.4.2 _ 04/LIB; E:/j2sdk1.4.2 _ 04/lib/tools. Jar
Add.; E:/j2sdk1.4.2 _ 04/bin in path;
Catalina_home = E:/tomcat 5.0.
Tomcat_home = E:/tomcat 5.0

My JDK version is 1.4.2 and is installed in the E:/j2sdk1.4.2 _ 04 folder.

The environment variables java_home and catalina_home must be added (at least for reference ). Classpath also says that it will be added, but it does not seem to have any impact if I didn't add it. Add ".; e:/j2sdk1.4.2 _ 04/bin. java class. the class file is affected because the javac.exe compilation command file cannot be found. As for the dot "." In the path, I used to need it in resin. I haven't confirmed whether it is necessary in Tomcat.
We also found that the values of catalina_home and tomcat_home are the same. I estimate that tomcat_home is used in earlier versions of Tomcat, and catalina_home is used now. However, these two items do not seem to be incompatible. If they are added or any of them is added, JSP will not be affected. However, I suggest using a catalina_home.

Tomcat5.0 is easier to set the virtual path, because it can be managed in the Web environment. Http: // [url]: 8080/admin can enter the Management Interface (the Administrator admin password can be set during tomcat installation), in Tomcat server-Service (Catalina) -context can be added under the host (localhost), similar to IIS
In the virtual path: document base refers to the file path starting from the actual E:/tomcat 5.0/webapps/root, path refers to the virtual path name starting from the virtual "/", such as "/mytest ". Tomcat seems to be a little poorly designed here. You only need to add a new context, save it, click the "Commit Changes" button, and then click context to view it. The interface will return to the logon interface, however, sometimes an error occurs on the page. You must manually enter http: // [url]: 8080/admin again. After context is added, the Apache Tomcat service must be "disabled/started" (in the Windows console ). It is said that in the http: // localhost: 8088/Manager Management Interface, start and stop can be used to restart the corresponding context, but after I stop, start cannot be started. Click undeploy to delete the context. Note: The physical folder corresponding to the context is also deleted !! That is to say, you have made an application and moved it here. If you are not careful, you will delete it-it is worse than the virus. Be careful !!

Let me talk about how to connect to MySQL through JSP. If JSP is used to connect to access, I feel like wearing a skirt on a man. Access and ASP are the one that is created in the sky. JSP connection to MySQL or Oracle is reasonable. I think the reason is: JSP is used for development, and its advantage is cross-OS platform. If JSP + access is used for combination, the cross-platform advantage will be lost. ASP and access have already worked very well and are simple. Why bother using JSP for development? Of course, if you are very familiar with JSP (or very unfamiliar with ASP), or like JSP (or very dislike ASP), or you have a large amount of data accumulated in access, you cannot use JSP + ACCESS technically.
The installation of MySQL is very simple and can be used normally after basic installation. You can create a database and set up a user in the MySQL environment. That is, people who are used to the GUI do not feel comfortable. Some MySQL graphical user interface management tools can be downloaded online, but it does not seem free of charge.

Use JSP to connect to the MySQL database, fetch data from the table, and write a code to test it:

Code 1:
<! -- First import some necessary packages -->
<% @ Page import = "Java. Io. *" %>
<% @ Page import = "Java. util. *" %>
<! -- Tell the compiler to use the SQL package -->
<% @ Page import = "Java. SQL. *" %>
<! -- Set Chinese output -->
<% @ Page contenttype = "text/html; charset = gb2312" %>

<HTML>
<Head>
<Title> MySQL test </title>
</Head>
<Body>
<%
Connection con;
Statement stmt;
Resultset RS;

// Load the driver. The following code loads the MySQL driver.
Class. forname ("com. MySQL. JDBC. Driver ");

// Register the MySQL driver
Drivermanager. registerdriver (new COM. MySQL. JDBC. Driver ());

// Connect to the database with the appropriate driver
// String dburl = "JDBC: mysql: // localhost: 3306/MySQL? Useunicode = true & characterencoding = gb2312 ";
// String dbuser = "root"; // User Name
// String dbpwd = "abcd1001"; // Password
// Establish a database connection
// Con = java. SQL. drivermanager. getconnection (dburl, dbuser, dbpwd );

String dburl = "JDBC: mysql: // localhost: 3306/MySQL? User = root & Password = abcd1001 & useunicode = true & characterencoding = gb2312 ";
Con = drivermanager. getconnection (dburl );

// Create a JDBC Statement
Stmt = con. createstatement ();

// Add a new record
// Stmt.exe cuteupdate ("insert into books (ID, name, title, price) values ('123', 'Tom ', 'tomcat le le', 999 )");

// Query records
Rs = stmt.exe cutequery ("select * from user ");

// Output query results
Out. println ("<Table border = 1 width = 400> ");
While (Rs. Next ())
{
String col1 = Rs. getstring (1 );
String col2 = Rs. getstring (2 );
String col3 = Rs. getstring (3 );
String col4 = Rs. getstring (4 );
// Print the displayed data
Out. println ("<tr> <TD>" + col1 + "</TD> <TD>" + col2 + "</TD> <TD>" + col3 + "</ TD> <TD> "+ col4 +" </TD> </tr> ");
}
Out. println ("</table> ");

// Close the database connection
Rs. Close ();
Stmt. Close ();
Con. Close ();
%>
</Body>
</Html>

Code 2:
<% @ Page contenttype = "text/html; charset = gb2312" %>
<% @ Page import = "Java. SQL. *" %>
<HTML>
<Body>
<%
Class. forname ("org. gjt. Mm. MySQL. Driver"). newinstance ();
String url = "JDBC: mysql: // localhost/chclyb? User = root & Password = abcd1001 & useunicode = true & characterencoding = 8859_1 ";
// Testdb is your database name
Connection conn = drivermanager. getconnection (URL );
Statement stmt = conn. createstatement (resultset. type_scroll_sensitive, resultset. concur_updatable );
String SQL = "select * from test ";
Resultset rs1_stmt.exe cutequery (SQL );
While (Rs. Next () {%>
The content of the first field is: <% = Rs. getstring (1) %>
The second field is: <% = Rs. getstring (2) %> <br>
<% }%>
<% Out. Print ("database operation successful, congratulations"); %>
<% Rs. Close ();
Stmt. Close ();
Conn. Close ();
%>
</Body>
</Html>

I can work with the above two sections of code. Pay attention to the class. forname (…) of the above two codes (...) Different Parts:

Class. forname ("com. MySQL. JDBC. Driver"); // in program code 1
Class. forname ("org. gjt. Mm. MySQL. Driver"). newinstance (); // in program code 2

The code in program code 2 is reserved to be compatible with the old MySQL version, and is now driven by class. forname ("com. MySQL. JDBC. Driver.
Can the above Code be successfully copied to the Tomcat virtual path? I'm afraid not. Another important thing is not done: Install the MySQL JDBC driver for Tomcat! This problem is not mentioned in most articles on the Internet. If I post a piece of code to connect to MySQL, Let's connect to the database. It cannot be connected!
MySQL driver needs to download from the Internet, download completed, copy to E:/tomcat 5.0/common/lib path (I downloaded the file name is mysql-connector-java-3.2.0-alpha-bin.jar ), install the Tomcat service with a stop/start command. The file name can be changed without affecting usage. Because this is a compressed package, Tomcat can automatically recognize the content in the package and register the relevant classes. Class. forname (...) You can find the answer in the package because there are two paths in the package: COM/MySQL/jdbc/driver and org/gjt/MM/MySQL/driver, the class files below are the same.

To test the connection to the Oracle Database Using JSP, we can say that, but we must also copy and register the oracle JDBC driver in the E:/tomcat 5.0/common/lib path, this driver file does not need to be inquired about on the Internet and looks like a dish (I was like this at the beginning !), The file is in the path E:/Oracle/ora90/jdbc/lib of Oracle, and the file name is classes12.jar. My sample code is as follows:

Program code 3:

<% @ Page contenttype = "text/html; charset = gb2312" %>
<% @ Page import = "Java. SQL. *" %>
<HTML>
<Body>
<%
Class. forname ("oracle. JDBC. Driver. oracledriver"); //. newinstance ();
String url = "JDBC: oracle: thin: @ 10.0.1.1: 1521: mydb"; // mydb indicates the SID of the database and 10.0.1.1 indicates the IP address of the database server.
String user = "system ";
String Password = "abcd1001 ";
Connection conn = drivermanager. getconnection (URL, user, password );
Statement stmt = conn. createstatement (resultset. type_scroll_sensitive, resultset. concur_updatable );
String SQL = "select * from test"; // test is the table name. You must create this table and then send several records.
Resultset rs1_stmt.exe cutequery (SQL );
While (Rs. Next ())
{
%>
The content of the first field is: <% = Rs. getstring (1) %>
The second field is: <% = Rs. getstring (2) %> <br>
<%
}
Out. Print ("the database operation is successful. Congratulations. ");

Rs. Close ();
Stmt. Close ();
Conn. Close ();
%>
</Body>
</Html>

About Oracle Database: I installed Oracle9i Enterprise Edition (under Windows Server 2003). After installation, Oracle will use port 80 to run its own apache1.3 web server, it also occupies port 8080 to run the tnslsnr Service (tnslsnr. EXE file in X:/Oracle/ora90/bin), provide some logs and documents. Therefore, we recommend that you do not install IIS on the Oracle server. If IIS is enabled first, the HTTP service of Oracle cannot be started. You cannot use port 8080. The problem is that if Tomcat is also installed, Tomcat uses port 8080 by default, so it is best to set the port to another value when installing Tomcat, such as 8088.
To create a table in Oracle, you must first create a tablespace and add a data file (or not, because a data file is created by default when creating a tablespace. Can be added later as needed ). Create another user (the "solution" and user name will be the same in the future ). When creating a table, you need to select the table space and table name. It is best to create the table under the corresponding solution and match them for better management. If you create a table under another "Scheme", you must use the [Scheme]. [Table name] format to access the table using the SQL * Plus statement.

So many things are my notes. Some things are really hard to understand, because they are new to some non-Microsoft things. I hope the experts will point out what I have said and what I have not understood. Don't accept anything from me for new users. Think about it for a moment. In your environment, what I said is whether debugging is successful.

My other article on my personal website: "100,000 why" computer learning network: http://www.why100000.com.

Zhang Qing zhangking@hotmail.com
Http://why100000.com
Http://sogo99.com
2005.6.1