(2) jbdc入门程序

首先需要准备环境

运行环境 ：jdk 1.8
开发环境： idea
项目构建：maven
需要的jar文件： mysql-connector-java:5.1.46
数据库:mysql

1.maven的环境配置

    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.46</version>
    </dependency>

2.测试的实体和对应的sql语句

• SQL语句

CREATE TABLE `user` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`username`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '用户名称' ,
`birthday`  date NULL DEFAULT NULL COMMENT '用户生日' ,
`sex`  char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '性别' ,
`address`  varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=11
ROW_FORMAT=DYNAMIC;

• User的实体

/**
 * @Project: java-sql
 * @description:  user的实体
 * @author: sunkang
 * @create: 2018-10-07 12:44
 * @ModificationHistory who      when       What
 **/
public class User implements Serializable {
    private int id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", address='" + address + '\'' +
                '}';
    }
}

3.jdbc入门程序

/**
 * @Project: java-sql
 * @description: jdbc的入门程序
 * @author: sunkang
 * @create: 2018-10-07 12:04
 * @ModificationHistory who      when       What
 **/
public class JdbcTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1.加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.通过驱动管理类获取数据库链接
            connection =  DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8", "root", "123");
            //定义sql语句 ?表示占位符
            String sql = "select * from user where username = ?";
            //3.使用连接获取预处理statement
            preparedStatement = connection.prepareStatement(sql);
            //4.为预处理的statement设置参数，第一个参数为sql语句中参数的序号（从1开始），第二个参数为设置的参数值
            preparedStatement.setString(1, "王五");
            //5.执行sql执行查询，得出出结果集
            resultSet =  preparedStatement.executeQuery();

            //6.遍历查询结果集，进行结果集的映射成对象
            List<User> userList = new ArrayList<User>();
            while(resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setSex(resultSet.getString("sex"));
                user.setBirthday(resultSet.getTimestamp("birthday"));
                user.setAddress(resultSet.getString("address"));
                userList.add(user);
            }
            //输出结果
            System.out.println(userList.toString());

        } catch (Exception e) {
            //7. 处理异常
            e.printStackTrace();
        }finally{
            //8. 释放资源，依次从结果集、statement，数据库连接一次释放，顺序不能反了
            if(resultSet!=null){
                try {
                    resultSet.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();
                }
            }

        }

    }
}