JSP的MYSQL數(shù)據(jù)庫的代碼_JSP教程
推薦：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">
<!--
function checknull() {
if(document.form1.id.value==null){
document.form1.id.value=1;
}
}
//-->
</script>
</head>
<body>
<jsp:useBean id="loginData" scope="page" class="data.LoginData"/>
<%
request.setCharacterEncoding("gb2312");
try{
Connection conn=loginData.getConn();
Statement stmt=conn.createStatement();
String sql="select * from userinfo";
ResultSet rs=stmt.executeQuery(sql);
rs.last();
int id=rs.getInt(1);
session.setAttribute("id",id+1);
rs.close();
stmt.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
%>
<p>數(shù)據(jù)庫的操作：</p>
<form id="form1" name="form1" method="post" action="createtable.jsp">
<label>
<input name="createtable" type="submit" id="createtable" value="創(chuàng)建表：userinfo" />
<%=session.getAttribute("userinfo")%>
<input name="insertnum" type="submit" id="insertnum" value="向表插入如下數(shù)據(jù)" /><%=session.getAttribute("insertsql")%>
<input name="inserttennum" type="submit" id="inserttennum" value="插入10條數(shù)據(jù)" />
<%=session.getAttribute("insertten")%><br />
</label>
<table width="664" height="86" border="2">
<tr>
<td width="168" height="41">id</td>
<td width="223">學(xué)號</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>學(xué)號：
<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>學(xué)號</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)建表，表的增刪該等操作：
<%@ 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>
</head>
<body>
<jsp:useBean id="loginData" scope="page" class="data.LoginData"/>
<%
request.setCharacterEncoding("gb2312");
%>
<%
//向數(shù)據(jù)庫里創(chuàng)建表的代碼
String createtable=request.getParameter("createtable");
if(createtable!=null){
try{
String sql="create table userinfo(id int primary key auto_increment,xuehao varchar(20),name varchar(20));";
Connection conn=loginData.getConn();
Statement stmt=conn.createStatement();
stmt.execute(sql);
session.setAttribute("userinfo","表userinfo創(chuàng)建成功！");
stmt.close();
conn.close();
}catch(Exception ex){
ex.printStackTrace();
session.setAttribute("userinfo","表userinfo已經(jīng)創(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
- jsp response.sendRedirect不跳轉(zhuǎn)的原因分析及解決
- JSP指令元素(page指令/include指令/taglib指令)復(fù)習(xí)整理
- JSP腳本元素和注釋復(fù)習(xí)總結(jié)示例
- JSP FusionCharts Free顯示圖表 具體實現(xiàn)
- 網(wǎng)頁模板:關(guān)于jsp頁面使用jstl的異常分析
- JSP頁面中文傳遞參數(shù)使用escape編碼
- 基于jsp:included的使用與jsp:param亂碼的解決方法
- Java Web項目中連接Access數(shù)據(jù)庫的配置方法
- JDBC連接Access數(shù)據(jù)庫的幾種方式介紹
- 網(wǎng)站圖片路徑的問題：絕對路徑/虛擬路徑
- (jsp/html)網(wǎng)頁上嵌入播放器(常用播放器代碼整理)
- jsp下顯示中文文件名及絕對路徑下的圖片解決方法
- 相關(guān)鏈接：
- 教程說明：
JSP教程-JSP的MYSQL數(shù)據(jù)庫的代碼
。