JDBC

JDBC：是JAVA提供的一套用来操作数据库的接口

 

 

【案例1】JDBCDemo

/*
    JDBC：是JAVA提供的一套用来操作数据库的接口

    通过Java程序操作数据库：
    1.先确定MySQL是否可以正常使用（MySQL服务是否开启）
    2.确定MySQL和驱动包的版本是否匹配(MySQL5.7  驱动包的版本5.1.37)
    3.确定MySQL账号密码是对的
    4.添加驱动包 :①在工程的根目录下创建一个lib目录
                ②将驱动包拷贝进来
                ③在驱动包上右键 --> add as library--> 选一下工程或module确定即可
 */
public class JDBCDemo {
    /*
        方式一：通过Driver获取数据库连接对象
     */
    @Test
    public void test() throws SQLException {
        //全类名 ：包括包名在内的类的全名称。
        //什么时候用 ：在同一个类中出现了不同包中相同的类名那么就需要使用全类名进行区分。
        Driver driver = new com.mysql.jdbc.Driver();
        /*
            connect(String url, java.util.Properties info)
            url : 数据库连接地址
                     jdbc:mysql://localhost:3306/atguigu
                     jdbc:mysql: 协议
                     localhost ：地址
                     3306 ： 端口号
                     atguigu ：库的名称
            info : （map）用来存放账号和密码
         */
        String url = "jdbc:mysql://localhost:3306/myemployees";
        Properties userInfo = new Properties();
        userInfo.setProperty("user","root");  //账号 --key值不能瞎写
        userInfo.setProperty("password","111111"); ///密码 -- key值不能随便写
        //获取数据库连接对象
        Connection connect = driver.connect(url, userInfo);
        System.out.println(connect);
    }

    /*
        方式二：通过DriverManager获取数据库连接对象
     */
    @Test
    public void test2() throws Exception {
        //1.创建Driver类的对象
        Driver driver = new com.mysql.jdbc.Driver();

        //2.通过DriverManager进行注册
        DriverManager.registerDriver(driver);

        //3.获取数据库连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myemployees", "root", "111111");
        System.out.println(connection);

    }

    /*
        方式二优化
        在Driver类中
                    static {
                try {
                    DriverManager.registerDriver(new Driver());
                } catch (SQLException var1) {
                    throw new RuntimeException("Can't register driver!");
                }
            }
     */
    @Test
    public void test3() throws Exception {
        //1.为了执行类加载让静态代码块执行
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取数据库连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myemployees", "root", "111111");
        System.out.println(connection);
    }

    /*
        最终版
     */
    @Test
    public void test4() throws Exception {
        String driverClassName = "";
        String url = "";
        String username = "";
        String password = "";
        //读取配置文件中的内容
        //1.创建Properties对象
        Properties properties = new Properties();
        //2.创建流
        FileInputStream fis = new FileInputStream("jdbc.properties");
        //3.加载流
        properties.load(fis);
        //4.读取数据
        username = properties.getProperty("username");
        password = properties.getProperty("password");
        driverClassName = properties.getProperty("driverClassName");
        url = properties.getProperty("url");
        //5.关流
        fis.close();


        //为了执行类加载让静态代码执行
        Class.forName(driverClassName);
        //获取数据库连接对象
        Connection connection = DriverManager.getConnection(url,username,password);
        System.out.println(connection);

    }
}

【案例2】PropertiesDemo

/*
    1.Properties是Hashtable的子类
    2.properties的k，v是string类型
    3.常用来读取配置文件的内容
 */
public class PropertiesDemo {
    public static void main(String[] args) throws Exception {
        //1.创建Properties对象
        Properties properties = new Properties();

        //2.创建流
        FileInputStream fis = new FileInputStream("jdbc.properties");

        //3.加载流
        properties.load(fis);

        //4.读取数据
        String username = properties.getProperty("username");
        String password = properties.getProperty("password");
        System.out.println(username + " " + password);

        //5.关流
        fis.close();
    }
}

 

