JDBC介绍

JDBC工作原理

JDBC应用编程

1、准备工作

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语句PreparedStatementString sql="select* from user";PreparedStatement ps=conn.prepareStatement(sql);ResultSet rs = ps.executeQuery();//处理执行结果集ResultSetwhile(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驱动程序并建立数据库连接Connectionprivate static Connection getConnection() throws ClassNotFoundException,SQLException {//注册JDBC驱动程序Class.forName("com.mysql.cj.jdbc.Driver");String url="jdbc:mysql://localhost:3306/db";//建立数据库连接ConnectionConnection 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;}}