網(wǎng)絡(luò)編程教程模板無憂>建站教程>網(wǎng)絡(luò)編程>JSP教程>

JSP的MYSQL數(shù)據(jù)庫的代碼_JSP教程

教程Tag：暫無Tag,歡迎添加,賺取U幣!

推薦：jsp驗證碼使用
jsp驗證碼使用 yanzhe,jsp %@ page autoFlush=false import=java.awt.*,java.awt.image.*,com.sun.image.codec.jpeg.*,java.util.*% %@ page import=org.apache.commons.lang.RandomStringUtils% % RandomStringUtils rs=new RandomStringUtils(); Str

最近研究了jsp的數(shù)據(jù)庫操作問題。本來我是要拿oracle數(shù)據(jù)庫來測試的。但是由于機子的內(nèi)存限制，就換成了mysql數(shù)據(jù)庫了。

第一次用jsp編數(shù)據(jù)庫的連接，發(fā)現(xiàn)問題還真不少。剛開始是jdbc配置就有問題，后來數(shù)據(jù)庫連接好了，中文設(shè)置也是個問題。后來還有sql語句的寫法問題出現(xiàn)了不少。經(jīng)過三天的努力，這些問題都給解決掉了。

下面把我這三天測試的代碼寫給大家看看：

第一個是javabean的數(shù)據(jù)庫的連接代碼，文件名字是LoginData.java：

package data;
import java.sql.*;

public class LoginData{
//定義mysql的連接地址
//String url="jdbc:odbc:logindemo";
Connection conn=null;
//Statement stmt=null;
//DatabaseMetaData dbmd=null;

public LoginData(){
this.connect();
}

public Connection getConn(){
return this.conn;
}
public boolean connect(){
try{
//使用JDBC橋創(chuàng)建數(shù)據(jù)庫連接
Class.forName("org.gjt.mm.mysql.Driver").newInstance();

//使用DriverManager類的getConnection()方法建立連接
//第一個參數(shù)定義用戶名，第二個參數(shù)定義密碼
this.conn=java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/logindemo?useUnicode=true&characterEncoding=gb2312","root","123456");
//stmt=conn.createStatement();
//dbmd=conn.getMetaData();
}catch(Exception ex){
ex.printStackTrace();
return false;
}
return true;
}
}

下面是測試代碼的界面代碼dbcode.jsp：

<%@ page contentType="text/html; charset=gb2312" pageEncoding="GB2312" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>無標題文檔</title>
<script type="text/JavaScript">

</script>
</head>

<table width="664" height="86" border="2">
<tr>
<td width="168" height="41">id</td>
<td width="223">學號</td>
<td width="249">姓名</td>
</tr>
<tr>
<td height="35" align="center" valign="middle"><input name="id" type="text" id="id" onblur="checknull" value="<%=session.getAttribute("id")%>" /></td>
<td align="center" valign="middle"><label>
<input name="xuehao" type="text" id="xuehao" />
_1~10
</label></td>
<td align="center" valign="middle"><label>
<input name="name" type="text" id="name" />
_1~10
</label></td>
</tr>
</table>
<p>姓名：
<input name="selectnametext" type="text" id="selectnametext" />
<input name="selectname" type="submit" id="selectname" value="查詢數(shù)據(jù)" />
   姓名：
<input name="deletename" type="text" id="deletename" />
<input name="deletenum" type="submit" id="deletenum" value="刪除數(shù)據(jù)" />
<%=session.getAttribute("deletenames")%></p>
<p>學號：
<input name="xuehao2" type="text" id="xuehao2" />
  姓名：
<label>
<input name="name2" type="text" id="name2" />
<input name="select_name" type="submit" id="select_name" value="查詢" />
</label>
<input name="updatanum" type="submit" id="updatanum" value="修改" />
</p>
<%
if(request.getAttribute("data")!=null){
String[][] data=(String[][])request.getAttribute("data");
out.println("查詢結(jié)果顯示如下：");
if(data[0].length>1){
out.print("<table border='2' width='70%'>");
out.print("<tr><td>序號</td><td>學號</td><td>姓名</td></tr>");
for(int i=0;i<data.length;i++){
out.print("<tr>");
for(int j=0;j<3;j++){
out.print("<td>"+ data[i][j] +"</td>");
}
}
out.print("</table>");
}else{
out.println(data[0][0]);
}
}
%>
</form>
<p> </p>
<p>  </p>
</body>

