JDBC

JDBC的5中连接方式
ResultSet
Statement
- - preparedStatement
总结一下JDBCAPI
封装一个JDCBCUtil工具类
事务处理
批处理
批处理

JDBC是一种连接JAVA和mysql的工具，后面又mybatis框架会使用到他所以兄弟们，学！

JDBC的5中连接方式

第一种使用driver

 public  void m1() throws SQLException {
 		//创建一个Dirver注册驱动
        Driver driver = new Driver ();
        //固定写法mysql可以换成其他数据库 java2022是数据库的名字
        String url = "jdbc:mysql://localhost:3306/java2022";
        Properties properties = new Properties ();
        properties.setProperty ("user", "root");
        properties.setProperty ("password", "123456");
        //灵魂一步drivet。connet返回一个connect连接
        Connection connect = driver.connect (url, properties);
        //statement是真正操作数据库的使用connect连接创建一个statement
        Statement statement = connect.createStatement ();
        //后续先不用看
        String path = "update news set newrong = 'newrong' where id = 1";
        int i = statement.executeUpdate (path);
        System.out.println (i>0 ? "cg" : "sb");
        statement.close ();
        connect.close ();
    }

第二种使用class.forname来动态选择

 @Test
    public  void m2() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        Class<?> aClass = Class.forName ("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance ();
        String url = "jdbc:mysql://localhost:3306/java2022";
        Properties properties = new Properties ();
        properties.setProperty ("user", "root");
        properties.setProperty ("password", "123456");
        Connection connect = driver.connect (url, properties);
        Statement statement = connect.createStatement ();
        String path = "update news set newrong = 'newrong' where id = 1";
        int i = statement.executeUpdate (path);
        System.out.println (i>0 ? "cg" : "sb");
        statement.close ();
        connect.close ();
    }

第三种使用DriverManager

 @Test
    public  void m3() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
        Class<?> aClass = Class.forName ("com.mysql.jdbc.Driver");
        Driver driver = (Driver) aClass.newInstance ();
        String url = "jdbc:mysql://localhost:3306/java2022";
        Properties properties = new Properties ();
        properties.setProperty ("user", "root");
        properties.setProperty ("password", "123456");
        DriverManager.deregisterDriver (driver);
        Connection connect = DriverManager.getConnection (url, properties);
        Statement statement = connect.createStatement ();
        String path = "update news set newrong = 'newrong' where id = 1";
        int i = statement.executeUpdate (path);
        System.out.println (i>0 ? "cg" : "sb");
        statement.close ();
        connect.close ();
    }

第四种使用Class.forname

 @Test
    public  void m4() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {
    	//底层自动注册（有一个静态方法） 所以可以只写这一个方法
        Class.forName ("com.mysql.jdbc.Driver");
       
        String url = "jdbc:mysql://localhost:3306/java2022";
        String user = "root";
        String password = "123456";
        Connection connect = DriverManager.getConnection (url, user,password);
        Statement statement = connect.createStatement ();
        String path = "update news set newrong = 'newrong' where id = 1";
        int i = statement.executeUpdate (path);
        System.out.println (i>0 ? "cg" : "sb");
        statement.close ();
        connect.close ();
    }

第五种使用properties文件进行填充

 @Test
    public  void m5() throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException, IOException {
        Class.forName ("com.mysql.jdbc.Driver");

        Properties properties = new Properties ();
        properties.load (new FileInputStream ("src\\com\\class3\\class3_22\\db.properties"));
        String url = properties.getProperty ("url");
        String user = properties.getProperty ("user");
        String password = properties.getProperty ("password");
        Connection connect = DriverManager.getConnection (url, user,password);
        Statement statement = connect.createStatement ();
        String path = "update news set newrong = 'newrong' where id = 1";
        int i = statement.executeUpdate (path);
        System.out.println (i>0 ? "cg" : "sb");
        statement.close ();
        connect.close ();
    }

