Web02_JDBC

1:JDBC

1:简介

1:简介/执行步骤

 2:快速入门

/*
1:先导包先导jar包:mysql-connector-java-5.1.48.jar
2:使用JDBC操作数据库
*/
public class Tets03_JDBC {
    public static void main(String[] args) throws Exception {
        //1:注册驱动,主要告诉jvm咱们使用的实现类到底是谁???
        Class.forName("com.mysql.jdbc.Driver");
        //2:建立连接url:jdbc:mysql://ip地址:端口/数据库名字
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "root");
        //3:通过链接获取执行者对象
        Statement statement = conn.createStatement();
        //4:使用执行者对象执行sql语句
        String sql = "UPDATE Student1 SET name='王四',score = 21.2 WHERE id = 3";
        int row = statement.executeUpdate(sql);
        System.out.println(row);
        //5:释放资源
        statement.close();
        conn.close();
    }
}

3:DriverManager

 

 4:Connection 

 

1:获取执行SQL的对象

 2:事务管理

/**
 * JDBC API 详解：Connection
 */
public class JDBCDemo3_Connection {

    public static void main(String[] args) throws Exception {
        //1. 注册驱动,可以省略,但是建议不要省略
        Class.forName("com.mysql.jdbc.Driver");
        //2. 获取连接：如果连接的是本机mysql并且端口是默认的 3306 可以简化书写(不要省略)
        String url = "jdbc:mysql://localhost:3306/db2?useSSL=false";
        String username = "root";
        String password = "1234";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3. 定义sql
        String sql1 = "update account set money = 3000 where id = 1";
        String sql2 = "update account set money = 3000 where id = 2";
        //4. 获取执行sql的对象 Statement
        Statement stmt = conn.createStatement();

        try {
            // ============开启事务==========
            conn.setAutoCommit(false);
            //5. 执行sql
            int count1 = stmt.executeUpdate(sql1);//受影响的行数
            //6. 处理结果
            System.out.println(count1);
            int i = 3/0;
            //5. 执行sql
            int count2 = stmt.executeUpdate(sql2);//受影响的行数
            //6. 处理结果
            System.out.println(count2);

            // ============提交事务==========
            //程序运行到此处，说明没有出现任何问题，则需求提交事务
            conn.commit();
        } catch (Exception e) {
            // ============回滚事务==========
            //程序在出现异常时会执行到这个地方，此时就需要回滚事务
            conn.rollback();
            e.printStackTrace();
        }

        //7. 释放资源
        stmt.close();
        conn.close();
    }
}

5:Statement

6:ResultSet

public class Test06Query {
    public static void main(String[] args) throws Exception {
        //1:注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2:建立连接url:jdbc:mysql://ip地址:端口/数据库名字
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "root");
        //3:通过链接获取执行者对象
        Statement statement = conn.createStatement();
        //4:使用执行者对象执行sql语句
        String sql = "SELECT * from Student1";
        //获取执行查询语句对象
        ResultSet rs = statement.executeQuery(sql);
        //5:解析结果集 boolean next():将游标往下移动一行,并且返回该行是否有数据,getxxx的方法获取数据
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            double score = rs.getDouble("score");
            Date bir = rs.getDate("bir");
            int age = rs.getInt("age");
            System.out.println(id + ":"  + name + ":" + age + ":" + bir + ":" + score);
        }
        //6:释放资源
        statement.close();
        conn.close();

    }
}

7: PreparedStatement概述

import java.sql.Statement;
/*
使用PreparedStatement预编译sql语句解决sql问题
*/
public class Test09PreparedStatement {
    public static void main(String[] args) throws Exception {
        //1:注册驱动,主要告诉jvm咱们使用的实现类到底是谁???
        Class.forName("com.mysql.jdbc.Driver");
        //2:建立连接url:jdbc:mysql://ip地址:端口/数据库名字
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db2", "root", "root");
        //3:使根据链接获取执行者
        String sql = "UPDATE Student1 SET name='王六',score = 23 WHERE id = ? and name = ?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setObject(1,3);
        ps.setObject(2,"王四");
        int row = ps.executeUpdate();
        if (row > 0){
            System.out.println("修改成功");
        }else{
            System.out.println("修改失败");
        }

        //5:释放资源
        ps.close();
        conn.close();
    }
}

2：连接池

1：简介

 

 2：Druid使用

1:使用步骤:

