When you face the problem of JSP connecting to the MySQL database, you first need to create a username table in the MySQL database, create two character fields in the table, the field names are: uid, pwd, and then insert a few pieces of test data.
There are two ways to implement JSP connection to MySql database:
The first way is to use JSP:
<%@ page contentType="text/html;
charset=gb2312" language="java"
import="java.sql.*"%>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<%//*******************************
****************
JDBC_ODBC connects to MySql database, no need to set data source****************
************************/
//********** Database connection code begins******/
//Please modify the following items yourself String server="localhost";
//The address of the MYSQL server String dbname="test";
//MYSQL database name String user="root";
//Login username for MYSQL database String pass="chfanwsp";
//MYSQL database login password String port="3306";
//SQL Server server port number,
The default is 1433//database connection string
String url="jdbc:mysql://"+server+":"+port+"/"+dbname+"?
user="+user+"&passWord="+pass+"&useUnicode
=true&characterEncoding=GB2312";
//Load the driver Class.forName("org.gjt.mm.mysql.Driver").newInstance();
//Establish a connection Connection conn= DriverManager.getConnection(url);
//Create statement object Statement stmt=conn.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// ****End of database connection code**********
String sql="select * from username";
ResultSet rs=stmt.executeQuery(sql);
//rs.first();while(rs.next()){out.print("Username:");
out.print(rs.getString("uid")+" password:");
out.println(rs.getString("pwd")+"<br>");}
rs.close();stmt.close();conn.close();%>
The second way is to use JavaBean to implement:
The Class file after DBConnMySql.java is compiled should be placed in the Web-INFclassesconn directory.
package conn; //Import package import java.sql.*;
//Import the class for database operations public class DBConnMySql
//Construction method, initialization {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//************************************
Driven by org.gjt.mm.mysql.Driver
* This method obtains various parameters required for connection to form a connection string.
Then establish a 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
("Error in operating database, please check carefully");
//System.err.println(e.getMessage());}return conn;}
//Close the database connection public void close(){try{//rs.close();
//stmt.close();conn.close();}catch(SQLException
sqlexception){sqlexception.printStackTrace();}}}
This file only implements the database connection. Next, write a test file, which uses SQL statements to query records from the database to verify whether the connection to our database is successful.
The source code of the connmysql.jsp file 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"/>
<% //Please modify the following items yourself String server="localhost";
//The address of the MYSQL server String dbname="test";
//MYSQL database name String user="root";
//Login username for MYSQL database String pass="chfanwsp";
//MYSQL database login password String port="3306";
//The port number of the SQL Server server, the default is 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();DBConn.close();%>