JDBC操作数据库_preparestatement 可以不用set吗-CSDN博客

本文链接：https://blog.csdn.net/weixin_39407066/article/details/81391065

JDBC

JDBC（Java DataBase Connectivity,java数据库连接）JDBC是连接数据库和java程序的桥梁，是一种用于执行SQL（SQL 是用于访问和处理数据库的标准的计算机语言。）语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。JDBC制定了统一的访问各类关系数据库的访问接口，为各个数据库厂商提供了标准接口的实现，JDBC并不能直接访问数据库，需要依赖与数据库厂商提供的JDBC驱动程序。JDBC支持多种关系型数据库，并且它是面向对象的，可以将常用的方法进行二次封装，从而提高代码的重用型。

JDBC的常用类和接口：在java.sql中。DriverManager类用来管理数据库中的所有驱动程序。是JDBC的管理层，作用于用户和驱动程序之间，跟踪可用驱动程序，并在数据库的驱动程序之间建立连接。此外，DriverManager类也处理诸如驱动程序登录时间限制及登录和跟踪信息的显示服务。类中都是静态方法。第一步：用registerDriver方法注册驱动，通常有两种方式去注册数据库驱动程序(这里采用MySQL数据库)，分别为以下代码，并且通过查看源码发现Driver中有一段静态代码块，是向 DriverManager注册一个Driver实例。这样在Class.forName("com.mysql.jdbc.Driver")的时候，就会首先去执行这个静态代码块，于是和DriverManager.registerDriver(new Driver())有了相同的效果。那么对于这两种方法，荐使用第二种，即Class.forName("类名")的方式。原因：在我们执行DriverManager.registerDriver(new Driver())的时候，静态代码块也已经执行了，相当于是实例化了两个Driver对象。

DriverManager.registerDriver(new Driver());  
Class.forName("com.mysql.jdbc.Driver");  


public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

第二步：getConnection方法建立连接，第三部：创建语句，执行语句。最后释放资源的时候与创建的相反，先关闭查询执行的语句，查询语句返回ResultSet对象，调用close方法，再关闭创建的语句也是调用close方法，最后关闭连接，调用close方法，都需要抛出异常。

Connection接口：代表与特定的数据库连接，要对数据表中的数据进行操作，首先要获取数据库连接。Connection就像在应用程序与数据库之间开通了一条通道，可以通过creatStatement方法创建语句。CreateStatement和PrepareStatement都是用来执行sql语句的，Statement和PrepareStatement相比。PrepareStatement提高了代码可读性，PrepareStatement接口是Statement接口的子接口，他继承了Statement接口的所有功能。它主要是拿来解决我们使用Statement对象多次执行同一个SQL语句的效率问题的。ParperStatement接口的机制是在数据库支持预编译的情况下预先将SQL语句编译，当多次执行这条SQL语句时，可以直接执行编译好的SQL语句，这样就大大提高了程序的灵活性和执行效率。ParperStatement最大的优点是它是安全的，防止sql注入。PrepareStatement也可以不用set，直接执行已经处理好的String。


String sql = "select * from users where  username= '"+username+"' and userpwd='"+userpwd+"'";
//sql注入
但要是我们把'or '1' = 1'当作密码传进去就会发生数据库安全问题
select * from user where username = 'user' and userpwd = '' or '1' = '1';

stmt = conn.createStatement();
rs = stmt.executeQuery(sql);


String sql = "select * from users where  username=? and userpwd=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, userpwd);
rs = pstmt.executeQuery();

statement接口：用于创建向数据库中传递SQL语句的对象，该接口提供了一些方法可以实现对数据库的常用操作，JDBC的批量操作接口(addBatch)不接受Select语句。并且addBatch等批处理操作并不是事务，可以和事务连用。

PreparedStatement接口：继承了statement接口，并且有自己的特定方法。PreparedStatement关闭后再调用对象会报异常。注意的是，它还无法批处理多条带不同参数的sql语句。

ResultSet接口：类似于一个临时表，用来暂时存放数据库查询操作所获得的结果集，PreparedStatement关闭后ResultSet不能使用。

 ResultSet resultSet = statement.executeQuery(strSQL);
 while (resultSet.next()) {指针移到下一行，初始在第一行前，
    HashMap<String, String> map = new HashMap<String, String>();
    Field[] fields = getFields(cls);//通过自定义存储数据类的class对象获取字段
    for (int j = 0; j < fields.length; j++) {
         Object value = resultSet.getString(fields[j].getName());
          //System.out.println(resultSet.getMetaData());      
          if (value == null) {
              value = "";
          }
       map.put(fields[j].getName(), value.toString());
    }
}

