一.开启SQL Server Management Studio数据库服务
二.建库建表
create database db_yuchangglxt--渔场管理系统
use db_yuchangglxt--运行
create table tb_yc
(
bh int primary key identity(1,1),--编号
mc varchar(100),--名称
lx varchar(100),--类型
jg float,--价格
ms varchar(100)--描述
)
--插数据
insert into tb_yc values
--('某渔场1','真不错',9.9,'还不错,好多好多个鱼'),
('某渔场2','真不错',9.9,'还不错,好多好多个鱼'),
('某渔场3','真不错',9.9,'还不错,好多好多个鱼'),
('某渔场4','真不错',9.9,'还不错,好多好多个鱼'),
('某渔场5','真不错',9.9,'还不错,好多好多个鱼'),
('某渔场6','真不错',9.9,'还不错,好多好多个鱼'),
('某渔场7','真不错',9.9,'还不错,好多好多个鱼'),
('某渔场8','真不错',9.9,'还不错,好多好多个鱼'),
('某渔场9','真不错',9.9,'还不错,好多好多个鱼')
select *from tb_yc
三.用eclipse编写代码及方法
1.建五个包
2.布局一个首页
3.实现增加,删除,显示数据功能
代码如下
utils:DBHelper类
package com.utils;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import com.sun.corba.se.spi.presentation.rmi.PresentationManager;
import com.sun.xml.internal.fastinfoset.util.PrefixArray;
public class DBHelper {
static {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConn() {
Connection conn =null;
try {
conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433;DatabaseName=db_yuchangglxt","sa","123");
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void myClose(Connection conn, PreparedStatement ps, ResultSet rs) {
try {
if(conn!=null && conn.isClosed()) {
conn.close();
}
if(ps!=null) {
ps.close();
}
if(rs!=null) {
rs.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
entity:YC类
package com.entity;
public class YC {
// bh int primary key identity(1,1),--编号
// mc varchar(100),--名称
// lx varchar(100),--类型
// jg float,--价格
// ms varchar(100)--描述
private int bh;
private String mc;
private String lx;
private float jg;
private String ms;
public YC() {
// TODO Auto-generated constructor stub
}
public YC(String mc, String lx, float jg, String ms) {
super();
this.mc = mc;
this.lx = lx;
this.jg = jg;
this.ms = ms;
}
public YC(int bh, String mc, String lx, float jg, String ms) {
super();
this.bh = bh;
this.mc = mc;
this.lx = lx;
this.jg = jg;
this.ms = ms;
}
public int getBh() {
return bh;
}
public void setBh(int bh) {
this.bh = bh;
}
public String getMc() {
return mc;
}
public void setMc(String mc) {
this.mc = mc;
}
public String getLx() {
return lx;
}
public void setLx(String lx) {
this.lx = lx;
}
public float getJg() {
return jg;
}
public void setJg(float jg) {
this.jg = jg;
}
public String getMs() {
return ms;
}
public void setMs(String ms) {
this.ms = ms;
}
@Override
public String toString() {
return "YC [bh=" + bh + ", mc=" + mc + ", lx=" + lx + ", jg=" + jg + ", ms=" + ms + "]";
}
}
dao:IYCDao接口
package com.dao;
import java.util.List;
import com.entity.YC;
/**
* 数据访问层
* @author Administrator
*
*/
public interface IYCDao {
/**
* 查询所有
* @return
*/
List<YC> cxsy();
/**
* 删除渔场
*/
int scyc(int bh);
/**
*
* 增加渔场
*/
int zjyc(YC yc);
}
dao:接口实现类:YCDaoimpl
package com.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.entity.YC;
import com.utils.DBHelper;
public class YCDaoimpl implements IYCDao {
public static void main(String[] args) {
List<YC>cxsy =new YCDaoimpl().cxsy();
System.out.println(cxsy);
}
/**
* 查询所有
*/
@Override
public List<YC> cxsy() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
YC yc = null;
List<YC> list = new ArrayList<YC>();
int n = 0;
String sql = null;
try {
conn = DBHelper.getConn();
sql = "select*from tb_yc";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
yc = new YC();
yc.setBh(rs.getInt(1));
yc.setMc(rs.getString(2));
yc.setLx(rs.getString(3));
yc.setJg(rs.getFloat(4));
yc.setMs(rs.getString(5));
list.add(yc);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, null);
}
return list;
}
/**
* 删除渔场
*/
@Override
public int scyc(int bh) {
Connection conn = null;
PreparedStatement ps = null;
int n = 0;
String sql = null;
try {
conn = DBHelper.getConn();
sql = "dalete from tb_yc where bh="+bh;
ps = conn.prepareStatement(sql);
n=ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, null);
}
return n;
}
/**
* 增加渔场
*/
@Override
public int zjyc(YC yc) {
Connection conn = null;
PreparedStatement ps = null;
int n = 0;
String sql = null;
try {
conn = DBHelper.getConn();
sql = "insert into tb_yc values(?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, yc.getMc());
ps.setString(2, yc.getLx());
ps.setFloat(3, yc.getJg());
ps.setString(4, yc.getMs());
n=ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
DBHelper.myClose(conn, ps, null);
}
return n;
}
}
biz接口类:IYCBiz
package com.biz;
import java.util.List;
import com.entity.YC;
public interface IYCBiz {
/**
* 查询所有
* @param gjc
* @param zd
* @return
*/
List<YC> cxsy();
/**
* 删除渔场
*/
int scyc(int bh);
/**
*
* 增加渔场
*/
int zjyc(YC yc);
}
biz接口实现类:YCBizimpl
package com.biz;
import java.util.List;
import com.dao.YCDaoimpl;
import com.entity.YC;
public class YCBizimpl implements IYCBiz{
public List<YC> cxsy() {
return new YCDaoimpl().cxsy();
}
@Override
public int scyc(int bh) {
return new YCDaoimpl().scyc(bh);
}
@Override
public int zjyc(YC yc) {
return new YCDaoimpl().zjyc(yc);
}
}
实现增加功能servlet类
package com.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.biz.IYCBiz;
import com.biz.YCBizimpl;
import com.entity.YC;
/**
* 新增图书的servlet控制器
*/
@WebServlet("/XZYCServlet")
public class XZYCServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.设置编码
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//获取编号
//int bh = Integer.valueOf(request.getParameter("bh"));
//名称
String mc = request.getParameter("mc");
//类型
String lx = request.getParameter("lx");
//价格
float jg = Float.valueOf(request.getParameter("jg"));
//描述
String ms = request.getParameter("ms");
//封装实体
YC yc = new YC(mc, lx, jg, ms);
//2.调用biz
IYCBiz isb = new YCBizimpl();
int n = isb.zjyc(yc);
if(n>0) {
response.getWriter().println("<script>alert('增加成功');location.href='CXSYServlet'</script>");
}else {
response.getWriter().println("<script>alert('增加失败');location.href='CXSYServlet'</script>");
}
}
}
实现删除功能servlet类
package com.servlet;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.biz.IYCBiz;
import com.biz.YCBizimpl;
/**
* 删除图书的servlet控制器
*/
@WebServlet("/SCTSServlet")
public class SCYCServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.设置编码
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//获取编号
int bh = Integer.valueOf(request.getParameter("bh"));
//2.调用biz
IYCBiz isb = new YCBizimpl();
int n = isb.scyc(bh);
if(n>0) {
response.getWriter().println("<script>alert('删除成功');location.href='CXSYServlet'</script>");
}else {
response.getWriter().println("<script>alert('删除失败');location.href='CXSYServlet'</script>");
}
}
}
实现显示数据servlet类
package com.servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.biz.IYCBiz;
import com.biz.YCBizimpl;
import com.entity.YC;
/**
* 查询所有的servlet控制器
*/
@WebServlet("/CXSYServlet")
public class CXSYServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//1.设置编码
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
//获取zd和gjc
String zd = request.getParameter("zd");
if(zd == null) {
zd = "mc";
}
String gjc = request.getParameter("gjc");
if(gjc == null) {
gjc = "";
}
//2.调用biz
IYCBiz isb = new YCBizimpl();
List<YC> cxsy = isb.cxsy();
//模糊查询
// List<YC> cxsy = isb.cxsy(zd, gjc);
//3.保存到域对象
HttpSession session = request.getSession();
session.setAttribute("cxsy", cxsy);
//4.跳转
response.sendRedirect("index.jsp");
}
}
主界面代码
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!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>
<c:if test="${empty cxsy }">
<jsp:forward page="CXSYServlet"></jsp:forward>
</c:if>
<center>
<h1>渔场管理系统首页</h1>
<hr/>
<form action = "CXSYServlet" method = "post">
<select name = "zd">
<option value = "mc">名称</option>
<option value = "lx">类型</option>
</select>
<input type ="text" name = "gjc"/>
<input type ="submit" value = "搜索"/>
</form>
<br><br>
<button onclick = "xzyc()">新增渔场</button>
<br><br>
<table border = "1" width = "60%">
<tr>
<td>编号</td>
<td>名称</td>
<td>类型</td>
<td>价格</td>
<td>操作</td>
</tr>
<c:forEach items="${cxsy }" var="yc">
<tr>
<td>${yc.bh }</td>
<td>${yc.mc }</td>
<td>${yc.lx }</td>
<td>${yc.jg }</td>
<td>
<button onclick = "scyc(${yc.bh})">删除</button>
<button onclick = "ckxq(${yc.bh})">查看详情</button>
</td>
</tr>
</c:forEach>
</table>
</center>
<script type="text/javascript">
//删除渔场的点击事件
function scyc(bh) {
//alert(bh)
if(confirm("你确定要删除该记录吗")){
location.href = "SCYCServlet?bh="+bh;
}
}
//新增渔场的点击事件
function xzyc() {
location.href = "xzyc.jsp";
}
//查看详情图书的点击事件
function ckxq(bh) {
location.href = "CZYCServlet1?bh="+bh;
}
</script>
</body>
</html>
主界面显示图片
实现增加功能xzyc.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!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>
<center>
<h1>新增渔场</h1>
<br/>
<form action = "XZYCServlet" method = "post">
<table border = "1">
<tr>
<td>名称</td>
<td><input type = "text" name = "mc"/></td>
</tr>
<tr>
<td>类型</td>
<td><input type = "text" name = "lx"/></td>
</tr>
<tr>
<td>价格</td>
<td><input type = "text" name = "jg"/></td>
</tr>
<tr>
<td>描述</td>
<td><input type = "text" name = "ms"/></td>
</tr>
<tr>
<td colspan = "2" align="center">
<input type = "submit" value = "确认新增"/>
<input type = "reset" value = "重置"/>
</td>
</tr>
</table>
</form>
</center>
</body>
</html>
实现显示数据功能cxsy.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!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>
<center>
<h1>查看渔场</h1>
<br/>
<table border = "1">
<tr>
<td>编号</td>
<td><input disabled="disabled" type = "text" name = "bh" value = "${ts2.bh }"/></td>
</tr>
<tr>
<td>名称</td>
<td><input disabled="disabled" type = "text" name = "mc" value = "${ts2.mc }"/></td>
</tr>
<tr>
<td>类型</td>
<td><input disabled="disabled" type = "text" name = "lx" value = "${ts2.lx }"/></td>
</tr>
<tr>
<td>价格</td>
<td><input disabled="disabled" type = "text" name = "jg"value = "${ts2.jg }"/></td>
</tr>
<tr>
<td>描述</td>
<td><input disabled="disabled" type = "text" name = "ms" value = "${ts2.ms }"/></td>
</tr>
<tr>
<td colspan = "2" align="center">
<button onclick = "fhsy()">返回首页</button>
</td>
</tr>
</table>
</center>
<script type="text/javascript">
function fhsy(){
location.href = "index.jsp";
}
</script>
</body>
</html>
增加页面