JDBC 连接MySql复杂到优化

学习目标:

        1. 熟练使⽤JDBC

        2. 掌握JDBC中核⼼类和接⼝

        3. 解决SQL注⼊问题

JDBC:

        JDBC是为了完成对数据库的访问操作而提供的一套统一的标准,

JDBC: 原理图

 JDBC连接数据库的步骤: 1.加载驱动 (将驱动类加载到JVM中)

Class.forName("com.mysql.jdbc.Driver");

2.获取连接 (通过数据库驱动管理器 DriverManager 获取连接)

Connection connection = DriverManager.getConnection(url , userName , password);

        解释: 

                url : 数据库的服务器的地址 jdbc:mysql://localhost:3306/数据库名字

                userName : 连接数据库的用户名

                password : 连接数据库的密码

3.编写对应的 sql 语句

String sql = "select * freom 表名";

sql语句的语法规则和数据库的语法规则相同,且其语句设定为String类型

4.利用步骤 2 中 的到的连接 , 获取执行sql语句的加载器 

Statement statement = connection.createStatement();

5.利用步骤四 将步骤三的sql语句执行

statement.executeUpdate(sql);

statement.execute(sql);

statement.executeQuery(sql);

注意:

        executeUpdate : 可以执行增删改

        剩下两个执行的是查询

6.将执行SQL后的结果处理

        返回值有三种:

                int 类型 : 影响的行数 大于 -1 ,证明执行成功

                boolean : true 证明执行成功

                ResultSet : 查询数据返回的结果集

while (resultSet.next()){
//     int id3 = resultSet.getInt(1);
//     String sname = resultSet.getString(2);
//     String subj = resultSet.getString(3);
//     String score = resultSet.getString(4);

//                *************************************************************

//     int id3 = resultSet.getInt("id");
//     String sname = resultSet.getString("sname");
//     String subj = resultSet.getString("subj");
//     String score = resultSet.getString("score");

}

 结果集中获取数据的两种方式:

        1.利用游标

                每一条数据有N个字段,第一个字段为1 , 以此类推,获取的类型要意义对应

                例如:  int id3 = resultSet.getInt(1);

        2.利用数据库中对应的字段

                例如 : int id3 = resultSet.getInt("id");

7.  最后执行完 要关闭连接 , Statement , 和结果集  实操代码: 拥有增删改查的全部功能

package com.qf.dao;

import com.qf.entity.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;




public class StudentDao {
    
    // 加载驱动只加载一次 [可以忽略不写]
    static {
        // 1.加载驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    Statement statement = null;
    Connection connection = null;
    private String url = "jdbc:mysql://localhost:3306/java2205_test";
    private String userName = "root";
    private String password = "123456";
    public void add(Student student){
        try {
            // 2.创建连接
            connection = DriverManager.getConnection(url , userName ,password);
            // 3.编写sql 语句
            String sql = "insert into student values " +
                    "('"+student.getId()+"' , '"+student.getName()+"' , '"+student.getSubject()+"' , "+student.getScore()+")";
            // 4.获取sql的执行指令
            statement = connection.createStatement();
            // 5.执行sql
            int i = statement.executeUpdate(sql);

            if (i > -1) System.out.println("添加成功");
        } catch ( SQLException e) {
            e.printStackTrace();
            System.out.println("添加失败");
        }finally {
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public void update( int id , Student student){
        try {

            this.getConnections();

            String sql = "update student set sname = '" + student.getName() + "' , " +
                    "subj = '" +student.getSubject()+ "' , score = '"+student.getScore()+"' where id = '" + student.getId() +"'";

            int i = statement.executeUpdate(sql);

            if (i>-1) System.out.println("修改成功");

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            this.closeAll();

        }
    }

    //删除
    public void delete(int id){
        try {
            this.getConnections();
            String sql = "delete from student where id = '" +id+ "'";

            int i = statement.executeUpdate(sql);
            if (i>-1) System.out.println("删除成功");
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("删除失败");
        }finally {
           this.closeAll();
        }
    }

    public Student selectStudent(int id){
        // 1.记载驱动
        try {
            this.getConnections();
            // 3.编写sql语句
            String sql = "select * from student where id = '"+id+"'";
            // 5.执行sql语句
            boolean execute = statement.execute(sql);

            ResultSet resultSet = statement.executeQuery(sql);
            Student student = new Student();
            //将查询出来的数据按照键获取出来,并打印到控制台中
            while (resultSet.next()){
                int id2 = resultSet.getInt("id");
                String sname = resultSet.getString("sname");
                String subj = resultSet.getString("subj");
                String score = resultSet.getString("score");

                System.out.println(id2 + " " + sname + " " + subj + " " + score + " ");

                student.setId(id2);
                student.setName(sname);
                student.setSubject(subj);
                student.setScore(score);
            }
            if (execute) {
                System.out.println("查询成功");
                return student;
            }

            return null;

        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("查询失败");
            return null;
        }finally {
            try {
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
//        return null;
    }

    public List selectAll(String biao){
        ArrayList list = new ArrayList<>();
        try {
            this.getConnections();
            String sql = "select * from " + biao;

            ResultSet resultSet = statement.executeQuery(sql);

            while (resultSet.next()){
                Student student = new Student();
//                int id3 = resultSet.getInt(1);
//                String sname = resultSet.getString(2);
//                String subj = resultSet.getString(3);
//                String score = resultSet.getString(4);

//                *************************************************************

//                int id3 = resultSet.getInt("id");
//                String sname = resultSet.getString("sname");
//                String subj = resultSet.getString("subj");
//                String score = resultSet.getString("score");
//                System.out.println(id3 + " " + sname + " " +subj + " "+ score);
//
//                student.setId(id3);
//                student.setName(sname);
//                student.setSubject(subj);
//                student.setScore(score);

//                ************************************************

                student.setId(resultSet.getInt(1));
                student.setName(resultSet.getString(2));
                student.setSubject(resultSet.getString(3));
                student.setScore(resultSet.getString("score"));
                //查出来的数据放入student
                list.add(student);
            }
            if (resultSet != null){
                System.out.println("查询成功,且有数据");
                return list;
            }
            return  null;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            this.closeAll();
        }
    }

    private void closeAll(){
        try {
            if (statement != null) statement.close();
            if (connection != null) connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    private void getConnections(){
        try {
            connection = DriverManager.getConnection(url , userName ,password);
            statement = connection.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
//        new StudentDao().add();
//        new StudentDao().update("翠花" , 1);
//        new StudentDao().delete(9);
//        Student student = new StudentDao().selectStudent(1);
//        System.out.println(student);
        List student = new StudentDao().selectAll("student");

        Iterator iterator = student.iterator();
        while (iterator.hasNext()){
            System.out.println(iterator.next());
        }
        System.out.println("-*----------------------*-");
        for (Student stu:student) {
            System.out.println(stu);
        }
    }
}