---

 

【案例1】CRUDDemo

/*
    通过代码对数据库进行增，删，改，查的操作
 */
public class CRUDDemo {
    /*
    CREATE TABLE person(
    id INT,
    age INT,
    NAME VARCHAR(20)
    );
        添加数据
     */
    @Test
    public void test() throws SQLException {
        //1.获取数据库连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.sql语句
        // ? :占位符
        String sql = "insert into person(id,age,name) values(?,?,?)";
        //3.预编译 -- 有了此对象就可以给占位符赋值
        PreparedStatement ps = connection.prepareStatement(sql);
        /*
        setInt(int parameterIndex, int x)
        parameterIndex : 第几个占位符
        x : 给占位符赋值的内容
         */
        ps.setInt(1,10);
        ps.setInt(2,18);
        ps.setString(3,"longge");
        //4.执行sql语句
        //返回值 ：有几行受到影响
        int result = ps.executeUpdate();//增，删，改的操作都使用此方法
        System.out.println("共" + result + "行受到影响");
        //5.关闭资源
        JDBCUtils.close(ps,connection);
    }

    /*
        删除数据
         //1.获取数据库连接对象
         //2.Sql语句
         //3.预编译
         //4.执行sql语句
         //5.关闭资源
     */
    @Test
    public void test2() throws SQLException {
        //1.获取数据库连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.Sql语句
        String sql = "delete from person";
        //3.预编译
        PreparedStatement ps = connection.prepareStatement(sql);
        //4.执行sql语句
        int result = ps.executeUpdate();//只能用来执行增，删，改的操作
        //5.关闭资源
        JDBCUtils.close(ps,connection);
    }

    /*
        修改数据
     */
    @Test
    public void test3() throws SQLException {
        //1.获取数据库连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.sql语句
        String sql = "update person set name=? where id=?";
        //3.预编译
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1,"fanlaoshi");
        ps.setInt(2,1);
        //4.执行sql语句
        int result = ps.executeUpdate();//增，删，改的操作都使用此方法
        System.out.println("共" + result + "行受到影响");
        //5.关闭资源
        JDBCUtils.close(ps,connection);
    }
}

 

【案例2】CRUDDemo2

public class CRUDDemo2 {
    /*
        查询单行数据
     */
    @Test
    public void test() throws SQLException {
        //1.获取数据库连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.Sql语句
        String sql = "select id,age,name from person where id=?";
        //3.预编译
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,10);
        //4.执行sql语句
        ResultSet resultSet = ps.executeQuery();//该方法用来执行查询语句
        //5.遍历结果集
        while(resultSet.next()){//是否有下一条数据
            /*
            getInt(String columnLabel)
            columnLabel : 字段名
             */
            int id = resultSet.getInt("id");
            int age = resultSet.getInt("age");
            String name = resultSet.getString("name");
            System.out.println(id + " " + age + " " + name);
        }
        //5.关闭资源
        JDBCUtils.close(ps,connection,resultSet);
    }

    /*
       查询表中所有数据
    */
    @Test
    public void test2() throws SQLException {
        //1.获取数据库连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.Sql语句
        String sql = "select id,age,name from person";
        //3.预编译
        PreparedStatement ps = connection.prepareStatement(sql);
        //4.执行sql语句
        ResultSet resultSet = ps.executeQuery();//该方法用来执行查询语句
        //5.遍历结果集
        while(resultSet.next()){//是否有下一条数据
            /*
            getInt(String columnLabel)
            columnLabel : 字段名
             */
            int id = resultSet.getInt("id");
            int age = resultSet.getInt("age");
            String name = resultSet.getString("name");
            System.out.println(id + " " + age + " " + name);
        }
        //5.关闭资源
        JDBCUtils.close(ps,connection,resultSet);
    }

    @Test
    public void test3() throws SQLException {
        List<Person> persons = getPersons();
        for (Person person : persons) {
            System.out.println(person);
        }

    }

    /*
        自定义一个方法该方法返回表中所有的数据
     */
    public List<Person> getPersons() throws SQLException {

        List<Person> list = new ArrayList<Person>();

        //1.获取数据库连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.Sql语句
        String sql = "select id,age,name from person";
        //3.预编译
        PreparedStatement ps = connection.prepareStatement(sql);
        //4.执行sql语句
        ResultSet resultSet = ps.executeQuery();//该方法用来执行查询语句
        //5.遍历结果集
        while(resultSet.next()){//是否有下一条数据
            /*
            getInt(String columnLabel)
            columnLabel : 字段名
             */
            int id = resultSet.getInt("id");
            int age = resultSet.getInt("age");
            String name = resultSet.getString("name");

            //创建对象
            Person person = new Person(id, age, name);
            //将对象放到集合中
            list.add(person);
        }
        //5.关闭资源
        JDBCUtils.close(ps,connection,resultSet);

        //返回集合
        return list;
    }

}

 

