The connection method of Js|mysql Jsp,javabean,mysql
Using JSP to realize the connection between Web and database:
(1). Complete the Environment settings, import the java.sql package, the following command:
Import java.sql.*
(2). Load Drive
(3). Connect to a database
(4). Statement interface
(5). Get the result set
1. Test environment:
Windows 2003
J2sdk1.4.2_06
TOMCAT 5.0
Mysql-4.0.23-win
Mm.mysql-2.0.4-bin.jar
2. Establish database and table
Build a publish database in MySQL and build a book table. Add Id,title,price to book
3. JavaBean Code: Dbconn.java
Package Border;
Import java.sql.*; Complete environment settings, import java.sql Package
public class Dbconn {
Public Dbconn ()
{
}
DECLARE variable
PRIVATE Connection conn = null;
Private ResultSet rs = null
Private String Server = "127.0.0.1"; Can ' t use localhost, your must use IP or CNAME
Private String port = "3306"; Change to your port
Private String db = "Publish"; Change to your DB name
Private String user = "root"; Change to your username
Private String pass = "root"; Change to your password
Private String drivername= "Org.gjt.mm.mysql.Driver"; MySQL Driver
Private String url= "jdbc:mysql://" +server+: "+port+"/"+db+"? useunicode=true&characterencoding=gbk&user= " +user+ "&password=" +pass;
Public Connection Getconn () {//get database Connection
try{
Class.forName (drivername). newinstance (); Load Drive
conn = Drivermanager.getconnection (URL); Connecting to a database
}
catch (Exception e) {
E.printstacktrace ();
}
Return conn;
}
Public ResultSet ExecuteSQL (String str) {
try{
Statement stmt = Conn.createstatement (); Statement interface
rs = stmt.executequery (str); Get result set
}
catch (Exception e) {
E.printstacktrace ();
}
Return RS;
}
}
Compile Javac Dbconn.java, and place the compiled file Dbconn.class under the directory "your project" \web-inf\classes\border\
4. Invoke Java Bean's JSP file test.jsp
<%@ page contenttype= "TEXT/HTML;CHARSET=GBK" import= "java.sql.*"%>
<jsp:usebean id= "Border" scope= "page" class= "Border.dbconn"/>
<%
ResultSet rs = null;
Connection conn = null;
conn = Border.getconn ();
rs = Border.executesql ("SELECT * from book");
%>
<body>
<br>
<H2 align= "center" > My i-Jsp JavaBean Mysql <br>
<table border= "1" align= "Center" >
<tr>
<th>
Id
</th>
<th>
Title
</th>
<th>
Price
</th>
</tr>
<%
while (Rs.next ()) {
%>
<tr>
<th>
<%=rs.getstring ("id")%>
</th>
<th>
<%=rs.getstring ("title")%>
</th>
<th>
<%=rs.getstring ("Price")%>
</th>
</tr>
<%}%>
<%
Rs.close ();
Conn.close ();
%>
</table>
<form name= "Form1" method= "Post" action= "update.jsp" >
<table width= "210" border= "1" align= "center" cellpadding= "0" cellspacing= "0" >
<tr>
<TD width= ">title:</td>"
<TD width= "127" ><label>
<input name= "title" type= "text" id= "title" >
</label></td>
</tr>
<tr>
<td>price:</td>
<td><label>
<input name= "Price" type= "text" id= "Price" >
</label></td>
</tr>
<tr>
<td><div align= "Right" >
<label>
<input type= "Submit" name= "Submission" value= "submitted" >
</label>
</div></td>
<td><label>
<input type= "reset" name= "Submit2" value= "reset" >
</label></td>
</tr>
</table>
</form>
</body>
5. update.jsp Write to Database
<%@ page contenttype= "TEXT/HTML;CHARSET=GBK" import= "java.sql.*"%>
<% request.setcharacterencoding ("GBK"); %>
<jsp:usebean id= "Border" scope= "page" class= "Border.text"/>
<%
Connection conn = null;
conn = Border.getconn ();
String tit=request.getparameter ("Title");
String prc=request.getparameter ("price");
Statement stmt = Conn.createstatement ();
Stmt.executeupdate ("INSERT into book (title,price) VALUES (' +tit+" ', ' "+prc+") ");
%>
<jsp:forward page= "text.jsp"/>
6. Provide several functions:
(1). getconnection
public static Connection getconnection (String URL,
String User,
String password)
Throws SQLException
Attempts to establish a connection to the given database URL. The DriverManager attempts to select a appropriate driver from the set of registered JDBC drivers.
Parameters:
Url-a database URL of the form Jdbc:subprotocol:subname
User-the database user on whose behalf the connection is being made
password-the user ' s password
Returns:
A connection to the URL
Throws:
Sqlexception-if A database access error occurs
(2). createstatement
Public Statement createstatement ()
Throws SQLException
Creates a Statement object for sending SQL statements to the database. SQL statements without parameters are normally executed using Statement. If the same SQL statement is executed many times, it could be more efficient to use a PreparedStatement object.
Result sets created using the returned Statement object would by default is type type_forward_only and have a concurrency l Evel of Concur_read_only.
Returns:
A new default Statement object
Throws:
Sqlexception-if A database access error occurs
(3). executequery
Public ResultSet executequery (String sql)
Throws SQLException
Executes the given SQL statement, which returns a single ResultSet object.
Parameters:
Sql-an SQL statement to is sent to the database, typically a static SQL SELECT statement
Returns:
A ResultSet object that contains the data produced by the given query; Never null
Throws:
Sqlexception-if A database access error occurs or the given SQL statement produces anything other than a single Resultse T object
(4). getString
public string getString (string columnName)
Throws SQLException
Retrieves the value of the designated column in the current row of this ResultSet object as a String in the Java programmi ng language.
Parameters:
Columnname-the SQL name of the column
Returns:
the column value; If the value is SQL NULL, the value returned is null
Throws:
Sqlexception-if A database access error occurs
(5). Next
public Boolean Next ()
Throws SQLException
Moves the cursor down one row to its current position. A ResultSet Cursor is initially positioned before the "A"; The ' the ' to ' method ' next makes the ' the ' the current row; The second call makes the second row, "The current row," and "on."
If an input stream was open for the "Current row", a call to the method next would implicitly close it. A ResultSet object ' s warning chain is cleared when a new row is read.
Returns:
True if the new current row is valid; False if there are no more rows
Throws:
Sqlexception-if A database access error occurs
7. ok! Now game is over~~
8. If you feel satisfied, want to reprint or collect this article, I am very grateful, but please note the author: border (border@hacker.cn)
http://blog.csdn.net/border1985
Finally welcome you to visit--China Safety Information Network--http://www.hacker.cn/
