本文共 3122 字,大约阅读时间需要 10 分钟。
最近项目中有个简单的需求就是需要读取数据库中的数据,并调用远程api去操作数据,成功则更新相应字段。那么最首要的一步就是java对Mysql数据库的操作,在此简单的记录一下。
整个操作流程如下:
1.加载数据库驱动:"com.mysql.jdbc.Driver"
2.通过url,user,password来建立连接
3.创建statement对象
4.执行sql语句,取出数据,更新数据,删除数据
5.断开连接
下面是一个demo:
import java.sql.*;public class DBTest{ private static final String url = "jdbc:mysql://url:port/database"; private static final String driver = "com.mysql.jdbc.Driver"; private static final String user = "username"; private static final String password = "password"; private static final String tableName = "tablename"; private static final int total = 100; private Connection conn = null; private static final int step = 5; public DBTest() throws ClassNotFoundException,InstantiationException,IllegalAccessException,SQLException{ try { //获得mysql驱动实例 Class.forName(driver).newInstance(); //通过url,user,password来获取连接 conn = DriverManager.getConnection(url,user,password); } catch ( ClassNotFoundException e){ e.printStackTrace(); } catch ( InstantiationException e){ e.printStackTrace(); } catch ( IllegalAccessException e){ e.printStackTrace(); } catch ( SQLException e) { e.printStackTrace(); } } public void process() throws SQLException{ if( conn ==null || conn.isClosed() ) { System.out.println("Fail to connect to Mysql server using TCP/IP..."); return ; } /*创建statement*/ Statement st = conn.createStatement(); /*必须重建一个statement,否则如果在原有的statement实例execute其他的sql语句的话, *原有的请求自动关闭,那么下面的rs.next就会失败 */ Statement st1 = conn.createStatement(); int i ; //避免记录过多select结果无法一次性装载入内存 for (i = 0; i < total; i+=step) { String querySql = String.format("SELECT * FROM %s where create_time <= DATE_ADD(NOW(),INTERVAL -3 MONTH) limit %d,%d", tableName,i,step); /*运行SQL读取数据*/ ResultSet rs = st.executeQuery(querySql); /*逐行读取数据*/ while (rs.next()) {// if (rs.getString("finished").equals("1")) {// continue;// } /*获取相应字段的信息*/ System.out.println("name=" + rs.getString("name") + " finished=" + rs.getString("finished")); String updateSql = String.format("update %s set finished = 0 where name = '%s';", tableName, rs.getString("name")); int resNum = st1.executeUpdate(updateSql); if (resNum > 0) { System.out.println("update ok " + resNum); } } } //关闭连接 conn.close(); } public static void main(String[] args) throws ClassNotFoundException,InstantiationException,IllegalAccessException,SQLException{ DBTest dbTest = new DBTest(); dbTest.process(); }}
如果运行过程中出现"com.mysql.jdbc.Driver"没有找到说明驱动没有安装,在pom.xml中添加:
mysql mysql-connector-java 5.1.6
References:
>
>
Author:忆之独秀
Email:leaguenew@qq.com
注明出处:
转载地址:http://uvnpf.baihongyu.com/