JDBC

一、JDBC的工作原理

（1.）java连接数据库的步骤

步骤1：Java应用程序编写代码

步骤2：调用JDBC API接口或是类

步骤3：加载JDBC的驱动（jar包）

步骤4：调用数据库并测试

（2.）java连接数据库的步骤

步骤1：Java应用程序编写代码

步骤2：调用JDBC API接口或是类

步骤3：加载JDBC的驱动（jar包）

步骤4：调用数据库并测试

二、JDBC API

（1.）JDBC API的核心作用：1.连接数据库2.执行sql语句3.保存结果

（2.）JDBC API执行顺序：

1. DriverManage管理驱动
2. 创建连接对象Connection对象
3. 调用Statement对象执行sql语句
4. 调用ResultSet对象保存查询结果【查询才会使用得到ResultSet】

三、JDBC编程步骤

1.注册JDBC驱动

2.管理连接

3.创建PreparedStatement对象-预编译对象

【为什么不使用Statement对象：因为Statement对象有sql注入的问题，sql注入是sql中的安全性问题】

4.发送SQL语句，并得到返回结果

5.处理返回结果

6.释放资源

四、JDBC实战：

步骤1：创建数据库

步骤2：创建工程

步骤3：创建实体类

package cn.kgc.entity;

public class User {
    private  Integer id;
    private  String  name;
    private  String pwd;
    private  double height;

    public User() {
    }

    public User(Integer id, String name, String pwd, double height) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
        this.height = height;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    public double getHeight() {
        return height;
    }

    public void setHeight(double height) {
        this.height = height;
    }
}

功能1：添加功能：

   public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtext","root","123456");
          String sql="insert into t_user(name,pwd,height)values(?,?,?)";
          PreparedStatement ps=connection.prepareStatement(sql);
          ps.setObject(1,"12");
          ps.setObject(2,"145");
          ps.setObject(3,12);
          ps.executeUpdate();
          ps.close();
          connection.close();
    }

功能2：删除功能：

public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtext","root","123456");
        String sql="delete from t_user where id=?";
        PreparedStatement ps=conn.prepareStatement(sql);
        ps.setObject(1,1);
        ps.executeUpdate();
        ps.close();
        conn.close();
    }

功能3：修改功能:

  public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtext","root","123456");
        String sql="update t_user set name=?,pwd=?,height=?,where id=?";
        PreparedStatement ps=connection.prepareStatement(sql);
        ps.setObject(1,"阿");
        ps.setObject(2,"22222");
        ps.setObject(3,66.2);
        ps.setObject(4,3);
        ps.executeUpdate();


        ps.close();
        connection.close();
    }

功能4：查询集合

  public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        ArrayList<User> list=new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtext","root","123456");
            ps=conn.prepareStatement("select  * from t_user");
            rs=ps.executeQuery();
            User user=null;
            while(rs.next()){
                user=new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPwd(rs.getString("pwd"));
                user.setHeight(rs.getDouble("height"));
                list.add(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                rs.close();
                ps.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
for(User u:list){
    System.out.println(u.getId());
    System.out.println(u.getName());
    System.out.println(u.getPwd());
    System.out.println(u.getHeight());
}

    }

功能5：查询对象：

 public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtext","root","123456");
        PreparedStatement ps=connection.prepareStatement(" select * from t_user where id= ? ");
        //占位符赋值
        ps.setObject(1,2);
        //查询语句
         ResultSet rs=ps.executeQuery();

        User user = new User();
        while(rs.next()){
            user.setId(rs.getInt("id"));
            user.setName(rs.getString("name"));
            user.setPwd(rs.getString("pwd"));
            user.setHeight(rs.getDouble("height"));
        }
        rs.close();
        ps.close();
        connection.close();

        System.out.println(user.getId());
        System.out.println(user.getName());
        System.out.println(user.getPwd());
        System.out.println(user.getHeight());
    }

功能6：模糊查询：

  public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        //数组
        ArrayList<User> list=new ArrayList<>();

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");

            connection= DriverManager.getConnection("jdbc:mysql://localhost:3306/dbtext","root","123456");
            ps=connection.prepareStatement("select  * from t_user where name like concat('%',?,'%')");
            ps.setObject(1,"a");
            rs=ps.executeQuery();
            User user=null;
            while(rs.next()){
                user=new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPwd(rs.getString("pwd"));
                user.setHeight(rs.getDouble("height"));
                list.add(user);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                ps.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
for(int i=0;i<list.size();i++){
    System.out.print(list.get(i).getId());
    System.out.print(list.get(i).getName());
    System.out.print(list.get(i).getPwd());
    System.out.print(list.get(i).getHeight());
    System.out.println("");
}
    }