[Java知识库]JDBC增删改查

?总共4个public方法：增删改查

package com.xy;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

/**
 * @ClassName JdbcUtil
 * @Description
 * @Author xy
 * @Date 2022/4/22
 * @Version 1.0
 **/
public class JdbcUtil {

    private static String driver = "com.mysql.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/mysql"; //连接数据库(mysql是数据库名)
    private static String user = "root";//连接mysql的用户名
    private static String password = "123";//连接mysql的密码
    private static Connection conn = null;


    static {
        try {
//            readProperties();
/*

jdk1.6以后会去读取mysql META-INF/services/java.sql.Driver下自动注册
mysqL驱动5.1.6版本会有META-INF/services/java.sql.Driver
*/
// jdk1.6、mysql 5.1.6以后不再需要手动加载   Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void readProperties() throws IOException {
        //读取配置文件
        InputStream in = JdbcUtil.class.getResourceAsStream("db.properties");
        Properties properties = new Properties();
        //加载配置文件
        properties.load(in);
        //获取配置文件中的数据
        driver = properties.getProperty("driver");
        url = properties.getProperty("url");
        user = properties.getProperty("user");
        password = properties.getProperty("password");
    }

    /**
     * 获取一个数据库链接
     */
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    private static void closeConn(Connection conn) {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException 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;
    }

    public static void main(String[] args) {
        System.out.println(queryForListMap("select db,user from db"));
    }
}

拓展

jdk1.6 使用了jdbc4，以后会去读取mysql META-INF/services/java.sql.Driver下自动注册
mysqL驱动5.1.6版本会有META-INF/services/java.sql.Driver

?

阅读世界，共赴山海

423全民读书节，邀你共读