一 数据库的连接
数据库的连接,在上一篇文章已经提过了,可以直接复制
可以去javaweb的专栏进行查看
二 简单的项目
使用一个简单的项目来简述一下,Javaweb对数据库的操作
1.目录结构
2.工具类的编写
-
DB_Query
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
public class DB_Query {
public static List Query(Object ...args) {
Connection conn = null;
List<String> list = new ArrayList<>();
PreparedStatement pr = null;
ResultSet res = null;
try {
//1 获取数据库连接
conn = (Connection) JDBCUtils.getConnection();
//2 预编译sql语句
//String sql = "select * from T_user where id = ?";
String sql = "select * from T_user";
pr = (PreparedStatement) conn.prepareStatement(sql);
//pr = (PreparedStatement) conn.createStatement();
//填充占位符
//pr.setObject(1,1);
//3 执行并返回结果集
res = (ResultSet) pr.executeQuery();
//4 处理结果集
//next作用:判断结果集下一条是否有数据,如果有数据返回true,并且指针下移,如果返回false,反之
while (res.next()) {
//获取当前这条数据的各个字段
int id = res.getInt(1);
String name = res.getString(2);
String password = res.getString(3);
String result = "id ="+id+",name ="+name+", password"+password;
list.add(result);
//System.out.println("id ="+id+",name ="+name+", password"+password);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeReqsource(conn, pr, res);
}
return list;
}
}
- 对数据库的增删改
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
public class DB_Update {
/**
* 通过接收到用户名和密码,插入数据到数据库
* @param username
* @param password
* @return
*/
public static Boolean insert(String username,String password) {
Connection conn = null;
PreparedStatement pr = null;
ResultSet res = null;
try {
//1 获取数据库连接
conn = (Connection) JDBCUtils.getConnection();
//2 预编译sql语句
//String sql = "select * from T_user where id = ?";
String sql = "insert into T_User(username,password) values(?,?)";
pr = (PreparedStatement) conn.prepareStatement(sql);
//pr = (PreparedStatement) conn.createStatement();
pr.setObject(1,username);
pr.setObject(2,password);
//填充占位符
//pr.setObject(1,1);
//3 执行并返回结果集
pr.execute();
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeReqsource(conn, pr, res);
}
return false;
}
/**
* 通过接收到用户名,删除指定的用户
* @param username
* @return
*/
public static Boolean delete(String username) {
Connection conn = null;
PreparedStatement pr = null;
ResultSet res = null;
try {
//1 获取数据库连接
conn = (Connection) JDBCUtils.getConnection();
//2 预编译sql语句
//String sql = "select * from T_user where id = ?";
String sql = "delete from T_User where username = ?";
pr = (PreparedStatement) conn.prepareStatement(sql);
//pr = (PreparedStatement) conn.createStatement();
pr.setObject(1,username);
//填充占位符
//pr.setObject(1,1);
//3 执行并返回结果集
pr.execute();
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeReqsource(conn, pr, res);
}
return false;
}
/**
* 通过从前端接收到用户名,可以执行修改操作
* @param username 根据用户名
* @param password 修改密码
* @return
*/
public static Boolean update(String username,String password) {
Connection conn = null;
PreparedStatement pr = null;
ResultSet res = null;
try {
//1 获取数据库连接
conn = (Connection) JDBCUtils.getConnection();
//2 预编译sql语句
//String sql = "select * from T_user where id = ?";
String sql = "update T_user set password= ? where username=?";
pr = (PreparedStatement) conn.prepareStatement(sql);
//pr = (PreparedStatement) conn.createStatement();
pr.setObject(1,password);
pr.setObject(2,username);
//填充占位符
//pr.setObject(1,1);
//3 执行并返回结果集
pr.execute();
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeReqsource(conn, pr, res);
}
return false;
}
//对以上三个方法的测试
public static void main(String[] args) {
// DB_Update.delete("admin");
// DB_Update.insert("admin","123123");
Boolean admin = DB_Update.update("admin", "11111");
System.out.println(admin);
}
}
- 数据库的连接这里不做过多的叙述了,可以看上一篇文章
3. 创建实体类,与数据库的表进行对应
public class User {
private int id;
private String username;
private String password;
public User() {
}
public User(int id, String username, String password) {
this.id = id;
this.username = username;
this.password = password;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
4. 编写一个User的DAO,用于调用工具类
public class UserDao {
/**
* 查询出所有的用户,以列表的形式返回
* @return
*/
public List Query(){
List query = DB_Query.Query();
return query;
}
/**
* 插入的方法
* @param username 在Service层获取到的用户名
* @param password 在Service层获取到的密码
* @return
*/
public Boolean insert(String username, String password) {
//插入返回的是成功或者失败
Boolean isInsert = DB_Update.insert(username, password);
return isInsert;
}
/**
* 删除指定用户名的数据
* @param username
* @return
*/
public Boolean delete(String username){
Boolean delete = DB_Update.delete(username);
return delete;
}
/**
* 对输入的数据进行更新,修改密码
* @param username
* @param password
* @return
*/
public Boolean update(String username,String password){
Boolean update = DB_Update.update(username,password);
return update;
}
}
三 编写四个Servlet分别对应增删改查
- 删除
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.io.PrintWriter;
public class DeleteServlet extends HttpServlet {
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
UserDao userDao = new UserDao();
String username = req.getParameter("username");
String password = req.getParameter("password");
Boolean insert = userDao.delete(username);
PrintWriter writer = resp.getWriter();
if (insert){
System.out.println("删除成功");
writer.write("删除成功");
}else{
System.out.println("删除失败");
writer.write("删除失败");
}
}
}
- 修改
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.io.PrintWriter;
public class UpdateServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String username = req.getParameter("username");
String password = req.getParameter("password");
UserDao userDao = new UserDao();
PrintWriter writer = resp.getWriter();
Boolean update = userDao.update(username, password);
if (update){
System.out.println("修改成功");
writer.write("修改成功");
}else{
System.out.println("修改失败");
writer.write("修改失败");
}
}
}
- 插入
public class InsertServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
UserDao userDao = new UserDao();
String username = req.getParameter("username");
String password = req.getParameter("password");
Boolean insert = userDao.insert(username, password);
PrintWriter writer = resp.getWriter();
if (insert){
System.out.println("添加成功");
writer.write("添加成功");// 用write()写出一个html
}else{
System.out.println("添加失败");
writer.write("添加失败");// 用write()写出一个html
}
}
}
- 查询
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.io.PrintWriter;
import java.util.List;
public class QueryServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//super.doGet(req, resp);
String username = req.getParameter("username");
String password = req.getParameter("password");
UserDao userDao = new UserDao();
List query = userDao.Query();
PrintWriter writer = resp.getWriter();
for (Object str : query) {
System.out.println(str);
String html = (String) str;
writer.write(html);
}
}
}
四 配置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>query</servlet-name>
<servlet-class>cn.atzxc.servlet.QueryServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>query</servlet-name>
<url-pattern>/query</url-pattern>
</servlet-mapping>
<!--修改用户名和密码-->
<servlet>
<servlet-name>update</servlet-name>
<servlet-class>cn.atzxc.servlet.UpdateServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>update</servlet-name>
<url-pattern>/update</url-pattern>
</servlet-mapping>
<!--添加-->
<servlet>
<servlet-name>insert</servlet-name>
<servlet-class>cn.atzxc.servlet.InsertServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>insert</servlet-name>
<url-pattern>/insert</url-pattern>
</servlet-mapping>
<!--删除-->
<servlet>
<servlet-name>delete</servlet-name>
<servlet-class>cn.atzxc.servlet.DeleteServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>delete</servlet-name>
<url-pattern>/delete</url-pattern>
</servlet-mapping>
</web-app>
五 编写一个简单的前端进行验证
<h1>查询所有的值</h1>
<form method="get" action="${pageContext.request.contextPath}/query">
<input type="submit" value="查询出所有值">
</form>
<h1>根据用户名修改密码</h1>
<form method="get" action="${pageContext.request.contextPath}/update">
<input type="text" name="username">用户名
<input type="password" name="password" >密码
<input type="submit" value="修改">
</form>
<h1>添加一个用户</h1>
<form method="get" action="${pageContext.request.contextPath}/insert">
<input type="text" name="username" >用户名
<input type="password" name="password">密码
<input type="submit" value="添加">
</form>
<h1>根据用户名删除一个数据</h1>
<form method="get" action="${pageContext.request.contextPath}/delete">
<input type="text" name="username" >用户名
<input type="password" name="password">密码
<input type="submit" value="删除">
</form>
最终当我们点击按钮后,会将我们在Servlet中的输出流返回给浏览器页面中。本项目是一个简单的项目。如果想要单纯的获取数据库的增删改查,可以直接在 二 2 中获取。