Java_JDBC学习

1、需要Jar包的支持

• java.sql
• javax.sql
• mysql.conneter-java… 连接驱动必须导入

2、在pom.xml导入Mysql依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>javaweb-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!--Mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
    </dependencies>
</project>

3、JDBC步骤及编辑代码

• 配置信息
• 加载驱动
• 连接数据库
• 向数据库发送Sql的对象Statement：CRUD
• 编写SQL
• 执行查询SQL
  • 查询使用：executeQuery
  • 增删改使用：executeUpdate
• 关闭连接

package com.test;
import java.sql.*;

public class TestJdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        String url="jdbc:mysql://localhost:3306/java_jdbc?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
        String username="root";
        String password="123456";
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //向数据库发送Sql的对象Statement,prepareStatement：CRUD
        Statement statement = connection.createStatement();
        //编写SQL
        String Sql="select * from users";
        //执行查询SQL
        ResultSet resultSet = statement.executeQuery(Sql);
        while (resultSet.next()){
            System.out.println("id"+resultSet.getObject("id") );
            System.out.println("name"+resultSet.getObject("name") );
            System.out.println("password"+resultSet.getObject("password") );
            System.out.println("birthday"+resultSet.getObject("birthday") );
        }
        //关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

4、预编译SQL

import java.sql.*;

public class TestJdbc2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        String url="jdbc:mysql://localhost:3306/java_jdbc?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
        String username="root";
        String password="123456";
        //加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //连接数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //编写SQL
        String Sql="INSERT INTO users(name,password,birthday) VALUES(?,?,?)";

        //使用预编译 prepareStatement 进行CRUD
        PreparedStatement preparedStatement = connection.prepareStatement(Sql);
        preparedStatement.setString(1,"李四");//给第一个占位符? 赋值
        preparedStatement.setString(2,"123456");//给第二个占位符? 赋值
        preparedStatement.setDate(3,new Date(new java.util.Date().getTime()));//给第三个占位符? 赋值

        //执行sql
        int i = preparedStatement.executeUpdate();
        if (i>0){
            System.out.println("插入成功！");
        }
        //关闭连接
        preparedStatement.close();
        connection.close();
    }
}

5、事务

保证SQL要么都执行成功，要么都失败！

ACID原则：保证数据的安全

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

Junit 单元测试

添加依赖

<dependency>
  <groupId>junit</groupId>
  <artifactId>junit</artifactId>
  <version>4.12</version>
</dependency>

简单使用

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

package com.test;

import org.junit.Test;

public class TestJdbc3 {
    @Test
    public  void  test(){
        System.out.println("Hello");
    }
}

package com.test;
import org.junit.Test;
import java.sql.*;
public class TestJdbc3 {
    @Test
    public void test() {
        //配置信息
        String url = "jdbc:mysql://localhost:3306/java_jdbc?useUnicode=true&characterEncoding=UTF-8&useSSL=false";
        String username = "root";
        String password = "123456";
        Connection connection = null;
        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //连接数据库
            connection = DriverManager.getConnection(url, username, password);

            //通知数据库开启事务 false为开启
            connection.setAutoCommit(false);
            //编写SQL
            String sql = "UPDATE account set money=money-100 WHERE name='A';";
            connection.prepareStatement(sql).executeUpdate();

            //制造错误
            // int i = 1 / 0;

            String sql2 = "UPDATE account set money=money+100 WHERE name='B';";
            connection.prepareStatement(sql2).executeUpdate();
            //提交事务
            connection.commit();
            System.out.println("success!!");
        } catch (Exception e) {
            try {
                //出现异常通知数据库回滚事务
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}