通过JDBC操作数据库：如果需要访问数据库，首先要加载数据库驱动，数据库驱动只需要第一次访问数据库时加载一次，然后在每次访问数据库时创建一个Connection实例，获取数据库连接，这样就可以执行SQL语句，最后完成数据库操作时，释放与数据库的连接。

二，建立链接：加载完数据库即可建立数据库的链接，要链接数据库可以使用DirverManager类的静态方法getConnection（）来实现，并分别将数据库的URL，数据库用户名和密码作为该方法的参数，即可建立到指定数据库的链接。值得注意的是声明url时候，String url = “jdbc:mysql://localhost:3306/db_database17”; 其中url的前半部分分别为jdbc:mysql://是连接对应数据库的固定写法，接着localhost是本地计算机，可以换成IP地址如：127.0.0.1,如果连接的是网络中其他计算机中的数据库，只需要将localhost改为那台计算机的IP地址即可。3306为数据库默认的端口号，最后的db_database17是所要连接的数据库名，由于一个程序中经常需要对数据库进行操作，如果每次操作数据库都要建立数据库的链接，这样会影响编程效率和代码冗余，为此可以创建一个池。

三，向数据库添加数据：一旦建立了数据库链接，就可以使用Connection接口的PreparedStatement（）获取preparedStatement对象，通过executeUpdate()方法来执行SQL语句，下面“tb_users为数据库中表名”

四，数据库查询：PreparedStatement接口的executeUpdate（）或executeQuery（）方法可以执行SQL语句，executeUpdate（）方法用于执行数据的插入，修改或删除操作，返回影响数据库记录的条数，executeQuery()方法用于执行SELECT查询语句，将返回一个ResultSet型的结果集。通过遍历查询结果集的内容，才可以获取SQL语句执行的查询结果。ResultSet对象具有指向当前数据行的光标，最初，光标被置于第一行之前，可以通过该对象的next（）方法将光标移到下一行，这个类似枚举器的迭代。

“select sex, name from tb_users where id =” + id

五，更改数据库：可以使用PrepareStatement接口中的executeUpdate（）方法对数据库中的表进行修改操作，返回是一个int值，表示受影响的行数，这样知道了共修改几行数据，如把id为1的数据age改为20，

六，模糊查询：比较常见的一种查询方式，例如在订单表中查询某年某月的订单信息，进行模糊查询需要关键字LIKE,使用like关键字进行模糊查询时，可以使用通配符”%“来代替0个或多个字符，使用下划线来代替一个字符。如查询tb_users表中用户名带李的用户信息。

事务：ACID

原子性：组成事务的语句形成了一个逻辑单元，不能只执行其中一部分，

一致性：在事务处理执行前后，数据库是一致的。

隔离性：一个事务处理对另一个事务处理的影响。

持续性：事务处理的效果能够被永久保存。

1.Connection.setAutoCommit(false) 关闭自动提交，打开事务，2.提交事务Connection.commit()，3.回滚事务Connection.rollback()。

注意：如果设置成非自动提交，在最后一定要调用commit或者rollback方法，con 既没有提交也没有回滚，表 USER 就会被锁住 ( 如果 mysql 数据库就是行锁 ) ，而这个锁却没有机会释放。有人会质疑，在执行 con.close() 的时候不会释放锁吗？因为如果应用服务器使用了数据库连接池，连接不会被断开。setAutoCommit（）方法要在执行，如：executeBatch或executeUpdate之前。

1和2需要一起用的关系，1在开头，2.在结尾，当执行2时，操作才上传的数据库。这也就是用事务的方式处理数据库。保证原子性。那么如何判断有没有出错呢，非常简单，执行数据库操作的方法，都会抛出java.sql.SQLException，使用try……catch语句块捕获异常，在catch块中，使用conn.rollback()回滚事务即可。

事务保存点处理：目的是为了回滚部分操作。Connection里的SetSavepoint方法，返回一个Savepoint对象。这时rollback（Savepoint对象）回滚方法需要有一个回滚点对象参数，

JTA容器:跨越多个数据源的的事务，需要用JTA容器实现事务。

隔离级别：多线程并发读取数据时的正确性。级别越高，越安全。但同样消耗性能。Connection的setTransactionIsolation（参数为Connection的静态常量）方法，一般不修改。

在处理大量数据的时候，由快到慢：1-4

1.既用事务，也用批处理

2.只用事务，不用批处理；

3.只用批处理，不用事务；

