【MySQL】Java连接MySQL数据库（封装版只需会MySQL）

一、准备

1. 普通项目

如果创建的是普通的Java项目，我们需要去maven仓库下载jdbc驱动包然导入项目中就能使用，具体步骤详见MySQL数据库之Java中如何使用数据库【JDBC编程】

2. maven项目

如果创建的项目是maven项目，我们只需在pom.xml文件里引入一组依赖即可

<dependencies><!--jdbc--><!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.49</version></dependency></dependencies>

引入依赖后点击右侧的maven刷新即可

创建类导入代码

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;public class JDBCDemo {private static DataSource dataSource;private static String database;private static String password;private static int port = 3306;  //如果修改了端口号提供设置/*** 提供构造方法 传入操作的数据库名称 与 数据库的密码* @param database 数据库名* @param password 数据库密码*/public JDBCDemo(String database,String password) throws SQLException {if (password == null){password = "";}if (database == null || database == ""){throw new SQLException("数据库名为空");}this.database = database;this.password = password;}/*** 如果修改了MySQL的端口号就进行设置* @param port*/public void setPort(int port){this.port = port;}// 封装CRUDprivate int SQL(String sql){int n = 0;Connection connection = null;PreparedStatement statement = null;try {// 建立连接connection = DBUtil.getConnect();// 构造请求statement = connection.prepareStatement(sql);// 发送请求n = statement.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {// 释放资源DBUtil.close(connection,statement,null);}return n;}public boolean insert(String sql) {return SQL(sql) == 1 ? true : false;}public boolean delete(String sql){return SQL(sql) == 1 ? true : false;}public boolean update(String sql) {return SQL(sql) == 1 ? true : false;}/**** @param map 键值对：  字段名：字段对应的Java类型* @param sql 对应的查询sql语句* @return 返回M键值对：   字段名：值（需要根据具体类型进行转换）*/public HashMap<String,String> select(Map<String,String> map, String sql){HashMap<String,String> result = new HashMap<>();Connection connection = null;PreparedStatement statement = null;ResultSet resultSet = null;try {// 建立连接connection = DBUtil.getConnect();// 构造sqlstatement = connection.prepareStatement(sql);// 处理响应resultSet = statement.executeQuery();Set<Map.Entry<String,String>> entry = map.entrySet();while (resultSet.next()){for (Map.Entry<String ,String> x : entry) {switch (x.getKey()){case "int":int i = resultSet.getInt(x.getValue());result.put(x.getValue(),String.valueOf(i));break;case "double":double d = resultSet.getDouble(x.getValue());result.put(x.getValue(),String.valueOf(d));break;case "float":float f = resultSet.getFloat(x.getValue());result.put(x.getValue(),String.valueOf(f));break;case "byte":byte b = resultSet.getByte(x.getValue());result.put(x.getValue(),String.valueOf(b));break;case "char":String c = resultSet.getString(x.getValue());result.put(x.getValue(),String.valueOf(c));break;case "String":result.put(x.getValue(),x.getKey());break;case "long":long l = resultSet.getLong(x.getValue());result.put(x.getValue(), String.valueOf(l));break;case "short":short s = resultSet.getShort(x.getValue());result.put(x.getValue(),String.valueOf(s));break;default:throw new Exception("类型错误");}}}} catch (SQLException e) {e.printStackTrace();} catch (Exception e) {e.printStackTrace();} finally {DBUtil.close(connection,statement,resultSet);}return result;}/*** 封装与数据库连接相关的操作*/private static class DBUtil {/*** 单例模式封装数据源* @return*/private static DataSource getInstance(){if (dataSource == null){synchronized (JDBCDemo.class){if (dataSource == null){dataSource = new MysqlDataSource();((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:" + JDBCDemo.port + "/"+ JDBCDemo.database + "?characterEncoding=utf8&useSSL=false");((MysqlDataSource)dataSource).setUser("root");((MysqlDataSource)dataSource).setPassword(JDBCDemo.password);}}}return dataSource;}/*** 与数据库建立连接* @return*/private static Connection getConnect() throws SQLException {return getInstance().getConnection();}/*** 释放资源* @param connection* @param statement* @param resultSet*/private static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) {if(resultSet != null){try {resultSet.close();} catch (SQLException e) {e.printStackTrace();}}if(statement != null){try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if(connection != null){try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}}
}

二、API

1. 创建实例JDBCDemo传入要操作的数据库的库名以及数据库密码

2. 如果修改过MySQL的端口号调用setPort(int)方法进行设置

3. 进行数据库增删改查

4. 代码示例如下

方法名	参数	说明
insert	String sql	只需传入增加数据的SQL语句即可
update	String sql	只需传入修改的SQL语句即可
delete	String sql	只需传入删除的SQL语句即可
inselect	Map<String,String> map, String sql	map是一组键值对，键是要查询数据库表的字段名，值是其对应的Java类型（如varchar(20)对应String)

public static void main(String[] args) throws SQLException {// 1.创建实例，传入数据库名（此处的数据库是指create database创建的数据库里与密码JDBCDemo jdbcDemo= new JDBCDemo("student","这里是数据库密码");// 2.如果修改过数据库的端口号可调用一下方法修改,默认是没有修改的jdbcDemo.setPort(3306);// 3.添加数据String name = "李四";int age = 10;jdbcDemo.insert("insert into student values(" + name + "," + age + ")");jdbcDemo.update("update student set age=" + 11 + "where name='李四'");jdbcDemo.delete("delete from student where name='张三'");// 获取树数据HashMap<String,String> tableName = new HashMap<>();map.put("name","String");map.put("age","int");HashMap<String,String> ret = jdbcDemo.select(map,"select * from student");int studentAge = Integer.parseInt(map.get("age"));}