JDBC1

JDBC

什么是jdbc：JAVA链接数据库

需要jar包的支持：
java.sql
javax.sql
mysql-connection-java…连接驱动

DAO

DataAccess Object：数据访问对象

实验环境搭建

CREATE TABLE users (
	id INT PRIMARY KEY,
	`name` VARCHAR(40),
	`password`  VARCHAR(40),
	email VARCHAR(60),
	birthday DATE	
);

INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(1,'张三','123456','zs@qq.com','2000-01-01');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(2,'李四','123456','ls@qq.com','2000-01-01');
INSERT INTO users(id,`name`,`password`,email,birthday)
VALUES(3,'王五','123456','ww@qq.com','2000-01-01');
SELECT * FROM users;

导入依赖

        <!--mysql的驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>

JDBC固定步骤

1、加载驱动
2、连接数据库，代表数据库
3、向数据库发送SQL的对象Statement：CRUD
4、编写SQL（根据业务，不同的SQL）
5、执行SQL
6、关闭连接

public class TestJdbc {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        //userUnicode=true&characterEncoding=utf-8 解决中文乱码
        String url = "jdbc:mysql://localhost:3306/mybatis?userUnicode=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("id="+rs.getObject("name"));
            System.out.println("id="+rs.getObject("password"));
            System.out.println("id="+rs.getObject("email"));
            System.out.println("id="+rs.getObject("birthday"));
        }

        //6、关闭连接，释放资源（一定要做）先开后关
        rs.close();
        statement.close();
        connection.close();

    }
}

预编译SQL

public class TestJDBC2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //配置信息
        //userUnicode=true&characterEncoding=utf-8 解决中文乱码
        String url = "jdbc:mysql://localhost:3306/mybatis?userUnicode=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
        String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";

        //4、预编译
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1,4);
        preparedStatement.setString(2,"狂神说java");
        preparedStatement.setString(3,"123456");
        preparedStatement.setString(4,"12345@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();

    }
}

事物

要么都成功，要么都失败
ACID原则：保证数据的安全

//开启事物
//事物提交
commit();
//事物回滚
rollback();
//关闭事物
/*
转账：
A：1000
B：1000
A（900）--100-->B(1100)

*/

Junit单元测试

依赖

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

简单使用
@Test注解只有在方法上有效，只要加了注解的方法，就可以直接运行

public class TestJDBC3 {
    @Test
    public void test12() {
        //配置信息
        //userUnicode=true&characterEncoding=utf-8 解决中文乱码
        String url = "jdbc:mysql://localhost:3306/mybatis?userUnicode=true&characterEncoding=utf-8";
        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 sql2 = "update account set money =money + 100 where name ='B'";
            connection.prepareStatement(sql2).executeUpdate();

            connection.commit();//以上两条sql都执行成功，提交事务
            System.out.println("sucess");

        } catch (Exception e) {
            try {
                //如果出现异常，通知数据库回滚事务
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}