JDBC的简单使用——MySQL

前面几篇学了基本的SQL语句和用工具操作数据库，今天讲一下用代码操作数据库。

连接MySQL

首先记得需要导包，jdbc连接MySQL数据库的jar包，名字：mysql-connector-java

代码如下：

//1、获取驱动类,路动类路径：com.mysql.jdbc.Driver
Class.forName("com.mysql.jdbc.Driver");
//获取url：jdbc:mysql://ip:端口号/数据库名称
String url = "jdbc:mysql://localhost:3306/test4";
//用户名
String username = "root";
//密码
String password = "123";
//获取Connection对象
Connection connection = DriverManager.getConnection(url, username, password);
System.out.print(connection);

总的来说，先要获取驱动类，然后就是获取Connection对象，就是一行代码DriverManager.getConnection(url, username, password);能走通，就说明连接上了。

修改数据库

@Test
    public void test1() throws ClassNotFoundException, SQLException {
        //获取驱动类
        Class.forName("com.mysql.jdbc.Driver");
        //获取url
        String url = "jdbc:mysql://localhost:3306/test4";
        //用户名
        String username = "root";
        //密码
        String password = "123";
        //获取Connection对象
        Connection connection = DriverManager.getConnection(url, username, password);
        System.out.print(connection);
        //获取Statement对象
        Statement statement = connection.createStatement();
        //String sql="INSERT INTO employee (eid,ename,edid) VALUES(null,'小红',2)";
        String sql = "UPDATE employee SET edid=1 WHERE ename='小红'";
        statement.executeUpdate(sql);
        statement.close();
        connection.close();
    }

就是在连接数据库之后，通过Connection对象获取Statement对象，通过Statement对象的execute方法执行对应的SQL语句，最后记得关闭

查询数据库

@Test
    public void test3() {
        Connection con = null;
        Statement state = null;
        ResultSet resultSet = null;
        try {
            String driverClassName = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://localhost:3306/test4";
            String username = "root";
            String password = "123";
            Class.forName(driverClassName);
            con = DriverManager.getConnection(url, username, password);
            state = con.createStatement();
            String querySQL = "SELECT * FROM employee";
            resultSet = state.executeQuery(querySQL);
            List<Map<String, String>> mapList = new ArrayList<>();
            while (resultSet.next()) {
                Map<String, String> map = new HashMap<>();
                map.put("ename", resultSet.getString("ename"));
                map.put("edid", String.valueOf(resultSet.getInt("edid")));
                mapList.add(map);
            }
            System.out.print(mapList.size());
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (state != null) {
                    state.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

查询数据库需要注意的地方就是：

执行SQL语句的方法是executeQuery()，只能执行查询语句。
他有一个返回值ResultSet，然后循环这个Set对象，获取数据，他提供了一个移动光标的方法next()，当他为null的时候，返回false，结束循环。
还提供了一系列的get方法，传表每一栏的下标index，或者每一栏的名称，获取对应的值。值的类型要与get的类型相同。值为int，就用getInt，值为String就用getString
最后记得关闭连接，倒着关闭。

PreparedStatement

上面用自己拼接sql语句的方式查询数据库，会容易被sql攻击。所以，我们需要用PreparedStatement来防止被sql攻击，具体方式如下：

public boolean login(String username, String password) throws Exception {
        Connection connection = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/test4";
            String user = "root";
            String psw = "123";
            connection = DriverManager.getConnection(url, user, psw);
            String sql = "select * from t_user where username=? and psw=?";
            pstat = connection.prepareStatement(sql);
            pstat.setString(1, username);
            pstat.setString(2, password);
            rs = pstat.executeQuery();
            return rs.next();
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (rs != null) rs.close();
            if (pstat != null) pstat.close();
            if (connection != null) connection.close();
        }
    }

如上，是一个登录接口，传用户名，密码给后台，后台查询数据库，我们在获取了connection之后，通过connection调用prepareStatement方法，需要传一个sql模板。

String sql = "select * from t_user where username=? and psw=?";

什么是sql模板呢？就是吧需要动态传的参数用问好代替。我们上面这个查询语句，需要动态变化的就是username和psw。然后，我们调用PreparedStatement的setXxx方法，去设置值，第一个参数表示给第几个值赋值，第二个参数就是需要赋值的值

//给第一个参数赋值为username
pstat.setString(1, username);
//给第二个参数赋值为password
pstat.setString(2, password);

大数据存储

这里以存mp3为例

CREATE TABLE t_binary(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(50),
	DATA MEDIUMBLOB
)

如上，首先创建一张表去存数据，数据类型不要错了，BLOB类型。

@Test
    public void test1() throws SQLException, IOException {
        Connection connection = JDBCUtils.getConnection();
        String sql="INSERT INTO t_binary VALUE(?,?,?)";
        PreparedStatement pstat = connection.prepareStatement(sql);
        pstat.setInt(1,1);
        pstat.setString(2,"KenT - 唱给谁听.mp3");
        byte[] bytes= IOUtils.toByteArray(new FileInputStream("D:/CloudMusic/KenT - 唱给谁听.mp3"));
        Blob blob=new SerialBlob(bytes);
        pstat.setBlob(3,blob);
        pstat.executeLargeUpdate();
    }

上面test1就是存的方法，步骤：

先连接数据库，获取Connection对象
然后，获取PreparedStatement对象，设置参数，其中blob参数，Blob是一个接口，先获取他的实现类SerialBlob，这个实现类的构造方法需要传一个byte[]数组，所以，我们只用把需要存储的文件转成byte[]数组就可以了
最后，执行sql语句就可以了

这里需要注意的是，要在my.ini服务器配置文件中设置最大存储

//这个大小大于你的存储文件即可
max_allowed_packet=20M

[mysqld]下边的是服务器配置，[mysql]下边的是客户端配置

接下来就是怎么取文件了

@Test
    public void test2() throws SQLException, IOException {
        Connection connection = JDBCUtils.getConnection();
        String sql="SELECT * FROM t_binary WHERE name=?";
        PreparedStatement pstat = connection.prepareStatement(sql);
        pstat.setString(1,"KenT - 唱给谁听.mp3");
        ResultSet rs = pstat.executeQuery();
        if (rs.next()){
            Blob blob = rs.getBlob("data");
            InputStream in = blob.getBinaryStream();
            FileOutputStream out = new FileOutputStream("D:/cgst.mp3");
            IOUtils.copy(in,out);
        }
    }

需要说的就是获取到ResultSet之后，获取到Blob，通过他的getBinaryStream()方法，转成输入流，拿到输入流之后，转成File即可

批处理

@Test
    public void test6() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "INSERT INTO t_user VALUE(?,?,?)";
        PreparedStatement pstat = connection.prepareStatement(sql);
        for (int i = 0; i < 10000; i++) {
            pstat.setInt(1, i + 1);
            pstat.setString(2, "user" + i);
            pstat.setString(3, i % 2 == 0 ? "男" : "女");
            pstat.addBatch();
        }
        long start = System.currentTimeMillis();
        pstat.executeBatch();
        long end = System.currentTimeMillis();
        System.out.print(end - start);
    }

注意点：

每添加一条数据，调用一遍pstat.addBatch();
当数据添加完以后，调用pstat.executeBatch();执行sql语句
mysql要开启批处理功能，不然慢到你无法想象。在获取Connection传的url后面，添加rewriteBatchedStatements=true。完整urljdbc:mysql://localhost:3306/test4?rewriteBatchedStatements=true