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";

// 密码 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();

}

}

}

}<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 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<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 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;

}

}<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();

// 将受影响数据的数量返回给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);

}

/**

* 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();

// 将受影响数据的数量返回给jsp

out.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%>" rel="external nofollow" >

<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" rel="external nofollow" type="text/css"></link>

<link rel="stylesheet" href="CSS/icon.css" rel="external nofollow" 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);" rel="external nofollow" rel="external nofollow" 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);" rel="external nofollow" rel="external nofollow" 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的数据)

以上所述是小编给大家介绍的JavaWeb连接数据库MySQL的操作技巧,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对网站的支持!

以上是 JavaWeb连接数据库MySQL的操作技巧 的全部内容, 来源链接: utcz.com/p/211408.html

回到顶部