</html>

下面是上面的界面調(diào)用的jsp頁面來處理數(shù)據(jù)庫的代碼，包括創(chuàng)建表，表的增刪該等操作：

<%
//向數(shù)據(jù)庫里的表添加數(shù)據(jù)的代碼

String insertnum=request.getParameter("insertnum");
if(insertnum!=null){
try{
String xuehao=request.getParameter("xuehao");
String name=request.getParameter("name");
String insertsql="insert into userinfo(xuehao,name) values(?,?)";
Connection conn=loginData.getConn();
PreparedStatement pstmt=conn.prepareStatement(insertsql) ;
pstmt.setString(1,xuehao);
pstmt.setString(2,name);
pstmt.execute();
session.setAttribute("insertsql","成功插入一條數(shù)據(jù)");
pstmt.close();
conn.close();
}catch(Exception ex){
ex.printStackTrace();
session.setAttribute("insertsql","插入數(shù)據(jù)不成功，請檢查后重新插入");
}
}
%>

<%
//批量添加數(shù)據(jù)

String inserttennum=request.getParameter("inserttennum");
if(inserttennum!=null){
try{
String xuehao=request.getParameter("xuehao");
String name=request.getParameter("name");
Connection conn=loginData.getConn();
Statement stmt=conn.createStatement();
int j=100000;
for(int i=0;i<j;i++){
String insertsql="insert into userinfo(xuehao,name) values('"+ xuehao + i + "','"+ name + i +"')";
stmt.execute(insertsql);
}
session.setAttribute("insertten","成功插入"+ j +"條數(shù)據(jù)");
conn.close();
stmt.close();
}catch(Exception ex){
ex.printStackTrace();
session.setAttribute("insertten","插入數(shù)據(jù)不成功，請檢查后重新插入");
}
}
%>

<% //查詢表的數(shù)據(jù)代碼

String selectname=request.getParameter("selectname");
String[][] data;
if(selectname!=null){
try{
Connection conn=loginData.getConn();
Statement stmt=conn.createStatement();
ResultSet rs;
String selectnametext=request.getParameter("selectnametext");
String sqlcount="select count(*) from userinfo where name like '%" + selectnametext +"%'";
rs=stmt.executeQuery(sqlcount);
rs.next();
int rowCount=rs.getInt(1);
if(rowCount>0){
data=new String[rowCount][3];
String sql="select * from userinfo where name like '%" + selectnametext +"%'";
rs=stmt.executeQuery(sql);
int row=0;
while(rs.next()){
for(int i=0;i<3;i++){
data[row][i]=rs.getString(i+1);
}
row++;
}
}else{
data=new String[1][1];
data[0][0]="找不到與"+ selectnametext + "相關(guān)內(nèi)容";
}
request.setAttribute("data",data);
conn.close();
stmt.close();
rs.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
%>

<%
//刪除數(shù)據(jù)代碼

String deletenum=request.getParameter("deletenum");
if(deletenum!=null){
try{
String deletename=request.getParameter("deletename");
Connection conn=loginData.getConn();
Statement stmt=conn.createStatement();
String deletesql="delete from userinfo where name like '%"+ deletename +"%'";
int deletenums=stmt.executeUpdate(deletesql);
session.setAttribute("deletenames","成功刪除"+ deletenums +"條數(shù)據(jù)");
stmt.close();
conn.close();
}catch(Exception ex){
ex.printStackTrace();
session.setAttribute("deletenames","刪除不成功");
}
}
%>

<jsp:forward page="dbcode.jsp" />
</body>
</html>

分享：jsp自動生成靜態(tài)文件(html)
jsp自動生成靜態(tài)文件(html): html.jsp %@ page contentType=text/html;charset=utf-8% %@page import=cn.nbaia.newPage.newsPage.*,cn.nbaia.newPage.newsImg.*,java.util.*,cn.nbaia.newPage.columnPage.*,cn.nbaia.company.*,cn.nbaia.link.* % %@page