/*
使用Druid创建数据库链接池
1:先将 druid-1.1.12.jar 和 mysql-connector-java-5.1.48.jar两个jar包导入
2:导入druid.properties配置文件
3:加载配置文件
4:创建连接池
*/
public class Test12Druid {
    public static void main(String[] args) throws Exception {
        //1:加载配置文件,找到这个文件中的内容
        Properties properties = new Properties();
        InputStream in = Test12Druid.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(in);
        System.out.println(properties);
         /*输出:{password=root, initialSize=5, driverClassName=com.mysql.jdbc.Driver,
         maxWait=3000, url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true, 
         username=root, maxActive=10}*/
        //2:创建连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println(connection);//com.mysql.jdbc.JDBC4Connection@69b794e2


        //***************************8
        System.out.println("当前property文件的位置在" + System.getProperty("user.dir"));

        /*当前property文件的位置在D:\IDEAProject\02web\web01*/
    }
}

3:JDBC案例

package com.itheima.ke.test14_anli;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.itheima.ke.test14_anli.pojo.Brand;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class TestDruid {
    /**
     * 查询所有
     * 1. SQL：select * from tb_brand;
     * 2. 参数：不需要
     * 3. 结果：List<Brand>
     */

    @Test
    public void testSelectAll() throws Exception {
        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
       //InputStream in = Test12Druid.class.getClassLoader().getResourceAsStream("druid.properties");

        prop.load(new FileInputStream("src/druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);

        //5. 获取数据库连接 Connection
        Connection conn = dataSource.getConnection();
        //2. 定义SQL
        String sql = "select * from tb_brand;";    
        //3. 获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //4. 设置参数
        //5. 执行SQL
        ResultSet rs = pstmt.executeQuery();
        //6. 处理结果 List<Brand> 封装Brand对象，装载List集合
        Brand brand = null;
        List<Brand> brands = new ArrayList<>();
        while (rs.next()){
            //获取数据
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");
            //封装Brand对象
            brand = new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);

            //装载集合
            brands.add(brand);
        }
        System.out.println(brands);
        //7. 释放资源
        rs.close();
        pstmt.close();
        conn.close();
    }



    /**
     * 添加
     * 1. SQL：insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);
     * 2. 参数：需要，除了id之外的所有参数信息
     * 3. 结果：boolean
     */
    @Test
    public void testAdd() throws Exception {
        // 接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1;
        String description = "绕地球一圈";
        int status = 1;

        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5. 获取数据库连接 Connection
        Connection conn = dataSource.getConnection();
        //2. 定义SQL
        String sql = "insert into tb_brand(brand_name, company_name, ordered, description, status) values(?,?,?,?,?);";
        //3. 获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //4. 设置参数
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);

        //5. 执行SQL
        int count = pstmt.executeUpdate(); // 影响的行数
        //6. 处理结果
        System.out.println(count > 0);

        //7. 释放资源
        pstmt.close();
        conn.close();
    }


    /**
     * 修改
     * 1. SQL：

     update tb_brand
     set brand_name  = ?,
     company_name= ?,
     ordered     = ?,
     description = ?,
     status      = ?
     where id = ?

     * 2. 参数：需要，所有数据
     * 3. 结果：boolean
     */

    @Test
    public void testUpdate() throws Exception {
        // 接收页面提交的参数
        String brandName = "香飘飘";
        String companyName = "香飘飘";
        int ordered = 1000;
        String description = "绕地球三圈";
        int status = 1;
        int id = 4;

        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5. 获取数据库连接 Connection
        Connection conn = dataSource.getConnection();
        //2. 定义SQL
        String sql = " update tb_brand\n" +
                "         set brand_name  = ?,\n" +
                "         company_name= ?,\n" +
                "         ordered     = ?,\n" +
                "         description = ?,\n" +
                "         status      = ?\n" +
                "     where id = ?";

        //3. 获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);

        //4. 设置参数
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);
        pstmt.setInt(6,id);

        //5. 执行SQL
        int count = pstmt.executeUpdate(); // 影响的行数
        //6. 处理结果
        System.out.println(count > 0);

        //7. 释放资源
        pstmt.close();
        conn.close();
    }

    /**
     * 删除
     * 1. SQL：
     delete from tb_brand where id = ?
     * 2. 参数：需要，id
     * 3. 结果：boolean
     */
    @Test
    public void testDeleteById() throws Exception {
        // 接收页面提交的参数
        int id = 4;
        //1. 获取Connection
        //3. 加载配置文件
        Properties prop = new Properties();
        prop.load(new FileInputStream("src/druid.properties"));
        //4. 获取连接池对象
        DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
        //5. 获取数据库连接 Connection
        Connection conn = dataSource.getConnection();
        //2. 定义SQL
        String sql = " delete from tb_brand where id = ?";
        //3. 获取pstmt对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        //4. 设置参数
        pstmt.setInt(1,id);
        //5. 执行SQL
        int count = pstmt.executeUpdate(); // 影响的行数
        //6. 处理结果
        System.out.println(count > 0);

        //7. 释放资源
        pstmt.close();
        conn.close();
    }

}