1 . jdbc和实体类复习
- jdbc:Java数据库连接
- jdbc连接数据库步骤:
1.加载驱动
2.创建连接
3.写sql
4.statement对象
5.执行sql得到结果集
6.处理结果集
7.关闭资源
- 实体类包括:属性 get set tostring方法 构造方法
2 . 实现一个简单的查询所有用户
- 建立工具类util用于访问数据库
public class DBUtil {
public static Connection getConnect() throws ClassNotFoundException, SQLException {
// 1 声明mysql驱动
String driver = "com.mysql.jdbc.Driver";
// 2 声明与mysql的连接对象
Connection conn = null;
//3 声明执行sql语句
Statement stmt;
// 4 URL指向要访问的数据库名test
String url = "jdbc:mysql://localhost:3306/whlg";
// jdbc:mysql jdbc是连接协议,mysql是子协议 //localhost: 本机//3306 数据库端口号
String user = "root"; // 5 用户名
String passwords = "123456"; //6 密码
Class.forName(driver);
conn= DriverManager.getConnection(url,user,passwords);
return conn;
}
public static void close(ResultSet resultset,Statement statement,Connection conn) throws SQLException {
if(resultset!=null){
resultset.close();
}
if(statement!=null) {
statement.close();
}
if(conn!=null) {
conn.close();
}
}
}
- 编写实现类User
public class User {
private int id;
private String username;
private String password;
public void setId(int id) {
this.id = id;
}
public void setUsername(String username) {
this.username = username;
}
public void setPassword(String password) {
this.password = password;
}
public int getId() {
return id;
}
public String getUsername() {
return username;
}
public String getPassword() {
return password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
- 在Dao层对数据库进行操作,这里写查询所有语句以及删除语句
public class UserDao {
/* public static void main(String[] args) throws ClassNotFoundException, SQLException {
}*/
public List<User> findAll() throws ClassNotFoundException, SQLException {
List<User> userlist=new ArrayList<>();
Connection conn= DBUtil.getConnect();
String sql="select * from tb_user";
PreparedStatement statement=conn.prepareStatement(sql);
ResultSet resultset=statement.executeQuery();
while(resultset.next()){
User users=new User();
int id=resultset.getInt(1);
String username=resultset.getString(2);
String password=resultset.getString(3);
users.setId(id);
users.setUsername(username);
users.setPassword(password);
userlist.add(users);
}
DBUtil.close(resultset,statement,conn);
return userlist;
}
public void deleteById(int id) throws SQLException, ClassNotFoundException {
// List<User> userlist=new ArrayList<>();
Connection conn= DBUtil.getConnect();
String sql="delete from tb_user where id=?";
PreparedStatement statement=conn.prepareStatement(sql);
// statement.execute();
statement.setInt(1,id);
statement.executeUpdate();
DBUtil.close(null,statement,conn);
}
}
- 为了将其界面显示,编写index.jsp,这里请求为 /hello
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>$Title$</title>
</head>
<body>
<a href="/hello">查询所有用户</a>
</body>
</html>
- 同时写与其对应的servlet,接收请求进行处理,在数据库中查询并且返回
public class UserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
// super.doGet(req, resp);
UserDao userDao=new UserDao();
List<User> all=null;
try {
all=userDao.findAll();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
resp.getWriter().write(all.toString());
}
}
- 在web.xml中编写相应的映射,使得到正确的servlet进行处理,并跳转成功
<servlet>
<servlet-name>hello</servlet-name>
<servlet-class>com.zr.servlet.UserServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>hello</servlet-name>
<url-pattern>/hello</url-pattern>
</servlet-mapping>
实现效果如下: