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

缺乏、安全感 2023-09-27 14:41 12阅读 0赞

## 一、准备 ##

1.  #### 普通项目 ####

![fd320aea0c4d086b4edba549fbfcca5b.png][]

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

1.  #### maven项目 ####

![f4d132de1b05119a9a9916a7c91e598d.png][]

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

![d17317702d4309f7009e0ce87f065b2e.png][]

![bf7dfdf330cc209b3669acfeabe8e4f3.png][]

<dependencies>
 
 
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.49</version>
 </dependency>
 </dependencies>

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

![3b14a52f2a9a26f5f3d13bbf903e6baf.png][]

![e2b5a52958459086bfe913c3a108fe4f.png][]

创建类导入代码

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 volatile 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;
 }
 
 // 封装CRUD
 private 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();
 
 // 构造sql
 statement = 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 {
            private 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传入要操作的数据库的库名以及数据库密码

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

1.  进行数据库增删改查

1.  代码示例如下

<table style=""> 
 <tbody> 
 <tr> 
 <td style="width:100px;vertical-align:top;height:52px;">方法名</td> 
 <td style="width:100px;vertical-align:top;height:52px;">参数</td> 
 <td style="width:100px;vertical-align:top;height:52px;">说明</td> 
 </tr> 
 <tr> 
 <td style="width:100px;vertical-align:top;height:52px;">insert</td> 
 <td style="width:100px;vertical-align:top;height:52px;">String sql</td> 
 <td style="width:100px;vertical-align:top;height:52px;">只需传入增加数据的SQL语句即可</td> 
 </tr> 
 <tr> 
 <td style="width:100px;vertical-align:top;height:52px;">update</td> 
 <td style="width:100px;vertical-align:top;height:52px;">String sql</td> 
 <td style="width:100px;vertical-align:top;height:52px;">只需传入修改的SQL语句即可</td> 
 </tr> 
 <tr> 
 <td style="width:100px;vertical-align:top;height:52px;">delete</td> 
 <td style="width:100px;vertical-align:top;height:52px;">String sql</td> 
 <td style="width:100px;vertical-align:top;height:52px;">只需传入删除的SQL语句即可</td> 
 </tr> 
 <tr> 
 <td style="width:100px;vertical-align:top;height:52px;">select</td> 
 <td style="width:100px;vertical-align:top;height:52px;">Map&lt;String,String&gt; map, String sql</td> 
 <td style="width:100px;vertical-align:top;height:52px;">map是一组键值对，键是要查询数据库表的字段名，值是其对应的Java类型（如varchar(20)对应String)</td> 
 </tr> 
 </tbody> 
</table>

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"));
 }

[fd320aea0c4d086b4edba549fbfcca5b.png]: https://img-blog.csdnimg.cn/img_convert/fd320aea0c4d086b4edba549fbfcca5b.png
[MySQL_Java_JDBC]: https://blog.csdn.net/qq_61903414/article/details/126348428?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522167681122516800188547381%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=167681122516800188547381&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-1-126348428-null-null.blog_rank_default&utm_term=JDBc&spm=1018.2226.3001.4450
[f4d132de1b05119a9a9916a7c91e598d.png]: https://img-blog.csdnimg.cn/img_convert/f4d132de1b05119a9a9916a7c91e598d.png
[d17317702d4309f7009e0ce87f065b2e.png]: https://img-blog.csdnimg.cn/img_convert/d17317702d4309f7009e0ce87f065b2e.png
[bf7dfdf330cc209b3669acfeabe8e4f3.png]: https://img-blog.csdnimg.cn/img_convert/bf7dfdf330cc209b3669acfeabe8e4f3.png
[3b14a52f2a9a26f5f3d13bbf903e6baf.png]: https://img-blog.csdnimg.cn/img_convert/3b14a52f2a9a26f5f3d13bbf903e6baf.png
[e2b5a52958459086bfe913c3a108fe4f.png]: https://img-blog.csdnimg.cn/img_convert/e2b5a52958459086bfe913c3a108fe4f.png