jdbc的学习和一些基础封装

最新推荐文章于 2024-08-29 16:08:22 发布

村里最菜的全栈

最新推荐文章于 2024-08-29 16:08:22 发布

阅读量82

点赞数

文章标签：学习 java 数据库

原文链接：https://blog.csdn.net/x18094/article/details/124346123

版权

这篇博客介绍了如何使用Java的JDBC连接MySQL数据库，包括创建Maven项目、配置数据库连接信息、封装JDBC工具类以及执行CRUD操作。通过ResourceBundle读取配置文件，简化了代码。示例中展示了查询学生姓名的Java代码。

摘要由CSDN通过智能技术生成

小白刚学东西一点啰嗦。

1.首先创建maven项目，也可以直接引入

2.然后就是具体代码

1.具体配置文件，最比较新的jar中可以省略driver的配置，因为是自动加载的

url=jdbc:mysql://localhost:3306/demo
user=root
password=123456

2.封装的java类，获取路径换了ResourceBound方法，网上统一的基本第一个相对路径方法，同层级还好，层级不一样就芭比Q了。

package com.pp.util;

import java.io.FileReader;
import java.io.Reader;
import java.sql.*;
import java.util.*;

public class JdbcUtil {
    private  static String driver;
    private  static String url;
    private  static String user;
    private  static String password;
    private  static Connection conn;

    static {

        try {
//            Properties properties = new Properties();
//            Reader reader = new FileReader("db.properties");
//            properties.load(reader);
//            driver = properties.getProperty("driver");
//            url = properties.getProperty("url");
//            user = properties.getProperty("user");
//            password = properties.getProperty("password");
            ResourceBundle bundle = ResourceBundle.getBundle("db");
//            driver = bundle.getString("driver");
//            driver = "com.mysql.cj.jdbc.Driver";
            url = bundle.getString("url");
            user = bundle.getString("user");
            password = bundle.getString("password");


        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static  Connection getConnection() {
        try {
//            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            return conn;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;

    }
    public static void closeConn(Connection conn) {
        if(conn !=null){
            try {
                conn.close();
                conn = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public static void closeStatement(Statement statement) {
        if(statement !=null){
            try {
                statement.close();
                statement = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
    public static void closeResultSet(ResultSet resultSet) {
        if(resultSet !=null){
            try {
                resultSet.close();
                resultSet = null;
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
//    统一查询封装
private static void execute(String sql) {
    try {
        conn = getConnection();
        conn.setAutoCommit(false);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.executeUpdate();
        conn.commit();
    } catch (SQLException e) {
        e.printStackTrace();
        try {
            conn.rollback();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    } finally {
        closeConn(conn);
    }
}

    /**
     * 增加数据
     */
    public static void create(String sql) {
//        String sql = "INSERT INTO USER (user_name,user_password,user_age) VALUES('老王','123456',18)";
        execute(sql);
    }

    /**
     * 删除数据
     */
    public static void delete(String sql) {
//        String sql = "DELETE FROM USER WHERE USER.user_name = '老王'";
        execute(sql);
    }


    /**
     * 修改数据
     */
    public static void update(String sql) {
//        String sql = "UPDATE USER SET USER.user_name = '老李'";
        execute(sql);
    }
    /**
     * 查询数据
     */
    private static ResultSet getResultSet(String sql) throws SQLException {
        PreparedStatement preparedStatement = null;
        //执行查询语句并返回结果集
        ResultSet resultSet = null;
        conn = getConnection();
        preparedStatement = conn.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        return resultSet;
    }
    public static List<Map<String, Object>> queryForListMap(String sql) {
        ResultSet resultSet = null;
        List<Map<String, Object>> result = new ArrayList<>();
        try {
            resultSet = getResultSet(sql);
            ResultSetMetaData rsmd = resultSet.getMetaData();//获得结果集结构信息,元数据
            int numberOfColumns = rsmd.getColumnCount();//获得列数
            while (resultSet.next()) {
                Map<String, Object> rowData = new HashMap<>();
                for (int j = 1; j <= numberOfColumns; j++) {
                    rowData.put(rsmd.getColumnName(j), resultSet.getString(j));
                }
                result.add(rowData);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConn(conn);
        }
        return result;
    }

    }

3.然后调用方法，我就用了一个简单查询语句ok

package com.pp;

//import java.sql.SQLException;
//import java.sql.Connection;
//import java.sql.DriverManager;
//import java.sql.PreparedStatement;
//import java.sql.ResultSet;
import com.pp.util.JdbcUtil;

import  java.sql.*;
import java.util.List;
import java.util.Map;

public class jdbctest {
    //mysql驱动包名
    private static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
    //数据库连接地址
    private static final String URL = "jdbc:mysql://localhost:3306/demo";
    //    private static final String URL = "jdbc:mysql://localhost:3306/demo?characterEncoding=utf-8&amp;useSSL=false";
    //用户名,更换成你自己的用户名，此处为root用户
    private static final String USER_NAME = "root";
    //密码，更换成你自己设定的密码，此处为:admin
    private static final String PASSWORD = "123456";
    public static void main(String[] args){
        Connection connection = null;
        try {
            //加载mysql的驱动类
//            Class.forName(DRIVER_NAME);
            //获取数据库连接
//            connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            connection = JdbcUtil.getConnection();
            //mysql查询语句
            String sql = "SELECT name FROM student";
//            PreparedStatement prst = connection.prepareStatement(sql);
            //结果集
//            ResultSet rs = prst.executeQuery();
//            while (rs.next()) {
//                System.out.println("用户名:" + rs.getString("name"));
//            }
//            rs.close();
//            prst.close();
            List<Map<String, Object>> zhmaps = JdbcUtil.queryForListMap(sql);
            System.out.println(zhmaps);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

4.最后就没了，懂得都懂，不懂的我也是自己摸索，有抄了其他大佬的博客链接地址