jsp-2 简单的servlet连接mysql数据库 增删改查

补：后来我才发现这玩意应该有高手写到jar包里面的.....

其实里面的意思也不是数据查询和数据操作

而是得到一个结果和多个结果的区别

对数据的Dao的处理

package com.javaweb.dao;import java.util.ArrayList;import java.util.List;import org.apache.commons.lang3.StringUtils;import com.javaweb.dao.DBDao;import com.javaweb.bean.User;public class UserDao { private DBDao dao=new DBDao(); public List<User> findAll(){ List<User> list=new ArrayList<User>(); StringBuffer sql=new StringBuffer(); sql.append(" select id,username,password "); sql.append(" from user "); try { list=dao.findAll(sql.toString(), User.class, null); } catch (Exception e) { e.printStackTrace(); } return list; } public int add(User User) { // TODO Auto-generated method stub if(User==null){ return 0; } StringBuffer sql=new StringBuffer(); sql.append(" insert into user(id,username,password) "); sql.append(" values(?,?,?) "); List<Object> params =new ArrayList<Object>(); params.add(User.getId()); params.add(User.getUsername()); params.add(User.getPassword()); return dao.execute(sql.toString(), params); } public int delete(User user) { // TODO Auto-generated method stub if(user==null){ return 0; } StringBuffer sql=new StringBuffer(); sql.append(" delete from user where id=?"); List<Object> params =new ArrayList<Object>(); params.add(user.getId()); return dao.execute(sql.toString(), params); } public int update(User user) { // TODO Auto-generated method stub if(user==null){ return 0; } List<Object> params =new ArrayList<Object>(); StringBuffer sql=new StringBuffer(); sql.append(" update user set "); if(StringUtils.isNoneBlank(user.getUsername())){ sql.append(" username= ?, "); params.add(user.getUsername()); } if(StringUtils.isNoneBlank(user.getPassword())){ sql.append(" password= ?,"); params.add(user.getPassword()); } String strSql=sql.toString().substring(0, sql.toString().length()-1)+" where id=? "; params.add(user.getId()); return dao.execute(strSql, params); }}

User类

package com.javaweb.bean;public class User { private String id; private String username; private String password; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; }}

servlet

package com.javaweb.action;import java.io.IOException;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang3.StringUtils;import com.javaweb.bean.User;import com.javaweb.dao.UserDao;public class ServletSql extends HttpServlet{ /** * 用于版本控制 */ private static final long serialVersionUID = -2357925750878300415L; private UserDao dao=new UserDao(); @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // TODO Auto-generated method stub doPost(req, resp); } @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // TODO Auto-generated method stub //纯碎是用来判断有没有错误 req.setCharacterEncoding("UTF-8"); String op=req.getParameter("op"); if(StringUtils.isNotBlank(op)){ if("queryAll".equalsIgnoreCase(op)){ queryAll(req, resp); }else if("add".equalsIgnoreCase(op)){ add(req, resp); }else if("delete".equalsIgnoreCase(op)){ delete(req, resp); }else if("update".equalsIgnoreCase(op)){ update(req, resp); }else{ } } } private void update(HttpServletRequest req, HttpServletResponse resp) { // TODO Auto-generated method stub User user; try { user = init(req,resp); user.setId(req.getParameter("id")); int rows=dao.update(user); if(rows>0){ queryAll(req, resp); } } catch (ServletException|IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private void delete(HttpServletRequest req, HttpServletResponse resp) { // TODO Auto-generated method stub User user=new User(); String id=req.getParameter("id"); user.setId(id); int rows=dao.delete(user); if(rows>0){ try { queryAll(req, resp); } catch (ServletException|IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public User init(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { User user=new User(); String id=req.getParameter("id");// user.setId(Integer.parseInt(id)); user.setId(id); String name=req.getParameter("name"); user.setUsername(name); String password=req.getParameter("password");// user.setPassword(Integer.parseInt(password)); user.setPassword(password); return user; } public void add(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { User User=init(req,resp); int rows=dao.add(User); if(rows>0){ queryAll(req, resp); }else{ resp.sendRedirect("index.jsp"); } } public void queryAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { List<User> list=dao.findAll(); req.setAttribute("list", list); req.getRequestDispatcher("/queryAll.jsp").forward(req, resp);// resp.sendRedirect("queryAll.jsp"); }}

有几个方法后接throws ServletException, IOException应该会更加的整洁

.properties文件

#u6570u636eu8fdeu63a5u914du7f6edb.driver=com.mysql.jdbc.Driverdb.url=jdbc:mysql://localhost:3306/javawebdb.userName=rootdb.password=123456789

DateUtils

package com.javaweb.utils;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.Date;public class DateUtlis { /** * 获取当前日期 * @return */ public static String getCurrentTime() { String returnStr = null; SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); Date date = new Date(); returnStr = f.format(date); return returnStr; } public static Date getDate(String strDate){ Date date=null; SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd"); try { date = f.parse(strDate); } catch (ParseException e) { e.printStackTrace(); } return date; } public static String getStrDate(Date date){ String strDate=null; SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd"); strDate = f.format(date); return strDate; }}

