Jdbc例子

Jdbc简单例子如下：

package com.wu.common.db.jdbc;

import java.sql.*;

/**
 * JDBC demos
 * @author wusq
 * @date 2018-06-12
 */
public class JdbcDemos {

    private static final String JDBC_CLASSNAME = "org.postgresql.Driver";
    private static final String JDBC_URL = "jdbc:postgresql://localhost:5432/test";
    private static final String JDBC_USERNAME = "postgres";
    private static final String JDBC_PASSWORD = "postgres";

    // 创建表create table table_test(id integer primary key, name varchar(32));

    /**
     * 批量插入
     */
    static void insertBatch(){

        Connection connection = null;
        PreparedStatement statement = null;

        try {
            Class.forName(JDBC_CLASSNAME);
            connection =  DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
            String sql = "insert into table_test(id,name) values(?,?)";
            statement = connection.prepareStatement(sql);
            for(int i=0; i<1000; i++){
                statement.setInt(1, i);
                statement.setString(2, i+"");
                statement.addBatch();
                if(i%100 == 0){
                    statement.executeBatch();
                    statement.clearBatch();
                }
            }
            statement.executeBatch();
            // connection.commit(); 自动提交事务
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(statement, connection);
        }
    }

    /**
     * 查询
     */
    static void query(){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            Class.forName(JDBC_CLASSNAME);
            connection =  DriverManager.getConnection(JDBC_URL, JDBC_USERNAME, JDBC_PASSWORD);
            String sql = "select id,name from table_test where id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, 1);
            resultSet =  statement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println(String.format("%s,%s", id, name));
            }
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.close(resultSet, statement, connection);
        }
    }

    public static void main(String[] args) {
        //insertBatch();
        query();
    }
}

Jdbc工具类如下：

package com.wu.common.db.jdbc;

import java.sql.*;

/**
 * Jdbc工具类
 * @author wusq
 * @date 2018-06-12
 */
public class JdbcUtils {

    /**
     * 关闭资源
     * @param statement
     * @param connection
     */
    public static void close(PreparedStatement statement, Connection connection){
        close(null, statement, connection);
    }

    /**
     * 关闭资源
     * @param resultSet
     * @param statement
     * @param connection
     */
    public static void close(ResultSet resultSet, PreparedStatement statement, Connection connection){
        try {
            if(resultSet != null){
                resultSet.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

JDBC驱动和jar包引用收集如下：
MariaDB

jdbc.driver=org.mariadb.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/dbname?useUnicode=true&characterEncoding=UTF-8&useOldAliasMetadataBehavior=true

<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.2.1</version>
</dependency>

Oracle

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orcl

PostgreSQL

jdbc.driver=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/mobile

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.1.1</version>
</dependency>

SQLServer

jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.url=jdbc:sqlserver://localhost:1433;DatabaseName=test