• JDBC复习

    什么是jdbc?

            java连接数据库！

    需要jar包的支持：

    • java.sql

    • javax.sql

    • mysql-connection-java...（连接驱动）

    实验环境搭建(SQL)

    1. DROP TABLE `users`;
    2.  
    3.  
    4. CREATE TABLE `users`(
    5.   `id` INT PRIMARY KEY,
    6.   `name` VARCHAR(50) NOT NULL,
    7.   `password` VARCHAR(40) NOT NULL,
    8.   `email` VARCHAR(60) NOT NULL,
    9.   `birthday` DATE
    10. ) ENGINE=INNODB DEFAULT CHARSET=utf8
    11.  
    12. INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('1','张三','123456','zs@sina.com','2021-07-14');
    13. INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('2','李四','123456','lisi@sina.com','1981-12-04');
    14. INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('3','王五','123456','wangwu@sina.com','1982-12-04');
    15. INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('4','赵六','123456','zhaoliu@sina.com','1987-12-05');
    16. INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('5','钱七','123456','qianqi@sina.com','2021-07-19');
    17. INSERT INTO `users` (`id`, `name`, `password`, `email`, `birthday`) VALUES('6','刘八','123456','liuba@sina.com','2021-07-19');

    导入数据库依赖pom.xml

    2. <project xmlns="http://maven.apache.org/POM/4.0.0"
    3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    5.     <modelVersion>4.0.0modelVersion>
    6.  
    7.     <groupId>com.zyygroupId>
    8.     <artifactId>javaweb-jdbcartifactId>
    9.     <version>1.0-SNAPSHOTversion>
    10.  
    11.     <dependencies>
    12.         <dependency>
    13.             <groupId>mysqlgroupId>
    14.             <artifactId>mysql-connector-javaartifactId>
    15.             <version>5.1.49version>
    16.         dependency>
    17.     dependencies>
    18.  
    19.  
    20. project>

    idea连接数据库

    jdbc固定步骤：

    1. 加载驱动

    2. 连接数据库

    3. 创建Statement

    4. 编写sql

    5. 执行sql

    6. 关闭连接

    1. import java.sql.Connection;
    2. import java.sql.DriverManager;
    3. import java.sql.ResultSet;
    4. import java.sql.SQLException;
    5. import java.sql.Statement;
    6.  
    7. /**
    8.  * @ClassName: TestJdbc
    9.  * @Description: TODO 类描述
    10.  * @Author: zyy
    11.  * @Date: 2021/12/14 17:16
    12.  * @Version: 1.0
    13.  */
    14. public class TestJdbc {
    15.     public static void main(String[] args) {
    16.         String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8";
    17.         String user = "root";
    18.         String pwd = "123456";
    19.         try {
    20.  
    21.             //1. 加载驱动
    22.             Class.forName("com.mysql.jdbc.Driver");
    23.             //2. 连接数据库
    24.             Connection con = DriverManager.getConnection(url, user, pwd);
    25.             //3. 向数据库发送sql的对象Statement
    26.             Statement statement = con.createStatement();
    27.  
    28.             //4.sql
    29.             String str = "select * from users";
    30.  
    31.             //5.执行sql
    32.             ResultSet rs = statement.executeQuery(str);
    33.             while (rs.next()) {
    34.                 System.out.println("id=" + rs.getInt("id"));
    35.                 System.out.println("name=" + rs.getString("name"));
    36.                 System.out.println("password=" + rs.getString("password"));
    37.                 System.out.println("email=" + rs.getString("email"));
    38.                 System.out.println("birthday=" + rs.getString("birthday"));
    39.             }
    40.             //6.关闭
    41.             rs.close();
    42.             statement.close();
    43.             con.close();
    44.  
    45.         } catch (ClassNotFoundException e) {
    46.             e.printStackTrace();
    47.         } catch (SQLException e) {
    48.             e.printStackTrace();
    49.         }
    50.     }
    51. }

     预编译

    1. public class Test2Jdbc {
    2.     public static void main(String[] args) {
    3.         String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8";
    4.         String user = "root";
    5.         String pwd = "123456";
    6.         try {
    7.  
    8.             //1. 加载驱动
    9.             Class.forName("com.mysql.jdbc.Driver");
    10.             //2. 连接数据库
    11.             Connection con = DriverManager.getConnection(url, user, pwd);
    12.  
    13.             //3.sql
    14.             String str = "insert into users(id, name, password, email, birthday) values (?,?,?,?,?)";
    15.             //4. 预编译
    16.             PreparedStatement statement = con.prepareStatement(str);
    17.             statement.setInt(1,8);
    18.             statement.setString(2,"小红");
    19.             statement.setString(3,"123456");
    20.             statement.setString(4,"xiaohong@sina.com");
    21.             statement.setDate(5,new java.sql.Date(System.currentTimeMillis()));
    22.  
    23.  
    24.             //5.执行sql
    25.             int count = statement.executeUpdate();
    26.  
    27.             if (count > 0) {
    28.                 System.out.println("插入成功！");
    29.             }
    30.             statement.close();
    31.             con.close();
    32.  
    33.         } catch (ClassNotFoundException e) {
    34.             e.printStackTrace();
    35.         } catch (SQLException e) {
    36.             e.printStackTrace();
    37.         }
    38.     }
    39. }

    JDBC事务

    要么都成功，要么都失败

    ACID原则：保证数据的安全

    开启事务
    事务提交  commit()
    事务回滚  rollback()
    关闭事务

    转账：
    A:1000
    B:1000

    A(900)   ---100-->    B(1100)

    Junit单元测试

    依赖

    1.       <dependency>
    2.             <groupId>junitgroupId>
    3.             <artifactId>junitartifactId>
    4.             <version>4.13version>
    5.         dependency>

    简单实用

    @Test注解只有在方法上有效，只要加了这个注解的方法，就可以直接运行！

    1. import org.junit.Test;
    2.  
    3. /**
    4.  * @ClassName: Test3Jdbc
    5.  * @Description: TODO 类描述
    6.  * @Author: zyy
    7.  * @Date: 2021/12/16 21:11
    8.  * @Version: 1.0
    9.  */
    10. public class Test3Jdbc {
    11.  
    12.     @Test
    13.     public void test() {
    14.         System.out.println("hello");
    15.     }
    16. }

    输出结果： 

     失败情况：

    新建表并插入数据

    1. CREATE TABLE `account`(
    2.   `id` INT PRIMARY KEY,
    3.   `name` VARCHAR(50) NOT NULL,
    4.   `money` FLOAT NOT NULL
    5. ) ENGINE=INNODB DEFAULT CHARSET=utf8
    6.  
    7. INSERT INTO `account` (`id`, `name`, `money`) VALUES(1,'A',1000);
    8. INSERT INTO `account` (`id`, `name`, `money`) VALUES(2,'B',1000);
    9. INSERT INTO `account` (`id`, `name`, `money`) VALUES(3,'C',1000);
    1. # 开启事务
    2. start transaction ;
    3.  
    4. # 模拟转账
    5.  
    6. update account set money=money-100 where name='A';
    7.  
    8. update account set money=money+100 where name='B';
    9.  
    10. # 回滚
    11. rollback ;
    12.  
    13. # 提交
    14. commit;

    1. import org.junit.Test;
    2.  
    3. import java.sql.Connection;
    4. import java.sql.DriverManager;
    5. import java.sql.SQLException;
    6.  
    7. /**
    8.  * @ClassName: Test3Jdbc
    9.  * @Description: TODO 类描述
    10.  * @Author: zyy
    11.  * @Date: 2021/12/16 21:11
    12.  * @Version: 1.0
    13.  */
    14. public class Test3Jdbc {
    15.  
    16.     @Test
    17.     public void test() {
    18.  
    19.         String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf-8";
    20.         String user = "root";
    21.         String pwd = "123456";
    22.         Connection con = null;
    23.         try {
    24.  
    25.             Class.forName("com.mysql.jdbc.Driver");
    26.             con = DriverManager.getConnection(url, user, pwd);
    27.  
    28.  
    29.             //开启事务 这里不开启事务的话异常情况就会有问题   false是开启
    30.             con.setAutoCommit(false);
    31.  
    32.             con.prepareStatement("update account set money=money-100 where name='A'").executeUpdate();
    33.  
    34.             //制造错误
    35. //            int i = 1 / 0;
    36.  
    37.             con.prepareStatement("update account set money=money+100 where name='B'").executeUpdate();
    38.  
    39.             con.commit();
    40.  
    41.             System.out.println("success");
    42.  
    43.         } catch (Exception e) {
    44.             System.out.println("error rollback");
    45.             if (con != null) {
    46.                 try {
    47.                     con.rollback();
    48.                 } catch (SQLException ex) {
    49.                     ex.printStackTrace();
    50.                 }
    51.             }
    52.  
    53.  
    54.         } finally {
    55.             if (con != null) {
    56.                 try {
    57.                     con.close();
    58.                 } catch (SQLException e) {
    59.                     e.printStackTrace();
    60.                 }
    61.             }
    62.         }
    63.  
    64.     }
    65. }

    输出结果：

     

     

  • 相关阅读:
    Java底层总结（未完善）
    LeetCode_区间问题_中等_795.区间子数组个数
    【UE5】 虚拟制片教程
    仿游戏热血江湖游戏类22（得到物品属性方法2）
    perl下mysql同步监控
    互斥锁，自旋锁，读写锁
    CilckHouse创建表
    scipy短时傅里叶分析STFT
    Python:操作SQLite数据库简单示例
    【开源】基于Vue和SpringBoot的数据可视化的智慧河南大屏
  • 原文地址：https://blog.csdn.net/qq_46423017/article/details/126337472