DButils

package com.javaweb.utils;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;/** * 数据库连接类 * @author SUMMER * */public class DBUtlis { private static String driver; private static String url; private static String userName; private static String password; static{ driver=PropertiesUtil.getProperty("db.driver"); url=PropertiesUtil.getProperty("db.url"); userName=PropertiesUtil.getProperty("db.userName"); password=PropertiesUtil.getProperty("db.password"); } /** * 创建数据库连接 * @return */ public static Connection getConnection() { Connection conn = null; try { Class.forName(driver); conn = java.sql.DriverManager.getConnection(url,userName, password); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } return conn; } public static void close(Connection conn, PreparedStatement psm,ResultSet rs) { close(rs); close(psm); close(conn); } public static void close(Connection conn, Statement st,ResultSet rs) { close(rs); close(st); close(conn); } public static void close(Connection conn, PreparedStatement psm) { close(psm); close(conn); } public static void close(Connection conn) { if (null != conn) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(ResultSet rs) { if (null != rs) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(PreparedStatement psm) { if (null != psm) { try { psm.close(); } catch (SQLException e) { e.printStackTrace(); } } } public static void close(CallableStatement proc) { if (null != proc) { try { proc.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * @param st */ public static void close(Statement st) { if (null != st) { try { st.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * @param conn */ public static void rollback(Connection conn) { if(conn != null) { try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } } } /** * @param conn * @param auto */ public static void setAutoCommit(Connection conn, boolean auto) { if(conn != null) { try { conn.setAutoCommit(auto); } catch (SQLException e1) { e1.printStackTrace(); } } } public static void main(String[] args) { DBUtlis.getConnection(); }}

propertiesUtils

package com.javaweb.utils;import java.io.IOException;import java.io.InputStream;import java.util.Properties;/** * 读取properties文件 * @author SUMMER * */public class PropertiesUtil { private static Properties prop; /** * 加载.properties文件,使用文件流 */ private static void init() { prop = new Properties(); InputStream is=PropertiesUtil.class.getResourceAsStream("/config/db.properties"); try { prop.load(is); } catch (IOException e) { e.printStackTrace(); } } /** * 获取value * @param key * @return */ public static String getProperty(String key) { if (prop == null ) { init(); } return prop.get(key).toString(); } public static void main(String[] args) { System.out.println(getProperty("db.driver")); }}

StringUtils

package com.javaweb.utils;import java.util.UUID;/** * 字符串处理工具类 * */public class StringUtlis { /** * 获取唯一ID * @return */ public static String getUUID() { UUID uuid = UUID.randomUUID(); String str = uuid.toString(); return str.replace("-", ""); } /** * 将驼峰式命名的字符串转换为下划线大写方式。如果转换前的驼峰式命名的字符串为空，则返回空字符串。</br> * 例如：HelloWorld->HELLO_WORLD * @param name 转换前的驼峰式命名的字符串 * @return 转换后下划线大写方式命名的字符串 */ public static String underscoreName(String name) { StringBuilder result = new StringBuilder(); if (name != null && name.length() > 0) { // 将第一个字符处理成大写 result.append(name.substring(0, 1).toUpperCase()); // 循环处理其余字符 for (int i = 1; i < name.length(); i++) { String s = name.substring(i, i + 1); // 在大写字母前添加下划线 if (s.equals(s.toUpperCase()) && !Character.isDigit(s.charAt(0))) { result.append("_"); } // 其他字符直接转成大写 result.append(s.toUpperCase()); } } return result.toString(); } /** * 将下划线大写方式命名的字符串转换为驼峰式。如果转换前的下划线大写方式命名的字符串为空，则返回空字符串。</br> * 例如：HELLO_WORLD->HelloWorld * @param name 转换前的下划线大写方式命名的字符串 * @return 转换后的驼峰式命名的字符串 */ public static String camelName(String name) { StringBuilder result = new StringBuilder(); // 快速检查 if (name == null || name.isEmpty()) { // 没必要转换 return ""; } else if (!name.contains("_")) { // 不含下划线，仅将首字母小写 return name.substring(0, 1).toLowerCase() + name.substring(1); } // 用下划线将原始字符串分割 String camels[] = name.split("_"); for (String camel : camels) { // 跳过原始字符串中开头、结尾的下换线或双重下划线 if (camel.isEmpty()) { continue; } // 处理真正的驼峰片段 if (result.length() == 0) { // 第一个驼峰片段，全部字母都小写 result.append(camel.toLowerCase()); } else { // 其他的驼峰片段，首字母大写 result.append(camel.substring(0, 1).toUpperCase()); result.append(camel.substring(1).toLowerCase()); } } return result.toString(); } public static void main(String[] args) { System.out.println(getUUID()); } }

utils就是工具类了

拿来就用，大多数情况下都有很多高手写jar包含他们

但是我没用那些jar直接上网找的

要求分门别类写，是个好习惯

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><% String path = request.getContextPath();// 获得当前的项目根目录路径 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path; //完整路径%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>这是首页</title></head><body> <table border=0 cellpadding=0 cellspacing=0 style="margin:auto;border-collapse:separate; border-spacing:10px;"> <tr> <td> <a href="<%=basePath%>/servletSql?op=queryAll">查询全部</a> </td> </tr> <tr> <td> <a href="<%=basePath%>/add.jsp">添加</a> </td> </tr> <tr> <td> <a href="<%=basePath%>/delete.jsp">删除</a> </td> </tr> <tr> <td> <a href="<%=basePath%>/update.jsp">修改</a> </td> </tr> </table> </body></html>

add

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% String path = request.getContextPath();// 获得当前的项目根目录路径 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path; //完整路径%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body><form action="<%=basePath%>/servletSql?op=add" method="post"> <table align="center" border="1"> <tr> <th colspan="3"> <label>添加信息</label> </th> </tr> <tr> <td>Id</td> <td> <input type="text" name="id" > </td> </tr> <tr> <td>用户名</td> <td> <input type="text" name="name" > </td> </tr> <tr> <td>密码</td> <td> <input type="text" name="password" > </td> </tr> <tr> <td> </td> <td> <input type="submit" value="保存"> <input type="reset" value="清空"> </td> </tr> </table></form></body></html>

delete

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% String path = request.getContextPath();// 获得当前的项目根目录路径 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path; //完整路径%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body><form action="<%=basePath%>/servletSql?op=delete" method="post"> <table align="center" border="1"> <tr> <th colspan="3"> <label>删除信息</label> </th> </tr> <tr> <td>Id</td> <td> <input type="text" name="id" > </td> </tr> <tr> <td> </td> <td> <input type="submit" value="保存"> <input type="reset" value="清空"> </td> </tr> </table></form></body></html>

queryAll

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% String path = request.getContextPath();// 获得当前的项目根目录路径 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path; //完整路径%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body><form action="<%=basePath%>/servletSql?op=delete" method="post"> <table align="center" border="1"> <tr> <th colspan="3"> <label>删除信息</label> </th> </tr> <tr> <td>Id</td> <td> <input type="text" name="id" > </td> </tr> <tr> <td> </td> <td> <input type="submit" value="保存"> <input type="reset" value="清空"> </td> </tr> </table></form></body></html>

update

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <% String path = request.getContextPath();// 获得当前的项目根目录路径 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path; //完整路径%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8"><title>Insert title here</title></head><body><form action="<%=basePath%>/servletSql?op=update" method="post"> <table align="center" border="1"> <tr> <th colspan="3"> <label>修改信息</label> </th> </tr> <tr> <td>Id</td> <td> <input type="text" name="id" > </td> </tr> <tr> <td>用户名</td> <td> <input type="text" name="name" > </td> </tr> <tr> <td>密码</td> <td> <input type="text" name="password" > </td> </tr> <tr> <td> </td> <td> <input type="submit" value="保存"> <input type="reset" value="清空"> </td> </tr> </table></form></body></html>

jsp-2 简单的servlet连接mysql数据库 增删改查

标签：insert   dal   tco   dtd   let   ble   iat   tps   manager   

小编还为您整理了以下内容，可能对您也有帮助：

一个servlet里实现增删改查??

第一步: 连接数据库

第二部： 查询出数据(可以加入条件) 并通过( jstl \ el )展现到页面

第三部 : 在你展现数据的最后添加一列(操作列：有删除、修改)

第四部：进行相关操作

一个servlet里实现增删改查??

第一步: 连接数据库

第二部： 查询出数据(可以加入条件) 并通过( jstl \ el )展现到页面

第三部 : 在你展现数据的最后添加一列(操作列：有删除、修改)

第四部：进行相关操作

jsp和数据库（sqlserver）连接后，系统进行增删改查，这些操作是怎么实现的，详细具体点拜托

建议使用MVC模式做，JSP页面提交相应的操作后，提交给Servlet，Servlet中调用Model中定义的增删改查方法，方法调用后返回结果，然后通过Servlet返回给JSP页面。对于前台的增删改查跟数据库中中新建查询的操作是一样的，只是JSP页面增删改查是调用数据库查询语句封装的函数方法而已！

用jsp servlet jdbc 做个学生管理系统，实现增删改查，改的步骤应该是什么？

先jsp调用servlet操控数据库查询要修改的数据显示到页面上

再修改数据提交到后台调用数据库更新数据