注：再mysql4之后（包括mysql4）我们可以不用写calss.forname()进行驱动了，已经自动注册过了但是还是建议写上

ResultSet

那么就有小伙伴就说那Select * from table 会返回一大堆结果呢，java中怎么返回呀，放心java是一个成熟的大姐姐了会有处理办法的
ResultSet就是他的方法其中的row数组下的Object数组中存储的每一行数据
让我们举个例子吧

 @Test
    public void m1() throws ClassNotFoundException, IOException, SQLException {


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

        Properties properties = new Properties ();
        properties.load (new FileInputStream ("src\\com\\class3\\class3_22\\db.properties"));
        String url = properties.getProperty ("url");
        String user = properties.getProperty ("user");
        String password = properties.getProperty ("password");
        Connection connect = DriverManager.getConnection (url, user, password);
        Statement statement = connect.createStatement ();
        String sql = "select * from news";
        ResultSet resultSet = statement.executeQuery (sql);
        while (resultSet.next ()) {
            int anInt = resultSet.getInt (1);
            String string = resultSet.getString (2);
            System.out.println (anInt + "\t" + string);
        }
        resultSet.close ();
        statement.close ();
        connect.close ();
    }

其中resultSet.getInt(1) 中的1表示的是第1列， news数据库中第一列为id为int类型第二列为varchar（）类型所以第二列为String别忘了close resultSet哦

Statement

在这里插入图片描述
一般情况下，我们不会在工作中使用Statement因为它存在sql注入问题
所以我们来介绍PreparedStatement

preparedStatement

完美的解决了sql注入问题

 Scanner sca = new Scanner(System.in);
        System.out.print ("请输入账号：");
        Integer localhost = sca.nextInt ();
        System.out.print ("请输入密码：");
        String localhostpasword = sca.next ();
        Class.forName ("com.mysql.jdbc.Driver");
        Properties properties = new Properties ();
        properties.load (new FileInputStream ("src\\com\\class3\\class3_22\\db.properties"));
        String user = properties.getProperty ("user");
        String password = properties.getProperty ("password");
        String url = properties.getProperty ("url");
        Connection connection = DriverManager.getConnection (url, user, password);
        String sql = "select * from news where id = ? and newrong = ?";
        PreparedStatement preparedStatement = connection.prepareStatement (sql);
        preparedStatement.setInt (1 , localhost);
        preparedStatement.setString (2, localhostpasword);
        ResultSet resultSet = preparedStatement.executeQuery ();
        if (resultSet.next ()){
            System.out.println ("恭喜你成功登录");
        }else {
            System.out.println ("很遗憾你登录失败");
        }
    }

其中需要注意的是executeQuery（）后的参数就不用写了，因为在前面已经被处理了，这里不仅画蛇添足还是错误的，非常好用~

总结一下JDBCAPI

在这里插入图片描述

封装一个JDCBCUtil工具类

public class JDBCUtils {
    private static String url;
    private static String password;
    private static String user;
    private static String driver;

    static {
        Properties properties = new Properties ();
        try {
            properties.load (new FileInputStream ("src\\com\\class3\\class3_22\\db.properties"));
            url = properties.getProperty ("url");
            password = properties.getProperty ("password");
            user = properties.getProperty ("user");
            driver = properties.getProperty ("driver");
        } catch (IOException e) {
            throw new RuntimeException ();
        }
    }

    public static Connection coonect() {
        try {
            Class.forName (driver);
            return DriverManager.getConnection (url, user, password);
        } catch (Exception e) {
           throw new RuntimeException ();
        }
    }

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

可以多使用自己写的工具这是写dml时候的代码

@Test
    public void m1(){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.coonect ();
            String sql = "update admin set name = ? where id = ?";
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.setString (1 , "xiahui");
            preparedStatement.setInt (2 , 1);
            int i = preparedStatement.executeUpdate ();
            System.out.println (i > 0 ?"成功" : "失败");
        } catch (SQLException e) {
            e.printStackTrace ();
        }finally {
            JDBCUtils.close (null , preparedStatement , connection);
        }
    }

