javaWeb-连接mysql数据库增删改查

1、连接MYSQL数据库的简单增删改查项目框架如图所示：所需工具：所需工具：所需工具：所需工具：程序代码：packageimportimportimportimportimportimportimportpublic class InsertOrUpdateAction extends HttpServlet /* */private static final long serialVersionUID = 1L;private UserDao userDao ;public void doGet(HttpServletRequest request, HttpServletResponse re

2、sponsethrows ServletException, IOException doPost(request, response;public void doPost(HttpServletRequest request, HttpServletResponse responsethrows ServletException, IOException request.setCharacterEncoding("utf-8"response.setCharacterEncoding("utf-8"userDao = new UserDao(;Stri

3、ng str = request.getRequestURI(;str = str.substring(str.lastIndexOf("/"+1;String yx = request.getParameter("yx"String zy = request.getParameter("zy"String age = request.getParameter("age"String dh = request.getParameter("dh"String qq = request.getPar

4、ameter("qq"String sex = request.getParameter("sex"UserBean userBean = new UserBean(;userBean.setYx(yx;userBean.setZy(zy;if(!"".equals(age&&age!=nulluserBean.setAge(Integer.valueOf(age;elseuserBean.setAge(0;userBean.setDh(dh;userBean.setQq(qq;userBean.setSex(sex;

5、if("InsertUserAction".equals(strif(userDao.insertUser(userBeanresponse.sendRedirect("SelectAllUser"elseresponse.sendRedirect("SelectAllUser"else if("UpdateUserAction".equals(strString strId = request.getParameter("id"if(!"".equals(strId&

6、;&strId!=nulluserBean.setId(Integer.valueOf(strId;elseuserBean.setId(-1;if(userDao.updateUser(userBeanresponse.sendRedirect("SelectAllUser"elseresponse.sendRedirect("SelectAllUser"packageimportimportimportimportimportpublic class LoginAction extends HttpServlet /* */private s

7、tatic final long serialVersionUID = 1L;public void doGet(HttpServletRequest request, HttpServletResponse responsethrows ServletException, IOException doPost(request, response;public void doPost(HttpServletRequest request, HttpServletResponse responsethrows ServletException, IOException String name=

8、request.getParameter("name"String password = request.getParameter("password"if("".equals(name|"".equals(passwordresponse.sendRedirect("/javaWebTest/index.jsp"elseif("123".equals(name&&"123".equals(passwordresponse.sendRedi

9、rect("SelectAllUser"elseresponse.sendRedirect("/javaWebTest/index.jsp"public class UserAction extends HttpServlet /* */private static final long serialVersionUID = 1L;private UserDao userDao ;public void doGet(HttpServletRequest request, HttpServletResponse responsethrows Servlet

10、Exception, IOException doPost(request, response;public void doPost(HttpServletRequest request, HttpServletResponse responsethrows ServletException, IOException request.setCharacterEncoding("utf-8"response.setCharacterEncoding("utf-8"userDao = new UserDao(;String str = request.get

11、RequestURI(;str = str.substring(str.lastIndexOf("/"+1;if("SelectAllUser".equals(str.trim(List list = userDao.getAllUser(; request.setAttribute("UserList", list;request.getRequestDispatcher("/userList.jsp".forward(request, response;else if("SelectOneUser&q

12、uot;.equals(strString strId = request.getParameter("id"int id = Integer.valueOf(strId;UserBean userBean = userDao.selectUserById(id;request.setAttribute("userBean", userBean;request.getRequestDispatcher("/updateUser.jsp".forward(request, response;else if("DeleteUse

13、rById".equals(strString strId = request.getParameter("id"int id = Integer.valueOf(strId;if(userDao.deleteUser(idresponse.sendRedirect("SelectAllUser"elseresponse.sendRedirect("SelectAllUser"public class UserDao private DBUtils dbUtil;/* 查询所有的user* return*/public Li

14、st getAllUser( dbUtil = new DBUtils(;List list = new ArrayList (; String sql = "select * from user"ResultSet rs = dbUtil.exceteSQl(sql;try while(rs.next(int i =1;UserBean userBean = new UserBean(;userBean.setId(rs.getInt(i+;userBean.setName(rs.getString(i+;userBean.setYx(rs.getString(i+;us

15、erBean.setZy(rs.getString(i+;userBean.setAge(rs.getInt(i+;userBean.setDh(rs.getString(i+;userBean.setQq(rs.getString(i+;userBean.setSex(rs.getString(i+;list.add(userBean; catch (SQLException e e.printStackTrace(;finallydbUtil.close(;return list;/* 根据id查询user* param id* return*/public UserBean select

16、UserById(int iddbUtil = new DBUtils(; UserBean userBean = new UserBean(;String sql = "select * from user where id = '"+id+"'"ResultSet rs = dbUtil.exceteSQl(sql;try if(rs.next(int i =1;userBean.setId(rs.getInt(i+;userBean.setYx(rs.getString(i+;userBean.setZy(rs.getString(

17、i+;userBean.setAge(rs.getInt(i+;userBean.setDh(rs.getString(i+;userBean.setQq(rs.getString(i+;userBean.setSex(rs.getString(i+; catch (SQLException e e.printStackTrace(;return userBean;/* 插入用户* param userBean* return*/public boolean insertUser(UserBean userBeandbUtil = new DBUtils(; String sql = &quo

18、t;insert into user (yx,zy,age,dh,qq,sex values(?,?,?,?,?,?"int row = dbUtil.excete(sql, new ObjectuserBean.getYx(,userBean.getZy(,userBean.getAge(,userBean.getDh(,userBean.getQq(,userBean.getSex(;if(row>0return true;return false;/* 删除用户* param id* return*/public boolean deleteUser(int iddbUt

19、il = new DBUtils(; String sql = "delete from user where id = '"+id+ "'"int row = dbUtil.excete(sql;if(row>0return true;return false;/* 修改user* param userBean* return*/public boolean updateUser(UserBean userBeandbUtil = new DBUtils(; String sql = "update user set y

20、x =? ,zy=?,age=?,dh=?,qq=?,sex=? where id = ?"int row = dbUtil.excete(sql, new ObjectuserBean.getYx(,userBean.getZy(,userBean.getAge(,userBean.getDh(,userBean.getQq(,userBean.getSex(,userBean.getId(;if(row>0return true;return false;packagepublic class UserBean private int id;private String n

21、ame;/电话private String yx;/院系private String zy;/专业private int age;/年龄private String dh;private String qq;private String sex;public int getId( return id;public void setId(int id this.id = id;public String getName( return name;public void setName(String name = name;public String getYx( return

22、 yx;public void setYx(String yx this.yx = yx;public String getZy( return zy;public void setZy(String zy this.zy = zy;public int getAge( return age;public void setAge(int age this.age = age;public String getDh( return dh;public void setDh(String dh this.dh = dh;public String getQq( return qq;public v

23、oid setQq(String qq this.qq = qq;public String getSex( return sex;public void setSex(String sex this.sex = sex;public class UserDao private DBUtils dbUtil;/* 查询所有的user* return*/public List getAllUser( dbUtil = new DBUtils(;List list = new ArrayList (; String sql = "select * from user"Resul

24、tSet rs = dbUtil.exceteSQl(sql;try while(rs.next(int i =1;UserBean userBean = new UserBean(;userBean.setId(rs.getInt(i+;userBean.setName(rs.getString(i+;userBean.setYx(rs.getString(i+;userBean.setZy(rs.getString(i+;userBean.setAge(rs.getInt(i+;userBean.setDh(rs.getString(i+;userBean.setQq(rs.getStri

25、ng(i+;userBean.setSex(rs.getString(i+;list.add(userBean; catch (SQLException e e.printStackTrace(;finallydbUtil.close(;return list;/* 根据id查询user* param id* return*/public UserBean selectUserById(int iddbUtil = new DBUtils(; UserBean userBean = new UserBean(;String sql = "select * from user wher

26、e id = '"+id+"'"ResultSet rs = dbUtil.exceteSQl(sql;try if(rs.next(int i =1;userBean.setId(rs.getInt(i+;userBean.setYx(rs.getString(i+;userBean.setZy(rs.getString(i+;userBean.setAge(rs.getInt(i+;userBean.setDh(rs.getString(i+;userBean.setQq(rs.getString(i+;userBean.setSex(rs.g

27、etString(i+; catch (SQLException e e.printStackTrace(;return userBean;/* 插入用户* param userBean* return*/public boolean insertUser(UserBean userBeandbUtil = new DBUtils(; String sql = "insert into user (yx,zy,age,dh,qq,sex values(?,?,?,?,?,?"int row = dbUtil.excete(sql, new ObjectuserBean.ge

28、tYx(,userBean.getZy(,userBean.getAge(,userBean.getDh(,userBean.getQq(,userBean.getSex(;if(row>0return true;return false;/* 删除用户* param id* return*/public boolean deleteUser(int iddbUtil = new DBUtils(; String sql = "delete from user where id = '"+id+ "'"int row = dbUti

29、l.excete(sql;if(row>0return true;return false;/* 修改user* param userBean* return*/public boolean updateUser(UserBean userBeandbUtil = new DBUtils(; String sql = "update user set yx =? ,zy=?,age=?,dh=?,qq=?,sex=? where id = ?"int row = dbUtil.excete(sql, new ObjectuserBean.getYx(,userBean

30、.getZy(,userBean.getAge(,userBean.getDh(,userBean.getQq(,userBean.getSex(,userBean.getId(;if(row>0return true;return false;package com.util;public class DBUtils private static Connection conn=null;private Statement st=null;private ResultSet rs = null;private PreparedStatement ps;statictry conn =

31、(Connection DriverManager.getConnection("jdbc:mysql:/localhost:3306/ygx?useUnicode=true&characterEncoding=UTF-8","root" ,"123456" ; catch (ClassNotFoundException e e.printStackTrace(; catch (SQLException e e.printStackTrace(;/* * Title: exceteSQl * Description: TODO

32、(执行不带参数的sql语句 * param sql* return ResultSet* throws*/public ResultSet exceteSQl(String sql try st =(Statement conn.createStatement(;rs = st.executeQuery(sql; catch (SQLException e e.printStackTrace(;return rs;/* * Title: exceteSQl * Description: TODO(执行带参数的sql语句 * param sql* param obj* return Result

33、Set* throws*/public ResultSet exceteSQl(String sql, Object objint length = obj.length; try ps = conn.prepareStatement(sql;preparStateSql(obj, length;rs = ps.executeQuery(; catch (SQLException e e.printStackTrace(;return rs;/* * Title: excete * Description: TODO(此方法适用于不带参数的修改，删除，增加 * param sql* retur

34、n int* throws*/public int excete(String sqlint row =0;try st = (Statement conn.createStatement(;row = st.executeUpdate(sql; catch (SQLException e e.printStackTrace(;finallyclose(;return row;/* * Title: excete * Description: TODO(此方法适用带参数的修改，删除，增加 * param sql* param obj* return int* throws*/public in

35、t excete(String sql, Object objint row =0;int length = obj.length; try ps = conn.prepareStatement(sql;preparStateSql(obj, length;row = ps.executeUpdate(; catch (SQLException e e.printStackTrace(;finallyclose(;return row;/* * Title: preparStateSql * Description: TODO(参数赋值 * param obj* param length* t

36、hrows SQLException void* throws*/private void preparStateSql(Object obj, int length throws SQLException for(int i=0;i if(obji.getClass(=String.classps.setString(i+1, obji.toString(;else if(obji.getClass(=Integer.classps.setInt(i+1, (Integerobji;else if(obji.getClass(=Double.classps.setDouble(i+1, (D

37、oubleobji;ps.setDate(i+1, (Dateobji;/* * Title: close * Description: TODO(数据库连接关闭 void* throws*/public void close(if(rs!=nulltry rs.close(; catch (SQLException e e.printStackTrace(;if(st!=nulltry st.close(; catch (SQLException e e.printStackTrace(;if(ps!=nulltry ps.close(; catch (SQLException e e.pr

38、intStackTrace(;public static void main(String args DBUtils db = new DBUtils(;String sql = "select * from user "ResultSet rr = db.exceteSQl(sql;try while(rr.next(int i =1; catch (SQLException e / TODO Auto-generated catch blocke.printStackTrace(;finallydb.close(;/String sql = "insert i

39、nto user (yx,zy,age,dh,qq,sex values("软件","测试",12,"110","123456789","女""/* String sql = "insert into user (yx,zy,age,dh,qq,sex values(?,?,?,?,?,?"int row = db.excete(sql, new Object"软件","测试",12,"110","

40、;123456789","女"/int row = db.excete(sql;if(row>0*/*String sql = "update user set yx=? where id =?"int row = db.excete(sql, new Object"计通",3;if(row>0*/所需要的驱动包：Web.xml配置：xml version="1.0" encoding="UTF-8"?><web-app version="2.5&qu

41、ot; xmlns= xmlns:xsi= xsi:schemaLocation=><display-name> display-name><servlet><servlet-name>UserAction servlet-name><servlet-class> servlet-class>servlet><servlet><servlet-name>InsertOrUpdateAction servlet-name><servlet-class> servlet-clas

42、s>servlet><servlet><servlet-name>LoginAction servlet-name><servlet-class> servlet-class>servlet><servlet-mapping><servlet-name>UserAction servlet-name><url-pattern>/SelectAllUser url-pattern><url-pattern>/SelectOneUser url-pattern><

43、;url-pattern>/DeleteUserById url-pattern>servlet-mapping><servlet-mapping><servlet-name>InsertOrUpdateAction servlet-name><url-pattern>/InsertUserAction url-pattern><url-pattern>/UpdateUserAction url-pattern>servlet-mapping><servlet-mapping><serv

44、let-name>LoginAction servlet-name><url-pattern>/LoginAction url-pattern>servlet-mapping> <welcome-file-list><welcome-file>index.jsp welcome-file>welcome-file-list>web-app>Jsp:<% page language="java" import= pageEncoding="UTF-8"%><%S

45、tring path = request.getContextPath(;String basePath = request.getScheme(+":/"+request.getServerName(+":"+request.getServerPort(+path+"/"%>DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html><head><base href="<%=basePat

46、h%>"><title>My JSP 'index.jsp' starting page title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"> <me

47、ta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><style type="text/css">.table1margin:0 auto;border-collapse: collapse;.table1 tdpadding: 0px;height:25px;border:1px

48、solid #6AB6B6;text-align:center;font-family:"微软雅黑"font-size:12px;background-color:#EAFEFE;.table1 td.title height:28px;color:#fff;background-color:#6AB6B6;font-weight:bold;font-size:20px;text-align: center;.table1 tr.colnamefont-size: 14px;font-weight: bold;* .nobd_table tdborder: none;sty

49、le>head><body><div align="center" style="padding-top: 30px;" id = "add" ><form action="LoginAction" method="post" ><table class="table1" width="50%" ><tr><td class="title" colspan=&

50、quot;2" >欢迎你的登录! td>tr> <tr><td>帐号 td><td> <input type="text" name="name" > td>tr><tr><td>密码 td><td> <input type="password" name="password" > td>tr><tr><td colspan="2

51、" > <input type="submit" value="登录" > <input type="reset" value="重置" > td>tr>table>form>div>body>html><% page language="java" import= pageEncoding="utf-8"%><% taglib prefix="c" uri

52、= %><%String path = request.getContextPath(;String basePath = request.getScheme(+":/"+request.getServerName(+":"+request.getServerPort(+path+"/"%>DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html><head><base href="

53、<%=basePath%>"><title>My JSP 'userList.jsp' starting page title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0&

54、quot;> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><meta http-equiv="description" content="This is my page"><style type="text/css">.table1margin:0 auto;border-collapse: collapse;.table1 tdpadding: 0px;height:

55、25px;border:1px solid #6AB6B6;text-align:center;font-family:"微软雅黑"font-size:12px;background-color:#EAFEFE;.table1 td.title height:28px;color:#fff;background-color:#6AB6B6;font-weight:bold;font-size:20px;text-align: center;.table1 tr.colnamefont-size: 14px;font-weight: bold;* .nobd_table td

56、border: none;style><script type="text/javascript">function goback(script>head><body><div align="center" style="padding-top: 30px;" id = "add" ><form action="UpdateUserAction" method="post" ><table class=&

57、quot;table1" width="50%" > <tr><td colspan="2" class="title" >user信息填写 td>tr><tr><td>院系 td><td> <input type="text" name="yx" id="yx" value="$userBean.yx " ><input type="

58、hidden" name="id" value="$userBean.id " > td>tr><tr><td>专业 td><td> <input type="text" name="zy" id="zy" value="$userBean.zy " > td>tr><tr><td>年龄 td><td><input type="

59、;text" name="age" id="age" value="$userBean.age " > td>tr><tr><td>电话 td><td> <input type="text" name="dh" id="dh" value="$userBean.dh " > td>tr><tr><td>QQ td><td>

60、 <input type="text" name="qq" id="qq" value="$userBean.qq " > td>tr><tr><td>行别 td><td><c:set value="男" var="sexs" > c:set><c:if test="$userBean.sex eq sexs "><input type="rad

61、io" name="sex" value="男" checked="checked" >男<input type="radio" name="sex" value="女">女 c:if><c:if test="$userBean.sex ne sexs "><input type="radio" name="sex" value="男" >

62、男<input type="radio" checked="checked" name="sex" value="女">女 c:if>td>tr><tr><td colspan="2" > <input type="submit" value="提交" >    <input type="reset" value="

63、重置" >     <input type="button" value="返回" onclick= > td>tr>table>form>div>body>html><% page language="java" import= pageEncoding="utf-8"%><% taglib prefix="c" uri= %><%String path = request.getContextPath(;String basePath = request.getScheme(+":/"+request.getServerName(+":"+request.getServerPort(+path+"/"%>DOCTYPE HTML PUBLIC "-/W3C/DTD HTML 4.01 Transitional/EN"><html><head><b