2、JDBC工具类、批处理语句、调用存储过程和函数、获取自增长主键的值

JDBC工具类

package org.westos.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author lwj
 * @date 2020/8/11 13:46
 */
public class JDBCUtil {
    private static String url;
    private static String username;
    private static String password;

    //构造私有化
    private JDBCUtil() {}
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream(new File("jdbc.properties")));
            Class.forName(properties.getProperty("classname"));
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    public static void close(ResultSet rs, PreparedStatement ps, Connection conn) throws SQLException {
        if (rs != null) {
            rs.close();
        }
        if (ps != null) {
            ps.close();
        }
        if (conn != null) {
            conn.close();
        }
    }

    public static void close(PreparedStatement ps, Connection conn) throws SQLException {
        if (ps != null) {
            ps.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

jdbc.properties配置文件，和工具类解耦合

classname=com.mysql.jdbc.Driver
url=jdbc:mysql:///jdbc?useSSL=false
username=root
password=995995zxvc

测试类

package org.westos.demo2;

import org.westos.bean.User;
import org.westos.util.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;

/**
 * @author lwj
 * @date 2020/8/11 13:29
 */
public class MyTest {
    public static void main(String[] args) throws SQLException {
        //测试JDBCUtil工具类能否获得连接
        Connection conn = JDBCUtil.getConnection();
        System.out.println(conn);
        //com.mysql.jdbc.JDBC4Connection@25f38edc,ok成功
        PreparedStatement preparedStatement = conn.prepareStatement("select * from users where id > 1");
        System.out.println(preparedStatement);
        //com.mysql.jdbc.JDBC42PreparedStatement@531d72ca: select * from users where id > 1
        ResultSet resultSet = preparedStatement.executeQuery();
        ArrayList<User> users = new ArrayList<>();
        while (resultSet.next()) {
            User user = new User();
            user.setId(resultSet.getInt(1));
            user.setUsername(resultSet.getString(2));
            user.setPassword(resultSet.getString(3));
            user.setBirthday(resultSet.getDate(4));
            //java.util.Date兼容java.sql.Date,父子关系,public class Date extends java.util.Date
            users.add(user);
        }
        users.forEach(System.out::println);
        //User{id=2, username='李四', password='123456', birthday=1999-09-09}
        //User{id=3, username='王五', password='123456', birthday=1999-09-09}
        JDBCUtil.close(resultSet, preparedStatement, conn);
    }
}

批处理

package org.westos.demo2;

import org.westos.bean.User;
import org.westos.util.JDBCUtil;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author lwj
 * @date 2020/8/11 14:42
 */
public class MyTest2 {
    public static void main(String[] args) throws SQLException {
        ArrayList<User> users = new ArrayList<>();
        for (int i = 1; i <= 1000; i++) {
            users.add(new User(i, "aaa" + i, "123456", new Date(new java.util.Date().getTime())));
        }

        Connection connection = JDBCUtil.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into users values(?,?,?,?)");
        for (User user : users) {
            preparedStatement.setInt(1, user.getId());
            preparedStatement.setString(2, user.getUsername());
            preparedStatement.setString(3, user.getPassword());
            preparedStatement.setDate(4, new Date(user.getBirthday().getTime()));
            //preparedStatement.executeUpdate(); 非批处理
            preparedStatement.addBatch();
            //批处理
        }
        preparedStatement.executeBatch();
        JDBCUtil.close(preparedStatement, connection);
    }
}

存储过程和函数(CallableStatement)

1、通过Connection对象的prepareCall()方法创建一个CallableStatement对象的实例。在使用Connection对象的prepareCall()方法时，需要传入一个String类型的字符串，该字符串用于指明如何调用存储过程

{?=call <procedure-name>[(<arg1>,<arg2>, ...)]}
{call <procedure-name>[(<arg1>,<arg2>, ...)]}

2、通过CallableStatement对象的registerOutParameter()方法注册OUT参数
3、通过CallableStatement对象的setXxx()方法设定IN或IN OUT参数
若想将参数默认值设为Null,可以使用setNull()方法
4、通过CallableStatement对象的execute()方法执行存储过程
5、如果所调用的是带返回参数的存储过程，还需要通过CallableStatement对象的getXxx()方法获取其返回值

JDBC调用存储过程

1、创建存储过程

DELIMITER $$
CREATE 
	PROCEDURE myProcedure1(IN num INT, OUT countNum INT)
	BEGIN
		DELETE FROM users WHERE id = num;
		SELECT COUNT(*) INTO countNum FROM users;
	END $$
DELIMITER ;

2、JDBC调用（CallableStatement）

package org.westos.demo2;

import org.westos.util.JDBCUtil;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

/**
 * @author lwj
 * @date 2020/8/11 14:50
 */
public class MyTest3 {
    public static void main(String[] args) throws SQLException {
        //调用server端的存储过程和函数
        Connection connection = JDBCUtil.getConnection();
        CallableStatement callableStatement = connection.prepareCall("{call myProcedure1(?,?)}");
        //调用自定义的存储过程
        callableStatement.setInt(1, 1);
        //设置IN模式下的输入参数
        callableStatement.registerOutParameter(2, Types.INTEGER);
        //注册OUT模式下的输出参数
        callableStatement.execute();
        //执行SQL,execute()方法返回true/false，如果返回结果中包含ResultSet，返回true，其余情况返回false
        int count = callableStatement.getInt(2);
        //获OUT模式下的参数返回值
        System.out.println(count);
        //999
    }
}

JDBC调用函数

调用系统函数

package org.westos.demo2;

import org.westos.util.JDBCUtil;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

/**
 * @author lwj
 * @date 2020/8/11 15:06
 */
public class MyTest4 {
    public static void main(String[] args) throws SQLException {
        Connection connection = JDBCUtil.getConnection();
        CallableStatement callableStatement = connection.prepareCall("{? = call md5(?)}");
        callableStatement.registerOutParameter(1, Types.VARCHAR);
        callableStatement.setString(2, "123456");
        System.out.println(callableStatement);
        //com.mysql.jdbc.JDBC42CallableStatement@443b7951: SELECT md5('123456')
        callableStatement.execute();
        String md5_123456 = callableStatement.getString(1);
        System.out.println(md5_123456);
        //e10adc3949ba59abbe56e057f20f883e
    }
}

SELECT MD5('123456');
/*
md5('123456')
e10adc3949ba59abbe56e057f20f883e
*/

获取自增长主键的值

package org.westos.demo2;

import org.westos.util.JDBCUtil;

import java.sql.*;

/**
 * @author lwj
 * @date 2020/8/11 15:28
 */
public class MyTest5 {
    public static void main(String[] args) throws SQLException {
        Connection connection = JDBCUtil.getConnection();
        PreparedStatement ps = connection.prepareStatement("insert into users values(null, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
        //如果你要获取自增长键的值，那么需要额外加一个参数
        ps.setString(1, "张三");
        ps.setString(2, "123456");
        ps.setDate(3, new Date(new java.util.Date().getTime()));
        int count = ps.executeUpdate();
        System.out.println(count);
        //1
        
        //获取自增长键的值
        ResultSet generatedKeys = ps.getGeneratedKeys();
        while (generatedKeys.next()) {
            int generatedKeysInt = generatedKeys.getInt(1);
            System.out.println(generatedKeysInt);
            //1001
        }
        JDBCUtil.close(generatedKeys, ps, connection);
    }
}