JDBC编程

JDBC：Java DataBase Connectivity，Java数据库的连接 

JDBC API编程步骤：

1、创建Java项目

2、准备数据库：memo数据库

3、添加JDBC驱动的依赖

4.1、加载驱动类

Class.forName("com.mysql.jdbc.Driver")

该步骤在5.1以后的驱动包不用手动导入，在META-INF文件夹中提供有一个services文件，里面存放着驱动类的全名称，在调用DriverManager.getConnection(url)方法时，会默认去加载。

4.2、获取连接：DriverManager.getConnection(url)

连接mysql数据库的url：jdbc:mysql://127.0.0.1:3306" +/hello?user=root&password=root

获取连接的两种方式：

通过调用DriverManager类的静态方法getConnection(url)；

通过DataSource（数据源）的方式获取，DataSource是一个接口，里面有两个重载方法getConnection()。

4.3、创建命令：connection.prepareStatement(sql)

Statement对象是将sql语句发送到数据库中，JDBC API提供的三种Statement对象

1）Statement：

用于执行不带参数的简单sql语句

2）PreparedStatement：

用于执行带或不带参数的sql语句；

sql语句会预编译在数据库系统；

执行速度快于Statement；

避免注入攻击（“1 or 1=1”）?：单个值匹配

        int 进行类型转换

        String 会当成一个整体

3）CallableStatement：

用来执行数据库存储过程的调用

4.4、SQL语句

4.5、参数占位，设置参数

4.6、执行（Query、Update）

查询：preparedStatement.executeQuery()；

更新（增加、删除、更新）：preparedStatement.executeUpdate()。

4.7、处理结果

4.8、释放资源

5、测试

 

类结构：

将数据库的操作封装为一个类：JdbcUtil

//JDBC编程
public class JdbcUtil {
    //jdbc:db://host:port/databasename?p1=v1&p2=v2
    private final String userName;
    private final String password;
    private final String database;
    private final String host;
    private final Integer port;

    public JdbcUtil(String userName, String password, String database, String host, Integer port) {
        this.userName = userName;
        this.password = password;
        this.database = database;
        this.host = host;
        this.port = port;
    }

    //1、连接数据库
    public Connection getConnection() {
        //加载驱动，5.1以后的驱动包不需要手动导入
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        String url = String.format(
                //jdbc:mysql://127.0.0.1:3306" +/hello?user=root&password=root
                "jdbc:mysql://%s:%d/%s?user=%s&password=%s",
                this.host,
                this.port,
                this.database,
                this.userName,
                this.password
        );
        try {
            return DriverManager.getConnection(url);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //2、创建操作命令
    public PreparedStatement getPreparedStatement(Connection connection, String sql) {
        try {
            return connection.prepareStatement(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //3、操作
    //查询操作
    public ResultSet query(PreparedStatement preparedStatement) {
        try {
            return preparedStatement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //更新操作
    public int update(PreparedStatement preparedStatement) {
        try {
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //4、关闭
    public void doClose(ResultSet set, PreparedStatement preparedStatement, Connection connection) {
        if (set != null) {
            try {
                set.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

MemoGroupDaoImpl实现类：实现对表的增删改查以及查询全部操作。

public class MemoGroupDaoImpl implements MemoGroupDao{
    private JdbcUtil jdbcUtil;

    public MemoGroupDaoImpl(JdbcUtil jdbcUtil) {
        this.jdbcUtil = jdbcUtil;
    }

    //插入
    @Override
    public boolean insert(MemoGroup memoGroup) {
        String sql = "insert into memo_group(name, created_time) values(?,?)";
        Connection connection = this.jdbcUtil.getConnection();
        PreparedStatement preparedStatement = this.jdbcUtil.getPreparedStatement(connection, sql);
        try {
            preparedStatement.setString(1, memoGroup.getName());
            preparedStatement.setDate(2, new Date(memoGroup.getCreatedTime().getTime()));
            int effect = this.jdbcUtil.update(preparedStatement);
            return effect == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.jdbcUtil.doClose(null, preparedStatement, connection);
        }
        return false;
    }

    //更新
    @Override
    public boolean update(MemoGroup memoGroup) {
        String sql = "update memo_group set name = ? where id = ?";
        Connection connection = this.jdbcUtil.getConnection();
        PreparedStatement preparedStatement = this.jdbcUtil.getPreparedStatement(connection, sql);
        try {
            preparedStatement.setString(1, memoGroup.getName());
            preparedStatement.setInt(2, memoGroup.getId());
            int effect = this.jdbcUtil.update(preparedStatement);
            return effect == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.jdbcUtil.doClose(null, preparedStatement, connection);
        }
        return false;
    }

    //删除
    @Override
    public boolean delete(int id) {
        String sql = "delete from memo_group where id = ?";
        Connection connection = this.jdbcUtil.getConnection();
        PreparedStatement preparedStatement = this.jdbcUtil.getPreparedStatement(connection, sql);
        try {
            preparedStatement.setInt(1, id);
            int effect = this.jdbcUtil.update(preparedStatement);
            return effect == 1;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.jdbcUtil.doClose(null, preparedStatement, connection);
        }
        return false;
    }

    //查询：通过id查询
    @Override
    public MemoGroup queryById(int id) {
        String sql = "select * from memo_group where id = ?";
        Connection connection = this.jdbcUtil.getConnection();
        PreparedStatement preparedStatement = this.jdbcUtil.getPreparedStatement(connection, sql);
        ResultSet resultSet = null;
        try {
            preparedStatement.setInt(1, id);
            resultSet = this.jdbcUtil.query(preparedStatement);
            if(resultSet.next()){
                MemoGroup memoGroup = new MemoGroup();
                memoGroup.setId(resultSet.getInt("id"));
                memoGroup.setName(resultSet.getString("name"));
                memoGroup.setCreatedTime(resultSet.getDate("created_time"));
                memoGroup.setModifyTime(resultSet.getDate("modify_time"));
                return memoGroup;
            }else{
                return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.jdbcUtil.doClose(resultSet, preparedStatement, connection);
        }
        return null;
    }

    //查询所有
    @Override
    public List<MemoGroup> queryAll() {
        String sql = "select * from memo_group";
        Connection connection = this.jdbcUtil.getConnection();
        PreparedStatement preparedStatement = this.jdbcUtil.getPreparedStatement(connection, sql);
        ResultSet resultSet = null;
        List<MemoGroup> memoGroups = new ArrayList<>();
        try {
            resultSet = this.jdbcUtil.query(preparedStatement);
            while(resultSet.next()){
                MemoGroup memoGroup = new MemoGroup();
                memoGroup.setId(resultSet.getInt("id"));
                memoGroup.setName(resultSet.getString("name"));
                memoGroup.setCreatedTime(resultSet.getDate("created_time"));
                memoGroup.setModifyTime(resultSet.getDate("modify_time"));
                memoGroups.add(memoGroup);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.jdbcUtil.doClose(resultSet, preparedStatement, connection);
        }
        return memoGroups;
    }
}