JDBC操作数据库简单实现

JDBC

    从基础学习下java操作数据库的相关内容

  1. 新建一个maven项目，添加下相关的依赖（mybatis/hibernate后面会用到 ）

    <dependencies>
        <!-- mysql数据库驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
            <scope>runtime</scope>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.4</version>
        </dependency>
        <!-- hibernate -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.2.6.Final</version>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.16.8</version>
        </dependency>
        <!-- log日志 -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.5</version>
        </dependency>
        <!-- 测试 -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

  2. 创建一个用户表，添加几条默认数据

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL COMMENT '用户名',
  `password` varchar(50) DEFAULT NULL COMMENT '密码',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `id_number` varchar(15) DEFAULT NULL,
  `creator` varchar(20) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `updater` varchar(20) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

  3. 创建一个实体类（先创建了一个基础DTO，通用数据放里面；重写了toString方法，用于展示数据；两个都实现了Serializable 接口，不知道有没有问题）

/**
 * 基础实体类
 */
@Data
public class BasicDTO implements Serializable {

    private Date createTime;
    private String creator;
    private Date updateTime;
    private String updater;

}

/**
 * 用户实体类
 */
@Data
public class UserDTO extends BasicDTO implements Serializable {

    private Long id;
    private String userName;
    private String password;
    private String name;
    private Integer age;
    private String idNumber;

    @Override
    public String toString() {
        return "User{" +
                "id='" + id + "'" +
                ", userName='" + userName + "'" +
                ", password='" + password + "'" +
                ", name='" + name + "'" +
                ", age=" + age +
                '}';
    }

}

  4. 创建查询逻辑（获取连接和关闭连接拿出了单独的方法；这里写了两种查询，普通查询Statement，前置处理PreparedStatement）

/**
 * 用户服务接口
 */
public interface UserService {

    List<UserDTO> getUserList(UserDTO userParam);

    UserDTO getUserById(Long id);

}


/**
 * 用户服务实现类
 */
public class UserServiceImpl implements UserService {

    // 日志
    Logger logger = LoggerFactory.getLogger(this.getClass());
    // JDBC连接配置
    private static String dbDriver = "com.mysql.jdbc.Driver";
    //private static String dbUrl = "jdbc:mysql://localhost:3306/student?autoReconnect=true&useUnicode=true&characterEncoding=UTF8";
    private static String dbUrl = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT";
    private static String username = "root";
    private static String password = "";

    /**
     * 查询用户列表
     *
     * @param userParam
     * @return
     */
    @Override
    public List<UserDTO> getUserList(UserDTO userParam) {
        List<UserDTO> userList = new ArrayList<UserDTO>();
        Connection connection = this.getConnection();
        try {
            // 执行查询
            Statement statement = connection.createStatement();
            String querySql = "select * from `user`";
            ResultSet result = statement.executeQuery(querySql);
            // 循环获取用户信息
            while (result.next()) {
                UserDTO user = new UserDTO();
                user.setName(result.getString("name"));
                user.setAge(result.getInt("age"));
                logger.info(user.toString());
                userList.add(user);
            }
            // 关闭
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection(connection);
        }
        return userList;
    }

    /**
     * 根据id查询用户
     *
     * @param id
     * @return
     */
    @Override
    public UserDTO getUserById(Long id) {
        UserDTO user = new UserDTO();
        Connection connection = this.getConnection();
        try {
            // 执行查询
            String querySql = "select * from `user` where `id` = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(querySql);
            preparedStatement.setLong(1, id);
            ResultSet result = preparedStatement.executeQuery();
            // 组装用户信息
            while (result.next()) {
                user.setName(result.getString("name"));
                user.setAge(result.getInt("age"));
            }
            logger.info(user.toString());
            // 关闭
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.closeConnection(connection);
        }
        return user;
    }

