JDBC连接数据库

JDBC

JDBC固定步骤

• 加载驱动
• 连接数据库
• 向数据库发送SQL对象 Statement
• 编写sql
• 执行sql
• 关闭连接

xml配置

  <dependencies>
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.47</version>
      </dependency>

  </dependencies>

JDBC 查询

package com.srx;

import java.sql.*;

public class TestJDBC {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //配置信息
        String url="jdbc:mysql://localhost:3306/lx?useUnicode=true&characterEncoding=utf8&&useSSL=true";
        String username="root";
        String pwd="123456";

        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.连接数据库 代表数据库
        Connection connection = DriverManager.getConnection(url, username, pwd);

        //3.向数据库发送SQL的对象Statement, PreparedStatement :CRUD
        Statement statement = connection.createStatement();

        //4.编写sql
        String sql=" SELECT * FROM userinfo";

        //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("age="+rs.getObject("age"));
            System.out.println("address="+rs.getObject("address"));
        }

        //6.关闭连接，释放资源  先开后关

        rs.close();
        statement.close();
        connection.close();
    }

}

JDBC 预编译

package com.srx;

import java.sql.*;

public class TestJDBC01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //配置信息
        String url="jdbc:mysql://localhost:3306/lx?useUnicode=true&characterEncoding=utf8&&useSSL=true";
        String username="root";
        String pwd="123456";

        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        //2.连接数据库 代表数据库
        Connection connection = DriverManager.getConnection(url, username, pwd);

        String sql="insert into userinfo(id,name,age,address)values (?,?,?,?)";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setInt(1,4);
        ps.setString(2,"wwl");
        ps.setInt(3,18);
        ps.setString(4,"上海");

        int i=ps.executeUpdate();

        if (i>0){
            System.out.println("ok");
        }
        else {
            System.out.println("no");
        }


        ps.close();
        connection.close();
    }
}

JDBC 事务

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

AFID原则：保证数据的安全

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

Junit 单元测试

代替main方法

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

package com.srx;

public class Test {

    @org.junit.Test
    public void aa(){
        System.out.println("hellow");
        System.out.println("123131");
    }
}

依赖

     <!--单元测试-->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.47</version>
      </dependency>

JDBC 事务

package com.srx;

import java.sql.Connection;
import java.sql.DriverManager;

public class Test {

    @org.junit.Test
    public void aa() {
        //配置信息
        String url="jdbc:mysql://localhost:3306/lx?useUnicode=true&characterEncoding=utf8&&useSSL=true";
        String username="root";
        String pwd="123456";
        Connection connection=null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.jdbc.Driver");

            //2.连接数据库 代表数据库
            connection = DriverManager.getConnection(url, username, pwd);

            //3.通知数据库开启事务 false 开启
            connection.setAutoCommit(false);

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

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

            connection.commit();
            System.out.println("success");
        }
        catch (Exception e){
           try {
               connection.rollback();
           }
           catch (Exception e1){
               e1.printStackTrace();
           }
        }
    }
}