最近研究了jsp的資料庫操作問題。本來我是要拿oracle資料庫來測試的。但是由於機子的記憶體限制，就換成了mysql資料庫了。
第一次用jsp編資料庫的串連，發現問題還真不少。剛開始是jdbc配置就有問題，後來資料庫連接好了，中文設定也是個問題。後來還有sql語句的寫法問題出現了不少。經過三天的努力，這些問題都給解決掉了。
下面把我這三天測試的代碼寫給大家看看：
第一個是javabean的資料庫的串連代碼，檔案名稱字是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橋建立資料庫連接
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
//使用DriverManager類的getConnection()方法建立串連
//第一個參數定義使用者名稱，第二個參數定義密碼
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>資料庫的操作：</p>
<form id="form1" name="form1" method="post" action="createtable.jsp">
<label>
<input name="createtable" type="submit" id="createtable" value="建立表：userinfo" />
<%=session.getAttribute("userinfo")%>
<input name="insertnum" type="submit" id="insertnum" value="向表插入如下資料" /><%=session.getAttribute("insertsql")%>
<input name="inserttennum" type="submit" id="inserttennum" value="插入10條資料" />
<%=session.getAttribute("insertten")%><br />
</label>
<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="查詢資料" />
姓名：
<input name="deletename" type="text" id="deletename" />
<input name="deletenum" type="submit" id="deletenum" value="刪除資料" />
<%=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("查詢結果顯示如下：");
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>
下面是上面的介面調用的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>
</head>
<body>
<jsp:useBean id="loginData" scope="page" class="data.LoginData"/>
<%
request.setCharacterEncoding("gb2312");
%>
<%
//向資料庫裡建立表的代碼
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建立成功！");
stmt.close();
conn.close();
}catch(Exception ex){
ex.printStackTrace();
session.setAttribute("userinfo","表userinfo已經建立過了！");
}
}
%>
<%
//向資料庫裡的表添加資料的代碼
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","成功插入一條資料");
pstmt.close();
conn.close();
}catch(Exception ex){
ex.printStackTrace();
session.setAttribute("insertsql","插入資料不成功，請檢查後重新插入");
}
}
%>
<%
//大量新增資料
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 +"條資料");
conn.close();
stmt.close();
}catch(Exception ex){
ex.printStackTrace();
session.setAttribute("insertten","插入資料不成功，請檢查後重新插入");
}
}
%>
<% //查詢表的資料代碼
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 + "相關內容";
}
request.setAttribute("data",data);
conn.close();
stmt.close();
rs.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
%>
<%
//刪除資料代碼
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 +"條資料");
stmt.close();
conn.close();
}catch(Exception ex){
ex.printStackTrace();
session.setAttribute("deletenames","刪除不成功");
}
}
%>
<jsp:forward page="dbcode.jsp" />
</body>
</html>
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