    /**
     * 获取连接
     *
     * @return
     */
    public Connection getConnection() {
        Connection conn = null;
        try {
            // 装载mysql驱动，该驱动被添加到DriverManager的registeredDrivers(线程安全CopyOnWriteArrayList)中
            Class.forName(dbDriver);
            // 获取数据库连接
            conn = DriverManager.getConnection(dbUrl, username, password);
            logger.info("------数据库连接成功------");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 关闭连接
     *
     * @param conn
     */
    public void closeConnection(Connection conn) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

  5. 创建测试类，执行测试（自己添加的数据，所以有一部分是null）

/**
 * JDBC查询测试
 */
public class UserServiceTest {

    private UserService userService;

    /**
     * 前置处理
     *
     * @throws Exception
     */
    @Before
    public void before() throws Exception {
        if (null == userService) {
            userService = new UserServiceImpl();
        }
    }

    @Test
    public void getUserList() throws Exception {
        List<UserDTO> userList = userService.getUserList(new UserDTO());
    }

    @Test
    public void getUserById() throws Exception {
        UserDTO user = userService.getUserById(Long.valueOf(1));
    }

}

---

下面是看的一部分源码的解析

   首先是加载数据库驱动  Class.forName(dbDriver);

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

    public static synchronized void registerDriver(java.sql.Driver driver)
        throws SQLException {

        registerDriver(driver, null);
    }

    public static synchronized void registerDriver(java.sql.Driver driver,
            DriverAction da)
        throws SQLException {

        /* Register the driver if it has not already been added to our list */
        if(driver != null) {
            registeredDrivers.addIfAbsent(new DriverInfo(driver, da));
        } else {
            // This is for compatibility with the original DriverManager
            throw new NullPointerException();
        }

        println("registerDriver: " + driver);

    }

  到这里DriverManager把mysql的驱动信息保存到了一个registeredDrivers列表（这是一个线程安全的CopyOnWriteArrayList）

  然后获取数据库连接  DriverManager.getConnection(dbUrl, username, password);

    @CallerSensitive
    public static Connection getConnection(String url,
        String user, String password) throws SQLException {
        java.util.Properties info = new java.util.Properties();

        if (user != null) {
            info.put("user", user);
        }
        if (password != null) {
            info.put("password", password);
        }

        return (getConnection(url, info, Reflection.getCallerClass()));
    }

  对用户名密码进行了非空的验证

    //  Worker method called by the public getConnection() methods.
    private static Connection getConnection(
        String url, java.util.Properties info, Class<?> caller) throws SQLException {
        /*
         * When callerCl is null, we should check the application's
         * (which is invoking this class indirectly)
         * classloader, so that the JDBC driver class outside rt.jar
         * can be loaded from here.
         */
        ClassLoader callerCL = caller != null ? caller.getClassLoader() : null;
        synchronized(DriverManager.class) {
            // synchronize loading of the correct classloader.
            if (callerCL == null) {
                callerCL = Thread.currentThread().getContextClassLoader();
            }
        }

        if(url == null) {
            throw new SQLException("The url cannot be null", "08001");
        }

        println("DriverManager.getConnection(\"" + url + "\")");

        // Walk through the loaded registeredDrivers attempting to make a connection.
        // Remember the first exception that gets raised so we can reraise it.
        SQLException reason = null;

        for(DriverInfo aDriver : registeredDrivers) {
            // If the caller does not have permission to load the driver then
            // skip it.
            if(isDriverAllowed(aDriver.driver, callerCL)) {
                try {
                    println("    trying " + aDriver.driver.getClass().getName());
                    Connection con = aDriver.driver.connect(url, info);
                    if (con != null) {
                        // Success!
                        println("getConnection returning " + aDriver.driver.getClass().getName());
                        return (con);
                    }
                } catch (SQLException ex) {
                    if (reason == null) {
                        reason = ex;
                    }
                }

            } else {
                println("    skipping: " + aDriver.getClass().getName());
            }

        }

        // if we got here nobody could connect.
        if (reason != null)    {
            println("getConnection failed: " + reason);
            throw reason;
        }

        println("getConnection: no suitable driver found for "+ url);
        throw new SQLException("No suitable driver found for "+ url, "08001");
    }

  进行了url的验证，循环找到刚才加载的驱动，执行  aDriver.driver.connect(url, info);

  没写完。。。