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

连接MYSQL数据库的简单增删改查

项目框架如图所示：

所需工具：

所需工具：

所需工具：

所需工具：

程序代码：package com.user.action; import java.io.IOException;

import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import com.user.bean.UserBean; import com.user.dao.UserDao;

public class InsertOrUpdateAction extends HttpServlet {

/** * */

private static final long serialVersionUID = 1L; private UserDao userDao ;

public void doGet(HttpServletRequest request, HttpServletResponse response throws ServletException, IOException {

doPost(request, response; }

public void doPost(HttpServletRequest request, HttpServletResponse response throws ServletException, IOException {

request.setCharacterEncoding(\; response.setCharacterEncoding(\;

userDao = new UserDao(; String str = request.getRequestURI(; str = str.substring(str.lastIndexOf(\+1; String yx = request.getParameter(\; String zy = request.getParameter(\; String age = request.getParameter(\; String dh = request.getParameter(\;

String qq = request.getParameter(\; String sex = request.getParameter(\; UserBean userBean = new UserBean(; userBean.setYx(yx; userBean.setZy(zy; if(!\.equals(age&&age!=null {

userBean.setAge(Integer.valueOf(age; }else{

userBean.setAge(0; }

userBean.setDh(dh; userBean.setQq(qq; userBean.setSex(sex; if(\.equals(str{ if(userDao.insertUser(userBean{ response.sendRedirect(\; }else{

response.sendRedirect(\; }

}else if(\.equals(str{ String strId = request.getParameter(\; if(!\.equals(strId&&strId!=null{ userBean.setId(Integer.valueOf(strId; }else{

userBean.setId(-1; }

if(userDao.updateUser(userBean{ response.sendRedirect(\; }else{

response.sendRedirect(\; } } } }

package com.user.action; import java.io.IOException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class LoginAction extends HttpServlet { /** * */

private static final long serialVersionUID = 1L;

public void doGet(HttpServletRequest request, HttpServletResponse response throws ServletException, IOException { doPost(request, response; }

public void doPost(HttpServletRequest request, HttpServletResponse response throws ServletException, IOException { String name= request.getParameter(\; String password = request.getParameter(\;

if(\.equals(name||\.equals(password {

response.sendRedirect(\; }else{

if(\.equals(name&&\.equals(password {

response.sendRedirect(\; }else{

response.sendRedirect(\; } } } }

package com.user.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 com.user.bean.UserBean; import com.user.dao.UserDao;

public class UserAction extends HttpServlet { /** * */

private static final long serialVersionUID = 1L; private UserDao userDao ;

public void doGet(HttpServletRequest request, HttpServletResponse response throws ServletException, IOException { doPost(request, response; }

public void doPost(HttpServletRequest request, HttpServletResponse response throws ServletException, IOException { request.setCharacterEncoding(\response.setCharacterEncoding(%userDao = new UserDao(;

String str = request.getRequestURI(; str = str.substring(str.lastIndexOf(\if(\{

List list = userDao.getAllUser(; request.setAttribute(\

request.getRequestDispatcher(\}else if(\{

String strId = request.getParameter(\int id = Integer.valueOf(strId;

UserBean userBean = userDao.selectUserById(id; request.setAttribute(\

request.getRequestDispatcher(\

}else if(\

String strId = request.getParameter(\int id = Integer.valueOf(strId; if(userDao.deleteUser(id{

response.sendRedirect(\}else{

response.sendRedirect(\} } } }

package com.user.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;

import com.user.bean.UserBean; import com.util.DBUtils; public class UserDao { private DBUtils dbUtil; /**

* 查询所有的user * @return */

public List getAllUser( {

dbUtil = new DBUtils(; List list = new ArrayList (; String sql = \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++; userBean.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(; }finally{ dbUtil.close(; } return list; } /**

* 根据id查询user * @param id * @return */

public UserBean selectUserById(int id{ dbUtil = new DBUtils(;

UserBean userBean = new UserBean(;

String sql = \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.getString(i++; }

} catch (SQLException e { e.printStackTrace(; }

return userBean; } /** * 插入用户 * @param userBean * @return */

public boolean insertUser(UserBean userBean{ dbUtil = new DBUtils(;

String sql = \

int row = dbUtil.excete(sql, new

Object[]{userBean.getYx(,userBean.getZy(,userBean.getAge(,userBean.getDh(,userBean.getQq(,userBean.getSex(}; if(row>0{

return true; }

return false; } /** * 删除用户 * @param id * @return */

public boolean deleteUser(int id{ dbUtil = new DBUtils(;

String sql = \int row = dbUtil.excete(sql; if(row>0 {

return true; }

return false; } /** * 修改user * @param userBean * @return */

public boolean updateUser(UserBean userBean{ dbUtil = new DBUtils(;

String sql = \

int row = dbUtil.excete(sql, new

Object[]{userBean.getYx(,userBean.getZy(,userBean.getAge(,userBean.getDh(,userBean.getQq(,userBean.getSex(,userBean.getId(}; if(row>0

{

return true; }

return false; } }

package com.user.bean; public class UserBean { private int id;

private String name;//电话 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 { this.name = name;

}

public String getYx( { return 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 void setQq(String qq { this.qq = qq;

}

public String getSex( { return sex; }

public void setSex(String sex { this.sex = sex; } }

package com.user.dao; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List;

import com.user.bean.UserBean; import com.util.DBUtils; public class UserDao { private DBUtils dbUtil; /**

* 查询所有的user * @return */

public List getAllUser( {

dbUtil = new DBUtils(;

List list = new ArrayList (; String sql = \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++; userBean.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(; }finally{ dbUtil.close(; } return list; }

/**

* 根据id查询user * @param id * @return */

public UserBean selectUserById(int id{ dbUtil = new DBUtils(;

UserBean userBean = new UserBean(;

String sql = \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.getString(i++; }

} catch (SQLException e { e.printStackTrace(; }

return userBean; } /** * 插入用户 * @param userBean * @return */

public boolean insertUser(UserBean userBean{ dbUtil = new DBUtils(;

String sql = \

int row = dbUtil.excete(sql, new

Object[]{userBean.getYx(,userBean.getZy(,userBean.getAge(,userBean.getDh(,userBean.getQq(,userBean.getSex(}; if(row>0{ return true; }

return false; } /** * 删除用户 * @param id * @return */

public boolean deleteUser(int id{ dbUtil = new DBUtils(;

String sql = \

int row = dbUtil.excete(sql; if(row>0 { return true; }

return false; } /** * 修改user * @param userBean * @return */

public boolean updateUser(UserBean userBean{ dbUtil = new DBUtils(;

String sql = \int row = dbUtil.excete(sql, new

Object[]{userBean.getYx(,userBean.getZy(,userBean.getAge(,userBean.getDh(,userBean.getQq(,userBean.getSex(,userBean.getId(}; if(row>0 { return true; }

return false; } }

package com.util;

import java.sql.Date;

import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.mysql.jdbc.Connection; import com.mysql.jdbc.Statement; public class DBUtils {

private static Connection conn=null; private Statement st=null; private ResultSet rs = null; private PreparedStatement ps; static{ try {

Class.forName(\

conn = (Connection

DriverManager.getConnection(\acterEncoding=UTF-8\} catch (ClassNotFoundException e { e.printStackTrace(; } catch (SQLException e { e.printStackTrace(; } } /** *

* @Title: exceteSQl

* @Description: TODO(执行不带参数的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 ResultSet * @throws */

public ResultSet exceteSQl(String sql, Object[] obj {

int 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 * @return int * @throws */

public int excete(String sql{ int row =0; try {

st = (Statement conn.createStatement(; row = st.executeUpdate(sql;

} catch (SQLException e { e.printStackTrace(; }finally{ close(; } return row; } /** *

* @Title: excete

* @Description: TODO(此方法适用带参数的修改，删除，增加 * @param sql * @param obj * @return int * @throws */

public int excete(String sql, Object[] obj { int row =0;

int length = obj.length; try {

ps = conn.prepareStatement(sql; preparStateSql(obj, length; row = ps.executeUpdate(; } catch (SQLException e {

e.printStackTrace(; }finally{ close(; } return row; } /** *

* @Title: preparStateSql

* @Description: TODO(参数赋值 * @param obj * @param length

* @throws SQLException void * @throws */

private void preparStateSql(Object[] obj, int length throws SQLException { for(int i=0;i {

if(obj[i].getClass(==String.class {

ps.setString(i+1, obj[i].toString(; }else if(obj[i].getClass(==Integer.class {

ps.setInt(i+1, (Integerobj[i];

}else if(obj[i].getClass(==Double.class

{

ps.setDouble(i+1, (Doubleobj[i];

}else if(obj[i].getClass(==Date.class//java.sql.Date {

ps.setDate(i+1, (Dateobj[i];

} } } /** *

* @Title: close

* @Description: TODO(数据库连接关闭 void * @throws */

public void close( { if(rs!=null { try { rs.close(;

} catch (SQLException e { e.printStackTrace(; } } if(st!=null { try { st.close(;

} catch (SQLException e {

e.printStackTrace(; } } if(ps!=null { try { ps.close(;

} catch (SQLException e { e.printStackTrace(; } } }

public static void main(String[] args { DBUtils db = new DBUtils(; String sql = \ResultSet rr = db.exceteSQl(sql; try { while(rr.next( { int i =1;

System.out.print(rr.getInt(i+++\System.out.print(rr.getString(i+++\System.out.print(rr.getString(i+++\System.out.print(rr.getInt(i+++\System.out.print(rr.getString(i+++\System.out.print(rr.getString(i+++\System.out.println(rr.getString(i++; }

} catch (SQLException e {

// TODO Auto-generated catch block

e.printStackTrace(; }finally{ db.close(; }

//String sql = \软件\\\测试\\\女\\\

/* String sql = \

int row = db.excete(sql, new Object[]{\软件\测试\女\//int row = db.excete(sql; if(row>0 {

System.out.println(\增加成功\}*/

/*String sql = \int row = db.excete(sql, new Object[]{\计通\if(row>0 {

System.out.println(\修改成功\}*/ } }

所需要的驱动包：

Web.xml配置：

xml version=\ encoding=\?>

xmlns=\

xmlns:xsi=\

xsi:schemaLocation=\ http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd\> display-name>

UserAction servlet-name>

com.user.action.UserAction servlet-class> servlet>

InsertOrUpdateAction servlet-name>

com.user.action.InsertOrUpdateAction servlet-class> servlet>

LoginAction servlet-name>

com.user.action.LoginAction servlet-class> servlet>

UserAction servlet-name> /SelectAllUser url-pattern> /SelectOneUser url-pattern> /DeleteUserById url-pattern> servlet-mapping>

InsertOrUpdateAction servlet-name> /InsertUserAction url-pattern> /UpdateUserAction url-pattern> servlet-mapping>

LoginAction servlet-name> /LoginAction url-pattern> servlet-mapping>

index.jsp welcome-file> welcome-file-list> web-app> Jsp:

<%@ page language=\ import=\ pageEncoding=\%> <%

String path = request.getContextPath(;

String basePath = request.getScheme(+\+request.getServerName(+\+request.getServerPort(+path+\; %>

DOCTYPE HTML PUBLIC \

\>