JDBC连接数据库
![又上班啦](https://i-blog.csdnimg.cn/blog_migrate/c2c1b03266d53c555c6dbdb43e4676c9.jpeg)
前言
今天第三天啦,还是有点不适应,大学生怎么会每天早上早起呢,呜呜呜呜。
准备工作
数据库
数据库使用的是免安装版的MySQL,方便简单快捷(免去了安装的痛苦,这东西实在是太难安装了)。
数据库管理工具
数据库管理工具我使用的是Navicat,建一张简单的表就行了。
所需的包
就是下图这个东西,需要下载过来并且导入进Idea。
还有需要两个jar包jstl-1.2jar和mysql-connector-java-5.1.47-bin.jar
以上就是所需要的基本配置了。
The 代码
流程详解
开发使用三层开发,有利于开发、部署、维护、扩展。
工具类DBUtil
这是用来连接数据库和关闭数据库连接的工具类。
package com.Day03.util;
import java.sql.*;
public class DBUtil {
public static Connection getConnection() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("连接数据库");
//创建连接
Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/testtt?useSSL=false","root","123456");
return connection;
}
public static void closeAll(ResultSet rs,Statement stmt,Connection conn) throws SQLException{ //ResultSet 结果集
if(rs != null){
rs.close();
stmt.close();
}
if (stmt != null){
stmt.close();
}
if(conn != null){
conn.close();
}
}
}
user类
package com.Day03.moodel;
public class User {
private int id;
private String name;
private String password;
private String age;
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 getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", password='" + password + '\'' +
", age='" + age + '\'' +
'}';
}
}
user类里我们给他写了4个属性和其get、set方法,并且重写了他们的toString。
数据访问层UserDao
package com.Day03.dao;
import com.Day03.moodel.User;
import com.Day03.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UserDao {
public int add(User user) {
Connection connection=null;
PreparedStatement pstmt=null;
int count=0;
try {
connection = DBUtil.getConnection();
//获得执行sql的Statement对象
pstmt =connection.prepareStatement("insert into user (name ,password,age) values (?,?,?)");
pstmt.setString(1,user.getName());
pstmt.setString(2,user.getPassword());
pstmt.setString(3,user.getAge());
//执行sql,获得结果
count =pstmt.executeUpdate();
System.out.println("insert操作成功"+count);
return count;
}catch (Exception e){
e.printStackTrace();
}finally {
try {
DBUtil.closeAll(null,pstmt,connection);
}catch (SQLException e){
e.printStackTrace();
}
}
return count;
}
public User selsectByName(String name){
ResultSet rs=null;
Connection connection=null;
PreparedStatement pstmt=null;
DBUtil util=new DBUtil();
User user=new User();
try {
connection=util.getConnection();
pstmt=connection.prepareStatement("select * from user where name=?");
pstmt.setString(1,name);
rs=pstmt.executeQuery();
//处理结果集
while (rs.next()){
user.setId(rs.getInt(1));
user.setName(rs.getString(2));
user.setPassword(rs.getString(3));
user.setAge(rs.getString(4));
}
}catch (Exception e){
e.printStackTrace();
}finally {
try {
util.closeAll(rs,pstmt,connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
return user;
}
public int delete(String userId){
Connection connection=null;
PreparedStatement pstmt=null;
int count=0;
try {
connection=DBUtil.getConnection();
System.out.println("连接成功");
pstmt=connection.prepareStatement("DELETE FROM `user` WHERE id=?");
pstmt.setString(1,userId);
count=pstmt.executeUpdate();
System.out.println("删除操作成功"+count);
return count;
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}finally {
try {
DBUtil.closeAll(null,pstmt,connection);
}catch (SQLException e){
e.printStackTrace();
}
}
return count;
}
}
这一层是用来访问数据库的,主要的区别就是增加和删除返回的值是我们SQL语句所影响到的数据库的行数,而查找返回的是我们所查找的数据。
业务逻辑层UserServic
package com.Day03.service;
import com.Day03.dao.UserDao;
import com.Day03.moodel.User;
import java.sql.SQLException;
public class UserService {
UserDao userDao=new UserDao();
public int add(User user) throws SQLException {
System.out.println("servic中add方法被调用");
return userDao.add(user);
}
public User selectByName(String name){
return userDao.selsectByName(name);
}
public int delete(String userId) throws SQLException {
System.out.println("servic中delete方法被调用");
return userDao.delete(userId);
}
}
这里主要的功能是调用UserDao里的方法实现逻辑性数据的生成、处理及转换。
可以像我一样在中间写入一个sout来测试是否运行到该层。
界面层AddServlet/HellowServlet/LoginServlet
LoginServlet:
package com.Day03.servlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public class LoginServlet extends HttpServlet {
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String name = req.getParameter("name");
String password = req.getParameter("password");
if (name.equals("123") && password.equals("123")) {
resp.getWriter().write("success!");
} else {
resp.getWriter().write("failed!");
}
}
}
AddServlet:
package com.Day03.servlet;
import com.Day03.moodel.User;
import com.Day03.service.UserService;
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.sql.SQLException;
public class AddServlet extends HttpServlet{
UserService userService=new UserService();
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
System.out.println("执行了doPost");
String method=req.getParameter("method");
if(method.equals("save")){
try {
insert(req,resp);
} catch (SQLException e) {
e.printStackTrace();
}
}else if (method.equals("search")){
try {
search(req,resp);
} catch (SQLException e) {
e.printStackTrace();
}
}else if(method.equals("delete")){
try {
delete(req,resp);
}catch (SQLException e){
e.printStackTrace();
}
}
}
public void insert(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException, SQLException {
User user=new User();
String name=req.getParameter("name");
String password=req.getParameter("password");
String age=req.getParameter("age");
user.setName(name);
user.setPassword(password);
user.setAge(age);
System.out.println(user);
int count=userService.add(user);
if (count>0){
resp.sendRedirect("/index.jsp");
}else {
resp.getWriter().write("<h2>failed</h2>");
}
}
public void search(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException, SQLException {
resp.setContentType("text/html;charset=GBK");
resp.setContentType("text/html");
String name=req.getParameter("name");
User user1=userService.selectByName(name);
if (user1!=null){
resp.getWriter().write("<h1>用户名:"+user1.getName()+"</h1><h1>密码:"+user1.getPassword()+"</h1><h1>年龄:"+user1.getAge()+"</h1>");
}else {
resp.getWriter().write("失败!");
}
}
public void delete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException, SQLException{
User user=new User();
String id=req.getParameter("id");
System.out.println("id是:"+id);
int count=userService.delete(id);
if(count>0){
resp.sendRedirect("/index.jsp");
}else{
resp.getWriter().write("<h2>failed</h2>");
}
}
}
HelloServlet:
package com.Day03.servlet;
import javax.servlet.*;
import java.io.IOException;
public class HelloServlet implements Servlet {
@Override
public void init(ServletConfig servletConfig) throws ServletException {
}
@Override
public ServletConfig getServletConfig() { //获得servlet配置的方法
return null;
}
@Override
public void service(ServletRequest servletRequest, ServletResponse servletResponse) throws ServletException, IOException { //实现功能
servletResponse.getWriter().write("hello,servlet");
}
@Override
public String getServletInfo() {
return null;
}
@Override
public void destroy() { //清除相关服务
}
}
web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<servlet>
<servlet-name>HelloSevlet</servlet-name> <!--与下方mapping的name保持一致-->
<servlet-class>com.Day03.servlet.HelloServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>HelloSevlet</servlet-name> <!--与上方name保持一致-->
<url-pattern>/hello</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>loginservlet</servlet-name> <!--与下方mapping的name保持一致-->
<servlet-class>com.Day03.servlet.LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>loginservlet</servlet-name> <!--与上方name保持一致-->
<url-pattern>/login</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>addServlet</servlet-name> <!--与下方mapping的name保持一致-->
<servlet-class>com.Day03.servlet.AddServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>addServlet</servlet-name> <!--与上方name保持一致-->
<url-pattern>/add</url-pattern>
</servlet-mapping>
</web-app>
前端页面add.jsp/delete.jsp/index.jsp/search.jsp
add.jsp:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>注册</title>
</head>
<body>
<form action="/add?method=save" method="post">
name:<input name="name" type="text">
password:<input name="password" type="password">
age:<input name="age" type="text">
<input type="submit" value="注册">
</form>
</body>
</html>
delete.jsp:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>删除</title>
</head>
<body>
<form action="/add?method=delete" method="post">
<input name="id" placeholder="请输入需要删除的id">
<input type="submit" value="删除">
</form>
</body>
</html>
index.jsp:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>$Title$</title>
</head>
<body>
<h1>登录页面</h1>
<form action="/login" method="post">
name:<input name="name" type="text">
password:<input name="password" type="password">
<input type="submit" value="login">
</form>
<a href="add.jsp">跳转到注册页面</a>
<a href="search.jsp">跳转到查询页面</a>
<a href="delete.jsp">跳转到删除页面</a>
</body>
</html>
search.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>search</title>
</head>
<body>
<form action="/add?method=search" method="post">
<input name="name" placeholder="请输入需要查找的名字">
<input type="submit" value="查找">
</form>
</body>
</html>
功能的实现
注册功能详解(其余功能也差不多)
运行项目后弹出默认网页index.jsp
点击下方的跳转到注册页面后网页跳转到add.jsp.(index.jsp→add.jsp)
↓
然后提交一个为save的表单(add.jsp里的method=save→AddServlet.java)
↓
从add.jsp里获得的method=save与AddServlet.java的dopost方法里的save、search、delete相比较
(equals(“save”)),所以运行接下来的insert(req,resp)
↓
跳转到AddServlet.java里的insert方法,(req.getParameter(s:“xxx”),意思是获得网页里名为xxx控件的数据)。获得数据后调用userService.add(),然后调用userDao.add()。(AddServlet.java→userService.java→userDao.java)
↓
public int add(User user) {
Connection connection=null;
PreparedStatement pstmt=null;
int count=0;
try {
connection = DBUtil.getConnection();
//获得执行sql的Statement对象
pstmt =connection.prepareStatement("insert into user (name ,password,age) values (?,?,?)");
pstmt.setString(1,user.getName());
pstmt.setString(2,user.getPassword());
pstmt.setString(3,user.getAge());
//执行sql,获得结果
count =pstmt.executeUpdate();
System.out.println("insert操作成功"+count);
return count;
}catch (Exception e){
e.printStackTrace();
}finally {
try {
DBUtil.closeAll(null,pstmt,connection);
}catch (SQLException e){
e.printStackTrace();
}
}
return count;
}
然后就是连接数据库,在数据库上进行数据修改。
其他两个功能我相信你们能触类旁通哒!
THE end
靓仔下班