跟Allen学Mybatis之一：如何使用jdbc操作数据库

主类：
public class JdbcExample {

    /**
     * 获取连接
     * @return
     */
    private Connection getConnection() {
        Connection connection = null;

        try {
            //  1 加载驱动获取连接
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/mybatis?zeroDateTimeBehavior=convertToNull";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url,user,password);
        } catch (ClassNotFoundException  | SQLException e) {
            e.printStackTrace();
            return null;
        }
        return connection;
    }

    public List<Role> getRole(String name){
        List<Role> list = new ArrayList<>();
        Connection connection = getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        // 模糊查询只认识 ？ 所以%必须作为参数传入
        // 2 拼装sql
        String sql = "select id,role_name,note from t_role where role_name like  ? "  ;
        try {
            // 3 根据connection + sql 获取 statement
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,  "%" + name  +"%" );
            //4 根据statment执行sql，得到结果集
            resultSet = preparedStatement.executeQuery();
            //5. 变例结果集，封装返回数据
            while (resultSet.next()){
                Role role = new Role();
                role.setId(resultSet.getLong("id"));
                role.setRole_name(resultSet.getString("role_name"));
                role.setNote(resultSet.getString("note"));
                list.add(role);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            //6. 关闭资源
             close(resultSet,preparedStatement,connection);
        }

    }

    private void close(ResultSet resultSet,Statement statement,Connection connection){
        try {
            if(resultSet != null && !resultSet.isClosed()){
                resultSet.close();
            }
            if(statement != null && !statement.isClosed()){
                statement.isClosed();
            }
            if(connection != null && !connection.isClosed()){
                connection.close();
            }
            System.out.println("资源都已经关闭");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        JdbcExample jdbcExample = new JdbcExample();
        List<Role> list = jdbcExample.getRole("名");
        System.out.println(Arrays.toString(list.toArray()));
    }
}

bean：

public class Role {

    private Long id;

    private String role_name;

    private String note;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getRole_name() {
        return role_name;
    }

    public void setRole_name(String role_name) {
        this.role_name = role_name;
    }

    public String getNote() {
        return note;
    }

    public void setNote(String note) {
        this.note = note;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", role_name='" + role_name + '\'' +
                ", note='" + note + '\'' +
                '}';
    }
}

创建表语句CREATE TABLE `t_role` (  `id` int(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `role_name` varchar(60) NOT NULL COMMENT '角色名称',
  `note` varchar(1024) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=MEMORY AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;