文章目录
前言：
本工程用的jar包:
工程类图：
环境：tomcat 8.0 ，idea，mysql数据库，上面jar包建议兄弟们用maven导入，更加方便
1.建立数据库表：
create table student1
(
id int auto_increment
primary key,
name varchar(20) not null,
sex varchar(20) not null,
age int not null,
dept varchar(100) not null
)
charset = utf8;
2.编写JavaBean
package com.dl.pojo;
/**
* @ClassName Student
* @Description TODO
* @Author 86188
* @DAte 2021/11/29
**/
public class Student {
private Integer id;
private String name;
private String sex;
private Integer age;
private String dept;
public Student ( Integer id, String name, String sex, Integer age, String dept ) {
this.id = id;
this.name = name;
this.sex = sex;
this.age = age;
this.dept = dept;
}
@Override
public String toString () {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", age=" + age +
", dept='" + dept + '\'' +
'}';
}
public Integer getId () {
return id;
}
public void setId ( Integer id ) {
this.id = id;
}
public String getName () {
return name;
}
public void setName ( String name ) {
this.name = name;
}
public String getSex () {
return sex;
}
public void setSex ( String sex ) {
this.sex = sex;
}
public Integer getAge () {
return age;
}
public void setAge ( Integer age ) {
this.age = age;
}
public String getDept () {
return dept;
}
public void setDept ( String dept ) {
this.dept = dept;
}
public Student () {
}
}
3.JDBC连接数据库：
3.1 JDBCUtils类编写：
package com.dl.Util;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.alibaba.druid.util.JdbcUtils;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
/**
* @ClassName JDBCUtil
* @Description TODO
* @Author 86188
**/
public class JDBCUtil {
private static DruidDataSource dataSource;
private static ThreadLocal<Connection> coons=new ThreadLocal<Connection>();
static{
try {
Properties properties=new Properties();
//读取配置文件属性
InputStream inputStream= JdbcUtils.class.getClassLoader ().getResourceAsStream ("Druid.properties");
//从流中加载数据
properties.load(inputStream);
//创建了数据库连接池
dataSource= (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
/** @author dl
* @Description 获取连接
* @Param []
* @return java.sql.Connection
**/
public static Connection getConnection(){
Connection connection=coons.get();
if(connection==null){
try {
connection=dataSource.getConnection();//从数据库连接池获取连接
coons.set(connection);//保存到ThreadLocal对象中，让后面的Jdbc操作使用
connection.setAutoCommit(true); //设置为手动管理事务
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return connection;
}
public static void close(Connection coon){
try {
if(coon!=null)
coon.close ();
} catch (SQLException throwables) {
throwables.printStackTrace ( );
}
}
}
3.2 Druid.properties 数据库配置文件：
username=数据库名字
password=数据库密码
url=jdbc:mysql://localhost:3306/mydatabase1?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
driverClass=com.mysql.jdbc.Driver
4. dao层代码编写：
4.1 StudentDao接口
import com.dl.pojo.Student;
import java.util.List;
import java.util.Map;
public interface StudentDao {
public void add( Student student );
public void delete(int id);
public void update(Student student);
public Student queryById(int id);
public List<Map<String, Object>> queryStudents ();
}
4.2 StudentDaoImpl 实现类：
package com.dl.dao.impl;
import com.dl.Util.JDBCUtil;
import com.dl.dao.StudentDao;
import com.dl.pojo.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* @ClassName StudentDaoImpl
* @Description TODO
* @Author 86188
* @DAte 2021/11/30
**/
public class StudentDaoImpl implements StudentDao {
QueryRunner runner = new QueryRunner ( );
Connection connection =JDBCUtil.getConnection ();
@Override
public void add ( Student student ) {
String sql ="insert into student1(name,sex,age,dept) values (?,?,?,?);";
try {
connection.setAutoCommit (false);
runner.update (connection,sql,student.getName (),student.getSex (),student.getAge (),student.getDept ());
connection.commit ();
} catch (Exception throwables) {
throwables.printStackTrace ();
}finally {
JDBCUtil.close (connection);
}
}
@Override
public void delete ( int id ) {
String sql = "delete from student1 where id =?";
try {
connection.setAutoCommit (false);
runner.update (connection,sql,id);
connection.commit ();
} catch (SQLException throwables) {
throwables.printStackTrace ();
} finally {
JDBCUtil.close (connection);
}
}
@Override
public void update ( Student student ) {
String sql ="update student1 set name = ?,sex = ?,age=?, dept=? where id=?";
try {
connection.setAutoCommit (false);
runner.update (connection,sql,student.getName (),student.getSex (),student.getAge (),student.getDept (),student.getId ());
connection.commit ();
} catch (SQLException throwables) {
throwables.printStackTrace ();
} finally {
JDBCUtil.close (connection);
}
}
@Override
public Student queryById ( int id ) {
String sql ="select * from student1 where id =?";
try {
MapHandler mapHandler = new MapHandler ();
Map<String, Object> query = runner.query (connection, sql, mapHandler, id);
// System.out.println (query);
Integer id1 = (Integer)query.get ("id");
String name = (String)query.get ("name");
String sex =(String) query.get ("sex");
Integer age = (Integer)query.get ("age");
String dept = (String)query.get ("dept");
Student student = new Student (id1,name,sex,age,dept);
System.out.println (student);
return student;
} catch (SQLException throwables) {
throwables.printStackTrace ();
} finally {
JDBCUtil.close (connection);
}
return null;
}
@Override
public List<Map<String, Object>> queryStudents () {
String sql ="select * from student1 ;";
try {
MapListHandler map = new MapListHandler ();
List<Map<String, Object>> mapList = runner.query (connection,sql,map);
mapList.forEach (System.out::println);
return mapList;
} catch (SQLException throwables) {
throwables.printStackTrace ();
} finally {
JDBCUtil.close (connection);
}
return null;
}
}
5.service层代码编写：
5.1 StudentService 接口：
package com.dl.service;
import com.dl.pojo.Student;
import java.util.List;
import java.util.Map;
/**
* @ClassName StudentService
* @Description TODO
* @Author 86188
* @DAte 2021/11/30
**/
public interface StudentService {
public void add ( Student student );
public void delete(int id);
public void update(Student student);
public Student queryById(int id);
public List<Map<String, Object>> queryStudents ();
}
5.2 Student ServiceImpl 实现类：
package com.dl.service.Impl;
import com.dl.dao.StudentDao;
import com.dl.dao.impl.StudentDaoImpl;
import com.dl.pojo.Student;
import com.dl.service.StudentService;
import java.util.List;
import java.util.Map;
/**
* @ClassName StudentServiceImpl
* @Description TODO
* @Author 86188
* @DAte 2021/11/30
**/
public class StudentServiceImpl implements StudentService {
private StudentDao studentDao = new StudentDaoImpl();
@Override
public void add ( Student student ) {
studentDao.add (student);
}
@Override
public void delete ( int id ) {
studentDao.delete (id);
}
@Override
public void update ( Student student ) {
studentDao.update (student);
}
@Override
public Student queryById ( int id ) {
return studentDao.queryById (id);
}
@Override
public List<Map<String, Object>> queryStudents () {
return studentDao.queryStudents ();
}
}
6.servlet层编写：
6.1 StudentServlet代码：
package com.dl.servlet;
import com.dl.pojo.Student;
import com.dl.service.Impl.StudentServiceImpl;
import com.dl.service.StudentService;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
import java.util.Map;
/**
* @ClassName StudentServlet
* @Description TODO
* @Author 86188
* @DAte 2021/11/30
**/
public class StudentServlet extends HttpServlet {
private StudentService service = new StudentServiceImpl ();
protected void add ( HttpServletRequest req, HttpServletResponse resp ) throws ServletException, IOException {
System.out.println ("0.........");
String name = req.getParameter ("name");
String sex = req.getParameter ("sex");
int age = Integer.parseInt (req.getParameter ("age"));
String dept = req.getParameter ("dept");
Student student = new Student (null,name,sex,age,dept);
service.add (student);
// req.getRequestDispatcher ("/index.jsp").forward (req,resp);
resp.getWriter ().write (student.toString ());
// req.getRequestDispatcher ("/page/add.jsp").forward (req,resp);
// resp.sendRedirect ("/index.jsp");
// resp.getWriter ().write (String.valueOf (student));
}
protected void delete ( HttpServletRequest req, HttpServletResponse resp ) throws ServletException, IOException {
int id = Integer.parseInt (req.getParameter ("id"));
service.delete (id);
resp.getWriter ().write ("success");
System.out.println ("delete");
}
protected void update ( HttpServletRequest req, HttpServletResponse resp ) throws ServletException, IOException {
int id = Integer.parseInt (req.getParameter ("id"));
String name = req.getParameter ("name");
String sex = req.getParameter ("sex");
int age = Integer.parseInt (req.getParameter ("age"));
String dept = req.getParameter ("dept");
Student student = new Student (id, name, sex, age, dept);
service.update (student);
resp.getWriter ().write (student.toString ());
System.out.println ("update");
}
protected void queryOne ( HttpServletRequest req, HttpServletResponse resp ) throws ServletException, IOException {
int id = Integer.parseInt (req.getParameter ("id"));
Student student = service.queryById (id);
resp.getWriter ().write (student.toString ());
System.out.println ("queryone");
}
protected void queryAll ( HttpServletRequest req, HttpServletResponse resp ) throws ServletException, IOException {
List<Map<String, Object>> maps = service.queryStudents ();
maps.forEach (System.out::println);
resp.getWriter ().write (maps.toString ());
System.out.println ("queryAll");
}
@Override
protected void doPost ( HttpServletRequest req, HttpServletResponse resp ) throws ServletException, IOException {
req.setCharacterEncoding ("utf-8"); //防止传过来数据乱码
resp.setCharacterEncoding ("utf-8");
String action = req.getParameter ("action");
System.out.println (action);
if("add".equals (action)){
add (req,resp);
return;
}
if("delete".equals (action)){
delete (req,resp);
return;
}
if("update".equals (action)){
update (req,resp);
return;
}
if("selectOne".equals (action)){
queryOne (req,resp);
return;
}
if("selectAll".equals (action)){
queryAll (req,resp);
return;
}
}
@Override
protected void doGet ( HttpServletRequest req, HttpServletResponse resp ) throws ServletException, IOException {
this.doPost (req,resp);
}
}
7.web.xml 配置：
7.1 servlet 的配置
<servlet>
<servlet-name>StudentServlet</servlet-name>
<servlet-class>com.dl.servlet.StudentServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>StudentServlet</servlet-name>
<url-pattern>/stu</url-pattern>
</servlet-mapping>
8.前端页面：
没有做完美的页面，只是简单的表单提交页面，足以说明问题！
8.1 index.jsp
<%--
Created by IntelliJ IDEA.
User: 86188
Date: 2021/11/30
Time: 19:46
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>$Title$</title>
</head>
<body>
<h3><a href="http://localhost:8080/StudentWeb/page/add.jsp" > 去添加学生</a></h3>
<h3> <a href="http://localhost:8080/StudentWeb/page/update.jsp">去修该学生信息</a></h3>
<h3> <a href="http://localhost:8080/StudentWeb/page/delete.jsp">删除学生</a></h3>
<h3> <a href="/StudentWeb/page/select.jsp">查询学生信息</a></h3>
</body>
</html>
8.2 add.jsp
<%--
Created by IntelliJ IDEA.
User: 86188
Date: 2021/11/30
Time: 20:04
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<form action="/StudentWeb/stu?action=add" method="post">
用户名：<input name="name" type="text" /> <br/>
性别：<input name="sex" type="text" /> <br/>
年龄：<input name="age" type="text" /><br/>
系院：<input type="text" name="dept" /><br/>
<input value="提交" type="submit"/>
</form>
</body>
</html>
8.3 delete.jsp
<%--
Created by IntelliJ IDEA.
User: 86188
Date: 2021/12/1
Time: 10:21
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<form method="post" action="/StudentWeb/stu?action=delete">
输入要删除学生的id：<br/>
<input type="text" name="id" /><br/>
<input value="提交" type="submit">
</form>
</body>
</html>
8.3 select.jsp
<%--
Created by IntelliJ IDEA.
User: 86188
Date: 2021/12/1
Time: 10:24
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<form action="/StudentWeb/stu?action=selectOne" method="post">
输入查询学生的id：<br/>
<input type="text" name="id" />
<input type="submit" value="查询">
</form>
<hr/>
</body>
</html>
8.4 update.jsp
<%--
Created by IntelliJ IDEA.
User: 86188
Date: 2021/12/1
Time: 10:20
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<form action="/StudentWeb/stu?action=update" method="post">
id:<input type="text" name="id"/><br/>
用户名：<input name="name" type="text"/> <br/>
性别：<input name="sex" type="text"/> <br/>
年龄：<input name="age" type="text"/><br/>
系院：<input type="text" name="dept"/><br/>
<input value="提交" type="submit"/>
</form>
</body>
</html>
9 .测试类：
9.1 jdbc连接数据库测试：
public class JDBCTest {
public static void main ( String[] args ) throws Exception {
Connection connection = JDBCUtil.getConnection ();
System.out.println (connection);
JDBCUtil.close (connection);
}
}
连接成功：
9.2 dao层的测试：
9.2.1 add方法测试：
private StudentDao studentDao = new StudentDaoImpl ();
@Test
public void add(){
studentDao.add (new Student (null,"zx","女",18,"计算机"));
}
执行成功：
数据库中新增数据：
9.2.2 update 方法测试：
@Test
public void update(){
studentDao.update (new Student (1,"zc","男",20,"软工"));
}
执行成功：
看看数据库数据：
其他方法就不测了，有兴趣自己测测！
1988
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