【案例3】JDBCUtils

/*
    工具类
 */
public class JDBCUtils {
    private static String driverClassName = "";
    private static String url = "";
    private static String username = "";
    private static String password = "";
    private static FileInputStream fis = null;

    static{
        try {
            //读取配置文件中的内容
            //1.创建Properties对象
            Properties properties = new Properties();
            //2.创建流
            FileInputStream fis = new FileInputStream("jdbc.properties");
            //3.加载流
            properties.load(fis);
            //4.读取数据
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            driverClassName = properties.getProperty("driverClassName");
            url = properties.getProperty("url");
        }catch (Exception e){
            //终止程序的运行（将编译时异常转换成运行时异常）
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }finally {
            //5.关流
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    public static Connection getConnection() {
        try {
            //为了执行类加载让静态代码块执行。
            Class.forName(driverClassName);
            //获取数据库连接对象
            Connection connection = DriverManager.getConnection(url, username, password);
            return connection;
        }catch (Exception e){
            //终止程序的运行（将编译时异常转换成运行时异常）
            e.printStackTrace();
            throw new RuntimeException(e.getMessage());
        }
    }

    /*
        关闭资源
     */
    public static void close(PreparedStatement ps, Connection connection) {
        if (ps != null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(PreparedStatement ps, Connection connection, ResultSet resultSet) {
        close(ps,connection);
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

 

【案例4】Person

package com.atguigu.jdbc2;

public class Person {
    private int id;
    private int age;
    private String name;

    /*
        为什么要有空参构造器 ：因为框架会通过空参构造器创建JavaBean的对象。
     */
    public Person(){

    }

    public Person(int id, int age, String name) {
        this.id = id;
        this.age = age;
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return id + " " + age + " " + name;
    }
}

---

【案例】批处理

package com.atguigu.jdbc4;

import com.atguigu.jdbc2.JDBCUtils;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/*
    批处理：
        1.连接地址最后要加上参数 ：
            jdbc:mysql://localhost:3306/myemployees?rewriteBatchedStatements=true
        2.驱动包的版本必须为: 5.1.3x

        3.批处理的API
               ps.addBatch();//添加到批处理
               ps.executeBatch();//执行批处理
               ps.clearBatch(); //清空批处理

 */
public class BatchDemo {
    /*
        向数据库插入十万条数据
     */
    @Test
    public void test() throws SQLException {
        //1.获取数据库连接对象
        Connection connection = JDBCUtils.getConnection();
        //2.sql
        String sql = "insert into person(id,age,name) values(?,?,?)";
        //3.预编译
        PreparedStatement ps = connection.prepareStatement(sql);
        for (int i = 1; i <= 100000 ; i++) {
            ps.setInt(1,i);
            ps.setInt(2,i);
            ps.setString(3,"aa" + i);
            ps.addBatch();//添加到批处理

            if (i % 1000 == 0){
                //执行sql
                ps.executeBatch();//执行批处理
                //清空批处理
                ps.clearBatch();
            }

        }
        //4.关闭资源
        JDBCUtils.close(ps,connection);
    }
}