JDBC连接

JDBC

开始–导入java.sql包–加载并注册驱动程序–创建Connection对象–创建Statement对象执行sql语句

–查询会出来结果–使用ResultSet对象–关闭ResultSet对象–关闭Connection对象–结束

–修改更新sql–关闭connection对象–结束

1.连接数据库

1.加载数据库驱动

Class.forName(getValue("DriverClassName"));

2.注册驱动

/* Driver driver = (Driver) aClass.newInstance();
        DriverManager.registerDriver(driver);*/

java drivermanager中已注册，可省略

3.读取配置文件

 url = "jdbc:mysql://localhost:3306/test";
 user = "root";
 password = "root";
DriverClassName=com.mysql.jdbc.Driver
/*url:http//localhost:8080/gmall/keyboard.jpg
jdbc:mysql://localhost:3306/test
jdbc:mysql :协议
localhost:ip地址
3306,默认mysql的端口号
 test:test数据库  改成需要连接的数据库
*/
//将方其放在resources中，读取配置文件
 private static Properties properties;
//static 代码块只进行一次  避免properties每次都重新new  节约资源
static {
properties = new Properties();    properties.load(DBUtil.class.getClassLoader().getResourceAsStream("db.properties"));

4.获取连接

  //2.父类引用指向子类对象 mysql -u root -p jdbc是大协议 mysql是子协议
Connection   connection = DriverManager.getConnection(getValue("url"), getValue("user"), getValue("password"));

5.关闭连接

 connection.close();

6.getConn()方法

 public static Connection getConn() {
        Connection connection = null;
        try {
            //1.注册驱动，把Driver加载起来
            Class.forName(getValue("DriverClassName"));
            //2.父类引用指向子类对象 mysql -u root -p jdbc是大协议 mysql是子协议
            connection = DriverManager.getConnection(getValue("url"), getValue("user"), getValue("password"));
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

7.getValue()

      
//增强代码健壮性  判断properties内是否有对应的key  获取对应的value  getValue()方法
public static String getValue(String key) {
        if (properties.containsKey(key)) {
            //存在的话返回value
            return properties.getProperty(key);
        } else {
            //自定义异常  若priorities内不存在相应key 抛出异常
            throw new KeyNotFoundException("没有找到对象" + key);
        }
    }

2.运行sql语句

1.Statement

​ 存在拼串操作

​ 存在sql注入问题

2.PreparedStatement

 //2.PreparedStatement用来执行sql语句 ParaedStatement(预编译提升性能  预防sql注入)  是Statemnet的子接口
PreparedStatement preparedStatement = connection.prepareStatement(sql);

3.执行操作（增删改）

preparedStatement.executeUpdate();

4.关闭连接（增删改）

 public static void close(PreparedStatement preparedStatement, Connection connection) throws SQLException {
        //资源的关闭
        //避免出现空指针
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }

3.增删改

1.executeUpdate()

public static void executeUpdate(String sql, Object... para) throws SQLException {
        //1.获取连接对象
        Connection connection = getConn();
        //2.PreparedStatement用来执行sql语句 ParaedStatement(预编译提升性能  预防sql注入)  是Statemnet的子接口

        PreparedStatement preparedStatement = connection.prepareStatement(sql);
    //给占位符赋值
        if (para != null && para.length > 0) {
            for (int i = 0; i < para.length; i++) {
                preparedStatement.setObject(i + 1, para[i]);
            }
        }
        //insert update delete  i表示受影响的行数
        //执行操作
        int i = preparedStatement.executeUpdate();
        System.out.println("受影响的行数" + i);
    //关闭资源
        close(preparedStatement, connection);

    }

2.测试类

public void executeUpdate() {
        try {
            DBUtil.executeUpdate("insert into tb_product values(?,?,?);", 7, "苹果", 10);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

4.查询

查询方法有返回值，需要对结果集进行操作

1.executequery()

//成员变量赋值
//使用泛型，输入类的class文件 返回相应的对象集合
public static <T> List<T> query(String sql, Class<T> t, Object... para) throws SQLException, IllegalAccessException, InstantiationException, NoSuchFieldException {
        //获取连接，并进行预编译
        Connection conn = DBUtil.getConn();
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        //赋值占位符
            for (int i = 0; i < para.length; i++) {
                if (para != null && para.length >= 1) {
                    preparedStatement.setObject(i + 1, para[i]);
                }
            }
        //执行,并返回结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //创建返回的结果集合
        List<T> tList = new ArrayList<>();
        //获取传入的类的实例
        T t1 = t.newInstance();
        //获取结果集的元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        //获取结果集列数
        int columnCount = metaData.getColumnCount();
   while(resultSet.next()) {
        for (int i = 0; i < columnCount; i++) {
            //处理结果集  把结果集赋值给对象
            //.next 判断结果集的下一条数据是否有数据，有数据返回true并指针下移
              //获取结果集的每一列的列名  getColumnName获取的是数据库表中的列名
                String columnLabel = metaData.getColumnLabel(i + 1);
              //获取成员变量
                Field field = t.getDeclaredField(columnLabel);
                //将属性修改为可访问的
                field.setAccessible(true);
              //给成员变量赋值
                field.set(t1, resultSet.getObject(i + 1));
            }
      
  //将查询到的对象放入集合中
        tList.add(t1);
        //关闭资源
       }
        closeresult(preparedStatement, conn, resultSet);
        return tList;
    }

//调用方法赋值
public static <T> List<T> executeQuery(Class<T> tClass, String sql, Object... para) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, NoSuchMethodException, InvocationTargetException {
        //获取数据库连接 运行DML语句
        PreparedStatement preparedStatement = getConn().prepareStatement(sql);
        //给占位符赋值
        for (int i = 0; i < para.length; i++) {
            if (para != null && para.length > 0) {
                preparedStatement.setObject(i + 1, para[i]);
            }
        }
        //获取结果集
        ResultSet resultSet = preparedStatement.executeQuery();
//获取列数  便利打印
        List<T> tList = new ArrayList<>();
        while (resultSet.next()) {
            T t = tClass.newInstance();
            for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
              //拼接字符串，获得传入类的set方法名
                StringBuilder name = new StringBuilder("set");
              //列名，表中的
                String columnName = resultSet.getMetaData().getColumnName(i);
              //表中列名若有_，则切割
                String[] s = columnName.split("_");
              //将切割过的字符串数组便利，把首字母大写并拼接  最后得到方法名
                for (String str : s) {
                    name.append(str.substring(0, 1).toUpperCase()).append(str.substring(1));
                }
//                    System.out.println(name);
              //获取列的值的类型  方法传参类型
                String columnClassName = resultSet.getMetaData().getColumnClassName(i);
              //得到方法
                Method declaredMethod = tClass.getDeclaredMethod(name.toString(), Class.forName(columnClassName));
              //调用方法
                declaredMethod.invoke(t, resultSet.getObject(i));
//                    System.out.print(resultSet.getObject(i)+" ");

            }
            System.out.println();
          //加入集合
            tList.add(t);
        }
        return tList;

    }

2.删除的关闭方法

 public static void closeresult(PreparedStatement preparedStatement, Connection connection, ResultSet resultSet) throws SQLException {
        //资源的关闭
        //避免出现空指针
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }

3.测试类

public void testquery(){
     String sql = "select g_name as gname  from goods where id =?;";
    List<Goods> query = null;
    try {
        query = DBUtil.query(sql, Goods.class, "7");
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        e.printStackTrace();
    } catch (InstantiationException e) {
        e.printStackTrace();
    } catch (NoSuchFieldException e) {
        e.printStackTrace();
    }
    System.out.println(query.toString());
}