jdbc:
1、加载驱动
Class.forName("com.mysql.jdbc.Driver");
2、创建连接
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/whlg","root","xyj123");
return connection;
}
3、写sql语句
//findAll()
String sql = "select * from tb_user";
//deleteById(int id)
String sql = "delete from tb_user where id = ?";
//findUserByUsername(String username)
String sql = "select * from tb_user where username=?";
4、得到statement对象
PreparedStatement ps = connection.prepareStatement(sql);
5、执行sql语句得到结果集
//findAll()
ResultSet resultSet = ps.executeQuery();
//deleteById(int id)
ps.setInt(1,id);
ps.executeUpdate();
//findUserByUsername(String username)
ps.setString(1,username);
ResultSet resultSet = ps.executeQuery();
6、处理结果集
//findAll()
List<User> userList = new ArrayList<>();
while (resultSet.next()){
User user=new User();
int id = resultSet.getInt(1);
String username = resultSet.getString(2);
String password = resultSet.getString(3);
user.setId(id);
user.setUsername(username);
user.setPassword(password);
userList.add(user);
}
//findUserByUsername(String username)
User user=null;
while (resultSet.next()){
user=new User();
int id = resultSet.getInt(1);
String username1 = resultSet.getString(2);
String password = resultSet.getString(3);
user.setId(id);
user.setUsername(username1);
user.setPassword(password);
}
7、关闭资源
public static void close(ResultSet resultSet, Statement statement,Connection connection) throws SQLException {
if(resultSet!=null){
resultSet.close();
}
if(statement!=null){
statement.close();
}
if(connection!=null){
connection.close();
}
}
实体类:
alt+insert
1.属性
2.get set
3.tostring
4.构造方法
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;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
软件:
jdk idea mysql sqlyog tomcat teamview
mysql闪退:ctrl+shift+esc打开任务管理器;启动mysql服务
util.DBUtil
public class DBUtil {
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/whlg", "root", "xyj123");
return connection;
}
public static void close(ResultSet resultSet, Statement ps, Connection connection) throws SQLException {
if(resultSet!=null){
resultSet.close();
}
if(ps!=null){
ps.close();
}
if(connection!=null){
connection.close();
}
}
}
dao.UserDao:持久层 对数据库的操作 crud
public class UserDao {
public List<User> findAll() throws ClassNotFoundException, SQLException {
Connection connection= DBUtil.getConnection();
System.out.println("获得连接成功");
String sql="select * from tb_user";
PreparedStatement ps= connection.prepareStatement(sql);
ResultSet resultSet = ps.executeQuery();
List<User> userList=new ArrayList<>();
while (resultSet.next()){
User user=new User();
int id = resultSet.getInt(1);
String username = resultSet.getString(2);
String password = resultSet.getString(3);
user.setId(id);
user.setUsername(username);
user.setPassword(password);
userList.add(user);
// System.out.println(user);
// System.out.println(id+"----"+username+"----"+password);
}
DBUtil.close(resultSet,ps,connection);
return userList;
}
public void deleteById(int id) throws SQLException, ClassNotFoundException {
Connection connection= DBUtil.getConnection();
System.out.println("获得连接成功");
String sql="delete from tb_user where id=?";
PreparedStatement ps=connection.prepareStatement(sql);
ps.setInt(1,id);
ps.executeUpdate();
DBUtil.close(null,ps,connection);
}
public User findUserByUsername(String username) throws SQLException, ClassNotFoundException {
Connection connection= DBUtil.getConnection();
System.out.println("获得连接成功");
String sql="select * from tb_user where username=?";
PreparedStatement ps= connection.prepareStatement(sql);
ps.setString(1,username);
ResultSet resultSet = ps.executeQuery();
User user=null;
while (resultSet.next()){
user=new User();
int id = resultSet.getInt(1);
String username1 = resultSet.getString(2);
String password = resultSet.getString(3);
user.setId(id);
user.setUsername(username1);
user.setPassword(password);
}
DBUtil.close(resultSet,ps,connection);
return user;
}
}
service.UserService:业务层
public class UserService {
public List<User> hello() {
UserDao userDao=new UserDao();
List<User> all=null;
try{
all=userDao.findAll();
}catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException throwables){
throwables.printStackTrace();
}
return all;
}
public boolean login(String username, String password) {
UserDao userDao=new UserDao();
User user = null;
try {
user = userDao.findUserByUsername(username);
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
if(user!=null&&user.getPassword().equals(password)){
return true;
}
return false;
}
}
servlet.UserServlet:控制层 实现页面跳转
public class UserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
UserService userService=new UserService();
List<User> all=userService.hello();
resp.getWriter().write(all.toString());
}
}
servlet.LoginServlet:控制层 实现页面跳转
public class LoginServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String username=req.getParameter("username");
String password=req.getParameter("password");
UserService userService=new UserService();
boolean flag = userService.login(username, password);
if(flag){
resp.getWriter().write("ok");
}else {
resp.getWriter().write("failure");
}
}
}
web.xml
<servlet>
<servlet-name>hello</servlet-name>
<servlet-class>com.summer.servlet.UserServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>hello</servlet-name>
<url-pattern>/hello</url-pattern>
</servlet-mapping>
<servlet>
<servlet-name>login</servlet-name>
<servlet-class>com.summer.servlet.LoginServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>login</servlet-name>
<url-pattern>/login</url-pattern>
</servlet-mapping>
index.jsp
<html>
<head>
<title>$Title$</title>
</head>
<body>
<a href="/hello">查询所有用户</a>
<form action="/login" method="post">
name:<input name="username" type="text">
password:<input name="password" type="password">
<input type="submit" value="login">
</form>>
</body>
</html>
Tomcat
点击导航栏上面的run,选择Edit configurations,点击左上角的+,在下面找到Tomcat server,添加Tomcat下的local服务器。
默认选择Server,在上方的Name中填写名字,在Application server中找到自己电脑上安装的Tomcat。
选择Deployment,点击+,选择External source,将其添加上,之后选择OK,Tomcat就添加到了我们的项目之中。
点击运行按钮,项目在浏览器中打开。
查询所有用户:
查询结果:
登录:
登录成功、失败: