Jdbc例子

爱被打了一巴掌 2022-05-22 08:04 172阅读 0赞

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