Java-MySQL的JDBC连接及JDBC工具类的实现

Java IDE 连接MySQL5.7的工具类：

import java.sql.*;

/*
JDBC工具类
MySQL
*/
public class DBHelper {
    //mysql
    private static final String DRIVER = "com.mysql.jdbc.Driver"; //驱动类的路径
    private static final String URL = "jdbc:mysql://127.0.0.1:3306/mysql?useUnicode=true&characterEncoding=UTF-8";
    private static final String USER = "root";
    private static final String PWD = "12053a1203884352";

    //获得连接
    public static Connection getConn(){
        Connection conn = null;
        try {
            //加载驱动
            Class.forName(DRIVER);
            //在驱动管理器的基础上获得链接
            conn = DriverManager.getConnection(URL, USER, PWD);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return conn;
    }

    //关闭连接
    public static void closeConn(Connection conn){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    /**
     * 设置SQL语句中参数的值
     * @param ps
     * @param values
     */
    public static void setParam(PreparedStatement ps, Object...values){
        //判断SQL语句中是否有参
        if(values!=null && values.length>0){
            //循环设置参数的值
            for (int i = 0; i < values.length; i++) {
                try {
                    ps.setObject(i+1, values[i]);
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 执行增 删 改
     * @param conn
     * @param sql
     * @param values
     * @return
     */
    public static int executeUpdate(Connection conn, String sql,Object...values){
        int count=0;
        try {
            //编写SQL语句
            PreparedStatement ps = conn.prepareStatement(sql);
            //设置参数的值
            setParam(ps,values);
            //执行SQL语句
            count = ps.executeUpdate();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 执行查询
     * @param conn
     * @param sql
     * @param values
     * @return
     */
    public static ResultSet executeQuery(Connection conn, String sql,Object...values){
        ResultSet rs = null;
        try {
            //编写SQL语句
            PreparedStatement ps = conn.prepareStatement(sql);
            //设置参数的值
            setParam(ps,values);
            //执行SQL语句
            rs  = ps.executeQuery();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }
}

测试：

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;

/*
@Project：MySQLConnection
@Package:
@Date:12:54
@User:15616
*/
public class Test {
    public static void main(String[] args) throws SQLException {
        Connection connection=DBHelper.getConn();
        //add
        String sql="insert messages(date,text) values(?,?)";
        DBHelper.executeUpdate(connection,sql, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()),"xxx");
        //del
        sql="delete from messages where id=?";
        DBHelper.executeUpdate(connection,sql,"1");
        //update
        sql="update messages set date=? where id=?";
        DBHelper.executeUpdate(connection,sql, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()),"2");
        //query
        sql="select * from messages";
        ResultSet resultSet=DBHelper.executeQuery(connection,sql);
        while (resultSet.next())
            System.out.println(resultSet.getString(1)+"\t"+resultSet.getString(2)+"\t"+resultSet.getString(3));
        DBHelper.closeConn(connection);
    }
}