Java JDBC

# JDBC

1.JDBC基本操作

什么是JDBC：Java连接数据库！

web.xml

<dependencies>
    <!--连接数据库-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
</dependencies>

IDEA连接数据库

TestJDBC 查询和删除

import java.sql.*;


public class TestJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //jdbc:mysql://localhost:3306/studydb?useUnicode=true&characterEncoding=utf-8
        String url = "jdbc:mysql://localhost:3306/studydb?useUnicode=true&characterEncoding=utf-8";
        String username = "root";
        String password ="root";

        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.连接数据库，代表数据库
        Connection connection   = DriverManager.getConnection(url,username,password);
        //3.向数据可发送SQL的对象Statement：CRUD
        Statement statement = connection.createStatement();

        //查询
        /*
        //4.编写SQL
        String sql = "select * from users";
        //5.执行SQL查询，返回一个ResultSet：结果集
        ResultSet rs = statement.executeQuery(sql);
        while (rs.next()){
            System.out.println("id = "+ rs.getObject("id"));
            System.out.println("name = "+ rs.getObject("name"));
            System.out.println("password = "+ rs.getObject("password"));
            System.out.println("email = "+ rs.getObject("email"));
            System.out.println("birthday = "+ rs.getObject("birthday"));
        }
         */
        //删除
        String sql = "delete from users where id = 4;";
        int i = statement.executeUpdate(sql);

        //6.关闭连接，释放资源(一定要做) 先开喉管
        rs.close();
        statement.close();
        connection.close();
    }
}

TestJDBC2 增加

import java.sql.*;


public class TestJDBC2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //jdbc:mysql://localhost:3306/studydb?useUnicode=true&characterEncoding=utf-8
        String url = "jdbc:mysql://localhost:3306/studydb?useUnicode=true&characterEncoding=utf-8&&useSSL=false";
        String username = "root";
        String password ="root";

        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.连接数据库，代表数据库
        Connection connection   = DriverManager.getConnection(url,username,password);
        //3.编写SQL
        String sql = "insert into users(id,name,password,email,birthday)values(?,?,?,?,?);";

        //4.预编译
        PreparedStatement preparedStatement =connection.prepareStatement(sql);

        preparedStatement.setInt(1,4);
        preparedStatement.setString(2,"啦啦");
        preparedStatement.setString(3,"123456");
        preparedStatement.setString(4,"ll@qq.com");
        preparedStatement.setDate(5,new Date(new java.util.Date().getTime()));

        //5.执行SQL
        int i = preparedStatement.executeUpdate();
        if (i>0){
            System.out.println("插入成功");
        }

        //6.关闭连接，释放资源(一定要做) 先开喉管
        preparedStatement.close();
        connection.close();
    }
}

sql

create table users(
id not null primary key ,
`name` varchar(40),
`password` varchar(40),
email varchar(40),
birthday date
);

insert into users(id,`name`,`password`,email,birthday)
values(1,'张三','123456','peng@qq.com','2000-01-01');

insert into users(id,`name`,`password`,email,birthday)
values(2,'李四','123456','lisi@qq.com','2000-01-01');

insert into users(id,`name`,`password`,email,birthday)
values(3,'王五','123456','wangwu@qq.com','2000-01-01');

2.JDBC事务

事务

要么都成功，要么都失败！

ACID原则：保证数据安全。

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

web.xml

<!--单元测试-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

import java.sql.*;


public class TestJDBC3 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //jdbc:mysql://localhost:3306/studydb?useUnicode=true&characterEncoding=utf-8
        String url = "jdbc:mysql://localhost:3306/studydb?useUnicode=true&characterEncoding=utf-8&&useSSL=false";
        String username = "root";
        String password = "root";

        Connection connection = null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.连接数据库，代表数据库
            connection = DriverManager.getConnection(url, username, password);
            //3.通知数据库开启事务 false 开启
            connection.setAutoCommit(false);

            String sql = "update account set money = money-100 where name = 'A'";
            connection.prepareStatement(sql).executeUpdate();

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

            String sql1 = "update account set money = money+100 where name = 'B'";
            connection.prepareStatement(sql1).executeUpdate();
            connection.commit();
            System.out.println("success");

        } catch (Exception e) {
            connection.rollback();
            e.printStackTrace();
        } finally {
            connection.close();
        }

    }
}