一个简单的jdbc编程案例

开发工具:idea

数据库:Mysql

1.创建t_role表

DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role` (
  `id` bigint(40) NOT NULL COMMENT '主键',
  `role_name` varchar(40) CHARACTER SET utf8 DEFAULT NULL COMMENT '角色名',
  `note` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

2.案例

pom中的依赖:

<dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.3.0</version>
    </dependency>

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

 

Role对象:

public class Role {

    private Long id;//主键
    private String roleName;//角色名称
    private String note;//备注

    public Long getId() {
        return id;
    }

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

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getNote() {
        return note;
    }

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

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

jdbc代码:

package com.lenovo;

import com.lenovo.pojo.Role;

import java.sql.*;

public class JdbcExample {

    public Role getRoleId(Long id){
        Connection connection = getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = connection.prepareStatement("select id,role_name,note from t_role where id = ?");
            ps.setLong(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                long roleId = rs.getLong(1);
                String roleName = rs.getString(2);
                String note = rs.getString(3);
                Role role = new Role();
                role.setId(roleId);
                role.setRoleName(roleName);
                role.setNote(note);
                return role;
            }

        }catch (Exception e){
            e.printStackTrace();
        }finally {
            close(rs,ps,connection);
        }
        return null;
    }

    private Connection getConnection(){
        Connection connection = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/mysql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&useSSL=false";
            String user = "root";
            String password = "root";
            connection = DriverManager.getConnection(url, user, password);
        }catch (Exception e){
            System.out.println(JdbcExample.class.getName());
            e.printStackTrace();
            return null;
        }
        return connection;
    }

    private void close(ResultSet rs, Statement st,Connection connection){
        try {
            if(rs != null && !rs.isClosed()){
                rs.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        try {
            if(st != null && !st.isClosed()){
                st.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        try {
            if(connection != null && !connection.isClosed()){
                connection.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        JdbcExample example = new JdbcExample();
        Role roleId = example.getRoleId(1l);
        System.out.println("角色名称:" + roleId.getRoleName());
    }
}

 

执行结果: