How to deal with JSP connection MySQL database problem

When you are faced with a JSP connection MySQL database problem, you first need to create a username table in the MySQL database, the table inside the creation of two character fields, field names are: Uid,pwd, and then insert a few test data.

The following two ways to implement JSP connection MySQL database

The first way, with JSP implementation:

<%@ page contenttype= "text/html;
charset=gb2312 "Language=" Java "
Import= "Java.sql.*"%>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 ">
<%//*******************************
****************
JDBC_ODBC connect MySQL database, do not need to set data source ***********
**********************/
Database Connection code started ******/
The following items please modify string server= "localhost" by yourself;
The MYSQL server address string dbname= "test";
MYSQL database name string user= "root";
MYSQL Database Login Username string pass= "chfanwsp";
MYSQL Database Login Password string port= "3306";
The port number of the SQL Server server.
Default is 1433//database connection string
String url = "jdbc:mysql://" +server+: "+port+"/"+dbname+"?
User= "+user+" &password= "+pass+" &useunicode
=true&characterencoding=gb2312 ";
Load driver Class.forName ("Org.gjt.mm.mysql.Driver"). newinstance ();
Establish a Connection connection conn= drivermanager.getconnection (URL);
Create a Statement object statement stmt=conn.createstatement
(Resultset.type_scroll_sensitive,
resultset.concur_updatable);
Database Connection code end *******
String sql= "SELECT * from username";
ResultSet rs=stmt.executequery (SQL);
Rs.first (); while (Rs.next ()) {out.print ("User name:");
Out.print (rs.getstring ("UID") + "password:");
Out.println (rs.getstring ("pwd") + "<br>");}
Rs.close (); Stmt.close (); Conn.close ();%>

The second way, using JavaBean to achieve:

Dbconnmysql.java compiled class files should be placed in the Web-infclassesconn directory.

Package conn; Import Package Imports java.sql.*;
The class public class Dbconnmysql for importing database operations
Constructs the method, initializes {private Connection conn;
Connection object private Statement stmt;
Statement Object Private ResultSet RS;
Result set object private String mysqldriver;
MYSQL Server Driver string private string Mysqlurl;
MYSQL Server Connection String//*********************************
Drive with Org.gjt.mm.mysql.Driver
* This method takes a variety of parameters for the connection, making up the connection string,
And then establish the connection * Server;dbname,user,pass,port
Represents the address of the MySQL server, respectively,
* Database, username, password, port
**********************************/
Public Connection Getconntomysql
(String server,string dbname,string user,string pass,string Port)
{//mysql Driver mysqldriver = "Org.gjt.mm.mysql.Driver";
Mysqlurl = "jdbc:mysql://";
Part of the connection string try{//the complete connection string Mysqlurl
=mysqlurl+server+ ":" +port+ "/" +dbname+ "? user=
"+user+" &password= "+pass+" &useunicode
=true&characterencoding=gb2312 ";
Class.forName (mysqldriver); conn
= Drivermanager.getconnection (Mysqlurl);}
catch (Exception e) {System.out.println
("Operation database error, please check carefully");
System.err.println (E.getmessage ());} Return conn;}
Closes the database connection public void Close () {try{//rs.close ();
Stmt.close (); Conn.close ();} catch (SQLException
SqlException) {sqlexception.printstacktrace ();}}}

This file is only the implementation of the database connection, and then write a test file, that is, SQL statements from the database query the record to verify the success of our database connection.

The connmysql.jsp file source code is as follows:

<meta http-equiv= "Content-type" content= "text/html;
charset=gb2312 "><%@ page contenttype=" text/html;
charset=gb2312 "language=" java import= "java.sql.*"%>
<jsp:usebean id= "dbconn" scope= "page" class= "Conn. Dbconnmysql "/>
<%//The following items please modify string server= "localhost";
The MYSQL server address string dbname= "test";
MYSQL database name string user= "root";
MYSQL Database Login Username string pass= "chfanwsp";
MYSQL Database Login Password string port= "3306";
The port number of the SQL Server server, which defaults to 1433Connection
Conn=dbconn.getconntomysql
(Server,dbname,user,pass,port);
Statement stmt=conn.createstatement
(Resultset.type_scroll_insensitive,
RESULTSET.CONCUR_READ_ONLY);
String sql= "SELECT * from username";
String sql1= "INSERT into username (UID,PWD) values
(' Dream Years ', ' Dream Years ') "; Stmt.executeupdate (SQL1);
ResultSet rs=stmt.executequery (SQL); while (Rs.next ())
{Out.print ("username:"); Out.print (rs.getstring ("UID") + "password:");
Out.println (rs.getstring ("pwd") + "<br>");}
Rs.close ();//stmt.close ();//conn.close ();D bconn.close ();%>