使用查询语句时的情况

 @Test
    public void m1() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        try {
            connection = JDBCUtils.coonect ();
            String sql = "SELECT  * from admin";
            preparedStatement = connection.prepareStatement (sql);
            set = preparedStatement.executeQuery ();
            while (set.next ()) {
                int id = set.getInt ("id");
                String name = set.getString ("name");
                System.out.println (id + "\t" + name);
            }
        } catch (SQLException e) {
            e.printStackTrace ();
        } finally {
            JDBCUtils.close (set, preparedStatement, connection);
        }
    }

事务处理

其中connect.setAutoCommit() true 表示自动提交 false 表示取消自动提交

 public static void main( String[] args ){
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet set = null;
        try {
            connection = JDBCUtils.coonect ();
            String sql = "UPDATE `user` set passwod = passwod - 1 where id = 1";
            String sql1 = "UPDATE `user` set passwod = passwod + 1 where id = 2";
            connection.setAutoCommit (false);
            preparedStatement = connection.prepareStatement (sql);
            preparedStatement.executeUpdate ();
            PreparedStatement preparedStatement1 = connection.prepareStatement (sql1);
            preparedStatement1.executeUpdate ();
            connection.commit ();
        } catch (SQLException e) {
            try {
                connection.rollback ();
            } catch (SQLException e1) {
                e1.printStackTrace ();
            }
            e.printStackTrace ();
        } finally {
            JDBCUtils.close (set, preparedStatement, connection);
        }
    }

不要忘记再catch时使用rollback来回滚操作，commit来手动提交

批处理

在这里插入图片描述

批处理

有一辆小车，有5000个小朋友，要从a到b 怎么样才能快速呢，按传统方法一次小车车只载一个小朋友要载5000次
代码如下：

 @Test
    public void m1(){
        Connection coonect = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            coonect = JDBCUtils.coonect ();
            String sql = "insert into `user` values(? , ? , '666')";
            preparedStatement = coonect.prepareStatement (sql);
            long l = System.currentTimeMillis ();
            for (int i = 0; i < 5000; i++) {
                preparedStatement.setInt (1,i);
                preparedStatement.setString (2 , "name");
                preparedStatement.executeUpdate ();
            }
            long l1 = System.currentTimeMillis ();
            System.out.println ("使用时间：" + (l1 - l));

        } catch (Exception e) {
            e.printStackTrace ();
        }finally {
            JDBCUtils.close (resultSet , preparedStatement , coonect);
        }
    }

运行结果如下：
在这里插入图片描述
一共运行了9秒，十分难受，
让我们换一个思路， 1辆车（特别特别大哦）我说一个数！载5000个！
使用批处理代码如下
：

 @Test
    public void m1(){
        Connection coonect = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            coonect = JDBCUtils.coonect ();
            String sql = "insert into `user` values(? , ? , '666')";
            preparedStatement = coonect.prepareStatement (sql);
            long l = System.currentTimeMillis ();
            for (int i = 0; i < 5000; i++) {
                preparedStatement.setInt (1,i);
                preparedStatement.setString (2 , "name");
                preparedStatement.addBatch ();
                if ((i+1) % 1000 == 0){
                    preparedStatement.executeBatch ();
                    preparedStatement.clearBatch ();
                }
            }
            long l1 = System.currentTimeMillis ();
            System.out.println ("使用时间：" + (l1 - l));

        } catch (Exception e) {
            e.printStackTrace ();
        }finally {
            JDBCUtils.close (resultSet , preparedStatement , coonect);
        }
    }

结果如下：
在这里插入图片描述
肥肠好用
其中使用了

Statement.addBatch（）添加数据
Statement.executeBatch() 处理数据
Statement.clearBatch() 清空数据
addBatch中有一个Object类型的动态数组初始大小为10 一次扩容1.5（Arraylist）

原文链接：https://blog.csdn.net/xasasaa/article/details/123666912