java之jdbc

1、JDBC.java

package com.ace.service;
import java.sql.*;

public class JDBC {
    public static void main(String[] args) {
        try {
            // 1、加载驱动类
            Class.forName("com.mysql.jdbc.Driver");
            // 2、获取连接对象
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/ace", "root", "888888");
            // 3、创建执行对象
            Statement statement = connection.createStatement();
            // 4.1、执行新增、删除、修改，返回影响行数
            int count_add = statement.executeUpdate("insert into student (id, name) values ('jdbc', 'JDBC新增')");
            if (count_add > 0) {
                System.out.println("新增成功");
            }
            int count_upd = statement.executeUpdate("update student set name = 'JDBC修改' where id = 'jdbc'");
            if (count_upd > 0) {
                System.out.println("修改成功");
            }
            int count_del = statement.executeUpdate("delete from student where id = 'jdbc'");
            if (count_del > 0) {
                System.out.println("删除成功");
            }
            // 4.2、执行查询，返回结果集对象
            ResultSet resultSet = statement.executeQuery("select * from student");
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                System.out.println("姓名：" + name + "，年龄：" + age);
            }
            // 5、关闭资源
            resultSet.close();
            resultSet = null;
            statement.close();
            statement = null;
            connection.close();
            connection = null;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2、BaseDao.java

package com.ace.service;
import java.sql.*;

public class BaseDao {

    private final String driver = "com.mysql.jdbc.Driver";
    private final String url = "jdbc:mysql://localhost:3306/ace";
    private final String username = "root";
    private final String password = "888888";

    private Connection connection = null;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;

    /**
     * 获取连接对象
     * @return 连接对象
     */
    public Connection getConnection() {
        try {
            Class.forName(this.driver);
            this.connection = DriverManager.getConnection(this.url, this.username, this.password);
            return connection;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 执行增删改调用此方法
     * @param sql 支持参数化的sql
     * @param params 参数数组
     * @return 影响行数
     */
    public int executeUpdate(String sql, Object[] params) {
        int count = -1;
        try {
            this.getConnection();
            this.preparedStatement = this.connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            count = preparedStatement.executeUpdate();
            this.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 执行查询调用此方法
     * @param sql 支持参数化的sql
     * @param params 参数数组
     * @return 结果集对象
     */
    public ResultSet executeQuery(String sql, Object[] params) {
        try {
            this.getConnection();
            preparedStatement = this.connection.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            resultSet = preparedStatement.executeQuery();
            return resultSet; // ?资源不能关
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 关闭资源
     */
    public void close() {
        try {
            if (this.resultSet != null) {
                this.resultSet.close();
                this.resultSet = null;
            }
            if (this.preparedStatement != null) {
                this.preparedStatement.close();
                this.preparedStatement = null;
            }
            if (this.connection != null) {
                this.connection.close();
                this.connection = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * BaseDao测试
     */
    public static void main(String[] args) {
        // 新增
        String sql_add = "insert into student (id, name) values (?, ?)";
        int count_add = new BaseDao().executeUpdate(sql_add, new Object[]{"basedao", "BaseDao新增"});
        if (count_add > 0) {
            System.out.println("新增成功");
        }
        // 修改
        String sql_upd = "update student set name = ? where id = ?";
        int count_upd = new BaseDao().executeUpdate(sql_upd, new Object[]{"BaseDao修改", "basedao"});
        if (count_upd > 0) {
            System.out.println("修改成功");
        }
        // 删除
        String sql_del = "delete from student where id = ?";
        int count_del = new BaseDao().executeUpdate(sql_del, new Object[]{"basedao"});
        if (count_del > 0) {
            System.out.println("删除成功");
        }
        // 查询
        String sql = "select * from student where name like ? and age = ?";
        ResultSet resultSet = new BaseDao().executeQuery(sql, new Object[]{"%张三%", 20});
        try {
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                System.out.println("姓名：" + name + "，年龄：" + age);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}