数据库是编程中重要的一部分，它囊括了数据操作，数据持久化等各方面。在每一门编程语言中都占有相当大的比例。
本次，我以MySQL为例，使用MVC编程思想(请参阅我之前的博客)。简单演示一下JavaWeb对数据库的操作。
1：我们需要掌握简单的SQL语句，并且会简单操作图形化的数据库。我们在数据库建一个表(Users)可以在里面随便添加几条数据。
2：接下来，我们获得驱动并连接到MySQL。
package com.joker.web.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
private static Connection con = null;
// 驱动程序名
private static String driverName = "com.mysql.jdbc.Driver";
// 数据库用户名
private static String userName = "root";
// 密码 1
private static String userPasswd = "*****";
// 数据库名
private static String dbName = "jokertest";
// 联结字符串
private static String url = "jdbc:mysql://localhost/" + dbName + "?user="
+ userName + "&password=" + userPasswd
+ "&useUnicode=true&characterEncoding=gbk";
public static Connection getConnection() {
try {
// 1.驱动
Class.forName(driverName);
// 2. 连接数据库 保持连接
con = DriverManager.getConnection(url);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
public static void closeConnection() {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
3.写我们的Dao文件,即对数据库的增删改查
package com.joker.web.dao;
import java.sql.*;
import java.util.*;
import com.joker.web.db.DBConnection;
import com.joker.web.entity.User;
public class UserDao {
// 查找所有数据，返回List集合
public List selectAll() {
Connection con = DBConnection.getConnection();// 连接数据库 保持连接
Statement stmt;
List list = new ArrayList();
try {
stmt = con.createStatement();// 执行SQL语句
ResultSet rs = stmt.executeQuery("SELECT * FROM users");// 查找
// 数据返回结果集
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("USER_NAME"));
user.setDisplayName(rs.getString("DISPLAY_NAME"));
user.setPwd(rs.getString("PWD"));
list.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBConnection.closeConnection();
}
return list;
}
// 按条件查找
// where USER_NAME='"+ name + "' and PWD='" + pwd + "'
public User selectWhere(String whereOption) {
Connection con = DBConnection.getConnection();// 连接数据库 保持连接
Statement stmt;
User user = null;
try {
stmt = con.createStatement();// 执行SQL语句
String sql = "SELECT * FROM users ";
if (!whereOption.equals("")) {
sql += whereOption;
}
// 查找数据返回结果集
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
user = new User();
user.setUserName(rs.getString("USER_NAME"));
user.setDisplayName(rs.getString("DISPLAY_NAME"));
user.setPwd(rs.getString("PWD"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBConnection.closeConnection();
}
return user;
}
// 新增
public int insert(User user) {
Connection con = DBConnection.getConnection();
PreparedStatement pstmt = null;
String sql = " insert into users(user_name,pwd,display_name) values(?,?,?)";
// 增加用prepareStatement
int count = 0;
try {
pstmt = con.prepareStatement(sql);
pstmt.setString(1, user.getUserName());
pstmt.setString(2, user.getPwd());
pstmt.setString(3, user.getDisplayName());
count = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBConnection.closeConnection();
}
return count;
}
// 修改
public int upDate(User user) {
Connection con = DBConnection.getConnection();
PreparedStatement pstmt = null;
String sql = " update users " + " set user_name = ? , " + " pwd = ? , "
+ " display_name= ? " + " where id= ? ";
int count = 0;
try {
pstmt = con.prepareStatement(sql);
pstmt.setString(1, user.getUserName());
pstmt.setString(2, user.getPwd());
pstmt.setString(3, user.getDisplayName());
pstmt.setInt(4, user.getId());
count = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBConnection.closeConnection();
}
return count;
}
// 删除
public int delete(int id) {
Connection con = DBConnection.getConnection();
PreparedStatement pstmt = null;
String sql = " delete from users where id = ?";
int count = 0;
try {
pstmt = con.prepareStatement(sql);
pstmt.setInt(1, id);
count = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DBConnection.closeConnection();
}
return count;
}
public List selectPage(int from, int rows) {
Connection con = DBConnection.getConnection();// 连接数据库 保持连接
Statement stmt;
List list = new ArrayList();
try {
stmt = con.createStatement();// 执行SQL语句
ResultSet rs = stmt.executeQuery("SELECt * FROM users LIMIT "
+ from + "," + rows);
while (rs.next()) {
User user = new User();
user.setId(rs.getInt("id"));
user.setUserName(rs.getString("USER_NAME"));
user.setDisplayName(rs.getString("DISPLAY_NAME"));
user.setPwd(rs.getString("PWD"));
list.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBConnection.closeConnection();
}
return list;
}
// 返回所有数剧条数
public int selectCount() {
Connection con = DBConnection.getConnection();// 连接数据库 保持连接
Statement stmt;
int count =0 ;
try {
stmt = con.createStatement();// 执行SQL语句
ResultSet rs = stmt.executeQuery("SELECT count(1) count FROM users");
while (rs.next()) {
count = rs.getInt("count");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
DBConnection.closeConnection();
}
return count;
}
}
4.使用Servlet当控制器，在Servlet对网页上的数据进行操作。
package com.joker.web.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import com.joker.web.dao.UserDao;
import com.joker.web.entity.User;
public class UserServlet extends HttpServlet {
public UserServlet() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doPost(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
String action = request.getParameter("action");
if (action.equals("select")) {
select(request, response);
} else if (action.equals("update")) {
update(request, response);
} else if (action.equals("add")) {
insert(request, response);
} else if (action.equals("delete")) {
delete(request, response);
}
}
// 新增
private void insert(HttpServletRequest request, HttpServletResponse response)
throws IOException {
String uName = request.getParameter("userName");
String pwd = request.getParameter("user");
String dName = request.getParameter("displayName");
User user = new User();
user.setUserName(uName);
user.setPwd(pwd);
user.setDisplayName(dName);
UserDao uDao = new UserDao();
int affCount = uDao.insert(user);
PrintWriter out = response.getWriter();
// 将受影响数据的数量返回给jsp
out.print(affCount);
}
// 删除
private void delete(HttpServletRequest request, HttpServletResponse response)
throws IOException {
String[] ids = request.getParameterValues("uid[]");
UserDao ud = new UserDao();
int count = 0;
for (int i = 0; i < ids.length; i++) {
count += ud.delete(Integer.parseInt(ids[i]));
}
PrintWriter out = response.getWriter();
out.print(count);
}
public void init() throws ServletException {
System.out.println("处室执行！！！！！！！！！！！！！！！");
}
// 查询
public void select(HttpServletRequest request, HttpServletResponse response)
throws IOException {
String page = request.getParameter("page");
String rows = request.getParameter("rows");
System.out.println("page:" + page + " rows:" + rows);
UserDao ud = new UserDao();
int rowsCount = Integer.parseInt(rows);
int from = (Integer.parseInt(page) - 1) * rowsCount;
List list = ud.selectPage(from, rowsCount);
HashMap map = new HashMap();
map.put("total", ud.selectCount());
map.put("rows", list);
// JSonArray ja = JSONArray.fromObject(list);
JSonObject jo = JSONObject.fromObject(map);// 单条数据
PrintWriter out = response.getWriter();
System.out.println(jo.toString());
// 将json数据返回给jspData-grid的url。
out.println(jo.toString());
}
// 修改
public void update(HttpServletRequest request, HttpServletResponse response)
throws IOException {
String id = request.getParameter("id");
String uName = request.getParameter("userName");
String pwd = request.getParameter("pwd");
String dName = request.getParameter("displayName");
User user = new User();
user.setId(Integer.parseInt(id));
user.setUserName(uName);
user.setPwd(pwd);
user.setDisplayName(dName);
UserDao uDao = new UserDao();
int affCount = uDao.upDate(user);
PrintWriter out = response.getWriter();
// 将受影响数据的数量返回给jsp
out.print(affCount);
}
}
5.使用Easy-UI框架，是数据库内容在页面进行显示
<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
main.jsp
<%-- ${sessionScope.user.userName}
${sessionScope.user.dislayName} --%>
6.各个操作具体图片:
6.1添加:添加（张三 666 学生 这条数据）默认显示五条数据
6.2 删除刚刚插入的数据
6.3 修改数据(修改的ID主键为10的数据)
以上所述是小编给大家介绍的JavaWeb连接数据库MySQL的操作技巧，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对考高分网网站的支持！
