First, software download
Download the following two tools directly to MySQL website: mysql-5.1.32-win32.msi, Mysql-gui-tools-5.0-r17-win32.msi
The former is the MySQL installation file, the latter is the MySQL tool installation file, including JDBC.
Second, the Environment configuration
Copy Mysql-connector-java-5.0.4-bin.jar from Mysql\mysql Tools for 5.0\java\lib to D:\Tomcat 6.0\lib and add classpath inside D:\ Tomcat 6.0\lib\mysql-connector-java-5.0.4-bin.jar can be.
The purpose of this configuration is to get your Java application to find the driver to connect to MySQL.
Copy this step is to configure the JSP connection database driver.
The application loads the MySQL driver code as follows:
try{
Class.forName ("Com.mysql.jdbc.Driver");
}
Catch (Exception e) {}
(2) Establish a connection with the specified database.
If the application and the MySQL server are on the same computer, the code for the connection between the application and the database car is as follows:
try{
String uri= "Jdbc:mysql://localhost:3306/car";
String user= "root";
String password= "123";
Con=drivermanager.getconnection (Uri,user,password);
}
Catch (SQLException e) {}
Where the root user has access to the database car,root the user's password is 123. If the root user does not have a password set, the above string password= "123"; Change to String password= "";
Three: The database query in the JSP page
Queries to implement a database in a JSP page are mainly connected to the Java Code section of the database and the HTML page Code section. To implement this page, we go through a three-step
1, write the code to connect the database
2, write the code to display the page
3, the code to connect the database into the appropriate location of the page code.
1. Code to connect to the database
1.1 Importing SQL Packages
<% import= "java.sql.*"%>
1.2 Connecting, querying, shutting down the database
How do I check if there are any elephants in the fridge? 1, open the refrigerator door. 2, take a look. 3, close the refrigerator door. This is the process .....
[HTML]
<%
Class.forName ("Com.mysql.jdbc.Driver"). newinstance ();
String url= "Jdbc:mysql://localhost:3306/news";
String user= "root";
String password= "1234";
Configuring and connecting to a database
Connection conn = drivermanager.getconnection (URL, user, password);
Statement st = Conn.createstatement ();
Query statements, displaying the last 10 and descending order
ResultSet rs = st.executequery ("SELECT * from Data order by ID DESC LIMIT 10");
Output table Header
Out.println ("<tr><td> title </td><td> content </td><td> Time </td></tr>");
Output the results of each query sequentially
while (Rs.next ()) {
Out.print ("<tr><td>" +rs.getstring ("title") + "</td><td>" +rs.getstring ("content") + "</ Td><td> "+rs.getstring" ("date") + "</td></tr><br>");
If you use a column name, enclose the quote
}
Out.print ("</table>Disconnecting a database
Conn.close ();
%>
2. HTML code for the page
To make the page beautiful, do a little landscaping ~ to make a table to store data
[HTML]
<%@ page language= "java" import= "java.util.*, java.sql.*" pageencoding= "gb2312"%>
<%
String path = Request.getcontextpath ();
String basepath = request.getscheme () + "://" +request.getservername () + ":" +request.getserverport () +path+ "/";
%>
<%request.setcharacterencoding ("UTF-8");%>
<%response.setcharacterencoding ("UTF-8");%>
<span><span class= "Tag" ></span></span><! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<style type= "Text/css" >
table{width:800px; Margin:auto padding:5px; font-size:12px; border:0px; background: #00CCFF;}
tr{background: #fff;}
td{padding:5px;}
#title {text-align:center;}
</style>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 "/>
Database queries in <title>jsp page </title>
<body>
<form method=post>
Title: <input type= "text" name= "title"/><br/>
Content: <input type= "text" name= "content"/><br/>
<input type= "Submit" value= "submitted"/>
</form>
<table >
<tr>
<TD width= "174" id= "title" > title </td>
<TD width= "449" id= "title" > Content </td>
<TD width= "161" id= "title" > Time </td>
</tr>
<tr>
<TD width= "174" > </td>
<TD width= "449" > </td>
<TD width= "161" > </td>
</tr>
</table>
</body>
3. Put the previous two code together
Note the placement of the code when you put it.
[HTML]
<%@ page language= "java" import= "java.util.*, java.sql.*" pageencoding= "gb2312"%>
<%
String path = Request.getcontextpath ();
String basepath = request.getscheme () + "://" +request.getservername () + ":" +request.getserverport () +path+ "/";
%>
<%request.setcharacterencoding ("UTF-8");%>
<%response.setcharacterencoding ("UTF-8");%>
<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<style type= "Text/css" >
table{width:800px; Margin:auto padding:5px; font-size:12px; border:0px; background: #00CCFF;}
tr{background: #fff;}
td{padding:5px;}
#title {text-align:center;}
</style>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 "/>
<title> Untitled Document 11</title>
<body>
<%
Connecting to the MySQL database
Class.forName ("Com.mysql.jdbc.Driver"). newinstance ();
String url= "Jdbc:mysql://localhost:3306/news";
String user= "root";
String password= "1234";
Connection conn = drivermanager.getconnection (URL, user, password);
Statement st = Conn.createstatement ();
%>
<table >
<tr>
<TD width= "174" id= "title" > title </td>
<TD width= "449" id= "title" > Content </td>
<TD width= "161" id= "title" > Time </td>
</tr>
<%
If you put the second row of the table in the while loop, you can draw the table based on the query results. The parameter is placed in the appropriate position within the <td>.
ResultSet rs = st.executequery ("SELECT * from Data order by ID DESC LIMIT 10");
while (Rs.next ()) {%>
<tr>
<TD width= "174" ><%=rs.getstring ("title")%></td>
<TD width= "449" ><%=rs.getstring ("content")%></td>
<TD width= "161" ><%=rs.getstring ("Time")%></td>
</tr>
<%}
Note the location of the "}"%>
</table>
<%
Rs.close ();
Conn.close ();
%>
</body>
Database inserts in four \jsp pages
This is how to put the elephant in the fridge of the proposition! Everybody knows that!
There are two main issues that we have to address:
1, how to get the input content
2, how to insert time.
Directly on the code
[HTML]
<%@ page language= "java" import= "java.util.*, java.sql.*" pageencoding= "gb2312"%>
<%
String path = Request.getcontextpath ();
String basepath = request.getscheme () + "://" +request.getservername () + ":" +request.getserverport () +path+ "/";
%>
<%request.setcharacterencoding ("UTF-8");%>
<%response.setcharacterencoding ("UTF-8");%>
<! DOCTYPE HTML PUBLIC "-//W3C//DTD XHTML 1.0 transitional//en" "Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ">
<style type= "Text/css" >
table{width:800px; Margin:auto padding:5px; font-size:12px; border:0px; background: #00CCFF;}
tr{background: #fff;}
td{padding:5px;}
#title {text-align:center;}
</style>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 "/>
<title> Untitled Document 11</title>
<body>
<form method=post>
Title: <input type= "text" name= "title"/><br/>
Content: <input type= "text" name= "content"/><br/>
<input type= "Submit" value= "submitted"/>
</form>
<%
Connecting to the MySQL database
Class.forName ("Com.mysql.jdbc.Driver"). newinstance ();
String url= "Jdbc:mysql://localhost:3306/news";
String user= "root";
String password= "1234";
Connection conn = drivermanager.getconnection (URL, user, password);
Statement st = Conn.createstatement ();
%>
<table >
<tr>
<TD width= "174" id= "title" > title </td>
<TD width= "449" id= "title" > Content </td>
<TD width= "161" id= "title" > Time </td>
</tr>
<%
ResultSet rs = st.executequery ("SELECT * from Data order by ID DESC LIMIT 10");
while (Rs.next ()) {%>
<tr>
<TD width= "174" ><%=rs.getstring ("title")%></td>
<TD width= "449" ><%=rs.getstring ("content")%></td>
<TD width= "161" ><%=rs.getstring ("Time")%></td>
</tr>
<%}%>
</table>
<%
Gets the input from the name in input. Time is the date and time format defined
String Gettitle=request.getparameter ("title");
String getcontent=request.getparameter ("content");
Java.text.SimpleDateFormat time = new Java.text.SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss");
String insertsql = "INSERT into data (title, content, Time) Values (' +gettitle+" ', ' "+getcontent+" ', ' "+time.format") (New J Ava.util.Date ()) + "')";
St.executeupdate (Insertsql);
%>
<%
Rs.close ();
St.close ();
Conn.close ();
%>
</body>
Example: Connect MySQL database, delete and check the operation record (good)
To copy the database jar package to the Web-inf\lib under the project
[Java] View plain copy print?
Import Java.sql.connection;//java Package
public class DbConnection
{
Private String dbdriver= "Com.mysql.jdbc.Driver";
Private String dburl= "JDBC:MYSQL://[IP address]:[port number [database name]"//change according to actual situation
Private String dbuser= "root";
Private String dbpass= "root";
Public Connection Getconn ()
{
Connection Conn=null;
Try
{
Class.forName (Dbdriver);
}
catch (ClassNotFoundException e)
{
E.printstacktrace ();
}
Try
{
conn = Drivermanager.getconnection (Dburl,dbuser,dbpass);//note is three parameters
}
catch (SQLException e)
{
E.printstacktrace ();
}
Return conn;
}
}
2. Insert operation
[Java] View plain copy print?
public int Insert ()
{
int i=0;
String sql= "INSERT into (table name) (column name 1, List 2) VALUES (?,?)";
Connection Cnn=getconn ();
try{
PreparedStatement prestmt =cnn.preparestement (SQL);
Prestmt.setstring (1, value);
Prestmt.setstring (2, value);//or: Prestmt.setint (1, value);
I=prestmt.executeupdate ();
}
catch (SQLException e)
{
E.printstacktrace ();
}
Return i;//returns the number of rows affected, 1 for execution successful
}
3. Update operation
[Java] View plain copy print?
public int Update
{
int i=0;
String sql= "Update (table name) set (column name 1) =?, Listing 2 =? where (column name) =? ";/note that there is a where condition
Connection Cnn=getconn ();
try{
PreparedStatement prestmt =cnn.preparestatement (SQL);
Prestmt.setstring (1, (value));
Prestmt.setstring (2, (value));/or: Prestmt.setint (1, value);
Prestmt.setint (3, (value));
I=prestmt.executeupdate ();
}
catch (SQLException e)
{
E.printstacktrace ();
}
Return i;//returns the number of rows affected, 1 for execution successful
}
4. Find operations
[Java] View plain copy print?
Public String Select
{
String sql = "SELECT * FROM (table name) where (column name) = (value)";
Connection CNN = Getconn ()//Here is the connection to Getconn () by the method that you write yourself
Try
{
Statement stmt = Conn.createstatement ();
ResultSet rs = stmt.executequery (SQL);
if (Rs.next ())
{
int m1 = Rs.getint (1);//or rs.getstring (1), based on the value type of the column in the database, parameter is the first column
String m2 = rs.getstring (2);
}
You can write the found value to the class, and then return the corresponding object
}
catch (SQLException e)
{
E.printstacktrace ();
}
return (the corresponding value of the variable);
}
5. Delete operation
[Java] View plain copy print?
public int Delete ()
{
String sql = "Delete from (table name) where (column name) = (value)";
int i=0;
Connection conn = Getconn ()//Here is the connection to Getconn () by the method written by yourself
Try
{
Statement stmt = Conn.createstatement ();
i = stmt.executeupdate (sql);
}
catch (SQLException e)
{
E.printstacktrace ();
}
Return i;//If 1 is returned, execution succeeds;
}
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