4.不用批处理，不用事务；

    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(url, user, password);
        String clear = "drop table if exists student";
        PreparedStatement stat = con.prepareStatement(clear, PreparedStatement.RETURN_GENERATED_KEYS);
        stat.executeUpdate();
        stat.close();
        String sql = "CREATE TABLE STUDENT " +
                "(id INTEGER not NULL AUTO_INCREMENT," +
                " name VARCHAR(255), " +
                " phone VARCHAR(255), " +
                " age INTEGER, " +
                " PRIMARY KEY ( id ))" +
                "ENGINE=InnoDB DEFAULT CHARSET=utf8";
        PreparedStatement statement = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
        statement.executeUpdate();
        statement.close();

        String str = "insert into STUDENT (id, name, phone, age) values(%d, %s, %s, %d)";
        String str1 = String.format(str, 1, 1, 1, 1);
        PreparedStatement strSt = con.prepareStatement(str1);//没用PreparedStatement.RETURN_GENERATED_KEYS
        //说明不用PreparedStatement.RETURN_GENERATED_KEYS也可以获取插入后的主键
        strSt.executeUpdate();
        ResultSet result1 = strSt.getGeneratedKeys();//被操作的数据表主键id必须设置AUTO_INCREMENT属性
        result1.next();
        System.out.println(result1.getInt(1));
        strSt.close();

        String str2 = "INSERT INTO student (id, name, phone, age) values(2, 2, 2, 2) ON DUPLICATE KEY UPDATE phone=2, age=2";
        PreparedStatement strSt2 = con.prepareStatement(str2, PreparedStatement.RETURN_GENERATED_KEYS);
        strSt2.executeUpdate();
        strSt2.close();

