1、Java操作数据库
如何将爬虫爬取的数据存储下来也是我们关注的问题之前,常用的存储方式有.txt,MySQL,Hbase等。前面已经介绍了Java输入流输出流操作文本的方式,现在主要介绍几种Java操作mysql的方式。
java操作数据库第一种方法
这种方法,是操作数据库的最简单,也是最原始的方法。
package crawlerTest;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class MysqlConnectionTest { public static void main(String[] args) throws ClassNotFoundException, SQLException { Connection connection=null; Statement stmt=null; ResultSet rst=null; String driver="com.mysql.jdbc.Driver"; String dbURL="jdbc:mysql://127.0.0.1:3306/test"; String user = "root"; String password = "112233"; Class.forName(driver); try { connection = DriverManager.getConnection(dbURL,user,password); } catch (SQLException e) { e.printStackTrace(); } stmt = connection.createStatement(); String sql="select * from auto_forum_comments limit 1,100"; rst=stmt.executeQuery(sql); while (rst.next()) { System.out.println(rst.getString(6)); } rst.close(); stmt.close(); connection.close(); }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
java操作数据库的QueryRunner方法
QueryRunner是apache下面的开源操作数据库的工具,其是一款非常实用的操作数据库的工具,也是本人最常用的一种工具。其中包括batch()、update()、insert()等常用数据操作方法,下面我通过具体的程序来说明这些方法。
首先,我们要创建一个对象类(model),用来封装对象。如下图所示,为我们在model下建的两个类。其中Address类为本案例所用的类,主要用来封装数据库中的数据(我们需要操作的数据表),如下图所示。
package model;public class Address { private String addr_id; private String addr_car; private String addr_url; private String addr_forum; private String craw_time; public String getAddr_id() { return addr_id; } public void setAddr_id(String addr_id) { this.addr_id = addr_id; } public String getAddr_car() { return addr_car; } public void setAddr_car(String addr_car) { this.addr_car = addr_car; } public String getAddr_url() { return addr_url; } public void setAddr_url(String addr_url) { this.addr_url = addr_url; } public String getAddr_forum() { return addr_forum; } public void setAddr_forum(String addr_forum) { this.addr_forum = addr_forum; } public String getCraw_time() { return craw_time; } public void setCraw_time(String craw_time) { this.craw_time = craw_time; }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
接着我们创建封装数据库,用户名及密码的类,如下图所示。
package db;import javax.sql.DataSource;import org.apache.commons.dbcp2.BasicDataSource;public class MyDataSource { public static DataSource getDataSource(String connectURI){ BasicDataSource ds = new BasicDataSource(); ds.setDriverClassName("com.mysql.jdbc.Driver"); ds.setUsername("root"); ds.setPassword("112233"); ds.setUrl(connectURI); return ds; }}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
下面表示写操作数据库语句了
package crawlerTest;import java.sql.SQLException;import java.util.List;import javax.sql.DataSource;import model.Address;import org.apache.commons.dbutils.QueryRunner;import org.apache.commons.dbutils.ResultSetHandler;import org.apache.commons.dbutils.handlers.BeanListHandler;import org.apache.commons.logging.Log;import org.apache.commons.logging.LogFactory;import db.MyDataSource;public class QueryRunnerTest { static final Log logger = LogFactory.getLog(QueryRunnerTest.class); public static void main(String[] args) throws ClassNotFoundException, SQLException { /****** 所要操作的数据库******/ DataSource ds = MyDataSource.getDataSource("jdbc:mysql://127.0.0.1:3306/test"); QueryRunner qr = new QueryRunner(ds); ResultSetHandler<List<Address>> h = new BeanListHandler<Address>(Address.class); /****** 执行sql语句获取数据******/ List<Address> CarData = qr.query("SELECT addr_id,addr_car FROM auto_forum_comments", h); /****** 遍历输出数据******/ for (Address car:CarData) { logger.info("CarId:"+car.getAddr_id()+"\tcarName:"+car.getAddr_car()); } }}