JDBC连接数据库

JDBC连接数据库

1.Mysql数据库创建

新建数据库-info

create database info；

新建student表

create table student(
      id  int  primary key auto_increment,
      name varchar(32) nut null,
      age  int nut null,
      course varchar(32) nut null
);

插入数据

insert into student(name, age, course) values
(“张三”, 18 ， “数学”),
(“李四”, 19,  “英语”);

2.Java代码

新建java项目-test2，如下：

2.1 导jar 包（见上图）

2.2 新建db.properties, 配置sql连接

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/info?useSSL=false
user=root
password=123456

2.3 封装 JdbcUtil类连接mysql

 public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String user= null;
    private static String password = null;

    static {
        //1. 创建Properties集合类。
        Properties properties = new Properties();

        try {
            //2. 加载文件
            properties.load(new FileInputStream("./src/db.properties"));
            //3. 获取数据，赋值
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            //4. 注册驱动
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取连接
     * @return 连接对象
     */
    public static  Connection getConnection(){
        Connection connection = null;

        try {
            connection = DriverManager.getConnection(url, user, password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return connection;
    }
    
    /**
     * 释放资源
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(connection != null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
    public static void close(Connection connection, Statement statement){
        close(connection,statement,null);
    }
    public static void  close(Connection connection){
        close(connection,null,null);
    }

}

2.4 封装BaseDao类，实现增删改查方法

public class BaseDao {
/**
     * 完成统一的增删改的方法
     *         方法名字：update
     *         方法的参数：
     *                  1.String  SQL语句
     *                  2.需要sql语句对应的参数
     *                      a.参数不确定
     *                         只能放一个数组，Object[]可以存放不同类型的数据
     *         返回值：
     *              int  受影响的行数
     */
    public int update(String sql, Object[] parameters) throws SQLException {
        //判断sql是否为空
        if (sql == null) {
            throw new SQLException("SQL is null");
        }
         //1.获取连接
        Connection connection = JdbcUtils.getConnection();
         //2.获取预处理的搬运工
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
         //3.获取参数元数据对象获取参数的个数
        int parameterCount = preparedStatement.getParameterMetaData().getParameterCount();
        //4.对?进行赋值操作
        for (int i = 1; i <= parameterCount; i++) {
            preparedStatement.setObject(i, parameters[i - 1]);
        }
        //5.执行sql语句
        int i = preparedStatement.executeUpdate();

        JdbcUtils.close(connection, preparedStatement);

        return i;

    }

    /**
     * 完成统一的查询方法
     *  1.查询一条数据
     *  2.查询多条数据
     *  3.查询任意类的数据类型的数据（反射）
     *  方法的分析：
     *      方法的名字:query
     *      方法的参数：
     *          1.sql
     *          2.SQL占位符，使用Object数组
     *          3.？？？
     *              Class<T>
     *                      1.必须带有泛型
     *                      2.这个参数是一个指定查询的类对象
     *        返回值：
     *              List<T>
     */
    public <T> List<T> query(String sql, Object[] parameters, Class<T> cls) throws SQLException {
        if (sql == null || cls == null) {
            throw new NullPointerException();
        }
         //1.获取数据的连接对象
        Connection connection = JdbcUtils.getConnection();
        //2.预处理sql语句
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
         //3.处理sql语句有几个?
        int parameterCount = preparedStatement.getParameterMetaData().getParameterCount();
         //4.给?赋值
        //参数不为空  并且你传入的真实的参数数组的长度刚好和我的?相等，才让你去赋值
        //select * from work where id = ?
        //Object[] objs = {10, 20}
        //1  = 1
        if (parameters != null && parameterCount == parameters.length) {
            for (int i = 1; i <= parameterCount; i++) {
                preparedStatement.setObject(i, parameters[i - 1]);
            }
        }
         //5.执行sql语句
        ResultSet resultSet = preparedStatement.executeQuery();
         //6.准备一个List集合
        ArrayList<T> ts = new ArrayList<>();
          //7.获取结果集元数据对象，获取字段的名字，还有列数，去获取数据库里面的行数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        //8.获取结果集中列的个数
        int columnCount = metaData.getColumnCount();
        //9.遍历数据库结果
        while (resultSet.next()) {
        //10.通过你给我传过来的实参 Work.class  new Work()
            T t = null;
            try {
                t = cls.getConstructor(null).newInstance(null);
             /*
            * i = 1 1 <= 4  id  13 i++
            * i = 2 2<= 4  name 老万 i++
            * i = 3 3<= 4  age 45 i++
            * i = 4 4<= 4 info 嘻嘻哒 i++  5  取得第一行数据
            * while再来取的是第二行数据
            * */
                for (int i = 1; i <= columnCount; i++) {
                      //11.获取字段的名字
                    String columnName = metaData.getColumnName(i);
                      //12.通过字段获取对应的值
                    Object value = resultSet.getObject(columnName);
                       //13.对类的对象进行赋值，但是我现在不能写死
                    BeanUtils.setProperty(t, columnName, value);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
             //14.存到list集合中
            ts.add(t);
        }
        //15.close
        JdbcUtils.close(connection, preparedStatement, resultSet);
         //16.返回值
        return ts.size() != 0 ? ts : null;
    }
}

2.5 新建student类（对应数据库中的student表）

public class Student {
    private int id;
    private String name;
    private int age;
    private String course;

    public Student() {
    }

    public Student(int id, String name, int age, String course) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.course = course;
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", course='" + course + '\'' +
                '}';
    }
}

2.6 测试增删改查方法

public class Demo1 extends BaseDao {

    @Test
    public void testAdd() throws SQLException {
        String sql = "insert into student(name,age,course) values(?,?,?)";

        Object[] objs = {"潘森", 40, "上单"};

        super.update(sql, objs);
    }

    @Test
    public void testDelect() throws SQLException {
        String sql = "delete from work";
        super.update(sql, null);
    }

    @Test
    public void testSelectAll() throws SQLException {
        String sql = "select * from student";
        List<Student> query = super.query(sql, null,Student.class);
        for (Student student : query) {
            System.out.println(student);
        }
    }
}