java使用JDBC连接MySQL数据库

java使用JDBC连接MySQL数据库，实现基本的增删改查

• 需要的jar包或者依赖文件，我选择的是依赖文件

<!--jdbc连接mysql需要的jar包-->
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.47</version>
    </dependency>

需要注意的是，如果选择的版本高于5，可能会报

  Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
获取连接对象失败
java.sql.SQLException: The server time zone value 'Öйú±ê׼ʱ¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

可以将加载驱动的对象换成“com.mysql.cj.jdbc.Driver”，将url的配置文件加上“serverTimezone=GMT%2B8”

• 下面的主要的代码及其注解
  将jdbc封装成一个工具类

package jdbc;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Auther: 
 * @Date: 
 * @Description:jdbc封装工具类
 */
public class JDBCUtil {
    //提取公共属性
    private static final String DRIVER = "com.mysql.jdbc.Driver";
    private static final String URL = "jdbc:mysql://localhost:3306/ww";
    private static final String USER = "root";
    private static final String PASSWORD = "123456";
    private static Connection connection = null;
   //优先加载公共代码块，只会加载一次，节约资源
    static {
       //加载对应驱动
       try {
           Class.forName(DRIVER);
       } catch (ClassNotFoundException e) {
           System.out.println("加载驱动失败");
           e.printStackTrace();
       }

   }

    //获取连接对象
    private static Connection getConnection(){
        try {
            return DriverManager.getConnection(URL,USER,PASSWORD);
        } catch (SQLException e) {
            System.out.println("获取连接对象失败");
            e.printStackTrace();
            return null;
        }
    }

    /* *
     * 功能描述:封装增删改的方法
     * @Param [sql]
     * @return: int
     * @auther:
     * @date:
     */
    public static int executeUpdate(String sql){
        //执行语句
        Statement statement = null;
        connection = getConnection();
        try {
            //创建语句对象
            statement = connection.createStatement();
            //执行语句
            return statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }finally {
            close(null,statement,connection);
        }

    }

    /* *
     * 功能描述:执行查询方法
     * @Param [sql]
     * @return: java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     * @auther: 
     * @date:
     */
    public static List<Map<String,Object>> list(String sql){
        Statement statement = null;
        ResultSet result = null;
        connection = getConnection();
        try {
            //创建语句对象
            statement = connection.createStatement();
            //执行语句
            result =  statement.executeQuery(sql);
            //创建结果集的结构化对象
            ResultSetMetaData rsmd  = result.getMetaData();
            //获取查询结果中的字段个数
            int  count = rsmd.getColumnCount();
            //创建数组存放字段名
            String [] columnNames = new String[count];
            //遍历所有字段，将字段名存入数组中
            for (int i = 0;i < count; i++){
                columnNames[i] = rsmd.getColumnName(i+1);
            }
            //创建list存储查询结果
            List<Map<String,Object>> list = new ArrayList<>();
            //取出结构化数据中的数据，每一次循环代表一行数据
            while (result.next()){
                //创建map存储一行数据
                Map<String,Object> map = new HashMap<>();
                //将每一行的数据中的字段名与值对应放入map中
                for (int i = 0; i< count; i++){
                    String columnName = columnNames[i];
                    //根据字段名获取字段名对应的值
                    String value = result.getString(columnName);
                    //将字段名和对应的字段值存入map中
                    map.put(columnName,value);
                }
                //将map存入list中
                list.add(map);
            }
            return list;

        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            close(result,statement,connection);
        }

    }
    //关闭连接
    //按照先开后关的顺序
    private static  void close(ResultSet resultSet, Statement statement, Connection connection ){
        try{
            if (resultSet != null){
                resultSet.close();
            }if (statement != null){
                statement.close();
            }if (connection != null){
                connection.close();
            }
        }catch (SQLException e){
            e.printStackTrace();
        }

    }

}

测试类

package jdbc;

import org.junit.Test;
public class SqlTest {
    @Test
    public void addTest(){
        String sql = "INSERT INTO `user` (u_id,u_tel,u_password,u_sex,u_name,u_code) VALUES (2, '15788261246', '123456','女','测试一','N101');";
        System.out.println(JDBCUtil.executeUpdate(sql));   ;
    }

    @Test
    public void updateTest(){
        String sql = "UPDATE `user` SET u_tel = '32165498725' WHERE u_id = 1 ";
        System.out.println(JDBCUtil.executeUpdate(sql)); ;
    }

    @Test
    public void deleteTest(){
        String sql  = "DELETE FROM `user` WHERE u_id = 1 ";
        System.out.println(JDBCUtil.executeUpdate(sql));

    }
    @Test
    public void selectTest(){
        String sql = "select * from `user`";
        System.out.println(JDBCUtil.list(sql));
    }
}

亲测有效！

第一篇博客，如有不正确，请多指教