JDBC编程简单入门

Java程序都是通过JDBC连接数据库，JDBC是由SUN公司提出的一系列规范，但是它之定义了接口规范，而具体的实现由各个数据库产商来实现。JDBC是一种典型的桥接模式。

（1）后台数据库

CREATE TABLE `role` (
  `id` bigint(20) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `role` VALUES ('1', '老师', '教书育人');
COMMIT;

（2）pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.qhc</groupId>
    <artifactId>jdbc-example</artifactId>
    <version>1.0-SNAPSHOT</version>


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

（3）代码

package com.qhc.jdbc;

/**
 * 实体类-role
 */
public class Role {

    private Long id;
    private String roleName;
    private String note;

    //getter AND setter
}

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;

public class JdbcExample {

    private static String DRIVER = "com.mysql.jdbc.Driver";
    private static String URL = "jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true";
    String USERNAME = "root";
    String PASSWORD = "123456";

    /**
     * （1）连接数据库，注册驱动和数据库信息
     * （2）操作Connection，打开Statement对象
     * （3）通过Statement执行SQL，返回结果到ResultSet对象
     * （4）使用ResultSet读取数据，然后通过代码转化为具体的POJO对象
     * （5）关闭数据库相关资源
     */

    public static void main(String[] args) {
        JdbcExample example = new JdbcExample();
        Role role = example.getRole(1L);
        System.out.println("角色名称为：" + role.getRoleName());
    }

    //获取连接
    private Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName(DRIVER);
            connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (ClassNotFoundException e1) {
            Logger.getLogger(JdbcExample.class.getName()).log(Level.SEVERE,null,e1);
            return null;
        } catch (SQLException e2) {
            Logger.getLogger(JdbcExample.class.getName()).log(Level.SEVERE,null,e2);
            return null;
        }
        return connection;
    }

    //获取指定id的角色信息
    public Role getRole(Long id) {
        Connection connection = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("select id,role_name, note from role where id = ?");
            ps.setLong(1,id);  //设置占位1处的参数
            rs = ps.executeQuery();  //执行查询
            while (rs.next()) {
                Long roleId = rs.getLong("id");
                String userName = rs.getString("role_name");
                String note = rs.getString("note");
                Role role = new Role();
                role.setId(roleId);
                role.setRoleName(userName);
                role.setNote(note);
                return role;
            }
        } catch (SQLException e) {
            Logger.getLogger(JdbcExample.class.getName()).log(Level.SEVERE,null,e);
        } finally {
            close(rs,ps,connection);
        }
        return null;
    }

    //关闭连接
    private void close(ResultSet rs, Statement stmt, Connection connection) {
        try {
            if (rs!=null && !rs.isClosed()) {
                rs.close();
            }
            if (stmt!=null && !stmt.isClosed()) {
                stmt.close();
            }
            if (connection!=null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            Logger.getLogger(JdbcExample.class.getName()).log(Level.SEVERE,null,e);
        }
    }
}

 

参考资料：深入浅出的MyBatis技术原理与实战