最近在学网页,在网页链接数据库的时候遇到了很多的错误,也积累了一些经验
首先我们要保证自己NB的环境要配置好:
准备工作:
①:安装jee版本的eclipse:http://www.eclipse.org/
②:下载好tomcat作为服务器:http://tomcat.apache.org/
③:下载并且安装好mysql :https://dev.mysql.com/downloads/mysql/
④:下载好jdbc:https://dev.mysql.com/downloads/connector/j/
数据库的安装过程:参见 x_smile 的博客:https://www.cnblogs.com/xsmile/p/7753984.html
下载jdbc的过程有必要讲一下,因为之前下载了新版本的Connector/J 8.0.11
后发现驱动不一样,写法也就不一样,比较麻烦所以我们推荐下载旧版本的Connector/J 5.1.46
实现步骤:
首先我们在mysql中建立database:test 然后建立table:student
内容分别是:序号、姓名、年龄。
那么这就要求我们建立一个java类来对应这些元素,首先要介绍的是Student.java
package com.example; //注意放在这个包里面
public class Student {
private String sid; //序号
private String name; //姓名
private String age; //年龄
public String getSid() {
return sid;
}
public void setSid(String sid) {
this.sid = sid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
}
连接数据库ConnectDB.java:
package com.example;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import com.example.Student;
public class ConnectDB {
private static Connection conn = null; //连线
private static Statement stmt = null; //操作指令 java就是和cmd的中介
private static ResultSet rs = null; //传回来的表
public static void startConnect() {
try {
Class.forName("com.mysql.jdbc.Driver"); //类别要被含进来
try {
conn = DriverManager.getConnection(
"jdbc:MySQL://127.0.0.1:3306/test", "root", "123456");
} catch (SQLException e) {
// TODO: handle exception
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
// TODO: handle exception
e.printStackTrace();
}
}
public static void endConnect() throws SQLException {
//关闭连接
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
}
public static void update(String sql) throws SQLException {
//数据库更新
startConnect(); //连接mysql
stmt = conn.createStatement(); //创建链接的中介
stmt.executeUpdate(sql); //执行操作指令
endConnect(); //关闭连接
}
public static ArrayList getList1(String sql) throws SQLException {
//数据库查询
ArrayList list = new ArrayList();
startConnect();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
Student st = new Student();
st.setSid(rs.getString("sid"));
st.setName(rs.getString("sname"));
st.setAge(rs.getString("sage"));
list.add(st);
}
endConnect();
return list;
}
}
上面要求把驱动包含进来:
显示数据的列表list.jsp
<%@page import="com.example.Student"%>
<%@page import="com.example.ConnectDB"%>
<%@ page language="java" import="com.example.ConnectDB,com.example.Student,java.util.*"
pageEncoding="UTF-8"%>
<%
ArrayList list = ConnectDB.getList1("select sid,sname,sage from student"); //获得test表中所有数据并以列表形式返回
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'list.jsp' starting page</title>
</head>
<body>
<input type="button" name="bottom" value="添加新学生"
οnclick="javascript:window.location.href='add.jsp'">
<br>
<br>
<table border="1">
<tr>
<td>
编号
</td>
<td>
姓名
</td>
<td>
年龄
</td>
<td>
操作
</td>
</tr>
<%
{
for (Iterator it = list.iterator(); it.hasNext();) {
Student s = (Student) it.next();
%>
<tr>
<td><%=s.getSid()%></td>
<td><%=s.getName()%></td>
<td><%=s.getAge()%></td>
<td>
<a href="update.jsp?sid=<%=s.getSid()%>">修改</a>
<a href="delete.jsp?sid=<%=s.getSid()%>"
οnclick="return confirm('确定删除?');">删除</a>
</td>
</tr>
<%
}
}
%>
</table>
</body>
</html>
删除的delete.jsp
<%@ page language="java" import="com.example.ConnectDB" pageEncoding="gbk"%>
<%
ConnectDB.update("delete from student where sid ="+request.getParameter("sid")); //获得要删除人的sid并删除
response.sendRedirect("list.jsp"); //转到list.jsp页面
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'delete.jsp' starting page</title>
</head>
<body>
This is my JSP page.<br>
</body>
</html>
添加的add.jsp
<%@ page language="java" pageEncoding="utf-8"%>
<%
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=utf-8");
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>add</title>
</head>
<body>
<form action="addinfo.jsp" method="post">
提交到addinfo.jsp
<p>
添加学生:
</p>
SID:
<input type="text" name="sid">
<br>
<br>
姓名:
<input type="text" name="sname">
<br>
<br>
年龄:
<input type="text" name="sage">
<br>
<br>
<input type="submit" name="submit" value="提交">
</form>
</body>
</html>
提交到的addinfo.jsp
<%@page import="com.example.ConnectDB"%>
<%@ page language="java" import="com.example.ConnectDB" pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=utf-8");
String sid=request.getParameter("sid");
String name =request.getParameter("sname");
String age = request.getParameter("sage");
ConnectDB.update("insert into student (sid,sname,sage) values ('"+sid+"','"+name+"','"+age+"')");
response.sendRedirect("list.jsp");
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'addinfo.jsp' starting page</title>
</head>
<body>
添加成功!<br>
</body>
</html>
修改的update.jsp
<%@ page language="java" import="com.example.ConnectDB" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'delete.jsp' starting page</title>
</head>
<body>
<form action="change.jsp" method="post">
提交到change.jsp
<p>
修改学生:
</p>
SID:
<input type="text" name="sid" value=<%=request.getParameter("sid") %>>
<br>
<br>
姓名:
<input type="text" name="sname">
<br>
<br>
年龄:
<input type="text" name="sage">
<br>
<br>
<input type="submit" name="submit" value="提交">
</form>
</body>
</html>
修改后提交到的change.jsp
<%@ page language="java" import="com.example.ConnectDB" pageEncoding="UTF-8"%>
<%
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html; charset=utf-8");//这三段一定要加进来否则会不能显示汉字
String oid =request.getParameter("sid");
String oname =request.getParameter("sname");
String oage =request.getParameter("sage");%>
<%
String updateUser="update student set sname= '"+oname+"' where sid="+oid;
ConnectDB.update(updateUser);
String updateUser1="update student set sage= '"+oage+"' where sid="+oid;
ConnectDB.update(updateUser1);
response.sendRedirect("list.jsp"); //转到list.jsp页面
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>My JSP 'delete.jsp' starting page</title>
</head>
<body>
This is my JSP page.<br>
</body>
</html>
最后运行结果:
帅气的分割线。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
最后
总结一下可能遇到的问题:
①sql指令容易丢掉单引号,如果丢掉的话就只能插入数字
②如果没有加上三段的编码那么可能显示汉字的时候会乱码
③jdbc版本的问题
参考了大神 东月之神 的博客:
https://blog.csdn.net/eastmoon502136/article/details/50783334