JDBC介绍
![](https://i-blog.csdnimg.cn/blog_migrate/0221d5753b8b59f8b7d73615c20ecf5e.png)
JDBC工作原理
![](https://i-blog.csdnimg.cn/blog_migrate/279628fc7054ce812f8aaeb1952deda5.png)
JDBC应用编程
1、准备工作
![](https://i-blog.csdnimg.cn/blog_migrate/0e24cf60abed4d55820a48092b186fa6.png)
![](https://i-blog.csdnimg.cn/blog_migrate/6a3d9ef7d5d4363e07a3b40d6a0fa985.png)
2、JDBC编程步骤
(1)注册JDBC驱动程序
Class.forName("com.mysql.jdbc.Driver");
或者DriverManager.registerDriver(new com.mysql.jdbc.Driver());
(2)建立数据库连接Connection
String url="jdbc:mysql://localhost:3306/db";
String user="root";
String password="mysqladmin";
Connection conn=DriverManager.getConnection(url, user, password);
(3)创建并执行SQL语句Statement实际应用我们用的是PreparedStatement
Statement stmt=conn.createStatement(); //conn为上一步创建的连接
String sql="select * from user";
ResultSet rs=stmt.executeQuery(sql);
(4)处理执行结果集ResultSet
while(rs.next()){ //rs为上一步返回的结果集
String name=rs.getString("username");
String pass=rs.getString("password");
System.out.printf("用户名:%s,口令:%s\n",name,pass);
}
结果集的getString(String columnLabel) ,该方法根据表中的列名获取当前记录指定列的值
(5)释放资源
rs.close(); //关闭结果集
stmt.close(); //关闭语句
conn.close(); //关闭连接
JDBC实现增加、删除和修改操作
开发工具:myeclipse2014、MySQL8.0【MySQL】MySQL8.0安装教程: https://blog.csdn.net/Trista_1999/article/details/132678207
文件结构:
User类:
package javabean;
public class User {
private String username;
private String password;
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;
}
}
UserDAO类:实现数据库连接和增删改查
package javabean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class UserDAO {
//查询
public static List<User> searchAll() throws ClassNotFoundException, SQLException{
List<User>users=new ArrayList<>();
//注册JDBC驱动程序
Connection conn = getConnection();
//创建并执行SQL语句PreparedStatement
String sql="select* from user";
PreparedStatement ps=conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
//处理执行结果集ResultSet
while(rs.next()){
String username = rs.getString("username");
String password = rs.getString("password");
User user=new User();
user.setUsername(username);
user.setPassword(password);
users.add(user);
}
//释放资源
rs.close();
ps.close();
conn.close();
return users;
}
//getConnection函数,注册JDBC驱动程序并建立数据库连接Connection
private static Connection getConnection() throws ClassNotFoundException,
SQLException {
//注册JDBC驱动程序
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/db";
//建立数据库连接Connection
Connection conn=DriverManager.getConnection(url, "root", "root");
return conn;
}
//添加
public boolean add(User user) throws SQLException, ClassNotFoundException{
Connection conn = getConnection();
String sql="insert into user(username,password) values (?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
System.out.print(sql);
int res=ps.executeUpdate();
ps.close();
conn.close();
return res==1;
}
//更新
public boolean update(User user) throws SQLException, ClassNotFoundException{
Connection conn = getConnection();
String sql="update user password=? where username=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, user.getPassword());
ps.setString(2, user.getUsername());
System.out.print(sql);
int res=ps.executeUpdate();
ps.close();
conn.close();
return res==1;
}
//删除
public boolean delete(String username) throws SQLException, ClassNotFoundException{
Connection conn = getConnection();
String sql="delete from user where username=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, username);
System.out.print(sql);
int res=ps.executeUpdate();
ps.close();
conn.close();
return res==1;
}
}