JavaWeb连接数据库MySQL的操作技巧

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

}

}

}

}

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 {

/**

* Constructor of the object.

*/

public UserServlet() {

super();

}

/**

* Destruction of the servlet.

*/

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.

*

* @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 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 + "/";

%>

<%-- ${sessionScope.user.userName}

${sessionScope.user.dislayName} --%>

6.各个操作具体图片:

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

6.2 删除刚刚插入的数据

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

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。