JSP connection MySQL, Oracle database Memo (Windows platform)

js|mysql|oracle|window| Data | Database JSP environment is currently the most popular Tomcat5.0. Tomcat5.0 has a WEB server of its own, and if it is a test, there is no need to integrate Tomcat with IIS or Apache. JSP testing can be done under Tomcat's own Web server.
You need to install the JDK before installing Tomcat5.0 (you must install the JDK if it is Windows Server 2003 because Windows Server 2003 is-windows Server 2003 release with no JVM and MS and SUN Just the right thing to do). After installation, you set up 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
Path in Add.; E:\j2sdk1.4.2_04\bin;
Catalina_home = E:\Tomcat 5.0
Tomcat_home = E:\Tomcat 5.0

Explained as follows: My JDK version is 1.4.2, installed under the E:\j2sdk1.4.2_04 folder.

    Environment Variables Java_home and Catalina_home must be added (at least that's what the references say). CLASSPATH also said to add, but I did not add seems to have no effect. Path in Add ".; E:\j2sdk1.4.2_04\bin; " Also, I can run without JSP, but manually compiling in a DOS window. Java classes to. class files have an effect because the Javac.exe compilation command file cannot be found. As for the point added to the path ".", I was required under resin, and it was not certain that it was necessary under Tomcat.
    We also found that the values of Catalina_home and Tomcat_home are the same, and I estimate that tomcat_home is used by the older version of TOMCAT and now uses Catalina_home. But these 2 seem not to be incompatible, all add, or add any one of them, JSP does not hand influence. But I suggest using a catalina_home to forget it.

    Tomcat5.0 Setting a virtual path is convenient because it can be managed in a WEB environment. Http://[url]:8080/admin can enter the Admin interface (admin admin password can be set during Tomcat installation), in Tomcat Server-service (Catalina)-host (localhost) You can add a context, similar to the virtual path in IIS
: Document Base refers to a file path from the actual E:\Tomcat 5.0\webapps\root, which refers to a virtual pathname from virtual "/", such as "/ MyTest ". Here Tomcat seems to design a bit imperfect, as long as add a new context, save, and then point "Commit Changes" button, and then point to see the context, the interface will be back to the login interface, but sometimes the page is wrong, must be manually entered the Http://[url ]:8080/admin. The Context,apache Tomcat service must be "off/start" (in the service of the Windows Management Console). There is information that in the Http://localhost:8088/manager management interface can start and Stop to restart the corresponding context, but I Stop after is not start up. Click the Undeploy, the context deleted, note: The context of the physical folder also deleted!! So you made an application and moved here, and if you're not careful, you delete it-worse than the virus. Be careful!!

Down I said the JSP connection MySQL problem. If you're using JSP to connect to Access, I feel like I'm wearing a dress on a man. Access and ASP are the perfect couple. JSP is reasonable to connect to MySQL or Oracle. The reason I think is: use JSP to do development, its advantage is across the OS platform, if the use of JSP + Access combination, Cross-platform advantages will not be. ASP and Access has been working very well, but also simple, why should you be awkward to use JSP development? Of course, if you are very familiar with JSP (or very unfamiliar to the ASP), or very much like JSP (or very dislike ASP), or you have a lot of data accumulation in Access, you have to use the jsp+access technology is not no good.
MySQL installation is very simple, basic installation can be used normally, set up a database, set up users, can be completed in the MySQL environment. is used to use the GUI interface of people, feel uncomfortable. The internet can be downloaded to some MySQL graphical user interface management tools, but it seems not free.

Use JSP to connect MySQL database, data from the table, write a piece of code to test:

Program Code 1:
<!--first import some of the necessary packages-->
<%@ page import= "java.io.*"%>
<%@ page import= "java.util.*"%>
<!--tell the compiler to use SQL package-->
<%@ page import= "java.sql.*"%>
<!--set Chinese output-->
<%@ page contenttype= "text/html; charset=gb2312 "%>

<title>mysql test</title>
<body>
<%
Connection con;
Statement stmt;
ResultSet rs;

Load the driver, the following code for loading the MySQL driver
Class.forName ("Com.mysql.jdbc.Driver");

Registering 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
Establishing 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 Declaration
stmt = Con.createstatement ();

Add new record
Stmt.executeupdate ("INSERT into Books (Id,name,title,price) VALUES (' 999 ', ' Tom ', ' Tomcat Bible ', 44.5)");

Query records
rs = Stmt.executequery ("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 data that is displayed
Out.println ("<tr><td>" +col1+ "</td><td>" +col2+ "</td><td>" +col3+ "</td> <td> "+col4+" </td></tr>);
}
Out.println ("</table>");

Close database Links
Rs.close ();
Stmt.close ();
Con.close ();
%>
</body>

Program Code 2:
<%@ page contenttype= "text/html;charset=gb2312"%>
<%@ page import= "java.sql.*"%>
<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 for 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 rs=stmt.executequery (SQL);
while (Rs.next ()) {%>
The first field content is: <%=rs.getstring (1)%>
The second field contains: <%=rs.getstring (2)%><br>
<%}%>
<%out.print ("Successful database operation, congratulations");%>
<%rs.close ();
Stmt.close ();
Conn.close ();
%>
</body>

I can work with all of the above 2 pieces of code. Note the differences in the Class.forName (...) section of the above 2-paragraph code:

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 for compatibility with the old version of MySQL, and is now used Class.forName ("Com.mysql.jdbc.Driver"); Driven up.
Will the above code be copied to Tomcat's virtual path to execute successfully? I'm afraid not. Because there is one important thing that has not been done: you need to install MySQL's JDBC driver for Tomcat5.0! This problem, the most articles on the internet did not say, to paste a piece of code to connect MySQL, let us go to connect the database, not even on the!
MySQL driver needs to download from the Internet, download complete, copy to E:\Tomcat 5.0\common\lib path (i download the filename is Mysql-connector-java-3.2.0-alpha-bin.jar), put Tomcat Service to a Stop/start installation is over. The file name can be changed, does not affect the use, because this is a compressed package, Tomcat can automatically recognize the contents of the package, and register the relevant classes in. The Class.forName (...) part of the previous section can be found in the package, because there are com/mysql/jdbc/driver and Org/gjt/mm/mysql/driver 2 paths in the package, and the following class files are the same.

Use JSP to connect Oracle database test, but also have to copy and register Oracle's JDBC drive under the E:\Tomcat 5.0\common\lib path, this driver file does not need to inquire around the internet, look very vegetable appearance (I was this!), This file is in Oracle's E:\oracle\ora90\jdbc\lib path, and the filename is Classes12.jar. My sample code is as follows:

Program Code 3:

<%@ page contenttype= "text/html;charset=gb2312"%>
<%@ page import= "java.sql.*"%>
<body>
<%
  class.forname ("Oracle.jdbc.driver.OracleDriver");  //.newinstance ();
  String url= "Jdbc:oracle:thin:@10.0.1.1:1521:mydb";//mydb sid,10.0.1.1 for database server IP
  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 name of the table, you create the table first, and then send a few records
  ResultSet Rs=stmt.executequery ( SQL);
  while (Rs.next ())
  {
%>
    The first field content is: <%=rs.getstring (1)%>
    The second field content is: <%=rs.getstring (2)%> <br>
<%
 }
  Out.print (" Database operation successful, congratulations. ");

Rs.close ();
Stmt.close ();
Conn.close ();
%>
</body>

About Oracle Databases: I am installing the Oracle9i Enterprise Edition (under Windows Server 2003), and after installation, Oracle will take up 80 ports to run its own Apache1.3 Web server, and also occupy 8080 port operations Tnslsnr Service (Tnslsnr. EXE file under X:\oracle\ora90\BIN), providing some log and documentation. Therefore, it is recommended that you install an Oracle machine without installing IIS, and that if IIS starts up, the HTTP service of Oracle will not start. Also cannot occupy port 8080. The problem is that if you also install Tomcat,tomcat by default 8080 ports, it is best to set the port to a different value, such as 8088, when installing Tomcat.
To create a table in Oracle, you must first create a table space, or you can add a data file (or not, because a data file is already built by default when you create a tablespace.) Can be added later as needed. Create a second user (the next "scenario" and user name). When you set up a table, you choose the table space and the scheme name, it is best to set the table under the corresponding scheme, to match them up, good management later. If you set up a table under another "scenario," use [scenario] when accessing with a sql*plus statement. The format of the [table name].

Sparse said so much, was something of my note-class. There are some things that you really don't understand, because you've just come in contact with something other than Microsoft. I hope the master points out what I said and understood the wrong place. Novice also don't completely receive my things, think about it, see in your environment, I said can debug successfully.