JDBC、druid连接数据库

JDBC

	Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
	Connection connection = DriverManager.getConnection(url, userName, password);

Druid

第一种方式：

      Properties properties = new Properties();
      properties.setProperty("url", url);
      properties.setProperty("username", userName);
      properties.setProperty("password", password);
      DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
      Connection connection = dataSource.getConnection();

第二种方式：使用配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/db3?useSSL=false
username=root
password=123456789
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000
minIdle=3

	InputStream inputStream = DruidConfiguration.class.getClassLoader().getResourceAsStream("druid.properties");
    properties.load(inputStream);
	DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
	Connection connection = dataSource.getConnection();

第三种方式：

	 DataSource dataSource=new DataSource();
	 dataSource.setUrl(url);
	 dataSource.setUsername(userName);
	 dataSource.setPassword(password);
	 Connection connection = dataSource.getConnection();

示例1

/*DruidDataSource druidDataSource = new DruidDataSource();

        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("jdbc:mysql://localhost:3306/pgmiprt?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&createDatabaseIfNotExist=true");
        druidDataSource.setUsername("pgmiprt");
        druidDataSource.setPassword("9H)7[01t");
        druidDataSource.setPassword("SX{45z]0");

        //配置初始化大小、最小、最大
        druidDataSource.setInitialSize(1);
        druidDataSource.setMinIdle(1);
        druidDataSource.setMaxActive(20);
        //连接泄漏监测
        druidDataSource.setRemoveAbandoned(true);
        druidDataSource.setRemoveAbandonedTimeout(30);
        //配置获取连接等待超时的时间
        druidDataSource.setMaxWait(20000);
        //配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
        druidDataSource.setTimeBetweenEvictionRunsMillis(20000);
        //防止过期
        druidDataSource.setValidationQuery("SELECT 'x'");
        druidDataSource.setTestWhileIdle(true);
        druidDataSource.setTestOnBorrow(true);*/

        Properties properties = new Properties();
        InputStream is = DruidUtils.class.getClassLoader().getResourceAsStream("druid.properties");
//        properties.load(new FileInputStream("src/main/resources/druid.properties"));
        properties.load(is);

        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);

//        java.sql.Connection connection = druidDataSource.getConnection();

        //where tenant_id='?' and account_id in (?)
        String countSql = "select count(*) count from out_factory_product ;";
//        DruidPooledConnection pooledConnection = dataSource.getConnection();
        java.sql.Connection connection = dataSource.getConnection();

//        java.sql.Connection connection = pooledConnection.getConnection();


        //4. 获取数据库连接对象
//        conn = ds.getConnection();
        //5. 创建sql语句
//        String sql = "select * from user";
        //6. 创建执行sql对象
        Statement statement = connection.createStatement();
        //7. 创建一个ResultSet：结果集对象，封装查询结果
        ResultSet resultSet = statement.executeQuery(countSql);
        //8. 然后在用ResultSet里的方法 next():游标向下移动一行 判断是否有数据 有就是true，没有就是false
        while (resultSet.next()) {
            //rs.getString():传递的是数据库的字段
            String a = resultSet.getString(1);
         /*   String username = rs.getString("username");
            int password = rs.getInt("password");*/
            //9. 输入结果
            System.out.println(a);
        }
        
        statement.close();
        connection.close();

示例2

 public Connection getCon() {

        try {
            DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
            connection = dataSource.getConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public Connection getConnect() {
        try {
            if (connection == null || connection.isClosed()) {
                return getCon();
            } else {
                return connection;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    public <T> List<T> query(String sql, Class<T> t) {
        log.info(sql);
        List<T> listRs = new ArrayList<T>();
        Statement newStatement = null;
        try {
            newStatement = getConnect().createStatement();
            ResultSet rs = newStatement.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                JSONObject obj = new JSONObject();
                for (int i = 0; i < rsmd.getColumnCount(); i++) {
                    obj.put(CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, rsmd.getColumnLabel(i + 1)), rs.getObject(i + 1));
                }
//                System.out.println("数据封装前："+obj);
                listRs.add(JSON.parseObject(JSONObject.toJSONString(obj, SerializerFeature.WriteMapNullValue), t));
            }
            return listRs;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (newStatement != null) {
                    newStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return listRs;
    }


    public JSONObject querySingle(String sql) {
        log.info(sql);
        Statement newStatement = null;
        try {
            newStatement = getConnect().createStatement();
            ResultSet rs = newStatement.executeQuery(sql);
            ResultSetMetaData rsmd = rs.getMetaData();
            while (rs.next()) {
                JSONObject obj = new JSONObject();
                for (int i = 0; i < rsmd.getColumnCount(); i++) {

                    obj.put(CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, rsmd.getColumnLabel(i + 1)), rs.getObject(i + 1));
                }
                return obj;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (newStatement != null) {
                    newStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }


    public int batch(List<String> sql) {
        Connection con = null;
        Statement newStatement = null;
        try {
            if (sql == null || sql.isEmpty()) {
                return 0;
            }
            con = getConnect();
            newStatement = con.createStatement();
            con.setAutoCommit(false);
            for (String sq : sql) {
                log.info(sq);
                newStatement.addBatch(sq);
            }
            int[] executeBatch = newStatement.executeBatch();
            con.commit();
            return executeBatch == null ? 0 : executeBatch.length;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                newStatement.close();
                con.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return 0;
    }


    public ResponseBean update(String sql) {
        Statement newStatement = null;
        log.info(sql);
        try {
            newStatement = getConnect().createStatement();
            return Response.success("共操作" + newStatement.executeUpdate(sql) + "条数据！");
        } catch (Exception e) {
            e.printStackTrace();
            return Response.success(e.getMessage());
        } finally {
            try {
                newStatement.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }


    public ResponseBean delete(String sql) {
        Statement newStatement = null;
        log.info(sql);
        try {
            newStatement = getConnect().createStatement();
            int res = newStatement.executeUpdate(sql);
            return Response.success("删除成功！共删除" + res + "条数据！");
        } catch (Exception e) {
            e.printStackTrace();
            return Response.error(e.getMessage());
        } finally {
            try {
                newStatement.close();
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }