【Java】【db】数据库开发

下载jdbc的驱动包
https://dev.mysql.com/downloads/connector/j/5.1.html
建立工程

package cn.it.demo1;

import org.junit.Test;

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

public class Demo1 {
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "******"; //pwd

        Connection con = DriverManager.getConnection(url, username,
                password);
        System.out.println(con);
    }
}

启动mysql服务

jdbc crud

package cn.it.demo2;

import org.junit.Test;

import java.sql.*;

public class Demo2 {
    @Test
    public void fun1() throws ClassNotFoundException, SQLException {
        String driverClassName = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "123456";

        //加载驱动类
        Class.forName(driverClassName);
        Connection con = DriverManager.getConnection(url,
                username, password);

        //statement向数据库发送sql语句
        Statement stmt = con.createStatement();
        //语句里不加分号
        String sql = "insert into tbl values(12, 'wang', '234')";
        String sql1 = "update tbl set name='zhao', pwd='222' " +
                "where id=12";
        int r = stmt.executeUpdate(sql1);
        System.out.println(r);

        stmt.close();
        con.close();
    }

    @Test
    public void fun2() throws ClassNotFoundException, SQLException {
        //执行查询
        String driverClassName = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "123456";

        //加载驱动类
        Class.forName(driverClassName);
        Connection con = DriverManager.getConnection(url,
                username, password);

        //statement向数据库发送sql语句
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("select * from tbl");

        /**
         * 解析rs
         * 调用next方法 把行光标移动到第一行
         */
        while (rs.next()){
            int id = rs.getInt(1); //通过列编号获取值
            String name = rs.getString("name"); //通过列名称获取值
            System.out.println("id = " + id);
            System.out.println("name = " + name);
        }

        // 关闭资源 倒序
        rs.close();
        stmt.close();
        con.close();
    }
}

新建一个utils包 省的每次用jdbc都要重新写很麻烦

在src下建立一个.properties文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testdb
username=root
password=******

JdbcUtils.java

package cn.it.demo3;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcUtils {
    private static Properties props = null;
    //只在JdbcUtils类被加载时执行一次
    static {
        //给props初始化
        //加载配置文件
        InputStream in = JdbcUtils.class.getClassLoader()
                .getResourceAsStream("dbconfig.properties");
        props = new Properties();
        try {
            props.load(in);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        try { //加载驱动类
            Class.forName(props.getProperty("driverClassName"));
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() throws SQLException {

        return DriverManager.getConnection(props.getProperty("url"),
                props.getProperty("username"),
                props.getProperty("password"));
    }
}

这个加载配置文件太秀了啊

Demo3.java

package cn.it.demo3;

import org.junit.Test;

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

public class Demo3 {
    @Test
    public void fun3() throws SQLException {
        Connection con = JdbcUtils.getConnection();
        System.out.println(con);
    }
}

大型文件mysql存储

想把mp3文件存到MySQL先建个表

create table tbl_bin(
    id int primary key auto_increment,
    filename varchar(100),
    data mediumblob);

下载一个commons-io.jar
https://commons.apache.org/proper/commons-io/download_io.cgi

bin就是jar

把mp3文件存到数据库

再读取出来 放到另一个地方

package cn.it.demo4;

import cn.it.demo3.JdbcUtils;
import org.apache.commons.io.IOUtils;
import org.junit.Test;

import javax.sql.rowset.serial.SerialBlob;
import java.io.*;
import java.sql.*;

/**
 * @author KNOE
 * @date 2020-09-19 15:52
 */
public class Demo4 {
    //把mp3保存到数据库中
    @Test
    public void fun1() throws SQLException, IOException {
        Connection con = JdbcUtils.getConnection();
        String sql = "insert into tbl_bin values(?, ?, ?)";
        PreparedStatement pstmt = con.prepareStatement(sql);

        pstmt.setInt(1, 1);
        pstmt.setString(2, "sign.mp3");
        /**
         * 要把文件转成blob
         * 先变为byte[]
         * 再创建blob
         */
        byte[] bytes = IOUtils.toByteArray(new FileInputStream("D:/CloudMusic/sign.mp3"));
        Blob blob = new SerialBlob(bytes);
        //设置参数
        pstmt.setBlob(3, blob);

        pstmt.executeUpdate();
    }

    //从数据库中读取mp3
    @Test
    public void fun2() throws SQLException, IOException {
        Connection con = JdbcUtils.getConnection();
        String sql = "select * from tbl_bin";
        PreparedStatement pstmt = con.prepareStatement(sql);

        ResultSet rs = pstmt.executeQuery();

        if(rs.next()){
            Blob blob = rs.getBlob("data");
            /**
             * 把blob变成硬盘上的文件
             * 1.通过blob得到输入流对象
             * 2.自己创建输出流对象
             * 3.把输出流数据写入到输入流
             */
            InputStream in = blob.getBinaryStream();
            OutputStream out = new FileOutputStream("d:/sign.mp3");
            IOUtils.copy(in, out);
        }
    }
}

这个大文件存进去以后直接MySQL读不好使。

批处理

值针对增删改，不管查询

在配置文件中添加批处理许可

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testdb?rewriteBatchedStatements=true
username=root
password=******

package cn.it.demo5;

import cn.it.demo3.JdbcUtils;
import org.junit.Test;

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

/**
 * @author KNOE
 * @date 2020-09-19 18:06
 */
public class Demo5 {
    @Test
    public void fun5() throws SQLException {
        Connection con = JdbcUtils.getConnection();
        String sql = "insert into tbl values(?, ?, ?)";
        PreparedStatement pstmt = con.prepareStatement(sql);

        for(int i = 0; i < 1000; i++){
            pstmt.setInt(1, i + 1);
            pstmt.setString(2, "stu_" + i);
            pstmt.setString(3, "p" + i);

            pstmt.addBatch(); //添加批
        }
        long start = System.currentTimeMillis();
        pstmt.executeBatch(); //执行批
        long end = System.currentTimeMillis();

        System.out.println(end - start);
    }
}

消耗0.6s 比不执行批处理快大约700倍
查看是否插入成功

select count(*) from tbl;