//        String str3 = String.format("DELETE FROM %s WHERE %s='%s'", "STUDENT", "id", 2);
//        PreparedStatement strSt3 = con.prepareStatement(str3, PreparedStatement.RETURN_GENERATED_KEYS);
//        strSt3.executeUpdate();
//        strSt3.close();

        //count(*) 它返回检索行的数目， 不论其是否包含 NULL值。count(column_name)是对列中不为空的行进行计数 判断表是否存在
        String str4 = "select count(1) from information_schema.tables where table_schema='%s' and table_name='%s'";
        str4 = String.format(str4, "test", "student");
        PreparedStatement strSt4 = con.prepareStatement(str4, PreparedStatement.RETURN_GENERATED_KEYS);
        ResultSet result = strSt4.executeQuery();//也可以用strSt3的executeQuery(str4);
        result.next();
        System.out.println(result.getInt(1) == 1);
        strSt4.close();//PreparedStatement关闭后ResultSet不能使用

        String str5 = "INSERT INTO student (id, name, phone, age) values(3, 3, 3, 3) ON DUPLICATE KEY UPDATE phone=3, age=3";
        PreparedStatement strSt5 = con.prepareStatement(str5, PreparedStatement.RETURN_GENERATED_KEYS);
        strSt5.executeUpdate();
        strSt5.close();

        String str6= "INSERT INTO student (id, name, phone, age) values(3, 3, 5, 5),(2, 2, 4, 4) ON DUPLICATE KEY UPDATE phone = values(phone)," +
                "age = values(age)";
        PreparedStatement strSt6 = con.prepareStatement(str6, PreparedStatement.RETURN_GENERATED_KEYS);
        strSt6.executeUpdate();

        ResultSet result6 = strSt6.getGeneratedKeys();
        result6.next();
        System.out.println(result6.getInt(1));//2
        result6.next();
        System.out.println(result6.getInt(1));//3
        result6.next();
        System.out.println(result6.getInt(1));//4
        result6.next();
        System.out.println(result6.getInt(1));//5
        result6.next();
        //这里打印2345可能是因为insert on update的原因

        String str7 = "INSERT INTO student (name, phone, age) values(4, 4, 4)";
        PreparedStatement strSt7 = con.prepareStatement(str7, PreparedStatement.RETURN_GENERATED_KEYS);
        strSt7.executeUpdate();
        ResultSet result7 = strSt7.getGeneratedKeys();
        result7.next();
        System.out.println(result7.getInt(1));//

        System.out.println("--------------");
        String batStr1 = "insert into STUDENT (id, name, phone, age) values(5, 5, 5, 5)";
        String batStr2 = "insert into STUDENT (id, name, phone, age) values(6, 6, 6, 6)";
        Statement strSt8 = con.createStatement();
        strSt8.addBatch(batStr1);
        strSt8.addBatch(batStr2);
        strSt8.executeBatch();
        System.out.println(strSt8.getMoreResults());//false
        strSt8.clearBatch();
        ResultSet result8 = strSt8.executeQuery("select * from STUDENT where id = 5");
        result8.next();
        System.out.println(result8.getInt(1));
        strSt8.close();

        String clear2 = "drop table if exists person";
        PreparedStatement stat0 = con.prepareStatement(clear2, PreparedStatement.RETURN_GENERATED_KEYS);
        stat0.executeUpdate();
        stat0.close();

        long l = System. currentTimeMillis();
        String sql2 = "CREATE TABLE person " +
                "(id INTEGER not NULL," +
                " name VARCHAR(255), " +
                " PRIMARY KEY ( id ))" +
                "ENGINE=InnoDB DEFAULT CHARSET=utf8";
        PreparedStatement stat2 = con.prepareStatement(sql2, PreparedStatement.RETURN_GENERATED_KEYS);
        stat2.executeUpdate();
        stat2.close();
        String sqlString = "insert into person values(?,?)";
        PreparedStatement pst = con.prepareStatement(sqlString);
        for ( int i = 1; i <= 10; i++) {
            pst.setInt(1, i);
            pst.setString(2, "name" + i);
            pst.addBatch();
//            if (i % 10 == 0) {
//                pst.executeBatch();
//                pst.clearBatch(); // 清空缓存
//            }
        }
        int [] arr = pst.executeBatch();
        System.out.println(pst.getMoreResults());//false
        System.out.println(arr[0]);//-1
        pst.close();
        System.out.println("cost " + (System. currentTimeMillis() - l));

        /**存储过程
         * 	DELIMITER $$
         USE `test`$$
         DROP PROCEDURE IF EXISTS `query_member`$$
         CREATE PROCEDURE `query_member`()
         BEGIN
         SELECT * FROM member;
         SELECT * FROM student;
         DELETE FROM user;
         END$$
         DELIMITER ;
         */
        Statement stmt = con.createStatement();
        boolean bool = stmt.execute("call query_member()");
        boolean check = true;
        do{
            if(bool){
                ResultSet rs = stmt.getResultSet();
                ResultSetMetaData rsmd = rs.getMetaData();
                int count = rsmd.getColumnCount();
                while(rs.next()){
                    for(int i= 1;i <= count; i++){
                        String key = rsmd.getColumnLabel(i);
                        System.out.print(key + ":" + rs.getString(key)+"\t");
                    }
                    System.out.println();
                }
            }else{
                int count = stmt.getUpdateCount();//如果不是ResultSet和没有更新返回-1
                if(count != -1){
                    System.out.println("更新成功影响数据库条数：" + count);
                }else{
                    check = false;
                }
            }
            bool = stmt.getMoreResults();
            //移至下一结果集，如果是ResultSet返回true,如果是更新语句getUpdateCount() > 0 或没有结果集可显示返回false
        }while(check);

        //批处理和事务
        con.setAutoCommit(false);
        try {
            System.out.println("--------------");
            String batStr9 = "insert into STUDENT (id, name, phone, age) values(?, ?, ?, ?)";
            PreparedStatement strSt9 = con.prepareStatement(batStr9);
            strSt9.setInt(1, 7);
            strSt9.setString(2, "7");
            strSt9.setString(3, "7");
            strSt9.setInt(4, 7);
            strSt9.addBatch();

            strSt9.setInt(1, 8);
            strSt9.setString(2, "8");
            strSt9.setString(3, "8");
            strSt9.setInt(4, 8);
            strSt9.addBatch();

            strSt9.executeBatch();//setAutoCommit 要在执行之前
            strSt9.clearBatch();
            strSt9.close();
            con.commit();
        }catch (Exception e){
            con.rollback();
        }

        con.close();
    }

    public static String getFieldSql(Class cls, Class<?> clsType, String fieldName, boolean primaryKey) {
        try {
            if (clsType.equals(int.class) || clsType.equals(Integer.class)) {
                return "int default 0";
            } else if (clsType.equals(short.class) || clsType.equals(Short.class)) {
                return "int default 0";
            } else if (clsType.equals(long.class) || clsType.equals(Long.class)) {
                return "bigint default 0";
            } else if (clsType.equals(byte.class) || clsType.equals(Byte.class)) {
                return "int default 0";
            } else if (clsType.equals(float.class) || clsType.equals(Float.class)) {
                return "double default 0";
            } else if (clsType.equals(boolean.class) || clsType.equals(Boolean.class)) {
                return "int default 0";
            } else if (clsType.equals(Date.class)) {
                return "date default 0";
            } else {
                //通过fieldName获取cls的独特字符串长度
                return primaryKey ? "varchar(50) default 'default' " : "varchar(200) default '默认值' null ";
            }
        } catch (Exception e) {
            return null;
        }
    }