先在mysql新增数据库和表先,把下面的几句代码复制去到mysql运行就可以创建成功了!
创建数据库
create database jdbc01 character set utf8 collate utf8_general_ci;
创建表:
use jdbc01;
create table users(
id int primary key auto_increment,
name varchar(40),
password varchar(40),
email varchar(60),
birthday date,
sex boolean
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
运行的效果:
一、创建MVC架构的Web项目
与mysql 需要的包是
|
项目所需要的包 |
|||
|
序号 |
包名 |
描述 |
所属层次 |
|
1 |
zhu.jdbc.domain |
相当于数据库的某张表 (只包含简单的属性以及属性对应的get和set方法，不包含具体的业务处理方法)，提供给【数据访问层】、【业务处理层】、【Web层】来使用 |
domain(域模型)层 |
|
2 |
zhu.jdbc.dao |
存放访问数据库的操作接口类 |
数据访问层 |
|
3 |
zhu.jdbc.dao.imp |
存放访问数据库的操作接口的实现类 |
|
|
4 |
zhu.jdbc.service |
存放处理系统业务接口类 |
业务处理层 |
|
5 |
zhu.jdbc.service.imp |
存放处理系统业务接口的实现类 |
|
|
6 |
zhu.jdbc.command |
相当于数据库命令的 增删查改 |
相当于三层架构的(DB层) |
|
7 |
zhu.jdbc.unit |
存放系统的通用工具类，提供给【数据访问层】、【业务处理层】、【DB层】来使用 |
|
|
8 |
zhu.jdbc.servlet |
Web层(相当于界面层) |
|
创建好的项目如下图(图-1)所示:
图-1
二、代码的编写
1. zhu.jdbc.unit的包编写
在zhu.jdbc.unit包下创建一个UnitMysql的类(这个类是建立与mysql的链接)
UnitMysql代码如下：
package zhu.jdbc.unit; import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException; public class UnitMysql {
public static final String URL="jdbc:mysql://localhost:3306/jdbc01";//链接的mysql
public static final String NAME = "root";
public static final String PASSWORD = "root";
public static final String DREIVER = "com.mysql.jdbc.Driver"; static {
try {
//加载驱动器
Class.forName(DREIVER); /*//还有另一种方法,但是会加载驱动二次,通常不使用该方法
try {
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
} catch (SQLException e) {
e.printStackTrace();
}*/
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
} public static Connection getConnection() {
try {
return DriverManager.getConnection(URL, NAME, PASSWORD);//创建与数据库的链接
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//设置一个公共的关闭链接、释放资源的方法 . 因为每次只要进行了增,删,查,改 之后 都必须要关闭事件, 那么就设置一个公共的方法
//而关闭资源要从 ResultSet先关闭-->,再到 PreparedStatement-->,最后到 Connection关闭
public static void Close(ResultSet rs, PreparedStatement ps, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
} if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
} if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2. 相当于三层架构的(DB层)
在zhu.jdbc.command包下创建一个IDaoCommand的类(这个类是建立与mysql的链接)
IDaoCommand代码如下：
package zhu.jdbc.command; import java.util.List; public interface IDaoCommand<T> {
//查询所有的数据
public List<T> queryAllData();
//新增数据
public int insertData(T t);
//修改数据
public int update(T t);
//删除数据
public int delete(int id);
//查询一条数据通过ID
public T queryDataById(int id);
}
3. domain(域模型)层
在zhu.jdbc.domain 包下创建一个Tb_User 类
Tb_User 代码如下：
package zhu.jdbc.domain; import java.sql.Date;
//这里相当于数据库的某张表
public class Tb_User {
private int id;
private String name;
private String password;
private String email;
private Date birthday;
private boolean sex; public boolean isSex() {
return sex;
}
public void setSex(boolean sex) {
this.sex = 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 getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
4.开发数据访问层(dao、dao.impl)
2.1： dao 继承 command的命令
在zhu.jdbc.dao包下创建一个ITb_User接口类，对于开发接口类，我习惯以字母I作类的前缀.
ITb_User代码如下：
package zhu.jdbc.dao; import zhu.jdbc.command.IDaoCommand;
import zhu.jdbc.domain.Tb_User; /**
* 这里这个类 是为了 ,后续 添加自己需要的方法. 如:模糊查询, 分页查询....
* 这个必须要继承ICommand类,那样就可以调用增删查改的方法了
* @author Xiao_Zhu
*
*/
public interface ITb_User extends IDaoCommand<Tb_User> {
//自己需要的方法
}
2.2： dao.ImpI 实现 dao
在zhu.jdbc.dao.imp包下创建一个ITb_UserImpI类
ITb_UserImpI代码如下：
package zhu.jdbc.dao.imp; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet; import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List; import zhu.jdbc.dao.ITb_User;
import zhu.jdbc.domain.Tb_User;
import zhu.jdbc.unit.UnitMysql; /**
* 实现 ITb_User类
*
* @author Xiao_Zhu
*
*/
public class ITb_UserImpI implements ITb_User {
public Connection conn1 = null;
public ResultSet rs = null;
public PreparedStatement ps = null; // 查询所有的数据
@Override
public List<Tb_User> queryAllData() {
conn1 = UnitMysql.getConnection();// 链接数据库
List<Tb_User> list = new ArrayList<Tb_User>();
try {
String sqlSelect = "select * from users "; // 查询多条数据
ps = conn1.prepareStatement(sqlSelect);
rs = ps.executeQuery();
Tb_User user = null;
while (rs.next()) {
user = new Tb_User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setBirthday(rs.getDate("birthday"));
user.setSex(rs.getBoolean("sex"));
list.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
UnitMysql.Close(rs, ps, conn1);
}
return list;
} // 新增
@Override
public int insertData(Tb_User t) {
conn1 = UnitMysql.getConnection();
int i = 0;
try {
String sqlInsert = "insert into users(name,password,email,birthday,sex) values(?,?,?,?,?) ;";
ps = conn1.prepareStatement(sqlInsert,
PreparedStatement.RETURN_GENERATED_KEYS);
// 这里的1,2..必须要按上面的新增的顺序来定义
ps.setString(1, t.getName());
ps.setString(2, t.getPassword());
ps.setString(3, t.getEmail());
ps.setDate(4, new java.sql.Date(t.getBirthday().getTime()));
ps.setBoolean(5, t.isSex());
ps.executeUpdate();
rs = ps.getGeneratedKeys();// 得到 最新的 ID
if (rs.next()) {// 是否存在
i = rs.getInt(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
UnitMysql.Close(rs, ps, conn1);
}
return i;
} // 修改
@Override
public int update(Tb_User t) {
conn1 = UnitMysql.getConnection();
int i = 0;
try {
String sqlUpdate = "update users set name=?, password =? ,sex=? where id=?";
ps = conn1.prepareStatement(sqlUpdate);
ps.setString(1, t.getName());
ps.setString(2, t.getPassword());
ps.setBoolean(3, t.isSex());
ps.setInt(4, t.getId());
i = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
UnitMysql.Close(null, ps, conn1);
}
return i;
} // 删除
@Override
public int delete(int id) {
conn1 = UnitMysql.getConnection();
int i = 0;
try {
String sqlDelete = "delete from users where id=?";
ps = conn1.prepareStatement(sqlDelete);
ps.setInt(1, id);
i = ps.executeUpdate();
if (i == 1) {
return i;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
UnitMysql.Close(null, ps, conn1);
}
return i;
} // 查询一条数据通过ID
@Override
public Tb_User queryDataById(int id) {
conn1 = UnitMysql.getConnection();
String sql = "select * from users where id=?";
Tb_User user = null;
if (id > 0) {
try {
ps = conn1.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
user = new Tb_User();
user.setId(rs.getInt("id"));
user.setName(rs.getString("name"));
user.setPassword(rs.getString("password"));
user.setBirthday(rs.getDate("birthday"));
user.setSex(rs.getBoolean("sex")); }
} catch (SQLException e) {
e.printStackTrace();
} finally {
UnitMysql.Close(null, ps, conn1);
}
}
return user;
} }
5. 业务处理层(service,service.imp)
- .service层
在zhu.jdbc.service包下创建一个ITb_UserService 类
ITb_UserService 如下:
package zhu.jdbc.service; import zhu.jdbc.command.IServiceCommand;
import zhu.jdbc.domain.Tb_User; public interface ITb_UserService extends IServiceCommand<Tb_User> {
//这里与dao层的中的ITb_User.java是一样的意思
}
1.1 这里继承的 IServiceCommand 类是在zhu.jdbc.command下 创建一个IServiceCommand的类
代码IServiceCommand 如下:
package zhu.jdbc.command; import java.util.List; public interface IServiceCommand<T> {
//查询所有的数据
public List<T> queryAllData();
//新增数据
public boolean insertData(T t);
//修改数据
public boolean update(T t);
//删除数据
public boolean delete(int id);
//查询一条数据通过ID
public T queryDataById(int id);
}
2. service.imp层
//这里的 service层要想与 dao层(BAL层逻辑层)建立联系那么必须要 创建 dao层的对象
在zhu.jdbc.service.imp包下创建一个ITb_UserServiceImpI 类
ITb_UserServiceImpI 代码如下:
package zhu.jdbc.service.imp; import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List; import zhu.jdbc.dao.ITb_User;
import zhu.jdbc.dao.imp.ITb_UserImpI;
import zhu.jdbc.domain.Tb_User;
import zhu.jdbc.service.ITb_UserService;
/**
* 链接与dao层的链接
* @author Xiao_Zhu
*
*/
public class ITb_UserServiceImpI implements ITb_UserService{
public Connection conn1 = null;
public ResultSet rs = null;
public PreparedStatement ps = null;
boolean b=false;
//这里的 service层要想玉 dao层(BAL层逻辑层)建立联系那么必须要 创建 dao层的对象
ITb_User myiTb_User=new ITb_UserImpI();//创建了 dao层的ITb_UserImp对象 //查询所有数据
@Override
public List<Tb_User> queryAllData() { return myiTb_User.queryAllData();
} //新增
@Override
public boolean insertData(Tb_User t) {
if (t!=null) { myiTb_User.insertData(t);
b=true;
}
return b;
} //修改
@Override
public boolean update(Tb_User t) {
if (t!=null) {
myiTb_User.update(t);
b=true;
}
return b;
}
//删除
@Override
public boolean delete(int id) {
if (id!=0) {
myiTb_User.delete(id);
b=true;
}
return b;
}
//查询一条数据
@Override
public Tb_User queryDataById(int id) {
if (id!=0) {
return myiTb_User.queryDataById(id);
}
else {
return null;
}
} }
6. Web层(相当于界面层)
在zhu.jdbc.servlet包下创建一个Servlet_TbUser 类
创建的Servlet_TbUser类要在web配置
web的配置内容如下:
<!--Servlet_TbUser.java的配置-->
<servlet>
<servlet-name>Servlet_TbUser</servlet-name>
<servlet-class>zhu.jdbc.servlet.Servlet_TbUser</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Servlet_TbUser</servlet-name>
<url-pattern>/zhu/Servlet_TbUser</url-pattern>
</servlet-mapping>
Servlet_TbUser 代码如下:
package zhu.jdbc.servlet; import java.io.IOException;
import java.sql.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List; import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse; import zhu.jdbc.domain.Tb_User;
import zhu.jdbc.service.ITb_UserService;
import zhu.jdbc.service.imp.ITb_UserServiceImpI; public class Servlet_TbUser extends HttpServlet { /**
*
*/
private static final long serialVersionUID = 1L; //这里建立 与service层的 联系 创建一个service层imp的某个的对
ITb_UserService myITb_UserService=new ITb_UserServiceImpI(); @Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
doPost(req, resp);
} @Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");//解决乱码
String type=request.getParameter("who");
//新增
if("Insert".equals(type)){
Insert(request, response);
}
else if("update".equals(type)){
update(request, response);
}
else if("queryById".equals(type)){
queryById(request, response);
}
else if("delete".equals(type)){
delete(request, response);
}
else if("queryAll".equals(type)){
queryAll(request, response);
}
}
//新增
public void Insert(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//这里jsp中name专递过来的参数
String name=request.getParameter("name");
String birthday=request.getParameter("birthday");
String password=request.getParameter("password");
String email=request.getParameter("email");
String sex=request.getParameter("sex");
//把获取到的这些值放到user里
Tb_User user =new Tb_User(); try {
//下面两句是把 string 转换为 sql类型的 时间格式
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
user.setBirthday(new Date(sdf.parse(birthday).getTime()));
} catch (ParseException e1) {
e1.printStackTrace();
} user.setEmail(email);
user.setName(name) ;
user.setPassword(password);
if ("1".equals(sex)) {
user.setSex(true);
}
else if ("0".equals(sex)) {
user.setSex(false);
}
//最后调用服务来添加
String message=null;
if (myITb_UserService.insertData(user)==true) {
queryAll(request, response);
}
else {
message="新增失败!!!";
request.setAttribute("msg", message);
request.getRequestDispatcher("/index.jsp").forward(request, response);
} }
//修改
public void update(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String name=request.getParameter("name");
String birthday=request.getParameter("birthday");
String password=request.getParameter("password");
// String email=request.getParameter("email");
String sex=request.getParameter("sex");
String id=request.getParameter("id");
//把获取到的这些值放到user里
Tb_User user =new Tb_User();
try {
//下面两句是把 string 转换为 sql类型的 时间格式
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
user.setBirthday(new Date(sdf.parse(birthday).getTime()));
} catch (ParseException e1) {
e1.printStackTrace();
}
user.setId(Integer.parseInt(id));
// user.setEmail(email);
user.setName(name) ;
user.setPassword(password);
if ("1".equals(sex)) {
user.setSex(true);
}
else if ("0".equals(sex)) {
user.setSex(false);
}
boolean b= myITb_UserService.update(user);
if (b==true) {
queryAll(request, response);
}
else {
request.setAttribute("msg", "修改失败!!");
request.getRequestDispatcher("/index.jsp").forward(request, response);
} }
//查询一条数据
public void queryById(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
Tb_User user=null;
String id= request.getParameter("id");
System.out.println(id);
user= myITb_UserService.queryDataById(Integer.parseInt(id) );
request.setAttribute("user", user);
request.getRequestDispatcher("/jsp/User.jsp").forward(request, response);
}
//删除
public void delete(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String id= request.getParameter("id");
System.out.println(id);
boolean message=myITb_UserService.delete(Integer.parseInt(id));
if (message==true) {
queryAll(request, response);
}
else { request.setAttribute("msg", "删除失败!!");
request.getRequestDispatcher("/index.jsp").forward(request, response);
}
}
//查询所有的数据
public void queryAll(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException{
List<Tb_User> lis=myITb_UserService.queryAllData();
request.setAttribute("list", lis);
request.getRequestDispatcher("/jsp/User.jsp").forward(request, response);
} }
三、创建jsp
User的代码如下:
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme() + "://"
+ request.getServerName() + ":" + request.getServerPort()
+ path + "/";
%>
<!-- c标签要使用,那么就必须要有它 -->
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<c:set scope="page" var="url"
value="${pageContext.request.contextPath }"></c:set> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>" rel="external nofollow" rel="external nofollow" > <title>新增用户</title> <meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page"> </head> <body>
<div align="center"
style="width: 400px; position: relative;left:450px">
<form action="${url}/zhu/Servlet_TbUser?who=Insert" method="post">
<h4>新增用户</h4>
姓名: <input type="text" name="name"><br />
密码: <input type="text" name="password"><br />
出生日期 : <input type="text" name="birthday"><br />
性别: <select name="sex">
<option value="0">男</option>
<option value="1">女</option>
</select><br />
<input type="submit" value="新增"/>
<hr />
</form>
</div>
<div align="center"style="width: 400px; position: relative;left:450px;">
<form action="${url}/zhu/Servlet_TbUser?who=queryAll" method="post">
<input type="submit" value="查询所有的数据"/> <br/>
<table border="1" cellspacing="0">
<thead>
<tr><td>ID</td><td>姓名</td><td>密码</td><td>日期</td><td>性别</td><td>操作</td></tr>
</thead>
<tbody>
<c:forEach items="${list}" var="list">
<tr>
<td>${list.id }</td>
<td>${list.name }</td>
<td>${list.password }</td>
<td>${list.birthday }</td>
<td><c:if test="${list.sex==false }">男</c:if>
<c:if test="${list.sex==true }">女</c:if></td>
<td><a href= "${url}/zhu/Servlet_TbUser?who=queryById&id=${list.id}" style='text-decoration:none' onclick='update(this)' >修改 </a>
<a href= "${url}/zhu/Servlet_TbUser?who=delete&id=${list.id}" style='text-decoration:none' >删除</a> </td>
</tr>
</c:forEach>
</tbody>
</table>
<hr />
</form>
</div>
<div align="center"
style="width: 400px; position: relative;left:450px">
<form action="${url}/zhu/Servlet_TbUser?who=update" method="post">
<h4>修改用户</h4>
<input type="hidden"name="id" value="${user.id }"/>
姓名: <input type="text" name="name" value="${user.name }"><br />
密码: <input type="text" name="password" value="${user.password }"><br />
出生日期 : <input type="text" name="birthday" value="${user.birthday }"><br />
性别:<c:if test="${user.sex==false }">
<select name="sex" >
<option value="0">男</option>
<option value="1">女</option>
</select>
</c:if>
<c:if test="${user.sex==true }">
<select name="sex" >
<option value="1">女</option>
<option value="0">男</option>
</select>
</c:if><br />
<input type="submit" value="保存修改"/>
<hr />
</form>
</div>
</body>
</html>
index.jsp代码如下:
<%@ page language="java" import="java.util.*" 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">
<html>
<head>
<base href="<%=basePath%>" rel="external nofollow" rel="external nofollow" > <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">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css" rel="external nofollow" >
-->
</head> <body>
<h1>${msg }</h1> <br>
</body>
</html>
四、运行效果:
源码下载地址: https://pan.baidu.com/s/1bQKEAi
