web项目实现mysql增删改查并从前端页面操作

1.看下各个包下面的文件,我上一篇文章已经说过了,这里对上一章有一部分重复的

2.User.java是数据库元素写的一个类,代码如下

package com.hqyj.wj.model;
//用户信息表
public class User {
	 private int id;
	 private String name;
	 private String birthday;
//	 public User(int id,String name,String birthday){
//		 this.id=id;
//		 this.name=name;
//		 this.birthday=birthday;
//	 }
	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 getBirthday() {
		return birthday;
	}
	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}
}

　3.UserDaoInf是一个接口实现数据库的增删查改方法,代码如下

package com.hqyj.wj.dao.inf;
import java.util.List;
import com.hqyj.wj.model.User;
/**
 * 数据访问层的接口定义数据接口的方法
 * 
 */
public interface UserDaoInf {
    //定义一个查询方法
	List<User> search();
     //定义数据库的插入
	int insert(User user);
    //定义跟新数据库
	int update(User user);
     //通过名字删除数据元素
	int delete(String name);
	
}

4.UserDao实现UserDaoInf接口,并 连接数据库

package com.hqyj.wj.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.hqyj.wj.dao.inf.UserDaoInf;
import com.hqyj.wj.model.User;

/**
 * 
 * @author wl 数据访问接口
 */
public class UserDao implements UserDaoInf {
	// 数据访问数据库的连接对象
	protected Connection con = null;
	// 预编译你写的sql语句
	protected PreparedStatement ps = null;
	// 查询预编译的sql语句
	protected ResultSet rs = null;

	// 获取数据库链接
	@SuppressWarnings("finally")
	public Connection getCon() {
		try {
			// 加载mysql驱动
			Class.forName("com.mysql.jdbc.Driver");
			// 获取数据库链接
			con = DriverManager.getConnection(
					"jdbc:mysql://127.0.0.1:3306/image?characterEncoding=utf8",
					"root", "root");
			System.out.println("链接成功");
			return con;
		} catch (Exception e) {
			System.out.println("链接失败" + e.getMessage());
			e.printStackTrace();
			return null;
		}

	}

	/**
	 * 查询方法
	 */

