java jdbc的入门教程

1. mysql连接驱动

​ mysql-connector-java-5.1.6.jar

maven配置:

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.6</version>
</dependency>

2. 建表语句

CREATE TABLE `sms` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
    `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `host` varchar(200) DEFAULT NULL,
    `TRIGGER_NAME` varchar(200) DEFAULT NULL,
    `DETAIL` varchar(200) DEFAULT NULL,
    `STATUS` varchar(10) DEFAULT NULL,
    `type` int(11) DEFAULT '0',
    `zone` varchar(200) DEFAULT NULL,
    `send_type` tinyint(4) DEFAULT '0',
    `sms_send_status` tinyint(4) DEFAULT '0',
    `alarm_type` tinyint(4) DEFAULT '0',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

3. 数据库表数据如下

4. JDBC的使用代码

public class JdbcTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet res = null;
        String driver = "com.mysql.jdbc.Driver";
        String userName = "root";
        String password = "123456";
        String url = "jdbc:mysql://localhost:3306/test?characterEncoding=utf-8";

        try {
            //加载数据库驱动
            Class.forName(driver);

            // 通过驱动管理类获取数据库连接
            connection = DriverManager.getConnection(url, userName, password);

            // 定义sql语句, ?标识占位符
            String sql = "select * from sms where id = ?";

            // 获取预处理statement
            preparedStatement = connection.prepareStatement(sql);

            // 设置参数, 第一个参数为sql语句中参数的需要从1开始
            preparedStatement.setString(1, "3");

            // 向数据库发出sql执行查询, 查询出结果集
            res = preparedStatement.executeQuery();

            // 遍历查询结果集
            while (res.next()) {
                System.out.println(res.getString("id") + " " + res.getString("time") + " " + res.getString("host"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            if (res != null) {
                try {
                    res.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();
                }
            }
        }
    }
}

5. 执行结果