因为数据库课程老师要求使用Java通过jdbc连接数据库，并且在前端实现增删查改的功能，所以就在网上找了个模板，改了一些，加了一些东西，勉强能用，不足的地方还请大家多多指教。
网上那个模板似乎不能在网上显示数据库的数据，是因为几个servlet类没有收到index.jsp的get/post请求，加了几个按钮，目前基本能使用。接下来就把我完成的东西分享出来给大家。
package javadatabase;
1、DBConnection类
主要是用来加载驱动，登录数据库啥的。
package javadatabase;
import java.sql.*;
/**
*Created by caijie on 2016/10/25.
*/
public class DBConnection {
/**
* 驱动类名称
*/
private static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
/**
* 数据库连接字符串
*/
private static final String DATABASE_URL = "jdbc:mysql://localhost:3306/Wechat?useUnicode=true&characterEncoding=utf-8&useSSL=false";
/**
* 数据库用户名
*/
private static final String USER_NAME = "root";
/**
* 数据库密码
*/
private static final String PASSWORD = "caijie";
/**
* 数据库连接类
*/
private Connection conn = null;
// 加载驱动
public DBConnection() {
try {
Class.forName(DRIVER_CLASS);
} catch (Exception e) {
System.out.println("加载驱动错误");
System.out.println(e.getMessage());
}
try {
conn = DriverManager.getConnection(DATABASE_URL, USER_NAME, PASSWORD);
} catch (Exception e) {
System.out.println("取得连接错误");
System.out.println(e.getMessage());
}
}
// 取得连接
public Connection getConnection() {
return this.conn;
}
public static void close(Connection conn) {
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(PreparedStatement pstmt) {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void close(ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2、ShowLine类
主要是用来获取数据库数据以及执行查询、修改和删除功能。
package javadatabase;
/**
* Created by caijie on 2016/10/25.
*/
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
public class ShowLine {
private PreparedStatement pstmt = null;
private ResultSet rs = null;
private Connection conn;
public void ExcuteDel(String sql) {
try {
conn = new DBConnection().getConnection();
} catch (Exception e) {
e.printStackTrace();
}
try {
// 查询数据库对象,返回记录集(结果集)
pstmt = conn.prepareStatement(sql);
} catch (Exception e) {
e.printStackTrace();
}
try {
int rows = pstmt.executeUpdate(sql);
if (rows >= 1) {
System.out.println("成功删除.....");
} else {
System.out.println("删除失败.....");
}
} catch (Exception e) {
// TODO: handle exception
}
}
public void ExcuteMod(String sql) {
try {
conn = new DBConnection().getConnection();
} catch (Exception e) {
e.printStackTrace();
}
try {
// 查询数据库对象,返回记录集(结果集)
pstmt = conn.prepareStatement(sql);
} catch (Exception e) {
e.printStackTrace();
}
try {
int rows = pstmt.executeUpdate(sql);
if (rows >= 1) {
System.out.println("成功修改.....");
} else {
System.out.println("修改失败.....");
}
} catch (Exception e) {
// TODO: handle exception
}
}
public void ExcuteAdd(String sql){
try {
conn = new DBConnection().getConnection();
} catch (Exception e) {
e.printStackTrace();
}
try {
// 查询数据库对象,返回记录集(结果集)
pstmt = conn.prepareStatement(sql);
}catch (Exception e)
{
e.printStackTrace();
}
try {
int rows = pstmt.executeUpdate(sql);
if(rows >= 1){
System.out.println("成功添加.....");
} else {
System.out.println("添加失败.....");
}
} catch (Exception e) {
// TODO: handle exception
}
}
public ArrayList getUserList(String sql){
ArrayList list = new ArrayList();
// 取得数据库操作对象
try {
conn = new DBConnection().getConnection();
} catch (Exception e) {
e.printStackTrace();
}
try {
// 查询数据库对象,返回记录集(结果集)
//pstmt = conn.prepareStatement(sql);
pstmt = conn.prepareCall(sql);
rs = pstmt.executeQuery();
// 循环记录集，查看每一行每一列的记录
while (rs.next()) {
String UserN = rs.getString(1);
String UserId = rs.getString(2);
String Signature = rs.getString(3);
String Portrait = rs.getString(4);
Boolean Sex = rs.getBoolean(5);
String Place = rs.getString(6);
User user = new User();
user.setUserN(UserN);
user.setUserId(UserId);
user.setSignature(Signature);
user.setPortrait(Portrait);
user.setSex(Sex);
user.setPlace(Place);
list.add(user);
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
return list;
}
}
3、User类
对数据库对象属性的封装
package javadatabase;
/**
* Created by caijie on 2016/10/19.
*/
public class User {
private String UserN;
private String UserId;
private String Signature;
private String Portrait;
private Boolean Sex;
private String Place;
public void setUserId(String userId) {
this.UserId = userId;
}
public void setSignature(String signature) {
this.Signature = signature;
}
public void setUserN(String userN) {
this.UserN = userN;
}
public void setPortrait(String portrait) {
this.Portrait = portrait;
}
public void setSex(Boolean sex) {
this.Sex = sex;
}
public void setPlace(String place) {
this.Place = place;
}
public String getUserN() {
return UserN;
}
public String getUserId() {
return UserId;
}
public String getSignature() {
return Signature;
}
public String getPortrait() {
return Portrait;
}
public Boolean getSex() {
return Sex;
}
public String getPlace() {
return Place;
}
}
package servlet;
1、UserServlet.java
查询表中数据显示在前端
package servlet;
import javadatabase.ShowLine;
import javadatabase.User;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
public class UserServlet extends HttpServlet {
ArrayList list;
public void init() throws ServletException
{
// 执行必需的初始化
}
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doGet(req, resp);
}
@Override
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
ShowLine Temp = new ShowLine();
this.list = Temp.getUserList("call u_search()");//这儿使用的存储过程，改成查询语句就行了
//System.out.print(list.get(0));
request.setAttribute("list", list);
request.getRequestDispatcher("index.jsp").forward(request, response);
}
}
2、AddServlet.java
执行点击添加按钮之后的功能
package servlet;
import javadatabase.ShowLine;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
* Created by caijie on 2016/10/29.
*/
public class AddServlet extends HttpServlet {
private static String name;
private static String id;
private static String signature;
private static String portrait;
private static String sex;
private static String place;
private boolean flag = false;
@Override
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.name = request.getParameter("usern");
this.id = request.getParameter("id");
this.signature = request.getParameter("signature");
this.portrait = request.getParameter("portrait");
this.sex = request.getParameter("sex");
this.place = request.getParameter("place");
String sql = "insert into user (usern,id,signature,portrait,sex,place) values" +
"('" + name + "','" + id + "','" + signature + "','" + portrait + "','" + sex + "','" + place + "')";
//System.out.print(sql);
ShowLine db = new ShowLine();
if(flag) {
db.ExcuteAdd(sql);
flag = false;
}
else
flag = true;
request.getRequestDispatcher("add.jsp").forward(request, response);
}
}
3、DelServlet.java
执行点击删除功能后的一系列操作。
package servlet;
/**
* Created by caijie on 2016/10/19.
*/
import javadatabase.ShowLine;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class DelServlet extends HttpServlet {
private static String sno;
@Override
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.sno = request.getParameter("id");
this.doPost(request, response);
}
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String sql = "delete from user where Id = '" + sno + "' ";
System.out.print(sql);
ShowLine db = new ShowLine();
db.ExcuteDel(sql);
request.getRequestDispatcher("userservlet").forward(request, response);
}
}
4、ModServlet.java
执行修改功能
package servlet;
import javadatabase.ShowLine;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
/**
* Created by Jason_Cai on 2016/11/28.
*/
public class ModServlet extends HttpServlet {
private static String choice;
private static String clumn;
private static String change;
private boolean flag = false;
@Override
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.choice = request.getParameter("id");
this.clumn = request.getParameter("eid");
this.doPost(request, response);
}
@Override
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.change = request.getParameter("change");
String sql = "update user set "+clumn+" = '"+change+"' where "+clumn+" = '"+choice+"' ";
//System.out.print(sql);
ShowLine db = new ShowLine();
if(flag) {
db.ExcuteMod(sql);
flag = false;
}
else
flag = true;
request.getRequestDispatcher("modify.jsp").forward(request, response);
}
}
Java代码以上就结束了
剩下的是web.xml文件和jsp文件
1、添加数据的界面
添加2、开始界面
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%>
list| 姓名 | Id | 签名 | 头像 | 性别 | 来自 | |
| ${user.getUserN()} | ${user.getUserId() } | ${user.getSignature() } | ${user.getPortrait() } | ${user.getSex() } | ${user.getPlace() } | 删除 |
3、修改界面
Created by IntelliJ IDEA.
User: Jason_Cai
Date: 2016/11/28
Time: 20:47
To change this template use File | Settings | File Templates.
--%>
修改4、web.xml
servlet类的注册和mapping
Created by IntelliJ IDEA.
User: Jason_Cai
Date: 2016/11/28
Time: 20:47
To change this template use File | Settings | File Templates.
--%>
修改最后提醒下大家，由于每个人使用的数据库不一样，我这是按照我写的数据库来写的，如果移植到大家的电脑上的话可能需要改jsp文件和java文件里的东西，有不懂的欢迎私信讨论。