JavaWeb连接数据库MySQL

数据库是编程中重要的一部分，它囊括了数据操作，数据持久化等各方面。在每一门编程语言中都占有相当大的比例。

本次，我以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";// 密码 1private 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 blocke.printStackTrace();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return con;}public static void closeConnection() {if (con != null) {try {con.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}}}}<span style="font-size:18px;color:#990000;"></span>

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<User> selectAll() {Connection con = DBConnection.getConnection();// 连接数据库 保持连接Statement stmt;List<User> list = new ArrayList<User>();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 blocke.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 blocke.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(?,?,?)";// 增加用prepareStatementint 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 blocke.printStackTrace();} finally {try {pstmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.printStackTrace();} finally {try {pstmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.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 blocke.printStackTrace();} finally {try {pstmt.close();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}DBConnection.closeConnection();}return count;}public List<User> selectPage(int from, int rows) {Connection con = DBConnection.getConnection();// 连接数据库 保持连接Statement stmt;List<User> list = new ArrayList<User>();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 blocke.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 blocke.printStackTrace();} finally {DBConnection.closeConnection();}return count;}}<span style="font-size:18px;color:#990000;"></span>

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 {/** * Constructor of the object. */public UserServlet() {super();}/** * Destruction of the servlet. <br> */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();// 将受影响数据的数量返回给jspout.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);}/** * Initialization of the servlet. <br> *  * @throws ServletException *             if an error occurs */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<User> list = ud.selectPage(from, rowsCount);HashMap<String, Object> map = new HashMap<String, Object>();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();// 将受影响数据的数量返回给jspout.print(affCount);}}<span style="font-size:18px;color:#990000;"></span>

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 + "/";%><!DOCTYPE HTML><html><head><base href="<%=basePath%>"><title>main.jsp</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><link rel="stylesheet" href="CSS/easyui.css" type="text/css"></link><link rel="stylesheet" href="CSS/icon.css" type="text/css"></link><script type="text/javascript" src="JS/jquery-1.8.2.min.js"></script><script type="text/javascript" src="JS/jquery.easyui.min.js"></script><script type="text/javascript">    var addFlags = true;$(function() {$('#dg').datagrid({title : '用户列表',url : 'servlet/UserServlet?action=select',fitColumns : true,/* 自适应宽度 */striped : true,/* 斑马线效果 */pagination : true,/* 底部显示分页工具栏 */singleSelect : false,/* 只允许选择一行 */rownumbers : true,/* 显示一个行号列 */ctrlSelect : true,/* 允许使用Ctrl键+鼠标点击的方式进行多选操作 */iconCls : 'icon-ok',checkOnSelect : true,/* 点击行的时候该复选框就会被选中或取消选中  */selectOnCheck : true,/* 单击复选框将永远选择行 */pagination : true,nowrap : true,rownumbers : true,collapsible : true,//是否可折叠的pageSize : 5,//每页显示的记录条数，默认为10 pageList : [ 2, 4 ,5],//可以设置每页记录条数的列表 toolbar : [{text : '查询',iconCls : 'icon-search',handler : function() {$('#dg').datagrid('reload');}},'-',{text : '修改',iconCls : 'icon-edit',handler : function() {alert('帮助按钮');}},'-',{text : '添加',iconCls : 'icon-add',handler : function() {    if(addFlags){    $('#dg').datagrid('insertRow',{                                        index: 0,// 索引从0开始                                     row: {}                                         });                                           var editIndex = 0;$('#dg').datagrid('selectRow',editIndex)        .datagrid('beginEdit',editIndex);   addFlags = false;    }/* , $('#dlg').dialog('open').dialog('center').dialog('setTitle','New User');$('#fm').form('clear') */}},'-',{ text : '删除',  iconCls : 'icon-remove',  handler : function() {  $.messager.confirm("信息确认","确定删除吗?",function(ret) {if (ret) {var row = $("#dg").datagrid("getSelections");if (row.length == 0) {$.messager.alert("提示:","请选择删除的数据");   return;}var ids = [];for ( var i = 0; i < row.length; i++) {   ids.push(row[i].id);}$.post("servlet/UserServlet?action=delete",{uid : ids},function(data) {if (data > 0) {$('#dg').datagrid('reload');        alert("删除成功");} else {alert("删除失败");} });}});}} ],columns : [ [{field : 'ck',checkbox : true,width : 50,},{field : 'id',hidden : true,},{field : 'userName',title : '用户名',align : 'center',editor : 'text',width : 100},{field : 'pwd',title : '密码',width : 100,editor : 'text',align : 'center'},{field : 'displayName',title : '级别',width : 100,height : 100,editor : 'text',align : 'center'},{field : 'option',title : '操作',width : 100,align : 'center',formatter : function(value, row, index) {if (row.editing) {var s = '<a href="javascript:void(0);" style="text-decoration:none" onclick="saverow('+ index + ')">保存</a>'+' '+'<a href = "javascript:void(0);" style="text-decoration:none" onclick="canclerow('+ index + ')">取消</a>';return s;} else {var e = '<a href="javascript:void(0);" style="text-decoration:none" onclick="editrow('+ index + ')">编辑</a>';return e;}}} ] ],onBeforeEdit : function(index, row) {row.editing = true;$("#dg").datagrid("refreshRow", index);},onAfterEdit : function(index, row) {row.editing = false;$("#dg").datagrid("refreshRow", index);}});//设置分页控件 var p = $('#dg').datagrid('getPager');$(p).pagination({beforePageText : '第',//页数文本框前显示的汉字 afterPageText : '页    共 {pages} 页',displayMsg : '当前显示 {from} - {to} 条记录   共 {total} 条记录'});});function editrow(index) {var row = $("#dg").datagrid("getSelected");if (row == null) {alert("请选择您要编辑的行");return;}$("#dg").datagrid("beginEdit", index);}function saverow(index) {$("#dg").datagrid("endEdit", index);var row = $("#dg").datagrid("getSelected");if(addFlags) {  dbSave(row);} else {  dbAdd(row);}addFlags = true;}function canclerow(index) {$("#dg").datagrid("rejectChanges");$('#dg').datagrid('reload');addFlags = true;}function dbSave(row) {var name = row.userName;var uid = row.id;var pwd = row.pwd;var dname = row.displayName;$.post("servlet/UserServlet?action=update", {id : uid,userName : name,pwd : pwd,displayName : dname}, function(data) {if (data == "1") {alert("修改成功");} else {alert("修改失败");}});alert(row.userName + "-" + row.pwd + "-" + row.displayName);}function dbAdd(row) {var name = row.userName;var pwd = row.pwd;var dname = row.displayName;$.post("servlet/UserServlet?action=add", {userName : name,pwd : pwd,displayName : dname}, function(data) {if (data == "1") {alert("添加成功");} else {alert("添加失败");}});}</script><body><%-- ${sessionScope.user.userName}    ${sessionScope.user.dislayName} --%><table class="easyui-datagrid" id="dg"></table></body></html><span style="font-size:18px;color:#990000;"></span>

6.各个操作具体图片:

6.1添加:添加（张三 666 学生 这条数据）默认显示五条数据

6.2 删除刚刚插入的数据

6.3 修改数据(修改的ID主键为10的数据)