jdbc基础2

2021-07-04 17:59发布

站内文章 / 数据库
598 0 0

day10

一、           转账没有事务

package com.ujiuye.demos;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

 

public class Demo01_转账没有事务 {

    public static void main(String[] args) {

        Connection conn = null;

        // 获取连接对象

        try {

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

            String url = "jdbc:mysql:///day07?serverTimezone=UTC";

            String name = "root";

            String password = "root";

            conn = DriverManager.getConnection(url,name, password);

        } catch (Exception e) {

            e.printStackTrace();

        }

        PreparedStatement ps = null;

        int i = 0;

 

        try {

            // 让洋老板的money-5000

            String sql = "update users set money = money - 5000 where uname = '洋老板' ";

            ps = conn.prepareStatement(sql);

            i = ps.executeUpdate();

            System.out.println(i > 0 ? "洋老板转账成功" : "洋老板转账失败");

        } catch (SQLException e) {

            System.out.println("洋老板转账失败1");

        }

 

        // 金宝的钱 + 5000

        if(i > 0) {

            try {

                // 让报错

                System.out.println(10/0);

                String sql = "update users set money = money + 5000 where uname = '金宝'";

                ps = conn.prepareStatement(sql);

                i = ps.executeUpdate();

                if(i > 0) {

                    System.out.println("金宝收钱成功");

                } else {

                    System.out.println("金宝收钱失败2");

                }

            }catch ( Exception e) {

                System.out.println("金宝收钱失败1");

 

            }

            try {

                ps.close();

            } catch (SQLException throwables) {

                throwables.printStackTrace();

            }

            try {

                conn.close();

            } catch (SQLException throwables) {

                throwables.printStackTrace();

            }

        }

    }

}

二、           转账有事务

package com.ujiuye.demos;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.SQLException;

 

public class Demo02_转账有事务 {

    public static void main(String[] args) {

        Connection conn = null;

        // 获取连接对象

        try {

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

            String url = "jdbc:mysql:///day07?serverTimezone=UTC";

            String name = "root";

            String password = "root";

            conn = DriverManager.getConnection(url,name, password);

        } catch (Exception e) {

            e.printStackTrace();

        }

        PreparedStatement ps = null;

        int i = 0;

 

        try {

            // 设置手动提交

            conn.setAutoCommit(false);

            // 让洋老板的money-5000

            String sql = "update users set money = money - 5000 where uname = '洋老板' ";

            ps = conn.prepareStatement(sql);

            i = ps.executeUpdate();

            System.out.println(i > 0 ? "洋老板转账成功" : "洋老板转账失败");

        } catch (SQLException e) {

            System.out.println("洋老板转账失败1");

        }

 

        // 金宝的钱 + 5000

        if(i > 0) {

            try {

                // 让报错

                System.out.println(10/0);

                String sql = "update users set money = money + 5000 where uname = '金宝'";

                ps = conn.prepareStatement(sql);

                i = ps.executeUpdate();

                if(i > 0) {

                    System.out.println("金宝收钱成功");

                } else {

                    System.out.println("金宝收钱失败2");

                }

                // 提交事务

                conn.commit();

            }catch ( Exception e) {

                System.out.println("金宝收钱失败1");

 

                try {

                    // 事务回滚

                    conn.rollback();

                } catch (SQLException throwables) {

                    throwables.printStackTrace();

                }

            }

            try {

                ps.close();

            } catch (SQLException throwables) {

                throwables.printStackTrace();

            }

            try {

                conn.close();

            } catch (SQLException throwables) {

                throwables.printStackTrace();

            }

        }

    }

}

三、用statement添加20w条数据

import java.sql.*;

public class Demo03_Statement添加20w条数据 {

    public static void main(String[] args) throws Exception {

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

        String url = "jdbc:mysql:///day07?serverTimezone=UTC";

        String name = "root";

        String password = "root";

        Connection conn = DriverManager.getConnection(url, name, password);

        String sql = "";

        Statement stat = conn.createStatement();

        long startTime = System.currentTimeMillis();

            try {

                for (int i = 1; i <= 200000; i++) {

                    sql = "insert into user1(`user`,password) values('admin" + i + "','123456')";

                    stat.executeUpdate(sql);

                }

            }catch (Exception e) {

                System.out.println("出现错误");

            }

        long endTime = System.currentTimeMillis();

        System.out.println("时间" + (endTime - startTime));

        stat.close();

        conn.close();

    }

}

四、PreparedStatement添加20w条数据

package com.ujiuye.demos;

 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

 

public class Demo04_PreparedStatement添加二十万条 {

    public static void main(String[] args) throws Exception {

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

        String url = "jdbc:mysql:///day07?serverTimezone=UTC";

        String user = "root";

        String password = "root";

        Connection conn = DriverManager.getConnection(url,user,password);

        String sql = "";

        PreparedStatement ps = null;

        long startTime = System.currentTimeMillis();

        try {

            for (int i = 1; i <= 200000; i++) {

                sql = "insert into user1(user,password) values('admin" + i +"','12324')";

                ps = conn.prepareStatement(sql);

                ps.executeUpdate();

            }

        } catch(Exception e) {

            System.out.println("出现错误");

        }

        long endTime = System.currentTimeMillis();

        System.out.println("时间：" + (endTime - startTime));

        conn.close();

        ps.close();

    }

}

五、批处理添加数据

package com.ujiuye.demos;

 

import java.sql.*;

 

public class Demo05_批处理添加 {

    public static void main(String[] args) throws Exception {

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

        String url = "jdbc:mysql:///day07?serverTimezone=UTC";

        String name = "root";

        String password = "root";

        Connection conn = DriverManager.getConnection(url, name, password);

        String sql = "";

        Statement stat = conn.createStatement();

        long startTime = System.currentTimeMillis();

        try {

            for (int i = 1; i <= 200000; i++) {

                sql = "insert into user1(`user`,password) values('admin" + i + "','123456')";

                stat.addBatch(sql);

            }

            stat.executeBatch();

        }catch (Exception e) {

            System.out.println("出现错误");