java获取sql数据_通过java api 读取sql 中数据（查询）

配置文件：dbconfig.properties 里面的数据

jdbc.url.jwhat=jdbc\:mysql\://ip\:3306/laibadev?useUnicode\=true&characterEncoding\=UTF-8

jdbc.username.jwhat=

jdbc.password.jwhat=

jdbc.dbType=mysql

table 表sql:

INSERT INTO `bns_spider_user` VALUES ('http://toutiao.com/m4227761960/', NULL, NULL, NULL, 43, ' 娱乐八卦姐妹淘', NULL);

INSERT INTO `bns_spider_user` VALUES ('http://toutiao.com/m3704154333/', NULL, NULL, NULL, 44, ' MC1890', NULL);

INSERT INTO `bns_spider_user` VALUES ('http://toutiao.com/m5360794786/', NULL, NULL, NULL, 45, ' 八卦队长', NULL);

INSERT INTO `bns_spider_user` VALUES ('http://toutiao.com/m3676970251/', NULL, NULL, NULL, 46, ' 娱乐圈电影解读', NULL);

INSERT INTO `bns_spider_user` VALUES ('http://toutiao.com/m3345854756/', NULL, NULL, NULL, 47, ' 青春娱乐网', NULL);

INSERT INTO `bns_spider_user` VALUES ('http://toutiao.com/m3178840731/', NULL, NULL, NULL, 48, ' 百街秀娱乐', NULL);

INSERT INTO `bns_spider_user` VALUES ('http://toutiao.com/m4619040717/', NULL, NULL, NULL, 49, ' 时光机Studio', NULL);

INSERT INTO `bns_spider_user` VALUES ('http://toutiao.com/m3908723027/', NULL, NULL, NULL, 50, ' 星扒客push', NULL);

import java.io.ByteArrayInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.Map.Entry;

import java.util.Properties;

import java.util.Set;

import cn.paltform.Spider;

/**

* ClassName:DBlUtils

* Function: TODO ADD FUNCTION.

* Reason: TODO ADD REASON.

* Date: 2015年9月14日 下午3:19:51

*

* @author

* @version

* @since JDK 1.7.79

*/

public class DBlUtils {

public static String url = null;

public static String username = null;

public static String password = null;

public static Connection conn;

public static Statement stmt;

public static ResultSet rs;

public static String fileName = null;

public static String PATH = "/dbconfig.properties";

public static Properties properties;

static {

try {

InputStream is = DBlUtils.class.getResourceAsStream(PATH);

System.out.println("is:" + is);

properties = new Properties();

properties.load(is);

url = properties.getProperty("jdbc.url.jwhat");

username = properties.getProperty("jdbc.username.jwhat");

password = properties.getProperty("jdbc.password.jwhat");

if (is != null)

is.close();

} catch (IOException e) {

e.printStackTrace();

}

}

public void closeConnection(Connection conn) {

if (conn != null) {

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

public static void insertd(String sql) {

try {

conn = DriverManager.getConnection(url, username, password);

conn.setAutoCommit(false);

stmt = conn.prepareStatement("load data local infile '' "

+ "into table loadtest fields terminated by ','");

StringBuilder sb = new StringBuilder();

InputStream is = new ByteArrayInputStream(sb.toString().getBytes());

((com.mysql.jdbc.Statement) stmt).setLocalInfileInputStream(is);

stmt.executeUpdate(sql);

conn.commit();

} catch (SQLException e) {

e.printStackTrace();

}

}

/**

* 查询url

*

* @param sql

* @return

*/

public static List selecturlBySql(String sql) {

List listUrl = new ArrayList();

try {

conn = DriverManager.getConnection(url, username, password);

conn.setAutoCommit(false);

stmt = conn.prepareStatement("load data local infile '' "

+ "into table loadtest fields terminated by ','");

StringBuilder sb = new StringBuilder();

InputStream is = new ByteArrayInputStream(sb.toString().getBytes());

((com.mysql.jdbc.Statement) stmt).setLocalInfileInputStream(is);

rs = stmt.executeQuery(sql);

if (rs != null) {

ResultSetMetaData metaData = rs.getMetaData();

int count = metaData.getColumnCount();

Map map = null;

while (rs.next()) {

map = new HashMap();

for (int i = 1; i <= count; i++) {

map.put(metaData.getColumnName(i), rs.getObject(i));

}

listUrl.add(map);

}

}

} catch (SQLException e) {

e.printStackTrace();

}

return listUrl;

}

public static void main(String[] args) throws Exception {

String sql = "select t.url from bns_spider t ";

List selecturlBySql = selecturlBySql(sql);

for (int i = 0; i < selecturlBySql.size(); i++) {

Map map = selecturlBySql.get(i);

Set set = map.entrySet();

Iterator iterator = set.iterator();

String key = null;

String value = null;

while (iterator.hasNext()) {

Map.Entry mapentry = (Map.Entry) iterator.next();

key = (String)mapentry.getKey();

value =(String) mapentry.getValue();

}

//System.out.println( key + " " + value);

}

// insertd(sql);

}

}