	public List<User> search() {
                //定义一个列表接收数据库数据
		List<User> list = new ArrayList<User>();
		try {
			// 定义一个sql语句
			// String
			// sql="SELECT a.id as 序号,a.salary as '薪水',b.`name` as '姓名'  from salary a LEFT JOIN `user` b on a.u_id=b.id";
			String sql = "SELECT * from user";
			// 获取数据库连接
			con = getCon();
			// 预编译sql语句
			ps = con.prepareStatement(sql);
			// 把编译出来的结果集装载到ResultSet对象里面
		          rs=ps.executeQuery();
			// 取出ResultSet里的结果集装载到数据模型里
			while (rs.next()) {
				User user = new User();
				user.setName(rs.getString("name"));
				user.setBirthday(rs.getString("birthday"));
				user.setId(Integer.parseInt(rs.getString("id")));
				list.add(user);
			}
		} catch (Exception e) {
			System.out.println("查询错误" + e.getMessage());
		} finally {
			try {
				rs.close();
				ps.close();
				con.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}

		return list;
	}

	public int insert(User user) {
  
		int i=0;
		try {
			// 定义一个sql语句
			// String
			// sql="SELECT a.id as 序号,a.salary as '薪水',b.`name` as '姓名'  from salary a LEFT JOIN `user` b on a.u_id=b.id";
			String sql = "insert into user(name,birthday) values(?,?)";
			// 获取数据库连接
			con = getCon();
			// 预编译sql语句
			ps = con.prepareStatement(sql);
			ps.setString(1, user.getName());
			ps.setString(2, user.getBirthday());
			
		    i=ps.executeUpdate();

		} catch (Exception e) {
			System.out.println("查询错误" + e.getMessage());
		} finally {
			try {
				
				ps.close();
				con.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
      
		return i;
	}
   //跟新信息
	public int update(User user) {
		int i=0;
		try {
			// 定义一个sql语句
			// String
			// sql="SELECT a.id as 序号,a.salary as '薪水',b.`name` as '姓名'  from salary a LEFT JOIN `user` b on a.u_id=b.id";
			String sql = "update user set name='" + user.getName() +"' where id='" + user.getId() + "'";
			// 获取数据库连接
			con = getCon();
			// 预编译sql语句
			ps = con.prepareStatement(sql);
		    i=ps.executeUpdate();

		} catch (Exception e) {
			System.out.println("查询错误" + e.getMessage());
		} finally {
			try {
				
				ps.close();
				con.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return i;
	}

	public int delete(String name) {
		int i=0;
		try {
			// 定义一个sql语句
			// String
			// sql="SELECT a.id as 序号,a.salary as '薪水',b.`name` as '姓名'  from salary a LEFT JOIN `user` b on a.u_id=b.id";
			String sql = "delete from user where name='"+name+"'";
			// 获取数据库连接
			con = getCon();
			// 预编译sql语句
			ps = con.prepareStatement(sql);
		    i=ps.executeUpdate();

		} catch (Exception e) {
			System.out.println("查询错误" + e.getMessage());
		} finally {
			try {
			
				ps.close();
				con.close();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
		}
		return i;
	}

}

5.UserServiceInf也是对数据库的增删改查方法和获取前端数据对数据库的增删改查操作方法

package com.hqyj.wj.service.inf;

import java.io.PrintWriter;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.hqyj.wj.model.User;

public interface UserServiceInf {
        
	List<User> search();
	int insert(User user);
	int update(User user);
	int delete(String name);
     //里面用ajax获取前端来的数据,并操作数据库的方法
	public void ss(HttpServletRequest request,HttpServletResponse response,PrintWriter out);
}

6.UserService实现UserService接口

　

package com.hqyj.wj.service;

import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;

import com.hqyj.wj.dao.UserDao;
import com.hqyj.wj.dao.inf.UserDaoInf;
import com.hqyj.wj.model.User;
import com.hqyj.wj.service.inf.UserServiceInf;

/**
 * 逻辑服务层实现类
 */
public class UserService implements UserServiceInf {
	UserDaoInf us = new UserDao();

	public List<User> search() {
              //返回的是 UserDao里面的search()方法
		return us.search();
	}

	public int insert(User user) {
              //调用了UserDao()里的insert()方法
		int i = us.insert(user);
		return i;
	}

	public int update(User user) {
		// TODO Auto-generated method stub
		int i = us.update(user);

		return i;
	}

	public int delete(String name) {
		int i = us.delete(name);
		return i;
	}

	public void ss(HttpServletRequest request, HttpServletResponse response,
			PrintWriter out) {

		// serverInder是从前端获取的数,目的是为了区分是增删改查的那个操作
                //request.getParameter()是获取拼在url地址栏下的值
		int serverInder = Integer.parseInt(request.getParameter("serverIndex"));
                //实现查询-----------------------
		if (serverInder == 1) {
			List<User> list = search();
			// 把list数据解析成前端页面能读取的数据
			JSONArray json = JSONArray.fromObject(list);
			out.print(json.toString());
		}
		// 现实插入---------------------------
		if (serverInder == 2) {
			String name = request.getParameter("name");
			String birthday = request.getParameter("birthday");
			User user = new User();
			try {
                                //解决从前端传到服务器(数据库)乱码问题
				name = new String(name.getBytes("ISO-8859-1"), "UTF-8");
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				birthday = new String(birthday.getBytes("ISO-8859-1"), "UTF-8");
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			user.setName(name);
			user.setBirthday(birthday);
			int i = insert(user);
			if (i == 1) {
				out.print("插入数据成功");
				System.out.println("插入数据成功");
			} else {
				out.print("插入数据失败");
				System.out.println("插入数据失败");
			}

		}

		// 实现删除
		if (serverInder == 3) {
			String name = request.getParameter("name");
			try {
				name = new String(name.getBytes("ISO-8859-1"), "UTF-8");
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			int i = delete(name);
			if (i == 1) {
				out.print("删除数据成功");
				System.out.println("删除数据成功");
			} else {
				out.print("删除数据失败" + i);
				System.out.println("删除数据失败" + i);
			}
		}

		// 实现更新
		if (serverInder == 4) {
			User user = new User();
			int id = Integer.parseInt(request.getParameter("id"));
			String name = request.getParameter("name");
			String birthday = request.getParameter("birthday");
			try {
				birthday = new String(birthday.getBytes("ISO-8859-1"), "UTF-8");
			} catch (UnsupportedEncodingException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try {
				name = new String(name.getBytes("ISO-8859-1"), "UTF-8");
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			user.setId(id);
			user.setName(name);
			user.setBirthday(birthday);

			int i = update(user);
			if (i == 1) {
				out.print("更改数据成功");
				System.out.println("更改数据成功");
			} else {
				out.print("更改数据失败" + i);
				System.out.println("更改数据失败" + i);
			}
			
			
		}
	}

}

7.controller控制器是调用UserService里面的方法,实现对前端页面操作数据库

package com.hqyj.wj.controller;

import java.io.IOException;
import java.io.PrintWriter;

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 com.hqyj.wj.service.*;
import com.hqyj.wj.service.inf.*;
import com.hqyj.wj.model.*;

import java.util.List;
import java.util.ArrayList;

public class OneServlet extends HttpServlet {

	public OneServlet() {
		super();
	}

	public void destroy() {
		super.destroy(); // Just puts "destroy" string in log
		// Put your code here
	}
  //doget对应的是ajax的$.get()方法
  //request是装载请求数据
 //response响应数据到前端对象
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
      		//解决中文乱码的问题
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		//在服务器端设置允许在其他域名下访问，及响应类型、响应头设置
		//这三句解决的是跨域问题
		response.setHeader("Access-Control-Allow-Origin", "*");
		response.setHeader("Access-Control-Allow-Methods","POST");
		response.setHeader("Access-Control-Allow-Headers","x-requested-with,content-type");
		  //响应的文本格式
		response.setContentType("text/html");
		//获取响应的输出对象
		PrintWriter out = response.getWriter();
		
		
		UserServiceInf service=new UserService();
		
		String num = request.getParameter("serverIndex");
		
		service.ss(request,response,out);
		
		
		//这里全部拿到UserService的ss()方法里了
		//实现查询----------------------
		/*int serverInder=Integer.parseInt(request.getParameter("serverIndex"));
		if(serverInder==1){
			List<User> list=service.search();
			//把list数据解析成前端页面能读取的数据
			JSONArray json=JSONArray.fromObject(list);
			out.print(json.toString());
		}
		//现实插入---------------------------
		if(serverInder==2){
			String name=request.getParameter("name");
			String birthday=request.getParameter("birthday");
		   User user=new User();
		   name=new String(name.getBytes("ISO-8859-1"),"UTF-8");
		   birthday=new String(birthday.getBytes("ISO-8859-1"),"UTF-8");
		   user.setName(name);
		   user.setBirthday(birthday);
		   int i=service.insert(user);
		   if(i==1){
			   out.print("插入数据成功");
			   System.out.println("插入数据成功");
		   }else{
			   out.print("插入数据失败");
			   System.out.println("插入数据失败");
		   }
			
		}*/
		out.flush();
		out.close();
	}
	  //doget对应的是ajax的$.post()方法
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("text/html");
		PrintWriter out = response.getWriter();
		
		out.flush();
		out.close();
	}

	public void init() throws ServletException {
		// Put your code here
	}

}

8.前端实现html代码　

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>ajax获取集合</title>
<script src="../js/jquery-3.1.1.min.js"></script>
</head>
<body>
    <table>
        <thead>
            <tr>
                <th>id</th>
                <th>姓名</th>
                <th>生日</th>
            </tr>
        </thead>
        <tbody>
        </tbody>
    </table>
    名字:<input type="text" placeholder="请输入姓名" id="name">
    生日:年<select>
    <option>1992</option>
    <option>1993</option>
    <option>1994</option>
    <option>1995</option>
    </select>
    月<select>
    <option>1</option>
    <option>2</option>
    <option>3</option>
    <option>4</option>
    </select>
    日<select>
    <option>2</option>
    <option>12</option>
    <option>22</option>
    <option>23</option>
    </select>
    <button>点击获取数据</button>
    <button>点击插入数据</button><br>
    请输入删除的名字:<input type="text" id="removename">
    <button>点击删除数据</button><br>
    请输入需要修改的id号:<input type="text" id="updateid"><br>
    请输入需要修改后的名字:名字:<input type="text" placeholder="请输入姓名" id="updatename"><br>
    请输入需要修改的后生日:生日:年<select>
    <option>1992</option>
    <option>1993</option>
    <option>1994</option>
    <option>1995</option>
    </select>
    月<select>
    <option>1</option>
    <option>2</option>
    <option>3</option>
    <option>4</option>
    </select>
    日<select>
    <option>2</option>
    <option>12</option>
    <option>22</option>
    <option>23</option>
    </select><br>
    <button>点击跟新数据</button>
    
    <script>
    $(function(){
        var str;
         //serverIndex区分增删改查的变量
       var serverIndex;
        //点击查看数据
        $("button").eq(0).click(function(){
            var str;
            serverIndex=1;
            //{serverIndex:serverIndex}是拼在地址栏上的,从后端获取他的值
            $.get("http://localhost:8080/jquery/servlet/OneServlet",{serverIndex:serverIndex},function(data){
            console.log(data);
            var num=eval(data);
            for(var i=0;i<num.length;i++)
                {
                str+=" <tr> <td>"+num[i].id+"</td> <td>"+num[i].name+"</td> <td>"+num[i].birthday+"</td></tr>";
            }
         $("tbody").html(str); 
         
            })
        })
        //点击插入数据
        $("button").eq(1).click(function(){
          //获取输入名字的值
          serverIndex=2;
          var name=$("#name").val();
          var year=$("select:eq(0) option:selected").val();
           var mouth=$("select:eq(1) option:selected").val();
           var day=$("select:eq(2) option:selected").val();
          var birthday=year+"/"+mouth+"/"+day;
           console.log(birthday);
           $.get("http://localhost:8080/jquery/servlet/OneServlet",{serverIndex:serverIndex,name:name,birthday:birthday},function(data){
               console.log(data);
           })
        
        })
        
        //点击删除数据
        $("button").eq(2).click(function(){
          //获取输入名字的值
          serverIndex=3;
          var name=$("#removename").val();
           $.get("http://localhost:8080/jquery/servlet/OneServlet",{serverIndex:serverIndex,name:name},function(data){
               console.log(data);
           })
          
        })
        //点击跟新数据
        $("button").eq(3).click(function(){
          //获取输入名字的值
          serverIndex=4;
          var id=$("#updateid").val();
          var name=$("#updatename").val();
          var year=$("select:eq(3) option:selected").val();
           var mouth=$("select:eq(4) option:selected").val();
           var day=$("select:eq(5) option:selected").val();
          var birthday=year+"/"+mouth+"/"+day;
           $.get("http://localhost:8080/jquery/servlet/OneServlet",{serverIndex:serverIndex,name:name,id:id,birthday:birthday},function(data){
               console.log(data);
           })
          
        })
        })
         
</script>
</body>
</html>